Keeping all three environment(Dev,Stag,Prod) in sync

Question

Keeping all three environment(Dev,Stag,Prod) in sync

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

ScottPletcher (12/28/2012)
I would never try to re-run the prod processes to get the data for other environments. That would be a nightmare to keep clean.

That's how we do it. Sure, we get discrepancies from time to time, and on such occasions we go back to good old backup and restore. The problem with regular backup and restore or SAN replication is that it also wipes out any new code you are developing or testing. And the advantage of using the same load process in your dev and/or test environments is that you can also test the effect of the new code on the load process itself.

John

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

John Mitchell-245523 (12/31/2012)
ScottPletcher (12/28/2012)
I would never try to re-run the prod processes to get the data for other environments. That would be a nightmare to keep clean.
That's how we do it. Sure, we get discrepancies from time to time, and on such occasions we go back to good old backup and restore. The problem with regular backup and restore or SAN replication is that it also wipes out any new code you are developing or testing. And the advantage of using the same load process in your dev and/or test environments is that you can also test the effect of the new code on the load process itself.
John

I use it to my advantage... it reminds people to save their work in SVN. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

ngreene SSC-Addicted Points: 461 More actions · Answer 3

replication solutions wont work because developers will be making changes to the dev environment, so once they change something then the databases are no longer in sync. Nightly backup and restore are the only ways to ensure the data is the same between Dev, Stag, and Prod.

The only other option may be data compare by red-gate, but that would likely be a slow option. May be worth testing with a free trial.

I would recommend trying to speed up the backup & restore process:

- Backup to solid state drives / faster disks.

- increase bandwidth between servers. 10GB cross connect

- compare red-gate compression to SQL Server compression. Compare compression ratio and speed.

ScottPletcher SSC Guru Points: 100262 More actions · Answer 4

John Mitchell-245523 (12/31/2012)
ScottPletcher (12/28/2012)
I would never try to re-run the prod processes to get the data for other environments. That would be a nightmare to keep clean.
That's how we do it. Sure, we get discrepancies from time to time, and on such occasions we go back to good old backup and restore. The problem with regular backup and restore or SAN replication is that it also wipes out any new code you are developing or testing. And the advantage of using the same load process in your dev and/or test environments is that you can also test the effect of the new code on the load process itself.
John

My big concern is that the QA or staging env would accidentally load data to production. But there's also:

the amount of time, resources and locking that can occur while loading data;

the extra processing load put on the non-prod systems;

potentially poor timing of the load/update jobs running -- QA could be in the middle of a critical demo or test.

Source issues could certainly be valid. In our case, I have separate source-only backups that I can run on non-prod environments. We also have source mgmt software outside of the db itself that controls source and source versioning. You may have to take additional steps to secure your source separately prior to the restore.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

ScottPletcher SSC Guru Points: 100262 More actions · Answer 5

Jeff Moden (12/29/2012)
Wouldn't truncating a table in QA violate the idea of keeping all 3 environments in sync? Presumably, keeping all 3 enviroments in sync really means keeping Dev and Staging in sync with production and not the other way around.

From prod to dev or staging, I would expect the sync to be a point-in-time, not maintained across time. I don't know of any dev or staging envs that never get modified, or that mods are restricted so that someone can't temporarily empty a table if they need to.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

wannalearn SSChasing Mays Points: 649 More actions · Answer 6

We decided to go with Full and Differential BackUp and Restore.

Thank you all for your time and feedback.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 7

Personally I would run the overnight loads independantly on each environment with Prod being the priority environment.

this way your data is then pretty much in sync, with the exception of any synthetic/surrogate keys that are generated.

You also get to test any changes to the load process and compare the results with a standard set of reports that can be verified on each system (eg Daily Sales Figures etc) with the Production database thus identifying any potential issues with changes before they get into UAT or Prod.

_________________________________________________________________________
SSC Guide to Posting and Best Practices