Moving Microsoft BizTalk Server 2006 Databases to a new SQL Server

Posted on September 6th, 2007 in SQL Server by Tom

So, for whatever reason you need to move your BizTalk databases from one SQL server to another. In my case, I was moving from a hostname to a CNAME, for DR purposes (failover server is in another DC, and not clustered).

In the event of a disaster, we could just fail over to the other database server, change the CNAME to point to the “failover” server, and we should be back up and running. The problem, I found, was changing from DC1BTSQL1 to the CNAME (we’ll call it BTSQL1). After searching for a while (maybe I’m an idiot, but all of these things were classified as “backup and restore methods” not “I’m changing server name” methods or anything like that) I found two scripts and an xml file.

Under %systemdrive%\Program Files\Microsoft Biztalk Server 2006\Schema\Restore are two VBS scripts. UpdateDatabase.vbs and UpdateRegistry.vbs. There’s also an XML file called “SampleUpdateInfo.xml.” Below is the procedure for changing the SQL server name. You can even change the names of the DBs. This procudure assumes you’ve backed up and restored, or attached, the DBs to the “new” server already.



1) Stop BizTalk Service on BizTalk Server
2) Stop Enterprise SSO Service on BizTalk Server
3) Edit C:\Program Files\Microsoft BizTalk Server 2006\Schema\Restore\SampleUpdateInfo.xml
     a. Do a “Replace All” and change SourceServer to DC1BTSQL1
     b. Do a “Replace All” and change DestinationServer to BTSQL1
     c. Uncomment the ruleenginedb node
     d. Uncomment the HWS Administration DB node
     e. Save it.
4) Drop to a command prompt and go to C:\Program Files\Microsoft BizTalk Server 2006\Schema\Restore
     a. Run cscript UpdateDatabase.vbs SampleUpdateInfo.xml
     b. Run cscript UpdateRegistry.vbs SampleUpdateInfo.xml
5) Restart Enterprise SSO Service
6) Restart BizTalk Service
7) Check BizTalk Configuration to verify that the database server is set to BTSQL1 for all configured options.
8 ) Rejoice.


One Response to 'Moving Microsoft BizTalk Server 2006 Databases to a new SQL Server'

Subscribe to comments with RSS or TrackBack to 'Moving Microsoft BizTalk Server 2006 Databases to a new SQL Server'.

  1. Tom said,

    on April 1st, 2008 at 1:51 pm

    I believe if you setup both machines with the same groups, accounts, and passwords (on each machine) that you could use a 2 server (BT node, SQL server) setup without a domain…that said, I’ve never actually tried it.

    For the sake of growth and ease of management, I wouldn’t do a multiple server setup without a domain, though.

Post a comment