Sample code to retrieve server and database info of Biztalk MessageBox and PIT

Since there is a request for this, here is an example to retrieve server name and database for Biztalk MessageBox and BAM Primary Import database.  Since this is accessing internal implementation, this code is not supportable (use at your own risk) and users will be required to maintain this code on their own when doing upgrade.

Here are the steps:

Create a console appplication project in Visual Studio,

Cut and paste the code below and run

Sample output:

InfoFound Management Server KW0081 Database BizTalkMgmtDb
InfoFound MsgBox Server KW0081 Database BizTalkMsgBoxDb
InfoFound PIT Server KW0081 Database BAMPrimaryImport
InfoFound DTA Server KW0081 Database BizTalkDTADb

 

Sample Code:

using

System;

using

System.Data.SqlClient;

using

System.Globalization;

using

Microsoft.Win32;

namespace KeithLimBizTalkBAMUtility

{

class InfrastructureInfo

{

[STAThread]

static void Main(string[] args)

{

InfrastructureInfo ii =

new InfrastructureInfo();

ii.GetInfo();

}

internal const string BtsAdminRegistryKey = @"Software\\Microsoft\\BizTalk Server\\3.0\\Administration";

internal const string BtsServerRegistryKey = @"MgmtDBServer";

internal const string BtsNameRegistryValue = @"MgmtDBName";

internal const string BtsMgmtSprocRetrieveMsgBoxPITandDTA = @"SELECT SubscriptionDBServerName, SubscriptionDBName, BamDBServerName, BamDBName, TrackingDBServerName, TrackingDBName FROM adm_Group";

internal const string BtsMigrateError = @"Error";

internal const string BtsMigrateInfo = @"Info";

internal const string BtsMigrateWarning = @"Warning";

private string managementServer;

private string managementDatabase;

private string dtaServer;

private string dtaDatabase;

private string pitServer;

private string pitDatabase;

private string msgBoxServer;

private string msgBoxDatabase;

internal static string BuildConnectionString(string server, string database)

{

const string SqlConnectionString = @"Data Source=""{0}"";Initial Catalog=""{1}"";Integrated Security=SSPI;Application Name=""BamManager"";Connect Timeout=60";

return String.Format(CultureInfo.InvariantCulture,

SqlConnectionString, server, database);

}

private void GetInfo()

{

RegistryKey key = Registry.LocalMachine.OpenSubKey(BtsAdminRegistryKey,

false);

SqlDataReader reader =

null;

SqlConnection btmConnection =

null;

if (key != null)

{

this.managementServer = (string)key.GetValue(BtsServerRegistryKey);

this.managementDatabase = (string)key.GetValue(BtsNameRegistryValue);

}

else

{

Console.WriteLine(BtsMigrateError + "Failed to retrieve Management database connection string from " + BtsAdminRegistryKey);

return;

}

Console.WriteLine(BtsMigrateInfo +

String.Format("Found Management Server {0} Database {1}",

this.managementServer, this.managementDatabase));

try

{

// Open up the Management Database

string btmConnectionString = BuildConnectionString(this.managementServer, this.managementDatabase);

btmConnection =

new SqlConnection(btmConnectionString);

btmConnection.Open();

// Query the Primary Import Database

SqlCommand cmd =

new SqlCommand(BtsMgmtSprocRetrieveMsgBoxPITandDTA, btmConnection);

reader = cmd.ExecuteReader();

if (!reader.Read())

{

Console.WriteLine(BtsMigrateError + "Failed to retrieve PIT and DTA databases from management DB (is group deployed?)");

}

this.msgBoxServer = reader.GetString(0);

this.msgBoxDatabase = reader.GetString(1);

this.pitServer = reader.GetString(2);

this.pitDatabase = reader.GetString(3);

this.dtaServer = reader.GetString(4);

this.dtaDatabase = reader.GetString(5);

Console.WriteLine(BtsMigrateInfo +

String.Format("Found MsgBox Server {0} Database {1}",

this.msgBoxServer, this.msgBoxDatabase));

Console.WriteLine(BtsMigrateInfo +

String.Format("Found PIT Server {0} Database {1}",

this.pitServer, this.pitDatabase));

Console.WriteLine(BtsMigrateInfo +

String.Format("Found DTA Server {0} Database {1}",

this.dtaServer, this.dtaDatabase));

}

finally

{

if (reader != null)

{

reader.Close();

}

btmConnection.Close();

}

}

}

}