Moving Databases

One of the ways that I measure the maturity of an organization it it's ability to handle change with a minimum amount of pain, and one good example is moving databases. It's not every day you need to move a database, but it's not uncommon either - sometimes because you need more space, need to reduce the load on the server, move the database to a server with more power, or maybe just migrate from that old NT4 box in the corner of the server room. So how prepared are you to move a database, right now?

1. First, you need to know how to move logins. I won't go into details here, but read this MS article on how to move your logins & passwords without having to run sp_change_users_login once you move the database. This is a 'I know how' step, nothing to really prepare for other than having the login mover script handy.
2. If you haven't standardized your drive letters across the enterprise (I use Z: for data, Y: for logs, X: for backups) you need to be ready to change the location of the data/log files. Easiest way is detach, copy to whatever new location, reattach. If you're using a backup, be sure you know who to move files during a restore.
3. If the database is replicated you definitely have to do more work. Will the new server also serve as the distributor, or will the original server continue to be the distributor? Remember that you can't detach a database that is replicated, you have to remove replication first. The subscriber will still have all the data when you finally get everything moved again so with some effort you won't have to do a snapshot (though on a server move I'd recommend it if at all possible so that you know it will work later if needed).
4. Are you using linked servers in the database? Or three part syntax to reference other databases on the same server? The linked servers already used will need to be transferred, and you'll have to create (if not already there) a linked server back to the original to support the cross database queries - OR - replicate the data you need to the new server. The latter solution is actually fairly elegant, it saves time finding/changing all those references and it gives you a local copy of the data, which means better performance and no cross server dependency. You can see that this is the first step where preparation can make a lot of difference. If you (or your developers) have embedded 3 or 4 part syntax (example below) you're potentially going to have to find/change/test all of them, including those that might be embedded in DLL's/EXE's used by the application.

    --three part syntax, we're in the Corp database, if we move we have to either have the Adventureworks database, or change the reference
    Select * from Adventureworks.person.contact where lastname='smith'

    --four part syntax, getting data from another server, this will work fine as long as the new server has a linked server called SERVER12 pointing to the correct server.
    select * from Server12.Adventureworks.person.contact

        The right way to handle this it to require two part syntax everywhere, using either views or synonyms to provide the needed abstraction. For example, in our three part syntax example above     we just do this:

create view vAdventureWorksPersonContact as

select * from AdventureWorks.person.contact

Which changes our code to:

Select * from dbo.vAdventureWorksPersonContact where lastname = 'smith'

Synonyms go a step deeper in that you can use them for stored procedures and functions in addition to tables. Regardless of technique, but abtracting those external references you know that you only have to change them, no further looking is needed if...if... you've applied a stern hand as changes are deployed!

5. If you're using the standard all databases maintenance plan you will need to remove the db from the original server plan, and make sure it gets added to the new server maintenance plan
6. The database may have DTS/SSIS packages that are dependencies. Those can be run on the original server with typically a few tweaks, or moved to the new server, again with a few tweaks. Pitfalls to watch for are missing or incorrect persmissions on file shares on the new server.
7. There are also usually business process jobs that run via SQL Agent (but could be Windows Schedule or other tasker), identify all of those and move them to the new server/disable on the old one. This can easily mean inspecting every job to make sure.
8. Update your documentation with lessons learned from the move, and also update your performance baseline on both servers.
9. How will you update your applications to point to the new server? DNS changes won't work here since we're only moving part of a server, same problem with SQL Server aliases. ASP.Net apps are the easiest to change because the connection string is in web.config, but what about client apps? How quickly/easily can you repoint all of them to the new server & database?
10. How are you going to test that all is well? Few of us have the kind of rigorous test suite we wish we had, so assuming you don't have that, how do you establish a comfort level? Simple testing involves logging in to the application and doing a few basic tasks first (make sure the database on the original server is marked as offline!), then move into some of the more complex work flow. Monitor the event log or other application level logging output, and supplement with Profiler set to catch errors as well. Having a couple 'regular' users work the system for an hour or so is a good test, and don't forget to include someone in a non-standard role (manager or lead) as well once the regular users are ok. That still leaves the danger that some external processes will fail, so vigilance for a full 7 days is required.

I'm sure there are other steps I haven't listed, but you can see that moving is not trivial. In fact, it's often risky enough that it makes sense to add space/memory/CPU capacity to the box to avoid the move, or to replace the entire server to accomplish the same. But at the same time I hope you'll realize that as a DBA you can do a lot to make those moves go faster and with less risk if you think about the problem now and put some good policies in place, especially for steps 4, 5, 6, and 7.

I blog  at http://blogs.sqlservercentral.com/blogs/andy_warren/default.aspx about SQLServer, SQL user groups, and related topics. I hope you'll visit and comment occasionally!