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:
- What is your operation's system downtime policy?
- Is your database server also involved in replication (either as publisher or subscriber)?
- Is the move between same releases of SQL Server or not?
- Will the existing server continue to operate or be retired?
- Will the system databases be moved, in particular the master database?
- 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>
go
if object_id('tempdb..#dist_status') is not null
drop table #dist_status
go
select *
into #dist_status
from distribution..MSdistribution_status
go
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.
Conclusion
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.
