Database development teams show an increasing interest in getting their SQL Server databases into source control. However, depending on the team’s actual needs, their development plan and the level of source control integration required, there are various ways to achieve getting a database under source control.
Once a SQL database is committed to a source control repository with all of its objects, the next task is to commit the static data. Static data is non-transactional data from tables that generally don’t change often like postal codes, department names etc. Many teams treat this type of data akin to the database structure itself, by versioning it to track changes and deploying it from source control just like database objects. As data is versioned in source control, managing it can be automated as well.
Imagine a scenario where you want to get your database into source control quickly and easily, including all schema objects and data from certain code tables that won’t change aka Static data. Then, once you have ported your database successfully to source control, to be able to update the repository nightly with any and all changed objects. In this way, you’ve fully and automatically source-controlled your database, without having to worry about direct integration, check ins, check outs etc. essentially providing much of the “gain” of database source control integration, with little of the “pain”. This article will describe how to build this “poor man’s” SQL database source control integration system using a 3rd party tool, ApexSQL Script
A request is received from the development team asking to copy one of the production databases to the DEV SQL Server in order to simulate real time testing scenarios on it. Once the production database backup is restored to the DEV SQL instance, replacing the old copy, the old DEV database users will be replaced with the live ones. But we still need the old DEV database logins for the DEV site connection.
A SQL database source control “label” or “tag” (aka revision tag) (name depends on the particular source control system) represents a snapshot in time of the source control repository and its contents. It can be saved as a reference for the future use. When the database development cycles reach a particular milestone e.g. a new build, a source control label can be created as a checkpoint. The team can continue to work on the database but revert to the source control label at any time.
Many development teams have the need for a quick and easy but effective solution to committing SQL Server database objects to source control, but aren’t yet ready to move to full source control integration at check in/check out level.
In order to get objects under a source control in a “Poor man’s” approach, creation scripts need to be produced for each table, stored procedure, and any other object in a database. Afterwards, the scripts need to be committed to a source control system.
ApexSQL Script is a SQL Server database migration tool which can create SQL scripts by converting SQL database objects and data into a single or multiple deployment SQL scripts, .NET solutions, or executable installers. In many cases when moving a SQL database, there’s no need to migrate the entire SQL database, but only a part of it.
In some cases a development team may decide to migrate databases between environments by generating a deployment script from multiple SQL databases (development/testing). They can use a tool like ApexSQL Script to create a single script from multiple sources, making it easy to apply to the target environment or the source control. Using this feature, there’s no need to script one SQL database at a time, as multiple data sources are processed simultaneously.