What's New in SQL2016 CTP2 Release

[SSIS-Team]

First published on MSDN on May 27, 2015
SQL Server 2016 CTP2 -- Now Available for public download.  In CTP2 release, there are many new MDS features.

What’s New for MDS

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.

For more information, see Create an Entity (Master Data Services) and Data Compression .

• 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.

Copy

<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.

  
  
  
  
  
  • 
    

    MDS_MDM_Sample_Index_Maintenace

    
    
  
  
  
  • 
    

    MDS_MDM_Sample_Log_Maintenace

    
    
  
  
  
  

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 .

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	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

For more information about permissions, see Security (Master Data Services) .

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) .

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) .

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.