SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19509 Visits: 7410
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.

One possibility is to mix differential backups and restores with the full backups and restores. This makes sure you get all the data but allows for overall faster processing.

Periodically -- weekly, biweekly, whatever -- you do a full backup. Naturally this can be done when the most time is available. This provides a "base" to apply differentials to. Then do daily -- or whatever -- differential backups that you can apply to the base to bring the dev and qa dbs up to date.

First, restore the "base" backup to a different, temporary "restore db name", using WITH NORECOVERY. Then apply the differential (if any). RESTORE the restore db name WITH RECOVERY. Run any required scripts (adjust permissions, resync users, etc.). For example, for "DB1", restore to "DB1_Restoring".

Once the restored db is verified fully ready to go, drop the existing db and rename the new one to the main name; for example, DROP DB1, RENAME DB1_Restoring to DB1.

Finally, immediately "pre-restore" the "base" backup to the "restore db name" (WITH NORECOVERY). That reduces the time needed for the next refresh, because the bulk of the data has already been restored.

Note that this also allows you to quickly make the dev or qa db look like prod using the standard differential backup method if you need to immediately investigate a prod problem.

It also leaves the original dev and qa dbs available until the new db restore is verified as working.

The big disadvantage with this method is that it requires more disk space ... I fall back on "disk is (relatively) cheap" there :-).

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
SatishAyyar
SatishAyyar
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 530
As Scott suggested , differential backup and a periodic full backup is a lot better solution to your problem. But then I was just giving you out different solutions out of which you may choose which better suits.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213617 Visits: 41977
wannalearn (12/27/2012)
Thank you all for your responses.

We can not use Log Shipping because Our databases do not store transactions. We load data everynight from different sources . So it is not our responsibility right now.

We use replication but only for some database. My senior DBA has not kept all databases in sync for some reason. it could be hardware issues.

This is exactly what we want as mentioned by @SQLFRNDZ - "his requirement is not DR and he just wants to copy a database to other lower environments daily and want to automate the process."

Is there any efficient way other than back up and restore ?

Thanks!


Yep... like I said, use the SAN replication if available. DR isn't it's only use. It's very handy and lightning fast at keeping databases in sync.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19509 Visits: 7410
Jeff Moden (12/28/2012)
wannalearn (12/27/2012)
Thank you all for your responses.

We can not use Log Shipping because Our databases do not store transactions. We load data everynight from different sources . So it is not our responsibility right now.

We use replication but only for some database. My senior DBA has not kept all databases in sync for some reason. it could be hardware issues.

This is exactly what we want as mentioned by @SQLFRNDZ - "his requirement is not DR and he just wants to copy a database to other lower environments daily and want to automate the process."

Is there any efficient way other than back up and restore ?

Thanks!


Yep... like I said, use the SAN replication if available. DR isn't it's only use. It's very handy and lightning fast at keeping databases in sync.



I'm not sure if SAN replication would even be applicable in your situation, since presumably you will be modifying the rows in the qa and staging areas yourself.

For example, I don't know that replication will handle a situation where you TRUNCATE the table in qa, say, but of course not in production. What happens when SAN replication then attempts to replicate the data? I don't think it will work smoothly, but I certainly can't say for sure, since I definitely admit I'm NOT a replication expert.

I do know that replication is potentially complex and you will require someone with expertise in it if you intend to use it in this situation.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213617 Visits: 41977
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.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34403 Visits: 16649
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
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213617 Visits: 41977
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. :-D

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ngreene
ngreene
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 347
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
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19509 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19509 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search