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?
- 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
- 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
during a restore.
- 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
- 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.
- 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
- 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.
- 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.
- Update your documentation with lessons learned from the move, and also
update your performance baseline on both servers.
- 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?
- 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.
--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 asselect * 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!
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
SQLServer, SQL user groups, and related topics. I hope you'll visit and comment