Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Keeping all three environment(Dev,Stag,Prod) in sync Expand / Collapse
Author
Message
Posted Friday, December 28, 2012 3:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 2,268, Visits: 3,425
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1401078
Posted Friday, December 28, 2012 7:15 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:00 PM
Points: 485, Visits: 452
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.
Post #1401105
Posted Friday, December 28, 2012 8:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 35,572, Visits: 32,164
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401113
Posted Friday, December 28, 2012 9:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 2,268, Visits: 3,425
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1401114
Posted Saturday, December 29, 2012 11:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 35,572, Visits: 32,164
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401194
Posted Monday, December 31, 2012 3:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 5,436, Visits: 10,116
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
Post #1401344
Posted Monday, December 31, 2012 6:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 35,572, Visits: 32,164
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401379
Posted Monday, December 31, 2012 6:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 10:49 AM
Points: 21, Visits: 312
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.
Post #1401380
Posted Monday, December 31, 2012 8:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 2,268, Visits: 3,425
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1401406
Posted Monday, December 31, 2012 8:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 2,268, Visits: 3,425
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1401407
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse