The trouble with backing up databases in ‘old production’ then restoring them to ‘new production’ is that it takes time.
And there may be some unforeseen delay switching the front-end apps over.
Resulting in ‘old production’ being updated with new data, and ‘new production’ becoming out of date.
Log-shipping is an ideal, built-in, tool that can be used to keep ‘new production’ in sync with ‘old production’ during that phase between backup/restore and switching the front-end to ‘new production’.
This time around there was no need to script the setting up of log-shipping. There were only 13 databases, so using the GUI did not take long.
The idea is to complete the backup/restore a week or so before the switch-over and set-up log-shipping to keep the data in sync.
Then at the designated switch-over time, it takes only a moment to bring ‘new production’ on-line, as a fully up-to-date copy of ‘old production’.
Here is my crib-sheet …
Preparation
- Primary and secondary servers should have as near as possible the same instance settings eg: max-memory, numa configuration, CLR, max dop, etc
- Ensure user databases are using full recovery model
- Create shared folder (on the target ideally)
- Default backup compression is enabled (ideally)
- Reduce VLF counts
- Configure file share folder and connectivity
- Disable tlog backups on primary
Preparation – Secondary Instance
- Ensure enough space for databases
- Matching drive letters for datafiles and logfiles (ideally)
- Configure file share
Preperation – Monitor Instance
- Ideally separate from primary and secondary
Security
- Config login is a sys admin role
- SQL server service account on primary needs read/write permission on backup directory (for the backup job)
- SQL server service account on secondary needs read permission on backup share and read/write permission to secondary share (for copy / restore jobs)
Configuring log-shipping
- Manually backup and restore database (with no recovery)
- Use notepad to cut and paste connection strings and paths
- Transfer logins, jobs and linked servers
The switch over
- Manually execute the backup, copy, and restore jobs a final time
- Manually restore each database “with recovery”
- Detach old databases (so there is no chance of them being updated)
- Point front-end-applications to new back-end server
Post Migration
- Full backups (the old ones cannot be restored now)
- Update all statistics
- Check compatability level
- Execute dbcc checkdb
- Enable plan-store (read/write)
- Monitor health and performance
Database Consultant 