Introduction

A few days back, in my article entitled ‘Master Data Services 2014: Creating nightly load processes’, I discussed the setting up of a nightly batch process to update a Master Data Services 2014 application that I have recently created for our finance department.

This is fine and dandy, however one critical factor that one often forgets is that backups too, must be taken.

When dealing with Master Data Services (hence forward referred to as MDS), aside from the routine backup of the MDS database, it is imperative to back-up your models (with their data),  so that one is able to ‘roll back’ in case of a ‘whoopsie’ and THIS is what I shall be discussing in this article.

Backing up your data models.

Unlike other ‘non-system’ SQL Server databases which one may port from one environment to another, the same cannot be said of your Master Data Services database. This is why it is imperative to back-up your models. Models are easily ported to another SQL Server instance and installed on another.

As a recap, the reader will see a high level view of my nightly process in the screen dump below:

What we shall be covering in this article, are the final two steps and some of the gotcha’s that I encountered.

MDSModelDeploy.exe is the key

The executable program, MDSMODELDEPLOY is the key to the whole MODEL backup process. Executing this module requires system administrative rights and this is where the whole story takes root. What we need to execute, to backup the model, is one simple statement.

mdsmodeldeploy createpackage -package OpenInvoiceFinalBackup.pkg -model Aging -service MDS1 -version VERSION_1 -includedata

Seven days of getting greyer hair!!

To begin, backing up the model (in our case) via the SQL Server Agent, requires a DOMAIN based account / process ID with system administration rights on the server hosting MDS. To further complicate matters, a part of the nightly load requires access to two other database servers on which we access the data via this domain account.

Getting all the parts to talk to one another, took me seven days and many sleepless nights.

First off and as discussed above, in order to comply with data security issues, I was compelled to utilize the domain account. The main issue came from ERRONEOUSLY trying to create a local user of the same name PLUS adding the domain account into the Administrators group. This was my first big mistake. The domain user name should NOT become a local user.

The second gotcha (and PRIOR to twigging on the fact that a local user was not necessary), was in CREATING a ‘local user’ within Windows 2008 R2  and trying to grant update rights (to the model) from within Master Data Services(explained below).

I suspected that to execute MDSModelDeploy required more than ‘Read’ rights (which in fact is so).

The issue lies in the fact that Master Data Services (MDS) will only permit one to allocate rights to local server users.

In fact, should you try to create a user utilizing the domain and user name, MDS will tell you that it cannot find the user and WILL NOT allow you to create a valid MDS user and thus you will NOT be able to assign any valid rights at all.

In short:

1)      Using the local account within as the SQL Server Agent account would permit the execution of MDSModelDeploy HOWEVER the remaining data pulls FAILED (naturally) as the other servers knew NOTHING of this local account.

2)      Using the Domain based account permitted the pulling of the data from the other database servers HOWEVER the execution of MDSMODELDEPLOY failed for the reasons stated above.

Get to the point Simon,  ..tell us the solution.

1)      Add the domain account to the Administrators group within Windows.

2)      Add the domain account within SSMS as a System Administrator login (which I had always had done).

3)      Within the security section of the MDS database, I gave the domain based ID db_owner rights.

4)      Use the domain account as the SQL Server agent login account name.

Renaming the backup file

The last step that I have in my process is to rename the backup file to incorporate the date on which the backup was made. I use the date only as the process runs once daily.
You might want to incorporate the time as well.
To do this, I have a job step that utilizes a ‘cmdExec’.
The rename statement is similar to the one shown below:

ren c:\OpeninvoiceBackups\OpenInvoiceFinalBackup.pkg OpenInvoiceFinalBackup.pkg%date:~4,2%%date:~7,2%%date:~10,4%

Conclusion

Backing up one’s MDS models are as important as backing up production databases. Security issues form 90% of the challenges in getting the various pieces of an application to talk to one another.
Should you have any questions or concerns, please feel free to contact me at steve.simon@sqlpass.org

Happy programming !!