Published on

April 7, 2008

Preparing to Move a SQL Server Database

One of the challenges that organizations face is the ability to handle change with minimal disruption. Moving a database is one such change that requires careful planning and execution. Whether you need to move a database to a new server for better performance, increased storage capacity, or simply to modernize your infrastructure, being prepared can make the process smoother and less risky.

1. Moving Logins

Before moving a database, it is important to ensure that logins and passwords are transferred seamlessly. Microsoft provides a helpful article on how to move logins and passwords without having to run the sp_change_users_login procedure once the database is moved. Familiarize yourself with this process and keep the login mover script handy.

2. Handling Data and Log Files

If your organization has not standardized drive letters across the enterprise, you may need to change the location of the data and log files when moving a database. The easiest way to do this is by detaching the database, copying the files to the new location, and then reattaching the database. If you are using a backup to move the database, make sure you know how to move the files during the restore process.

3. Replication Considerations

If the database is replicated, additional steps need to be taken. Determine whether the new server will also serve as the distributor or if the original server will continue to handle replication. Remember that you cannot detach a replicated database; you must remove replication first. However, the subscriber will retain all the data, so with some effort, you can avoid taking a snapshot. It is recommended to take a snapshot during a server move to ensure that everything works as expected.

4. Linked Servers and Cross-Database Queries

If your database uses linked servers or three-part syntax to reference other databases on the same server, you will need to transfer the linked servers to the new server. Alternatively, you can replicate the necessary data to the new server, which eliminates the need for cross-server queries. Standardizing your code to use two-part syntax, views, or synonyms can simplify this process and reduce the need for extensive code changes.

5. Maintenance Plans and Jobs

If your organization uses a standard maintenance plan for all databases, you will need to remove the database from the original server’s plan and add it to the new server’s plan. Additionally, identify any DTS/SSIS packages that are dependencies and make the necessary tweaks to run them on the new server. Don’t forget to review and update any business process jobs that run via SQL Agent or other task schedulers.

6. Updating Applications

After moving the database, you will need to update your applications to point to the new server and database. This may involve changing connection strings in web.config files for ASP.Net applications or updating client applications. Consider the time and effort required to repoint all applications and establish a plan for testing the functionality of each application on the new server.

7. Testing and Monitoring

Testing is a crucial step in ensuring that the move was successful. Start with basic tasks and gradually move to more complex workflows. Monitor the event log and application-level logging output for any errors or issues. It is recommended to have regular users, including those in non-standard roles, test the system for an extended period. Remain vigilant for any external processes that may fail during the first week after the move.

By following these steps and implementing good policies, you can significantly reduce the risks and challenges associated with moving a SQL Server database. Remember, preparation is key to a successful database move.

For more SQL Server-related topics and discussions, visit my blog at http://blogs.sqlservercentral.com/blogs/andy_warren/default.aspx. Feel free to leave comments and engage in the conversation!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.