Choosing The Best Transactional Replication Method
Posted on 2004-08-01
I have a GoldMine database in MSSQL. I want to snapshot just the CONTACT1, CONTACT2 and the CONTHISTtables only.
These tables will get very large, the CONTACT1 table having eventually over 1,000,000 records. I will be using another application to access just these three tables for analysis and I do not want to lock up the live database. Because these tables are getting quite large, I was thinking of transactional replication, however the GoldMine tables do not have a Primary Key and I do not know how GoldMine would react if I was to add them.
Therefore I was going to use SNAPSHOT publication, but I am concerned about resource load.
We have a beefy server, 4 GB RAM and a quad processor, the SNAPSHOT and the HOST database would be on the same server.
So, should I be worried about running SNAPSHOT?
Most of our resource problems come from record locking when the users run their filters and queries from GoldMine, the server locks up because all the clients are fighting for the same info, and the CPU's on each side (client and server) are getting maxed bc (I think) they keep slamming the server waiting for the client who locks the tables running these queries (extremem filters) to end.
So, how long would such a SNAPSHOT take, what is a good time interval, and / or am I completely off base and smoking crack?