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?