sql server replication: I need to set up replication between 2 servers (both running SQL 2000
SP4) in 1 domain with a network connection.

I haven't done this before and am not finding much in the way of
step-by-step instructions or best practices online or in BOL. There
is a lot of money involved and I don't want to be the part that
breaks down, so I'm looking for detailed advice!

After meeting with the programmers here's what I know: the databases
will be around .5G. They will be on a server in a call center where an
application will query for the record, and then the call taker will
update the record (I think through the application).

Data modifications need to be replicated from server1 to server2 as
quickly as possible. Server2 will be a backup server, so if server1
were to go down during a call, most of the information entered at that
point would have been replicated to server2.

My plan is to set up transactional replication and immediate updating.
I will have 2 test servers to load the databases onto and run tests,
so any test advice would be appreciated.

Thank you!

Im assuming the a Clustered Solution is too pricy or else you'd go down that
route?

Disclaimer: This is stricly my opinion here, but stay away from Replication
for any sort of Disaster Recovery solution.

Replication doesn't take into account, Identity coulmns, SP changes,
Permissions, etc. Sure you can find workarounds for all of these to make
create a DRP with Replication, but IMO theres just too much stuff to account
for. I'd go with Log Shipping. If you don't have Enterprise Ed you can still
use it with a little extra work. I know that many times LS isn't as "up to
the minute" as Replication, but it's worth it in light of the endless
possibilities that Replication can screw you for DR purposes.

1. I forgot to Replicate that table.
2. I forgot to replicate that column.
3. We can't go live yet because I need to alter all the Identity coulmns.
4. I forgot to Replicate that SP.
5. I need to assign permissions.


In the last 5 months I had to fail over to my LS warm standby boxes twice.
Both times I lost some data.(maybe 15 minutes) You know what management said
about my data loss? (I thought they'd be mad.) Nothing! Not a word either
time! The reason for this was because of how quickly I brought us back up to
a fully funtional state. It took longer for the Apps to be pointed to my new
DB's than it did for me to bring them live. There was no question as to if
we had everything we needed to operate.

Just my opinion.



[quoted text, click to view]

I normally use bi-directional transactional replication for something like
this. There are many things you have to watch out for, but it can be done.
One of the cons is that your schema must be very stable, otherwise you will
be tearing it up and rebuilding it each time you need to change the schema.

If you schemas change frequently use log shipping.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
[quoted text, click to view]

It all sounds OK, apart from the immediate-updating part :) - why would you
want immediate-updating? This'll try to start distributed transactions with
the publisher, so if the publisher is down, there will be no data
modifications allowed on the subscriber. Also, there will be issues with
identity columns - if you have any, they will not be recreated on the
subscriber. For this scenario, I would recommend either log shipping, or
transactional replication with queued updating subscribers.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Thanks for the advice. I was going to use immediate updating (it
sounded as if it were what I was looking for: to make sure edits were
replicated to the backup server as close to real-time as possible (the
information is for routing emergency vehicles, so out of date data
could have fatal repercussions).

The programmers had designed the application environment to include 2
servers so 1 would be replicated and therefore redundant. From your
suggestions, it sounds as if this may not be accurate, and there could
be problems getting the second server up and running and available for
access immediately.

Based on all of that, would you recommend log shipping over
bi-directional transaction replication?