Managing Data in SQL Server Modeling Services
[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.]
A SQL Server Modeling Services database provides a central location to store models. This topic provides an overview of managing data in a Modeling Services database. When necessary, there are links to more specific information on each area.
Use Schemas to Manage Models
Modeling Services uses SQL Server schemas to organize models in the form of tables, views, and other database objects. For more information about schemas, see "Oslo" Repository Schemas. Schemas provide a natural way to group database objects into different domains.
In Microsoft code name “M”, a module becomes a SQL Server schema when you load an “M” model into a Modeling Services database. The model definition within the module becomes other database objects that are owned by the SQL Server schema. For more information about this process, see Using "M" Tools. If you decide to use “M” to write and load models into a Modeling Services database, then you automatically use SQL Server schemas to organize your models in the database.
It is also possible to directly model data in the Modeling Services database. For more information, see Schema Design Patterns.
If you are using “M” to load models, it is possible to update the schema in the database using the force parameter of the Mx.exe tool. Note that this drops and re-creates target database objects, so you have to back up any data in target tables that you want to retain. To completely remove a model from a Modeling Services database, use the uninstall command of the Mx.exe tool. There are times when the uninstall command is unable to remove a model. In those cases, you can drop and recreate the target database. For more information, see Updating Models in the Database (SQL Server Modeling CTP).
Use Folders to Manage Model Instances
Models can take advantage of Modeling Services Folders to provide benefits in organizing, securing, and versioning model instances. A model instance consists of rows in one or more tables of a specific schema. These rows can reference a Folder in the [Repository.Item].[FoldersTable] table. This enables other features, such as securing access to the data belonging to a specific Folder. For an example of using Folders in “M”, see Adding Folders (Modeling Services). For more information about using Folders with T-SQL, see "Oslo" Repository Folder Design Patterns and How to: Create and Use "Oslo" Repository Folders.
Use Operational Auditing and Change Tracking to Manage Data Changes
It is often important to understand how data in a Modeling Services database has changed over time. This can be useful in proving compliance to policies, or it might help operations staff understand a change that caused a problem. Modeling Services supports operational auditing and change tracking. For more information about the architecture and design of these features, see "Oslo" Repository Change Tracking. For more information about how to use these features, see Change Tracking Tasks.
Follow SQL Server 2008 Best Practices
Modeling Services uses SQL Server 2008. You should follow SQL Server 2008 best practices for database and server management, such as backup-and-recovery, scalability, and availability. For more information about SQL Server 2008 best practices, see SQL Server 2008 Books Online. For more information about best practices that specifically apply to the Modeling Services, see "Oslo" Repository Administration.