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:
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.
- 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.
- 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.
Posts
0 comments:
Post a Comment