Link to home
Create AccountLog in
Avatar of Jerry Seinfield
Jerry SeinfieldFlag for United States of America

asked on

Migrate SQL server databases to latest version on prem

Hello Experts,

Looking for some real expertise here for SQL admins or DBA. I need to build a POC to migrate 5 LOB applications from their current infrastructure (Windows 2008 - 2012- 2016, SQL 2008 - 2012- 2016) to a newer infrastructure that will replace current one (Windows 2022 for the server OS and either SQL 2019 or 2022).

Can someone please provide high level steps to migrate all databases in the clients entire sql environment?

Any best practices, or advices?

Avatar of arnold
Flag of United States of America image

What is the environment physical servers for each reference, or a virtualize environment of the newer systems?

HW spec available?

Here is the point are you in an upgrade cycle of some hardware that is older than 10 years old?

Web based apps?
IIS changed, became more granularity configurable... testing and verification...

Avatar of Jerry Seinfield


All our SQL server are VMs hosted in a VMware infrastructure, so essentially we will handle the Vmware upgrade, but need your expertise for the SQL side of things, i.e.

Backup databases, export database, build new SQL, create disk partition for SQL logs, and databases, import databases, test the app

These are web apps, and some of them are client server where code is Visual Basic

Please, provide details

Any updates so far?

I am against in-place uolpgrades OS, sql, etc.
Reusing the IPs.
(Main issue one way path)
Setup a new VM with the new OS, sql server. Restore data from backup. The VM test that will have the app in question. That will be accessing the data on the new server/sql to confirm operations.

This provides the testing environment.
You also have a test of how long it takes to restore, not accounting for production, resource prioritization.
Sql should have a higher hypervisor priority for VPN, memory, storage.

Do an app at a time.

If your intent only to update the sql side, clone one of the app servers, target it at the new server/sql to confirm the app will work without the need to update the sql ncli ...

Multiple hosts, sql server on the host currently not hosting the sql server..

If you have interdependence of databases that has to be transitioned at the same time, the test will coter that.

Thanks Arnold,

Any chance that you can share a blog or link that supports your statement? I'd like to get a better understanding on the whole process.

Thank you in advance

What do you mean, to which aspect.

An in-place upgrade is amazing when it goes through, but what are the ramifications contingencies when the upgrade fails, or it goes through but the underlying services, interactions do not go as expected?

In a scenario server 2012, sql 2012 would you go to server 2016/sql 2016 or server 2019/2022 sql server 2019/2022?

Are you using server core to reduce resources?

Went down this road once, yhe outcome was not seemless and the work to restore from backup server first, than databases.......

Was not pleasant.

It is not dissimilar from people choices of components and vendors. If you went through it and it was successful, you'd repeat until ....
Once you go through an unexpected difficulty, you might ever try it again, as in my case.

VM avails option to test on a clone if so inclined. In your case which will you do first, upgrade sql or server. What are the possible conflicts for either?

If not mistaken, there was a situation that server and sql had a conflict requiring an application of a specific update....

Ok, Arnold, looking for best practices here. 

Mi situation is, I do have several applications (web and client/server) using multiple databases across several SQL servers with all versions that I had mentioned.

My plan is to build a brand new server OS Windows server 2022, and deploy SQL either 2019 or 2022, then import data from other SQL databases and apps. I would like to keep a centralized SQL database server running latest version if possible, and set best SQL practices

Is that the best practices? if not, could you please highlight the steps here, so I can draft a plan that involves best practices for SQL upgrades?

No HA for SQL or SQL AAG for resilience so far, but that might be implemented in the future, for now let's focus on how to move all SQL dB's from legacy infrastructure to new one

Thank you in advance

That seems fine, the thing to consider since you have sql 2008, you have to go through an intermediary step to be in a position to consolidate it under sql 22.
2019 should work, provided the DB properties options, compatibility is SQL server 2008, 100 not 80 nor 90.

best practices applies in different ways
in your situation having a seemingly dispersed setup with different sql versions, applications, etc.
The Best Practices you seem to be after is how to consolidate the current environment onto a single SQL server running a newer version.....

you have to break down the DB, activity, size, resource demand....
To make sure the cumulative of your existing can run on the newer based on storage, memory, cpu resources. and include growth of both storage and demand for the next five years.

As I noted, you would likely have to deal with one DB/App/ transition at a time

If you have a virtual Environment, perhaps not consolidating is wiser. i.e. shifting a VM SQL and a VM with related App, to a new host when the existing host resources can not withstand the demand, versus trying to migrate a DB/App to a new host, new VM.////

I may be reading too much into the possible scenarios

From renewal point, I would think the first thing would be to migrate to the newer, then have the DBA look at utilization and see whether the new engine can implement changes to improve performance.

Personally, I would stay away from trying to do everything in one shot.

Avatar of lcohan
Flag of Canada image

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