140 likes | 371 Views
SQL Azure Federations. Patrick Riley October 2011, KY .NET UG. Data Virtualization. Federated System. SQL Azure Federations. “shard” [ shahrd ]. “shard” “ shard·ing ” [ shahrd ] [ shahrd-ing ]. noun A single physical database in a federated infrastructure
E N D
SQL Azure Federations Patrick Riley October 2011, KY .NET UG
“shard” [shahrd] “shard” “shard·ing” [shahrd] [shahrd-ing] noun A single physical database in a federated infrastructure A horizontal partition in a database verb The process of breaking an application’s logical database into smaller chunks of data Distributing chunks of data across multiple physical databases to achieve application scalability
Federation Concepts Federation: an object which defines the federation and the federation key Federation Root: The database which houses the federation object Federation Key: the key used for partitioning data (ex: Customer ID) and the distribution style (range) Atomic Unit: A single value of the federation key, always housed in the same federation member Federation Members (shards): a physical container for a range of atomic units Federated Tables: tables in federation members which contain partitioned data Reference Tables: small-scale lookup data copied to each federation member
Into the weeds…Federation Syntax -- in master database, create federation root database CREATE DATABASE marchmadness_db -- connect to the new marchmadness_db, create the federation. this creates the first federation member. CREATE FEDERATION section_federation(s_id RANGE BIGINT) -- connect to the federation member and deploy schema to the federation member USE FEDERATION section_federation(0) … GO CREATE TABLE section_row (…, section_id BIGINT NOT NULL) FEDERATED ON (s_id = section_id) CREATE TABLE section_fan (…, section_id BIGINT NOT NULL) FEDERATED ON (s_id = section_id) -- create a reference table to be replicated to all members CREATE TABLE teams (…) --When you want to scale out sections, connect to marchmadness_db and SPLIT the federation ALTER FEDERATION section_federation SPLIT AT(s_id=101) -- When you want to scale in., connect to marchmadness_db and MERGE the federation data ALTER FEDERATION section_federation MERGE AT (s_id=201) //Create command with filtering SqlCommand cm = newSqlCommand(“USE FEDERATION section_federation(s_id=55) ”+ ”WITH FILTERING=ON”) cm.ExecuteNoneQuery(); SqlCommand cm = newSqlCommand(“SELECT … FROM dbo.section_row WHERE …”) cm.ExecuteQuery(); …
EnzoSQL Shard Library Open source hybrid shard approach for SQL Azure and SQL Server Abstracts the physical storage Enterprise Library caching Task Parallel Library for fan out queries Round robin inserts Limitation of caching the shard directory Online repartitioning will present challenges
Entity Framework Support Coming soon Challenge of managing connections MARS currently not supported
The “No SQL” Gene Google BigTable, HBase/Hadoop, Hypertable Amazon Dynamo, Voldemort, Cassandra, Riak Common Characteristics Key Value store Runs on a large number of commodity machines Data is partitioned and replicated Consistency requirement is relaxed (because the CAP theorem, you can’t have Consistency, Availability, and Partitioning at the same time)
Questions? Links: Cihan’s blog: http://blogs.msdn.com/b/cbiyikoglu/ Enzo SQL Server and SQL Azure Library: http://enzosqlshard.codeplex.com/ Cloud Ninja Azure Federations sample : http://shard.codeplex.com/ SQL Azure Federations with Entity Framework: http://windowsazurecat.com/2011/09/sql-azure-federations-entity-framework-code-first/ PDC 2010 Presentation Lev Novik: http://channel9.msdn.com/events/PDC/PDC10/CS02 Contact: patrick@patrickmriley.net http://patrickmriley.net
References: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.ii.doc/opt/tlsset06.htm http://www.queplix.com/index.php?option=com_content&view=article&id=88 http://blogs.msdn.com/b/cbiyikoglu/archive/2010/10/30/building-scalable-database-solution-in-sql-azure-introducing-federation-in-sql-azure.aspx http://www.bluesyntax.net/files/EnzoFramework.pdf http://blogs.msdn.com/b/cbiyikoglu/archive/2011/01/18/sql-azure-federations-robust-connectivity-model-for-federated-data.aspx http://social.technet.microsoft.com/wiki/contents/articles/how-to-shard-with-sql-azure.aspx http://enzosqlshard.codeplex.com/ http://geekswithblogs.net/hroggero/Default.aspx http://horicky.blogspot.com/2009/11/nosql-patterns.html