SQL Server Compatibility Levels Vs Setting Up A Contained Database.
Updated: Apr 18
Whether you are a small business owner or work at an enterprise organization, effective management of your SQL Server is integral for successful data operations. As new versions of Microsoft’s database engine come out, there is an option to set the compatibility level so that existing databases that can be configured to work with the new version and still maintain backward compatibility.
This post will discuss what SQL Server Compatibility Levels are, why they are important and how they help ensure that databases remain compatible across different versions of sql server. We’ll also look at how database administrators can can leverage this feature in order to optimize their networks for peak performance levels. By understanding these concepts, organizations will be well-equipped to handle upgrade paths while keeping pace with rapid technology changes without sacrificing stability.
What are SQL Server Compatibility Levels and why they are important
SQL Server Compatibility Levels serve a crucial role in ensuring seamless integration and optimal performance in diverse database environments. These various supported compatibility levels determine the specific SQL Server version behaviors supported by a database, facilitating backward compatibility and smooth transitions during version upgrades. Maintaining an effective source database compatibility level framework is essential for organizations to protect their existing applications from potential disruptions, optimize database functionalities, and promote efficient resource utilization.
By setting an appropriate compatibility level, DBAs can continue harnessing the benefits of earlier SQL Server iterations while embracing the latest innovations and features, empowering a harmonious ecosystem that caters to an organization's diverse needs. As such, SQL Server Compatibility Levels act as a critical bridge to align legacy applications with current technological advancements, fostering continuous improvement and sustainable progress in the realm of data management.
Here are the available SQL Server Compatibility Levels:
The default compatibility level for a new database is the current version of SQL Server, but it can be changed to any of the available supported compatibility levels. The various supported compatibility levels or level affects certain database behaviors, such as syntax and query optimization, so it's important to choose the right and lowest supported compatibility level for your needs.
In SQL Server, compatibility level refers to the version of the SQL Server database engine with which a particular database is compatible. Changing the compatibility level of a database can affect the behavior of certain database features and may also impact the performance of queries.
Some of the more important compatibility level differences in SQL Server 2016 are:
Compatibility level 80: This is the default compatibility level for SQL Server 2000. Databases set to this level do not support some of the advanced features introduced in later versions of SQL Server.
Compatibility level 90: This is the default compatibility level for SQL Server 2005. Databases set to this level support many of the advanced features introduced in SQL Server 2005, such as Common Table Expressions (CTEs), recursive queries, and cross-apply joins.
Compatibility level 100: This is the default compatibility level for SQL Server 2008 and 2008 R2. Databases set to this level support additional features such as filtered indexes, compressed backups, and MERGE statements.
Compatibility level 110: This is the default compatibility level for SQL Server 2012. Databases set to this level support additional features such as the SEQUENCE object, user-defined server roles, and columnstore indexes.
Compatibility level 120: This is the default compatibility level for SQL Server 2014. Databases set to this level support additional features such as the In-Memory OLTP feature and support for JSON data.
Compatibility level 130: This is the default compatibility level for SQL Server 2016. Databases set to this level support additional features such as the STRING_SPLIT function, temporal tables, and row-level security.
Compatibility level 140: This is the default compatibility level for SQL Server 2017. Databases set to this level support additional features such as graph database capabilities and adaptive query processing.
Compatibility level 150: This is the default compatibility level for SQL Server 2019. Databases set to this level support additional features such as accelerated database recovery and UTF-8 support.
How to set the compatibility level of a database in Microsoft SQL Server
Knowing how to set the compatibility level of a database in Microsoft SQL Server is an important part of managing your data. The SQL Server Compatibility Level feature allows you to ensure all objects and data within a given database are compatible with a specific version of SQL Server, thus preventing any potential compatibility issues.
To use this feature, you will have to use the ALTER DATABASE statement in Transact-SQL with the option COMPATIBILITY_LEVEL set to whatever desired version of sql server you wish your database to operate at. Careful consideration should be made when setting the compatibility mode, sql server version or level, as changing the level may affect some functionality available in that database engine version only.
You can set the backward compatibility level for older version of a database in SQL Server Management Studio (SSMS) by following these steps:
Connect to the SQL Server instance in SSMS.
Expand the Databases folder.
Right-click on the database you want to modify and select Properties.
In the Database Properties window, select the Options page.
Scroll down to the Compatibility Level option and select the desired level from the dropdown menu.
Click OK to save the changes.
Alternatively, you can also use a T-SQL command to change the compatibility level between multiple versions of a database:
USE [database_name]
GO
ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = {compatibility_level}
GOReplace "database_name" with the name of the database you want to modify, the appropriate query and "compatibility_level" with the desired level number. For example, to set the compatibility level to SQL Server 2012 (110), you would use:
USE [database_name]
GO
ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = 110
GOMake sure to test your application after changing the compatibility level, as it may affect the behavior of certain features and queries.
When Does Changing compatibility levels Take Effect?
Changing the compatibility level of a database takes effect immediately for new connections to the specified third source database compatibility level. For existing connections, the new database compatibility level setting will take effect after the next time the connection is established.
When the compatibility level is changed, it affects the behavior of certain database features and query optimizations. Therefore, it's important to thoroughly test the database and any applications that use it after changing the compatibility level to ensure that everything is still functioning as expected.
Note that changing the compatibility level of a database is a one-way operation. Once you change the compatibility of database level down to a lower version, you cannot change it back to a higher version. Therefore, it's recommended to backup the database before changing the compatibility user database level to lowest supported version to ensure that you have a copy of baseline data in the database at the previous level.
Troubleshooting issues when working with SQL Server Compatibility Levels
When working with SQL Server 2012 Compatibility Levels, there are some issues that may arise, and here are some troubleshooting tips to help you resolve them:
Queries not working: If you're experiencing issues with queries not working after changing the compatibility level, it may be because some query syntax or behavior has changed. Check the Microsoft documentation for the specific compatibility level to identify the changes in syntax and behavior that may affect your queries.
Poor query performance: If you're experiencing slow query performance after changing the compatibility level, it may be because the query optimizer is using a different execution plan. Try updating the statistics for the affected tables, and check the query plan to identify any changes in the execution plan.
Missing features: If you're missing features after changing the compatibility level, it may be because the feature is not supported in the new level. Check the Microsoft documentation to identify the features that are not available in the new level.
Backup and restore issues: If you're having issues restoring a database backup after changing the compatibility level, it may be because the backup was created at a higher compatibility level. In this case, you will need to either restore the backup to a higher compatibility level instance or change the compatibility level of the target database to the level of the backup.
Application compatibility issues: If you're experiencing compatibility issues with an application after changing the compatibility level, it may be because the application was designed to work with a specific compatibility level. Contact the application vendor or developer for guidance on the compatibility level to use with the application.
Upgrading the compatibility level in SQL Server can be done using the ALTER DATABASE command. Here are the steps to upgrade the compatibility level of a database:
Connect to the SQL Server instance using a tool like SQL Server Management Studio.
Right-click on the database that you want to upgrade the compatibility level for and select "Properties".
In the "Options" tab, you will see a "Compatibility level" drop-down list. Select the desired compatibility level from the list.
Click "OK" to save the changes. This will generate a script that you can review and execute to upgrade the compatibility level.
Alternatively, you can use the following T-SQL command to upgrade the compatibility level of a database:
ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = [compatibility_level]
Replace [database_name] with the name of the database you want to upgrade and [compatibility_level] with the desired compatibility level number of model database (e.g. 100 for SQL Server 2008, 110 for SQL Server 2012, etc.).
Restoring And Compatibility Level
When restoring a database backup to a different SQL Server instance or a database restore to different version of SQL Server or version, it's important to consider the compatibility level of the restored database.
If the database backup was taken on a SQL Server instance with a higher compatibility level than the target SQL Server instance, the restored database may not be accessible until the compatibility level of user database is changed. In this case, you can change the compatibility level of sql server on the restored database using the same steps mentioned in my previous answer.
To change the compatibility level when restoring a database, you can use the WITH REPLACE and WITH MOVE options in the RESTORE command. Here's an example of how to restore a database backup and change its compatibility level:
RESTORE DATABASE [database_name]
FROM DISK = 'C:\backup\database_backup.bak'
WITH REPLACE, MOVE 'logical_data_file_name' TO 'C:\data\database.mdf', MOVE 'logical_log_file_name' TO 'C:\data\database.ldf', STATS = 10,
-- Change the compatibility level to SQL Server 2016
MOVE 'logical_data_file_name' TO 'C:\data\database.mdf', MOVE 'logical_log_file_name' TO 'C:\data\database.ldf',
RECOVERY, STATS = 10, REPLACE, NORECOVERY,
-- Change the compatibility level to SQL Server 2016
MOVE 'logical_data_file_name' TO 'C:\data\database.mdf', MOVE 'logical_log_file_name' TO 'C:\data\database.ldf',
COMPATIBILITY_LEVEL = 130;
In the example above, the database backup is restored with the WITH REPLACE option to overwrite the existing database, and the WITH MOVE option is used to specify the file paths for the data and log files. The compatibility level older sql server versions is then changed to SQL Server 2016 using the COMPATIBILITY_LEVEL option.
It's important to note that changing the database compatibility level used when restoring a database may require updates to database objects that rely on features that are no longer supported at the new compatibility level. Additionally, changing the database compatibility level may also affect the performance of certain queries. As such, it's recommended to test the impact of changing database compatibility levels first in a development or test environment before making changes in a production environment.
It's important to note that upgrading the compatibility level of a database may require updates to database objects that rely on features that are no longer supported at the new compatibility level. Additionally, changing the database compatibility level may also affect the performance of certain queries. As such for even more performance improvements, it's recommended to test the impact of changing various database compatibility levels first in a development or test environment before making changes in a production environment.
In general, it's important to thoroughly test any changes to the compatibility level and ensure that everything is functioning as expected before deploying new version to production.
What is the difference between setting a compatible level and setting up a contained database?
Setting a compatibility level and setting up a contained database are two different concepts in SQL Server.
Setting a compatibility level refers to changing the version of the SQL Server Database Engine that a database will be compatible with. This affects the syntax and behavior of certain queries and features. For example, if you set the compatibility level of a database to SQL Server 2016, certain features that are not available in SQL Server 2016 will not be supported in that database. Setting the compatibility level is typically done to maintain compatibility with an older version of applications or to take advantage of new features introduced in a newer version of SQL Server.
On the other hand, setting up a contained database is a way to isolate a database and its associated users from the rest of the SQL Server instance. In a contained database, the database includes all the metadata and security information needed for the database to function independently, without relying on the entire server-level configuration. This includes database-level logins, instead of relying on server-level logins, and authentication mechanisms such as passwords or certificate-based authentication, rather than using Windows authentication. Contained databases are useful when you need to move the database to a different SQL Server instance, as it minimizes the amount of configuration that needs to be done on the new instance.
What is a contained database (Overview)
A contained database is a type of database in SQL Server that includes all the metadata and security information needed for the database level control, to function independently, without relying on the server-level configuration. This includes database-level logins, instead of relying on server-level logins, and authentication mechanisms such as passwords or certificate-based authentication, rather than using Windows authentication.
Contained databases are useful when you need to move the database to a different SQL Server instance, as it minimizes the amount of configuration that needs to be done on the new instance. With a contained database, you can simply move the database to the new instance, and the database will continue to function with its own configuration and security settings.
In a contained database, all the metadata and configuration information is stored within the database itself, rather than in the master system database, which is the case with traditional SQL Server databases. This allows for greater portability and flexibility, as the database can be moved between different SQL Server instances without having to configure the new instance to match the configuration of the original instance.
Contained databases can be created using the SQL Server Management Studio or using Transact-SQL statements. They are supported in SQL Server 2012 and later versions.
In summary, a contained database is a type of database in SQL Server that includes all the metadata and security information needed for the database to function independently, without relying on the server-level configuration. This allows for greater portability and flexibility when moving the database between different SQL Server instances.
You can set up a contained database in SQL Server using either SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL) statements.
Using SSMS:
Open SSMS and connect to the SQL Server instance where you want to create the contained database. Right-click on the Databases folder and select "New Database" from the context menu.
In the New Database dialog box, enter the database name and select "Contained database" under the Compatibility Level option.
Choose the default options for the rest of the settings and click OK to create the contained database.
Using T-SQL:
You can also create a contained database using T-SQL statements. Here's an example:
CREATE DATABASE [database_name]
CONTAINMENT = PARTIAL;
In this example, replace [database_name] with the name you want to give the contained database. The CONTAINMENT = PARTIAL option specifies that the database will be a contained database.
Note that when creating a contained database, you may need to configure the necessary authentication settings, such as setting up contained database users and configuring the authentication method. You can do this using T-SQL statements or the SSMS graphical interface.
It's also important to note that not all features and options are supported in a contained database, so you should check the documentation to see what limitations apply before creating a contained database.
Working with SQL Server database compatibility levels can be complicated and requires careful testing to ensure that everything is working as expected. By understanding the differences between each and latest database compatibility level, you will be able to choose the right one for your needs and avoid any compatibility issues or query performance problems. Additionally, it’s important to keep in mind that changing a database's compatibility level is a one-way operation so it’s always best practice to back up your database before making changes. With these tips in mind, you should have all of the information needed to successfully work with different SQL Server Compatibility Levels.
Other Resources
Aliasing SQL instances https://www.bps-corp.com/post/sql-instance-aliasing-in-sql-server System Databases https://www.bps-corp.com/post/a-guide-to-the-system-databases-in-sql-server Database Ownership
https://www.bps-corp.com/post/what-is-database-ownership Set Compatibility Level
Contained DB