Seeds incorrect if direction of synchronisation is changed

redeye

I have set up a project where I want to synchronise the contents of a database on my local machine to a remote server. That works fine. If I change the direction of the synchronisation, the data is synchronised correctly, but the seeds are not. I have set up a second project where the source database and destination are "reversed" and then it runs correctly.

So, to make this a bit more clear...

In my first project I am synchronising from Database A to Database B. Which ever direction I do the synchronisation in, I end up with the following code:

-- Reseed identity on [dbo].[LoginLog]
DBCC CHECKIDENT('[dbo].[LoginLog]', RESEED, 195)
GO


Now I set up a new project and have that synchronise from B to A (ie B is set up as the "source" database)

Now it generates the following code:

-- Reseed identity on [dbo].[LoginLog]
DBCC CHECKIDENT('[dbo].[LoginLog]', RESEED, 224)
GO

The seed value is different.

What is happening, is the code is always using the seed value on the "left hand" database irrespective of the direction of the synchronisation. I feel this is wrong.

Regards

Paul

richardjm

I have a test table here and when I change direction the RESEED does indeed change when the direction changes. Not sure how you're managing to see an issue.

I'll log it to see if the testers can find the issue. If you could send some data or backup of the tables that reproduces the problem that would help us greatly. At least there is a work around for the moment even if the need for it is a bit strange.

We'll certainly look into it.

HTH

redeye

To reproduce the problem, please do the following:

1) Create a table on the source database:

CREATE TABLE [dbo].[SeedError](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Data] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_SeedError] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

2) Use SQL compare and have the same table created on the destination databse

3) On the source database, execute the following:

INSERT INTO [SeedError] ( [Data] )
VALUES ( 'Row 1' )

INSERT INTO [SeedError] ( [Data] )
VALUES ( 'Row 2' )

4) Use SQL Data Compare to synch up the contents of this table

5) On the DESTINATION database, execute the following:

INSERT INTO [SeedError] ( [Data] )
VALUES ( 'Row 3' )

INSERT INTO [SeedError] ( [Data] )
VALUES ( 'Row 4' )

6) In SQL Data Compare, switch direction of the synchronisation, and run the wizard for this table. Look at the SQl that is generated. You will see:

-- Reseed identity on [dbo].[SeedError]
DBCC CHECKIDENT('[dbo].[SeedError]', RESEED, 0)
GO


That is incorrect. If you run the synchronisation and then try and insert a row on the source database, you will get a primary key vioaltion error.

Regards

Paul

richardjm

I think the issue may be to do with the schema not being refreshed (which is where the information on the seed in stored) between your comparisons. When you click "Refresh Comparison" it refreshes on the data of the table not the schema of the tables.

If you go into the comparison wizard and click to refresh the schema and then refresh the comparison I think it might work out, seems to be working here.

Not very nice I agree and we should look into improving that in the future.

redeye

Yep, that seems to fix it. If you have a project which compares "A" against "B", and simply switch synchronisation direction after the compare is done, then you get this problem. If, as you say, you refresh the schema after switching direction (or exit Data Compare and start it up again -- assuming you have saved the project after switching direction) then the problem does indeed go away.

It is only when you do everything "in one go" without a schema refresh that the problem occurs.

Thanks for your help.

Paul

richardjm

Glad we worked out what was going on. I was thoroughly confused for a while there

redeye

Richard,

I'm still getting this problem. Could it be that the project was originally set up as comparing "left to right" and now I am comparing "right to left". When the project opens and refreshes the schema, could it be using the seeds from the left hand database ?

The point is, after a synchronisation, I am getting primary key errors. There is then no point in re-synching the databases after that as they are identical, and (as reported by me in a previous post), if the tables are identical (or empty) but the seeds are different, Data Compare does nothing about this.

I really feel this behaviour is erroneous given there is an option to "reseed identity columns". If Data compare says they are the same then the behaviour on both databases should be the same... but it isn't. I understand where you are coming from by way of explanation, but from where I am sitting, this is a bug!

Regards

Paul

richardjm

We've had a comment about the reseed empty tables although we'll have to work on the UI to incorporate the changes necessary to give the option.

It's certainly in there for future but won't be in 6.1 as we're in final testing of that version already.

I'll have a chat with our UI designers in the new year and see what we can work out.

redeye

Thanks Richard ... but to be clear, it is not just empty tables that this should work for. If two tables are the same but theior seeds are different, Data Compare does nothing. It should synch them up. That shouldn't need a change to the UI -- you already have the tick box which says "synchronise seeds" ... just make that work!

Regards

Paul

richardjm

The problem is not that the option is there the problem is that how would you turn it off for a table in particular as there are no changes in the table data the check boxes on the main form aren't shown.

Nothing for the user to un-check if they don't want the seeds to be synchronised for a table that has no data changes but that has been compared.

(currently getting brain in gear from a lovely two week break)