Maintaining Parallel environments

• Gordon Pollokoff

  SSCommitted

  Points: 1930

  More actions

  February 9, 2011 at 1:27 pm

  #104041

  I've been ask to research methods for keeping two environments in synch. Although the immediate answer would seem to be some form of mirroring or replication, there are a couple of caveats. The goal is to create a pre-production environment that could be used for testing final deployments, data migrations and final stability of release. And once the release has been deemed production ready we would need to only "throw a switch" and the pre-production environment would now be production and the production would be pre-production, thereby minimizing down time even for major releases.

  I'm personally opposed to this approach, at least at the SQL Server level, but folks higher up the ladder are convinced that this should be "easy" to implement. Replication is a problem because the destination enviroment needs to be able to be modify in both schema and data, without those changes breaking the synchronization of production to non-production. Additionally, we have some legacy 3rd party systems that due to the lack of even primary keys on some tables (not my design!) and no rowguids at all, replication can't even be set up.

  So, we've got databases on SQL Server 2000, 2005 and 2008 that are a mix of commercial products and in-house developed applications. We want to create a pre-production environment into which we can deploy new releases, included possible data migrations as we roll systems out to new sites, but keep the data in synch with current production. And when ready, we can just switch the two enviroments.

  I am accepting all suggestions.

  Thanks,

  Gordon

  Gordon Pollokoff

  "Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

• GSquared

  SSC Guru

  Points: 260824

  More actions

  February 9, 2011 at 2:02 pm

  #1284826

  It's not easy, and not practical.

  Here's the simplest reason why: If you add a new column to PreProd (what most would call Dev), how does the data in that stay synched up with Production, which doesn't have the column? If you need to change a constraint in Dev to remove an obsolete product/service (for example), how do you synch that with Production, when it no longer allows data that Production does allow?

  Half the idea of a Dev environment is that you can mess with the data without breaking anything that matters to anyone.

  Also, keep in mind, if you keep any regulated data at all in Production, having it in another environment, where developers, et al, will have access to data they really shouldn't have access to, is a HUGE legal liability. If, for example, any of the production databases contain credit card numbers, or Social Security numbers, then you have to scrub those before moving them into a development/testing environment.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Gordon Pollokoff

  SSCommitted

  Points: 1930

  More actions

  February 11, 2011 at 7:45 am

  #1285718

  GSquared,

  I happen to agree that this isn't practical. However, I am still tasked with seeing if it can be done.

  Just to clarify the need. This is to create a situation where a new release can be pushed into the PREPROD environment, and once the deployment has been verified, this environment would become PROD (and prod would revert to PREPROD). So the data synchronization is one way, PROD to PREPROD. I don't think it can really be done, but my boss thinks it can be done. Additionally, he is being told that it can be done with our Oracle databases, which means that I now have to be able to do it for our SQL Server databases.

  Gordon Pollokoff

  "Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones