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.
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.
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
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?
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)
12. Set the max memory size for SQL Server.
Suggested Max Memory Settings for SQL Server 2005/2008
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?
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.)
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.
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.
b. ETL processes
c. External processes
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.
How can I copy a SQL Server 2008 instance to another server? *Missing step for roles and permissions*
Copy Database from Instance to another Instance – Copy Paste in SQL Server
Moving SQL Server 2008 R2 to another Machine
Moving System Databases *Not Recommended *
Moving User Databases
Move SQL Server Instances from one data center to another data center
Maximum Number of Processors Supported by the Editions of SQL Server
How many logical processors does SQL Server 2008 R2 Enterprise Edition support?
How to: Migrate a Reporting Services Installation
How to Transfer (Copy) Maintenance Plans from One Server to other
How to move SSAS from one server to another
Automate Database Restore to Remote Instance with SSIS
Automatically Transfer and Synchronize SQL Server logins, SIDs, Passwords and Permissions
Query to find instance patch level:
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)
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)]
Scripting Server Permissions and Role Assignments
/* Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SET NOCOUNT ON
-- Role Members
'EXEC sp_addsrvrolemember @rolename ='
', @loginame ='
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
'--Server Level Permissions'