Introduction
While Master Data Services is the best tool to come along to permit user to update their qualitative and quantitative data, disaster recovery from the failure of a model or the MDS database itself can be a challenge to say the least.
The main challenge comes NOT with restoring the model nor the database, but rather with restoring the data security including user rights and process ID’s access to the data models and daily processing jobs via the SQL Server Agent.
A case at hand occurred when a user asked to have an additional attribute added to a core entity. The user wanted the field placed in a particular position and between two specific columns.
As the astute user will note, this is NOT easy to do and required me to take out the columns from the critical position to the ‘end of columns’; create the new attribute, and re add the deleted attribute. This is not the critical part. The issue was that the entity is a part of a hierarchy and this further complicated matters.
Security was based upon the one attribute within the higher level entity. Satellite companies could see their own records (Member = 2) and the mother company can see their own PLUS all records from the satellite company (Member = 1 and / or two).
Security Token (mother)
Child records (Satellite Coy)
Child Records(Satellite and Main Company
I only mention this here is as this was the root cause for the restore. Please read on.
When the hierarchy was originally designed, a mother company’s employee was able to either:
1) See all records.
2) See satellite company records only.
3) See main company records only.
The important point was that ‘all records’ was the default on entering explorer which was not the case after mucking about trying to get attributes in the desired position. Somehow the hierarchy became corrupt, and the normal stats in bottom left corner of Explorer showed ‘0 – 0 members of 0’available (while there were members on the screen) and in order to actually get beyond the first page of members, the end user was required to select either 1 or 2 from the security keys (listed above) and more importantly, could not see both at once.
I shall be discussing the creation of this type of security within Master Data Services in a further article.
Two critical points are to be made, prior to discussing the disaster recovery steps necessary to get back up and running.
1) Each time one recovers, the security that you built into the user ID’s somehow does not seem to be recoverable and has to be reprogrammed.
2) A process ID that runs the SQL Server Agent will no longer have necessary rights to the MDS models and metadata and WILL cause daily loads to fail.
The recovery process
After each daily process, we take backups not only the MDS database but the production models as well.
Prior to describing how to restore a model, the reader must understand that this should be a last resort. It is always easier to try to remedy a problem without resorting to Draconian measures.
The Steps:
1) Delete the model from within MDS: To delete the model, simply enter the Master Data Manager as shown below: Choose the System Administration Task
2) Select the Manage Option and Models
3) Choose Model Maintenance and Delete X. You will be informed that you will lose ‘the whole bang shoot’. Simply acknowledge this and continue.
4) Once the model has been removed it is time to restore our back up of the model. This is done with the following command. While one can do this within the Master Data Manager, I prefer to do this from the command prompt. The application which will restore your model is called:
MDSModelDeploy and in SQL Server 2012 this may be found in c:\program files\microsoft SQL Server\110\master data services\configuration.
Simply issue the following command.
mdsmodeldeploy deploynew -package MyFileFinalBackupToday.pkg -model Aging -service MDS1
where MyFileFinalBackupToday.pkg is the data backup and ‘Aging’, the model name. MDS1 is
the ‘service’. This is the name that you gave to the web application when Master Data Services
was initially set up. In our case (MDS1).
Great!!! The model has now been restored. This was the easy part.
The issue is that the permissions are no longer there, invalid, or can no longer be guaranteed.
Resetting permissions
Clicking on the ‘User and Groups’ permissions tab gets you to the maintenance screen.
For each user reset the necessary permissioning, remembering that there are three options for each Entity (Read Only, Update, and DENY). The astute reader, will note that these permissions may be granted at the entity level or at the attribute level. This obviously depends upon your business rules.
My setting for my account may be seen in the following screen dumps. The first screen dump shows general information and nothing more.
The membership tab must be check as well. This is informative only HOWEVER it will show you which Window user groups your user was a member of when he last logged in.
We now are at a critical point where we must ensure that our users has the necessary rights to do his day to day work.
Most users merely require Explorer rights. Administrators and Process IDs require
access to the remaining tabs as well as to the MDS Metadata.
Assigning rights to the models FOR THIS USER comes next and this must be done with the greatest of care. I always work on the principle of ‘minimum necessary rights’ to achieve the user’s requirements.
Remember however that I have assigned update permission for myself as the MDS Admin (at the Entity Level) however your users would certainly not have this.
As mentioned above, I shall discuss Security in detail in an upcoming article.
Restoring the Process ID
While I belabored the security issue above (merely as a grounding) I have found that the restoration of a SQL Server Agent Process ID is not for the faint hearted and is genuinely enough to give one grey hair.
OK, this is what must be done to restore the necessary rights to a Process ID
1) Remove and re-add the Process ID to the MDS database within SQL server management studio.
2) The login should be OK. The Process ID should have Administrator rights from inception.
3) Having re-added the Process ID to the MDS database, we must delete the Process ID from the ‘User and Group’ screen (within the Master Data Manager) and ENSURE that the ID is in fact removed. We now re-add the Process ID to the Master Data Manager.
Having created the Process ID I now login and logout of Windows as the process ID to force the windows groups to show up in the screen dump above.
The windows permission groups for the process ID may be confirmed in the Master Data Manager by its last login and is indicative that we are on the right track.
4) We now set the appropriate functions as was shown above.
5) Next comes the important part setting the RIGHTS for the process ID.
NOTE that the process ID has update rights TO EVERYTHING (All models and the metadata). This is very important.
To recap:
The Process ID has:
Windows Administrator Rights.
Update rights to the metadata
Update right to the Data Models.
6) We have now reached a point where we need to test to see if the process ID can read the models. THIS IS CRITICAL as if it cannot, you daily loads WILL fail.
7) To test the visibility of the models to the process ID we once again utilize our old friend MDSModelDeploy. Note that our model ‘Aging’ is shown(See below). This indicates that the Process ID is good to go. Should the list be blank, then model visibility is STILL an issue.
Conclusion
While normal database recovery is usually fairly painless, the recovery of the MDS database or more often your data models can be challenging to say the least. The restoration of user rights is fairly simple and takes only a few minutes for each user. The big challenge seems to be to restore the necessary rights to a Process ID running the SQL Server Agent. Somehow the Process ID rights become corrupt upon restoration and I have found that you have to ensure TWICE that the Process ID has been removed from the Master Data Manager and from the MDS database BEFORE re-adding it to both.
As mentioned above, I shall be writing an additional article later in February describing the setup of Master Data Services security and considerations in doing so.
As always should you have any questions or thoughts, please feel free to contact me at
Happy programming.
