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();

}

}

}

}

Comments

• Anonymous
  February 08, 2006
  This is great, this is exactly what I was looking for.
  Again, this is only for 2004, where as in 2006 you can use the context property and it will automatically resolve where the home is?
• Anonymous
  February 08, 2006
  That's correct.
• Anonymous
  February 09, 2006
  another way is querying the sp [adm_Group_Enum]... for people that don't like use "select" statements from code
• Anonymous
  March 17, 2006
  Why use the unsupported SQL statements and registry when you can use a few lines of code to query the supported WMI objects?
  
             using(ManagementObjectSearcher search = new ManagementObjectSearcher("ROOT\MicrosoftBizTalkServer", "SELECT * FROM MSBTS_GroupSetting"))
             {
                 ManagementObjectCollection results = search.Get();
                 foreach(ManagementObject btsGroup in results)
                 {
                     Console.WriteLine("BAM database information:");
                     Console.WriteLine("Server:{0}", btsGroup["BAMDBServerName"]);
                     Console.WriteLine("Database:{0}",  btsGroup["BamDBName"]);
  
                     Console.WriteLine("Tracking database information:");
                     Console.WriteLine("Server:{0}", btsGroup["TrackingDBServerName"]);
                     Console.WriteLine("Database:{0}", btsGroup["TrackingDBName"]);
  
                     Console.WriteLine("MessageBox database information:");
                     Console.WriteLine("Server:{0}", btsGroup["SubscriptionDBServerName"]);
                     Console.WriteLine("Database:{0}", btsGroup["SubscriptionDBName"]);
                 }
             }
• Anonymous
  March 20, 2006
  Thanks Matt, this is very useful.  Now I would just need to read up more on the supportable objects on WMI.