Versioning Existing Model Schemas
[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]
As you develop an application, your models change to support new features and functionality. This type of versioning is schema versioning. This topic examines the challenges and potential solutions for updating models to a newer schema version.
Note
Another type of versioning is data versioning. For more information, see Versioning Data with Modeling Services Folders.
Schema Versioning During Development
During application development, a model schema changes frequently to adapt to the developing application. During this development stage, you should deploy the models to a development SQL Server 2008 database. This provides the most freedom for schema versioning. Under some conditions, versioning is as simple as redeploying the model to the database. In more complex scenarios, you may have to recreate the target database before deploying new schema versions. For more information, see Updating Models in the Database (SQL Server Modeling CTP).
Schema Versioning Production Data
After deploying a model to a production SQL Server database, applications and users typically add data to the model that you want to preserve. This complicates the process for updating the model schema, because it involves changes to the existing data and to the applications that depend on the data. In “M”, examples of schema versioning include adding or removing fields from types, extents, or computed values. In the database, this has the effect of adding or removing columns from the associated tables and views.
Although you can create a new “M” module or SQL Server schema for each iteration of a model, it is not always desirable to do so. However, migrating existing data from a previous model version to a new model version is a manual task. If the model is written in “M”, one possible solution is to create a new SQL Server database with the Mx.exe tool. Deploy the modified schema to the new database, and then use database scripts or SQL Server Integration Services to move the data from the current database into the new schema. After the new database contains the necessary data that conforms to the new schema, take the original database offline and rename the new database to the name of the original database. This is a custom process that requires careful planning, and there might be other strategies or tools that achieve the same result. These strategies require you to immediately modify any dependent applications to use the new schema version.
If the model is created and managed directly in SQL Server 2008, there are additional options for a more gradual versioning strategy. The existing tables can be altered to meet the requirements of the new model version. The existing views can also be modified to provide a translation layer for clients of the older model. For more information, see How to: Version "Oslo" Repository Schemas.
See Also
Concepts
Versioning Data with Modeling Services Folders