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 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.
<########################################################################################################################################################################
Created by: Thomas Mucha
Created: 2017-09-14
Description: This script allows you to clone models with data from one MDS server to another. The CLONE funtion preserves the Models' MUID values. Cloning models allows
for future model updates.
See http://www.sqlchick.com/entries/2015/3/16/how-to-deploy-master-data-services-models-between-environments for a good explanation of MDS deployment options
In order to run this script
########################################################################################################################################################################>
cls
<#############################################>
<###### Modify the following parameters ######>
[string[]]$Models = ('Model1','Model2','Model3','Model4','Model5') ;
$SourceServer = "SourceServerInstance" ;
$DestinationServer = "DestinationServerInstance" ;
$MDSDatabaseName = "MDS_Database_Name" ;
$PKGBackupPath = "\\BackupDirectory\$SourceServer\MDS1"; #This directory must be accessible by both Source and Destination servers
[int]$BackupPKGCleanupPeriod = -30 ; #File age of backup PKG files in days. Any files accessed before (Get-Date).AddDays($BackupPathCleanupPeriod)} will be deleted
[int]$SourcePKGCleanupPeriod = -30 ; #File age of locally saved PKG files in days. Any files accessed before (Get-Date).AddDays($BackupPathCleanupPeriod)} will be deleted
[int]$DestinationPKGCleanupPeriod = -30 ; #File age of locally saved PKG files in days. Any files accessed before (Get-Date).AddDays($BackupPathCleanupPeriod)} will be deleted
$MDSServiceName = "MDS1"
$MDSModelVersion = "VERSION_1"
$DeleteModelsOnDestinationServer = "No" #"Yes" to delete any existing models from the destination server with the same name as those on the source server
#"No" Prints warning that models with the same name exist on both the source and the destination servers. Script exits.
[string[]]$GroupList = ("DomainName\ADGroup1","DomainName\ADGroup2"); # Groups and Users must already exist in MDS in order for the permissions to be set.
[string[]]$UserList = ($null); #Leave NULL if no users are to be added. Only use the user list when necessary, the permissions should be handled by groups
<######## Modify the above parameters ########>
<#############################################>
[string]$date = Get-Date -format s;
$date = $date -replace "-", "";
$date = "_$date" -replace ":", "";
##Delete Models from Destination server
If ($DeleteModelsOnDestinationServer -eq "No")
{
[String[]]$ExistingModels = $null
foreach ($Model in $Models)
{
$ExistingModels = $ExistingModels + (Invoke-Sqlcmd -Query "select name from mdm.tblmodel where name = '$Model';" -ServerInstance $DestinationServer -Database $MDSDatabaseName -QueryTimeout 0).name;
}
If ($ExistingModels.Length -gt 1)
{
Write-Warning "Model(s) $ExistingModels exist on $DestinationServer. Migration has been halted!"
Break
}
}
If ($DeleteModelsOnDestinationServer -eq "Yes")
{
Write-Verbose "Deleting any existing models on $DestinationServer" -Verbose
foreach ($Model in $Models)
{
Invoke-Sqlcmd -Query "declare @muid uniqueidentifier; select @muid = MUID from mdm.tblmodel where name = '$Model';
if @muid is not null
begin
exec mdm.udpModelDeleteByMUID @Model_MUID= @muid
end;" -ServerInstance $DestinationServer -Database $MDSDatabaseName -QueryTimeout 0;
Write-Verbose "$Model has been found and deleted on $DestinationServer" -Verbose
};
};
Write-Verbose "Determining MDS version and directory on destination server: $DestinationServer" -Verbose
$RemoteDestMDSPath = (Get-ChildItem -Recurse -Directory "\\$DestinationServer\c$\Program Files" | where { $_.FullName -like "*Master Data Services*Configuration" }).FullName ;
Write-Verbose "Determining MDS version and directory on source server: $SourceServer" -Verbose
$RemoteSourceMDSPath = (Get-ChildItem -Recurse -Directory "\\$SourceServer\c$\Program Files" | where { $_.FullName -like "*Master Data Services*Configuration" }).FullName ;
$LocalSourceMDSPath = $RemoteSourceMDSPath.Replace("\\$SourceServer\c$", "c:");
$LocalDestinationMDSPath = $RemoteDestMDSPath.Replace("\\$DestinationServer\c$", "c:");
##Cleanup any old Model pkg files - any "pkg" files older than 30 days will be deleted
Write-Verbose "Testing for and creating backup directory" -Verbose
if (-Not (Test-Path -Path $PKGBackupPath))
{ New-Item $PKGBackupPath -Type directory };
Write-Verbose "Cleaning up PKG files from backup directory" -Verbose
Get-ChildItem $PKGBackupPath | where {$_.Extension -eq '.pkg' -and $_.LastWriteTime -lt (Get-Date).AddDays($BackupPKGCleanupPeriod)} | Remove-Item ;
Write-Verbose "Cleaning up PKG files from $RemoteSourceMDSPath directory" -Verbose
Get-ChildItem $RemoteSourceMDSPath | where {$_.Extension -eq '.pkg' -and $_.LastWriteTime -lt (Get-Date).AddDays($SourcePKGCleanupPeriod)} | Remove-Item ;
Write-Verbose "Cleaning up PKG files from $RemoteDestMDSPath directory" -Verbose
Get-ChildItem $RemoteDestMDSPath | where {$_.Extension -eq '.pkg' -and $_.LastWriteTime -lt (Get-Date).AddDays($DestinationPKGCleanupPeriod)} | Remove-Item
##Create packages from source models
Write-Verbose "Creating Model Packages on source server: $SourceServer" -Verbose
foreach ($Model in $Models)
{
$CMD = "MDSModelDeploy createpackage -model $Model -version $MDSModelVersion -service $MDSServiceName -package $Model$date -includedata"
invoke-command -ComputerName $SourceServer -ArgumentList $CMD,$LocalSourceMDSPath -ScriptBlock {
param ($CMD,$LocalSourceMDSPath)
Set-Location $LocalSourceMDSPath
cmd /c $CMD
};
};
##Move pkg files to destination server via the Backup directory
Write-Verbose "Copying Model Packages to $PKGBackupPath" -Verbose
Copy-Item "$RemoteSourceMDSPath\*$Date.pkg" "$PKGBackupPath";
Write-Verbose "Copying Model Packages to $RemoteDestMDSPath" -Verbose
Copy-Item "$PKGBackupPath\*$Date.pkg" "$RemoteDestMDSPath";
##Deploy clones of the packages to the destination server
Write-Verbose "Deploying Model Packages as clones on destination server: $DestinationServer" -Verbose
foreach ($Model in $Models)
{
$filename = (Get-ChildItem $RemoteDestMDSPath | where {$_.Name -Like "$Model$Date.pkg"}).Name
$CMD = "MDSModelDeploy deployclone -package ""$filename"" -service $MDSServiceName"
invoke-command -ComputerName $DestinationServer -ArgumentList $CMD,$LocalDestinationMDSPath -ScriptBlock {
param ($CMD,$LocalDestinationMDSPath)
$LocalDestinationMDSPath
Set-Location $LocalDestinationMDSPath
cmd /c $CMD
};
};
##Add permissions to newly deployed models
Write-Verbose "Setting update permissions for groups and/or users" -Verbose
foreach ($Model in $Models)
{
foreach ($Group in $GroupList)
{
Invoke-Sqlcmd -ServerInstance $DestinationServer -Database MDS_MasterData -Query "declare @groupid int;
declare @groupmuid uniqueidentifier;
declare @groupname nvarchar(100);
declare @modelid uniqueidentifier;
declare @modelname nvarchar(100);
select @groupid = ID, @groupmuid = MUID, @groupname = [Name]
from mdm.tblUserGroup
where [Name] = '$Group'
select @modelid = MUID, @modelname = [Name]
from mdm.tblModel
where [Name] = '$Model'
declare @userperms table (RoleAccess_ID int, RoleAccess_MUID uniqueidentifier, Privelege_ID int, Privelege_Name nvarchar(100), ObjectType_ID int, ObjectType_NAME nvarchar(100), Securable_ID int, Securable_MUID uniqueidentifier, Securable_Name nvarchar(100), Model_ID int, Model_MUID uniqueidentifier, Model_Name nvarchar(100), SourceUserGroup_ID int, SourceUserGroup_MUID uniqueidentifier, SourceUserGroup_Name nvarchar(100), IsModelAdministrator int)
insert into @userperms
exec mdm.udpSecurityPrivilegesSummaryGet @SystemUser_ID=1,@Principal_ID=@groupid,@PrincipalType_ID=1,@IncludeGroupAssignments=1,@Model_ID=NULL
if not exists (select 1 from @userperms where ObjectType_ID = 1 and Securable_Name = @modelname and Privelege_ID = 2)
if not exists (select 1 from @userperms where ObjectType_ID = 1 and Securable_Name = @modelname)
begin
declare @p14 bigint
declare @p15 uniqueidentifier
exec mdm.udpSecurityPrivilegesSaveByMUID @SystemUser_ID=1
,@Principal_MUID=@groupmuid
,@PrincipalType_ID=2
,@Principal_Name=@groupname
,@RoleAccess_MUID='00000000-0000-0000-0000-000000000000'
,@Object_ID=1
,@Privilege_ID=2
,@Model_MUID=@modelid
,@Model_Name=@modelname,@Securable_MUID=@modelid,@Securable_Name=@modelname,@Status_ID=0,@Return_ID=@p14 output,@Return_MUID=@p15 output
end"
}
}
if ($UserList.Length > 0)
{
foreach ($Model in $Models)
{
foreach ($User in $UserList)
{
Invoke-Sqlcmd -ServerInstance $DestinationServer -Database MDS_MasterData -Query "declare @userid int;
declare @usermuid uniqueidentifier;
declare @username nvarchar(100);
declare @modelid uniqueidentifier;
declare @modelname nvarchar(100);
select @userid = ID, @usermuid = MUID, @username = UserName
from mdm.tbluser
where UserName = '$User'
select @modelid = MUID, @modelname = [Name]
from mdm.tblModel
where [Name] = '$Model'
declare @userperms table (RoleAccess_ID int, RoleAccess_MUID uniqueidentifier, Privelege_ID int, Privelege_Name nvarchar(100), ObjectType_ID int, ObjectType_NAME nvarchar(100), Securable_ID int, Securable_MUID uniqueidentifier, Securable_Name nvarchar(100), Model_ID int, Model_MUID uniqueidentifier, Model_Name nvarchar(100), SourceUserGroup_ID int, SourceUserGroup_MUID uniqueidentifier, SourceUserGroup_Name nvarchar(100), IsModelAdministrator int)
insert into @userperms
exec mdm.udpSecurityPrivilegesSummaryGet @SystemUser_ID=1,@Principal_ID=@userid,@PrincipalType_ID=1,@IncludeGroupAssignments=1,@Model_ID=NULL
if not exists (select 1 from @userperms where ObjectType_ID = 1 and Securable_Name = @modelname and Privelege_ID = 2)
if not exists (select 1 from @userperms where ObjectType_ID = 1 and Securable_Name = @modelname)
begin
declare @p14 bigint
declare @p15 uniqueidentifier
exec mdm.udpSecurityPrivilegesSaveByMUID @SystemUser_ID=1
,@Principal_MUID=@usermuid
,@PrincipalType_ID=1
,@Principal_Name=@username
,@RoleAccess_MUID='00000000-0000-0000-0000-000000000000'
,@Object_ID=1
,@Privilege_ID=2
,@Model_MUID=@modelid
,@Model_Name=@modelname,@Securable_MUID=@modelid,@Securable_Name=@modelname,@Status_ID=0,@Return_ID=@p14 output,@Return_MUID=@p15 output
end"
}
}
}
1 comment:
Nice to know master data services and limitations.
microsoft access to sql server migration
ms access to sql server migration tool
ms access migration
access to sql server migration tool
Post a Comment