Introduction

Elastic database tools for Azure SQL DB enables the data-tier of an application to scale out and in using database sharding patterns, significantly streamlining the development and management of cloud database applications. This sample provides a command-line interface similar to the SQLCMD Utility that allows you to execute Transact-SQL queries across an entire set of databases. The sample illustrates using shard map manager and multi-shard query functionality available with the elastic database client library. The prerequisite is to first create a sharded application which can be easily achieved using the Getting Started sample application for elastic database client library.

Building the Sample

After downloading the sample project and its code, simply build and then execute the utility (ShardSqlCmd.exe) in a command window. When building the sample, Visual Studio will download the elastic database client library using NuGet.

Syntax

ShardSqlCmd

-S server
-d shard map manager database
-sm shard map name
-U login id (user name)
-P password
-E trusted connection
-t query timeout
-pr PartialResults mode
-sn include $ShardName column in results

Command-line Options

-S server
Required. Specify server_name to connect to the Azure SQL Database server that contains the ShardMapManager database.

-d shard map manager database
Required. Specify the name of the Shard Map Manager database that contains the shard locations and mappings for the shard set.

-sm shard map
Required. Specify the name of the Shard Map. Transact-SQL Queries will execute against all shards in the shard set contained within the Shard Map.

-U login id (user name)
Specify the user login id.

-P password
Specify the user-specified password. Passwords are case sensitive.

If neither the -U option nor the -P option is specified, -E must be specified and ShardSqlCmd tries to connect using Microsoft Windows Authentication mode. Authentication is based on the Windows account of the user who is running ShardSqlCmd. An error will occur if Windows Authentication is attempted to connect to a Azure SQL Database.

-E trusted connection
Uses a trusted connection instead of using a user name and password to log on to SQL Server. Currently, Microsoft Azure SQL Database does not support Windows Authentication, therefore use SQL Server authentication when connecting to Azure SQL Database.

-t query timeout
Optional. Specifies the number of seconds before a Transact-SQL Query times out. If a timeout value is not specified, the default value is 60 seconds.

-pr partial results
Optional. When specified, the value of the Multi-Shard Execution Policy is set to partial results. Partial results indicates a best-effort execution policy that, unlike complete results, tolerates unsuccessful command execution against one or more shards and returns the results of the successful commands. Any errors encountered are returned to the user along with the partial results. If the not specified, the default is set to complete results. Complete results indicates a strict execution policy that requires successful command execution against all shards. An unsuccessful execution leads to results being discarded and the error being propagated to the user.

-sn include $ShardName column in results
Optional. When specified, the value of the Multi-Shard Execution Options indicates to include a $ShardName pseudo column in the result set. This pseudo column will identify the data source and database from which the result was returned. If the parameter is not specified, the default behavior will not include the $ShardName pseudo column in the result set.

Technologies Illustrated

This sample utility illustrates some of the main aspects of the elastic database client library for Azure SQL DB. The code sample will introduce you to the following Elastic Scale technologies.

Using the ShardSqlCmd Utility

The ShardSqlCmd Utility is an interactive tool allowing execution of Transact-SQL Queries across a sharded collection databases.

Outcome

Once you have the sample running in a command-line window, you specify the parameters required to connect to the ShardMapManager database. Once connected, the utility will indicate the number of shards in the shard set identified in the shard map based on the shard map name. At this point, you can provide a Transact-SQL Query followed by a GO statement, as demonstrated in the above illustration, to execute the query against all the shards. The results will display in the window based on the execution policy and execution options.

Next Steps

Elastic database tools for Azure SQL DB provide you with powerful capabilities to scale the data tier of your cloud applications in tandem with the changing capacity needs of your business. If you enjoyed this sample application, continue further exploring elastic database tools and capabilities here: http://go.microsoft.com/?linkid=9862897.

Troubleshooting

Common Error Messages: