We have 3 databases: the first for development, the second for testing by our users and finally the third for production. At this moment all our project leaders have the GRANT rights to create and alter tables, triggers, views, FK-constraints, ... on all three databases.
There are multiple development teams, all working on different parts of the database, all with their own development / testing / production cycle.
Normally we replace the development and testing databases with a restore of the production database after 14 days. So development and testing can happen with realistic data.
Due to a new regulation we have to restrict the access of the project leaders to the production environment. That means we will have to do their job of creating and altering tables, views, ... We have to make sure that the scripts that were used to alter the test database (and that were tested and approved) are exactly those that will run on the production database.
What we came up with is this:
We allow them full access to the development database, as they can today
They give us the necessary (SQL?) scripts to go from a given version to the next version of that part of the database they are working on (we call that delta-scripts)
We run their delta-scripts on the development and test database after each restore, and finally on the production database.
But we see a lot of practical problems with this. For starters:
Views, triggers, SP's, indexes, FK's , ... can or can not be affected by any of these delta scripts.
This process should be fully automated, as it will have to be invoked after each restore.
The delta scripts can be altered during development en testing, yet the code that finally will run on production must be exactly the same as the one that was tested on the test database.
Any better ideas than what we came up with? Where can I find a good guide of how to manage this ?
What tools are available that can help us with the delta-scripts?
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages