How to Merge a Conflict in SQL Server 2016 Master Data Services

By:   |   Comments   |   Related: > Master Data Services


At my company we use SQL Server Master Data Services (MDS) to manage the master data. We have a couple of entities inside a model and we want to know how to prevent multiple users from updating the same entity member at the same time?


In SQL Server 2016, Master Data Services has the ability to merge conflicts. A conflict arises when more than one person has modified a member of an entity at the same time. MDS doesn't prevent you from modifying it, but when you try to publish your changes, you get a chance to review your change and those of your coworkers and choose which change should be kept.

SQL Server 2016 Preview

At the time of writing, SQL Server 2016 is still in preview (currently Release Candidate 3 (RC3) has been released). This means functionality or features of Master Data Services might change, disappear or be added in the final release. To be honest, we're very close to the release date, so it seems unlikely.

Merging a Conflict in SQL Server Master Data Services

I created a very simple entity - with just a few attributes - to test this new functionality. A couple of sample members are already added to this entity.

Sample entity in SQL Server Master Data Services

Let's open this entity in the MDS Excel Add-in (which you can download here).

Excel Add-in for SQL Server Master Data Services

First we're going to try the scenario where multiple people update the entity, but not the same member. Let's update one of the attributes of the second member with the browser using the Explorer section. Once you hit OK in Explorer, the change is immediately committed on the server.

Update Country to USA in SQL Server Master Data Services

Using Excel, we're updating a different member. As you can see, the second member still has the original value for its country.

Update birth date in Excel Add-in for SQL Server Master Data Services

Let's publish this change to the server.

Publish change 1 in SQL Server Master Data Services

However, there's no conflict and the update is published just fine to the server. This is because the add-in only publishes the change and ignores all the other members which are unchanged. Now let's update the same member at the same time. In the browser, we update the city of the first member.

Change member 1 City in SQL Server Master Data Services

In Excel, we also update the city of the first member, but to a different value.

Change member 1 in the Excel Add-in for SQL Server Master Data Services

Next we publish the change to the server.

Publish the change in SQL Server Master Data Services

Now there's a conflict, because the change we want to publish conflicts with the change on the server. MDS will not just update the member to the new value. The reason is because MDS keeps track off all transaction changes. This means that for each change, it knows the old value and the new value. With this change however, the old value is not the same as the current value on the server, so there's a conflict. The change is indicated in red and an error message is displayed.

Conflicts for two City changes in SQL Server Master Data Services

In the add-in, there's a new action in the ribbon to resolve the conflict.

Merge conflict button in Excel Add-in for SQL Server Master Data Services

When you click this button, a pop-up will show you the conflict and it gives you three possible solutions:

  • Keep your value
  • Keep the value on the server
  • Discard all changes and keep the original value
Merge conflict in SQL Server Master Data Services

Let's decide to keep the value from the add-in and click Accept. The change now gets the normal "orange change" color and the InputStatus changes from Error to Unchanged.

Conflict merged in Excel Add-in for SQL Server Master Data Services

However, we're not yet at the finish line! We still have to publish the change to make it take effect.

Publish changes again in SQL Server Master Data Services

When we refresh the browser window, we can take a look at the new value.

Successful data publish in SQL Server Master Data Services

The same merge functionality is present in the browser as well. If you try to update a member which has already been changed by someone else, you immediately get a pop-up window asking you to merge the conflict.

Merged data in the browser for SQL Server Master Data Services

After merging the conflict, don't forget to click on OK to commit the change to the server!


The ability to merge a conflict in Master Data Services is useful new functionality. It makes working with entities in a team much easier and it prevents users from accidentally updating the same attribute of a member multiple times. This functionality is supported in the Explorer at the MDS web site as well as in the Excel add-in.

Next Steps
  • Try it out yourself! You don't need multiple users to test this functionality. You can update a member in the browser and in the Excel add-in to create a conflict.
  • You can find more Master Data Services tips in this overview.
  • For more SQL Server 2016 tips, you can use this overview.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck 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 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

Comments For This Article

get free sql tips
agree to terms