For those of you who have been following along with this series of posts, I have been looking at how Azure SQL Database Elastic Scale can be used to Horizontally Scale a Payroll System using PowerShell. If you want to review the Business Case, check out the first post, which also contains a discussion on Horizontally Scaling Databases and Sharding. In the second post, we set up the Shard Map Manager Database and talked about the tables that were created in this database. In this post, I will go into detail about creating the Sharded Databases and have a look at how the Sharded Databases can be queried using a C# Console Application.
The PowerShell Runbook Part 2
At the end of the last post, we had used our PowerShell Runbook to set up the Shard Map Manager Database. The Shard Map Manager Database contains information about the various Shards and how those Sharded Databases should be queried. The next part of the Runbook creates the 4 Sharded databases. The first Sharded Database will contain EmployeeID's 1-25; the second will contain EmployeeID's 26-50; the third will contain EmployeeID's 51-75, and the final Sharded Database will contain EmployeeID's 76-100. The next part of the PowerShell Runbook can be seen below.
Create Sharded Database
This part of the Runbook Creates the Sharded Database PayrollEmployee_1_25 and the 4 tables that control the Sharding in the Sharded Database. As you can see, these tables are similar to the tables that are included in the Shard Map Manager Database, except that they have the word "Local" at the end of the name instead of "Global". The 4 tables are explained briefly below:
- [__ShardManagement].[ShardMapManagerLocal] - Stores version information about the Elastic Scale Database. The data in this table is identical to the data in the [__ShardManagement].[ShardMapManagerGlobal] table in the Shard Map Manager Database.
- [__ShardManagement].[ShardMappingsLocal] - Stores information about the Shards that get created such as the minimum and maximum values for each Shard. This table is currently empty.
- [__ShardManagement].[ShardMapsLocal] - Stores the ShardMapId and the name of the Shard Map that has been created. All of the rest of the information that we create in the Shard Map Manager Database will link back to this ShardMapId.
- [__ShardManagement].[ShardsLocal] - Stores information about the Sharded database that contains the Shard. The data in this table is very similar to the data in the[__ShardManagement].[ShardsGlobal] table in the Shard Map Manager database.
The next part of the PowerShell Runbook creates the Shard Maps in the Sharded Database. As can be seen below, the minimum value for the Shard is set to 1, and the maximum value for the Shard is set to 25. A custom PowerShell function called Populate-SqlDatabase is also called which creates the Employee, Payroll and PayrollDetails tables. In a production system, you would probably want to do this step as an SSIS package after you have set up the Sharded Databases. I am doing it here so that everything can be set up using a single Runbook in case you want to try this out on your own instance.
Create Shard Maps in the Sharded Database
In the Azure SQL Database, the above part of the PowerShell Runbook adds a record to the [__ShardManagement].[ShardMappingsGlobal] table in the Shard Map Manager Database and the [__ShardManagement].[ShardMappingsLocal] in the Sharded Database PayrollEmployee_1_25. The data from the [__ShardManagement].[ShardMappingsLocal] table can be seen below:
[__ShardManagement].[ShardMappingsLocal] Data
The rest of the PowerShell Runbook re-runs the above code to create the remaining 3 Sharded Databases: PayrollEmployee_26_50, PayrollEmployee_51_75 and PayrollEmployee_76_100. Again, this code could be made much more modular, but I have kept it very "un-modular" for demonstration purposes. When the entire Runbook has completed, [__ShardManagement].[ShardMappingsGlobal] table in the Shard Map Manager Database has the following 4 records:
[__ShardManagement].[ShardMappingsGlobal] Data
Notice the minimum and maximum values for each Shard. When we look at the [__ShardManagement].[ShardsGlobal] table in the Shard Map Manager Database, we can see the names of the Sharded Databases.
[__ShardManagement].[ShardsGlobal] Data
The Console Application to Query the Shards
The last thing we'll look at is how you would query the Sharded Databases. Unfortunately, there is no way to demonstrate how to query the databases in Enterprise Manager so I had to write a Console Application in C#. If you want to get the Console Application to run on your environment, you would just need to change the ServerName, UserName and Password in the App.config file so that they match your Azure SQL Database Environment. The first thing that the Console Application does is to use the ShardMapManagerFactory to get a reference to the ShardMapManager using the connection string to the Shard Map Manager database (see 1 below). The code then uses the TryGetRangeShardMap method to get all of the Mappings for the Shards (see 2 below).
ShardMapManagerFactory and ShardMapManager
Before the Query is run, all of the Shards are retrieved from the Mappings (see 3 below). These Shards are then used to instantiate the MultiShardConnection object (see 4 below). From here, everything proceeds as it would normally with ADO.NET, although instead of Command and DataReader objects we use MultiShardCommand and MultiShardDataReader objects (see 5 and 6 below).
Establishing the Connection and Querying the Shards
When we run the Console Application and type in 30 for the EmployeeID, we can see that the correct information is returned from the PayrollEmployee_26_50 Database Shard. We also only specified a connection to the Shard Map Manager Database.
Running the Console Application
I hope that gives a bit of insight into how you can use Azure SQL Database Elastic Scale in a real world application. If you are interested to read more about this topic, check out the work from Sidney Higa at Microsoft, which a lot of this series is based upon. Links to his work are in the references below. Also, feel free to download the Runbook, PowerShell Modules and the Console Application using the links below.
References
http://azure.microsoft.com/en-us/documentation/articles/sql-database-get...
http://blogs.technet.com/b/cbernier/archive/2014/04/08/microsoft-azure-a...
http://azure.microsoft.com/en-us/documentation/articles/sql-database-ela...
https://gallery.technet.microsoft.com/scriptcenter/Elastic-Scale-Shard-c...
