Upgrading a 2008 SQLServer database

When we started to look into upgrading, we already had SQLServer 2017 installed, but the databases were at compatibility 100 (2008). Using the compatibility mode option is a great way of divorcing the version of SQLServer from that of your databases, allowing you to keep the version of SQLServer up-to-date without worrying about breaking your application.

Our testing process for upgrading the databases was to:

  1. Clone the production databases into the sandbox cluster
  2. Re-configure the sandbox application to target these new databases
  3. Use cypress.io to load test the application
  4. Take a SQL trace whilst load testing
  5. Increase the database compatibility option from 100 to 140
  6. Re-run the load tests and SQL trace
  7. Load both traces into Qure profiler to analyse the differences.

It is expected that some queries will perform better, and some might be considerably worse, so this testing is important to ensure we don’t take down the production application!

We also invited our QA test engineer to regression test the application to ensure this change hadn’t unintentionally broken anything; and as luck would have it, there were no issues.

Relevant Reads

SQLServer cardinality estimator; how it was improved in v120 (2014) and why you should upgrade your databases from v100 (2008).

Another big improvement in 2014 was how dirty writes are handled with tempdb:

Other interesting reads:

Popular posts from this blog

Taking a memory dump of a w3wp process

sp_blitzIndex

GitLab Badges