August 21, 2008 at 1:46 pm
Existing SQL Server schema (scheme?): Production Server = A. Mirrored to Server B. Snapshot replication 2x / day from A to a 'Reports Server' = C. Now we need to replicate using VPN from Server C to offsite Server D.
When I attempted to replicate from C to D, I broke the snapshot from A to B, because it was unable to delete a view. We have tables, views, stored procedures, and one user-defined function to replicate. In addition, there are more tables and views on the Reports server (C) than on the Production Server (A).
One possible work-around, quite klugey from my view, is to port the full backup files and transaction logs to the offsite server D, then 'simple' replication from C to D of the unique content on C.
I would prefer to NOT change the existing production configuration at all, other than to enhance the notification processes when replication fails, which is a lower level of severity than when the entire server fails (network level monitoring tools identify that event, if it ever happens).
Questions:
1. Does my 'kluge' sound like it will work?
2. Is there a better solution, preferably one with elegant simplicity in it?
(elegant simplicity = upon viewing, is intuitively obvious that it's a good solution, easy to maintain, etc etc etc) - i've arrived at such a solution maybe twice in my life.
3. Does anyone have any coherent references (my forays into BOL feel like traversing a maze, perhaps because I've never caught on to a good way to navigate BOL) or models they can point me to for setting up what I need?
4. Once I land the DB at the far end of the VPN (offsite), I then have to connect at least two other systems to the same DB OR replicate to other instances at the DR location. Production is an 11 server configuration (web, application, etc), DR is a smaller config.
TIA for any and all suggestions!
August 21, 2008 at 3:42 pm
Sorry for the bad news but from what I have seen in republishing architectures the solution is "peer-to-peer". Merge was advertised as the tool of choice once but DDL replication in Merge is "shaky" as best!
Just my $0.02
* Noel
August 22, 2008 at 11:23 am
noeld (8/21/2008)
Sorry for the bad news but from what I have seen in republishing architectures the solution is "peer-to-peer". Merge was advertised as the tool of choice once but DDL replication in Merge is "shaky" as best!Just my $0.02
Thanks for the observation. I've also been performing a lit review and see why you suggest p2p. Since my 'reports' server (C in my description above) does not exactly replicate the database structure in my production server (A), I'm not convinced that I can use peer-to-peer. I think I need some form of hybrid, where (D) holds unique content from A + unique content from C. Which implies I can replicate between C and D, BUT FOR the snapshot replication process from A to C. So I need a work-around for the snapshot replication limitations. Hence, using a replication of the unique C attributes + a replica from A. However, since I cannot connect A-D directly, I have to use a 'kluge' to work around the lack of connectivity. Are we having fun yet?
August 22, 2008 at 2:24 pm
steve smith (8/22/2008)
noeld (8/21/2008)
Sorry for the bad news but from what I have seen in republishing architectures the solution is "peer-to-peer". Merge was advertised as the tool of choice once but DDL replication in Merge is "shaky" as best!Just my $0.02
Thanks for the observation. I've also been performing a lit review and see why you suggest p2p. Since my 'reports' server (C in my description above) does not exactly replicate the database structure in my production server (A), I'm not convinced that I can use peer-to-peer. I think I need some form of hybrid, where (D) holds unique content from A + unique content from C. Which implies I can replicate between C and D, BUT FOR the snapshot replication process from A to C. So I need a work-around for the snapshot replication limitations. Hence, using a replication of the unique C attributes + a replica from A. However, since I cannot connect A-D directly, I have to use a 'kluge' to work around the lack of connectivity. Are we having fun yet?
Understood. I was suggesting a "simple" way. The problem with republishers is that to DROP tables or TRUNCATE them from the primary is NOT an option and unless your tables are really small DELETE FROM (without where clause) is prohibitive. And YES, we are having FUN.
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply