SQL Server 2016 : Change compatibility level after migration

Question

SQL Server 2016 : Change compatibility level after migration

river1

SSC-Insane

Points: 24300
More actions
November 28, 2017 at 12:57 am

#330157

Dear all,
I have migrated my SQL Server from SQL Server 2012 to 2016.
Not I have the followinf list of databases with below compatibily level:
master 110
tempdb 130
model 130
msdb 130
dwhReportServer 110
dwhReportServerTempDB 110
dwhMasterDataServices 100
SSISDB 110
dwhDataMart_PE 110
dwhStaging 110
dwhStagingArchive 110
dwhCore 110
dwhDataMart_MIS 110
dwhLogging 110
dwhCalculation 110
dwhMasterDataServicesGateway 110
dwhExport 110
dwhDataMart_UPDB 110
1) I would like to start to understand why were all the system databases converted to SQL Server 2016 compatibility level (130) but the master stayed in 2012 (110) ? Can I using t-sql migrate the master DB to 130?
2) The reporting server that I have is in using reporting services 2012. Can I the databases (dwhReportServer 110, dwhReportServerTempDB 110) to 130?
3) The database (repository) of SSIS (SSISDB 110) can I convert to 130? because the database SSIS sits in our SQL Server 2016. as SSDT we are suing VS 2013.
thank you for the support
December 5, 2017 at 4:57 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ricky Valencia SSCommitted Points: 1907 More actions · Answer 1

Hi

The act of changing the compatibility level tells the database to change its feature set. That is, some features will be added, but at the same time some old features will be removed. Hence it is important that you scan and test your application after changing compatibility levels.

T-SQL to change compatibility level

USE master go ALTER DATABASE [yourdatabasesname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE go ALTER DATABASE [yourdatabasesname] SET COMPATIBILITY_LEVEL = 130 go ALTER DATABASE [yourdatabasesname] SET MULTI_USER GO

Youcan change the compatibility level of your Report Servers. Please refer to the CompatibilityLevels by instance table below.

I hope this helps.

Best Regards,

river1 SSC-Insane Points: 24300 More actions · Answer 2

Hello,

Let me start by thank you for your reply. Appreciated.

Yes. I am aware that new feactures will be added and some removed. But what about below question? Can you help me clarify?

1) I would like to start to understand why were all the system databases converted to SQL Server 2016 compatibility level (130) but the master stayed in 2012 (110) ? Can I using t-sql migrate the master DB to 130?

Can I migrate the SQL Server MasterDB? I Don't expect many problems as I don't have any user based functionalities on it. But I don't know if something needs to be taking in consideration. For example, if I have to put the database in from 130 to 110 compatibilty mode again, is it possible?

2) The reporting server that I have is in using reporting services 2012. Can I the databases (dwhReportServer 110, dwhReportServerTempDB 110) to 130?

I did not undertood your answer related with this topic. Do you mean that even using reporting services in version 2012 I can still migrate the database from 110 to 130?

3) The database (repository) of SSIS (SSISDB 110) can I convert to 130? because the database SSIS sits in our SQL Server 2016. as SSDT we are suing VS 2013.