Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a draft of requirements that look like this:

  • Production database should be copied in real-time to "Pre-prod" database
  • The Pre-prod database should be available to read and update
  • It should be possible to make schema and data changes to Pre-Prod
  • Schema and data changes on Pre-prod should not effect Prod

The databases will be on the same SQL Server instance.

I'm wondering what approach to take? Transactional Replication seems the best option, but applying schema changes to a subscriber seems like a recipe for disaster.

Mirroring is out because the mirror will not be available; log shipping is out for similar reasons.

Are there any other technologies I should be considering?

share|improve this question
5  
Making updates to a pre-prod database on the same instance as the production database also seems like a recipe for disaster. –  Jon Seigel Oct 31 '13 at 13:25
    
+1 to Jon and totally agree with it. Also, You have to change your requirement and redefine - near to real time as you will be hitting the limitations - unless you design as sort of ETL that will continuously extract, transform and load data using CDC (sql version dependant) or use third party softwares that will allow to do it. –  Kin Oct 31 '13 at 21:29
1  
Is there actual justification for the real-time data? Once any changes begin to be applied to the pre-prod environment (especially at the schema level) it will necessarily block this "replication" (for lack of a better term) and will cause all kinds of problems. I think I'd try to ascertain as to whether or not that's an actual requirement or if it's a perceived requirement. –  swasheck Nov 21 '13 at 18:01
add comment

1 Answer

Transactional replication gets you close, but as you said, changing the subscriber schema is a recipe for disaster.

Transactional replication uses stored procedures to apply the data changes. You could, with every table change, change those procedures to deal with the underlying changes. That is a lot of additional manual work, but I don't think you will find a solution without that. Those procedures are created automatically when calling sp_addarticle (See value 0x02 for the @schema_option parameter and the @ins_cmd, @upd_cmd and @del_cmd parameters.)

However, you might be better of to take a copy at the beginning of the development cycle and then not apply new changes till the end of the cycle. That is a simple backup-restore with the added benefit that you do not have to constantly adjust testing scripts do deal with the changing data.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.