February 2012

Volume 27 Number 02

SQL Azure - Scaling Out with SQL Azure Federation

By George Huey | February 2012

Many companies have been unable to meet user demand because they could not scale out. The theory of scaling out—adding servers to accommodate increasing workload and traffic—isn’t hard to understand, but the implementation can be complicated and expensive. Over the past several years, the concept of elastic cloud computing (the ability to scale out as well as down) to meet user demand has become more realistic and affordable as companies such as Microsoft and Amazon have started to provide the tools that architects and developers need to keep their companies thriving in a competitive marketplace.

In this article, I introduce you to one of the new tools Microsoft has made available to database architects and developers for scaling out a database in the cloud: SQL Azure federation. If you haven’t had a chance to work with Azure or SQL Azure, check out the free three-month trial that Microsoft offers at https://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200056234.

When Microsoft released SQL Azure in 2009, I immediately wanted to move some of my databases to it. The first problem I ran into was how to get a database into SQL Azure. At the time, there wasn’t a good way to migrate database objects and data from SQL Server to SQL Azure. I ended up writing a program to help automate the migration process, which I named SQL Azure Migration Wizard (SQLAzureMW) and released to CodePlex (https://aka.ms/sqlazuremigration). Using this tool I successfully migrated my database to SQL Azure, and using Visual Studio I was able to deploy my application to four worker roles in Azure. I quickly discovered that the amount of data I was collecting would exceed the 10-GB size limit (which has since been increased to 150 GB) and that I needed to add retry logic to handle SQL Azure throttling (high I/O and CPU use as well as a large number of concurrent connections to a single database). To address these issues and achieve the throughput I required, I modified the application to add retry logic and re-architected the database to allow sharding of the data. Sharding, also known as federating, is the process of breaking a large database into many smaller databases and distributing these databases across different hardware devices to take advantage of greater physical resources. The end configuration for my application was 125 Window Azure worker roles and 64 x 1 GB SQL Azure databases. I could now process in four hours what used to take a week, and I realized around a 40% cost reduction because I was able to shut down my application when I didn’t need to collect information.

SQL Azure Federation

SQL Azure federation provides tools that allow developers to scale out (by sharding) in SQL Azure. Here are some of the benefits of a sharded database:

• Taking advantage of greater resources within the cloud on demand.
• Allowing customers to have their own database, to share databases or to access many databases.
• Reducing the exposure of a single point of failure.
• Benefiting from the lower costs of using cloud resources on an as-needed basis and releasing them when done.
• Reducing I/O bottlenecks and SQL Azure throttling.

Figure 1shows a high-level view of SQL Azure federation. The main parts are the federation root database, the federation and the members.

Figure 1 SQL Azure federation

Federation Root Database

The federation root database is a SQL Azure database that contains metadata about the federations. It’s the first thing you need to create when setting up your own federations. You can create the federation database through the SQL Azure database management portal or by using the following T-SQL script:

CREATE DATABASE [fedRoot] COLLATE French_CI_AS (MAXSIZE = 100 GB, EDITION = 'business')

Any federations you create will inherit the properties of the root database. For example, all federations created with the preceding T-SQL script will be a maximum size of 100 GB, relate to the business edition and have a collation of French_CI_AS. That said, because all federation members are individual databases, you can use the ALTER DATABASE command to change their characteristics. If you then perform a split action on the modified federation member, the two new federation members resulting from the split inherit their parent’s characteristics.

Federation

The federation is where you define the data type (e.g., Customer ID, Product ID) you’ll shard on. As with creating the root database, you can create a federation through the SQL Azure database management portal, with SQLAzureMW or by using this T-SQL script while connected to your root database:

CREATE FEDERATION <FederationName>(<DistributionKeyName> <DistributionType> RANGE)

The CREATE FEDERATION process creates a new database (based on the parent database, which in this case is fedRoot, shown in the preceding section). In this example, <FederationName> is the name of the federation (not the name of the physical database, which is a System-GUID). <DistributionKeyName> is the name for the distribution key, and <DistributionType> is the distribution data type that data will be sharded on. The valid distribution data types are int, bigint, uniqueidentifier and varbinary (up to 900).

Federation Member

The federation member is the shard (i.e., the database containing a specific range of information). By default, a created federation contains one federation member with a range of low to high (containing all the data). To create new federation members, you can use the SQL Azure database management portal or the following T-SQL script:

USE FEDERATION ROOT WITH RESET
GO
ALTER FEDERATION <FederationName> SPLIT AT (<DistributionKeyName>=<splitpoint>)

The SPLIT AT command tells SQL Azure federation to find the member (database) that contains the <splitpoint>value and to split that federation member at that point. Figure 2 shows an example of how SPLIT AT works.

Figure 2 SPLIT AT

As you can see in Figure 2, ALTER FEDERATION splits the member (5000000, high) into two members (5000000, 9000000) and (9000000, high). You don’t even have to take your application offline to do a split (i.e., scale out). SQL Azure federations perform the split in real time and without any loss of data. One thing to remember when reading the member ranges is that the low value is inclusive, while the high value is “up to but not including.”  So the customer data for ID 9000000 would reside in the federation member whose range is (900000, high).

When you first deploy your database schema to SQL Azure federation, I recommend that you deploy your database schema first (i.e., don’t perform a split operation). Once you have the schema deployed to your federation member, you can work on splitting your federation member and migrating your data. That said, tooling for schema deployment is in the works and should greatly simplify the deployment process, allowing you to create as many empty federation members as you want and then perform parallel database schema deployment. 

Migrating Database Schema from SQL Server or SQL Azure to SQL Azure Federation

If you have an existing database that wasn’t designed with sharding in mind, you’ll probably need to make some major architectural modifications to both your database and your application before you can think of migrating to SQL Azure federation. It’s possible that your database won’t work in a sharded environment. Here are just a few factors you need to consider when thinking about sharding your database:

• In SQL Azure, you can’t do cross-database joins. Each SQL Azure federation member is a SQL Azure database and thus lives by all the SQL Azure rules. These databases (members) are totally autonomous, and there is no cross-referential integrity between them.
• What are you going to shard your data on? Remember that you have four possible data types: int, bigint, uniqueidentifier and varbinary.
• Is your primary ID an Identity column?  SQL Azure federations don’t support Identity on a column, so you’ll have to find another way to make your column unique.
• If you shard your data into X number of federation members and then decide you need to make a schema change, you’ll have to modify the schema on each federation member database. Once split, a federation member database becomes a unique database.

To avoid the complications of dealing with such issues (which are just a few of the potential concerns that can come up in migration), the first database I migrated to SQL Azure federation had already been designed for sharding. I started by determining how to get my database schema to SQL Azure federation. Unfortunately, no tool exists that migrates the database schema to SQL Azure federation and figures out what table/column you want to federate on. Manual effort is required. I used SQLAzureMW to create my baseline T-SQL script, but you can use SQL Server Management Studio if you want. Once I had my T-SQL script, I modified it to reflect the table and column I wanted to federate on. Figure 3 shows what an appropriate T-SQL script would look like. 

Figure 3 Example T-SQL for creating a federation and a federated table

CREATE FEDERATION CustomerFederation(cust_id BIGINT RANGE)
GO
USE FEDERATION CustomerFederation(cust_id=1) WITH RESET, FILTERING=OFF
GO
CREATE TABLE [dbo].[Customer](
      [CustomerID] [bigint] NOT NULL,
      [Title] [nvarchar](8) NULL,
      [FirstName] [nvarchar](50) NOT NULL,
      [MiddleName] [nvarchar](50) NULL,
      [LastName] [nvarchar](50) NOT NULL,
      [Suffix] [nvarchar](10) NULL,
      [CompanyName] [nvarchar](128) NULL,
      [EmailAddress] [nvarchar](50) NULL,
      [Phone] [nvarchar](25) NULL,
      [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED
(
      [CustomerID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)FEDERATED ON (cust_id=CustomerID)
GO

For clarity, I included the CREATE FEDERATION and USE FEDERATION commands. The most important part of the script in Figure 3 is the distribution key name cust_id. The distribution key name tells SQL Azure federation what you’re federating on and which federation member you want to work with. Here are the USE FEDERATION formats:

USE FEDERATION ROOT WITH RESET
USE FEDERATION <FederationName> (<DistributionKeyName> = value) WITH RESET, FILTERING=<OFF|ON>

Notice the two statements. USE FEDERATION ROOT WITH RESET tells the connection that you want to set your source database to the federation root database. USE FEDERATION and what follows it sets the source database to the federation member that contains the distribution key name value. <FederationName**>** is the name of the federation you want to work with. <DistributionKeyName> is the distribution key name as defined in the CREATE FEDERATION statement. Value tells SQL Azure federation which federation member you want to work with. This value must be the same type (int, bigint, uniqueidentifier and varbinary) as defined in the CREATE FEDERATION statement. WITH RESET is a required keyword that makes the connection reset explicit. It’s there to remind you that things like temp tables and connection settings are lost every time you use the USE FEDERATION command. FILTERING=<OFF|ON> sets the scope of the connection. When FILTERING is set to OFF, the scope of the connection is set to the federation member’s full range. For example, if a federation member’s data for cust_id is between 500 and 1000 and you connect with FILTERING set to OFF, the working set of data will be all the data in that range. When FILTERING is set to ON, the scope of the connection is set to the federation value. For example, if the filtering value is set to cust_id=25, the query processer returns only the cust_id information that matches 25. FILTERING works only for federated tables. If you have a table that isn’t federated (e.g., a pick list table), all the data is returned.

In the USE FEDERATION code in Figure 3, I put cust_id=1. The USE command told SQL Azure that I wanted to use the federation member that contained the customer ID value of 1. At this point, I could have used any valid bigint number since I have only one federation member.

To create a table that can be federated, you need to add the following argument to the end of the CREATE TABLE statement in Figure 3:

FEDERATED ON (<DistributionKeyName>=<ColumnName>)

FEDERATED ON tells the CREATE TABLE statement that you want to federate the table and identify the column name to federate on. In Figure 3 my FEDERATED ON statement looked like this:

FEDERATED ON (cust_id=CustomerID)

Cust_id is the original distribution key name I used when I created the federation. CustomerID is the column name I want to federate on. I added this information for each table in my database that I wanted to federate on. Once this manual work was done, I just needed to run that script while connected to the federation root. I saved the T-SQL script to a file and used SQLAzureMW. You could also use SQL Server Management Studio.

For more information on USE FEDERATION, see https://msdn.microsoft.com/en-us/library/windowsazure/hh597457.aspx.

Migrating Data from a Single Database to Many Databases

Once you set up your federation root database, modify the schema and migrate it to a single federation member, you next need to decide whether to migrate your data to the single federation member or to create all the federation members you plan on using and then migrate the data. If your database is smaller than the maximum database size allowed by SQL Azure, just migrate the data from your original database to your new federated database. You can use SQLAzureMW, selecting the tables you want to migrate and then selecting the advanced option Data Only.

If your database is larger than the maximum size database allowed by SQL Azure, you can shard  your data at the source and then upload the sharded data to the correct federation member. This process isn’t as easy as it sounds. Besides being tedious, the process involves figuring out what federation member contains what data range, finding the real database name for a federation member and sharding the data.

Figure 4shows how you can find what federation member exists in a federation and what the data range is for each member.

Figure 4 Listing the federation member and the data range

SELECT fed.federation_id
       , fed.name
       , fmd.distribution_name
       , fmd.member_id, range_low
       , range_high
       , typ.name
  FROM sys.federations fed
  JOIN sys.Federation_distributions dis ON dis.federation_id = fed.federation_id
  JOIN sys.federation_member_distributions fmd ON fmd.federation_id = fed.federation_id
  JOIN sys.types typ ON typ.system_type_id = dis.system_type_id
 ORDER BY fed.name, range_low

Once you have all the federation members listed, you need to determine the database name of each federation member. To find the physical names of the members, I looped through the result set from the SQL query in Figure 4 using the range_low value in the USE FEDERATION statement:

USE FEDERATION CustomerFederation(cust_id=<range_low>) WITH RESET, FILTERING=OFF
GO
SELECT DB_NAME()
GO

Once I connected to the federation member, I used SELECT DB_NAME() to get the physical name.

The easiest way I found to shard data is to use Bulk Copy Program (BCP) with a SELECT statement and a WHERE clause that has the range of data I’m interested in. If you have many tables and many shards, this process can quickly become time consuming.

Finding what federation member contained what data range by using the SQL query in Figure 4 and then using SELECT db_name() was both tedious and time-consuming, and something I wanted to avoid doing for each federation. To automate this process, I created a tool named SQL Azure Federation Data Migration Wizard (SQLAzureFedMW), which you can find at https://sqlazuremw.codeplex.com/. Using SQLAzureFedMW, you can pick the source database and the target SQL Azure federation members, as shown in Figure 5.

Figure 5 Picking the source database and target federation members with SQLAzureFedMW

SQLAzureFedMW lets you select the source database that you want to shard and migrate via the Data Source tab. You then select the SQL Azure Federation Target tab and click Connect to Server to connect to your SQL Azure federation root database. A list of your federations will display. Just click on a federation to see a list of the federation members. You can select the tables you want to migrate and the federation members you want the data migrated to.

NOTE  SQLAzureFedMW requires source table names to match destination table names. If SQLAzureFedMW doesn’t find a destination table that matches the source table, that table is ignored. Also, the destination table schema must match the source table schema.

Once you select the tables for data transfer and the destination federation members, click Next. SQLAzureFedMW then does the following:

• Shards the source data using BCP and displays the BCP output results for your information.
• Shows the BCP commands used to shard the data for your information.
• Shows the BCP commands that will be used to upload the data to SQL Azure federation.

Figure 6 shows the BCP results and commands that SQLAzureFedMW generates. Once the data is sharded, you can upload it to your federation members by clicking Next.

Figure 6 BCP command summary in SQLAzureFedMW

When you click Next, SQLAzureFedMW kicks off four parallel BCP upload processes by default. You can control the number of parallel BCP upload processes (as well as BCP batch size and chunking size) by modifying SQLAzureFedMW.exe.config. A running display like the one in Figure 7 shows the result of the BCP uploads. When the processes are finished, your data will be running in the correct shards.

Figure 7 BCP upload results in SQLAzureFedMW

Scaling Out

There are three ways to scale out: SQL Azure database management portal, SQL script and SQLAzureMW.

SQL Azure Database Management Portal

With the SQL Azure database management portal (shown in Figure 8), you can manage your federation members and scale out your federation as the need arises. You must be connected to your federation root to access the federation management options.

Figure 8 SQL Azure database management portal

Once you’re connected to the federation root, you identify which federation you want to work with. As you can see in Figure 8, the database management portal shows the federation members and provides information (free space, used space) on each one so that you can determine whether you need to split a member. To split a member, click the member you want to split. A small dialog box displays, in which you have several options: Overview, Query, Split and Resize. Select Split, and enter the value you want to split the federation member on. Because this is an asynchronous process, the user interface will be updated to show the new federation member when the sharding process is completed.

SQL Script

You can manually scale out a federation by using the ALTER FEDERATION command:

ALTER FEDERATION <FederationName> SPLIT AT (<DistributionKeyName>=<value>)

In ALTER FEDERATION, you specify the federation name you want to work with and then enter the distribution key name and value on which you want to start a new federation member. Because this process is also asynchronous, you can’t use ALTER FEDERATION again until the current ALTER FEDERATION process completes. To figure out what current processes are running, execute the following query:

SELECT ops.percent_complete FROM sys.dm_federation_operations ops
  JOIN sys.federations fed on ops.federation_id = fed.federation_id
 WHERE fed.name = '<FederationName>'

When the ALTER FEDERATION operation is complete, the job record is removed and the preceding query returns a null.

SQLAzureMW

The third way to scale out your federation is by using SQLAzureMW, which has a maintenance option (shown in Figure 9) that allows users to manage their target server. Under Server Maintenance, select Maintenance and click Next. You’ll be prompted for your server connection. You also must select the target server type: SQL Server, SQL Azure or SQL Azure Federation.

Figure 9 SQL Azure Migration Wizard (SQLAzureMW)

When working with SQL Azure federation, be sure that you have SQL Azure Federation selected and that you specify the federation root database, as shown in Figure 10.

Figure 10 Connection to SQL Azure federation in SQLAzureMW

Once SQLAzureMW connects to the federation root, all the federations appear in the left pane and all the federation members of the selected federation are listed in the right pane, as you can see (behind the dialog box) in Figure 11.

Figure 11 Federation member split in SQLAzureMW

To create a new federation or add a new federation member, click Create. To create a new federation member, select the Create Splitpoint tab and enter a splitpoint value. SQLAzureMW will then display a status bar and wait (performing the ALTER FEDERATION query shown earlier) for SQL Azure federation to finish the split process.

Of the three scaling out processes just described, SQLAzureMW provides the fullest set of functionality for federation creation, database migration, and scaling out or scaling down. I recommend that you start with this tool. The only drawback to using SQLAzureMW is that it is an open source tool and is not supported by Microsoft. The second tool of choice is the SQL Azure database management portal. This portal is the main tool of choice when you need more detailed information (such as query performance, collation, active connections, maximum size, space used) on the federated database.

Scaling Down

I can now scale out to meet user demands, but I can’t forget that I still need to deal with an area that’s easy to overlook: scaling down in periods of low user demand because I no longer need the resources or expense of having an extra database floating around. I wish I could tell you that scaling down in SQL Azure federation is easier than scaling up, but it’s not. Scaling down your federation members also requires a lot of manual work. SQL Azure federation doesn’t support scaling down (merge process) in version 1, but Microsoft is working on it for future releases. For now, dealing with the merge process manually can be fairly easy (though monotonous) or incredibly difficult. Recall that when you split a federation member the resulting two federation members have exactly the same schema (mirror copies of each other except for the federated data) and are totally autonomous. This means that you can modify the table schema (or any database object) on one federation member without modifying the other federation members. You now have one federation member that doesn’t match the other federation members. My point here is that you need to have a very good reason before changing the table schema (or modifying other database objects such as stored procedures) on one federation member and not the others. When modifying table schema, you have to consider how you’ll merge two different table structures, thus making the scale down (merge) process a lot more complex.

If you don’t make schema changes to individual federation members, the merge process is less complex but still requires a lot of work (at least it did at the time I wrote this article). Here are the important points to consider when scaling down your federation members:

• To avoid losing any data, take offline the part of the application that accesses the federation member you want to drop. It would be easier to take the entire application offline, but you would be cutting service to users who aren’t on the specific federation you’re removing.
• At this time, when you drop (merge) a federation member, SQL Azure federation drops the federation member and moves the range values to the lower or upper federation member, but the actual data in the federation member being dropped will be deleted. So you must back up your data before you use ALTER FEDERATION to DROP a federation member. I used SQLAzureMW to connect to my SQL Azure federation root database and then selected the federation, federation member and the tables I wanted to back up. I saved the generated script for the later upload process.

Once your application is offline (for that specific federation member) and your data is backed up, you can drop a federation member in two ways: using either a T-SQL command or SQLAzureMW.

T-SQL Command

Let’s first look at the ALTER FEDERATION command in Figure 12.

ALTER FEDERATION CustomerFederation DROP AT (HIGH cust_id = 5000000)

Figure 12 Scaling down

The command has the following format:

ALTER FEDERATION <FederationName> DROP AT ([LOW|HIGH] <DistributionKeyName> = <value>)

In the ALTER FEDERATION command, you specify the federation name you want to work with. Then you enter your distribution key name and the LOW or HIGH value of the federation member you want to drop. In Figure 12, in the before row, I wanted to drop the federation member that had the range (low=5000000, high=9000000). I also had to decide if I wanted to move range (5000000, 9000000) to the (1000000, 5000000) range on the left or to the (9000000, high) on the right. If you want to move a federation member to the left, in the ALTER FEDERATION command, you specify HIGH and the low value of the federation member you want to drop. If you want to move the federation member to the right, you specify LOW and use the high value in the ALTER FEDERATION command. Keep in mind that the only valid values that can be entered are the low and high value in the range. You can’t enter a number in between.

This is an asynchronous process and you can’t do another ALTER FEDERATION command until the current one is finished. You can figure out what current processes are running by executing the following query:

SELECT ops.percent_complete FROM sys.dm_federation_operations ops
  JOIN sys.federations fed on ops.federation_id = fed.federation_id
 WHERE fed.name = '<FederationName>'

When the ALTER FEDERATION operation is complete, the job record is removed and the above query returns a null.

SQL Azure Migration Wizard

I used SQLAzureMW to back up selected tables to my local machine. Then, as shown in Figure 13, I selected the federation member I wanted to drop and selected to have it dropped on the low range.

Figure 13 Dropping a federation member in SQLAzureMW

Once the federation member was dropped,  I uploaded the backed-up data to the new federation member. Once again, I used SQLAzureMW to back up my federation member (saved generated script), dropped the federation member and uploaded the data to the new federation member, as shown in Figure 14.

Figure 14 Uploading data to federation member in SQLAzureMW

With these processes in mind, I refactored my application to take various ranges offline and leave other ranges online. I did this by adding a range metadata table to my root federation database. In this table, I listed the current ranges I wanted to take offline and had my application check whenever a specific user tried to log in; or if the application hit a database error, it would go back to the root and see if I had taken that specific federation member offline. If so, the user would receive a message saying that the service was currently offline for database maintenance.

Next I needed to back up the data. I didn’t really need to back up my pick list tables or other static tables since my other federation members had the same tables and data. I did need a process that would enable me to back up data from specific tables. To do this, I used SQLAzureMW to back up selected tables to my local file system and saved the generated SQL scripts so I could upload the data after I had dropped the federation member that was no longer required. Once I had backed up the data, I used SQLAzureMW to drop the federation member that was no longer needed, as in Figure 13. After the federation member had been successfully dropped, I used SQLAzureMW to upload the data into the federation member that now contained my old range plus its original range, as in Figure 14.

After SQLAzureMW finished the upload process (and I verified that everything went well), I remove the offline record from my federation status table in the root database, thus signifying that the specified range was back online.

That completed the merge process for me. Your efforts at scaling down might be more complicated. For example, what would you do with unfederated tables on which your application does inserts, updates and deletes?  My point is that you need to carefully consider the scaling down process before you take your application live.

One last comment about scaling down:  Remember that SQL Azure charges by the size of the database. For example, say that you have two 10-GB federation members. You would be paying $199.98 per month total for both federation members. If you decide that you wanted to scale down to one 20-GB database, you would still be paying $199.98 per month and would lose the benefits that federating your database brings.

Conclusion

For years, developers have been working with commodity processors for scaling out the UI and business logic layers while buying enterprise-class database servers to scale up to meet database processing requirements. In moving from on-premise to elastic cloud computing, corporations are looking to have “just enough” resources to meet user demand; that is, to keep their costs to a minimum, they want the ability to add more resources (scale out) when user demand increases and to release these resources (scale down) when user demand decreases. Using Azure and SQL Azure federation to scale applications out and down works well, but they aren’t a magic bullet. You’ll still need to do a lot of work on your databases to prepare them for SQL Azure federation.

As I mentioned earlier, scaling out your database requires an architectural design that allows for data sharding, and modifying an already existing database to migrate to a sharding environment isn’t a simple task and might not be the right solution for you. But for data that can be sharded—or new databases that you create with federating in mind—SQL Azure federation provides a great foundation for scaling out and saves a lot of setup time. As SQL Azure federation matures and adds more functionality (such as scaling down), it  will allow you to concentrate more on your application and less on the infrastructure processes.

If you haven’t had a chance to work with Azure or SQL Azure, check out the free three-month trial that Microsoft offers at https://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200056234.

George Huey is a principal architect for the Developer & Platform Evangelism Group at Microsoft. George works with companies to help them understand new and emerging technologies and how these technologies can be applied to solve their business problems. He is also the author of the SQLAzureMW and SQLAzureFedMW tools.

A special thank you goes to technical expert  Cihan Biyikoglu  for reviewing this article.