SQL Server conflict resolution is not a light reading topic. This article teaches you how to resolve SQL Server synchronisation conflicts for merge publications, but requires you to have some prerequisit knowledge:
- Firstly you must know how to write SQL queries at a basic level and know the basics of how a database is put together. You will need to know how to do SQL SELECT, UPDATE and DELETE commands and understand the concepts of unique keys in a database
- Secondly, you must be able to find out how the tables are connected in the database
- Thirdly, you must be familiar with your database at a business level or have a savvy business champion at hand. This is important for knowing which data must be discarded in the event of a SQL conflict
What is a SQL Server Conflict?
When you synchronize two SQL databases with each other, sometimes there can be conflicts. A conflict is where a row in each database cannot be merged. This can happen for a few different reasons that I won’t go into, but fundamentally the problem is that the unique keys in a row in one database table are the same as the unique keys in a row in the database table that is being synchronised, the non unique data in the same row is different and the SQL Server doesn’t know which data should be kept / overwritten.
How To Resolve An SQL Server Conflict
Once the SQL Synchronisation has finished, it should say something along the lines of Synchronization completed with 0 resolved conflicts. If it says any number other than 0, you have SQL Server conflicts that need to be resolved (even though it says that there are resolved conflicts – confusing, I know).
The first thing to do is find out what the conflicting data is. You can do this by right clicking the merge publication on the SQL Server and clicking View Conflicts. Clicking on each conflict in the list will give you details about the row that’s conflicting.
The next thing to do is to go through each conflict one by one, finding the row of data in both databases and viewing it in a SQL SELECT statement. You should identify the unique keys that correspond to these rows in the table.
Once you’ve identified your SQL Server conflict, you need to put your business hat on and decide which data should be kept.
- Should the row on one server overwrite the row on the other server?
- Should the data in both rows be kept?
- Perhaps neither should be kept?
- Perhaps both are slightly incorrect and need updating?
You will only be able to answer these questions by knowing the business processes and some sleuthing may be involved to avoid data corruption.
Here’s a few examples / scenarios of how to decide which data should be kept:
- One SQL Server is an archive of the other SQL Server, so it always holds data that should be overwritten
- There are two SQL Servers, one has data from an American version of the corporate website, the other is English. The SQL Server conflict pertains to an English transaction, so it looks like the data on the American SQL Server should be overwritten, but a little research shows that the English customer is currently working overseas in America, so the American SQL Server has the data to retain this time…
- Rows in a SQL database table have a timestamp attribute, so the data with the latest timestamp holds the correct data
- Rows in a SQL database table have a timestamp attribute, but once rows are entered, business logic dictates that they shouldn’t be changed so long after the first entry. You seek out the computer operator and they tell you that it was an accident; the data with the older timestamp should be replicated
As you can see, deciding which data to keep in a conflict requires some business logic and may require some sleuthing, but is essential to the integrity of the data.
Once you’ve decided which data to keep, the next stage is to resolve the SQL Server conflict. Here’s where your SQL query knowledge comes in. Depending on the data, you will either want to delete a record from one SQL Server and do another SQL Synchronisation (merge) to replicate the correct data to the other server, or you will want to keep both bits of data.
Keeping both bits of data is easiest. Simply change one of the unique keys on either SQL Server then click Remove Conflict in the conflict viewer before doing another synchronisation.
To remove an incorrect, first try choosing a winner or loser in the conflict viewer (depending on which record you want to keep), then do another synchronisation. This is the fastest and easiest way to resolve a SQL conflict. Unfortunately, it rarely works and you will probably see your conflict come back.
If this didn’t work, try deleting the record on the SQL Server with the incorrect data, then perform a synchronisation. I strongly advise that you take a backup prior to doing this because sometimes SQL thinks that the incorrect record and the correct one are the same and will delete the correct one when you perform your next sync.
After deleting a record, you will enter one of three scenarios:
- The record will delete, you do your merge synchronisation and the correct data is replicated across with 0 SQL Server conflicts. Your job is done and you’ve resolved the conflict 🙂
- The record will delete, you do your merge synchronisation and the correct data is deleted during the synchronisation. In this case, you are reverting to your backup and you’ll have to change one of the unique keys on the incorrect row to something unique, remove the conflict in the conflict viewer, synchronize the database, check that the correct data is present and there are no new conflicts, then delete the incorrect data and do another synchronisation. Don’t forget to check that the correct data is there after you synchronised and that there are no new conflicts. Deleting the incorrect data may cause you to enter scenario number 3, in which case, simply take note of the causes of your inability to delete the data and update any references to the incorrect record to the correct record (assuming that the references should be pointing here, otherwise should they be deleted?)
- You won’t be able to delete the record because of a relationship between the data you are trying to delete and some data in another table. In this case you will have to decide whether the other data should be deleted or perhaps it should reference the correct data?
If it should reference the correct data, change a unique key on the incorrect data, remove the conflict, perform a synchronisation, then update any records that complain about the incorrect data being deleted to reference the correct data record. Finally, remove any SQL conflicts and perform another synchronisation before checking that correct data hasn’t been deleted during the synchronisation.
If it should be deleted, delete it before deleting the other record (where’s where understanding the database layout comes in handy). You may have to delete a lot of records before being able to finally delete the original conflicting incorrect data, removing the conflict and performing a final synchronisation. If you’ve deleted data, don’t forget to check that the correct data hasn’t been deleted during the synchronisation.
As you can see, resolving SQL Server conflicts is not simple and requires business logic / knowledge as well as SQL Server knowledge. Depending on the conflict and complexity of the database, it can take many hours to resolve a SQL Server conflict.