DBA

Microsoft SQL Server Migration plan and steps

Advertisements

Migration is nothing but moving a databases from one instance to another instance. It could be a single database or all the databases. DBA will get this many times, for testing or development server new build or removing legacy/old servers

In general, this will be a combination of migration and upgrade of newer version ex: existing 2012 from old server to new server 2016. Some will have two instances in same server and migrate for dev and test purpose.

Types: Best backup/restore, for VLDB very large databases, you can plan SAN replication or VM level and mount point/ VMDK movements with detach attach database etc.

Supported Version and Edition Upgrades, if you are going higher version of migration.

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades?view=sql-server-ver15

Important point and Steps, scripts to start with it:

Migrate everything and test it some days and do a final cut over.

 

Pre-Migration Checklist (Source Server)
Roughly calculate all the DB size
Run DMA tool and make necessary changes in the code
Install new SQL and (ssrs,ssas & ssis) if needed and ready for migrate
Generate the backup Scripts (full & diff & log)
Generate User Logins Scripts from MSDN - sp_help_revlogin
Generate SQL Agent Jobs Scripts & check maintenance plan
Generate Linked server Scripts - get the passwords
Check anything else configured in that server (log shipping, mirror, replication & AG) make a note of it
Pre-Migration Checklist (Destination Server)
Check the server compatibility after restore. You can keep or change to higher compatibility mode of DBs. You need to test that.
Make the backup avialble on destination by copy paste or share path
copy all the scripts - jobs, linked server, logins
Check Orphan User issues
Generate Restore Scripts, check drive path and size
Start full backup restore with NORECOVERY mode
Run the jobs, linked server, logins scripts etc
create the DBA maintenance plan
Down time cut over window
remove/ pause the both source & destination server from monitoring tool
Email and Inform application and ticketing team
Source server:
Set all the databases to read only mode & stop the agent, ssas and ssis service.
Take the final log backup
Destination Server:
Restore the transaction log backups with recovery
Bring all the database to read write mode
Run check DB and reindex - optional based on size of DBs
Point the application to the new DB server IP (Connection string etc to altered by the application support team)
Once all good email team to enable monitoring
-------------------------------------------------------------------All DB size
select sum(size/128) As SizeIn_MB from sys.master_files
-------------------------------------------------------------------
--select * FROM SYS.DATABASES -- name, database_id, state_desc = 'online', 
--select * from SYS.master_files state_desc = 'online'

/*BACKUP_SCRIPT change the backup location*/

SELECT 'BACKUP DATABASE ['+ NAME + '] TO DISK =''F:\MIGRATION_BACKUP\Full\'+ NAME +'_20_SEP_FULL.BAK'''FROM SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4)AND state_desc = 'online'

-------------------------------------------------------------------
/*REPORT LOGINS*/
SELECT 'USE ['+NAME+'];EXEC SP_CHANGE_USERS_LOGIN ''REPORT'' 'FROM MASTER.SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4) AND state_desc = 'online'

sp_change_users_login 'update_one', 'TestUser1','TestUser1'

---------restore ----- Change the backup location and MDF & LDF also this only works with one ldf and mdf
-- You have to restore the more than one MDF or LDF manually, use the following to find more that one files
select database_id,db_name(database_id) as dbname from SYS.master_files group by database_id having count(database_id)>2 




SELECT 'RESTORE DATABASE ['+ DB_NAME(D.database_id)+'] FROM DISK =''F:\MIGRATION_BACKUP\Full\'+ DB_NAME(D.database_id) +'_20_SEP_FULL.BAK'''+
'WITH '+
'MOVE '''+d.name+''' TO ''D:\mssql\data\'+d.name+'.mdf'','+
'MOVE '''+l.name+''' TO ''e:\mssql\log\'+l.name+'.ldf'', norecovery' FROM  SYS.master_files D JOIN
sys.master_files L ON (D.database_id=L.database_id) AND D.file_id=1 AND L.FILE_ID=2
where D.database_id NOT IN (1,2,3,4) -- add the database_id of that more that 2 count DB here
AND l.state_desc = 'online'

--================= -- Cut over time
/*READ_ONLY*/

SELECT 'ALTER DATABASE ['+NAME+'] SET  READ_ONLY WITH NO_WAIT'FROM SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4) AND state_desc = 'online'
-------------------------------------------------------------------

/*Final log backup use differnt folder for log*/ -- change the path F:\MIGRATION_BACKUP\Log\

SELECT 'BACKUP Log ['+ NAME + '] TO DISK =''F:\MIGRATION_BACKUP\Log\'+ NAME +'_20_SEP_log.TRN'''FROM SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4)AND state_desc = 'online'

-- You have to restore the more than one MDF or LDF manually, use this 
select database_id,db_name(database_id) as dbname from SYS.master_files group by database_id having count(database_id)>2 

-- restore log -- change the path F:\MIGRATION_BACKUP\Log\
SELECT 'RESTORE DATABASE ['+ DB_NAME(D.database_id)+'] FROM DISK =''F:\MIGRATION_BACKUP\Log\'+ DB_NAME(D.database_id) +'_20_SEP_log.TRN'''+
'WITH '+
'MOVE '''+d.name+''' TO ''D:\mssql\data\'+d.name+'.mdf'','+
'MOVE '''+l.name+''' TO ''e:\mssql\log\'+l.name+'.ldf'', Recovery' FROM  SYS.master_files D JOIN
sys.master_files L ON (D.database_id=L.database_id) AND D.file_id=1 AND L.FILE_ID=2
where D.database_id NOT IN (1,2,3,4) -- add the database_id of that more that 2 count DB here
AND l.state_desc = 'online'


/*READ_write*/

SELECT 'ALTER DATABASE ['+NAME+'] SET  READ_WRITE  WITH NO_WAIT'FROM SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4) AND state_desc = 'online'

------------------------------------------------------------------- Optional
/*Check Db*/

SELECT 'DBCC CHECKDB( ['+NAME+'])'+' WITH DATA_PURITY'
FROM SYS.DATABASES WHERE database_id NOT IN (1,2,3,4)AND state_desc = 'online'

/*UPDATE USAGE*/
SELECT 'DBCC UPDATEUSAGE( ['+NAME+'])'
FROM SYS.DATABASES WHERE database_id NOT IN (1,2,3,4)AND state_desc = 'online'

/*UPDATE STATISTICS*/
SELECT 'USE ['+NAME+'];EXEC sp_MSforeachtable ''UPDATE STATISTICS ? WITH FULLSCAN'' 'FROM MASTER.SYS.DATABASES
WHERE database_id NOT IN (1,2,3,4) AND state_desc = 'online'

--(OR) --create maintenance job and run

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

4 + 1 =