By: Koen Verbeeck | Comments (2) | Related: > Master Data Services
Problem
SQL Server 2016 introduced many new features and improvements for all its products, and Master Data Services (MDS) is no exception. In the past year, over a dozen tips have been written about the changes for MDS. This tip gives a comprehensive overview of all the new features and links back to the original tips for more details.
Solution
User Interface Redesign
One of the more pleasant features is the redesign of the user interface of the MDS website. It has an updated look, sports a tile view at the home page and just looks like something that has been built this century. An advantage is that the website now beautifully renders in most browsers, instead of just Internet Explorer.
Unfortunately, not every section of the website has already received its redesign. Some are still in the old layout. Most noticeably is the Explorer, which keeps to be rendered in Silverlight. This means browsers other than Internet Explorer possibly continue to have issues with the Explorer.
Performance Improvements
A lot of effort has been put into making MDS faster than its predecessors. This means you can create larger models and load data faster into MDS, but also querying MDS might have better performance. The following improvements have been made:
- The MDS Excel add-in has been improved to reduce data load times and to enable to handle much larger entities.
- You can enable data compression on an entity in order to reduce compress the data within the entity (and its indexes). This leads to less storage being used, but also less I/O traffic when reading data, but a bit more CPU usage in order to compress or decompress the data. MDS uses only row compression. Sadly page compression isn't used, which would give even more storage savings, especially since data in MDS typically compresses well due to redundancy (e.g. in domain based attributes). When creating new entities, they are by default compressed. You can read more about compression in MDS in the tip Using Data Compression in Master Data Services 2016.
- MDS uses the Dynamic Content Compression feature of IIS. This reduces the size of XML responses which limits network traffic. As with data compression, CPU usage is increased. If you want to disable this feature, you need to modify the MDS web.config file:
<configuration>
<system.webServer>
<urlCompression doStaticCompression="true" doDynamicCompression="false " />
</system.webServer>
</configuration>
- You can create custom indexes on an attribute of an entity. This can be one single attribute, but also a list of attributes which leads to a composite index. You can also mark an index as unique, which can help forcing uniqueness in an entity. Keep in mind MDS already creates quite some indexes on an entity, especially if domain based attributes are used. If you want to know more about custom indexes, please refer to Add a Custom Index in Master Data Services 2016.
- Two maintenance jobs have been added: one for cleaning up the transaction log and one for index maintenance. Both are implemented as SQL Server Agent Jobs. The index maintenance job performs some partition management as well (only relevant if you use multiple versions for your models). If an index is more than 30%, it is rebuilt online.
The transaction log maintenance job will clean up the following types of data: transaction history, validation issues and staging batches. You can configure the number of days for which the log has to be kept for each model. If you don't configure a value for a model, the system default is taken, which is configured using the MDS Configuration Manager. The default is -1, meaning all logs will be kept.
The blog post Master Data Services 2016 Performance Enhancements by the MDS team details a test scenario where the performance of MDS 2016 is compared with MDS 2014 SP1. Some operations have over 2000% performance improvement!
Improved Security
MDS 2016 brings a lot of changes to the security model.
- Some roles have been made explicitly, such as the super user role, the model administrator and the entity administrator.
- Permissions can now be set on a more granular level. While previous versions only had read, update and deny, you can now choose between the permissions create, read, update, delete and deny. If a user is assigned create, update or delete, the read permission is automatically granted.
You can find more info about MDS security in the tip Permission Changes in Master Data Services 2016.
Improved Troubleshooting
MDS introduces a couple of changes in order to improve debugging and troubleshooting. Most noticeably are the new trace capabilities, which are configured in the web.config file. The trace writes by default warnings and activity tracing to log files. You can find these in C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication\Logs. You can find an example of how I used the tracing to troubleshoot a business rule error in the blog post Database Error has Occurred when Publishing Business Rule. You can find more information about trace settings in the MSDN page Tracing (Master Data Services).
Next Steps
- You can find a full overview of all the new MDS features on the MSDN page What's New in Master Data Services (MDS).
- Also check out the Master Data Services team blog, where additional info can be found on the new features.
- You can find more Master Data Services tips in this overview.
- For more SQL Server 2016 tips, you can use this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
