August 11, 2009 at 8:49 am
HI
I have a reporting application running from 2 different databases on 2 servers for 2 different sets of report.But both these databases need some common tables which are as of now present in Server1.
What is the best replication mechanism to replicate these tables to server 2?
1.Most of these tables are changed infrequently.
2.Few of these tables are updated by eod job, so cannot afford a lock on these tables
3.Data should always be present in both the servers as its a global application and reports can be generated anytime
Issues that i faced with Snapshot Replication:
1.It locks the table
2.It truncates the table before replicating
1.Is there a way to stop the above 2 activities for snapshot?
2.Is Transactional replication suitable for such scenario?Are there any drawbacks for Transactional Replication?
what do you guys suggest?
Thanks
Koustav
August 11, 2009 at 9:05 am
koustav_1982 (8/11/2009)
HII have a reporting application running from 2 different databases on 2 servers for 2 different sets of report.But both these databases need some common tables which are as of now present in Server1.
What is the best replication mechanism to replicate these tables to server 2?
1.Most of these tables are changed infrequently.
2.Few of these tables are updated by eod job, so cannot afford a lock on these tables
3.Data should always be present in both the servers as its a global application and reports can be generated anytime
Issues that i faced with Snapshot Replication:
1.It locks the table
2.It truncates the table before replicating
1.Is there a way to stop the above 2 activities for snapshot?
2.Is Transactional replication suitable for such scenario?Are there any drawbacks for Transactional Replication?
what do you guys suggest?
Thanks
Koustav
Does both the servers modify data in the 'common' tables? If yes, I suggest you to create peer to peer transactional replication.
If only server1 modifies data in the 'common' tables, you can create a transactional replication.
You need to have primary key defined on the table for transactional or peer to peer replication to work. The only drawback i can think of is If the log reader agent is scheduled to run at a larger interval of time, you wont be able to truncate the log, so better schedule it to run continously.
Though i believe snapshot replication is best suited for this kind of scenario(considering data from server1 needs to be moved to server2 on a routine basis), while applying the snapshot, the distribution agent either deletes the object on the target dbase or truncates/deletes the data in the table (both of which u do not want to happne).
August 11, 2009 at 7:49 pm
Peer to peer replication is sql 2005 feature.
Peer-to-peer replication is available only in SQL Server 2005 Enterprise Edition.
http://technet.microsoft.com/en-us/library/ms151196(SQL.90).aspx
MJ
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply