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?

  • 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, 2013 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 Shah
    Oct 31, 2013 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, 2013 at 18:01

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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