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 |
|
MasterCephus
Starting Member
2 Posts |
 Posted - 2010-08-03 : 10:47:39
|
| Very new to replication, so I need some major help on this...here is my set up:3 SQL servers1. Main SQL Server where all data comes in2. Main replication server 3. Secondary SQL Server for analysisSo #1 gets published and #2 is a subscriber to #1. Then #2 is published, and #3 is a subscriber to #2. It's weird, but in my mind it makes sense for what I need it for...my question/problem I run into is the following:anytime we need to delete a table, or modify an existing table. I have to break replication throughout the whole system (from #3 all the way to #1) then do the modifications, then recreate the whole replication system. This is time consuming, and it seems rather silly to have to do that...Now I am very new to all this, so I might be missing something. Please any help would be very appreciated! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-04 : 14:59:01
|
| DDL changes will be replicated, so no need to break replication there.Look at sp_addArticle and sp_dropArticle in BOLWorst case ever should be having to run the snapshot agent.I'm very curious as to why you've set up #2 to be both a subscriber and publisher...? why not just let #2 and #3 be subscribers? Are you modifying the data on #2? Remember that the subscriber in a transactional replication system should be treated as read only. Doing otherwise risks identity errors, consistency errors, PK/FK violations, and breaking replication. |
 |
|
|
MasterCephus
Starting Member
2 Posts |
Posted - 2010-08-05 : 08:47:50
|
| It's probably more from my lack of knowledge. I made #2 a subscriber and publisher with the idea that I would have more subscribers so I didn't want to put that much overhead (again potential lack of knowledge) on #1 as it's running pretty hard with incoming transactions of data. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-05 : 11:59:58
|
| Additional subscriber(s) won't overload the publisher.May want to setup a dedicated machine for the distributor though. |
|
|
|
|
|
|
drop tables/schema changes with Replication