May 11, 2016 at 4:35 pm
We are moving 6 production databases from one physical server to another. The drive numbers and names on the new and old servers do not match up, but we will be able to take care of that.
My question is:
On the weekend of the move, what is the best way to stop servers in order to be sure that all transactions that were started are committed before we take the backup of the databases on the existing prod. Move the .bak files to the new server and restore them there.
Should the web servers get stopped first and then the database server? Also, on the database side, is it sufficient to run DBCC OPENTRAN to make sure there are no open transactions and then maybe put the sql server on single user mode and make backups of the 6 databases.
If we go the route that has been mentioned above, is it ok to just take full backups of the 6 databases and restore with replace option on the new server and not worry about copying/doing anything with the transaction logs on the source(old server)? As we would have kind of made sure all open transactions closed before making the backup, so don't need anything from transaction logs applied.
Appreciate any advice from folks who have been there...done that.
Thanks,
bvip.
May 12, 2016 at 3:14 am
If you have the time to completely shut down transactions prior to the backup, yes, that sounds like an adequate process and you won't have to worry about the transaction logs.
Just be aware that this will be time consuming. You'll shut down all the apps, then put the databases into restricted user (single user is a bit problematic in the event that you're still getting connections from the outside) in order to prevent unwanted connections. Sys.dm_exec_requests is how I would check for existing connections. If you have outstanding connections, is the plan to kill them to have their transactions roll back? That could take time. Then it's however long the full backup and the full restore takes. Just plan for this and you should be fine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2016 at 7:32 am
Bharatvip (5/11/2016)
We are moving 6 production databases from one physical server to another. The drive numbers and names on the new and old servers do not match up, but we will be able to take care of that.My question is:
On the weekend of the move, what is the best way to stop servers in order to be sure that all transactions that were started are committed before we take the backup of the databases on the existing prod. Move the .bak files to the new server and restore them there.
Should the web servers get stopped first and then the database server? Also, on the database side, is it sufficient to run DBCC OPENTRAN to make sure there are no open transactions and then maybe put the sql server on single user mode and make backups of the 6 databases.
If we go the route that has been mentioned above, is it ok to just take full backups of the 6 databases and restore with replace option on the new server and not worry about copying/doing anything with the transaction logs on the source(old server)? As we would have kind of made sure all open transactions closed before making the backup, so don't need anything from transaction logs applied.
Appreciate any advice from folks who have been there...done that.
Thanks,
bvip.
If downtime is an issue, and most production systems have SLAs of .999 or higher than you can use this checklist to minimize the downtime.
1. Create FULL backup scripts for all current prod dbs.
BACKUP DATABASE [sourcedb]
TO DISK = N'\\FQDN\foldername\sourcedb_Full backup filename.bak'
WITH NOFORMAT, NOINIT, NAME = N'sourcedb-full DBBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
2. Create DIFFERENTIAL backup scripts for all current prod dbs.
BACKUP DATABASE [sourcedb]
TO DISK = N'\\FQDN\foldername\diff_Last_Backup.dif'
WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'sourcedb-diff DBBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
3. Create User script on current production database. (look for a script named help_revlogin)
4. Create a restore script that will restore the full backups and move the database files to the locations you want on the new server. (note the NORECOVERY option)
;RESTORE DATABASE [targetdb] FROM DISK = '\\FQDN\foldername\sourcedb_full_Backup filename.bak' WITH REPLACE, FILE = 1, NORECOVERY, STATS=10
, MOVE 'sourcedb_data' TO 'NEWPATH\TARGETDB_data.mdf'
, MOVE 'sourcedb_log' TO 'NEWPATH\LOG\TARGETDB_log.ldf'
5. Create a restore script that will restore the differential backup. this will be very similar to the FULL backup restore only the differential file will be used and you can RESTORE WITH RECOVERY to bring the database online, if needed.
Once you have all of these scripts then perform in this order.
1. Take full backups on old prod server - can be done before downtime
2. Restore full backups on new server - can be done before downtime
3. Stop all DB activity
4. Take differential on old prod server
5. Restore differential on new prod server - if WITH RECOVERY databases will be online at this point.
6. Run user script on new server
7. You may also need to do an orphan user check, just do a internet search for 'reconcile orphan user SQL Server script' should get you what you need.
8. Point applications/processes to new server.
9. test....
This works with less downtime because your full backups can be done before the downtime and the differentials which you take after downtime are completed quicker than a full backup.
I ran this procedure on a recent server migration I just had to do on 3TB of data. Took 20hrs for the full backups and 15 mins for the differentials. On 3 TB of data there was 1hr of downtime, with only 15 mins of downtime on the database server (testing, creating cnames etc takes time too!)
May 12, 2016 at 1:55 pm
Also if you have time and want to minimize your downtime as possible you can cunsider setting up something like db mirroring or log shipping beforehand.
Joie Andrew
"Since 1982"
May 12, 2016 at 2:59 pm
Thanks a lot for the detailed list of things to do for the migration.
Actually 5 of the 6 databases we are migrating are less than a Gig and the 6th one has a data file just under 100 GB. The backup file is about 25 GB.
We also are in a position to have ample down time and can request the users to stop working before we start our migration.
So with the above in mind and based on what you have said it seems we should be able to stop the applications and make sure all transactions have
completed ( Sys.dm_exec_requests), we should not have existing connections at this point. If we do then we will kill and wait for rollback to complete.
Next we can take full backups of the databases. Move the .bak files to the destination server and restore them. That's what we will do then.
Thanks,
BVip.
May 12, 2016 at 9:36 pm
You have lots of time and the database files are small. This can be easy:
1. Detach each database. This will complete all active transactions and shut down each database in a clean state.
2. Copy all the database files to the new locations.
3. Attach the database to the new SQL Server instance.
-Eddie
Eddie Wuerch
MCM: SQL
May 15, 2016 at 1:19 pm
Bharatvip (5/11/2016)
We are moving 6 production databases from one physical server to another. The drive numbers and names on the new and old servers do not match up, but we will be able to take care of that.My question is:
On the weekend of the move, what is the best way to stop servers in order to be sure that all transactions that were started are committed before we take the backup of the databases on the existing prod. Move the .bak files to the new server and restore them there.
Should the web servers get stopped first and then the database server? Also, on the database side, is it sufficient to run DBCC OPENTRAN to make sure there are no open transactions and then maybe put the sql server on single user mode and make backups of the 6 databases.
If we go the route that has been mentioned above, is it ok to just take full backups of the 6 databases and restore with replace option on the new server and not worry about copying/doing anything with the transaction logs on the source(old server)? As we would have kind of made sure all open transactions closed before making the backup, so don't need anything from transaction logs applied.
Appreciate any advice from folks who have been there...done that.
Thanks,
bvip.
Stop all services to the databases would be best.
Take the databases offline cleanly and copy\paste (not cut\paste) the database files to the new server.
Attach the databases and that's it.
If you need the original databases bring them online
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply