Considerations before changing Compatibility Level in SQL Server

You’ve done an upgrade on your mission critical database thinking that the latest version/release would help you overcome all the problems in world, and then realize that there’s a huge performance dip when compared between the older and newer version of your SQL Server Databases. Queries which used to run lightening fast on your lower version are now taking minutes and this can be a frustrating experience for your customers, business teams.

Note that with every database upgrade, compatibility level changes – for example, with SQL 2008 the Compatibility Level was set to 100; in SQL 2012 to 110; in SQL 2014 it’s set to 120; and today, with SQL Server 2016 Compatibility Level to 130.

Although most workloads would benefit from the upgraded Compatibility Level, a few considerations before changing ‘that’ compatibility level in your SQL Server database.

  1. You move to the new Compatibility Level post upgrade, and see how things perform. In case you notice some regressions, you just simply set the Compatibility Level back to its original level, or keep the upgraded one, and only reverse the Cardinality Estimates back to the legacy mode.
  2. If you have multi-tenant applications spanning across multiple databases, it may be necessary to update the provisioning logic of your databases to ensure a consistent Compatibility Level across all databases; old and newly provisioned ones. Your application workload performance could be sensitive to the fact that some databases are running at different Compatibility Levels, and therefore, Compatibility Level consistency across any database could be required in order to provide the same experience to your customers all across the board. Note that it is not a mandate, it really depends on how your application is affected by the Compatibility Level.
  3. Regarding the Cardinality Estimates, and just like changing the Compatibility Level, before proceeding in production, it is recommended to test your production workload under the new conditions to determine if your application benefits from the Cardinality Estimates improvements.
  4. There are improvements with every upgraded version of SQL Server but if for some reason your workload is not tested fully with the higher version and giving you performance headaches, you should think of using “Alter Database” command, change it’s compatibility level and see the behavior.

Hope this helps!

Happy learning!

Aman Kharbanda






A/B testing solution for SQL Server upgrades

Many of you might’ve come across a situation wherein you’re planning for a SQL Server upgrade, and at the same time, skeptical of the performance/throughout that you’re going to get from this upgraded version of database.

Database Experimentation Assistant (DEA) is a new A/B testing solution for SQL Server upgrades. It will assist in evaluating a targeted version of SQL for a given workload. Customers who are upgrading from previous SQL Server versions (SQL Server 2005 and above) to any new version of the SQL Server will be able to use these analysis metrics provided, such as queries that have compatibility errors, degraded queries, query plans, and other workload comparison data, to help them build higher confidence, making it a successful upgrade experience.

Current available version (As of this writing) is 2.1.51169.2

Screen Shot 2018-01-22 at 11.49.54 AM.png

Supported sources and target versions

Source: SQL Server 2005 and above
Target: SQL Server 2005 and above
Analysis: SQL Server 2008 and above

Happy learning!

Aman Kharbanda

Microsoft® Data Migration Assistant v3.3

Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server. It recommends performance and reliability improvements for your target environment. It allows you to not only move your schema and data, but also uncontained objects from your source server to your target server.

Here’s the download link

What is new in V3.3?

  • DMA v3.3 enables migration of an on-premises SQL Server instance to SQL Server 2017, on both Windows and Linux.



  • Migration blocking issues:  DMA discovers the compatibility issues that block migrating on-prem SQL Server database(s)s to Azure SQL Database(s). It then provides recommendations to help customers remediate those issues.
  • Partially or unsupported features:  DMA detects partially or unsupported features that are currently in use at the source SQL Server. It then provides comprehensive set of recommendations, alternative approaches available in Azure and mitigating steps so that customers can plan ahead this effort into their migration projects.
  • Discovery of issues that can affect an upgrade to an on-premises SQL Server. These are described as compatibility issues categorized under these areas:
    • Breaking changes
    • Behavior changes
    • Deprecated features
  • Discover new features in the target SQL Server platform that the database can benefit from after an upgrade. These are described as feature recommendations and are categorized under these areas:
    • Performance
    • Security
    • Storage

Supported source and target versions

  • Source: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016
  • Target: SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, and Azure SQL Database

Screen Shot 2018-01-05 at 11.39.44 AMScreen Shot 2018-01-05 at 11.40.18 AMScreen Shot 2018-01-05 at 11.42.28 AM


Happy learning!

Aman Kharbanda


SQL Operations Studio (Public Review) is now available

SQL Operations Studio is now available. Here’s the link to download and install it’s December (Latest release as of this writing) release:

Get SQL Operations Studio (preview) for Windows

This release of SQL Operations Studio (preview) includes a standard Windows installer experience, and a .zip:


  1. Download and run the SQL Operations Studio (preview) installer for Windows.
  2. Start the SQL Operations Studio (preview) app.

.zip file

  1. Download SQL Operations Studio (preview) .zip for Windows.
  2. Browse to the downloaded file and extract it.
  3. Run \sqlops-windows\sqlops.exe

Get SQL Operations Studio (preview) for macOS

  1. Download SQL Operations Studio (preview) for macOS.
  2. To expand the contents of the zip, double-click it.
  3. To make SQL Operations Studio (preview) available in the Launchpad, drag to the Applications folder.

Supported Operating Systems

SQL Operations Studio (preview) runs on Windows, macOS, and Linux, and is supported on the following platforms:


  • Windows 10 (64-bit)
  • Windows 8.1 (64-bit)
  • Windows 8 (64-bit)
  • Windows 7 (SP1) (64-bit) – Requires KB2533623
  • Windows Server 2016
  • Windows Server 2012 R2 (64-bit)
  • Windows Server 2012 (64-bit)
  • Windows Server 2008 R2 (64-bit)


  • macOS 10.13 High Sierra
  • macOS 10.12 Sierra


    • Red Hat Enterprise Linux 7.4
    • Red Hat Enterprise Linux 7.3
    • SUSE Linux Enterprise Server v12 SP2
    • Ubuntu 16.0

Allows us to connect well to the DB and it gives a nice feel of GUI integration.
I’ll pen down more in next writeup about this tool!

    • Screen Shot 2018-01-04 at 5.32.35 PM.png


Happy learning!

Aman Kharbanda