Snapshot with Identity field preservation - "Identity Crisis" -- sql server replication

SoulCages
3/14/2007 11:45:00 AM

Hi Paul,

Thanks for the reply and the tip on queued updating subscribers. I did see
that this setting was turned to "off" in the article properties. I am
actually trying to figure out how to turn it on. I stumbled across this
article on Microsoft's site:

http://msdn2.microsoft.com/en-us/library/ms146889.aspx

Should I just follow the section "To create a publication that supports
queued updating subscriptions" ?

To answer your question (it's a good one), we are actually planning to
migrate over to the off-site server in the future and that is why we want to
preserve the identity field. The server we are replicating our data to is
much faster and once converted, we would transition the replication to go in
the other direction to have total redundancy at both locations. In addition,
we have more redundant disks in our RAID array on one server than the other
so we want to keep the identity fields intact in case a recovery was
necessary, since we do not have physical access to the server that is
off-site and must work with our IT support out at that data center.

Thanks again for your help.

[quoted text, click to view]

SoulCages
3/14/2007 1:21:03 PM

Thanks Paul, those are excellent suggestions. I will look into doing that.
Now if I can just figure out how to turn on that queued updating subscribers
feature... my project for tomorrow - looks like that article in my previous
response will help me accomplish that task. :)

Thanks again for your input!

[quoted text, click to view]

Paul Ibison
3/14/2007 4:09:30 PM

If you set up queued updating subscribers you'll have the identity attribute
maintained, and on the article properties tab you can select to have
automatic identity range management. This way you'll have everything you
need on the subscriber. No doubt there's a good reason, but I'm interested
though in why you'd need the identity property on the subscriber if the data
will be wiped on the next initialization anyway.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Paul Ibison
3/14/2007 8:07:36 PM

For redundancy like this, I'd set up transactional replication with queued
updating subscribers. Data can then flow in either direction. If you use
snapshot replication the chnages made on the publisher may be few but the
entire snapshot will have to get sent over to get these data changes to the
subscriber. There is also a more granular reporting capability for
transactional of exactly what has changed.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Paul Ibison
3/15/2007 12:00:00 AM

To enable updating subscribers in the GUI, you need to enable the advanced
options (checkbox on the first page of the wizard). This must be done for
both the publisher and subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

SoulCages
3/19/2007 11:24:35 AM

Paul,

Thanks for your help.

I noticed that when attempting to set up transactional replication with
queued updating subscribers, it will not allow me to replicate tables that do
not have a primary key, but when I chose Merge Replication, it seems to allow
them to be replicated. Can you please explain the difference between the two
methods (Transact vs. Merge)? I do not understand why transactional
prohibits table publishes without primary keys, but merge does.

Should we consider adding identity fields to all of our tables that do not
have primary keys? Our lead developer has concerns that this may cause
problems with performance since we have some indices set up to perform
certain queries.

Thanks again Paul, you have been a huge help.

Ryan

[quoted text, click to view]

Paul Ibison
3/20/2007 12:00:00 AM

Queued updating subscribers is very similar conceptually to merge
replication.
It doesn't scale as well as merge, can't deal with BLOBS but is usually much
faster than merge.
Also, as you found out, there is a PK requirement which doesn't exist in
merge. You could ass a surrogate key (identity) to enable transactional -
the overhead should be pretty minimal (4 bytes per row for the extra column
and then there is the index).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : Snapshot with Identity field preservation - "Identity Crisis"