Log ship MSDB, recommended or not | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Log ship MSDB, recommended or notI am considering log shipping MSDB and wonder if anybody has tried it and could comment on it.
I know a lot of poeple assume it is not feasible but it might be a limitation of the GUI of SSMS. In 2008, it is possible to script log shipping and configure it to restore to a database named differently than the original one. This would allow not to overwrite MSDB on the destination server by naming the destination database differently, for instance MSDB_DifferentlyNamed, and therefore not break the log shipping requirements. Log shipping needs access to MSDB on both primary and secondary server.
SQL server 2008 does not seem to allow log shipping for msdb via the SSMS GUI. I believe it is due to the fact that the GUI does not allow to select a different name than the original database for the destination database. This would mean that MSDB would need to be sent in restoring state on the destination server, hence preventing it’s usage which is required for log shipping in the first place.
Now, what I am wondering is if one can do it, if he should do it. In other word, anybody tried or has reliable info on why one should or should not do it? Is the GUI not allowing log shipping of SSMS because of previous consideration which have not been revised or is there more to it which would make it not recommanded.
I believe it has been specified in your other post that due to the constraint of RECOVERY model as SIMPLE on system database, MSDB too it is not possible to log ship the system database.
If you are attempting to provide standby for system databases, then include a daily backup strategy and store the backups on other server.
MSDB can be put in full recovery mode. Some of my servers have MSDB in full recovery mode.
I’m not saying you cannot put the MSDB in FULL recovery model, the default setting is SIMPLE due to obvious reason that system databases will not store enormous data as user database does. The recovery models are designed to control the transaction log maintenance and for point in time recovery when FULL model is deployed.
Further if you are managing MSDB to maintain backup & restore history for your complete SQL Server instance then you can deploy it to put in FULL and that you consider placing the msdb transaction log on fault tolerant storage.
By design the log shipping will not allow ship the transaction log for MSDB using GUI, as referred earlier you can do it using TSQL statement and design your own log shipping methods.
I’m still kind of unsatisfied with your approach of why you want to log ship a system database, appreciate if you can clarify this point.
The reason I am looking to keep a MSDB up to date on a redundant server is that log shipping is not our only redundancy method. It is in fact our last resort. However, during our last failover test, we ended up in a situation were we had few databases not recovered including MSDB.
Restoring those few databases is much harder without the backup history contained in MSDB. We cannot rely on scripts which would use it. Therefore when end up adding manual interventions which makes the downtime longer. This is basically what I want to prevent.
Having a MSDB up to date would require performing regular backups and moving them to the other server. I would believe log shipping would be less ressource expensive than increasing the frequency of regular backups and copying them. I also like to knowI have a copy of the jobs and ssis on the other server even if I did not try it yet.
Let me know if you see it owtherwhise or still believe I am wrong.
Now we are talking , you are nearly there in terms of providing the standby approach for your systemd databases too.
In this case the best and recommended way is to perform backup for system databases on regular basis, the reason they are not included in log shipping is not much updated on daily basis and using the native backup methods.
A backup and restore strategy contains a backup portion and a restore portion. The backup part of the strategy defines the type and frequency of backups, the nature and speed of the hardware that is required for them, how backups are to be tested, and where and how backup media is to be stored (including security considerations). The restore part of the strategy defines who is responsible for performing restores and how restores should be performed to meet your goals for availability of the database and for minimizing data loss.
Further read on this MSDN article http://msdn.microsoft.com/en-us/library/ms191253.aspx that talks about how best you can take care of recovery strategy.
If in doubt you can even discuss with me offline by sending an email where I normally offer the consultancy to keep up the enviornment.