Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thebends
Starting Member
11 Posts |
 Posted - 2008-04-06 : 15:11:28
|
| I have a single table on database A that needs to be replicated to two databases B and C in as-near-to-real-time as possible. The table is cleared every morning, and receives about 40,000 records per hour. Records are only added to the table, never updated or deleted.I'm wondering what is the best approach to replicate this table :1 - Transactional Replication2 - A trigger on the INSERT on table in A that then inserts the record into B and C3 - Timed jobs running on B and C that pull new records from Athanks all in advance. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-06 : 15:29:00
|
| Is your destination tables need to be cleared as well?1)I would do with transactional replication if table has primary key.but your log will fill up as you delete all records every morning.2)If destination tables are not cleared ,thenI would use Import/Wizard with following query:Select * from yourtable where datediff(hour,datecolumn,getdate())<=1and run every 30min -hour for performance reasons |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-06 : 16:29:50
|
| Truncate table doesn't generate much log. |
|
|
|
thebends
Starting Member
11 Posts |
Posted - 2008-04-06 : 17:21:09
|
| Thanks,The clearing only happens at the end of the day manually on all 3 tables.The table does have a primary key.every 30 minutes update is not acceptable. As I said I need near real-time performance, a maximum lag of 1-2 minutes between a record inserted into A and propagated to B and C. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-06 : 17:37:20
|
| Then go for transactional replication |
|
|
|
|
|
|
Replicating only one table, what's best approach