SQL Replication

[pbray3]

I am looking to set up SQL Replication over a WAN (rel. high latency) for an SQL 2000 system.

Does anybody have experience in setting this up, requirements, bug-bears etc... .Also, I am reading about the "distributor" bit, a dedicated svr recommended - does this need SQL installed also, if so what licensing model is best?

 

[mrdenny]

If the distributer will be it's own SQL Server then yes it will need SQL installed on it. Unless you are planning on clustering this SQL Server Standard edition will work fine. You'll need mabey 25-30 cals to cover everything.

When setting up the replication and running the agents you'll need to increase the timeouts and decrease the amount of data being sent is a single pass.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Catadmin]

You also have to be very very very sure to set Rep up in the right order. Double-check this to be sure before you do it, but I believe the order is Distributor, Publisher then Subscriber. I screwed up the order the first time I set it up and ended up having to go into the System Tables to delete the replication pointers (Microsoft has a KB Article on this) before I could re-install it and get it working correctly.

Also, SQLServerAgent and SQLServerService MUST be running on the same domain user account (not the local box account) for all machines involved for this to work. Or, at least, that's how I had to set it up to get it working.

Lastly, go over the different types of replication carefully and decide which one you want to use before you actually install Rep. It will save you a lot of time and stress if you know beforehand rather than having to change things around after everything's been set up. Remember that Transactional Rep requires a persistant connection. Connection goes down, replication goes *Crash*. Snapshot, on the other hand, only needs an occasional connection.

And, Replication will affect how you add columns, constraints and how you drop tables when these actions involve replicated tables. You will find yourself running into a lot of "You can't do that. This table is currently being replicated." error messages for certain actions in SQL.

Hope that helps.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???