Sync MS SQL Server DB in Real Time - Both DBs Being Writable

Firstly let me start by stating I'm not after a 'how to' solution, rather an explanation on if my question is possible or not. I'm also not a DBA so am not sure if what I'm asking is quite simply, a stupid question. Anyway - where it goes...

BACKGROUND

I've been asked to look into creating a pilot env for a MS CCF based application. It's very early days.. The aim of the env is to enable a pilot of any changes being promoted into the production env. The catch is that the pilot env will be running alongside the production env.

So our business team wants to run both these env's alongside one another, the pilot env with any new changes being deployed. And the existing prod where the changes haven't yet been deployed.

In regards to middleware - It's been hinted that our programmers can change the CCF package so that it references service versions. This in itself is a topic for another question which I expect our programmers should be able to answer.

MY QUESTION

Our current production env is connecting to its own DB instance (I believe it's MS SQL Server 2005) which in turn has DR capability. If a pilot env is to be set up which will run alongside Production (using live data) how will DB synchronisation take place? In my mind a 2nd DB instance will need to be created (to cater for the new changes) and be in sync with the prod DB in real-time, with both DB's instances (prod and pilot) being writable.

Is this even possible?

To date I've only been able to find articles on Active/Passive configurations. I'm wondering if any of you might have any ideas or suggestions which you might share so I can go away and do a little bit of research.

It should be noted that our DBA is on leave for the next couple of weeks which is why I’m looking for ideas myself first.

I don't care what environment -- you do not want the "test" environment
easily accessible by the end users ever.

They are bound to screw up and put production data in the test environment
-- then complain that it was so much that you have to mess up production by
getting it back from the test.

Trying to automatically replicate production data to a test environment is
not suggested either. That is because you want the QA testers to setup
oddball cases in the test environment as your developers are working on
the software. The suggested way is that you refresh the data after moving
the changes into production.

In short this isn't going to be a 'test' environment. All the relevant testing is completed and signed off before being promoted into Prod.

The aim I guess is to have a 2nd live prod env which will only be made available to a group of pilot users. I'm happy in the knowledge that the application and middleware services can be configured to use the correct versions of service - eg, existing prod service v1, pre-prod (or prod pilot group) v2. My only concern is about the back end and keeping it in sync.

I've been told by our dev team that sometimes the DB is updated during an application change, sometimes it isn't. For this reason I don't think we can point both packages to the single DB - Thus I need to find a solution for a active/active and in sync DB's.

I'm also happy to go back to the business and advise that what they want simply isn't possible.

Some of it depends on how they do DB changes. I know some developers just
add columns and never delete the old ones. If there is data in the original
column that is relevant, they migrate it to the new column.

Replication is possible in that situation -- but I would do a three stage
replication in that case. Replicate the data from production to a second DB
on the same instance. Then from secondary DB replicate to the dog & pony
show server. That way if replication goes wonky to the D&P server it
probably won't hurt production.

Note that you will have to rebuild replication if any columns are added.

Some of it depends on how they do DB changes. I know some developers just
add columns and never delete the old ones. If there is data in the
original
column that is relevant, they migrate it to the new column.

Replication is possible in that situation -- but I would do a three stage
replication in that case. Replicate the data from production to a second
DB
on the same instance. Then from secondary DB replicate to the dog & pony
show server. That way if replication goes wonky to the D&P server it
probably won't hurt production.

Note that you will have to rebuild replication if any columns are added.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.