Running older SQL Server (< 2017) databases on Cloud SQL for SQL Server

If you have databases running on SQL Server instances with versions older than 2017, you may be wondering if you can run those databases on Cloud SQL and how. 

Let’s break the SQL Server footprint down into three distinct parts: 

1. Edition refers to the licensing level of SQL server - of which Cloud SQL currently supports Standard (default), Enterprise, Express, and Web. 

2. Version refers to the release of SQL server: 2019, 2017, 2016, 2014, 2012, 2008r2, etc. 

3. Database Compatibility Level refers to the version of SQL Server with which the database is to be made compatible. 

With the recent release, CloudSQL now supports SQL Server 2019 in addition to SQL Server 2017. This means that when creating a Cloud SQL instance, it spins up a SQL Server instance with the version (either 2019 or 2017) and edition of choice (as selected during the creation). 

SQL Server has always been great at maintaining compatibility with the older versions to ensure users aren’t limited to the version they first created their database on, and can use the newer features without running into unexpected performance issues. This is achieved using a feature called the Database Compatibility Level. It provides backward compatibility with earlier versions of SQL Server in what relates to Transact-SQL and query optimization behaviors only for the specified database, not for the entire server.

Simply put, a Database Compatibility Level allows running a SQL Server database created on an older supported version of SQL Server on a newer version SQL Server instance without introducing breaking changes. The only caveat here is that any functionality that was removed from the database engine in the newer version or dependencies on legacy server objects/functionality would be unavailable to the restored database, irrespective of its Database Compatibility Level. For more details, see here. 

This means that a SQL Server 2008 database, for example, can be restored to Cloud SQL for SQL Server. The restored database will default either to the Database Compatibility Level of the original server, or the lowest supported level on the destination server, whichever is higher. Since the default Compatibility Level of SQL Server 2008 is 80, which is also the lowest supported Compatibility Level for SQL server 2017 and 2019, the database will keep the Compatibility Level of 80. By comparison, a database newly created on Cloud SQL will have the default Compatibility Level of the selected SQL Server version (150 for SQL Server 2019 and 140 for 2017). Here is a list of SQL server versions and their default and supported Compatibility Levels. 

As a result, the restored database with an older Compatibility Level would still be able to leverage the improvements in the newer SQL Server database engine ( like Clustered Columnstore Index, Dynamic Data Masking etc.) without running into unexpected query performance changes triggered by query optimizer improvements. To unlock those specific query optimizer improvements, the Compatibility Level of the database must be increased as indicated by the SQL Server documentation. More details on breaking changes per Database Compatibility Level and implications of Database Compatibility Level changes can be found in these Microsoft documents. [1] [2] 

Example 

Let’s say we want to move our SuperImportantSQLDB to Cloud SQL. We check its Database Compatibility Level by querying the database-scoped table sys.databases as follows - 

We backup and restore the database to a SQL Server 2019 instance of Cloud SQL by following the steps mentioned here. 

Once restored, we check the Compatibility Level again to ensure it has remained the same, while the SQL Server version (checked with ‘SELECT @@VERSION’) has now changed to SQL Server 2019.  

An easy way to see the impact of the Compatibility Level is to run a query and look at the Cardinality Estimator (CE) version that the Query Engine is using. A CE essentially predicts how many rows a query will likely return. The CE version 70 (introduced in SQL Server 7.0) remained unchanged until SQL Server 2014. Since then, every new SQL Server release has an associated CE version (120 for SQL Server 2014 and so on). Since our database is at 110 Compatibility Level (SQL 2012), the Query Optimizer uses the older CE version (70). For a database with a Compatibility Level of 150, the Query Optimizer would instead use the CE version 150. 

As mentioned earlier, the great thing about Compatibility Mode is that in this case, while the database would see the same query optimizer behavior as before, we can still use features introduced in SQL Server 2019. One such feature is the ability to create an index online as a resumable operation which allows transaction log truncation and reuse during the index creation. 

If we wanted to try a newer query optimizer feature like Adaptive Query Processing, we may want to do so in an incremental fashion, starting with our least critical database as opposed to upgrading the entire server at once. We could accomplish this by changing the Compatibility Level of individual databases, one at a time. This is especially useful in the case of mixed-use SQL instances which may have to support databases in multiple versions depending on the DB use-case. See here for list of versions and supported compatibility levels. As with ANY database change, testing is a critical part of the process.  

To change the DB Compatibility Level, we issue the ALTER DATABASE command and specify the desired compatibility level.

Closing Thoughts

SQL Server’s Database Compatibility feature plays an important role in planning a migration from older SQL Servers to Cloud SQL. Not only does it support running previous database versions on Cloud SQL, it provides a graceful and controlled path to modernization for legacy applications by allowing DBAs and Devs the ability to enjoy the benefits of Cloud SQL’s managed platform, while preserving legacy database functionality.