Keep database schema under version control with SQL Examiner
Version controlling your database schema is important to ensure smooth development, deployment and maintenance of a database. This article describes how to use the advantages of version control in the process of database development.
Step 0. Before You Begin
A database schema can be represented as a set of database creation scripts. These scripts can be used to create the database, including all tables, views, stored procedures and other objects from the scratch, or update an existing production database to the newest version without causing loss of data. Naturally, the best way to keep a database schema under version control is by keeping its creation scripts in the source control system.
Database creation scripts are stored individually. A separate file for every object will contain a creation script for that object. For example, a file named dbo.MyTable1.sql will contain a script for creating the table MyTable1. For convenience, all scripts are grouped in several subfolders by the type of an object they create, such as Tables, Views, Stored Procedures, and so on.
As the development goes, objects get updated, replaced, or removed from the database schema. When an object has changed, you need to save a new creation script for that object in the source control system. As a result, you will have a comprehensive history of changes to all objects in your database saved in the source control system. This way you’ll be able to go back to any version of that database schema. You can specify versions of database schemes by the date or assign meaningful labels. You can also annotate any changes being made using check-in comments.
Step 1. Preparing the Scripts and Adding Them to the Version Control System
In order to get your database under version control, you’ll need to produce database creation scripts for every table, stored procedure, and other objects in the database. You will then need to add the creation scripts into your favorite version control system. While there can be many different ways to save the database as a set of creation scripts and check it into the version control system, using SQL Examiner is the simplest way.
To save the initial creation scripts and add them into the version control system, Launch SQL Examiner and choose Script Database Wizard from the Tools menu. The Wizard will ask you to specify the database you want to add to the version control system, select database objects to add (you can check all objects), and specify connection parameters for your version control repository.
The wizard works quickly, but may still take some time depending on the number of objects you have in your database. After the wizard is finished, your database schema has been successfully saved into the version control system.
Step 2. Updating the Database Schema in the Repository
Your initial version of the database is now safely stored in the repository. As you continue working with the project, tables are added, changed and removed, stored procedures evolve, and objects are added to the database. In order to keep your database schema under version control, you need to save all the changes into the version control repository.
In order to store the changes, commit the application code as you always do. SQL Examiner will help you commit database scripts. Launch SQL Examiner and compare your current development database with the last version of the database schema stored in the repository. After that, launch the Update Script Wizard, select the database, specify objects you are updating, and follow to add the updated creation scripts into the repository. The new database structure complete with all the changes will be saved as a new version in the source control system.
Step 3. Examining Differences between Database Versions
One of the benefits of using a version control system is the ability to investigate what changes have been made from one version of a database to another. The two versions of a database don’t have to be two adjacent releases; you can pick any two versions from the repository, different repositories or other sources.
In order to examine the differences between database versions, launch SQL Examiner and click the “New…” button on the toolbar. You will be asked to specify the server, connection parameters, and the database name for both versions. Click “Compare” to begin the comparison.
You can compare two database schemas from a variety of sources. Either source can be:
- A database running on the server;
- A version of a database in the version control repository;
- A set of database creation scripts on the hard disk or network location.
The comparison process is fast and simple. SQL Examiner makes it easy to quickly compare different versions of a database schema coming from diverse sources.
Step 4. Updating Production Database
One of the most important things a version control system is used for is the ability to update the production database on the client side without losing data. It is quite common when a client has an older version of a database that must be updated to the latest release, which has substantial differences and may require a different database structure to operate. This situation means you’ll have to convert the database structure from one version to another. In a complex database, a manual update would not only require a significant amount of labor, but would possess significant risks to the integrity of the database and any data it stores.
Launch SQL Examiner allows automating the process, safely migrating the database schema from one version into another. In order to safely migrate a database, you will need to have both versions of the database schema stored under the version control system. First, follow Step 3 from this guide to create a new version comparison, specifying the sources in the version control repository and selecting the two versions of a database, the production one and the one you are updating to. In the same wizard, you’ll be able to compare these versions and generate an automated migration script.
Run the migration script on the server to upgrade the database schema, and deploy the new version of the database.