The correct title might be: “Moving the BizTalk databases – The right way”. Because the way outlined in the BizTalk help, is not much help. Scared yet? Well not after reading thru my articles.
Why move databases?
This is a very natural question and the answer is very simple: Because you have to. At some time you might need to move databases around in a big datacenter or, as for me, you need to move to a default instance on a new server (remember kids BAM needs SSIS to work properly and it will not on a named instance).
Is it possible?
Yes, very. There is even a script that does a lot of things for you but there are a lot of manual steps you need to take.
The scenario
So in order to make these articles a bit more texbook-like I will describe a scenario that I went thru.
The object of moving the databases was that the customer wanted one of their environment to be less of a testing environment and more like a staging environment. The BizTalk installation was setup on an SQL server machine that was used for patch testing and such. Not a good base for a staging environment.
I moved the databases form a working environment into a new server. The BizTalk databases did not exist before the move (this is useful to know if you plan on doing a log shipping database move, because this is not).
The BizTalk machines (this is a clustered environment) stayed the same, only the databases were moved.
The two BizTalk machines are called BizTalk001 and 002. They are a BizTalk cluster but if you do not have a clustered environment; just ignore everything I write about that.
The old SQL server is called PhyBizTalkDb and the databases to be moved are installed under the BizStage named instance.
The new SQL Server is called VBizTalkDb and the BizTalk databases will be installed at the default instance.
Microsoft support and disclaimer
In no way would I undertake this feat if Microsoft did not support it, and they do. You can find the quite extensive article here. I will refer to this article so keep it handy.
The next thing to understand is that in no way can you make me liable for anything you do to your BizTalk environment based on what you read here.
I was fortunate enough, as well as prepared enough, to test the whole scenario in another environment before heading into the scenario above. I highly recommend you do the same. Just see to that the BizTalk and SQL Servers are on different machines, otherwise you don’t get any practice.
Preparation
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.
- 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! Lets 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.
- Script all the BizTalk SQL jobs to files and store them securely.
- Script all the BizTalk SQL users and store them securely as well.
- 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.
More to follow.
Blog Post by: Mikael Sand