1 / 13

SQL Azure Federations

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

akina
Download Presentation

SQL Azure Federations

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Azure Federations Patrick Riley October 2011, KY .NET UG

  2. Data Virtualization

  3. Federated System

  4. SQL Azure Federations

  5. “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

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

  7. 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(); …

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

  9. Entity Framework Support Coming soon Challenge of managing connections MARS currently not supported

  10. 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)

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

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

More Related