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 -VM snapshot.
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.
Supported Version and Edition Upgrades:
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 |
| Take VM snapshot for rollback |
| 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
