Sql-server – Benchmarking procedures or Upgrade tests to be done after upgrading to SQL Server 2016 from SQL Server 2012

profilersql serverupgrade

I need suggestions on how to benchmark performance or create baselines to test the upgrade from SQL Server 2012 to SQL Server 2016 (both standard edition), also if any post upgrade tasks that needs to be checked to ensure smooth performance after upgradation.

P.S : I have recently created a test snapshot of my 2012 SQL Server database(also have separate snapshot of 2012 SQL Server as well) and have upgraded it to 2016 SQL Server (both standard) in AWS RDS. I want to test the performance and functionality of both the instances to check if the upgrade went smooth and I can proceed it for our main production Database.

Please help me on all the checks to be performed.

I have done replaying a trace file from the current server on the new server.
Below are the results:

Best Answer

When I did SQL 2005 to 2012 I used the Distributed Replay Controller feature. You run a profiler trace with the DRC template. Then run the command on a few servers and it runs those trace files against a target server.

During the process you check whatever performance monitoring app you use and make a note of the times

Related Solutions

SQL Server Upgrade – Moving from SQL Server 2008 to 2012

Turning my comments to answer :

SSMS is just a tool to connect to SQL Server.

You did an inplace upgrade, but were connecting to SQL Server 2012 using SSMS 2008R2 and SSMS 2012.

Using T-SQL - will give you the correct version:

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + ' 
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation

Best practice:

Before upgrade - run the above script to get the version and after upgrade, run it to make sure that you are on correct right version.
Always use the latest version of SSMS (regardless of SQL Server version).

SQL Server – Upgrade from SQL Server 2012 to 2016

What you've got sounds like it should work and would get you online quickly, however you will have no HA plan if something fails before you've got the 200GB database replicated to at least one of your secondaries.

Also what will you do if your upgrade fails? I don't think there will be a way back to the old server without going back and restoring backups.

When we've done similar migrations (2012 to 2014), we've done the following:

Create the Availability Group
Take a full backup of current production
Restore on all servers in the AAG (leaving in restore mode)
Take a differential backup of current production shortly before your 30 minute window and restore to all servers on the AAG (leaving in restore mode)
Take regular log backups of the current production server and restore (you could automate this with log shipping)
When your 30 minute window starts, restore the last of the log backups, bring the new primary online, make sure everything is attached to the AAG and you're ready to go

Best Answer

Related Solutions

SQL Server Upgrade – Moving from SQL Server 2008 to 2012

SQL Server – Upgrade from SQL Server 2012 to 2016

Related Question