Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are looking into setting up a master-master replicated MSSQL database and I am interested to hear of any potential pitfalls that we may run into along the way and generally any advice that you might have for us.

To expand on our situation a bit we currently have an internal SQL 2008 R2 server and a web based MySQL server. We use SSIS a lot to transfer the data between the two servers. This ends up with the data on the two servers being out of sync as the SSIS packages run hourly or nightly. We have investigated MSSQL->MySQL replication but we've never found a comprehensive solution.

This has brought us to what we are hoping to set up with the web using both the current MySQL and an additional replicated MSSQL database.

Specific areas that I'm interested in finding out more on are:

  • How does a master-master set up deal with an internet connection being dropped? I've read that one node becomes read-only but I can't seem to find much detail about this.
  • Is there anything to be wary of connecting from PHP to an MSSQL server?
  • What type of replication would you recommend? Bi-directional transactional seems to fit the bill quite well.

Thanks for your time

share|improve this question

migrated from stackoverflow.com May 10 '13 at 16:03

This question came from our site for professional and enthusiast programmers.

    
I would recommend not mixing technologies in this scenario. Getting Peer-to-Peer replication to work in SQL Server alone is challenging enough. You're asking about how to build a nightmare with these requirements. –  Jon Seigel May 10 '13 at 17:08
1  
Let's take a step back for a second. Why do you need to do this? And do you have control over the application code? (I'm assuming this is not a vendor database solution?) –  Jon Seigel May 10 '13 at 17:16
    
@JonSeigel when you talk about mixing technologies, I presume you are talking about MySQL/MSSQL, the plan here is to migrate from MySQL to MSSQL but that is going to have to be a gradual process. –  Rob Forrest May 11 '13 at 11:08
    
The rationale behind doing this is to provide replicated data between our in-house DB and our website. Our current situation involves far too many rather flaky SSIS packages. –  Rob Forrest May 11 '13 at 11:12
    
Yes, that's what I mean. Master-master replication is probably the hardest thing to get right. Okay, are you synchronizing all the data in the database, or only some of it? What are the reasons why the solution doesn't use only a single centralized database? –  Jon Seigel May 11 '13 at 12:23

1 Answer 1

up vote 1 down vote accepted

(I'm putting this in an answer, as it's way too long for a comment.)

We have a scenario similar to yours for our bug tracking system. We use it internally, of course, but customers can also submit issues through a page we created on our customer SharePoint site.

What we decided to do was host the database and website only at the office and provide external access from there (which we were already doing for some of our SaaS customers). If the internet totally bombs out (rare), it's more important that we can continue to work than for our customers to be able to submit new issues.

In your scenario, I don't know how critical the data is, how much data there is, or how important it is for external users to be able to write data.

Perhaps you could consider using a database at the alternate location as a read-only secondary, but direct all writes to the primary. While this will probably involve some application changes to separate read-only and read-write connections, this type of solution might be enough to satisfy the requirements for the small amount of time the office internet is down.

Regardless, I stick by my recommendation to not mix MySQL/SQL Server if you can avoid it. IMO, you'll be better off long-term by directing resources into proceeding with the existing migration plan, and hold off on developing a more robust replication solution until that stage of the project is complete.

Also, definitely try your best to avoid any master-master replication scenario. These can be highly non-trivial to configure and support at the best of times. The $ and time that will be spent developing and debugging a solution involving master-master heterogeneous replication will be astronomical, and probably won't ever work correctly 100% of the time (actually, probably nowhere close to that). Not that a built-in homogeneous replication solution will be perfect either, but at least in that case, you can call for customer support if something blows up and you don't know how to fix it; if you roll your own solution, you're on your own.

share|improve this answer
    
Thanks for your input on this, we had no idea that master-master would be such a problem! I think we'll probably try and investigate a master-slave relationship instead like you suggested. –  Rob Forrest May 12 '13 at 16:52
    
@Rob: You're welcome. Let me know if I can be of further assistance. Different vendors have different solutions for master-master replication, all with their own benefits and drawbacks, but in the general case, it's not an easy problem to solve. Well, at least if you care about maintaining ACID (which I assume you do). For this reason, plus my own experience, is why I suggest actively avoiding it unless it's absolutely necessary. It's also highly desirable that the system was built to support such a scenario during the design phase. –  Jon Seigel May 12 '13 at 17:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.