Introduction

The main goal of many organizations, today, is reducing costs while maintaining the highest degree of stability and efficiency. To this end, we should think out of the box about how we can help to achieve this as DBAs. The approaches include:

• Centralization
• Curtailment the SAN storage
• Reducing the Microsoft license for Windows and SQL Server

Database Migration is a possible solution to achieve this goal. However, some DBA’s do not have a clear view of the requirements, and the actual steps for how to accomplish this with minimum risks and zero downtime.

In this article, I will list commons steps for database migration, including the following:

• Migration Requirements
• Reporting Services (SSRS) Migration
• Databases Engine Migration Preparation
• Database Engine Migration Process
• Migration recommendation
• Conclusion
• References

Migration requirements

In this phase, we should have good knowledge about Microsoft SQL Server services and how we can manage it to be able to collect the below information:

• How many SQL Server instances? If we are going for migration project, we should list all the instance included in this process to prepare our scripts and to open the shared folder Port 445 to be able to take backup and restore on this shared folder.

• How many Databases need to be migrated? This is very important information to be able to know how we will distribute the databases between the new instances that we will migrate to.

• Databases size? This information is required to be able to design the disk chart diagram required for this databases

Reporting Services (SSRS) Migration

For moving reporting RDL from server to another server, we have two options:

1. Backup/Restore the two databases {ReportServer, ReportServerTemp} from the old reporting service instance to the new one

2. Moving the RDL files and data source using third party tools like RS ScripterThis tool can grab all the RDL and data source from the destination and deploy it to the new report server. For more information about this tool check this link

More information can be found on this article – Moving the Report Server Databases to Another Computer (SSRS Native Mode)

Database Migration

In this stage, will list all the steps one by one, that it will lead us to the optimal migration with no downtime, and without missing anything. These steps are divided into two phases, Preparation and Migration which will be covered next:

Databases Engine Migration Preparation

• Application Server: The Development team should be ready to change the connection string to the new SQL Server instance name. However, sometimes they will not be able to do this, and in such case we need to us routing alias to direct any connection attempts from the application server to the Old SQL Server.

  Then the alias will direct it to our new SQL Server

  To do this action:

  • Open RUN
  • Write Cliconfg
  • Select Alias and TCP/IP

    

    Figure 2 Adding Alias

• Server configuration: script all the current server configuration like [CPU, IO, Memory, the threshold of parallelism, Max degree of parallelism, CLR]

• RCSI (Read Committed Snapshot Isolation): List all databases with RCSI option enable, this configuration saved in MSDB and when you move the DB by backup/restore this kind of configuration you will lose it.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

  Select name, ' EXEC sp_resetstatus ''' + name + ''' GO ALTER DATABASE ' + name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE go ALTER DATABASE ' + name + ' SET READ_COMMITTED_SNAPSHOT ON; go ALTER DATABASE ' + name + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE go ' as 'Run on Destination Server.' from sys.databases where is_read_committed_snapshot_on = 1 and database_id>4 and name <>'distribution'

• Service Broker: figure out the databases with the service broker option enabled

  1 2 3 4 5 6 7 8 9 10 11 12 13

  Select name,' USE master ; GO ALTER DATABASE [' + name + '] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE ' + name + ' SET ENABLE_BROKER ; GO ALTER DATABASE [' + name + '] SET  MULTI_USER WITH ROLLBACK IMMEDIATE GO' from sys.databases where is_broker_enabled = 1 and database_id>4 and name <>'distribution'

• Non-Contained Object Migration: here we need to migrate the following: Linked Servers, Users, Privileges Alerts, Operator, Mail configuration, Bussniues jobs and System DB user objects

  To cover all of the above, I am using a very helpful PowerShell script from DBATools which covers many cases.

  For more information about this PowerShell script check it here DBATools; some samples from the PowerShell command:

  • Copy-SqlLogin -Source “Server name”-Destination “Server name”-Force
  • Sync-SqlLoginPermissions -Source “Server name”-Destination “Server name”
  • Copy-SqlJob -Source “Server name”-Destination “Server name”-Force
  • Copy-SqlOperator -Source “Server name”-Destination “Server name”-Force
  • Copy-SqlAlert -Source “Server name”-Destination “Server name”-Force
  • Copy-SqlDatabaseMail -Source “Server name”-Destination “Server name” -Force
  • Copy-SqlLinkedServer -Source “Server name”-Destination “Server name”–Force
  • Copy-SqlSysDbUserObjects -Source “Server name”-Destination “Server name.”

• SYNONYMS: it is like linked server we need to list it then change the connection string with new server
  1 2 3 4 5 6 7 8

  Declare @DBname Nvarchar(500)=(Select DB_NAME()) Select @DBname Select   ' Use ['+@DBname+'] GO Create SYNONYM dbo.[' + Name + '] For ' + Base_Object_name   FROM SYS.SYNONYMS

Database engine migration process

These previous four points are pre-preparation for the migration process, and now let’s start the actual migration.

• Full Backup from the source server: Backup is the most important step that the entire migration processes depend on it. Thus, I am using one of my customized stored procedures: (DMV_backup_Database), it is very sophisticated stored procedure to drive all of the SQL backups types (FULL, DIFF, LOG) with using the backup device, dynamic technology parameters, Table type for eliminating long some databases from the backup. This stored procedure will require creating two shared folders on the destination server, to be able to take the backup direct from the source to the destination SAN disks.

  For more information about this stored procedure including parameters and script see Appendix A.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

  USE [msdb] GO   CREATE TYPE [dbo].[Exceptionlist] AS TABLE( [list] [int] NULL ) GO   USE [msdb] GO DECLARE @return_value int declare @Exceptionlist_value Exceptionlist insert into  @Exceptionlist_value Select 2 UNION SELECT 6 UNION SELECT 9--- DB's ID Excluded from backup process EXEC @return_value = [dbo].[DMV_BackupAll] @start_DB_ID = 5, @END_DB_ID = 200, @FULLTapeBackupLocation = N'\\DestinationServer\Backup\FULL\', @FULLBackup = 1, @FULLoverwrite =1 , @DIFFTapeBackupLocation = N'\\DestinationServer\Backup\DIFF\', @DiffBackup= 0 , @DIFFoverwrite = 0, @LOGBackupLocation = N'\\DestinationServer\Backup\LOG\', @LOGBackup = 0, @LOGoverwrite = 0, @COMPRESSION = 1, @Exceptionlist_DBs = @Exceptionlist_value, @job_name='DMV_UserDB_Full_Backup', @p_recipients = 'SQLGULF-DBConsultant@MostafaElmasry.com'   SELECT 'Return Value' = @return_value

• Create Backup Device for a Full backup: script all the backup devices created in the source after the backup successfully processes and create them on the destination server to be able to do the restore using this backup device.

  1 2 3 4 5 6 7 8 9 10 11

  USE [master] GO Select 'USE [master] GO EXEC master.dbo.sp_addumpdevice  @devtype = N''disk'', @logicalname = N'''+name+'_FULLtape'', @physicalname = N''\\DestinationServer\Backup\FULL\'+name+'_FULLtape.back'' GO ' from Sys.Databases where state_desc ='online' AND Database_id > 4

• Restore Full Backup on destination server: The second step is restoring the entire FULL backup on the new server with No recovery option. For making the databases in the restore mode to be able to restore the Differential backup overwrite it.

• DB in Read-only status: in the source server, we need to update the status of all of the databases to be in a Read-Only status, to make sure there is no change will occur on the DB level. The following script will generate a T-SQL script for altering each database to be in a Read-only mode.

  1 2 3 4 5 6 7 8 9 10 11 12

  select 'EXEC sp_resetstatus '''+NAME+''' GO ALTER DATABASE ['+NAME+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE ['+NAME+'] SET  READ_ONLY WITH ROLLBACK IMMEDIATE GO ALTER DATABASE ['+NAME+'] SET MULTI_USER ' from sys.databases where database_id>4 and name <>'distribution' and state_desc = 'online'

• Differential Backup from the source server: Now the DB’s are in Read only mode so taking a differential backup will cover all the changes happened on the database from the last full backup. Here, also, we will use the same stored procedure “DMV_backup_Database” we used it in the full backup before.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

  USE [msdb] GO DECLARE @return_value int declare @Exceptionlist_value Exceptionlist insert into  @Exceptionlist_value Select 2 UNION SELECT 6 UNION SELECT 9--- DB's ID Excluded from backup process EXEC @return_value = [dbo].[DMV_BackupAll] @start_DB_ID = 5, @END_DB_ID = 200, @FULLTapeBackupLocation = N'\\DestinationServer\Backup\FULL\', @FULLBackup = 0, @FULLoverwrite =0 , @DIFFTapeBackupLocation = N'\\DestinationServer\Backup\DIFF\', @DiffBackup= 1 , @DIFFoverwrite = 1, @LOGBackupLocation = N'\\DestinationServer\Backup\LOG\', @LOGBackup = 0, @LOGoverwrite = 0, @COMPRESSION = 1, @Exceptionlist_DBs = @Exceptionlist_value, @job_name='DMV_UserDB_DIFF_Backup', @p_recipients = 'SQLGULF-DBConsultant@MostafaElmasry.com'

• Create Backup Device for DIFF backup: Script the backup devices related to the DIFF backup from the source server and create it on the destination server.

  1 2 3 4 5 6 7 8 9 10 11

  USE [master] GO Select 'USE [master] GO EXEC master.dbo.sp_addumpdevice  @devtype = N''disk'', @logicalname = N'''+name+'_DIFFtape'', @physicalname = N''\\DestinationServer\Backup\FULL\'+name+'_DIFFtape.back'' GO ' from Sys.Databases where state_desc ='online' AND Database_id > 4

• Restore the Differential Backup on destination server: Restore the differential backup with Recover option and overwrite.

• Change DB’s status to Read-Write status: When we restored the differential backup on the destination server, will find all the databases in read only mode because we take the differential backup from the source after updating the databases by read only mode.

  1 2 3 4 5 6 7 8 9 10 11 12

  select 'EXEC sp_resetstatus '''+NAME+''' GO ALTER DATABASE ['+NAME+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE ['+NAME+'] SET  READ_WRITE WITH ROLLBACK IMMEDIATE GO ALTER DATABASE ['+NAME+'] SET MULTI_USER WITH ROLLBACK IMMEDIATE ' from sys.databases where database_id>4 and name <>'distribution' and state_desc = 'online'

• Migrate Login from source server: Execute the PowerShell command again for moving the SQL Server users to grantee there is no missing user.

  • Copy-SqlLogin -Source “Server name”-Destination “Server name”-Force

  • Sync-SqlLoginPermissions -Source “Server name”-Destination “Server name.”

• Disable login on the source server: Now all of our databases are moved to the new server. To know if the application is working fine do disable the logins in the old instance.

  1 2 3 4 5 6 7 8 9 10 11

  USE master; GO SELECT 'ALTER LOGIN ['+name+'] DISABLE;' FROM sys.syslogins where name not like '%##MS%' and name not like '%NT AUTHORITY%' and name not like '%NT SERVICE%' and name not in ('SQLSHACK','SA')

• Check DB compatibility: If we migrated from old version to new version as Example 2016 we need to update the DB compatibility to be 140

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  select name, ' EXEC sp_resetstatus ''' + name + ''' GO ALTER DATABASE ' + name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE go USE [master] GO ALTER DATABASE [' + name + '] SET COMPATIBILITY_LEVEL = 140 go ALTER DATABASE ' + name + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE go ' from sys.Databases where Database_Id > 4 AND COMPATIBILITY_LEVEL <> 140

• Check Failed login on both servers: failed logins on of the things that we should monitor to know if there is any application still connected to the old SQL Server. Here is the steps for creating Failed login audit in SQL Server

  • Create folder on local SAN storage

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

    USE Master; GO SET NOCOUNT ON -- 1 - Variable declaration) DECLARE @LogPath nvarchar(500) DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT) SET @LogPath = 'C:\ServerAuditing\' ---------------+ @DBName -- 5 - Remove all records from @DirTree DELETE FROM @DirTree -- 6 - @LogPath values INSERT INTO @DirTree(subdirectory, depth) EXEC master.sys.xp_dirtree @LogPath -- 7 - Create the @LogPath directory IF NOT EXISTS (SELECT 1 FROM @DirTree ) ----------WHERE subdirectory = @DBName) EXEC master.dbo.xp_create_subdir @LogPath SET NOCOUNT OFF GO

  • Create server audit specifications

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

    USE [master] GO IF  EXISTS (SELECT * FROM sys.server_audits WHERE name = N'FAILED_LOGIN_GROUP') ALTER SERVER AUDIT [FAILED_LOGIN_GROUP] WITH (STATE = OFF); GO IF  EXISTS (SELECT * FROM sys.server_audits WHERE name = N'FAILED_LOGIN_GROUP') DROP SERVER AUDIT [FAILED_LOGIN_GROUP] GO USE [master] GO /****** Object:  Audit [FAILED_LOGIN_GROUP]    Script Date: 10/25/2010 13:21:58 ******/ CREATE SERVER AUDIT [FAILED_LOGIN_GROUP] TO FILE ( FILEPATH = N'C:\Server Auditing' ,MAXSIZE = 100 MB ,MAX_ROLLOVER_FILES = 2 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '8f5c8e0b-6ba3-4964-8da5-d7184220f9ca' ) GO ALTER SERVER AUDIT [FAILED_LOGIN_GROUP] WITH (STATE = ON); GO IF  EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'FAILED_LOGIN_GROUP') ALTER SERVER AUDIT SPECIFICATION [FAILED_LOGIN_GROUP] WITH (STATE = OFF) GO IF  EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'FAILED_LOGIN_GROUP') DROP SERVER AUDIT SPECIFICATION [FAILED_LOGIN_GROUP] GO GO CREATE SERVER AUDIT SPECIFICATION [FAILED_LOGIN_GROUP] FOR SERVER AUDIT [FAILED_LOGIN_GROUP] ADD (FAILED_LOGIN_GROUP) WITH (STATE = ON) GO

Migration recommendation

In this juncture, there are some recommended points we should consider; they are the key elements any successful migration project:

• The master database should be hosted on a separate disk (200 GB, SAS, RAID 5)

• TempDB databases should be hosted on a separate disk (150 GB, SSD-, RAID 1/0)

• MDF files for the user’s databases should be hosted on a separate disk (SAS, RAID 5)

• Transaction log (LDF) should host in separate disk (150 GB, SAS, RAID 5)

• TempDB databases files should be configured based on the logical processors count if the logical processors less than or equal to 8 configure your tempdb with the count of the logcail processors, If it is more than 8 logical processors configure your tempdb on 8 files for more information check microsoft recommendation.

Conclusion

A successful database migration is not mission impossible. But it is important to approach it systematically. One only needs an awareness of what to do and the requirement to successfully carry it out. And, as always, make sure every single step is documented.

Appendix A – DMV_BackupAll stored prodecure

You can download this script here

• Author
• Recent Posts

Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East.

Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server.

Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases.

View all posts by Mustafa EL-Masry

Latest posts by Mustafa EL-Masry (see all)

• Concept and basics of DBCC Commands in SQL Server - March 31, 2017
• Hybrid Cloud and Hekaton Features in SQL Server 2014 - February 28, 2017
• How to analyze Storage Subsystem Performance in SQL Server - February 23, 2017