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!