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?
Best 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.