February 6, 2020 at 10:45 pm
Hi,
DBA gurus please give inputs regarding this project to move 100 DB's , msdb jobs , users, linked servers etc from one server sqldev to sqldevnew. sqldev is on lower version and has limited hardware and now we need to add 100 more DB's to make it scalabale and this is a VAL environment. Main question I have is the name and IP , what is the best recommendation and exact steps I need to tell the OS and Network Team. I need the old server sqldev up so I can plan to take backups etc. and the new server sqldevnew would need to have name changed to sqldev as the config files in 100s of places points to sqldev ?
After this is sorted ,
I would like to use backup of source DBs and restore to destination through scripts or use logshipping.
Please recommend which is better or if there is a better option.
Thanks in Advance
February 7, 2020 at 8:52 am
Script and test and destroy, rinse and repeat a number of times until all things have been ironed out.
This will give you then a proven strategy on how to do this for real.
As for the name change, there are plenty of things on the internet for renaming a SQL server. Should you though is a internal discussion you need. This may cause confusion from your infrastructure team if they name objects outside of the AD forest (SAN disks etc) after the new server name.
There are alternatives to that, like once you have done the migration for real, use a CNAME of SQLDEV to point to SQLDEVNEW.
But as you need to test the migration the dev's will need to change the connection strings anyway, so why not just point everything to the new server and be done.
You may also want to look at things like DBATools and their powershell modules for server migrations. Again script it, test it, destroy it, rinse and repeat until all things have been fully tested. You'll not get everything first pass, you may need several attempts to get this right.
February 8, 2020 at 12:24 am
Thank you for the inputs, we cannot change the name of server in config files to point to sqldevnew. It has to be sqldev.
Can you tell me the exact steps how to accomplish this and the safest way . What can I tell the OS Admin? originally i thought the following but its risky
I need help with first step.. how to keep two servers with IP's on the network before Imove stuff.. Not getting an easy answer!
February 8, 2020 at 12:52 am
you have the wrong order there
and 2 servers mean 2 different ip's and 2 different names so they can be alive at same time.
keep old server with old name
1 - keep old server with old name
2 - create new server with new name.
3 - install SQL Server now on new server with new name ( sql server default instance will use the hostname and that is what is needed ).
4 - apply login / server security scripts.
5 - script and apply msdb jobs, linked servers from old server to new server.
- - if you have scripts/jobs that update other servers make sure that you change them to point to test servers before running jobs
- - change connection strings as required for testing
6 - restore full backups from old server to new one (copy only backup so log chain on old server is not lost)
7 - use script to restore all backup files to new server.
9 - test
9 - do steps 4 to 7 again
10 - test again
do 9 and 10 as many times as required
11 - go live date
12 - restore full backups from old server
13 - apply connection string changes to all systems affected
14 - final test
15 - signoff
16 - shutdown old server
February 8, 2020 at 8:25 am
How are you going to test you've successfully done a full transfer?
Part of things like this need testing.
If you want to wing it, well that's your choice, but I strongly suggest you don't.
If the servername is all your bothered about, then use a CNAME or another A record in DNS to point to SQLDEVNEW instead. Probably the safer option than having to rename servers multiple times because you didn't to test it to ensure it works.
I shall say it again, TEST TEST TEST
Especially as your going to fall into the new CE, good luck with that without proper testing.
Remember TEST TEST TEST
Your going to need it
February 9, 2020 at 12:30 pm
You could use dbatools.io (already mentioned) to move logins, linked servers, jobs etc ahead of time. We did this a couple of years ago when switching between data centers. We had the luxury of being able to set up a new cluster and update our connection strings on the go-live date. The jobs/logins were all disabled on thew new server and the powershell scripts were prepared to enable them all on the day. We had log shipping running from our live data center to our new data center so we didn't need to backup/restore on the day. We just had to ensure all data had been synced up before we committed to the switch.
We managed to be bring 4 environments live within a matter of hours with no issues. It was a seamless transition for us.
In your case, you could do something similar. On the day itself, you will need to shut down the application services and take a full backup / restore to the new server if you are not doing log shipping. You can then rename the server physically and use sp_dropserver/sp_addserver to correct your instance meta data or just use DNS as already suggested. Then the final step would be to enable logins, jobs etc. Once the application testing comes back green you are then in a position to shut down the old server.
This is all very high level and not a detailed set of processes to migrate.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply