Issues I've Noticed

bpage

SQL Data Compare is a great product that makes my job much easier. But I have noticed a couple issues:

• Inserting and updating tables that are referenced by Indexed Views is very slow in SQL Server. Operations that would take a minute can take an hour. SQL Data Compare should drop these indexes before updates and recreated them afterward.
• SQL Data Compare can reseed on an identity column too low. Say for example you have Server1 with a seed of 100 and Server2 with a seed of 200. Maybe this is a development server and there has been a lot of testing. When you synchronize from Server1 to Server2, Server 2's seed will a be set to 100 and the rows from 101 to 200 will be removed. However, if someone were to perform an Inserting during the synchronization there could be a row with a value of 201 in the identity column. This could by adding an option to let SQL Server recalculate the seed column.

  DBCC CHECKIDENT('schema.table', RESEED)
  

Chris Auckland

Thanks for your post.

Inserting and updating tables that are referenced by Indexed Views is very slow in SQL Server. Operations that would take a minute can take an hour. SQL Data Compare should drop these indexes before updates and recreated them afterward.

SQL Data Compare has the option to 'Drop primary keys, indexes, and unique constraints'. This option will drop and then recreate the PK's, indexes and unique constraints in the generated SQL script. The option can be found in Options > Synchronization Behaviour. Hopefully it will do what you need.

SQL Data Compare can reseed on an identity column too low. Say for example you have Server1 with a seed of 100 and Server2 with a seed of 200. Maybe this is a development server and there has been a lot of testing. When you synchronize from Server1 to Server2, Server 2's seed will a be set to 100 and the rows from 101 to 200 will be removed. However, if someone were to perform an Inserting during the synchronization there could be a row with a value of 201 in the identity column. This could by adding an option to let SQL Server recalculate the seed column.

SQL data compare will set the seed value when it interrogates the schema before the data comparison. If data in the table is modified between running the compare and applying the sync script, then the seed value could be invalid. You can refresh the seed value by selecting Refresh on the Tables & Views tab in project configuration.

Alternatively, you could turn off the option to 'reseed identity columns' and let SQL Server worry about setting the seed value. However, this will mean that the seed values will probably not be in sync with the source.

I hope this helps.

bpage

Chris Auckland wrote:

Thanks for your post.

Inserting and updating tables that are referenced by Indexed Views is very slow in SQL Server. Operations that would take a minute can take an hour. SQL Data Compare should drop these indexes before updates and recreated them afterward.

SQL Data Compare has the option to 'Drop primary keys, indexes, and unique constraints'. This option will drop and then recreate the PK's, indexes and unique constraints in the generated SQL script. The option can be found in Options > Synchronization Behaviour. Hopefully it will do what you need.

This does not drop Indexes on Views that reference the table. That's my point. It really should. These indexes place constraints on the table just as if there were on the table itself and the enforce schemabindding on the table.

Chris Auckland

SQL Data Compare can drop the index from an indexed view, but only if it is not being used for the comparison key.

What are you using as the comparison key for the view?

mako0201

bpage wrote:

[*]SQL Data Compare can reseed on an identity column too low. Say for example you have Server1 with a seed of 100 and Server2 with a seed of 200. Maybe this is a development server and there has been a lot of testing. When you synchronize from Server1 to Server2, Server 2's seed will a be set to 100 and the rows from 101 to 200 will be removed. However, if someone were to perform an Inserting during the synchronization there could be a row with a value of 201 in the identity column. This could by adding an option to let SQL Server recalculate the seed column.

DBCC CHECKIDENT('schema.table', RESEED)

I have the same issue! I'm running data synchronization on a table from server A to B with "include identity columns" option checked. The new seed value in the DBCC CHECKIDENT statement is getting set to maximum value in the identity column of the table but on server A!!! I think it should take maximum value in the identity column from the table on server B, as it is the server where the changes will appear, but maybe I'm missing something. Please advise.

David Atkinson

Please go to Project Options and uncheck the option Reseed Identity Columns. In the next release we hope to change this option to be deselected by default to avoid situations that you describe where the source database's seed isn't suitable for the target.

You can select Save as My Defaults which will ensure that future projects use this options set.

Kind regards,

David Atkinson
Red Gate Software