sql server replication: Hi all

:) I actually use it quite extensively for failover.

For high availability, that is along best practice lines. Best practices
basically says that your options for availability go in the following order:
1. Clustering
2. Log shipping
5. Replication :)

If clustering is ruled out due to costs and log shipping is ruled out due to
latency, then you have three options in the order that I would suggest them.

1. Transactional with queued updating
2. Merge
3. Transactional

Do NOT try to do transactional with immediate updating for a failover
scenario, you'll have nothing but problems.

--

Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Transactional is sometimes used for failover, however, I would not recommend
it. On the subscriber you will not have defaults or identity columns that
your application may depend on. Also, you'll not be able to replicate users
or permissions. For failover to a standby server I'd recommend log shipping
or clustering. Out of these 2, clustering is the method which will give you
automatic failover.
HTH,
Paul Ibison

Michael,
thanks for this - it is very interesting. I have wondered about using
Transactional with queued updating even when the subscriber had no intention
of changing the data, purely because it has the advantage of creating the
defaults and identities on the subscriber. Helpful to know that you are
using it in this case.
Regards,
Paul

you can create your publications so that the subscriber tables have defaults
and the identity property on their columns

have a look at the sp_addarticle @creation_script parameter with the
@schema_option = 0x00

Make sure you use the Not For Replication option:)

I strongly recommend using bi-directional transactional replication when you
are using replication for fault tolerance. In this case you know that only
one side will ever be updated at one time. Using bi-directional replication
for other situations is possible but you have to partition carefully and
plan for conflicts.


[quoted text, click to view]

Thanks Hilary - I'd seen this option in the arguments before but had never
appreciated its significance.
Regards,
Paul

It actually isn't because it creates the defaults, identities, etc.

It is suggested because you have a resilient configuration that allows
applications to nearly instantaneously redirect to a standby server. And
moreover, the standby server has the intelligence to understand what is
going on. It can also automatically synch the primary with everything that
happened when it comes back online and you can redirect users at your
discretion. It also gives you one other thing, scalable availability. If
your back is against the wall and you need more write throughput for a short
period of time, you can use both sides if necessary.

I also don't recommend doing bi-directional unless you really, really,
really, really, really, really, really, know what you are doing AND you have
100% control over every single application that hits your database, because
if not, you are going to blow it up.

--

Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

I wish you guys would have offered this as a solution for me :-) . So far it works like a charm. Queued updating was the one setting I never tried..

Thank you so much