How to Migrate an Instance of SQL Server 2008 to a New Server

The following is a plan I recently used when we needed to migrate an instance of SQL Server 2008 to another server.  I am posting it on the internet to help anyone else in a similar situation. 

Our migration went pretty well all things considered.  I forgot to include a step to set up the services to use domain level service accounts so we had to scramble a bit to track down those passwords.  That was minor compared to the other issue…

An even bigger gotcha occurred when we attempted to throw a huge load at the new server.  Once the machine went above 50% CPU everything freaked out.  We couldn’t even access the database through SSMS and were forced to reboot.  The event log had the following error:  “There is insufficient system memory in resource pool ‘default’ to run this query.”

It was a real nail-biter and we even discussed the possibility of a rollback.  One of the guys on our team did some Googling and thought the problem might be that SQL Server 2008 only supports up to 64 logical processors and we had 80 on the new server.  We had two choices at that point: upgrade to R2, which does support more processors, or turn off hyper-threading to reduce our logical processor count.  We chose to disable hyper-threading because it introduced the least amount of risk and that seemed to do the trick. 

In hindsight, we should have tried to throw a big load at the server during testing; but, we are a small team with loads of responsibilities.  All things considered I thought we did a great job with the resources we had.  We met our objective with-in the required time period.

 

Scenario 

 

A more powerful server has been acquired and we want to move a production instance of SQL Server 2008 to the new server.  In our environment, the SSRS, SSIS, and SSAS instances are on separate servers.  Some of our databases are used by a public internet website.  We intend to copy the instance to the new server, rename the old server, and then rename the new server to what the old server had previously been named. 

Technical Considerations

1.       Is Encryption being used on any of the databases?  First, ask everyone.  Second, check for yourself to be sure.  If it is being used you will probably need to transfer the keys.

a.       Three Types

                                                               i.      Cell-level encryption

I believe this query will return more than one row if any of your databases are using cell-level encryption:

SELECT  * FROM    sys.symmetric_keys

http://johnnycode.com/2012/02/09/sql-server-cell-level-symmetric-encryption-the-right-way/

                                                             ii.      TDE (Transparent Data Encryption)

If this query doesn’t return any results you are probably not using TDE:

SELECT db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys

                                                            iii.      Windows File Level encryption

1.       Click Start, Run, and type SECPOL.MSC

2.       Expand Public Key Policies

3.       Click on Encrypting File System

4.       If it says “No Encrypting File System Policies Defined” than the instance is not using an Encrypting File System (EFS.)

2.       Is SQL Server instance on source server at the same patch level as the target?  The version number is displayed next to the instance in Management Studio.

3.       In our case, we intend to backup, copy, and restore one database, and then detach, copy, and attach the other databases.  The advantage of the detach/copy/attach method over the backup/restore method is that it will be faster to copy large files across the network than to wait for the backup/restore operations to complete.

4.       We have decided NOT to attempt to migrate any of the system databases (master, model, msdb.)

5.       We will NOT be migrating maintenance plans and intend to recreate them on the new server.

6.       Is Database Mail enabled on the old server?  If so, you will want to enable it on the new server and set it up the same way.

7.       Two SQL Agent Jobs need to exist on the new server.  One will be created as part of the creation of the maintenance plan; the other is the syspolicy_purge_history job.

syspolicy_purge_history  What is it?

http://sqlserverpedia.com/blog/sql-server-bloggers/syspolicy_purge_history-what-is-it/

8.       The new server should have the same number of hard drives mapped to the same letters as the old server.  The databases and log files will reside in the same locations on the new server as they were on the old server.

9.       Are there any shared drives that need to be recreated on the new server?

10.   Are there any proxy accounts that need to be recreated on the new server?

11.   You should enable the “Lock Pages in Memory” security right in Windows to your SQL Server Service account if you are on Enterprise edition.

Enable the Lock Pages in Memory Option (Windows)

http://msdn.microsoft.com/en-us/library/ms190730.aspx

12.   Set the max memory size for SQL Server.

Suggested Max Memory Settings for SQL Server 2005/2008

http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

Testing

1.       Test detach/copy/attach method on Development server

a.       Can development website access the databases as expected?

b.      Can the ETL processes push data into the server?

c.       Can the SSRS Server read data from the server?

d.      Can the SSAS Server read data from the server?

e.       Simulate a heavy workload

2.       Test the backup/copy/restore method on the Development server

a.       Can the ETL processes push data into the server?

Preparation

1.       Script resource pools from old server and recreate on new server.

a.       Script resource pools from old server.  SSMS, Management, Resource Governor

b.      Execute script on new server

c.       Setup resource governor using function

2.       Script SQL Agent operators from old server and recreate on new server.

a.       Script SQL Agent operators from old server.  SSMS, SQL Server Agent, Operators

b.      Execute script on new server

3.       Document maintenance plans on old server

a.       SSMS, Management, Maintenance Plans

b.      These are actually SSIS packages on the server.  You could export the packages and script out the SQL agent jobs; but, they wouldn’t appear under the Maintenance Plans.

4.       Document domain level account user ids and passwords that are used to run the SQL Server Services.  (Administrative Tools, Services. SQL Server & SQL Server Agent.  Log On As.)

Deployment

1.       Script logins from old server

                                 i.      How to transfer logins and passwords between instances of SQL Server http://support.microsoft.com/kb/918992

                               ii.      Follow the instructions from the link above to create a script from the old server.  Take the resulting script and save a copy.  Then either:

1.       change everyone’s default database to MASTER (change it back to what is was after all databases have been re-attached or restored using SSMS)

2.       OR separate the logins by the default database.  You only want to create the logins after the database exists otherwise the creation will fail.

                              iii.      Script Server Permissions and Role Assignments from old server and execute on new server. 

http://www.kendalvandyke.com/2009/01/scripting-server-permissions-and-role.html

2.       Disable SQL Agent jobs on old server and the ETL Server

3.       Disable external processes that access the old server

4.       Backup the single database in which we are using the backup/copy/restore method.

5.       Restore the single database in which we are using the backup/copy/restore method.

6.       Verify restored database is accessible.

7.       Disable website access.

8.       Detach all databases from the old server (except system databases.)

9.       Copy detached databases and log files from the old server to the new server using the same file paths.

10.   Attach all databases on the new server.

11.   Logins and permissions

a.       Execute Logins script

b.      Execute roles and permissions script

12.   Rename old server

13.   Rename new server to same name as old server

14.   Set up the domain level services accounts using the user-ids and passwords that were documented (administrative tools, services.  SQL Server & SQL Server Agent.  Log On As.)

15.   Verify that everything is working properly. 

a.       Website

b.      ETL processes

c.       External processes

d.      SSRS

e.       Heavy workload

16.   Checkpoint:  If everything is not working properly decide if there is time to diagnose issue otherwise, begin rollback procedures.

17.   Enable SQL Agent on SSIS server

18.   Enable external processes

19.   Create Maintenance Plans on new server

20.   Run any jobs missed during migration.

 

More Information

How can I copy a SQL Server 2008 instance to another server? *Missing step for roles and permissions*

http://serverfault.com/questions/202210/how-can-i-copy-a-sql-server-2008-instance-to-another-server

Copy Database from Instance to another Instance – Copy Paste in SQL Server

http://blog.sqlauthority.com/2011/05/27/sql-server-copy-database-from-instance-to-another-instance-copy-paste-in-sql-server/

Moving SQL Server 2008 R2 to another Machine

http://www.sqlservercentral.com/Forums/Topic1130455-1550-1.aspx#bm1130572

Moving System Databases *Not Recommended *

http://msdn.microsoft.com/en-us/library/ms345408(v=sql.100).aspx

Moving User Databases

http://msdn.microsoft.com/en-us/library/ms345483(v=sql.105).aspx

Move SQL Server Instances from one data center to another data center

http://social.msdn.microsoft.com/Forums/zh/sqldatabaseengine/thread/96dc2632-6e27-4ba2-8ea8-64c2eec4f5fe

Maximum Number of Processors Supported by the Editions of SQL Server

http://msdn.microsoft.com/en-us/library/ms143760(v=sql.100).aspx

How many logical processors does SQL Server 2008 R2 Enterprise Edition support?

http://blogs.msdn.com/b/saponsqlserver/archive/2011/04/18/how-many-logical-processors-does-sql-server-2008-r2-enterprise-edition-support.aspx

 

Related

How to: Migrate a Reporting Services Installation

http://msdn.microsoft.com/en-us/library/ms143724(v=sql.100).aspx

How to Transfer (Copy) Maintenance Plans from One Server to other

http://sqlbuzz.wordpress.com/2011/08/27/how-to-transfercopy-maintenance-plans-from-one-server-to-other/

How to move SSAS from one server to another

http://www.sqlservercentral.com/Forums/Topic1223397-17-1.aspx#bm1225894

Automate Database Restore to Remote Instance with SSIS

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/automate-database-restore-to-remote

Automatically Transfer and Synchronize SQL Server logins, SIDs, Passwords and Permissions

http://www.msbicoe.com/post/2012/04/05/Automatically-Transfer-and-Synchronize-SQL-Server-logins-SIDs-Passwords-and-Permissions.aspx

 

 

Query to find instance patch level:

SELECTSERVERPROPERTY(‘productversion’),SERVERPROPERTY(‘productlevel’),SERVERPROPERTY(‘edition’)

 

Query to list database files and their paths:

select name,physical_name fromsys.master_filesorderby 1

 

Hardware information from SQL Server 2008 and 2008 R2:

— (Cannot distinguish between HT and multi-core)

SELECT

       cpu_count AS [Logical CPU Count]

      ,hyperthread_ratio AS [Hyperthread Ratio]

      ,cpu_count / hyperthread_ratio AS [Physical CPU Count]

      ,physical_memory_in_bytes / 1048576 AS [Physical Memory (MB)]

      ,sqlserver_start_time

FROMsys.dm_os_sys_info;

 

Scripting Server Permissions and Role Assignments

/* Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SET NOCOUNT ON

SELECT 'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context'

-- Role Members
SELECT 'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
+ QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1)
+ QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC

-- Permissions
SELECT server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ']' AS '--Server Level Permissions'
FROM sys.server_permissions AS server_permissions WITH ( NOLOCK )
INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ( 'S', 'U', 'G' )
ORDER BY server_principals.name,
server_permissions.state_desc,
server_permissions.permission_name