PowerShell
Edit|Remove
.\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'
 Introduction

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:

 

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:

 

 

 

  1. 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:

  1. A new shard map is created within a shard map manager.

    The metadata for two different shards is added to the shard map.

  2. 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.