How to move SQL Server from one computer/server/machine to another?

How to move SQL Server from one computer/server/machine to another?

Moving SQL Server from one computer to another is not a very difficult thing to do, but it often stumps newbie DBAs. Of course, it needs careful planning to ensure that the SQL Server is moved completely and properly to the new machine, and with a minimal downtime and no data loss. This article introduces you to a couple of methods you can employ to move/migrate SQL Server from one computer to another.

Before we get into the nitty-gritty, lets look at some reasons why one would want to move SQL Server from one computer to another? Here are a few:

• Number of users has increased and the current database server simply doesn't have the required processing capacity.
• You found out that the current database server is having hardware problems and is not reliable anymore.
• You are enhancing your database applications and adding more features, and require more processing power, but the current server cannot be scaled up anymore. Instead of scaling out, you decided to scale up, by moving to a powerful new server.
• Your infrastructure team came up with a new corporate spec, and you must upgrade your SQL Server to the new hardware specification.

Moving the entire SQL Server from one physical machine to another is different from just moving one or more databases. Moving an entire server means, moving more than just databases. You need to make sure the new server is configured with the same server level settings as the old server, and the new server must contain the same logins, role memberships etc. If you are interested in just moving databases, you could use backup/restore or detach/attach (using sp_detach_db and sp_attach_db) approach. You can find more information about moving databases, using the links provided at the end of this article. I also listed some useful books for DBAs.

For now we will concentrate on moving SQL Server completely from one machine to another.

Here are the steps involved in the process:

• Purchase a new server, with the required hardware configuration.
• Install the exact same version of Windows operating system on the new server.
• Make sure the new server has the exact same drive letters with at least same or more disk space, as the old server. Configure your RAID subsystem as needed.
• Take a note of the Windows service packs, Windows hotfixes and security patches that are currently installed on the old server, and apply them on the new server in the right order.
• Take a note of any specific configuration changes that were made to the old server, and configure the new server accordingly. For example, if the old server is using AWE memory and has a /3GB switch or /PAE switch in the boot.ini file, then you need to add those switches on the new server's boot.ini as well. Another example would be, the pagefile. Page file can be spread across multiple drives, with specific amount of storage on each drive. You must mirror those settings on the new server as well.
• Make sure the new server has the same number of (or more) network cards as the old server, and the network cards are configured optimally (for example, full duplex mode).
• Once the new server is at the same (or better) level as the old server, from operating system's point of view, start with SQL Server installation. Install the same version of SQL Server (I tested these steps on SQL Server 7.0 and SQL Server 2000 only. Most of it is applicable to SQL Server 6.5 as well).
• Make sure your new SQL Server is configured with the same authentication mode as the old server (Windows authentication (integrated security) or mixed mode).
• Take a not of service packs, security patches, hotfixes that are installed on the old SQL Server, and install them on the new SQL Server as well.
• Reboot the new server if necessary and make sure SQL Server, SQL Agent (and Microsoft Search) service are starting fine, without any errors.

You can perform the above steps much faster, if you have a corporate standard for installing and configuring SQL Server. Also, documentation of server settings is very important. Okay, now we have our new SQL Server ready. The next step would be to move the data, jobs, DTS packages, logins etc. to the new server. Moving all these objects individually would be very time consuming, as well as error-prone. If you can afford some downtime on your old SQL Server, follow these steps:

• Make sure no users are trying to connect to your SQL Server. Inform them well in advance about the downtime. Shutdown any applications or websites that might be accessing this SQL Server.
• Stop the SQL Server, SQL Agent and MS Search services on the OLD SQL Server.
• Stop the SQL Server, SQL Agent and MS Search services on the NEW SQL Server.
• Copy the data files and transaction log files from your old SQL Server's hard drive (for example D:\MSSQL\Data) to the new SQL Server's hard drive (to the same location, in this example, D:\MSSQL\Data). Since we are copying all database files, including the system databases (like master, model, msdb, distribution), your server level settings, jobs, DTS packages, alerts, operators, logins etc. will be copied across.
• Copy all the other folders (like backup, ftdata, jobs, log, repldata etc.) under the old server's MSSQL directory, to the same location on the new server.
• Copy any other folders or files from the old server to the new server, that will be accessed by your jobs, DTS packages, stored procedures etc.
• If you have any specific net library configurations, aliases, protocol binding orders on the old server, make sure you apply those changes to the new SQL Server, using the tools, Client Network utility and Server Network Utility.
• Take a note of any shares on the old server, and recreate them on the new server, with the same permissions and access rights to the relevant Windows accounts.
• Start the SQL Server, SQL Agent and MS Search services on the new server. Connect to the new server, using Enterprise Manager and make sure all the databases are displayed and that there are no suspect databases. Scan through the SQL Server error logs, SQL Agent error logs, System and Application event logs, to make sure there are no errors. If you find any errors, fix them before you proceed any further.
• Once you are happy with the new server's state, shutdown the SQL Server, SQL Agent and MS Search services.

Now its time to rename the new server as the old server, and remove the old server from the network. Consider that your old server is being called 'ServerA' and the new server we are building is currently named 'ServerB'. Follow these steps to swap the names of these servers:

• Rename 'ServerA' to 'ServerA_OLD'. You can do this by right clicking on 'My Computer', select 'Properties', go to 'Network Identification' tab. Also, remove this server from the domain, and place it in it's own workgroup. Reboot the server 'ServerA'. As we renamed it, it will now come up as 'ServerA_OLD'.
• On ServerA_OLD SQL Server, either disable all the jobs or disable the SQL Agent service. This is important, because, if the SQL Agent starts on this machine, it might kick off some jobs, that will interfere with other servers, or duplicate the work, that is already being done by our new server ServerA. If this situation is not taken care of, it will result in chaos and unpredictable results.
• Rename the new server 'ServerB', to 'ServerA' and make sure it is in the production domain. Reboot 'ServerB'. As we renamed it, it will now come up as 'ServerA' (the name of the original server).
• Connect to 'ServerA' using Query Analyzer and execute the command SELECT @@SERVERNAME. This will return the correct server name (if it didn't, then drop that server by running sp_dropserver and re-add the correct server name (ServerA), by running sp_addserver (Be sure to specify the 'local' parameter). For more information on how to drop and add the server names, read the article mentioned in the links section, at the end of this article.)
  

Now your new SQL Server is ready for use. Before you let the users start working with this server, perform testing and make sure the server is fully functional and all aspects of your production application are working fine. Once you are happy, you can let your users start accessing this new server. Since the name of the new server is now same as the old one, your applications will connect to the new server, without any problems. You don't have to reconfigure or change any DSN and connection string properties.

As you can see, it is not a very difficult task to move an SQL Server from one machine to another, but it requires careful planning. The only time consuming step in this process is the file copy. Copying databases that are several giga bytes (GB) or terra bytes (TB) in size from one machine to another over the network will be very time consuming, It can take a few hours or even a couple of days. The speed of file copy depends on the speed of your hard drives and your network bandwidth. Consider zipping the bigger files (using an application like WinZip or WinRAR) before copying. That could speed up the file copy over the network.

Though this method works well for mirroring SQL Server installation onto a new machine, it will require server downtime. If you are running a mission critical, 24/7 database and cannot afford any downtime at all, then you could follow the alternative approach described below:

• Follow the same steps as explained above, for building a new SQL Server from scratch.
• Copying database files requires you to shutdown the SQL Server. Since we don't want any downtime at all, forget about copying physical database files. Simply create backups of your static (read-only) databases and restore them onto the new server, using BACKUP and RESTORE commands.
• You cannot apply the above step for your ever changing OLTP database, because, by the time you restore the backup onto the new server, the old server would have received more changes. To avoid this problem, you need to resort to log shipping. Log shipping is nothing but an automated process of backup and restore. The following steps will explain the process of log shipping (Note that SQL Server 2000 Enterprise Edition provides inbuilt logshipping. We don't need that for this purpose. We can simply create a job, that backs up and restores the databases).
• Take a full database backup of your OLTP database from the old server, and restore it onto the new server. Make sure you restore using WITH STANDBY or WITH NORECOVERY option, as this allows us to restore further transaction log backups onto this database.
• Create a job on the old server, with two steps. The first steps backs up the transaction log of the OLTP database. The second step restores this transaction log backup file onto the new server, using WITH STANDBY or WITH NORECOVERY option of the RESTORE command. Schedule this job to run every 10 minutes or 15 minutes, depending on the volume of transactions your OLTP database receives.
• This job will take care of shipping all new transactions since the full database backup, to the database on the new server. Let this job run until you can afford some downtime on your production database (that is, mid-night, or early morning, or a planned maintenance window).
• Once you are ready to swap the servers, ask your users to logoff (if there are any) and shutdown any applications or websites that are currently accessing this database on the old server. To be doubly sure, no new transactions are coming in, set the database in 'single user' or 'dbo use only' mode. Take one last transaction log backup of the production OLTP database, and restore it onto the database on new server. This time use the WITH RECOVERY option of the RESTORE command.
• Now the OLTP database on the new server is completely in synch with the one on the old server. Its time to rename the servers now. Just follow the steps explained above (in the other approach), for renaming the servers.

As is evident, this method requires only a few minutes (5 to 10 minutes at the max) of downtime. The only time consuming processes in this approach are the "Amount of time it takes to create the last transaction log backup and apply it on the new server, the server renaming process (also depends on how quick you are on the keyboard:-), and the server reboot process".

Additional note about servers involved in replication topology: If the server you are moving is a distributor, then all replication agents (log reader, snapshot, distribution and merge) will be suspended, when you shutdown the server during the above process. The only side effect of this, would be the growing transaction log of the published database (as the transaction log cannot be truncated, until the replicated transactions are picked up by the log reader). If you have pull subscriptions, the pull distribution or merge agents will fail to connect to the distributor and you will see errors on your subscribers. These agents will most probably succeed (unless expired) when you restart them at the end of this process. If the server you are moving happens to be a publisher, just make sure you stop the replication agents related to this publisher, on the distributor and everything should be okay, once the new publisher comes back up. If the server you are moving happens to be a pull or push subscriber, make sure you stop the corresponding distribution agent, till the server swapping process completes.

How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000

To transfer logins and passwords from a SQL Server 7.0 server to an instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS Package Transfer Logins Task in SQL Server 2000. To do this, follow these steps:

1. Connect to the SQL Server 2000 destination server, move to the Data Transformation Services in SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.
2. After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information about the Source, Destination and Logins tabs as appropriate. Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under a Domain Account to complete the task. Note The DTS method will transfer the passwords but not the original SID. If a login is not created by using the original SID and user databases are also transferred to a new server, the database users will be orphaned from the login. To transfer the original SID and bypass the orphaned users, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.

Microsoft Help: