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).
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.
'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.
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":
- 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.]
- If a column exists in a table in the source schema but does not in the same1 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
NULLvalues), 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 DBMS2, 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 1 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 same1 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.
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.
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.
1 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.
2 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.