Download - Windows Azure SQL Database Federations
NEIL MACKENZIE
SQL Database Federations
Scaling out relational data in Windows Azure
Who Am I?
Neil MackenzieAzure Architect @ Satory Global
Windows Azure MVPBlog: http://convective.wordpress.com/Twitter: @mknz
Book:Microsoft Windows Azure Development Cookbook
Content
Windows Azure PlatformMotivation for SQL Database FederationsOverview of SQL Database FederationsTransact-SQL StatementsDMVsFan-out queries
Windows Azure
Cloud Services: Platform-as-a-Service
Web roles, worker roles Infrastructure-as-a-Service
Virtual Machines, SQL Server 2012
Connectivity Endpoints, Virtual Network, Service Bus
Storage Windows Azure Storage Service Windows Azure SQL Database
SQL Database
Database-as-a-Service Multi-tenanted Priced in GB/month
Database One primary and two secondary databases Quorum commit Maximum size: 150GB
Programmability TDS protocol SQL Server authentication Change connection string and go
Scalability
Scale up Use more powerful hardware Price/performance problem Fails at internet scale
Scale out Use commodity hardware Cost-effective way to increase performance Resilient towards failure
Scale-Out Data
Use multiple databases (shards)Sharding provides:
Larger data sizes Higher performance
Issues: Data distribution Connection routing
SQL Database Federations
Sharding-as-a-ServiceFederated database comprises:
Root database Federation member databases (shards)
Federation specified by: Federation name Distribution key
Routing Connection pooling
Federation Members
Federation member Federated data distributed by distribution key range Error to insert or update distribution key outside
range Member databases can have:
different sizes different schemas
Distribution key in: Clustered index Each unique index
Table Types
Federated tables In federation membersCREATE TABLE ( … )FEDERATED ON (CustomerId = custId)
Reference tables In federation membersCREATE TABLE ( … )
Common tables In root database
Federation Operations
Transact SQL support for federations: CREATE FEDERATION USE FEDERATION ALTER FEDERATION DROP FEDERATION
CREATE FEDERATION
CREATE FEDERATION federation_name (distribution_name <data_type> RANGE)
Data types: INT BIGINT UNIQUEIDENTIFIER VARBINARY(n) - n<=900
Root database can support several federations e.g. – customer federation, product federation
USE FEDERATION
USE FEDERATION ROOT WITH RESET Routes connection to root database
USE FEDERATION federation_name(distribution_name = value)WITH RESET, FILTERING={ON|OFF} Routes connection to appropriate federation member FILTERING= OFF allows connection to be used for any
data in the federation member FILTERING=ON restricts connection to a specified
distribution key
ALTER FEDERATION: SPLIT
ALTER FEDERATION CustomerFederationSPLIT AT (CustomerId = 200)
100 400 500
100 200 400 500
Range:
Range:
ALTER FEDERATION: DROP
ALTER FEDERATION CustomerFederationDROP AT (LOW CustomerId = 200)
ALTER FEDERATION CustomerFederationDROP AT (HIGH CustomerId = 200)
100 200 400 500
200 400 500100
200 400 500100
Dynamic Management Views
Definition e.g. sys.federation_member_distributions
History e.g. sys.federation_member_distribution_history
Operations e.g. sys.dm_federation_operations
Operation errors e.g. sys.dm_federation_operation_errors
sys.federation_member_distributions
Columns federation_id int member_id int (database id) Distribution_name sysname Range_low sqlvariant Range_high sqlvariant
Range: includes Range_low excludes Range_high
Security Principal
Root database CREATE USER user_name FROM LOGIN login_name
Federation member CREATE USER user_name
Security is otherwise like SQL Database
Fan-Out Queries
Loop over: sys.federation_member_distributions.Range_low
Perform queries in parallelTwo-step process
Member query Summary query (if necessary)
Summary
SQL Database Federations Sharding-as-a-Service Elastic scalability for SQL Database databases Management support Developer support
More Information
MSDN Wikihttp://bit.ly/A7sUdo
Cihan Biyikoglu blog:http://blogs.msdn.com/b/cbiyikoglu/
Post based on this presentation:http://bit.ly/wqD4Xo