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

Development database synchronization. Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 8:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,607, Visits: 1,039
We have a production 10TB database.
We need a copy of the production database for development team and the dev database should be updated with the change in prod daily during night hours.

What is the possible method we can use for this.



Tanx
Post #1508898
Posted Monday, October 28, 2013 8:19 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:52 PM
Points: 590, Visits: 911
Does the dev database need to be read-write or read-only?



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508906
Posted Monday, October 28, 2013 8:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,607, Visits: 1,039
it should be read-write, but only one way synchronize from prod to dev.

Tanx
Post #1508909
Posted Monday, October 28, 2013 8:31 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:52 PM
Points: 590, Visits: 911
Well that limits your options to:
1. backup and restore nightly (not a great option with a 10TB database)
2. Replication (Transactional)
3. SAN Replication if applicable
4. 3rd party tool (like Red Gate Compare Tools) to script out the daily DML (if any) and DDL changes that took place over the day.

#1 doesn't seem like a good fit
#2 may have an impact on the performance of your production server
#3 good option, but you would have to talk to your SAN Admin (assuming you have one)
#4 Depends on the amount of data that changes daily




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508913
Posted Monday, October 28, 2013 9:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,607, Visits: 1,039
If we chose transactional replication what happens to the changes made in dev databases.

Tanx
Post #1508944
Posted Monday, October 28, 2013 9:14 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:52 PM
Points: 590, Visits: 911
I forgot to mention two other options:
CDC
Change Tracking

Here is a great article describing these options (compared to Transactional Replication):
http://www.brentozar.com/archive/2013/09/transactional-replication-change-tracking-data-capture/




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508954
Posted Monday, October 28, 2013 9:50 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:52 PM
Points: 590, Visits: 911
Transactional replication is designed for read-only subscribers, but it is not enforced. So, you can still write to the database, but then you could have conflict issues.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508990
Posted Thursday, October 31, 2013 1:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
Eswin (10/28/2013)
We have a production 10TB database.
We need a copy of the production database for development team and the dev database should be updated with the change in prod daily during night hours.

What is the possible method we can use for this.



Why does a dev DB needs to be refreshed with prod data on a daily basis?.

Even, If they modify or fix a issue or developing a new module i dont think they require latest copy on a daily basis. Considering it's size not recommed from my side.


Regards
Durai Nagarajan
Post #1510053
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse