Real time mirroring between two sql server databases

Question

I'm a c# programmer, not a DBA and I've had the (mis)fortune to be handed a database admin task. So please bear this in mind when answering this question.

What I've been asked to do is to create a real time two-way mirror between two databases with a 10 Megabit connection between them. So when either changes it updates the other. This is not a standard data mirroring/failover task where one DB is the master and the other is a backup - both are live and each needs to instantly reflect changes made to the other.

In my head this sounds like a tall order, one which may even be impossible - after all in a rapidly changing environment with lots of users this is going to be massively resource intensive and create locks and queues of jobs all over the place.

Is it possible? If so, can anyone either give me some basic instructions and/or point me at some places to start my reading and research?

Cheers, Matt

Answer 1

You'd be looking at some form of replication - either merge or transactional. There are plenty of guides to choosing which type would best fit your environment.

Of course, what the business tends to demand tends to be impossible if taken literally - e.g. they always want 100% consistent at all times, no latency, no penalties for any types of queries. You will have to manage some expectations. It's never going to be free.

Replication does also tend to require schema changes (such as adding rowguidcol columns to transactionally published tables). If you do go down the replication route, I'd recommend you try out setting it up on some small practice databases first, to get a feel for how it works, and problems you may encounter along the way.

Answer 2

It sounds like you're after Merge Replication. It's not simple, requires your applications be aware of how this works.

Merge replication generally leads to headaches and lots of late nights, missed deadlines, and downtime if you don't have experienced DBA folks on hand to ensure it's set up correctly. And even then - I've had clients report errors that "cannot happen" because of slightly misconfigured environments.

I'd strongly suggest looking at what the base requirements are, instead of blindly following down the merge replication path.

In my experience I've found that I've usually been able to get away with either log shipping and/or SQL Mirroring in most cases.

Answer 3

What you need to test in order to achieve this goal is indeed replication but not any form: Transactional Peer - to - Peer replication (peer to peer was introduced from sql 2005 versions). I am not saying this is without trouble and it will be easy... You need somebody who knows DBA, otherwise you will be responsible for something you don't know very good :)

Merge replication can merge modifications done at each participating server in the replication but not in real time. Each server will be autonomous until conflicts are solved. You have schedules when the merge occurs. It is not real time.

Transactional Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

consider to read below documentation before and good luck. It can be fun :)

http://www.sql-server-performance.com/articles/dba/PeertoPeer_Replication_in_SQL_Server_2008_p2.aspx

http://msdn.microsoft.com/en-us/library/ms151196.aspx