nabeel92
asked on
SQL replication ways !
Hi there,
I'm just thinking on how to implement a disaster recovery site. Goal is that in an instance of primary site failure, the secondary site becomes active one with all services available. In this context, my Question is regarding SQL replication.
Now, at the primary site I have an SQL principal, mirror and witness server installed so that in case if principla/mirro server goes down, the witness automates the failover to the other server. But if all 3 SQL servers (Principal, Mirror & Slave) in that site go down; or in other words the whole site goes down, then what are the possible ways for me to setup replication before hand so that my database is still available at secondary site ? Is there a way to setup this up !
Am not much familiar with setting up these procedure so would appreciate if someone can explain in a bit detail ! thanks
I'm just thinking on how to implement a disaster recovery site. Goal is that in an instance of primary site failure, the secondary site becomes active one with all services available. In this context, my Question is regarding SQL replication.
Now, at the primary site I have an SQL principal, mirror and witness server installed so that in case if principla/mirro server goes down, the witness automates the failover to the other server. But if all 3 SQL servers (Principal, Mirror & Slave) in that site go down; or in other words the whole site goes down, then what are the possible ways for me to setup replication before hand so that my database is still available at secondary site ? Is there a way to setup this up !
Am not much familiar with setting up these procedure so would appreciate if someone can explain in a bit detail ! thanks
Database mirroring can be used in conjunction with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients. This copy is known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database. for complete info check this link.... http://msdn.microsoft.com/en-us/library/ms151799.aspx
http://74.125.155.132/search?q=cache:LKTT-OGkgrUJ:download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/ReplicationAndDBM.docx+sql+server+2005+mirrored+database+transactional+replication+setup&cd=4&hl=en&ct=clnk&gl=us
Before we can give you the beginning of an answer -- you need to make some decisions: What are your tolerances: for data loss 1 minute, 1 hour, 4 hours, 1 day?
For downtime: 1 day, 1 hour, 1 minute?
What if your off-site is down?
Will you base it on internet connection? Server faults? SQL faults?
Do you want to do SQL replication or drive replcation?
Just throwing thoughts.
For downtime: 1 day, 1 hour, 1 minute?
What if your off-site is down?
Will you base it on internet connection? Server faults? SQL faults?
Do you want to do SQL replication or drive replcation?
Just throwing thoughts.
- if you are using oracle ...plan the communication between the sites...i would implement Oracle data guard... by configuring archive logging and scripting it via rsync inorder to transfer archive log files to DR site....
- if you have tsm tivoli ibm then configuration to automatic copy the backups from main site to DR site via tcp
- or if you have SAN then implement SAN to SAN replication ibm metro mirror
madunix
- if you have tsm tivoli ibm then configuration to automatic copy the backups from main site to DR site via tcp
- or if you have SAN then implement SAN to SAN replication ibm metro mirror
madunix
As far as SQN replication -- another company to look at is Faclonstor. Fantastic company at a median price in the market. And they can work with just about any type of SAN units.
ASKER
Thanks for the link guys. I'm gonna go over these today and have a read. At this stage, I was more interested in knowing if there are possible ways if we don't have SAN and it seems there is a way ?
@madunix, we are using Microsoft SQL Server 2005.
@ jimpen, downtime should be < 1 minute as these are core revenue generating services. SQL replication, not drive replication. Offsite Criteria is just a DR, whether by human error or natural.
@MohammedU: I'm having a read of that now and will post back !
@madunix, we are using Microsoft SQL Server 2005.
@ jimpen, downtime should be < 1 minute as these are core revenue generating services. SQL replication, not drive replication. Offsite Criteria is just a DR, whether by human error or natural.
@MohammedU: I'm having a read of that now and will post back !
If you need downtime < 1 minute then it is better use HW replication.
MohammedU, if you are using the "mirroring" option, does the active server and the mirror need to reside on the same subnet or does it support WAN networks?
ASKER
itprotocall: I had a read of XOSoft Replication/ HA. Are you able to give me a bit of detail on how you have it setup ?
From my understanding, You install agents on your principal DB and replica server and that's it really ? In my case, the principal SQL server will have a mirror and witness SQL server in its own site. Will that cause any issue with the XoSoft R/HA agents ? Secondly, is it a manual switchover or does the 2nd unit automatically become active when the primary goes down ?
From my understanding, You install agents on your principal DB and replica server and that's it really ? In my case, the principal SQL server will have a mirror and witness SQL server in its own site. Will that cause any issue with the XoSoft R/HA agents ? Secondly, is it a manual switchover or does the 2nd unit automatically become active when the primary goes down ?
This is sounding more like the old axiom:
You can have it your way:
Cheap
Fast
Good
Pick any two.
For response on that level you should be looking at clustered SQL and app servers with disk to disk replication. I could work with a firm to design at that level, but I just don't have the experience to really advise you.
I'm just going to have to fold my tent on any further advice. Sorry. ;-)
You can have it your way:
Cheap
Fast
Good
Pick any two.
For response on that level you should be looking at clustered SQL and app servers with disk to disk replication. I could work with a firm to design at that level, but I just don't have the experience to really advise you.
I'm just going to have to fold my tent on any further advice. Sorry. ;-)
Hi! As some have mentioned, yes, Double-Take Availability can assist in your SQL HA solution. If you'd like more info, just drop me a line via my profile/email.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
most of the software provide real-time replication over WAN and automated failover capabilities with many other benefits over SQL native replication.
my personal favorite is CA Xosoft HA - http://www.arcserve.com/us/products/features.aspx?id=8282
others are doubletake, neverfail, etc...