By: Koen Verbeeck | Comments | Related: > Master Data Services
Problem
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?
Solution
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](/tipimages2/4307_SampleEntity.jpg)
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](/tipimages2/4307_AddIn.jpg)
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](/tipimages2/4307_update_USA.jpg)
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](/tipimages2/4307_birth_date.jpg)
Let's publish this change to the server.
![Publish change 1 in SQL Server Master Data Services](/tipimages2/4307_publish_change.jpg)
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](/tipimages2/4307_change_member.jpg)
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](/tipimages2/4307_Excel.jpg)
Next we publish the change to the server.
![Publish the change in SQL Server Master Data Services](/tipimages2/4307_Actual_index.jpg)
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](/tipimages2/4307_All_indexes.jpg)
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](/tipimages2/4307_merge_conflict.jpg)
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](/tipimages2/4307_solutions.jpg)
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](/tipimages2/4307_conflict_merged.jpg)
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](/tipimages2/4307_publish_again.jpg)
When we refresh the browser window, we can take a look at the new value.
![Successful data publish in SQL Server Master Data Services](/tipimages2/4307_success.jpg)
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](/tipimages2/4307_merging.jpg)
After merging the conflict, don't forget to click on OK to commit the change to the server!
Conclusion
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.
About the author
![MSSQLTips author Koen Verbeeck](/images/Koen-Verbeeck-2018-2.png)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips