.\GetMappings.ps1 -UserName 'mysqluser' -Password 'MySqlPassw0rd' -ShardMapManagerServerName 'abcdefghij.database.windows.net' -ShardMapManagerDatabaseName 'MyShardMapManagerDB' -ShardMapName 'CustomerIdShardMap' .\GetShards.ps1 -UserName 'mysqluser' -Password 'MySqlPassw0rd' -ShardMapManagerServerName 'abcdefghij.database.windows.net' -ShardMapManagerDatabaseName 'MyShardMapManagerDB' -ShardMapName 'CustomerIdShardMap'
.\GetMappings.ps1 -UserName 'mysqluser' -Password 'MySqlPassw0rd' -ShardMapManagerServerName 'abcdefghij.database.windows.net' -ShardMapManagerDatabaseName 'MyShardMapManagerDB' -ShardMapName 'CustomerIdShardMap' .\GetShards.ps1 -UserName 'mysqluser' -Password 'MySqlPassw0rd' -ShardMapManagerServerName 'abcdefghij.database.windows.net' -ShardMapManagerDatabaseName 'MyShardMapManagerDB' -ShardMapName 'CustomerIdShardMap'
Elastic database tools in Azure SQL DB let you build and manage applications that use sharding patterns to scale-out across many databases. The Powershell scripts here allow you to easily examine the contents of Shard Maps that contain metadata about your sharded environment, without having to write c# code.
For more information about Elastic database tools, please see the following:
Requirements
Before running these scripts, you need to have an existing sharded setup of databases, with the shard mappings already defined in the shard map manager.
For execution of this script, you need to have following information about your shard map setup:
User Name
Password
Server name on which Shard Map Manager database is present
Shard Map Manager database name
Shard Map Name
GetMappings.ps1
For a given sharding setup, this script outputs the range or list mapping of the shards.
Examples
.\GetMappings.ps1 -UserName 'mysqluser' -Password 'MySqlPassw0rd' -ShardMapManagerServerName 'abcdefghij.database.windows.net' -ShardMapManagerDatabaseName 'MyShardMapManagerDB' -ShardMapName 'CustomerIdShardMap’
GetShards.ps1
For a given sharding setup, this script outputs the list of shards defined in a particular shard map.
Examples
.\GetShards.ps1 -UserName 'mysqluser' -Password 'MySqlPassw0rd' -ShardMapManagerServerName 'abcdefghij.database.windows.net' -ShardMapManagerDatabaseName 'MyShardMapManagerDB' -ShardMapName 'CustomerIdShardMap’
Additional Powershell Capabilities
The scripts included here rely on a Powershell library ShardManagement.psm1 which includes several other functions supporting shard map management, allowing you to manipulate shard maps without writing c# code. Some of the common scenarios are:
Creating a Shard Map Manager and getting a reference to it.
# Create Shard Map Manager
New-ShardMapManager -UserName '<user_name>' -Password '<password>' -SqlServerName '<server_name>' -SqlDatabaseName '<smm_db_name>'
# Try to get a reference to the Shard Map Manager
$ShardMapManager = Get-ShardMapManager -UserName '<user_name>' -Password '<password>' -SqlServerName '<server_name>' -SqlDatabaseName '<smm_db_name>'
Where <user_name> and <password> are the credentials used to connect to shard map manager database, in this case <smm_db_name>, on server <server_name>.
2. Populating a shard map.
With the sample below, following steps are performed:
A new shard map is created within a shard map manager.
The metadata for two different shards is added to the shard map.A variety of key range mappings are added, and the overall contents of the shard map are displayed.
# $ShardMapManager is the shard map manager object
$ShardMap = New-RangeShardMap -KeyType $([int]) -RangeShardMapName 'RangeShardMap' -ShardMapManager $ShardMapManager
# Add a new shards to hold the range being added.
Add-Shard -ShardMap $ShardMap -SqlServerName '<shard_server_name>' -SqlDatabaseName 'sample_shard_0'
Add-Shard -ShardMap $ShardMap -SqlServerName '<shard_server_name>' -SqlDatabaseName 'sample_shard_1'
# Create the mappings and associate it with the new shards
Add-RangeMapping -KeyType $([int]) -RangeHigh '50' -RangeLow '0' -RangeShardMap $ShardMap -SqlServerName '<shard_server_name>' -SqlDatabaseName 'sample_shard_0'
Add-RangeMapping -KeyType $([int]) -RangeHigh '100' -RangeLow '50' -RangeShardMap $ShardMap -SqlServerName '<shard_server_name>' -SqlDatabaseName 'sample_shard_1'
Add-RangeMapping -KeyType $([int]) -RangeHigh '150' -RangeLow '100' -RangeShardMap $ShardMap -SqlServerName '<shard_server_name>' -SqlDatabaseName 'sample_shard_0'
Add-RangeMapping -KeyType $([int]) -RangeHigh '200' -RangeLow '150' -RangeShardMap $ShardMap -SqlServerName '<shard_server_name>' -SqlDatabaseName 'sample_shard_1'
Add-RangeMapping -KeyType $([int]) -RangeHigh '300' -RangeLow '200' -RangeShardMap $ShardMap -SqlServerName '<shard_server_name>' -SqlDatabaseName 'sample_shard_0'
# List the shards and mappings
Get-Shards -ShardMap $ShardMap
Get-Mappings -ShardMap $ShardMap
See the content of ShardManagement.psm1 for more details.