Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
New Features in SQL Server Master Data Services
To successfully and meaningfully merge disparate data sources, you need to have a common or master format for the data and a set of rules to translate the different data elements into a common format. That’s exactly what MDS does.
Michael Otey
September 9, 2016
3 Min Read
First introduced with SQL Server 2008 R2, Master Data Services (MDS) is one of the lesser-known SQL Server subsystems. It’s not used nearly as widely as some of the other, better-known subsystems, such as Analysis Service, Integration Services or Reporting Services. However, MDS plays a very important role for businesses that need to create single master data view of their data, when that data comes from multiple data sources and is described differently in each of those sources.
This kind of data disparity is often the result of mergers and acquisitions, where a company has acquired one or more organizations over time and needs to merge data from these other entities with its own data for centralized reporting. To successfully and meaningfully merge disparate data sources, you need to have a common or master format for the data and a set of rules to translate the different data elements into a common format. That’s exactly what MDS does.
Some of the most important new features in Master Data Services include:
Data compression is enabled by default – Entity-level data compression is enabled by default. This feature uses SQL Server Row Level compression for all the entity-related tables and indexes, which reduces disk I/O.
Dynamic Content Compression is enabled by default – Dynamic Content Compression significantly reduces the size of the XML response and reduces network I/O.
New SQL Agent maintenance jobs – SQL Server 2016 includes the new MDS_MDM_Sample_Index_Maintenance and MDS_MDM_Sample_Log_Maintenance jobs for periodic index and log maintenance.
New Super User function -- The new Super User function permission gives a user or group the same permissions as the Server Admin.
More granular permissions – There are now more granular levels of permissions. SQL Server 2016 MDS provides the following new permissions: Read, Create, Update and Delete. You also can combine the Read, Create, Update and Delete permissions.
Schedule transaction log cleanup -- Transaction logs can be cleaned up at pre-determined intervals or on a schedule. This is important because MDS systems with many data changes can have very large transaction logs, which can impact performance. You can also call stored procedures to clean the transaction logs.
MDS Add-in for Excel -- The new Master Data Services Add-in for Excel enables users to create and edit rules to validate MDS data.
SQL Business Rules Extensions -- You can now use SQL scripts as extensions of MDS business rule conditions and actions.
Custom Indexes --You can create a non-clustered index on one entity attribute or on a list of attributes to improve query performance.
Improved Many to Many Mapping -- MDS in SQL Server 2016 has enhanced its ability to manage many-to-many relationships. When you use an entity as a mapping table for other entities, the Master Data Manager now lets you click through and view related entities.
Entity Sync Relationship -- You can share entity data between different models using a new entity sync relationship.
Member Revision History -- The member revision history is recorded every time a member is changed. You can rollback a revision history and view and annotate revisions.
HPE and Microsoft are the underwriters of this article.
About the Author
You May Also Like
