Transparent Scale-Out (OLTP)---a Technical Reference Guide for Designing (OLTP Solutions Mission-Critical OLTP Solutions

With transparent scale-out, an application does not need to know that the data is distributed in several databases across Microsoft SQL Server instances or servers. A benefit of transparent scale-out is that the application is simpler to code and maintain because it does not need to know how the data is partitioned or where the data is located. The primary SQL Server feature that allows transparent scale-out is the distributed partitioned view (DPV), sometimes referred to as federated view.

The mechanism used by SQL Server for DPV data access to a remote database is known as linked server. A linked server is a SQL Server database object that resides at an instance scope. Object linking and embedding database (OLE DB) access is built into the query engine. This allows data to be retrieved by the same basic mechanism from a remote storage engine as it is from the local SQL Server storage engine.

DPVs are helpful when the data can be partitioned but there is a requirement to access or change the data as a single table. SQL Server factors the query into smaller queries that run on each server, and then all the results are aggregated. As with data-dependent routing (DDR), master and reference data would most likely be replicated across all servers to minimize data movement for join purposes. Depending on the complexity of the queries and how they are coded, there can be performance penalties when performing cross-server queries.

In some cases, DPVs are used to help manage very large databases (VLDBs). Instead of creating and maintaining a multi-terabyte database, several smaller databases within the same instance are created. This helps with database backup and recovery, with index maintenance, and with data availability in case a database becomes unavailable for any reason.

Best Practices

The following resources provide some best practices and some pitfalls to avoid.

• Start with SQL Server Books Online (BOL). There is a lot of information about DPVs in BOL; it is necessary to review most of this to get a good understanding of exceptions or limitations. Start with the BOL article Distributed Queries.1

• A brief summary of best practices for federated SQL Servers, and tips of things to do and to avoid follows:

  DO:

  • Avoid cross-server joins whenever possible. One solution is to replicate the reference/dimension tables to every server to avoid having to copy records from the remote server to the local server to perform the join.

  • Use the same collations in all databases, otherwise the startup filters are not applied and the queries are sent to servers with different collations resulting in unnecessary data being retrieved.

  • Use the same session settings in all connections, otherwise startup filters are not applied and the queries are always sent to servers with different session settings resulting in unnecessary data being retrieved.

  • Create an index with the partitioned column as the leading column on the index because it is expected that most of the queries will contain the partitioned column in the WHERE clause. The optimizer uses this index and the associated statistics to perform more efficient queries.

  • Keep in mind that plan guides do not work for distributed queries.

  • Create some startup stored procedures that can run the queries that you need. Otherwise, after restarting SQL Server, the first user running each query will pay a heavier penalty because the query has to touch every server. These startup procedures also create a local connection pool to each server. Even though creating a connection is fast, it is still more efficient if the first user does not have to wait.

  DO NOT:

  • Do not use non-deterministic functions directly in your INSERT or UPDATE statements.

  • Do not use code predicates that require SQL Server to convert to a type in your tables, because this requires the query to be sent to every server for execution. See the blog article Distributed Partitioned Views / Federated Databases: Lessons Learned2 for more information.

• DPVs are sometimes called a "90% solution," meaning that 90% of the time they just work. The remaining 10% of the time, it can be difficult to work around the problems encountered. For example, query hints do not work and plan guides are not recognized by queries from a linked server.

• SQL Server development and management tools are not specifically designed to support DPVs. Registering each node in SQL Server Management Studio in the same group can help when developing, deploying, and performing maintenance tasks.

Case Studies and References

This section provides some case studies for reference.

• With rapid growth, Myspace3 IT redesigned the architecture several times. The current architecture is based on scale-out, using several SQL Server technologies including DDR, DPV, and Service Broker.

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

• Because of the challenges associated with scale-out and with the limitations of DPV, be careful when adopting DPV unless the use is reasonably straightforward. Consider using local partitioned views within an instance to partition the data across multiple databases instead of DPV, if this is useful and sufficient.

• Pick the partitioning scheme very carefully. It is impractical to change the partitioning scheme without a major rewrite once the data has been loaded and queries have been deployed for production. The initial design has to be very thorough to cover the various data access and update needs.

• Try partitioning in the following sequence: table partitions, local partitioned views, and then DPVs. The optimizer is good at partition elimination at the various levels if matching constraints are included in the WHERE clause of the queries.

• Identify how often master/reference data is changed and how best to keep local copies of this data in sync across all servers. If the data is changing slowly, a SQL Server Integration Services (SSIS) or an extraction, transformation, and load (ETL) process can be used to keep the local copies in sync. Alternately, merge replication can also be used. If the data changes more frequently, there might be a requirement for transactional replication.

Appendix

Following are the full URLs for the hyperlinked text.

1 Distributed Querieshttps://msdn.microsoft.com/en-us/library/ms188721.aspx

2 Distributed Partitioned Views / Federated Databases: Lessons Learnedhttps://blogs.msdn.com/b/sqlcat/archive/2007/06/20/distributed-partitioned-views-federated-databases-lessons-learned.aspx

3 Myspacehttp://www.baselinemag.com/article2/0,1540,2082921,00.asp