Cumulative Update 3 for SQL Server Management Studio 2008 R2: What’s in it for managing SQL Azure Databases?

  • Article

Microsoft SQL Azure Database periodically adds new features to its existing service offerings. A few months back, via the Service Update 2 and Service Update 3, the following features were made available to users of SQL Azure Databases:

  1. “ALTER DATABASE MODIFY NAME” T-SQL. This now allows users to change the names of their database hosted in SQL Azure. (More details can be found here).
  2. Support for the Hierarchyid, Geometry and Geography data types(More details can be found here).
  3. Creation of spatial index (CREATE SPATIAL INDEX) (More details can be found here).

In order to enable users to work with these features when connected to SQL Azure, SQL Server Management Studio 2008 R2 was updated via the Cumulative Update 3 (CU3). The list of SSMS enhancements that happened for SQL Azure in SSMS 2008 R2 CU3 is as follows (please note that these capabilities are already present in SSMS 2008 R2 for users connected to their on-premise database servers):

1. Database Rename support in Object Explorer (OE).

You can now rename a SQL Azure Database from OE itself. Steps for achieving the purpose are:

  1. Connect to “master” database on SQL Azure.
  2. Expand the “Databases” node in OE.
  3. Select required database node.
  4. Either press “F2” or right click on the node and select “Rename” from the context menu.
  5. When the database node text has become editable, type the new name of the database and press “Enter”.

2. Tables having columns with Spatial data types (Geometry, Geography) and Hierarchyid data type can now be scripted for SQL Azure database using SSMS.

Before CU3 of SSMS 2008 R2, SSMS throws exception when we script a table, having columns with these data types, for SQL Azure database. We have fixed this issue in CU3 by adding support of these data types for SQL Azure database.

3. Spatial Index support in SSMS for SQL Azure.

Spatial Index scripting support for SQL Azure is also added as part of CU3 for SSMS 2008 R2.

Note:

One part of Spatial Index support that we have not addressed yet is the creation of Spatial Index for SQL Azure from SSMS. We have plans to address this by adding a new template to the Template Explorer for creating Spatial Index on a table in SQL Azure database. But that will happen in a Public CU release only. Till then, in case you find this useful, you can add the given template to the Template Explorer in your SSMS 2008 R2.

-- ====================================================

-- Create Spatial Index template for SQL Azure Database

-- ====================================================

CREATE SPATIAL INDEX <index_name, sysname, spatial_index>

ON <schema_name, sysname, dbo>.<table_name, sysname, spatial_table>

(

<column_name, sysname, geometry_col>

)

USING <grid_tessellation, identifier, GEOMETRY_GRID>

WITH

(

BOUNDING_BOX = (

<x_min, float, 0>,

<y_min, float, 0>,

<x_max, float, 100>,

<y_max, float, 100>),

GRIDS =(

LEVEL_1 = <level_1_density, identifier, HIGH>,

LEVEL_2 = <level_2_density, identifier, LOW>,

LEVEL_3 = <level_3_density, identifier, MEDIUM>,

LEVEL_4 = <level_4_density, identifier, MEDIUM>),

CELLS_PER_OBJECT = <cells_per_object, int, 10>,

DROP_EXISTING = OFF

)