This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server - Windows only
Azure SQL Managed Instance
This topic summarizes the changes and updates in the latest release of SQL Server Master Data Services.
For an overview of how you organize data in Master Data Services, see Master Data Services Overview.
To install Master Data Services, set up the database and Website, and deploy the sample models, see Master Data Services Overview (MDS).
Download
To download SQL Server 2016 (13.x), go to Evaluation Center.
Have an Azure account? Then go here to spin up a Virtual Machine with SQL Server already installed.
Performance improvements enable you to create larger models, load data more efficiently, and get better overall performance. This performance improvement includes decrease data load times of Microsoft Excel add-in and enable the Microsoft Excel add-in to handle larger entities.
For more information about the add-in for Microsoft Excel, see Master Data Services Add-in for Microsoft Excel.
The following feature improvements are included.
There is data compression on the entity level, which by default is enabled. When data compression is enabled, all the entity related tables and indexes are compressed with SQL Row Level compression. This improvement significantly reduces the disk I/O when reading or updating the master data, especially when the master data has millions of rows and/or has a lot of NULL value columns.
Because there is a slight increase in the CPU usage on the SQL Server engine side, if you have CPU bound on the server you can turn off data compression by editing the entity.
For more information, see Create an Entity (Master Data Services) and Data Compression.
The Dynamic Content Compression IIS feature is enabled, by default. It significantly reduces the size of the xml response and saves the network I/O, though CPU usage is increased. If you have CPU bound on the server, you can turn off data compression by adding the following setting to the Master Data Services Web.config file.
<configuration>
\<system.webServer>
<urlCompression doStaticCompression="true" doDynamicCompression="false " />
\</system.webServer>
</configuration>
For more information, see URL Compression
The following new SQL Server Agent jobs do index and log maintenance.
MDS_MDM_Sample_Index_maintenance
MDS_MDM_Sample_Log_maintenance
By default the MDS_MDM_Sample_Index_Maintenance job runs weekly. You can modify the schedule. You can also manually run the job at any time by using the udpDefragmentation stored procedure. When you insert or update a large volume of master data, or after you create a new version from the existing version, it is recommended that you run the store procure.
An index with more than 30% fragmentation is rebuilt online. During the rebuild, the performance is affected on the CRUD operation on the same table. If performance degradation is a concern, it is recommended that you run the store procedure during off business hours. For more information about index fragmentation, see Reorganize and Rebuild Indexes.
For more information, see this post on the Master Data Services Blog, Performance and Scale Improvement in SQL Server 2016.
The new Super User function permission gives a user or group the same permissions as the Server Admin in the previous release of Master Data Services. The Super User permission can be assigned to multiple users and groups. In the previous release, the user who originally installed Master Data Services was the server admin, and it was difficult to transfer this permission to another user or a group. For more information, see Functional Area Permissions (Master Data Services).
A user can now explicitly be assigned the Admin permission at the model level. This means that if the user is assigned permissions in the model subtree, such as the entity level, they still have this Admin permission.
In this release of Master Data Services, we're providing more levels of permissions by introducing the following new permissions: Read, Create, Update, and Delete. For example, a user that has only the Update permission can now update the master data without creating or deleting the data. When you give a user the Create, Update or Delete permission, the user is automatically assigned the Read permission. You can also combine the Read, Create, Update, and Delete permissions.
When you upgrade to SQL Server 2016 (13.x) Master Data Services, old permissions are converted to new permissions as shown in the following table.
| Permission in previous release | New permission |
|---|---|
| User who originally installs Master Data Services has the Server Admin permission. | User has the Super User function permission |
| User has Update permissions at the model level and no permissions in the model subtree, and so is implicitly a model admin. | User has explicit Admin permissions at the model level. |
| User has Read-only permissions. | User has Read access permissions. |
| User has Update permissions. | User has all four access permissions: Create, Update, Delete, and Read. |
| User has Deny permissions | User has Deny permissions |
For more information about permissions, see Security (Master Data Services).
You can now clean transaction logs at predetermined intervals or on a schedule, using the System settings and at the model level. For an MDS system with lot of data changes and ETL processes, these tables can grow exponentially and lead to performance degradation and storage space issues.
The following types of data can be removed from the logs.
Transaction history older than a specified number of days.
Validation issues history older than a specified number of days.
Staging batches that ran before a specified number of days.
You can configure the frequency that data is removed from the transaction logs, using the System settings and at the model level. For more information, see System Settings (Master Data Services) and Create a Model (Master Data Services). For more information about transactions, see Transactions (Master Data Services).
The SQL Server Agent job, MDS_MDM_Sample_Log_maintenance, triggers cleanup of the transaction logs and runs every night. You can use SQL Server Agent to modify the schedule for this job.
You can also call stored procedures to clean the transaction logs. For more information, see Transactions (Master Data Services).
In SQL Server 2016 (13.x) Master Data Services, added features could improve debugging and make it easier to troubleshoot issues. For more information, see Tracing (Master Data Services).
Improvements in manageability help to lower maintenance costs and positively impact your return on investment (ROI). These improvements include transaction log maintenance and improvements to security, and the following new features.
Using attribute names that are longer than 50 characters.
Renaming and hiding Name and Code attributes.
For more information, see the following topics.
Manage Business Rules (MDS Add-in for Excel)
In the Master Data Services Add-in for Excel you can manage Business Rules, such as creating and editing business rules. Business rules are used to validate data.
Business Rules Extension
You can apply user-defined SQL scripts as an extension of business rule conditions and actions. SQL functions can be used as a condition. SQL stored procedures can be used as an action. For more information, see Business Rules Extension (Master Data Services).
Business Rule Management Experience Redesigned
The business rule management experience in MDS has been redesigned to improve the experience. For more information about this feature, see Business Rules (Master Data Services).
Business Rule Management Functionality Removed from the MDS Add-in for Excel
Business Rule management functionality has been removed from the MDS Add-in for Excel because we redesigned the experience.
New Business Rule Conditions
Seven new business rule conditions were added to provide a complete set of conditions. For more information, see Business Rule Conditions (Master Data Services).
Many-to-Many Relationships in Derived Hierarchies
You can now create a Derived Hierarchy that displays many-to-many relationships. A many-to-many relationship between two entities may be modeled through by using a third entity that provides a mapping between them. The mapping entity is an entity that has two or more domain-based attributes referencing other entities.
For example, entity M has a domain-based attribute that references A and a domain-based attribute that references B. You can create a hierarchy from A to B using the mapping entity.
For more information, see Show Many-to-Many Relationships in Derived Hierarchies (Master Data Services)
Edit Many-to-Many Relationships in Derived Hierarchies
You can edit the many-to-many relationship by modifying the mapping entity members. For more information, see Show Many-to-Many Relationships in Derived Hierarchies (Master Data Services).
Derived Hierarchy Management Experience Improved
The derived hierarchy management experience in MDS has already been improved. For more information about this feature, see Create a Derived Hierarchy (Master Data Services).
Business Rule management functionality has already been removed from the MDS Add-in for Excel because we redesigned the experience.
Custom Indexes
You can create a nonclustered index on one attribute (single index) or on a list of attributes (composite index), in an entity, to help improve the query performance. For more information, see Custom Index (Master Data Services).
Attribute Filters
For a domain-based attribute, for a leaf member, you can use a filter parent attribute to constrain the allowed values for the domain-based attribute. For more information, see Create a Domain-Based Attribute (Master Data Services).
Entity Sync Relationship
You can share entity data between different models by creating an entity sync relationship. For more information, see Entity Sync Relationship (Master Data Services).
Purge Soft Deleted Members
You can now purge (permanently delete) all soft-deleted members in a model version. Deleting a member only deactivates, or soft-deletes, the member. For more information, see Purge Version Members (Master Data Services).
Member Revision History
A member revision history is recorded when a member is changed. You can roll back a revision history, and view and annotate revisions. Using the Log Retention Days property, you can specify how long historical data is retained. For more information, see Member Revision History (Master Data Services).
Merge Conflicts
If you try to publish data that has been changed by another user, the publish will fail with a conflict error. To resolve this error, you can perform merge conflicts and republish the changes. For more information, see Merge Conflicts (Master Data Services) and Merge Conflicts (MDS Add-in for Excel).
ChangeSets
You can use changesets to save pending changes to an entity, and you can view and modify pending changes. If the entity requires approval for changes, you must save the pending changes into a changeset and submit for approval by the administrator. For more information, see Changesets (Master Data Services).
ChangeSet Email and Management
In this release, you can now view and manage all changes by model and version. You can also receive email notifications each time a changeset status changes for an entity that requires approval. For more information, see Manage Changesets (Master Data Services) and Notifications (Master Data Services).
View and Manage Revision History
You can view and manage revision history, by entity and by member. If you have update permissions, you can roll back a member to a previous version. For more information, see Member Revision History (Master Data Services).
Save or Open Query Files in MDS Add-in for Excel
From the Entity Explorer page, you can click Excel to save the shortcut query files. Or you can open the query file stored on your computer, in the MDS Add-in for Excel. The saved file can be opened using QueryOpener application. For more information, see Shortcut Query Files (MDS Add-in for Excel).
The query file contains the filters and hierarchy information from the explorer page.
Sample Model Deployment Packages Updated
The sample packages were updated to support new scenarios. For more information, see SQL Server Samples: Model Deployment Packages (MDS).
Get help
Contribute to SQL documentationDid you know that you can edit SQL content yourself? If you do so, not only do you help improve our documentation, but you also get credited as a contributor to the page.
For more information, see Edit Microsoft Learn documentation.
Master Data Services and Data Quality Services Features Supported by the Editions of SQL Server 2016
Deprecated Features of Master Data Services
Discontinued Features of Master Data Services
Training
Learning path
Use advance techniques in canvas apps to perform custom updates and optimization - Training
Use advance techniques in canvas apps to perform custom updates and optimization
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.