Moving the BizTalk databases

This used to be a big issue at my old blog, but thanks to that, and also the wonderful documentation and content providers at Microsoft, the old article that was criticized has been updated. They got the number of steps down and there is clarity about the whole 32 vs 64 bit issue. More on that later.

Preparations

Before you begin you have get a couple of things done. Get a person who knows SQL server and has access rights to everything you need on both machines. On an enterprise level this is usually not the BizTalk admins, nor the BizTalk developers. The person needs to be SQL Server Sysadmin. Plan the outage! In our case we were lucky enough to get a full week between two testing stints. Set aside a day in which the platform is completely out. Plan the backups! Let’s say you get what I got: The backups run once a day at 3am. Therefore nothing may enter or leave the platform after 3am. You need that backup to be 100% compatible with a fallback (retreat?) scenario. More info on backing up your databases can be found here. If you are using BAM there might be activities that starts before the database move and they need to be completed manually. There is a script for that. Get a txtfile and paste the names of the source and destination servers and everything else you might find useful. Read thru the article by Microsoft just to see what you are expected to do, and what you might need to ignore.

Custom stuff

Are there any custom databases or custom components that might be affected by the database move? If you have custom databases, you might want to move them as well and if you have custom components for posting to BAM or some added functionality, make sure that they do not use hard coded connection strings or simply update anything pointing to the old database server.

32 vs 64 bit how?

Imagine you are on a 64-bit machine. It should not be hard to do if you have any contact with BizTalk. If you run the cmd-tool by using Run+”cmd”, you get the 64 bit version BUT its path points to the “System32” folder. To make things even more confusing, the 32 bit version of the cmd tool is in the SysWOW64 folder. Many people like me just assumed that the message “make sure you use the 64-bit version” was to run the one in the SysWOW64 folder. Which was wrong, which caused all sorts of issues, which prompted me to write the original post. That is now resolved. So make sure you are using the correct version. If you by any chance are running BizTalk on a 32-bit machine, you do not need to move the databases. You need to upgrade my friend! The article by Microsoft is now 99% there and you really should follow that one to the letter except for a two things.

Wrong config if you use EDI

If you plan to move the EDI-functionality you need to add a node to the SampleUpdateInfo.xml file. You are supposed to add the following to the “other databases” node.

<Database Name="MsEDIAS2" oldDBName="old dta db name" oldDBServer="old dta server" newDBName="new dta db name" newDBServer="new dta server" />

The thing is that this will not work with the scrip for updating the registry. Open the file UpdateRegistry.vbs and go to line 131. It says to look for an attribute called EDI. If it finds this attribute, the EDI settings will be updated. However as the guide says you should update your SampleUpdateInfo.xml to include an attribute called MsEDIAS2. Update the line in the script to:

set node = configObj.selectSingleNode("/UpdateConfiguration/OtherDatabases/Database[@Name='MsEDIAS2']")

Update SQL server scripts by hand

It is simple to move the BizTalk SQL Server jobs but there are some addendums to the article.

  1. BizTalk Backup has a database server name in it.
  2. Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb also has a server name in it.
  3. Rules_Database_Cleanup_BizTalkRuleEngineDb.sql run this when/if the Rule engine DB has been created on the new server.
  4. TrackedMessages_Copy_BizTalkMsgBoxDb.sql also has a server name in it.

BAM databases and other BAM components

If you are using BAM in your environment, good for you. Be sure to follow this article to the letter after moving the databases and running the scripts. Everything is there but I would just like to point out some things you need to have in mind.

  1. Do not forget to move the SSIS packages for BAM DM and BAM AN. It is simple and can be done using SQL server management studio.
  2. Do not forget that you might need to update all your BAM components, like if you are running the BAM portal.

Closing words

Remember: This is not really all that hard and if you practice you can move an entire production environment in under an hour.