I have found one thing in SQL I do very, very, very well… Can’t you tell by the title? There are times when to use and not to though…
Very good times to use it:
- Migrating a “live” database from one server to another.
- Moving a cluster from one domain to another while keeping the primary site up.
- Non-automatic failover DRs
- Setting up an initial DR system in house or across a WAN.
- Creating a read-only server where the data is allowed to stale for long periods of time.
- During setup of the SQL server pair for the HA Availability Group (especially on larger databases) or any other major setup (replication) before switching this DR structure “on”.
- Where you have two sites where you need to go through some sort of FTP connection to transfer the data.
- Where you are limited on budget and need to have a warm DR server up, but you are limited to only one DR SQL server. You can easily set it up for a many to one scenario.
- You are limited to SQL Server Standard only.
- Where another copy of the database already exists on the server, but you need the one from the other server for DR purposes. Example: we have DBA_MaintenanceDB on every SQL server (every server). It is meant for the DBA’s to store data in (like old backup information from MSDB, SP_AskBrent results, database inventories, and all sort of other junk out there), but B.M.T. the company had a DBA that thought he would write SPs in there to run against the primary production database. Well he left and never transferred the knowledge. Until we get that mess cleaned up and sorted out, we need to have a copy of the DBA_MaintenanceDB on our DR server, so it on the secondary server it is named LS_Prod_DBA_MaintenanceDB so we still have all of those SPs.
Bad times to use it:
- For a reporting server that needs to be kept as up to date as possible.
- For a reporting server where you need different securities on the database than in production
- You are horrible at remembering the difference between “With Recovery” and “With NoRecovery” (I raise my hand to this, and it’s annoying)
- Your RPO and RTO for DR is less than 5 minutes each.
- Remember this: anything that happens in the database on the production server will occur to the log shipped copy on the other server after all of the TRN files have been restored. So if you need another copy of the DB, yet something has to be different, don’t use log shipping
I have been watching the Logshipping BrentOzar blog like a hawk now, just because it’s fun to try to help out others in the understanding of how Log Shipping works. In doing this I have learned more than I knew before, but still learn more every time someone asks another question.
I will dig into details on this more as the days go on.