385
Chapter 15: Executing Distributed Queries
15
Collations also play an important role in distributed queries. SQL Server supports multiple
collations, and collations can differ even at the column level. Each character value has an
associated collation property. For distributed queries all character data is defi ned by the
character set and sort order of the local instance of SQL Server. Thus, for distributed que-
ries, SQL Server can interpret the collation property of the character data of both the local
SQL Server instance and the remote data source and treat them accordingly.For example, SQL Server can use the collation properties to determine the proper
comparison and ordering operations and apply the same rules for converting, comparing,
and ordering for local columns.Indexed Access
The important thing to remember about indexed access is that SQL Server can use execu-
tion strategies that include using the indexes of the Index provider to evaluate predicates
and perform the necessary sorting operations against remote tables. However, to enable
indexed access against a provider, the IndexAsAccessPath provider option must be set.Sharding and Federations
The concept of Database Sharding has been gaining popularity over the past several years, due
to the enormous growth in transaction volume and the size of business application databases.Database Sharding is a "shared-nothing" partitioning scheme for large databases across a
number of servers, enabling new levels of database performance and scalability achievable.
If you think of broken glass, you can get the concept of sharding — breaking your database
down into smaller chunks called shards and spreading those across a number of
distributed servers.Microsoft has taken this concept and implemented a sharding/partitioning strategy called
Federations, which will soon be available in Azure SQL Database. This is not a chapter on
Azure SQL Database (see Chapter 31, “Managing Data in Windows Azure SQL Database” for
more information), the concept of partitioning or sharding your data does not apply only
to Azure SQL Database or cloud databases. You can use these techniques and technologies
with on-premises databases as well.You must understand when it’s appropriate to shard your database; the decision to do so
should not be taken lightly because it adds an additional level of complexity to your appli-
cation. For certain scenarios the benefi ts of database scale out can be huge. This is particu-
larly true for applications that require massive throughput.You can use many different approaches to scale out your databases. You need to determine
the right strategy because it can directly impact the complexity and performance of your
solution. For example, you might have a scenario in which you have a workload for a single
application spread across multiple databases with the need for your application to dynami-
cally determine which database to connect to for a given query and the ability to combinec15.indd 385c15.indd 385 7/30/2012 4:50:41 PM7/30/2012 4:50:41 PM
http://www.it-ebooks.info