January 14, 2010 at 9:25 pm
Hi there
I have 2 questions
1) our system is using merge replication on selected tables to replicate data
can i create another replication(transactional) on different tables on same database?
e.g can i have 2 types of replication on same database
2) our developers have set the default value of identity to begin with 0 and use that 0 as default constraint in many places
since the table is in merge replication the manual identity insert didnt work and the the first id value was 2
now they have to change all the default constraint and SPs
Question is
Can i create the table with 0 as start in publisher and the add it to the replication snapshot?
will the first row 0 still be there? will it goin to break the replication?
Cheers
January 15, 2010 at 5:20 am
Hi,
I try to answer for your questions:
1. Yes you can create as many replications/publications ( In fact the correct terminology is "Publications" in your case ) as you wish on the same database even on the same table/article ( table = article in replication terminology), and of course any type of publications Transactional, Merge, Snapshot. The limitations of publications depends on SQL server version and edition.
2. Here we have too many imprecisions. First of all a column having IDENTITY property set cannot have a DEFAULT value. I guess you mention de Identity Seed default configuration is being set to (0,1), this means that the starting value is 0 then is incremented by 1 after every successful or failed insert .
Second, is very strange to set up a constraint to an IDENTITY column to match a discrete value equal 0, or anything else. I thing there is a database design problem.
In case when the constraints are for the existence of the row having the identity column equal to 0, there is no reason to set the identity seed = 0.
If the constraints are in SP-s then you have to insert the first row having Identity value = 0 ( before configure the subscription database ) by manual identity insert, OR let the replication initialization copy the default values from the publisher.
In Merge replication you must use IDENTITY column , witch has to be managed manually or automatically by the sql server, because you must have different identity intervals between all tables participating in the replication topology. The correct management of the IDENTITY column is a base requirement for a smooth merge replication.
The conclusion is that you cannot have the same identity setting ( seed, increment ) on a table participating in merge replication.
If the constrained row must bee there (required by SP), then you have to insert on the publication database and replicate to the subscribers, and set automatic identity management of the IDENTITY column.
I hope may answers was useful and to the point.
Bye.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply