SQL Server 2016 CTP 2 released almost a day ago, and I’ve had a chance to install it and play with Master Data Services to see what changes has been made in this product. Master Data Services is a service of SQL Server first released in SQL Server 2008R2, and enhanced in SQL Server 2012. There was no changes in MDS of SQL Server 2014. But fortunately MDS wheels are spinning now, there are some changes in 2016. Changes are not major though they are evidence of Microsoft investing on this services nowadays.
Changes in MDS of SQL Server 2016 CTP 2 are:
- New Layout for MDS Web interface
- Control on Logging configuration for Models
- Data Compression Option for Entities
- Explicit Hierarchies are Deprecated
- Entity Dependencies tab in Web explorer
- Change in Security Functions
- Detailed level of security on models
- SQL Server Jobs for Maintenance Log and Index
- Display Name for attributes such as Name and Code
Areas that hasn’t been changed are:
- Version Management
- Derived Hierarchies
- Excel Add-in experience
- Entity Relationship
- Business Rules
- Subscription Views
- Entity Based Staging
I personally pleased that subscription views, and entity based staging structure had no changes, as they work really well and they are great features of MDS. However I would like to see some new types of rules in Business Rules at least. Now let’s go more in details into changes;
New Layout for MDS Web interface
This change is the very first change that you will feel after installation and configuration MDS. the old MDS web layout has been changed to a new layout which looks more professional now.
Control on Logging configuration for Models
You now have configuration control on logging for the models. Models contains entities, and entities contains data. Data usually have a log, and the log file for transactional data grows time by time. So having a control on the retention is really useful feature. in 2016 you can control that with a setting under Model configuration as below image illustrates.
Log Retention Days configuration means (Sourced from MSDN):
The number of days after which the logs will be deleted. The default value is -1 and indicates the log tables will not be cleaned.
If the value is 0, log tables retain only today’s data. Data logs for the previous days are truncated.
If the value is greater than 0, the log data is retained for the number of days specified by the value.
The default value is -1, means logs won’t be deleted. so be careful that you change it appropriately. the log history contains validation history, transactional log history, ans staging batches history.
Data Compression Option for Entities
SQL Row Level Security helps to compress entity data and its index in a very good level to minimize the I/O operation. this would be specially helpful when the data values range are not so wide. There is however slightly increase in the CPU usage with this compression which needs to be considered when there are limitation on CPU.
In the screenshot above you see that Data Compression option is available for the entity.
Explicit Hierarchies are Deprecated
Well, I never used Explicit Hierarchies in my MDS projects, so I’m not so much concerned about deprecation of it. MDS had previously two kind of hierarchies: Explicit, and Derived. Derived hierarchies are good for set one to many relation between entities. explicit hierarchies are also good for that purpose, but they are build for specific purpose of dynamic level hierarchies. In the new version of MDS Explicit will be deprecated. Screenshot below mentioned that clearly.
Entity Dependencies tab in Web explorer
In previous version of MDS entity relationship could be created in Excel Add-in, but there was no way to see it in the Web UI. the new version of MDS have a tab in MDS explorer for it. Unfortunately I couldn’t manage to have a screenshot of how it works, as my Excel Add-In doesn’t work for MDS 2016, and I couldn’t built a relationship so I could illustrate it here. As soon as the problem solves I’ll put screenshot here for better understanding.
Change in Security Functions
Super User function is the new permission in SQL Server 2016 MDS, this permission works as Server Admin. You might think why this permission even exists? actually I had some troubles with prior version of MDS and the story was: In prior version of MDS the server admin access was given only to the user who originally configured MDS, and there was no way to change it! you might imagine that if that person left the company then how that permission should be assigned to someone else. Actually there was no way to change it through UI. It took me some time to figure out I should apply some changes directly in MDS database tables to change that security. Fortunately this new feature in MDS 2016 resolves that issue.
Detailed level of security on models
Detailed level security in prior version of MDS had three modes: Read-only, Update, and Deny. This method of permission limits you from setting create only, or update only securities. The new version of MDS however is much better with having six modes: Admin, Deny, Read, Create, Update, Delete.
Table below shows the mapping between permission in prior version of MDS and the 2016 version of it (source from MDS team blog post):
|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|
SQL Server Jobs for Maintenance Log and Index
MDS 2016 works with SQL Server Agent because it has couple of maintenance jobs that are for log and indexes.
Display Name for attributes such as Name and Code
You can define display name for columns in the new version of MDS, and that helps for changing display name of Code and Name which were unchangeable columns in prior versions of MDS.