SQL Server Modeling Services Scalability and Availability

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

Scalability for the SQL Server Modeling Services database is the potential for the database to maintain performance goals while adapting to increased workloads. Availability of the Modeling Services database focuses on keeping the database online and responsive for dependent users and applications.

Scalability

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. Modeling Services leverages the following SQL Server features to improve scalability:

Standard transactional replication.
Peer-to-peer transactional replication.
Snapshot isolation.
Vertical partitioning.

Standard Transactional Replication

Standard transactional replication is used 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.

Peer-to-peer Transactional Replication

Peer-to-peer transactional replication is used 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 could access his or her regional copy of the Modeling Services database with all changes replicating between this server and the 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.

Snapshot Isolation

To prevent reduced throughput that occurs from reader-writer blocking, the Modeling Services uses row versioning-based isolation levels (also known as snapshot isolation). Snapshot isolation uses the version store in tempdb to store rows that were modified by transactions, requiring no locks on the rows themselves. Incoming readers can still access the most recently committed version of the same row. For query-intensive stores, like the Modeling Services database, snapshot isolation reduces the frequency of blocking, improving the scalability of the database.

Vertical Partitioning

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, while queries that join both tables on the primary key can still access the entire set of data. For this reason, vertical partitioning is normally reserved for scenarios where the columns in the secondary table are less frequently accessed than those in the main table. When the Modeling Services database uses vertical partitioning, the updatable views conveniently hide this implementation by joining the vertically partitioned base tables.

Availability

In addition to replication strategies that improve availability, Modeling Services uses several other SQL Server 2008 features, including Database Mirroring and the Resource Governor. Database mirroring keeps two copies of the Modeling Services database on two separate SQL Server instances, typically on different servers. The mirrored database can then act as a standby in the event that the primary Modeling Services server fails.

The Resource Governor in SQL Server 2008 allows Modeling Services to prioritize important workloads. There are critical Modeling Services queries that need to take precedence over others. There are also workloads that should have a lower priority or a resource restriction to avoid degrading the overall system performance. The Resource Governor allows for these types of decisions to improve availability.

Share via