Change Management

Managing change in SQL Server applications requires a process that protects data integrity & maximizes production uptime, while providing flexibility for new development. I would propose the following as a minimum configuration to accomplish these goals.

Create 3 database environments -- one for production, one for development, one for QA. Only the DBA has direct access to production or QA. The developer has direct access to only the development db. These 3 db's can sit on separate servers, or alternately the development and QA db's can run on named instances on the same server if size & activity is modest.

Create 3 application front-ends -- one for each database; containing some code, config file or other means to connect the front-end to the correct db; having some method to prominently display the connected server & database when it is not connected to the production db. (The front-end is really a single design, it is only the connection string that varies between the 3 copies.)

To begin, the DBA takes a backup from production & restores it to both the development & QA servers.

The developer connects the DEV front-end to the DEV db, writes T-SQL scripts to make any changes to the design, and updates the front-end for those changes. It is possible to use the GUI to generate the change script, but the GUI is never used to actually apply those changes - that is accomplished with the script. The developer also prepares documentation for the desired change in the front-end.

At this point, the developer may decide to share the DEV copy with select stakeholders, especially if the change is complex, for feedback & verification that the change meets the requirements. This step is commonly referred to as user acceptance testing.

When the developer verifies the change in that environment, they pass the change script to the DBA who reviews the script, and then applies it to the QA db. The developer will then connect the QA front-end to the QA db and verify the change.

The QA person will then evaluate & verify the change using the documentation provided by the developer. Upon their sign-off, the app is ready for release.

The developer notifies all users that the database update will commence.

The DBA will backup all 3 db's and then apply the script to production. Following that, the developer will connect the production front-end to the production db & verify. The developer publishes the new front-end & notifies the users that the update is complete.

The DBA takes a fresh production backup & restores to both DEV and QA, and the developer reconnects DEV to DEV and QA to QA. The DBA may also pass a copy of the backup file to the developer, if the 4th environment is used as follows.

A 4th possible environment is for the developer to maintain a copy of the DEV db on their PC, using e.g. the MSSQL Developer Edition along with a 4th copy of the front-end. This provides ultimate flexibility of design; the developer restores the backup to that system & can use that as a test-bed for feasibility studies of design changes without impacting the true DEV environment.

Throughout all the above processes, source control is used to maintain matching copies of the front-end & database scripts to allow rolling back changes that have unintended consequences.