May 29, 2020 at 1:20 am
Hello Gurus,
Task is to move a 500 GB DB from lower version ( source ) to a brand new server ( destination- SQL Server 2019 ) .
Plan is to use logshipping. Take a full backup on Source , restore full backup on Destination with Standby / ReadOnly .
Tlog backups will automatically be done on source , copy and restored on Destination
On Friday , say users stop using the Primary at 5 PM on the source , can I just go the destination, at around 7 PM , change the destination from ReadOnly to Readwrite and it will be all set ? or any other gotchas?
How to handle the server name change ? App config files will have source name in the files. If I shut down the source, rename the destination and source , what about SQL instance name etc ? Thanks in Advance,
May 29, 2020 at 7:19 am
"How to handle the server name change ? App config files will have source name in the files. If I shut down the source, rename the destination and source , what about SQL instance name etc ?"
This part is easy: I have done this several times using SQL Server aliases, with these no changes in app config files are necessary. You can find a description here:
https://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/
May 29, 2020 at 7:33 am
Alias requires you to change EVERY single client - its clumsy and not desirable if it can be avoidable
If the old server is not going to be used then a possible way is using DNS Alias is one way - another is for at go live date to change the old name to point to the new IP
If both servers are going to be active and both with SQL installed then you have to change the configs (Moving one database does not mean moving a server)
May 29, 2020 at 7:42 am
https://docs.dbatools.io/#New-DbaClientAlias
Maybe clumsy, but works.
(But of course there is always potential for optimisation.)
May 29, 2020 at 9:04 pm
I know we use SQL Aliases at my work and they can be a pain. They are nice once they are set up and working, but what if a developer put the server name, instance name and port in the config file instead of using the alias? Or what if a machine needs the new alias and it is offline?
We manage aliases through logon scripts (vbscript) so when a user logs onto a machine, they get the new aliases. This worked great until work from home started and the logon script doesn't run when you connect to the VPN. End users need to run it manually after connecting to the VPN. As such, my recommendation is to avoid aliases if you can.
If it was up to me, I'd have database connection strings in the app config for the applications, have a tool or process so all in-house apps are stored on a network share and copied locally at runtime along with the config file, and when you go to migrate the instance to a new server, you update the application config files at the same time
That is, if the names of things are changing. If your physical servers/VM's will have the same name at the end (old one being shut off and\or renamed and new one named the same as the old) AND the instances will have the same name on both servers, you should have no issues connecting to them assuming firewall rules are set right. End users shouldn't notice the upgrade.
Some non-server name/instance name gotchas though are you may have better or worse performance by upgrading to the latest and greatest. 2019 also has some new features you can use which may be beneficial (like query store).
I know when I did a similar thing (migrated a SQL instance to a new physical box), I left the instance names the same and failover software to handle the data migration. I was not confident enough in the code on the database to do an upgrade to the SQL instance at the same time as the server upgrade (windows server 2003 to 2012 R2), so we kept the versions the same. Failover software made it simple though. Install new instances on the new server, add server to failover, fail things over. Repeat for all VHOSTS and we were happy. Any failover fails, put it back on the original server until we get it sorted out.
We ended up updating software at the same time as we had some tools written in VB6 that were hard-coded to connect to the "server/instance, port", so changing servers made a minor additional step, but it was just renaming 2 folders on a network share. "AppRepo" and "AppRepoNew". Anything with an application config file had that updated, anything that was hard-coded had that updated. Got a downtime window of a weekend (as I like to have time to troubleshoot and rollback if neccessary) and end users didn't notice a thing. Worked on Friday, and worked the following Monday.
Failover software was DxEnterprise. I do not work for them and am not endorsed by them, I just really like the product. Similar to Polyserve from HP back in the day, but a lot more advanced than that was.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply