| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2007-05-17 : 05:18:46
|
| Hi pros,We now have a business requirement to use SQL Replication to replicate our Live Database Server to a Live Reporting Server. So updates will be in one direction.I wonder if someone with some experience in this area can give me a rough idea of what I'm getting myself into.How good is SQL Replication?How much time am I going to spend reading up before I'll know enough to do this?What kind of a footprint does it leave on the server?How much more complex does it make maintenance and DB upgrades?What's the lag time? If a user updates a record on the Database Server and then emmediatly runs a report on the Reporting Server, will they see that change reflected?PS: Anyone know what Robvolk is doing these days? I miss that guy. He's one of the few good ones left. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-17 : 09:48:57
|
| You can use transactional replication, read books online and you'll get most of asked. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-17 : 12:50:14
|
| 1. I don't know how to quantify "good".2. It's easy to setup, not easy to troubleshoot when it's broken.3. You'll get a bunch of jobs, distribution database, and your transaction log backups on the publisher will be bigger.4. It depends. What is meant by maintenance and DB upgrades?5. It depends on how you set it up.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-18 : 04:20:36
|
quote:
Originally posted by tkizer
1. I don't know how to quantify "good".2. It's easy to setup, not easy to troubleshoot when it's broken.3. You'll get a bunch of jobs, distribution database, and your transaction log backups on the publisher will be bigger.4. It depends. What is meant by maintenance and DB upgrades?5. It depends on how you set it up.Tara Kizerhttp://weblogs.sqlteam.com/tarad/
3.) I run our databases in simple mode and do a nightly backup.4.) Running Service Packs / Hotfixes; Adding columns to tables. What happens if you have a trigger that updates another table? Do you have to switch that trigger of on the replicated server because the replication is pushing that cahnge accross already? Or don't you have to worry about it? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-18 : 11:35:24
|
| 3. Your LDF file may be a little bigger then as that's where the data is stored until it gets pushed to the subscriber.4. Service packs and hotfixes don't impact replication. Schema changes can though. You can't have a subscriber when you alter the table that is an article. So you have to drop the subscriber, make the change, add the subscriber back, then either do a snapshot or get the data across manually.I don't understand what you mean. When talking about the servers, say publisher and subscriber so that we know which one you are referring to. Could you provide an example of your trigger scenario?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-22 : 06:38:13
|
| OK,Publisher has an audit trigger on tableA that inserts records into tableB.The Publisher then processes an insert into tableA.If the Subscriber has the same DB structure (with a trigger on tableA), what puts a record in tableB on the subscriber? The trigger on tableA on the subscriber OR replication? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-22 : 11:43:54
|
| I'm not sure. I would think that you wouldn't have tableB be an article, so that the normal trigger would take care of everything. But please do test.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-22 : 12:18:32
|
| I agree with Tara. "lighter" on the Logs, and replication bandwidth, NOT to replicate tableB.How up to date does your Reporting Server have to be? Is a daily [or maybe a bit more often than that] "freshen" enough? If creating a script to copy "changed data" might be enough/easier.Kristen |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-22 : 18:10:44
|
quote:
Originally posted by tkizer
I'm not sure. I would think that you wouldn't have tableB be an article, so that the normal trigger would take care of everything. But please do test.Tara Kizerhttp://weblogs.sqlteam.com/tarad/
5 second delay max at a push - aim would have to be no more than one second |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-22 : 18:26:49
|
I havent used triggers ever, but I think there are different settings to enable/disable triggers for replication...And 1 sec delay for subscriber to have latest transactions on publisher is prbably not gonna happen  There are lot of factors - load on the publisher, bandwidth from publisher to subscriber, hardware related etc. Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-22 : 22:45:09
|
| Take look at 'Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION' in books online. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-23 : 06:39:07
|
quote:
Originally posted by dinakar
I havent used triggers ever, but I think there are different settings to enable/disable triggers for replication...And 1 sec delay for subscriber to have latest transactions on publisher is prbably not gonna happen There are lot of factors - load on the publisher, bandwidth from publisher to subscriber, hardware related etc. Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
Thanks, that's the kind of thing I need to know now so that I can set the right kind of expectations. |
|
|
|
|
Replication Newbie