In the past two installments of this series I’ve discussed two key aspects of moving a SQL Server database from one server (or instance) to another. First I covered the advantages of designing databases to make them easier to move, and then I looked at the methods that you can use to move a database. Now it’s time to look specifically at the types of database dependencies that you must account for after you move a database.

A good way to look at the kinds of dependencies an individual database may have is by starting from the database itself and working outwards. In this article, I’ll show you how to build up a checklist that you can use for building your own checklists for database migrations.

Why Include Dependencies?

Moving a database is an inevitable task. The scenarios are all familiar. Some common reasons you may need to move a database include the following:

  • Upgrading hardware to a new server.
  • Upgrading the Windows operating system to a new version (requiring a server rebuild).
  • Upgrading to a new version of SQL Server on a new instance.
  • Building a fully functional test, QA, or development version of the database.

Whatever the reason, eventually you have to move most databases to a new server. But moving a database to a new server does not end with copying and then restoring or attaching it to the new server. Just moving a database to a new location may not leave it in a functional state.

In order for the moved database to be functional, you have to move many of its dependencies. In fact, to be fully functional, you may need to transfer all its dependencies to the new location. In this article, when I refer to a database’s dependencies I mean those components the database depends on, as well as components that depend on the database.

For those databases that are completely self-contained and do not require any references outside themselves, moving them from one server to another is easy. But even in that case, as you’ll see in the next section, you may need to take implied dependencies into account.

Completely self-contained and isolated databases are the exception. The majority of production databases, especially the larger and more complex ones, depend on application components or other SQL Server features outside the database.

Building a List of Database Dependencies

To start the list, I’ll provide some suggestions to help you distinguish the self-contained data in a database and then work outwards, step-by-step, iterating the types of database dependencies that you need to note.

Dependencies Related to Security

I’ll define a self-contained database as one that does not contain, nor require, any references to other data or applications outside itself. Given the architecture of SQL Server, even the most self-contained database has two potential dependencies that you need to make note of, both related to database security.

The first type of database dependency is ubiquitous: it’s present in every database that follows general best practices regarding database security. This type of database dependency is mapping an internal database user to its SQL Server login. It arises because of the way SQL Server handles authentication and access (permissions). Any user who wants access to a database must first log into SQL Server using a valid login. Then to access the database, the login must correspond to a user in the database. The database username normally just matches the login name, and has the same SID (system ID). Now if the SQL Server employs Windows-only authentication, that login will be a Windows account, and if the database has been moved to a new location on the same domain, the SIDs will match up automatically. But if the SQL Server uses mixed Windows and SQL Server authentication, a SQL Server login will have a SQL Server-generated SID that exists in both the master database and the database that you’re moving. So when you move the database, you need to ensure that the database user names have SIDs that will match up properly in the new SQL Server instance. Microsoft provides the system stored procedure sp_change_users_login for just this purpose. (For more details, see "Managing Logins and Jobs after Role Switching" in SQL Server 2005’s Books Online.)

The second type of dependency is database ownership. Every database must be owned by some login. Most DBAs try to make each database owned by the sa (SQL Server system administrator login) login, even when the SQL Server uses Windows-only authentication. Having the database owned by sa is better than being owned by some Windows account, because the sa login is always around, whereas a Windows account, even the SQL Server service accounts, could eventually change. This is usually a minor dependency, just something you need to watch for, because when you move the database to a new server, by default it will be owned by the login of the user who restores or attaches it.

Dependencies on Other Databases

A SQL Server database normally has all its data self-contained. What I mean is that for tables in that database, all indexes, foreign keys, and other types of constraints that can apply to that data will be contained in the same database and move along with it. This even includes CLR assemblies that have been compiled in a SQL Server 2005 database: their compiled images are contained in the database and move along with it. Transact-SQL code in the form of triggers and stored procedures is also stored in the database.

However, a database can contain references to other local databases (i.e., databases on the same SQL Server instance), which creates another type of dependency. In some cases, queries embedded in one database may need to make reference to tables in another database on the same server. SQL Server has a simple mechanism for referring to other databases on the same server: all you need to do is prefix the object name (usually a table) with the database and schema name. Here’s a simple example:

USE master
GO
SELECT COUNT(*)
FROM AdventureWorks.HumanResources.Department

The example refers to data in the AdventureWorks database from master, but it could be any database. As soon as a database makes references in its Transact-SQL code, or its CLR code, to other local databases, a new type of dependency has arisen.

This type of dependency, references to other databases, works both ways: the database you’re moving may refer to other local databases and other local databases may refer to it. If you move the database to another server, you’ll need to account for any of those references and reproduce them properly on the new server. This may require that you move several databases together, if you move any at all.

In addition, a database may have dependencies on remote databases (i.e. databases on other SQL Servers). These are implemented as linked servers (the exclusive mechanism for referring to remote servers in SQL Server 2005). If the linked server is defined permanently on a SQL Server instance, then you can just prefix remote database object names with the linked server name before the database name, as in the following snippet:

USE master
GO
SELECT COUNT(*)
FROM SQL02.AdventureWorks.HumanResources.Department

In this case, SQL02 is the remote server that has the AdventureWorks database you want to query. Most often, you’ll find references to linked servers in the code (Transact-SQL or CLR) embedded in the database. You can just recreate the linked server with the same name on the new SQL Server that you’re moving the database to, in order to duplicate the dependency.

SQL Server 2005 uses linked servers for replication, though you won’t necessarily see them in any database code. Replication presents a special case because its configuration is complex and when you configure replication, SQL Server marks the objects in a database that are being replicated. For moving a database, if it is a publisher, it’s much easier to remove replication from the database and then set up replication again on the new instance. The important point to note is that if the database you are moving is replicated you have additional external dependencies to address.

External File References

Another type of dependency consists of references to external files. It’s quite common for databases to associate pictures, images, documents, etc. with rows in some tables. The actual pictures may be stored externally as files on disk, usually on some kind of share, and the database actually just contains the filenames so that an application can find the files on a file server. It’s important to make sure after you move a database to a new location that your database still has valid file references.

Dependencies on Applications

You may find several types of dependencies related to applications. Most commonly you’ll have SQL Agent jobs that depend on the database, and that the database may rely on for maintenance. Some of these jobs may be critical for the application to function so you must also move them to the new server with the database you are moving.

You may also have Transact-SQL scripts located on the server, invoked perhaps by SQL Agent jobs that you also need to move.

Another common type of application dependency is on SQL Server Integration Services (SSIS) and/or Data Transformation Services (DTS) packages. If these are stored internally, they will be a part of the msdb database, along with SQL Agent jobs. If these packages are stored externally on disk, you’ll also need to copy them to the new server.

If you are moving just one database to a new server, you may need to pick out the relevant jobs and packages and recreate them on the new server. In the simple case where you only had one database on the SQL Server instance to begin with, you may just be able to backup the msdb system database (which contains SQL Agent jobs and schedules) and restore it on the new server.

Another type of dependency is on calls to the Windows command shell. SQL Server allows you to do this using the extended stored procedure xp_cmdshell. If you find references to using xp_cmdshell in your Transact-SQL code, or in your SQL Agent jobs, you’ll need to ensure that xp_cmdshell is enabled on the server to which you are moving the database.

Dependencies on SQL Server Configurations

The last type of dependency I’ll discuss is based on SQL Server configurations. This breaks down into two specific areas. First of all, for the moved database to function properly with its applications, you may need to do some minimal hardware configuration, providing a minimal level of CPU, memory, and disk I/O.

Secondly, the original SQL Server instance may have had specific SQL Server-level configurations that you must reproduce on the new server. These also may include CPU and memory settings, but much more. You can visually inspect these settings using SQL Server Management Studio. Look in the Properties dialog box of the original SQL Server instance, or you can get a list using the Transact-SQL sp_configure stored procedure. The best way to approach this is to look for any SQL Server configurations that are different from the default, and then analyze each one you find to determine whether you must also set a similar configuration on the new server.

Conclusion

Once you’ve moved a database to a new server, you must also bring over all the required database dependencies so that the database will function properly with its applications. In this article we’ve identified the following list of types of dependencies:

  • Security:
  • - Mapping an internal database user to its SQL Server login
  • - Database ownership
  • Other Databases
  • - References to other local databases
  • - References to remote databases (linked servers and replication)
  • External Files
  • Applications
  • - SQL Agent Jobs
  • - Transact-SQL scripts
  • - SSIS packages
  • - Calls to the Windows command shell
  • Configurations
  • - Server hardware (CPU, memory, disk I/O)
  • - SQL Server configurations

Using this list, you can expand the detail to create a checklist for moving a database from one server to another. The more complex the move is, based on the number and types of dependencies you have to include, the more helpful a checklist will be.