June 11, 2003 at 8:54 am
I am trying to setup a replication method between 5 servers, called csprod, csqa, cspreprod, csmast, and csdev.
The issue that I have is that most of the tables that need to be replicated do not have primary keys defined, and the database developer's will not define primary keys, so standard replication through publishing and subscribing is out of the question.
What I'd like to do is put a system of triggers in place, where when a row is inserted or updated on csprod, it will update the other servers. Any suggestions on how to do this, or any better methods?
Thanks,
Scott
June 11, 2003 at 11:52 am
We are doing something similar, but not because of the primary keys though...Anyway, what we did was create triggers on the base tables for insert/update/delete that will insert a record into a "queue" table. You must have some sort of identifiers on the base tables that you can use to determine which rows were changed (unique or almost unique). We are inserting the "Key" field, a table ID (what table was updated), and a timestamp into the "queue" table. Also the queue table has an identity column. We then have another table that lists all servers that should receive the changes. Each server record has a entry in the server table keeps track of the last ID (Identity value) that it copied across. We then have a procedure that runs in a loop at the destination server/DB that checks the control table for changes every X seconds and delete/inserts those rows into the local tables. We are using linked servers in the copy-proc. You could use bcp-out, bcp-in, insert, but that's extra work; linked servers are much easier.
We also have a master proc that calls the copy procs on each "subscriber". The master proc runs as a SQL Agent job on the "Publisher", and is configured to start when the server starts.
Let me know if you need any addition info...good luck.
-Dan
-Dan
June 11, 2003 at 3:42 pm
PK is a must for transactional replication, have you consider using another type of replication?
June 12, 2003 at 9:46 am
I was unaware that merge replication didn't require pk's. I thought of log shipping, but some of the remote db's are across slow wan links and I don't have the bandwidth available.
I setup insert triggers in my testbed, but I can't setup update triggers since most of the tables don't have a unique enough field to query against reliably.
I'm going to setup merge replication in my testbed and see what happens.
Scott
June 12, 2003 at 10:24 am
Check that merge replication will add a rowID column to all your tables, and if you have a lot of rows, then it will increase the size of your db a lot.
If you have a slow network link, then merge agent can be good to use.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply