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