SQL Server – Upgrading to SQL Server 2017 from SQL Server 2012

sql serversql-server-2012sql-server-2017

Upgrade or New Build? Any suggestions?
Does anyone have any gotchas or war stories to share about this process?

We have a 2012 instance in Prod that has both OLTP and OLAP databases. We are creating two instances, one to be dedicated to each of those tasks. The OLAP instance is a new build, however to minimize end-user down time we are considering an upgrade for the instance that will house the OLAP databases.

I'm looking for general recommendations that go beyond what I'm reading in the Microsoft docs.

Best Answer

Unless it's a CU or SP, I'd always stick to a side-by-side upgrade over an in-place one. It allows you to rigorously test and UAT the new version and ensure that all your queries/processes still run efficiently and aren't plagued by new Execution Plans or query engine "features" that can turn out to be a serious hindrance.

If you can't spout the license, try upgrading to 2017 Developer Edition, which is still a full version. As long as you don't use the Dev edition for production, you can do all the dev/testing/staging you want on it.

Here's my favorite post on the topic. The e-book is available in the FRK. (I seem to remember this post being longer but I guess not). https://www.brentozar.com/archive/2015/10/sql-server-migration-and-upgrade-checklist-video-and-ebook/

Related Solutions

Sql-server – SQL Server replication for off site copy

Based on what you have described Log shipping is going to be the way to go. Log shipping is old school, tried and true.

Replication is pretty fragile, provides massive amounts of room for failure especially when it comes to adding new tables and getting those tables into the replication topology.

As for 2012 upgrade costs they may not be as bad as you think. If you have software assurance and an enterprise agreement that'll save you a bunch of money on licensing.

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

Best Answer

Related Solutions

Sql-server – SQL Server replication for off site copy

SQL Server Upgrade – Moving from SQL Server 2008 to 2012

Related Question