Monday, 18 September 2017

Master Data Services Model Cloning

Yes! My company uses Master Data Services or MDS. Yes! It's a pain. And yes! I needed to learn how to use and administer it. For anyone that hasn't had this well meant tool forced upon on them, it feels ... (if I'm being kind) ... a little unfinished.

Based on my experience MDS administration requires an awkward mix of command line tools, the MDS web interface, and even some sql scripts and stored procs executed directly on the database. There are a few strange quirks, like the fact that individual users or groups can be set as administrators, but there is a single super-user. This super-user can only be an individual and not an AD group (although I'll be happy to be proved wrong on this!). Users in the System Administration role can deploy models, but still need to be given permissions to view and edit those models by the super-user (this has changed in SQL 2016 - multiple super-users are supported as well as AD groups are now supported as super-users).

The following article explains a certain quirk regarding deploying models across environments in order to allow for ongoing development: How to Deploy Master Data Services Models Between Environments.

The development team at my company actively maintains and develops their BI solution with MDS as a core component. I have therefore created a PowerShell script to aid in deploying and synchronising models across MDS environments. My goal was to create a script that did not require any MDS knowledge. However, it will help to have a basic understanding of how MDS works and is deployed. The script performs and supports the following:
  • Multiple models can be migrated. 
  • Multiple AD Groups and/or users can be given update permissions to the models.
  • Creates model packages (pkg files) from source MDS service and saves them to the local MDS directory specifying the -includedata switch. 
  • Copies the model packages to a central directory (or back up directory)
  • Cleans up the various directories (backup, local MDS on source and destination servers)
  • Copies the packages from the central directory (or back up directory) to the destination server
  • Deploys clones of the models to the destination server
  • Assigns Update permissions to the defined groups and/or users 
There are some restrictions / limitations:
  • If migrating multiple models they all need to use the same version. There is a parameter for the -version switch of the MDSModelDeploy createpackage command but the same value will be used for each model. If you need to deploy multiple models with different versions the script will need to be run multiple times.
  • The MDS Service name parameter value is also the same for all models being migrated. 
  • The AD Groups and users will already need to exist in MDS. If they are not there, the permissions part of the script will fail. I aim to add a check for the existence of the users and groups. 
  • I have tested this using PowerShell version 5. I have no reason to believe it wouldn't work in version 4, but I have not tested that.
  • I have only tested this script against SQL Server 2012 and 2014. 
The basic prerequisites are:
  • The script is designed to run remotely so the machine the script is run on must have access to all servers and directories.
  • The credentials under which the script is run must have all the necessary permissions on the remote servers and directories. I have tested the script as a system administrator on all servers and with full control on the file share directory used for backups.
  • The credentials under which the script is run must have at least db_owner permissions on the MDS database. I have tested this as a sys_admin on the database servers. 
  • The credentials under which the script is run must have permission to access the System Administration functional area in the target Master Data Services environment.

A downloadable version of the script is on my Google Drive.