Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jan 5 '11 at 7:32

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

add comment

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
    
Thanks for the warning - will bear it in mind going forward –  Matt Thrower Jan 4 '11 at 16:14
add comment

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.