• Login
• Become a member
• RSS
• Part of the TechTarget network

Microsoft is determined to enshrine Windows Azure and SQL Azure as the world’s flagship public-cloud Platform as a Service (PaaS) and relational database. Confronted by a continuous stream of new Infrastructure as a Service (IaaS) improvements to Amazon Web Services, the Windows Azure and SQL Azure teams have quickened their pace of adding new features and cutting service prices.

Managing SQL Azure Federations
Clicking the Databases tile and the Summary arrow of the database you want to manage in the Databases list opens the database’s Summary page, which includes a Query Usage (CPU) chart, Database Properties pane and Federations pane (see Figure 1). The sample federated database contains about 5 GB of event counter data from a live Windows Azure application.

Figure 1. This Summary page for a 5 GB federated database includes a link arrow at the lower right to open a management page for the named federation. Selecting the federation name enables the Drop Federation link. The summary page for all databases includes a New button (circled) to create a new federation root.

Creating a new SQL Azure Federation requires specifying its Name -- “WADFederation” for this example -- and Distribution Name, Id, representing an integer CounterId value, which ranges from 1 to 6 for the sample database and acts as the Federation Distribution Key. The current SQL Azure Federations preview supports only the Range Distribution Type, which is limited to int, bigint, uniqueidentifier or varbinary data types.

Clicking the Federations link arrow opens a Federation Members page with an icon for each member; the icon displays the percentage of capacity being used and available for each federation member (see Figure 2). By default, the database that you use to start the federation becomes its root member.

Figure 2. The Federation Members page displays an icon for each federation member, starting with the root federation member -- shown as “Low” here -- which contains Id values from -2,147,483,648 (the minimum value of the int data type) to 1.

Left-clicking an icon displays a menu with Overview, Query, Split and Resize items (in the dialog box in Figure 2). The Query item offers the following choices:

• New Query, which issues a USE FEDERATION [WADFederation]([Id = 2 WITH FILTERING = OFF,RESET instruction to select the federation member to query (2 for this example)
• Create Federated Table, which issues the same instruction as New Query and adds a template to define a new table (see Figure 3)
• Split, which enables dividing the selected member into two members at a specified Id value
• Resize, which can change the maximum size of the member database in 1 GB intervals

Figure 3. The Create Federated Table template includes a stub for defining the new table’s schema. Federation members need not have identical schemas, but all must use the same data type for the federation distribution key (Id for this example).

Using SQL Server 2012 Management Studio
SQL Server 2012 Management Studio (SSMS) is the first prerelease version to support SQL Azure Federations and it offers database developers and DBAs an alternative to the Management Portal’s Silverlight user interface. When you connect to a SQL Azure server with a federated database, the top-level Databases node gains a Federations subnode. Right-clicking the federation name node (WADFederation for this example) and choosing Connect to a Federation Member opens a dialog box in which you specify the federation member (see Figure 4).

Figure 4. SQL Server 2012 RC0’s Management Studio doesn’t display federation member databases, which are automatically named by a global unique identifier, or GUID, in its default Databases hierarchy. Instead, specify the Federation Distribution Value in the Connect to a Federation Member dialog to open the RootDatabaseName::FederationName member.

Executing a query against a federation member requires choosing Tools and then Options and setting the SQL Server Object Explorer, Scripting page’s Script for Database Engine Type property to SQL Azure Database. Microsoft principal architect George Huey’s open source SQL Azure Federation Data Migration Wizard is the simplest and fastest method of populating federated databases automatically (see Figure 5).

Figure 5. The SQL Azure Federation Data Migration Wizard uploads data from SQL Server 2008 R2 or SQL Server 2012 to the appropriate members of SQL Azure Federations automatically. The wizard uses the bcp utility to speed uploads with SQL Server’s native Tabular DataStream (TDS) protocol.

SQL Azure price changes
In December, Microsoft increased the maximum database size of SQL Azure Business Edition databases from 50 to 150 GB, while capping the monthly price at the 50-GB size’s $499.95 per month. This meant a 67% reduction in the cost of a 150 GB database. At the same time, Microsoft updated SQL Azure Federations from preview to production status applied a facelift to the SQL Azure Management Portal (formerly code-named Project Houston; see Figure 6), and enabled data-tier applications (DAC) export and import operations to and from Windows Azure binary large object (blob) storage.

Figure 6. Clicking the Manage button in the upgraded Windows Azure Management Portal’s Server group for a selected subscription opens the new SQL Azure landing page for the selected server. The new portal enables graphical management for SQL Azure Federations.

Then in February, the SQL Azure team announced more changes -- a new, smaller Web edition database priced at $4.99 a month for up to 100 MB of data and substantial across-the-board price reductions for SQL Azure databases, as shown in Table 1.

Table 1. Above lists the cost for each SQL Azure database a month and the cost savings of new pricing for Web Edition and Business Edition databases. The database size stretches from 100 MB to 150 GB.

Finer-grained pricing took the bite out of 9 GB or 10 GB transitions for both Web (up to 10 GB) and Business (10 GB to 150 GB) editions, as shown in Figure 7.

Figure 7. Comparison of cost per month of SQL Azure databases in maximum sizes from 100 MB to 150 GB with prices adjusted on Dec. 12, 2011 (old), and Feb. 14, 2012 (new), shows more gradual transitions between database sizes now.

Conclusion
Over its brief two-year lifetime, SQL Azure has grown from a maximum database size of 10 GB to 150 GB, gained more sophisticated management tools and has become substantially less costly to implement. If your organization needs a proven, highly available relational database in a multinational public cloud, give SQL Azure a test drive. The Windows Azure 90-Day Free Trial includes use of a 1 GB SQL Azure database at no charge for three months.

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.

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.