Learn About SQL Server Master Data Services 2016

SQL Server 2016 comes up with the new release of Master Data Services (MDS). In it, some new features have been added to improve its performance. It helps the database administrators to manage the database properly. In the following discussion, we will be discussing the changes that have taken place in SQL Server Master Data Services 2016.

In-Depth Study of Latest Features in Master Data Service

There are various changes that have taken place in MDS SQL Server 2016 are discussed below:

Configuration control

There is a control on logging configurations for models. Models enclose the entities and the entities contain data. The data have a log file that keeps on increasing depending upon the transactional data. Therefore, it is important to have the control on retention and it is a necessary feature. In MDS 2016, user can control it with the mentioned setting under Model Configuration. It also gives an option to manage the Log retention days. By default, it is 1 and indicates that the log tables will not be cleaned. If the value is 0, then the log tables preserve only day’s work. The data logs of the previous days are truncated. If the value is greater than 0 then, the log will retain for number of days that is specified by value. The default value is -1, which means that log will not be deleted. Therefore, user must take care while changing it as it contains the history of transactional log, validation, and staging batches.

Data Compression

Row-level security in SQL helps in compressing the entity data and indexes it in a way to minimize the I/O operation. It is helpful at the time, when values of data are not so wide. However, there is an increase in CPU usage with this compression, which is needed to be considered when there are some limitations on CPU.

Explicit Hierarchies

SQL Server Master Data Services previously had two kinds of hierarchies, i.e. Explicit and Derived. Explicit hierarchies are built for some specific purpose of dynamic level of hierarchies. Derived hierarchies are used to set one to many relations between the entities. In SQL 2016, Explicit is deprecated.

Entity Dependencies

In the previous edition of MDS, the relationship entity could be created in Excel Add-in but there is no way to be visible on web. The new edition of MDS has a tab in MDS explorer for it to view on web.

Functioning of Security

There is the new permission in SQL 2016, i.e. super user function. This permission works as Server Admin. In the previous edition of MDS, the server admin access is given to the user who configured the MDS and no one can change it. However, in this new feature in Master Data Service in SQL Server 2016 helps to resolve this issue.

Detailed-level Security

In the previous edition of MDS, there are three modes- Update, Read-Only, and Deny that limits the settings create only, or update only securities. Whereas in the new version, there are six modes available, i.e. Deny, Read, Admin, Create, Delete, and Update.

Display Attributes Name

User can display the name for columns in the latest edition, which helps in changing the display name of code. It makes easy for users to set the desired name accordingly. These were unchangeable in the prior editions of MDS.

Conclusion

In the above discussion, latest features in SQL Server Master Data Services 2016 are described. It helps users in having the deep understanding about the features of MDS 2016 for its proper usage.