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.
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.
Not sure that this is sufficent to handle moving all the biztalk stuff from 1 server to another.
try this method twice and had to change stuff manually in the administrator GUI.
I’ve done this a few times and haven’t had to change anything in the admin interface. What is it that you’re changing in the admin UI?
I did it again on another server and it was better. but it’s still not as simple as stated. I didn’t build these biztalk servers, so I’m guessing they weren’t built properly. working in the dark, but using your scripts and some manual tweaking I can get it working on a new server.
Having just migrated our BizTalk 2006 installation to a new SQL Server, I wanted to mention another migration option. I was hesitant to use the vbs scripts provided in BizTalk because I didn’t have confidence it would capture some unique things we have going on with our installation, such as 3rd party adapters and EDI and such. Instead, I took the approach of making sure I gave the new SQL server the same name as the old SQL server. By doing this, I was able to avoid having to make any configuration changes to the BizTalk server. Simply moving all the relevant databases, jobs, and users to the new SQL server and making sure the DNS entries were updated was sufficient.
This wouldn’t be a practical option in all scenarios, but if circumstances allow for it, I think this is easier and, IMO, safer than relying on the vbs scripts.
Please tell me which 4 database are by default present with biztalk server 2006.