Tools for maintaining and deploying database schemas and data for SQL Server?

Question

I'd like to maintain the schema and 'static' data of a SQL Server database as code in a version control system. I'd also like to be able to deploy specific versions of the database code to actual instances of the relevant database, i.e. migrate a database to a new version (and optionally 'migrate-down' to an old version).

Clarification

Databases

I'm assuming that I don't need to explain what a database is or that SQL Server is a specific database system, i.e. specific software for managing databases.

Database Schema as Code

Among software developers that work with databases, it is desirable to be able to maintain the 'schema' of a database, i.e. information about its structure, in the form of 'code'. That code should allow someone to create a database with the same 'schema' as is represented by the code. The code could be in the form of DDL (Data Definition Language) statements, e.g. SQL code, typically in a vendor-specific version thereof, that is used to create objects like tables and indexes in a database, or it could be in a general programming language, e.g. Java, Ruby, C#, etc.

Version Control and Code

Once the schema of a database is represented in code, that code can be maintained in a version control or source control system, software for managing revision control. From Wikipedia:

Revision control, also known as version control and source control (and an aspect of software configuration management), is the management of changes to documents, computer programs, large web sites, and other collections of information.

Maintaining code that represents the schema of a database in a version control system is desirable because a history of changes to the schema can be kept and other tools, like the set of tools I'm asking about in this question, can access the schema, even different versions of the schema, for other uses.

Software Deployment

'Deployment', in the context of software development and software maintenance, involves "... all of the activities that make a software system available for use.". In the context of this question, I'm interested in recommendations for software tools for making available a specific version of a database schema in the form of concrete specific instances of databases in the SQL Server DBMS.

Example

As an example, consider a database schema with versions v1, v2, and v3, and some number of actual databases, all of which have one of those versions of the schema. For this question, recommended software should be capable of upgrading (or downgrading) a particular database from its current version to any other version of the same schema, and it should use the code for specific schema version that is stored in the version control system.

Some Specific Criteria Mentioned in Comments

"select a version control entry containing a schema, (re)grade the DB to match (what does that mean exactly?"

The database schema is represented as code in a version control system. I'm not interested in tools that work directly with version control. The tools about which this question is soliciting recommendations should be able to make use of a 'version' of the database schema. An example of a schema 'version' would be the code that represents the schema as-of a specific revision ('commit' in Git) or a specific tag/label for version control systems that support such features.

Here is a basic list of functionality that should be provided for a tool to "(re)grade the [target] DB to match":

Tables

• If a table exists in the source schema but not the target database, the tool should create the table in the target database. This should include ancillary objects like keys, constraints, defaults, indexes, triggers, etc.
• If a table does not exist in the source schema but it does in the target database, the tool should provide means for specifying whether the table in the target database should be dropped or not. [This setting may apply to all such tables.]

Table Columns

• If a column exists in a table in the source schema but does not in the same¹ table in the target database, it should be created in the target database. As the DDL statements for most (?) DBMS already provide ways to specify the initial value of new columns for existing tables (e.g. especially for new columns that do not allow NULL values), I'm agnostic about whether there needs to be anything special provided by the tool itself to handle this. However, if a recommended tool uses some form of 'schema code' that is not the standard DDL statements of the respective DBMS², then the tool should provide some means of specifying the initial values of columns to be added.
• If a table column does not exist in the source schema but does exist in the target database, the tool should provide means for specifying whether the column in the target database should be dropped or not. [This setting may apply to all such table columns.]
• If the same ¹ table column exists in both the source schema and the target databases, but they are of different types or there are other differences (e.g. length, numeric scale, etc.), the tool should change the column in the target database to match the source schema and make a best effort to preserve the existing data in that column. It's perfectly valid for a recommended tool to exit reporting failure if it is unable to convert the existing data without loss (e.g. if the length of a column is decreased and data would need to be truncated).

Objects Ancillary to a Table(s)

Some examples of these objects include primary keys, foreign key constraints, defaults, and indexes.

• If an object ancillary to some table exists in the source schema but not in the target database, it should be created in the target database. As above, for the creation of new table columns, the tool should be able to handle scenarios involving possible conflicts or errors adding the ancillary object. It's perfectly fine for the tool to simply leave it up to the people maintaining the schema code to ensure that any conflicts or errors are handled properly – it's entirely valid for the tool to exit reporting failure if it encounters a conflict or error like this.
• If an object ancillary to some table does not exist in the source schema but does exist in the target database, the tool should provide means for specifying whether the ancillary object should be dropped in the target database. [This setting may apply to all such objects.]
• If the same¹ object exists in both the source schema and in the target database, the tool should change (or drop and recreate) the object in the target database to match the source schema.

Other Objects

By 'other object', I'm referring to objects like views, stored procedures, user-defined functions, etc. These objects can generally (?) be safely dropped and recreated. A recommended tool should be able to drop and recreate these objects in the necessary order for objects that depend on other such objects (e.g. a view that references another view).

• If an other-object, etc., exists in the source schema but not in the target database, it should be created in the target database.
• If an other-object like a view, blah blah blah, does not exist in the source schema but does in the target database, the tool should provide some means of specifying whether the other-object in the target database should be dropped. [This setting may apply to all such other-objects.]
• If one of these objects exists in both the source schema and the target database, the tool should change (or drop and recreate) the object in the target database to match the source schema.

Static Data

Rows in table with 'static' data to be synced should be identified by the values of the columns in the primary key for that table.

Rows in static data tables in the target database should be modified to match the source schema. Rows in the source schema that are not in the target table should be inserted into the target table. It would be nice if the tool provided a means of specifying whether rows that exist in the target table that do not exist in the source schema should be deleted.

Notes

¹ This is actually a potentially subtle point! Most tools will probably implement 'sameness' for database objects as having the same name. But there are alternatives, at least for some DBMS, e.g. in SQL Server, extended properties could be used to implement a form of persistent identity for database objects that is independent of the name of the object. This would be handy as then the tool would be able to automatically handle (at least some) cases where objects are renamed.

² I asked about SQL Server specifically, but at this point I'd really love it if anyone would provide an answer to this question at all, for any DBMS.

Answer 1

There are a few options in front of you.

Liquibase (Free, Apache License) is the only free option I know of that supports SQL Server. It is it's own source control package, which means that you'll have to learn another set of commands and figure out branching, merging, etc. Bonus point for Liquibase is that if you know Java you can build your own automation using the Liquibase libraries.

Offscale (Free, unspecified license) takes things a bit further by allowing you to also source control datasets, do automated testing of a given model with a test dataset. Unfortunately, no support for SQL Server.

Redgate SQL Source Control is a nice commercial option. It has support for SQL Server, Oracle, etc. and a variety of tried and true source control platforms (svn, git, mercurial, perforce, etc.). It does support data versioning as well. They have a companion product for continuous integration (automated deployment) and a variety of other tools in the same space. A little too expensive for personal use in my opinion, but very inexpensive for enterprise usage. There is a free trial.

Answer 2

DB Ghost

Overview

The DB Ghost products satisfy the requirements.

The Change Manager product can generate scripts (DROP and CREATE scripts, which can be run 'manually') for all of the schema objects as well as static data. The Change Manager Professional product can be automated to do so via command line, e.g. for scripting a particular development database regularly.

The Packager, Packager Plus, and Packager Plus Professional products can deploy changes in the form of a specific version of the scripts created by Change Manager. Packager Plus can perform a 'dynamic upgrade', basically a schema and static-data 'sync' between a target database and a source database, where the source database can be created from scripts. Packager Plus can also create an executable that can be distributed to perform the dynamic upgrade in the relevant target environment. Packager Plus Professional can be automated to do all the aforementioned via command line.

'Syncing' versus Migrating

One common strategy for maintaining changes to a database schema involves explicitly maintaining database migrations, both for upgrading and for downgrading. A migration is effectively code for performing a single change to a database instance of a schema.

You can maintain migrations with DB Ghost, but it only just allows you do to so, it definitely doesn't support it beyond that. However, I think this is A Good Thing.

Instead of migrating databases during a deployment, DB Ghost instead 'syncs' the target database (the database to which you wish to deploy changes) with a model source database that is generated during deployment from a specific version of the database schema. Instead of executing specific migrations, it compares the source and target databases, and makes the changes necessary to the target database to resolve any differences it detects.

The chief advantage of syncing versus migrating is that instead of maintaining all of the scripts for all of the migrations one can (mostly) maintain scripts for the current version of all the database objects in the schema. Thus the code that represents the schema is organized by database object instead of spread out over a (potentially large) number of migrations.