November 20, 2023 at 4:23 pm
Hi all,
I'm planning to migrate a database from SQL Server 2008 (Windows Server 2008 R2) to SQL Server 2019 (Windows Server 2019), I've ran the Data Migration Assistant to assess the move and it came back without any issues, I'm also planning on using this tool for the actual migration.
There are currently multiple reports and automations that pull info from this database, my plan was after migrating the server to either create a CNAME or A record pointing traffic going to the old server (SERVER1) to the new server (SERVER2).
The SQL servers have unique instance names, but the database name will stay the same, for example:
My main concern is that the DNS record redirect will not work properly due to the instance name being different on the new server, if a report was pulling info from SERVER1/DATABASE1, would it still work fine if the DNS record was pointing to the SERVER1 even through the instance name is different?
This server/database was setup many years ago and the employees who created it are no longer around, so unfortunately I can't ask them directly. If this is in the wrong section, please let me know.
November 20, 2023 at 5:18 pm
Don't use CNAMES like that, you are asking for problems. It can break anything that is encrypted and/or implements mutual authentication.
you could try registering another A record for the old hostname, pointing it to the new server and then moving the relevant SPNs. test that first though, i've never done that. When I have had a requirement to reuse a server name, I recycled the old SQL Machine account in AD.
November 21, 2023 at 8:07 pm
I could be dumb, but I am pretty sure that "server\database" is not a valid way to connect to a SQL instance. It needs to be server\instance. Otherwise how does SQL know what instance to connect to if your server has multiple instances.
I do agree with CreateIndexNonclustered though but I would to a step further and say that CNAME AND A records both pose issues as neither are "instant" changes. What I mean is the DNS records is cached on the local machines for faster lookups of addresses. And if you have multiple DNS servers (for redundancy in case one fails), you also have to wait for the changes to replicate to each DNS server you have.
A better approach from my experience is to ALWAYS use the server\instance name when connecting and when doing a migration like this, update the apps that need it. It reduces the risk of some system pointing to the old server due to something like using the IP or the FQDN instead of the friendly name or using cached values. And SPNs can be a pain in the butt as they too take time to replicate across systems. With the approach of updating everything, you can schedule downtime and swap things over and it is EASY to test that everything migrated by turning off the old instance and see if all apps work and doing a sample of some reports. Changing the DNS records it could fail because of SPN issues or it could fail due to caches or waiting for it to replicate. It is one reason I really like having failover software in place. We use DxEnterprise, but you can use whatever you like. That way the virtual host (VHOST) is the same, and the instance name can be the same I can do the upgrade on the offline hosts then force a failover, do the upgrade on the primary, and fail back and I'm only offline for about 2 minutes. If I need to migrate to a new server/VM, I can just fail over to the new one. If I need to do an upgrade, it is a bit more involved as I need to do an uninstall/install and do a few tweaks in the failover software to make it happy again, but no DNS changes required and downtime can be light.
BUT if you are really curious about the process and risks - try it out on a test system and see how it goes. That also allows you to document any odd things that you run into that you don't want to find out during the live migration. For example, if this is your SSRS instance being migrated, make sure you know the steps to restore the encryption keys from a 2008 to a 2019 box as the encryption algorithms changed. I got hit with this when I was planning on my SSRS upgrade. I thought it would be trivial and it failed on the test system due to the encryption algorithm change. I believe that applies to all certificates actually, not just the SSRS ones... so if you have certificates on the 2008 box, make sure you have a backup and know the process to upgrade them to ones supported by 2019 without data loss.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply