Friday, October 14, 2016

Synchronizing databases with stored procedures referencing FREETEXTTABLES

Applies to: xSQL Schema Compare v7.0.0 - v9.0.0

Some time ago, I had to copy an entire database to a new one that was already created on Azure. So, of course, I decided to use xSQL Schema Compare and Data Compare, given that the “backup/restore” option was not available. There were two elements in the database that made this case unique in regards to the schema synchronization process:
  • One of the tables (let’s call it ‘T’) had a full-text index.
  • There was a stored procedure (we will call this ‘SP’) that used table ‘T’ as a FREETEXTTABLE.

Full-text indexes are non-transactional by design, so in the synchronization script for the new database, these are added after the main transaction, which synchronizes all the other elements including the stored procedures.

The issue: The synchronization script tries to create a stored procedure which uses the table ‘T’ as a FREETEXTTABLE, before the full-text index is created. SQL Server will not allow this because to use a table as a FREETEXTTABLE, there needs to be a full-text index in this table.

The workaround: Thankfully, the comparison process is customizable and the solution to this problem is just a matter of checking/unchecking a few database objects from the comparison options. For this particular case, the comparison and synchronization needs to be done in 2 steps, each with different comparison options.
  1. Exclude stored procedures by unchecking the “Stored Procedure” checkbox (picture bellow) and compare and synchronize the databases. Schema Compare will not generate a script to synchronize stored procedures, which means that SQL Server will have no problem with adding full-text indexes at the end of the synchronization.
  2. Do the comparison again, but this time, uncheck everything except for the Stored Procedures (leaving them checked is not an issue, but it will take longer and since the other objects were synchronized, there is no need to compare them again) in the Database Objects tab. This time, a synchronization script will be generated only for the stored procedures, which were not synchronized on the first step.
Conclusion: As a general practice, to solve problems where the order in which database objects are synchronized becomes an issue, all you need to do is remove the objects that are causing problems from the comparison, compare and synchronize, and then do another comparison and synchronization only for the objects that were excluded in the first step.   


Post a Comment