SQLServerCentral Article

Moving Your Database to a New Server


Time to Give Your Databases a New Home?

Do you have a server that is underperforming? Do you have end-users constantly reminding you that a server is slow at certain times of the day? Maybe the server has passed its used-by date and requires an overhaul, or maybe that you have a need to implement load balancing by having a second server (or a third). If this (or any other scenario that requires a replacement server) fits you then read on to find out what you need to consider and the approaches that are available to you to have your new server up and running in as little time as possible.

We will assume here that your aim is to have a replacement server, and that this server already has the OS, services and applications installed, which may or may not be an exact replica of the existing server. Therefore, what is left is the task of moving all relevant databases (and other SQL Server related objects) from one server to another.

Understanding What is Involved

Before delving into specifics, there are questions that need consideration upfront. The following is not meant to be an exhaustive list, but gives some idea as to what might be involved:

  1. What is your operation's system downtime policy?
  2. Is your database server also involved in replication (either as publisher or subscriber)?
  3. Is the move between same releases of SQL Server or not?
  4. Will the existing server continue to operate or be retired?
  5. Will the system databases be moved, in particular the master database?
  6. How should all this be implemented? Using scripts and/or SSIS packages?

Means of Approach

The fact that some operations are

12x7 has meant that system downtime is a premium that needs to be considered

carefully. The following discussion will principally be concerned with restoring

a database to another machine, and how quickly this can be achieved.

Most companies already have a

database maintenance plan setup that performs regular full and differential

(optional) database backups followed by frequent transaction log backups. Figure

1 shows a remote database restore process that makes use of the full and

differential backups only, making it applicable for all database recovery

models. Note the serial nature of the process and the sequence to be followed

by each individual task. Steps 1 and 4 are tasks to perform against the current

system, whereas steps 2, 5 and 6 are tasks to perform against the replacement

server. Steps 3 and 7 are dependent on your system downtime policy, which may (in

its simplest form) require all affected databases to be set in read-only mode.

The overall process execution

time will largely depend on the time taken to perform the differential backup

on the existing databases and the corresponding restores on the replacement

server. Therefore, the smaller the differential backup the shorter the

execution time. This can be achieved by performing the maintenance process as

close as possible to the completion of the full backup (shaded regions between

steps 1 and 4), thus reducing the size of the differential backup.

Figure 1. Restoring a database to a remote server using the full and differential backups.

In the event that transaction log

backups exist, then the approach would be to perform a NORECOVERY restore of

the full, differential (if available) and all transaction logs (in the

appropriate order) on the replacement server before the start of system

downtime, then after system downtime is initiated, perform a final transaction

log backup on the existing system and then to restore it on the replacement

server. As with the previous approach, the closer the transaction log backup is

to the completion of the differential backup, the quicker will be the backup

and restore process.

What happens if you have a

replicated database? Well, it depends on whether you are dealing with a

publisher or a subscriber. With the former, there is the issue of whether or

not the replacement server takes over completely and whether it will have the

same name as the existing server. The consequence being that replication may

need to be rebuilt on the publisher as well as the need to move the existing

server off the network (or offline it). This is a subject for another time, suffice

to say that your network admin staff would be help you with such matters.

In the case where you are only

interested in moving a subscription database, then the approach is much simpler.

The trick is to use a no-sync subscription (to be created at point B' in

Figure 1), particularly where very large tables are involved (There are caveats

to using a no-sync subscription, but nothing that a DBA cannot handle). A

prerequisite for using a no-sync subscription is to ensure that data in both

publisher and subscriber databases are in sync. That is, to ensure that all undelivered

commands in the distribution database have been delivered to the subscriber

before performing the final backup.

The following script (to be

executed at point A' in Figure 1) will help with this check, which displays

the number of undelivered commands in the distribution database for all

articles in all publications on that server.

use <publication database>
if object_id('tempdb..#dist_status') is not null 
 drop table #dist_status
select * 
into #dist_status
from distribution..MSdistribution_status 
select p.name as publication, a.name as article, t.*
from #dist_status t
left outer join sysarticles a
 on t.article_id = a.artid
inner join syspublications p
 on a.pubid = p.pubid
 where UndelivCmdsInDistDB > 0
 order by p.name,a.name,t.agent_id

Other Considerations

If you are migrating, say from

SQL Server 2000 to SQL Server 2005, then you will need to run the SQL Server

2005 Upgrade Advisor against your server to identify an incompatibilities that

will need taken care of beforehand. This includes DTS packages, whose meta-data

are stored in the msdb database. There may be a need to rework some of the DTS

packages before it will execute in 2005, otherwise look at migrating these

packages using the Package Migration Wizard in SQL Server 2005.

If you do not wish to restore the

system databases then there are other considerations for you to take note of.

One of the most important being the user logins. There are three approaches to

this, including (a) manually create the logins using the system stored

procedures; (b) use off-the-shelf scripts to generate the logins (e.g. http://support.microsoft.com/default.aspx?scid=kb;en-us;246133)

; or (c) use the SSIS Transfer Logins Task. Method (a) will require some

extra work to realign the SIDS for the logins, so the approach I would take is either

(b) or (c). Then, there are also linked servers, which are easy enough to

script out and apply.

Finally, there is a tool that can

help with putting your scripts and process together. SQL Server Integration

Services (or SSIS for short) is a powerful tool that can be used for such a

task. I used it in my last server migration project, and I can say that it

beats running scripts in QA (or the Management Studio equivalent in 2005). Once

you are familiar with the SSIS interface and know how to configure the various

components that you will be using, it is rather fun to design and to deploy.


In conclusion, the aim of this article is to provide you with some approaches that are available to you as you ponder the need to provide a new home for your databases. The focus of the discussion is more on the need to minimize the downtime to your production system. With some proper planning - and this involves doing as much of the work outside of the system downtime window - it is possible to achieve minimal impact to your production systems. Hopefully, there is sufficient information here for you to plan your next database-move project.


2.5 (2)

You rated this post out of 5. Change rating




2.5 (2)

You rated this post out of 5. Change rating