April 30, 2008 at 8:48 am
Hi All,
Logshipping and Replication setup on DR servers:
We have 3 servers which are on site A, A1, B1 and C1 with replication setup here
A1 is the production server (in this case with 2 databases), A1 is the publisher
B1 is the Distributor and Subscriber
C1 is also another subscriber.
This is the setup already setup on site A and working.
Now we want a DR for site A, We want the same setup as site A on the DR side(site B) with replication as being setup on site A.
so we want to setup another site B on remote location. Where we can synchronize our site A db’s using log shipping
We have created 3 servers on site B as, A2,B2 and C2
Now the issue is we want to setup logshipping from A1 to A2,
And setup replication between A2,B2 and C2.
Hence we need to make A2 as publisher, B2 as (distributor,subscriber) of A2 and C2 as subscriber of A2.
?’s
Can we do logshipping and replication like this?
What is the best way of doing this?
Site1 is working on cluster server with A1 as production, B1(active cluster) and C1(passive cluster), will this affect the process?
When we do logshipping from A1 to A2, A2 will be in restoring mode, Can we still do replication like that?
While doing replication, we need to create distributor and then from distributor we need to subscribe the publication for B2 and C2?
In a scene of DR, how fast we can bring the site B back on production in case of failover at site A and How?
Can we keep the site 2 on standby, and do this process?
Note: All servers are on sql server 2005 with sp2 on both site A and site B.
The whole idea of this process is to keep our site A replica to another location and the replication process running, so that in a case of DR we can restore back all the Db’s to the latest point of time with minimum downtime.
Thanks
May 1, 2008 at 1:44 pm
Hi Nicole,
There are some hidden details, but based on the info you provided, if I were you, I would use mirroring between A1 and A2, and script out the replication details from the servers on site A. Mirroring gives you minimal data loss and an easy way to switch roles if needed.
Unfortunately, neither with mirroring, nor logshipping you can't set up the replication topology as the secondary/mirror databases are not available for use (ok, in logshipping you can open the database in read-only mode, but it won't help you). If you have to provide a fully functional (including replication) site B in minutes then you can use replication to A2. However, if you have to provide only an up-and-running A2, then mirroring sounds better for me and you can setup the replication manually.
(As replication is occasionally a nasty bastard, I recommend you to script out the replication, set up everything on site B, start the mirroring and then break the mirror (no failover!), recover the mirror database and test the replication scripts.)
In short:
Mirroring pros: easy setup and operation, less bandwith (in SQL 2008, even less:)), easy failback.
Replication pros: everything can be up-and-running, no human intervention needed in case of failover.
Hope this helps,
-- Erik http://blog.rollback.hu
May 2, 2008 at 9:47 am
Erik makes very valid points contrasting mirroring, tranlogging, replication
- I agree that L-S is a non-starter because applying the *.TRN would need A2 offline
mirroring is also good in that you could configure for auto-failover
L-S might be every 15 mins (ours are) meaning a typical 30-min latency (bulk dump+copy+restore), whereas transactional replication is continuous and nearly real-time (bursty but not bad as L-S)
perhaps B1 (your Distributor) could have a new PUSH sub to A2 (or A2 PULL from B1)
- this gives advantage that A2 is almost as up-to-date, can be on-line and repl to B2, C2
repl can be tricky to setup but once you've scripted stuff [in case need to recreate], it runs well
HTH
Dick
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply