Migrating SQL Server Databases – The DBA’s Checklist (Part 1)
- by Sadequl Hussain
It is a fact of life: SQL Server databases
change homes. They move from one instance to another, from one version to the
next, from old servers to new ones. They move around as an organisation’s data
grows, applications are enhanced or new versions of the database software are
released. If not anything else, servers become old and unreliable and databases
eventually need to find a new home. Consider the following scenarios:
1. A
new database application is rolled out in a production server from the
development or test environment
2. A
copy of the production database needs to be installed in a test server for
troubleshooting purposes
3. A
copy of the development database is regularly refreshed in a test server during
the system development life cycle
4. A
SQL Server is upgraded to a newer version. This can be an in-place upgrade or a
side-by-side migration
5. One
or more databases need to be moved between different instances as part of a
consolidation strategy. The instances can be running the same or different
version of SQL Server
6. A
database has to be restored from a backup file provided by a third party
application vendor
7. A
backup of the database is restored in the same or different instance for
disaster recovery
8. A
database needs to be migrated within the same instance:
a. Files
are moved from direct attached storage to storage area network
b. The
same database is copied under a different name for another application
Migrating SQL Server database applications is
a complex topic in itself. There are a number of components that can be
involved: jobs, DTS or SSIS packages, logins or linked servers are only few
pieces of the puzzle. However, in this article we will focus only on the
central part of migration: the installation of the database itself.
Unless it is an in-place upgrade, typically
the database is taken from a source server and installed in a destination
instance. Most of the time, a full backup file is used for the rollout. The
backup file is either provided to the DBA or the DBA takes the backup and
restores it in the target server. Sometimes the database is detached from the
source and the files are copied to and attached in the destination.
Regardless of the method of copying, moving,
refreshing, restoring or upgrading the physical database, there are a number of
steps the DBA should follow before and after it has been installed in the
destination. It is these post database installation steps we are going to
discuss below. Some of these steps apply in almost every scenario described
above while some will depend on the type of objects contained within the
database. Also, the principles hold regardless of the number of databases
involved.
Step
1: Make a copy of data and log files when attaching and detaching
When detaching and attaching databases,
ensure you have made copies of the data and log files if the destination is
running a newer version of SQL Server. This is because once attached to a newer
version, the database cannot be detached and attached back to an older version.
Trying to do so will give you a message like the following:
Server:
Msg 602, Level 21, State 50, Line 1
Could
not find row in sysindexes for database ID 6, object ID 1, index ID 1. Run DBCC
CHECKTABLE on sysindexes.
Connection
Broken
If you try to backup the attached database
and restore it in the source, it will still fail.
Similarly, if you are restoring the database
in a newer version, it cannot be backed up or detached and put back in an older
version of SQL. Unlike detach and attach method though, you do not lose the
backup file or the original database here.
When detaching and attaching a database, it
is important you keep all the log files available along with the data
files. It is possible to attach a database without a log file and SQL Server
can be instructed to create a new log file, however this does not work if the
database was detached when the primary file group was read-only. You will need
all the log files in such cases.
Step
2: Change database compatibility level
Once the database has been restored or
attached to a newer version of SQL Server, change the database compatibility
level to reflect the newer version unless there is a compelling reason not to
do so. When attaching or restoring from a previous version of SQL, the database
retains the older version’s compatibility level. The only time you would want
to keep a database with an older compatibility level is when the code within
your database is no longer supported by SQL Server. For example, outer joins
with *= or the =* operators were still possible in SQL 2000 (with a warning
message), but not in SQL 2005 anymore. If your stored procedures or triggers
are using this form of join, you would want to keep the database with an older
compatibility level. For a list of compatibility issues between older and
newer versions of SQL Server databases, refer to the Books Online under the sp_dbcmptlevel
topic.
Application developers and architects can
help you in deciding whether you should change the compatibility level or not.
You can always change the compatibility mode from the newest to an older
version if necessary. To change the compatibility level, you can either use the
database’s property from the SQL Server Management Studio or use the sp_dbcmptlevel
stored procedure.
Bear in mind that you cannot run the built-in
reports for databases from SQL Server Management Studio if you keep the
database with an older compatibility level. The following figure shows the
error message I received when trying to run the “Disk Usage by Top Tables”
report against a database. This database was hosted in a SQL Server 2005 system
and still had a compatibility mode 80 (SQL 2000).
Continues…
