migrating to the latest version of sql server

Question

migrating to the latest version of sql server

m.azizi

Valued Member

Points: 62
More actions
February 18, 2024 at 7:00 am

#4380534

Hello everyone,
We have several SQL Server 2014 instances with databases ranging from 3 to 8 terabytes. We are upgrading our datacenter and we will be moving these databases to new servers with SQL Server 2022. What would you recommend for the best transferring method to minimize or even eliminating downtime? Should we set up a mirror or replication? or just bare the few days to copy the database files to the new location?
Thanks in advance for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

a piece of the puzzle here is connectivity. are you already using cnames to point to the server, so with a simple DNS change, you can say PRODSERVER points to another physical machine?

there are four options i can think of: in order of options, for any migrations that require minimum down time this is my quick list.

1.AAG,(seconds)
log shipping, are the top two,(15 minutes)
backup and restore(budget 4 hours)
detach/attach are low on my list,(budget 4 hours)
replication is barely on the radar as far as a possibility, but it can be done.

So can you set up Always Availability? the most minimal downtime would be measure in seconds during the failover. add SQL2014 on the new server, and once the databases are synchronizing, change the cname to point to the listener IP instead of the server.

Then later we can do the actual upgrade. On the new passive node, do an in place up grade to 2019/2022, patch it, and then failover to the newly patched and upgraded server, then consider in-place upgrading the original node, or evicting it from the AAG.

for a failover time of 15 minutes or so,(assuming you set the jobs to every 15 minutes) you can use log shipping to a higher version of SQL server (SQL2014 to SQL2019/22 works just fine) the databases on the log shipping target are not able to be read until you do the RESTORE DATABASE {whatever} WITH RECOVERY, because the db has to be upgraded to the server version.

in that case, you do these steps:
find the path the log shipping is sending the backups to.

create the BACKUP LOG [DatabaseName] TO DISK =N'\\backupserver\sqlbackups\DatabaseName_tail.trn WITH NO RECOVERY, NOFORMAT,

INIT,

NAME = N'DatabaseName-log Database Backup',

SKIP,

COMPRESSION,

NOREWIND,

NOUNLOAD,

STOP the Log Shipping backup job.
run your script above manually. this puts the db in the RECOVERYING state on the primary.
run the two jobs on the log shipping secondary for Copy logs and restore logs.
issue the RESTORE DATABASE [DatabaseName] with Recovery
confirm any cname changes are working, and consider deprecating the original server. if things are not working, you can rollback by restoring the database back; but because of the upgrade, you cannot "reverse log ship"(setting up new log shipping back to the original host.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

A solid advice from Lovell as usual!

😎

I have used the backup method many times with only a few minutes of downtime, even on relatively slow connections. Suggest that you script and test every step before the actual D-Day. Please keep the full fallback option open as it has saved my bacon few times when communication links have gone down in the worst possible moment 😉

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 3

If performance is your goal: just one huge warning: "Which Version of SQL Server Should You Use?"

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 4

Johan Bijnens wrote:

If performance is your goal: just one huge warning: "Which Version of SQL Server Should You Use?"

Johan has a good point, just as Orwell stated, not all "versions" are equal when it comes to performance!

😎

Hint: https://www.sqlservercentral.com/forums/topic/performance-concernce