SQLServerCentral Article

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


  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


  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.

  5.     --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!

  6. 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

  7. 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.

  8. 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.

  9. Update your documentation with lessons learned from the move, and also

    update your performance baseline on both servers.

  10. 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?

  11. 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



3.96 (24)

You rated this post out of 5. Change rating




3.96 (24)

You rated this post out of 5. Change rating