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

Installing a 1t db and best practices to keep 3 data bases in sync Expand / Collapse
Author
Message
Posted Tuesday, October 1, 2013 12:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 2:54 PM
Points: 95, Visits: 188
Hi, All

This is my project which i am going to work on.
-- have to install and configure test/dev/prod databases using SQL 2008 and the database have the potential to grow 1t+ (need suggestions and best practices on hardware and software) environment is vmware
-- have to keep the prod and dev and test databases in sync all the time
-- the test data gets new data weekly through SSIS packages.

updated the requirement

issue one ---in the diagram the idea is to keep the load db and reporting databases 1 and 2 in sync.
issue two --- we get request from front end to make a change in reporting 1 and it has to take place in 30min its just a time frame.
issue three--- can we keep the reporting db1 and load db in sync even if there are changes done.

Thank you for every one for looking at my post


  Post Attachments 
Untitled1.jpg (4 views, 88.24 KB)
Post #1500555
Posted Tuesday, October 1, 2013 2:09 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:05 PM
Points: 36,710, Visits: 31,158
Just a suggestion...

Keeping a reporting database in sync with production is a fine idea. Keeping a development database in sync with production is going to be a very painful thing especially for the developers.


--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 #1500591
Posted Tuesday, October 1, 2013 5:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
I'm with Jeff. Keeping dev in sync with prod is a bit of a problem.





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1500626
Posted Wednesday, October 2, 2013 6:15 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 23, Visits: 192
I too will tell you that keeping them in sync is going to be a hard task.

Do you have SAN drives on these VMs? If so, you might look at a nightly job to sync the data up, so you get a fresh copy daily rather than continously syncing.










Jason Carter
Tampa, Florida

"Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young" - Henry Ford
Post #1500743
Posted Wednesday, October 2, 2013 9:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 2:54 PM
Points: 95, Visits: 188
yes we do have SAN
Post #1500814
Posted Wednesday, October 2, 2013 9:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 2:54 PM
Points: 95, Visits: 188
thank you ALL and there is slightly change in the requirement
Post #1500815
Posted Wednesday, October 2, 2013 9:26 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 23, Visits: 192
On a couple of our QA servers, we have daily jobs that will detach the database, copy a drive via SAN specific commands, then reattach the newly copied database. SAN copies are much faster than copying via windows.

We also run a few other processes after wards to get our QA users permissioned.

Basically automated data refreshes for DEV and QA. Hope the idea helps.










Jason Carter
Tampa, Florida

"Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young" - Henry Ford
Post #1500816
Posted Wednesday, October 2, 2013 9:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 2:54 PM
Points: 95, Visits: 188
thanks and how to put databases in sync when the date is changing in both at different timings?
Post #1500817
Posted Wednesday, October 2, 2013 9:36 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 23, Visits: 192
My suggestion is for a once a day sync, not continuous.

Using my suggestion you would have a fresh copy of production every morning. Anything you did in DEV/QA yesterday is gone.









Jason Carter
Tampa, Florida

"Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young" - Henry Ford
Post #1500818
Posted Wednesday, October 2, 2013 10:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
harita (10/2/2013)
thanks and how to put databases in sync when the date is changing in both at different timings?


Why would you want to have the dev database perfectly in sync with your production database?

This a nightmare to manage, and a nightmare for the developers who must continually deal with the constant changes. Not to mention regulatory requirements of scrubbing production data when used in a QA or dev type of environment.

Do the once a day method. You will save you and your boss plenty of hair that way.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1500839
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse