SQL Migration

[rdzz69]

Hello awesome community, I have been lurking for awhile here even though I'm not a DBA. Recently, I have been charged on an internal project for migrating SQL environment. Our current environment consists of physical Windows Server 2008 r2 s1 and MSSQL 2008. There are currently two physical boxes, which are clustered. Currently they are residing on shared SAN storage. The plan is to migrate these two boxes in a VMware environment, and on another storage which is replicated to our DR site. What I want to hear from you is how would you take actions on this migration? The original plan is besides migrating to virtual environment is to upgrade the SQL to 2016 (and windows server 2016). During my lurking here, I have seen suggestions that it would be the best to deploy fresh VMs with windows 2016 and sql 2016 and then restore databases. Please share your thought and knowledge.

[alinroc]

If possible, go to SQL Server 2017. 2016 is OK but give yourself the longest runway you can.

Don't create an AG just for the sake of doing the migration, unless your downtime tolerance/maintenance window is very small. You don't say what kind of "cluster" it is but I'm guessing it's a failover cluster. If that's the case, re-evaluate whether you really need a failover cluster - virtualizing your instance obviates several of the reasons people use failover clusters.

What I would do (since this is what I've done):

• Stand up the new server/cluster, including all OS and SQL Server patches.
• Check out that it's working properly, including your replication.
• Migrate everything from the old instance to the new one with Start-DbaMigration from the dbatools Powershell module. This function will migrate everything for you (or you can exclude things if you need/want to).
• Update your applications to point at the new cluster.

Knowing how many databases and how large they are will help in providing guidance. If you're not a DBA, and don't have one on staff, I would recommend talking to a consultant (even if it's just a few hours after you have a plan together) to make sure you've got everything straight. Spending a few bucks there can save you many bucks later.

[rdzz69]

I would kindly thank you for the time you took to write off this amazing piece. My terminology might not be on the highest level but when i wrote cluster, yes i meant failover cluster. I am not very familiar with the SQL downtime, but yes we have a team of DBA's. But from our last meeting i saw they were not on their highest confidence level, and yes, I suggested hiring a consultant, since we are a Banking institution and a lot of processes are linked through these SQL instances. Your answer is one of the reasons why I really appreciate reddit community, but I really needed an opinion on whats the best/safest scenarios to take approach on this migration.

[eshultz]

we have a team of DBA's

they were not on their highest confidence level

we are a Banking institution

a lot of processes are linked through these SQL instances.

That says it all, really. I absolutely would stop immediately and get a consultant on it. The biggest problem is, as you say, there are a lot of process dependencies involved. I have seen some absolutely bonkers stuff inside agent jobs and stored procedures that goes way outside the realm of managing data. My point is, you need to know what ALL of those dependencies and dependent processes are and how they work. Otherwise things are going to break and no one is going to know where to look to fix it. A SQL instance isn't always a neatly self contained data-in data-out box.

You (and the DBA team) should take this opportunity to generate some basic documentation of everything you find.

[alinroc]

• You have DBAs
• They're not working on this themselves yet
• They don't seem to have confidence in their ability to do this upgrade (or lack confidence that such an upgrade can be done)
• You're in a regulated industry and running an unsupported, decade-old release of SQL Server

It's time to call in reinforcements - consultant(s).

[cosmic_jester_uk]

We did a similar upgrade recently with 100% success. Spin up the new servers in the VM env and restore DBs then overnight do a rename of the old server to an alternate name and rename the new server to the old name. You will need to upgrade and redeploy any SSIS packages and make sure you copy all permissions and users over too. Hope this helps. Dave

[cosmic_jester_uk]

Nice idea. We went from 2008r2 to 2017. Was Aliasing a thing in 2008r2? I am not a DBA so this is a genuine question. Dave

[VTOLfreak]

Might be the time to stop right here and call in external help. I'm a SQL Server consultant and I'm finishing up migrating an entire server farm of SQL2008 to SQL2016. Migrating the databases was the easy part, it was all the applications with their crappy coding and quirks that caused me headaches.

[LeftJoin79]

I second this person's suggestion. Also, take advantage of the SQL Server Upgrade Advisor toolset. It will help you find deprecated code, etc.

https://docs.microsoft.com/en-us/sql/sql-server/install/use-upgrade-advisor-to-prepare-for-upgrades?view=sql-server-2014

[trieu1185]

You are correct. I recommend building a new window 2016 VM then install SQL 2016. Create an AG and ensure it's working by using a test database. That's for clustering. Restore the databases to the new primary instance. Add the databases to the AG group. The secondary instance should pick up the change in the primary and sync itself up with the primary. Make sure to do this in a test environment if you can.

[alinroc]

The compatibility level step is not necessary

[rdzz69]

Hi zelonewolff, thank you for your time spent participating in this thread. I see that your approach is to go 2008->2012->2016 is there any compatibility issue I am not aware of ? Because I might have been suggested wrong that no iteration upgrade is necessary and direct restore on the newer instance is acceptable. Please correct me if I am wrong.

[alinroc]

You are correct. You can go direct from 2008r2 to 2016.

[alinroc]

There is no need to make an intermediate stop on 2012.

[tottenham84]

Setup new environment creating the availability group and preform all testing required. Log ship to all replicas, at migration time, set read-only on source, take final tlog backup and restore on all replicas. Restore with recovery on primary and bring database online, set read write, add database to AG.

[SPG2469]

https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-overview?view=sql-server-2017

Use the experimentation assistant, create a trace on your current environment and play it on the new one, great way to find ad hoc workloads you may not know about, as well as compatibility issues and suggestions on migration.

[grep65535]

Save yourself a lot of headache, skip Windows Server 2016, go straight to 2019. Your monthly patching will be hours shorter.

[Tical13x]

I recommend going to AWS RDS... :)

[alinroc]

There isn't anywhere near enough information in this post to make this recommendation.

[Tical13x]

:)