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
Best Answer
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.