EDITOR’S NOTE: The following is the first article in a two-part series on managing Microsoft’s sharding technology...
SQL Azure Federations with Transact-SQL (T-SQL). Part 1 focuses on using T-SQL to create federated tables and migrating data from on-premises SQL Server. The second part looks at the last step in the T-SQL management of SQL Azure Federations: splitting the federation root member table.
For more on SQL Azure
Read the basics on SQL Azure development, inside and out
SQL Azure Federations is Microsoft’s new cloud-based scalability feature. It enhances traditional partitioning by enabling database administrators (DBAs) and developers to use their Transact-SQL (T-SQL) management skills with “big data” and the new fan-out query tool to emulate MapReduce summarization and aggregation features.
Highly scalable NoSQL databases for big data analytics is a hot topic these days, but organizations can scale out and scale up traditional relational databases by horizontally partitioning rows to run on multiple server instances, a process also known as sharding. SQL Azure is a cloud-based implementation of customized SQL Server 2008 R2 clusters that run in Microsoft’s worldwide data center network. SQL Azure offers high availability with a 99.9% service-level agreement provided by triple data replication and eliminates capital investment in server hardware required to handle peak operational loads.
The SQL Azure service released Dec. 12 increased the maximum size of SQL Azure databases from 50 GB to 150 GB, introduced an enhanced sharding technology called SQL Azure Federations and signaled monthly operating cost reductions ranging from 45% to 95%, depending on database size. Federations makes it easier to redistribute and repartition your data and provides a routing layer that can handle these operations without application downtime.
How can DBAs and developers leverage their T-SQL management skills with SQL Azure and eliminate the routine provisioning and maintenance costs of on-premises database servers? The source of the data to be federated is a subset of close to 8 million Windows Azure table rows of diagnostic data from the six default event counters: Network Interface Bytes Sent/Sec and Bytes Received/Sec, ASP.NET Applications Requests/Sec, TCPv4 Connections Established, Memory Available Bytes and % Processor Time. The SQL Server 2008 R2 Service Pack 1 source table (WADPerfCounters) has a composite, clustered primary key consisting of PartitionKey and RowKey values. The SQL Azure destination tables are federated on an added CounterID value of 1 through 6, representing the six event counters. These tables add CounterID to their primary key because Federation Distribution Key values must be included.
T-SQL for creating federations, adding tables
SQL Server Management Studio (SSMS) 2012 supports writing queries with the new T-SQL [CREATE | ALTER | DROP | USE] federations keywords. You can download a trial version of SQL Server 2012 and the Express version of SSMS 2012 only here.
To create a new SQL Azure Federation in the database that contains the table you want to federate, select it in SSMS’ Available Databases list, open a new Query Editor window and where DataType is int, bigint, uniqueidentifier or varbinary(n) type, the following:
CREATE FEDERATION FederationName (DistributionKeyName DataType RANGE)
Then click Execute. For example, to create a Federation named WADDiagnostics with Id (from CounterId) as the Distribution Key Name, type this:
CREATE FEDERATION WADFederation (ID int RANGE)
The RANGE keyword indicates that the initial table will contain all ID values.
When you refresh the Federations node, the new WADFederation node appears, as shown in Figure 1.
Figure 1. SQL Server Management Studio 2012 Express and higher editions support writing T-SQL queries against SQL Azure Federations.
Adding a table to a Federation requires appending the FEDERATED ON (DistributionKeyName = SourceColumnName) clause to the CREATE TABLE statement. For example, to create an initial WADPerfCounters table into which you can load data, double-click the WADFederation node to add the AzureDiagnostics::WADFederation federated database node, select it in the Available Databases list, open a new query and type the following:
CREATE TABLE [WADPerfCounters](
[PartitionKey] [bigint] NOT NULL,
[RowKey] [varchar](100) NOT NULL,
[Timestamp] [datetime2](7) NOT NULL,
[EventTickCount] [bigint] NOT NULL,
[DeploymentId] [varchar](50) NOT NULL,
[Role] [varchar](20) NOT NULL,
[RoleInstance] [varchar](20) NOT NULL,
[CounterName] [varchar](100) NOT NULL,
[CounterValue] [decimal](16,8) NOT NULL,
[CounterId] [int] NOT NULL,
CONSTRAINT [PK_WADPerfCounters] PRIMARY KEY CLUSTERED
( [PartitionKey] ASC,
[CounterId] ASC ))
FEDERATED ON (Id = CounterID)
Then execute. Refresh the lower Tables list to display the first federated table (see Figure 2).
Figure 2. The CREATE TABLE instruction for SQL Azure Federations requires use of the FEDERATED ON keywords.
Adding Rows with Data Migration Wizard
Microsoft principal architect George Huey’s SQL Azure Federation Data Migration Wizard automates migrating data from SQL Server 2008 R2 to SQL Azure federated tables with the Bulk Copy Protocol (BCP). Download SQLAzureFedMW v1.3 from CodePlex, extract the files to a folder of your choice, then run SQLAzureFedMW.exe to open the Select Servers dialog’s Data Source page.
If your source and destination databases use the char(n) or varchar(n) data type for string data, rather than nchar(n) or nvarchar(n), open the SQLAzureFedMW.exe.config file in Visual Studio or a text editor and change the two instances of the –n parameter of the AppSettings’ BCPArgsIn and BCPArgsOut keys to –c, as shown in Figure 3.
Figure 3. Make the corrections encircled above to specified ANSI character encoding for char(n) and varchar(n) data types for uploading by BCP.
Click Connect to Server and select your SQL Server 2008 R2 source database and table (see Figure 4).
Figure 4. Select the source database and target tables in SQLAzureFedMW’s Select Servers dialog box.
Click the SQL Azure Federation Target tab and Connect to Server to open the Connect to Server dialog. Replace Server with your server name in two places, type your password and replace Federated Root with your database name (see Figure 5).
Figure 5. Connecting to the server requires typing the assigned database name, wariw3xke0 for this example as the Server name prefix and User name suffix.
Click Connect and verify that the Federation and Members values are correct (see Figure 6). The ID (Min to Max) item in the Select Federation Members list indicates that all rows will be uploaded to the Federation Root Member’s table.
Figure 6. Select the target federation and members in the Select Servers dialog when uploading data to individual or multiple federation members.
Click Next, and then Yes when asked if you’re ready to download data from the source database to a local set of files. Click Next when the download completes (see Figure 7).
Figure 7. The View Results dialog box displays a row for each 1,000-row page to be uploaded in sequence to SQL Azure by BCP.
Click Next to start the upload process and Yes to confirm you’re ready to upload data. The View Results page shows upload progress with a series of alternating red and blue bullets (see Figure 8).
Figure 8. Rows of alternating blue and red dots are SQLAzureFedMW’s upload progress indicator.
When the upload completes, the View Results page reports the total number of rows uploaded and the uploading time.
ABOUT THE AUTHOR
Roger Jennings is a.NET developer and writer, the principal consultant of OakLeaf Systems and curator of the OakLeaf Systems blog. He's the author of more than 30 books on the Windows Azure Platform, Microsoft operating systems, databases, .NET data access, Web services and InfoPath 2003.