Best Practices for Improving Availability and Scalability of SQL Server Modeling Services

Article
09/22/2010

[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.]

This topic describes best practices for improving the availability and scalability of SQL Server Modeling Services.

Availability and Scalability

High availability for the Modeling Services database involves strategies to ensure that Modeling Services data can be accessed during planned or unplanned outages. As more data is added to the Modeling Services database, applications will depend on the ability to access that data. It is important to evaluate choices that improve the availability of the Modeling Services database.

Scalability is the potential for the database to maintain performance goals while adapting to increased workloads. Scalability involves scale-up strategies of upgrading hardware as well as scale-out strategies of adding additional machines and SQL Server database instances. Performance tuning also affects scalability, because the database can efficiently handle a larger workload.

The following suggestions are current best practices for improving availability and scalability of the Modeling Services database. In some cases, the same strategy improves both areas of concern. Each suggestion will highlight the benefits achieved by using the suggested technique. Note that the strategies below are based on general best practices for managing availability and scalability in SQL Server 2008. An attempt is made to distinguish suggestions that are specific to Modeling Services scenarios. For more information about general availability considerations for SQL Server 2008, see Improving Availability. For more information about general scalability considerations for SQL Server 2008, see Improving Scalability.

Database Mirroring

Use Database Mirroring to maintain two copies of the Modeling Services database on two separate SQL Server instances, typically on different servers. The database mirror acts as a standby in the event that the primary Modeling Services server fails. For more information about Database Mirroring on SQL Server 2008, see Database Mirroring. For more information about specific considerations for using Database Mirroring with the Modeling Services database, see Using Database Mirroring with SQL Server Modeling Services.

Standard Transactional Replication

Use standard transactional replication to create a read-only copy of the Modeling Services database used for reporting and non-operational queries. Offloading these types of queries to a read-only copy of the database improves the scalability of the source Modeling Services database. For more information, see Transactional Replication Overview.

Peer-to-Peer Transactional Replication

Use Peer-to-peer Transactional Replication to create a read-write replica of the Modeling Services database. In this scenario, each copy of the database is both a publisher and a subscriber. This supports a database scale-out strategy that includes support for geo-scaling. For example, a user might access his or her regional copy of the Modeling Services database, and the user’s changes would replicate between this server and a remote Modeling Services database. This also factors into availability as well, because the replicated Modeling Services instances could be used in the event that there are problems with the original Modeling Services database. For more information, see Peer-to-Peer Transactional Replication.

Vertical Partitioning

Consider using vertical partitioning of entities for new model schemas in the Modeling Services database. Vertical partitioning involves moving infrequently used columns from a main table into one or more secondary tables. Common queries that access the columns only in the main table perform better, whereas queries that join the main table with the secondary tables can still access the entire set of data. For this reason, vertical partitioning results in better performance in scenarios where the columns in the secondary tables are less frequently accessed than those in the main table.

Consider two different types of vertical partitioning. The first method is to simply take out columns from the main table and add them to one or more secondary tables. These tables all share identical primary key values and the same number of rows. The second method of partitioning involves normalization techniques. For example, a table that describes an Employee entity might have columns applicable only to full-time employees as well as columns applicable only to part-time employees. Columns shared for both types of employees could be left in the main table. Columns applicable to full-time or part-time employees could be moved into two separate tables. The original table could then contain a distinguishing column to indicate the type of employee that the row describes. Based on this column, a join can be made with the correct secondary table to get the additional attributes.

If you add new model schemas to the Modeling Services database using the Microsoft code name “M” language and tools, you can create vertical partitioning through the design of the “M” types and their relationships. If you add new model schemas directly to the Modeling Services database, you have full control over the partitioning choices that influence performance.

Table Design Patterns

When adding tables directly to the Modeling Services database, follow the best practices for table design. This can maximize the performance of queries that target these new tables. For more information, see Performance Implications of Table Design Choices.

Share via