Link to home
Create AccountLog in
Avatar of William Fulks
William FulksFlag for United States of America

asked on

SQL Upgrade Best Practices

I have been tasked with upgrading an old app that uses an application server and a SQL database server. We are currently running SQL 2008 (I know it's ancient!) and will be moving to SQL 2019.

Rather than do a bunch of sequential in-place upgrades, it seems like the easier fix would be to stand up a new database server with a fresh install of SQL 2019 and then do an export from the old server and import to the new one. Am I correct in this?

There's over a terabyte of data in SQL. I did a test run of an in-place upgrade to SQL 2012 and it took @ 6 hours just to pass all the tests before even allowing the upgrade. I cancelled at that point to reschedule for a weekend since it is a production server.

If I go with a new database server, I get that I will need to copy over permissions, settings, etc. What about the databases themselves? Will there be a conversion process of any kind when I import a 2008 export into SQL 2019? Or should I do the SQL 2012 upgrade first?

Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Based on some "Google research", I think you can restore directly from SQL 2008 to SQL 2019.  I suggest running the MS's (free) compatibility tool to check for potential issues first.

Be sure to adjust the compatibility level after the restore.  You might as well start running at the latest level asap.

Usually a straight backup and restore of the database is all that is required. 

I would check fo any other features used such as Views, triggers, server links, agent settings and scripts, other other unusual items.

Also, any critical tables could be exported as CSV or Excel spreadsheets in case there are problems migrating tables.

Logins and User accounts are set up seperately, there is a MS script to help migrating them:

Transfer logins and passwords between instances - SQL Server | Microsoft Learn

Avatar of skullnobrains

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

Typically you'd a full restore a day (or two or whatever) ahead of time, then for the final conversion you'd do a differential and apply that for the final cutover.  Then the time to recover doesn't depend on how large the original db was, only the amount of data that changed between the last full backup and the diff.  Typically an hour or two per db (at most) will be enough to restore the diff, unless you have a very large amount of activity on the db just prior to cutover.

Avatar of skullnobrains

i am a bit concerned whether differential backups is feasible across versions but that would be an alternative to replication if it does work.

@scott do you know for a fact it is actually feasible ? (i genuinely have no idea, btw)

@william : i presume you can grab a copy of the app and play with the new server for a while ?

A full backup is fully workable across versions, can't see why a diff backup would not be.  Naturally test it out on one small db first.

the reason is full backups can be in sql format while diff backups are as far as i know (and i am no sql server expert) only row based. last time i checked row based backups where not comoatible across versions... but really i am unsure.

Avatar of William Fulks


I appreciate all the input. Going to start working on this soon and will let you know how it goes.

Personally upgrading in-place precludes you from testing the application with the newer sql version.

There are layers of testing that might be involved.

So the new version requires a newer SQL version. I can't test one or the other without upgrading both.

Do you have access to a test environment? Physical/virtualize?

Two changes at the same time?

Does the current version support a newer sql server?

chances are the old app version will work with the new server.

have you managed to copy the data ? setup some replication or other incremental mechanism ?

So we do have a production server and a test server, BUT they tie to a single database server that's running both databases. So we technically do have a test environment but it won't work in this regard because of the version requirements. In other words, the new version is not compatible with what we're current running so I can't have them side by side.

This is an application that previous admins have failed to maintain so we're jumping from a version 2016 to a version 2022 and there's a lot of differences.

Do you have virtualization capacity/capability?

The unknown is whether the existing application will work with the newer SQL version.
The second part if the old application works with the new SQL version, what is involved in the upgrade process from the old application to the new application.

trying to upgrade both the sql server and teh application to the new version in place takes a significant risk if things do not go as planned.

You mentioned your in-place upgrade to SQL went six hours before you ran out of time.

What do you have in the form of resources? potentially a workstation with hyper-v Role, that has adequate additional Storage, 2TB SSD for the new SQL version onto which you restore the production data (for testing the old app with the new DB)
SQL login transfer scripts MS provides. much depends....

When is your Hardware upgrade cycle?
Any thoughts on Virtualization?

i believe you need to upgrade the db first and bother with the app later.

running both versions of sql server alongside is likely not possible though you may double check.

simple testing does not require an unreasonably performant machine and you probably do not even need the full dataset but i cannot guess what hardware is available to you.

if possible, run a newer sql version with the full dataset and leave the app alone in the mean time.

it won't be easy unless you manage to run a separate server of take the risk to perform the update.

note that 1Tb is not that huge and most dbs besides sql server allow such upgrades with next to zero "preparation", conversion steps or whatever fancy word tries to cover the fact they wrapped an otherwise very decent db in some proprietary and ever changing messy bloatware with changing file formats, credential settings, useless (and dangerous) domain integration... with many alternatives, you would just take a snapshot, stop the old version and start the new one using the same db files.

Decided to just take a stab at doing an in-place upgrade and see how it goes. The vendor's support isn't very helpful because they want $20k to do it for us. Nice, right?

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

if you are running a recent enough windows version, you might be able to take a snaphot (a.k.a. shadow copy) if you have no space to take a proper backup.

hope all goes well and you do not spend the week end fighting ;)

20k$ ? i sometimes take less than that to build complete solution designs from scratch ^^

I performed the upgrade yesterday without issue. One mistake I had made on the first try was I did not stop the SQL service (from within management studio) and that's why it took 6 hours to do the health checks and stuff. When I stopped everything the whole process took less than 30 minutes. Thanks again for all the input!