sql serversql-server-2017upgrade
I have one machine we've been fighting to do an in place upgrade of SQL Server from 2016 SP2 CU3 to 2017. It keeps failing, but that's another topic. My question is, is there a way I can run the upgrade, but force it to omit upgrading user databases to 2017?
If I can get the system databases upgraded and the server running I'll manually upgrade user databases at another time.
After poking around in SSMS for a while I noticed that on the secondary replica there was a pause icon next to the Availability Databases. The primary had shown both were "green", but there was an option on the secondary to Resume Data Movement. I resumed the first database, and immediately the In Recovery status message was removed. A minute later it changed from Not Synchronizing to Synchronized, and everything worked as expected.
Here is a screenshot of the AG Databases after I fixed "Patch", but before fixing the test database:
Note you can also use TSQL on the secondary to resume replication on multiple database at the same time:
ALTER DATABASE [Patch] SET HADR RESUME;
ALTER DATABASE [test] SET HADR RESUME;
GO
As per BOL
When you upgrade to SQL Server 2014 Integration Services (SSIS) on a machine that has one of these earlier versions of Integration Services installed, SQL Server 2014 Integration Services (SSIS) is installed side-by-side with the earlier version.
It also says that:
Now, as per my personal experience with In-Place SQL Server upgrade from SQL Server 2008 R2 to SQL Server 2012 I didn't face compatibility issues. Packages ran fine, except for few where I had to open packages and compile script task again.
Best Answer
No. As soon as you attach a database to SQL Server, assuming it is compatible, the internal system tables will be upgraded.
While this process is a one-way operation, it takes a few seconds at most.
If you do not wish to upgrade your user databases, don't upgrade to the latest SQL Server.
If you want to keep your databases on a previous compatibility level, that can be done from T-SQL code or inside SQL Server Management Studio, but for all intents and purposes, the internal version of the database will match that of the instance it is installed on.