SQL Azure enhancements with SQL Server 2008 R2

By: Arshad Ali | Comments (1) | Related: > Azure

Problem

If you were unhappy with the capabilities of SQL Server Management Studio (SSMS) while working with SQL Azure, then there is good news for you. Microsoft has announced the November CTP for Microsoft SQL Server 2008 R2. The SSMS of this version allows you to work with SQL Azure in almost the same way as when you are connected to a local SQL Server. In other words, now you can use your favorite Object Explorer in SSMS to browse through the database objects hosted in SQL Azure as well.

Solution

In my first tip in this series, I discussed how you can start working with Microsoft SQL Azure. I also discussed the limitations of SSMS in terms of its capabilities to work with SQL Azure.

With SQL Server 2008 R2 (Nov CTP), SSMS has been enhanced to support SQL Azure connectivity using Object Explorer along with a host of other features. In this tip, I am going to go through the latest enhancement in SSMS which support SQL Azure.

If you have not already worked with SQL Azure, please review this tip and go through the steps of setting up an account. Also, you will need to download and install SQL 2008 R2 (Nov CTP) to use these new features.

Click on "Connect" in Object Explorer, select Database Engine, provide your credential to connect to SQL Azure and choose the database to connect to.

You should now now be connected to your database hosted in SQL Azure using Object Explorer.

Now you can drill-down similar to the way you can when connected to a local SQL Server. Some capabilities are still limited and more features are likely to come in future CTPs or in the RTM. For example when you click on the "New Table" option, it will not open up the table designer, but rather will give you a create table script template which is compatible with SQL Azure also the "Design" option will not be available for an existing table if you are connected to SQL Azure. For that you need to generate a script for the existing table, make the required changes and run the script again to modify the table.

The Generate and Publish Scripts Wizard has also been enhanced to use SQL Azure for both the source and destination for the scripts it publishes. It can be handy if you want to move your database or database objects from one server or database to another server or database on SQL Azure.

Though you can use almost all the important T-SQL statements with SQL Azure, there are few of which are not supported and a few need a little bit different syntax, you can find a complete list of supported/unsupported T-SQL statements here.

The syntax differences are mostly related to the definition of the physical storage location. For example when you create a database in the cloud you specify the create database statement without specifying the file-groups and file information. Likewise when you create a table you don't specify the file group on which the table is to be created.

--Script #1 : CREATE TABLE statement in SQL Server

CREATE TABLE [Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL, 
[Title] [nvarchar](8) NULL, 
[FirstName] [nvarchar](50) NOT NULL, 
[LastName] [nvarchar](50) NOT NULL, 
[EmailAddress] [nvarchar](50) NULL, 
[Phone] [nvarchar](30) NULL, 
[Timestamp] [timestamp] NOT NULL, 
PRIMARY KEY CLUSTERED 
( 
[CustomerID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] 
GO

Script #1 contains a simple script to create a table in SQL Server, as you can notice, by default, the table along with the primary key index is being created in the PRIMARY file-group whereas in Script #2 in SQL Azure you just specify the create table statement without specifying the physical storage location.

--Script #2 : CREATE TABLE statement in SQL Azure

CREATE TABLE [Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL, 
[Title] [nvarchar](8) NULL, 
[FirstName] [nvarchar](50) NOT NULL, 
[LastName] [nvarchar](50) NOT NULL, 
[EmailAddress] [nvarchar](50) NULL, 
[Phone] [nvarchar](30) NULL, 
[Timestamp] [timestamp] NOT NULL, 
PRIMARY KEY CLUSTERED 
( 
[CustomerID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) 
) 
GO

So if you are wondering how you will move your database schema objects and data to SQL Azure there are several options, for example you can use Visual Studio Team System 2008 Database Edition or a third party database comparison tools to generate the database schema objects by comparing your database with a blank database and for data comparing your table with an empty table. You need to do some tweaking before executing the scripts generated by these options, in other words you need to remove any T-SQL statement which is not supported by SQL Azure or which has slightly different syntax.

To save you from some of this work, George Huey has developed a SQL Azure Migration Wizard which helps you in migrating your SQL Server 2005/2008 database to SQL Azure. This tool is smart enough to tweak the T-SQL statements to a great extent to make it compatible with SQL Azure, but there might be some changes you need to do in the generated scripts for which it will give you comments inline as you can see in the image below.

Note

The step by step demonstration shown above is based on SQL Azure October CTP and SQL Server 2008 R2 Nov CTP. There might be new features or changes in the way the current features work in upcoming CTPs or in the RTM. Kindly refer to Microsoft SQL Azure site and Microsoft SQL Server 2008 R2 site for latest updates.
You can not create a table without a primary key in SQL Azure.
Even though you can create a new database in SSMS it will not be visible in Object Explorer (only the current database will be visible in Object Explorer) unless you connect to that database, it means you still can not switch from one database to other if you are connected to SQL Azure.

Next Steps

Review Moving your database to cloud with SQL Azure - Part 1 tip.
Review Microsoft SQL Azure on MSDN.

About the author

Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Sunday, December 20, 2009 - 8:34:42 AM - mgerasimov	Back To Top (4590)
You can use any schema comparison and synchronization tool which supports SQL Azure to move your database to cloud without any script tweaking. I know, that SQL Examiner do it properly, but, probably, SQL Compare or Apex SQL Diff do it perfecty too.