Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to sync an entity in SQL Server 2016 Master Data Services


By:   |   Read Comments   |   Related Tips: More > Master Data Services

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

I have multiple models in Master Data Services (MDS). Some of the entities occur in all models and keeping them update in all models takes a lot of work. Is there a better way to do this and reduce maintenance?

Solution

Master Data Services has a new feature in the SQL Server 2016 release: Entity Sync. With this feature, you can easily sync an entity from a model to other models. When you update the entity in the base model, the changes are reflected in the synced entities.

SQL Server 2016 Preview

At the time of writing, SQL Server 2016 is still in preview (currently Release Candidate 0 (RC0) has been released). This means functionality or features of Master Data Services might change, disappear or be added in the final release.

Test Set-up

To test this new feature, we'll use the MDS sample models. When MDS is installed on a server, sample models are copied to the installation folder. You can find them in for example in "C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages". This location can vary slightly depending on your SQL Server installation and version. You can follow this guide on how to install them in your MDS environment. In total, three models are available:

  • Products
  • Customers
  • Chart of Accounts

We will sync the country entity from the Products model to the Customers model.

Sync the country entity from the Products model to the Customers model

It's important to notice that the Country model already exists on the Customers model. This means you can start using Entity Sync in an existing environment without much troubles.

Use the Entity Sync in an existing environment

Syncing an Entity

To configure Entity Sync, go to Administration, then click on Manage and finally choose Entity Sync

Entity Sync menu

Next, click on Add to create a new instance of Entity Sync.

Add new Entity Sync

The configuration is quite straight forward. You have to specify the source model and entity, along with the version of that model (if applicable). You also need to specify the target model of course, along with its version. You can choose if you want to create a new entity, or if you want to use an existing one as target. If you choose a new entity, the name can't already be taken by another entity.

Configure new Entity Sync

Since the Country entity already exists, we'll choose Existing Entity from the dropdown menu.

Choose an Existing Entity

The final option is the frequency of the entity sync. Either you run the synchronization on demand, or you let MDS schedule it.

Frequency of the entity sync

The schedule can either be expressed in days or in hours.

Schedule for the entity sync

This schedule is implemented behind the scenes by a SQL Server Agent job that runs every hour.

SQL Server Agent Job that is created based on the schedule

The Entity Sync is now configured. However, what happens if the target entity is out of sync with the source entity? For example, I added an extra country in the Customers model, which doesn't exist in the Product model. In such a case, the initial sync will fail and the entity sync won't be created.

Out of sync error message

Let's delete this rogue country and start again.

Delete a member and start again

Now the initial sync succeeds and the entity sync instance is created. It has been created to be run on demand, as it's easier to test it that way.

A new entity sync is born

Let's test this new feature by adding a new member to the source entity in the Product model.

Test the new feature by adding a new member

We can now run the Entity Sync by clicking on Execute. You'll get a message stating the sync was successful or not.

Execute the entity sync

Let's verify if the new member has been added to the Country entity in the Customers model.

Verify the synced member

Notice that in the screenshot above, all the members of the target entity are locked. You cannot create, change or delete a member in the target entity. You can now only modify the source entity. It's also impossible to modify the attributes of the target entity.

Modify an entity

A property is added to the entity overview, stating of the entity is synced or not, as can be noticed in the previous screenshot.

Notice it's not possible to sync an entity if the source entity is the target of another sync. In other words, you cannot "chain" syncing entities.

Error message indicating you cannot chain entities

It's possible though to sync one single entity multiple times. We can easily test this by syncing the Country attribute of the Product model to another entity in the Customer model.

Sync on single entity multiple times

The new entity is created without an issue:

Sync entity again with success

Conclusion

Entity Sync is an easy to use new feature in Master Data Services 2016. It always you to synchronize an entity from one model to one or more target models. If you make a change to this source entity, the changes are reflected in the target entities. The changes are either synced on demand, or by a pre-defined schedule (per hour or per day). In existing models where the entities already contain data, the data must already be in sync before the Entity Sync feature can be enabled.

Next Steps
  • Try it out yourself! Install the sample models and start syncing some entities!
  • You can find more Master Data Services tips in this overview.
  • For more SQL Server 2016 tips, you can use this overview.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools