Scenario:
Database Change Management forms an important part of an application development process. Database Change Management refers to the process of maintaining the Integrity and proper functioning of a database schema throughout a collaborative development cycle.
Collaborative development environments typically involve multiple database developers working in various sections of the same database schema in parallel, introducing changes that are invisible to one another. Key challenge in such an environment is to ensure that the changes done in parallel does not affect the system as a whole and incremental changes done to the system always takes into account all the recent changes that have been effected thereby maintaining integrity.
Typical Approach:
- A copy of the production database script is stored in a versioning system (VSS, CVS etc.,).
- Developer - 1 involved in database development checks out a copy of the schema to make necessary changes
- Developer - 2 also involved in development checks out his own local copy of the schema to make the required changes
- After making the required changes, Developer - 1 checks the versioning system for any latest updates and checks-in the modified schema.
- Developer - 2 when through with the changes in his local copy, checks the versioning system to find the schema to be modified by Developer - 1.
- Developer - 2 manually identifies any overlapping changes made by Developer - 1. Generates upgrade scripts as required to update his local development database to analyze the new changes on top of the changes he has introduced.
- Ensuring satisfactory functioning of this system in his local database, merges the schema in the versioning system with his own changes.
- Once the development cycle is complete, the Administrator generates an upgrade script based on the changes that have happened to the schema in the versioning system to update the required testing/staging databases.
- After required testing, the changes are propagated to the production environment.
- The script in the repository is then tagged representing the milestone for tracking/audit purposes.
Key Limitations:
- Time Consuming as developers have to spend time manually, generating upgrade scripts to update their local database changes with the new modified schema done by any of his colleagues
- Error Prone as the upgrade scripts are to be manually coded to upgrade the existing database with the new changes
- Increase in Complexity with increase in the number of developers working on the same Schema.
- Administrators will have to spend a whole lot of time manually to identify the new changes in the schema and generate appropriate upgrade scripts to update testing/development/product database setups.
DBChangeManager Solution:
One of the possible collaborative development environment using SwisSQL DBChangeManager is shown in figure below.
In the figure above Versioning System (CVS, VSS etc.) forms the prime element of the process. With DBChangeManager, the collaborative development cycle follows the below process :
- A copy of the production database script is stored in a versioning system. All developers involved in collaborative development, checks out a copy of the script to make the required changes.
- Developers compare the checked out copy of the script with their own development database(s) to check for any latest updates to the database schema using DBChangeManager. Changes if any, are synchronized into the development database so that the developer can start working with the latest schema.
- Developers make the required updates to the database schema, script out the database and check into the Versioning System.
- Changes made over a period of time are then updated into the testing/staging databases from the repository using DBChangeManager by the Administrator.
- Upon expected functioning of the system, DBChangeManager is further used to synchronize the testing/staging database with the production database.
- The script in the repository is then tagged representing the milestone for tracking/audit purposes.
As you can see, the DBChangeManager fits well with the Developers and the Administrators in a Collaborative development environment reducing both time and effort involved in maintaining the integrity of the database schema.
Solution Benefits:
- Saves developer's time and effort spent in upgrading their local development database(s) to reflect the latest schema.
- Saves Administrator's time and effort spent, by automating generation of upgrade scripts for the schema and in propagating the changes across testing/staging/production databases.
- Guarantees error free and efficient upgrade script for any database setup.
- Ensures complete rollback of changes on failures during synchronization. Guarantees integrity of the database at any point in time.
- No additional overhead of maintaining the migration scripts required with DBChangeManager.
- Web based interface makes it accessible from any system in the network in which the tool is installed.
- Serves as an effective SQL Server Database Change Management Tool.
|
