In this blog post we will cover some new abilities in SQL Server Management Studio 2012 (SSMS) that enhance the ability to work with SQL Azure.  Specifically, we will highlight support in SSMS for the new SQL Azure feature known as SQL Azure Federation, which was just introduced this past December. Federations in the SQL Azure database provide the ability to achieve greater scalability and performance from the database tier of the application through horizontal partitioning of data in multiple databases. One or more tables within a database are split by row, and are stored across multiple system-managed databases, called Federation members. This type of horizontal partitioning is often referred to as ‘sharding’. Detailed information about SQL Azure Federation is available on Cihan Biyikoglu’s blog, MSDN Online, and this video demonstration.

The SQL Azure Management Portal and the SQL Server Management Studio (SSMS) of the SQL Server 2012 RC0 release include rich tooling support for managing Federations. One can easily create new Federations, view the Federation meta-data & the Federation members, split Federation members, and work with the database objects in the Federation root and work with the Federation members. The features that were especially built for Federations in SSMS include the following:

Streamlined view in the Object Explorer: If one connects to the SQL Azure virtual server in the SSMS, only the user created databases are listed in the Object Explorer and the GUID-named Federation members are filtered out. This helps in reducing the clutter and makes it easier to work with the databases.

View, create, split and delete Federations: The Federation root database can have more than one Federation and all the Federations are listed under the node titled ‘Federations’ in the object explorer. The right-click context menu on the Federations node and the Federation members have the option to create new Federations, split and delete Federation members, view top 1000 Federation members and connect to a Federation member. 

SQL Server Management Studio 2012 Screen Shot: The right-click context menu on a Federation has the options for viewing, splitting and deleting Federation members.

 

SQL Server Management Studio 2012 Screen Shot: Listing the Top 1000 Federation members in a Federation.

Scripting Federations: The right-click context menu on a Federation provides the option for scripting a Federation. The CREATE To scripting option for a Federation scripts out the layout of the Federation and includes all the splits in the Federation in form of ALTER FEDERATION…SPLIT AT. Running the script will recreate the Federation and all the Federation members in the Federation.

SQL Server Management Studio 2012 Screen Shot: Scripting a Federation.

Connecting to a Federation member: The ‘Connect to a Federation Member’ dialog is used for connecting to a Federation member by specifying a distribution value for the distribution key. The validator in the dialog lists the permissible range for the distribution value based on the data type of the key and also validates that the value is in the correct format and range.

   

SQL Server Management Studio 2012 Screen Shot: Connect to a Federation member dialog.

Viewing Federation member and the objects in it: After a successful connection to a Federation member, the member is displayed in the Object Explorer in a separate node. The Federation member is listed in a friendly name comprising of :: (Federation Distribution Key = Range_Low..Range_High). The icon for the Federation member and the Federated table are different from the icons for other databases and tables to make it easy to distinguish Federation objects. The column in the Federated table that is linked to the Federation Distribution Key is labeled as ‘federated’ for easily identifying it. A federated table, when scripted out, is scripted with the ‘FEDERATED ON’ clause that can be used to recreate the table in other Federations.

SQL Server Management Studio 2012 Screen Shot: Viewing Federation member and the objects in it. The T-SQL Editor displays the CREATE To script for the Federated table Customers with the FEDERATED ON clause.

Reconnecting to a Federated member after it splits: A split operation can be in progress for a Federation member while there is a connection to it in the Object Explorer. After the split operation completes, the original Federation member database is deleted and the connection to the member in the Object Explorer becomes invalid. SSMS detects this and pops up a message that the split has completed and provides an option to invoke the ‘Connect to a Federation Member’ dialog to connect to the new Federation member database that now has the data from the original Federation member.

SQL Server Management Studio 2012 Screen Shot: Reconnecting to a Federation member after it splits using the ‘Connect to a Federation Member’ dialog.

Querying the Federation members in the T-SQL Editor: The T-SQL Editor displays the name of the Federation member that it is connected to in the same friendly syntax as used in the Object Explorer. The connection can be easily switched between the Federation root database and the Federation member using the Available Database dropdown control in the T-SQL Editor toolbar. If the USE FEDERATION query is issued to change the connection to another federation member or to the root, the T-SQL Editor detects that the connection has changed and updates the friendly name to the Federation member or the root that it is connected to.

SQL Server Management Studio 2012 Screen Shot: The Federation member is displayed in the same friendly syntax as used in the Object Explorer. The Available Databases dropdown control can be used to change connection to the Federation root.

SQL Server Management Studio 2012 Screen Shot: The friendly name is updated to the Federation member database that the T-SQL Editor is connected if the connection is changed using the T-SQL statement – USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF

Warning messages: Warning messages alert the users against taking action that can have far reaching impact. For example, if an attempt is made to delete the Federation root database, a warning message is displayed alerting the user that the database has Federations in it and deleting the database will also delete all the Federations in the database.

SQL Server Management Studio 2012 Screen Shot: Warning message when deleting a Federation root database.

With the native support for Federations in SSMS, administrators and developers can work with Federation through an intuitive interface that makes it easy to navigate and manage at scale. We would like to hear feedback on this experience. You can email your feedback to mailto:SAPESupport@microsoft.com.