Recently a colleague and I had a discussion about which method is the best to use for backing up MDS (Master Data Services) for disaster recovery. We came to the conclusion it all depended on the SQL environment version you were recovering to. Below are the 2 different methods for backing up an MDS database.
- Fast to create, can backup specific versions
- Will always be able to deploy a model to a MDS install
- Can be automated via SSIS
- Can be restore to a server version that is +1, 0 and -1
- If you want to save backups of different models, then the models will have to be backed up separately.
- Security is not retained; it will have to be redone. Can take about 2 minutes per user. On a small model this should not be a problem, however, when scaled out it could be very time consuming.
- Saving more than one backup of a model would require a script/maintenance task to remove backups older than X amount of time.
When deploying the model, the option DeployClone must be used, not DeployNew. DeployNew will remove the MUID and therefore will not allow any changes to be deployed to that model at a later stage. DeployClone keeps it all intact and will allow for future updates.
- Security is retained
- Simple to set-up and maintain
- Easy to restore
- Maintenance tasks to clean up old backups
- Cannot be restored to a server version less than the current version.
The database backup for MDS can be scheduled just like other backups. You can use the maintenance wizard to set up the backup and clean up task on a schedule.
If you know what version the disaster recovery environment will be on, for example the same version as the current MDS server version or higher, then it is simpler to use the Database Backup method. If you are uncertain of what version the disaster recovery version will be then you would want to use the Model Backup method in case you have to restore to a version less than the current MDS server version.
Hope this helps you to choose what is best suited for your environment!
Special thanks to Janlo Du Toit for the discussion on these methods 🙂