What's New in Master Data Services
Applies To: SQL Server 2016 Preview
This topic summarizes the changes and updates in the SQL Server 2016 release of Master Data Services.
Do you want to try out SQL Server 2016 CTP2? Sign up for Microsoft Azure, and then go Here to spin up a Virtual Machine with SQL Server 2016 CTP2 already installed. You can delete the Virtual Machine when you’re finished.
[CTP 2.0]Improved Performance
Performance improvements enable you to create larger models, load data more efficiently, and get better overall performance. This includes improvement the performance of the add-in for Microsoft Excel has been improved to decrease data load times and enable the 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 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.
The Dynamic Content Compression IIS feature is enabled, by default. This 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.
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. It is recommended that you run the stored procedure each time a large volume of master data is inserted or updated, or after a new version is created from the existing version.
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.
[CTP 2.0]Improved Security
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 later assigned permissions in the model subtree, such as the entity level, he will not lose this Admin permission.
In this release of Master Data Services, we’re providing more granular 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 Community Technology Preview 2 (CTP2) Master Data Services, old permissions are converted to new permissions as shown in the following table.
Permission in previous release
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).
[CTP 2.0]Transaction Log Maintenance
You can now clean transaction logs at pre-determined intervals or on a schedule, using the System settings and at the model level. For a 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_Maintenace, 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).
[CTP 2.0]Improved Troubleshooting
In SQL Server 2016 Community Technology Preview 2 (CTP2) Master Data Services, features have been added to improve debugging and make it easier to troubleshoot issues. For more information, see Tracing (Master Data Services).
[CTP 2.0]Improved Manageability
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, as well as 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.
[CTP 2.2] Custom Indexes
You can create a non-clustered 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).
[CTP 2.2] 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).
[CTP 2.2] Member Revision History
A member revision history is recorded when a member is changed. You can rollback a revision history, as well as 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).
[CTP 2.3] 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. For more information, see Manage Business Rules (MDS Add-in for Excel).
[CTP 2.3] 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 the use of 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)
[CTP 2.3] 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).
>> Be sure to share your feedback on the new SQL Server 2016 capabilities on www.stackoverflow.com.