For a DBA\Developer it may be a common requirement to keep various instances of SQL Server databases (includes development, testing, staging and production database) in Sync as and when new changes get introduced in the Schema.
As with any application development cycle, database schema get updated over time with simple or complex changes, challenging the integrity of the database and hence the application. Due to this reason, changes are not directly effected on the production databases. Changes are sequentially propagated over various local database setups which includes testing and staging databases before being effected in the production setup. This change propagation (from testing to staging and then to production) involves preparation of appropriate schema upgrade scripts whose complexity increase with the quantity and the complexity of changes being effected (synchronizing). These tasks are very time consuming and is error prone.
Typical approach :
Step 1: Analyze Changes
This step involves the following actions:
Analyze the modified sql server schema manually for all the changes that have been made in the various objects to propagate the same to the required sql server database.
Check if the proposed changes do not affect any other dependent objects in the database system.
If the proposed changes affect the functioning of the system, co-ordinate with the respective developer and get the issues resolved.
Step 2: Generate Migration/Upgrade Scripts
This step involves the following actions:
Based on the analyzed changes, code the appropriate migration (upgrade) scripts to update the target schema to reflect the changes.
Ensure dependencies are appropriately handled in the migration scripts to ensure that the system will work as before, after executing the migration scripts.
Test the scripts for functionality and database integrity.
Step 3: Execute and Save Scripts for maintenance
This step involves the following actions :
Execute the migration scripts using standard SQL Server client tools (Query Analyzer or SQL Server Management Studio) on the target database.
Script out the target database object after executing the migration scripts and save the same in a Version system for future reference.
Save the migration scripts for Audit trail (to keep track of the various changes done to the Database over time).
Key Limitations :
Manual effort involved in identifying the whole list of changes by comparing the SQL Server databases, is time consuming and error prone.
Migration scripts coded manually for updating the databases, taking into account all the dependencies, is inefficient and error prone.
Migration scripts will have to be maintained separately to have an audit trail of changes in schema over a period of time.
As you can see, each and every cycle of changes would involve the above mentioned steps (and actions) which will be time consuming, error prone and non-productive.
DBChangeManager Solution:
SwisSQL DBChangeManager comes to the rescue of the DBAs/Developers who are thrown with the above specified challenges. It automates the process of Comparison, Upgrade script generation and Execution of the same on the target databases to ensure a rapid, precise and error free Synchronization of SQL Server databases. With DBChangeManager, all you need to do would be:
Specify the SQL Server Resources to be compared. The source can be a Schema script (SQL File) or a live database that contains the changes and the target should be the database which needs to be Synchronized.
Compare and view the list of Schema differences through a powerful, accurate and highly usable difference viewer of DBChangeManager.
Select the database objects that needs to be synchronized to generate the appropriate Synchronization (Migration) Script.
Execute the Synchronization script through the tool.
Note: With DBChangeManager, user need not save the migration scripts separately as the same will be saved and maintained by DBChangeManager as part of its Synchronization Audit process for future reference.
Solution Benefits :
Saves time and effort involved in comparing and identifying the changes/differences between two SQL Server databases.
Saves time and effort involved in generating the upgrade scripts by automating the same through an easy-to-use interface.
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 tool accessible from any system in the network in which the tool is installed.
