locked
HA (High Availability) with mirroring and replication RRS feed

  • Question

  •  

    Here is the situation we are trying to resolve. The client has 2 locations, each has local appliations running on a database. db schema on both locations are the same, data is different and won't overlap.

     

    The requirements are:

    1. at each location the application can read and write

    2. near zero down time for applications on each site

    3. db on one site also has the data from the other site for DR purpose

     

    The client is running on SQL 2005 STD SP2

     

    We looked at the approach of setting up db mirroring on each location + 2-way transactional replication between both locations. The mirroring was fine, and I was able to set up transactional replication from mirrored publisher to a non-mirrored subscriber. But, from what I experienced and from reading, there is no way to have the subscriber db to be mirrored, since Distribution Agents simply doesn't have the option to specify Failover Partnr for mirroring, so I guess it is not supported. Any comments on that?

     

    Assuming that's correct, then the only way of using SQL out-of-box technique seems to be using Clustering on each location instead of mirroring, then the 2-way transactional replication works on clustered subscribers I think (although I haven't tested it). Peer-2-Peer replication would have been a good candidate between sites in this case,  but STD version of sql 2005 ruled that out.

     

    Any suggestions and comments are welcome.

     

    Thanks

    Friday, January 11, 2008 8:26 PM

Answers

  • I guess I'm not understanding what problem you're trying to solve.  Are you trying to create an HA solution for DR or are you trying to create a reporting instance, i.e. Operational Data Store that is isolated from both production instances?  There are a number of ways to create an ODS but it probably should have been addressed during the data modeling phase.  (At least that's what I try to do when I have the luxury.)

    The reporting instance is directly dependent on the kind of data latency you can accept.  If you mirror DB1 on servers A and B and you mirror DB2 on servers C and D you could log ship both instances to server E.  You could log ship both instances to servers E, F, and G if you want.  You don't have to leave your log shipped databases continually in recovery.  You can allow the logs to accumulate and apply them three times a day or whatever.  (That's the latency factor I mentioned.)  The closer to real-time you need your reporting instance to be determines the cost and difficulty of the effort. 

    If you have to have real-time reporting you're going to have to report off of the mirrored instances and accept the inherent performance hit.  That is the only true real-time reporting solution.  Any other option, including replication, will involve some sort of data latency.  Alternatively, you can try and figure out which reports really do require real-time data and which don't.  It's been my experience that users always swear that they have to have real-time reports until you tell them how much that will cost.  Suddenly, it's okay if those sales reports lag a few hours or even days behind.

    I don't really think the fact that DB1 and DB2 have the same schema has any relevance with regard to an HA solution.
    Monday, January 14, 2008 9:45 PM

All replies

  • What were the issues you found with mirroring? Did you find a problem with the following setup?

     

    1. DB A (principal) from Locaion1 is mirrored to DB A (Mirror) on Location 2

    2. DB B (principal) from Location2 is mirrored to DB B (mirror) on Location 1

    Saturday, January 12, 2008 5:34 AM
  • We analyzed your proposed way, and we didn't purse it mainly because we are using 2005 STD, so only synchronous mode of mirror is available. Since 2 locations are geographically apart, there will be a big performance impact for applications (for each transaction has be to committed on both locations)

    Let me provide a little more details on our setup. It was initially one big database, then for load balance and some other reasons, we partitioned into 2 databases with the exact same schema, one on each location. Data on each location don't overlap, and the same application runs on each location (on different data)

    At this time, there is no redundancy at either location which is not acceptable by requirement, so we set up db mirror and it works in our lab. But, there is another requirement to have each location contains data for the other location in the same database, so I try to set up 2-way transactional replication. It goes like this:

    Location 1, principal on server A and mirror on server B (with witness C)
    Location 2, principal on server D and mirror on server E (with witness F)
    transactional replication from A to D
    transactional replication from D to A

    The problem is that transactional replication from A to D works but fails when D failover to E, although A failover to B is still able to replication to D. This is consistent with my understand of db mirror in which you specify FailoverPartner for snapshot and logreader agent for A failover to B still maintains replciation to D, but the distribution agent doesn't have the option for FailoverPartner, so when the subscriber fails over, the replication just broke.

    Saturday, January 12, 2008 7:39 AM
  • i see...

    Saturday, January 12, 2008 7:51 AM
  • If it were me, I would go with the db mirroring and then use log shipping as a warm stand-by.  Great white paper on the topic at this link.  I don't think that replication is the right technology here since what you're really seeking is High Availability and DR.  If you were trying to keep the two databases in sync then I might use replication.  There's way too much overhead for HA or DR.

    Database Mirroring and Log shipping Working Together


    Monday, January 14, 2008 4:50 PM
  • Thanks for the reply. With Mirror + LS, the only problem is that the destination db is not always accessible (at least during log restore). The business side requries it be accessible so reports can be run at both locations.

     

    So basically, DB_1 will be mirrored between servers A and B, while maintain a readable copy on server D, so reports can run on Server D with the full data (DB_1 + DB_2, i mentioned before they have the same schema but different data). Same thing for DB_2 mirrored on D and E, and maintain a copy on server A so reports can run on server A for data DB_1+DB_2

     

    Does that make sense?

    Monday, January 14, 2008 9:09 PM
  • I guess I'm not understanding what problem you're trying to solve.  Are you trying to create an HA solution for DR or are you trying to create a reporting instance, i.e. Operational Data Store that is isolated from both production instances?  There are a number of ways to create an ODS but it probably should have been addressed during the data modeling phase.  (At least that's what I try to do when I have the luxury.)

    The reporting instance is directly dependent on the kind of data latency you can accept.  If you mirror DB1 on servers A and B and you mirror DB2 on servers C and D you could log ship both instances to server E.  You could log ship both instances to servers E, F, and G if you want.  You don't have to leave your log shipped databases continually in recovery.  You can allow the logs to accumulate and apply them three times a day or whatever.  (That's the latency factor I mentioned.)  The closer to real-time you need your reporting instance to be determines the cost and difficulty of the effort. 

    If you have to have real-time reporting you're going to have to report off of the mirrored instances and accept the inherent performance hit.  That is the only true real-time reporting solution.  Any other option, including replication, will involve some sort of data latency.  Alternatively, you can try and figure out which reports really do require real-time data and which don't.  It's been my experience that users always swear that they have to have real-time reports until you tell them how much that will cost.  Suddenly, it's okay if those sales reports lag a few hours or even days behind.

    I don't really think the fact that DB1 and DB2 have the same schema has any relevance with regard to an HA solution.
    Monday, January 14, 2008 9:45 PM