Methods of Syncing Multiple Enviroments Databases

• Alasdair Thomson

  Hall of Fame

  Points: 3630

  More actions

  June 7, 2016 at 3:56 am

  #319358

  So we work in a multi-server/database environment, having a Local, Development, Pre-Production & Production setup.

  Local is the developers local SQL environment, Development is a server based UAT environment, Pre-Production is meant to be a change test & verify environment that is as close a copy to production as possible and production being... well production.

  Our databases contain multiple models, each with varying degrees of database integrity, from highly structured dimensional models to simple transactional models with no FK usage. Our databases are medium in size (approx. 6TB in total) with 98.5% of this data related to a single model that rarely needs to be tweaked or changed outside of normal ETL processes.

  So here is what I'd like to achieve, We'd like to be able to sync the developers local databases & the development environment being synced automatically on a given schedule. The local and development environments don't have as much storage as pre-production/production and so a normal backup and restore isn't an option. Existing 3rd party tools don't seem an option as the tools we have tried have always had issues fully managing/co-ordinating the changes of the highly structured models so I'm looking to see if there are other ideas and methods we could look at.

  To be clear, the main reason for this is that it takes time to keep local and development environments upto date and developers (rightly so) need to be able to openly play and test changes and structure before it goes formally into the change process for deployment into pre-Prod and Prod environments.

  Initially I thought of using the backup and restore of filegroups. Have the objects that contain large amounts of data on a secondary filegroup, so that we can perform a backup and restore of the Primary filegroup objects onto whichever environments need to be synced, this would have meant it could be automated or run on demand with little work, but in testing and researching this approach it won't work.

  So have I missed anything? Does anyone have a similar need that they have been able to work out a solution for. FYI Were on SQL 2012 (Enterprise version). I have also considered database replication but from my own experience as well as that teeth sucking sound the DBA's make when you even think of replication I have discounted this idea.