DBA

Microsoft SQL Server upgrade plan and steps

Advertisements

SQL server upgrade is going lower version of SQL to higher version – the latest version. It can be a minor upgrade i.e. service pack patch apply or major upgrade from one version to another ex: 2016 to 2017.

Types:

We can perform this in the existing system that is called In-place upgrade. This requires more downtime and better roll back/fall back plan. Since, if anything happens take more time to restore previous state, but quickest one, just taking a backup of everything, mean all and start installation wizard upgrade option.

We can install new latest SQL version and copy everything to the new server that is called side by side upgrade. This is something like combination of migration going with upgrade to latest version.

Both types will be useful when there is a requirement. Like, we can remove legacy software and hardware or run in the same server and just upgrade the database engine.

Important point and Steps, scripts to start with it:

You have to Download and run Data Migration Assistant to check all features are supported in the higher version and mark the Deprecated functions. It is good to test the data load in the newer server, if possible by using testers for high IOPS applications.

Take a backup of everything including system database.

In-place: It is easy one, just copy and run the newer version of software and choose upgrade.

Side-by-Side: It needs some work and pre-plan. Restore a backup with norecovery mode and migrate everything to the new server like jobs, logins, linked servers etc.  and do a final cut over.

Pre Upgrade
Hardware and software requirements
Check the upgrade support path direct or intermediate versions - MSDN
Download and run Data Migration Assistant - DEPRECATED FEATURES check
Collect the System Instance Name and Version
Start-up parameter, Login mode
Collect the SQL and Agent account and password
Collect the Network Protocols and components installed, such as SSRS,SSAS
Check the windows version and SP level supported for upgrade
Check the SQL Server version and SP level supported for upgrade
Check if the .net framework version is 3.5 SP1
Generate User Logins Scripts from MSDN - sp_help_revlogin
Generate the backup, Linked server and job scripts
Take some workload and test it in the newer server
Upgrade Plan
Check and Install Windows Installer supported for upgrade
Run a SQL backup
Take a copy of resource DB from BIN directory
Backup the reporting service key if available
Inform ticketing team and disable the server from monitoring
Stop & disable the services
Run the SQL 2012 upgrade
Reboot the server
Post UP-grade
Verifying the version number
Start and Verify the services
Verify the logins are mapped correctly
Start the other all stopped services
Check compatibility levels and test before change
Run the Check DB, UPDATEUSAGE and update statistics

 

Dynamic In-fly query to use:

-------------------------------------------------------------------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

Leave a Reply

%d bloggers like this: