June 20, 2019 at 2:24 pm
I've been tasked with moving our production SQL databases to a new server, and in order to minimize the impact on applications we've decided to try renaming the new server to be the same as the old server.
So the plan I've come up with is as follows:
sp_dropserver PROD2;
GO
sp_addserver PROD, local;
GO
There are some obvious issues that I will need to address... for example:
-- We replicate some tables in a database from a third server to PROD - I'll have to set that up again, but that is fairly straightforward.
-- I will be log shipping the ReportServer database but will not setup SSRS until after the server rename, to avoid conflicts within SSRS with the server name. I can have it point to the existing database; we don't have an encryption key.
-- Will need to recreate linked servers to this new instance; relatively simple.
Just wanted to ask if anyone sees any gaping holes in the plan, knows of any gotchas that I've missed, etc. The good news is that we have a test environment that gets cloned off from production every Monday; so I have a space where I can run through the whole process and if things get really wrecked, it goes back to working on Monday (or sooner if we manually re-snap)
June 20, 2019 at 3:28 pm
2a. Set the instance to pause to drain connections (or dbs to restricted user)
2b. Tail log backup of databases
2c. script out all logins/linked servers/jobs (This could be done ahead of time, but a final version should be checked for updates)
3a. delete/disable Logshipping jobs
4a. set new server to restricted user for each db, just to allow a smoke test before you turn this on to users. If you don't do this, you can't go back to the old server if something is wrong without data being potentially lost/changed on the new server. Take a few minutes and let admins connect and determine if this is ready to go.
Mostly looks good to me.
June 20, 2019 at 5:57 pm
Steve -
Thanks! All good points that I unfortunately forgot 😀
June 21, 2019 at 8:51 pm
It's also handy to setup a c-name for production that points to your current server. Change connections to the current server to use the c-name instead of the server name. Then when you finally bring up the new server, you just point the c-name to the new server. No server renaming necessary. It makes going forward switching from old to new servers a bit easier. Things don't work? Just point the c-name back to the original server while you fix the problem and try again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply