SQL Data Services underwent a massive change with the last CTP, eliminating the entity bag container, whatever they called it thingie and moved to a fully cloud-based relational model. Another major change was that it was given a new and more appropriate name: SQL Azure. You can get the CTP and have access to what I call SQL Server lite in the cloud. Since SQL Azure supports the fully relational model along with stored procedures and views, you can connect to SQL Azure with a regular old ADO.NET connection string like the following one, allowing you to code against SQL Azure with .NET the same way you did with plain old SQL Server.
Once you are all signed up for the CTP you can go into the web based admin tools and create a database. I created a database called Northwind and another one called Northwind_Lite for testing.
To be honest, I am not sure what else you can do in the web interface. So you have to connect via SQL Management Studio to create your database schema. There is the first problem. SQL Azure does not support the object explorer view that you get in SQL Management Studio, so you will have to hack a little bit.
Connecting to SQL Azure with SQL Server Management Studio
This is not as easy as it sounds. :) Since you can’t connect through the object explorer, you will have to open a new TSQL Query window.
In the log in dialog, enter in the server name from the CTP’s connection string and the user name and password that you choose to administer the CTP.
SQL Azure does not support the “Use” statement, or the ability to change databases on your connection. So you have to cheat and use some of the advanced options when logging in. You can do this by selecting the “Options >>” button on the log in dialog and then selecting “Connection Properties”. Under the Connect to database option, you have to select the database that you want to work with, since the default will be the Master database and most likely you will not be building any applications using the Master database.
After you connect you will get an error about the inability to apply connection settings, which you can ignore.
You will notice right away that there is nothing in your database as the following SQL statement will show:
select * from sys.objects
We now have to migrate some database objects from our SQL Server database to SQL Azure.
Migrating Existing SQL Server Objects to a SQL Azure Database
It would be cool if there were some easy way to migrate your databases to SQL Azure in this CTP. There is not. I suspect that in future CTPs this will not be a problem. But for now, you have to get creative. Some hacks and shortcuts are in order.
To get started, let’s just copy over one table. To do this, open your local SQL Server in the object explorer. Drill down to the Northwind database and drill down into the Customers table. Right click and select Script Table as|CREATE To|Clipboard and you will have a nice CREATE TABLE statement on your clipboard.
Then paste the TSQL into the Query Window that is connected to your SQL Azure database. Here is what my generated TSQL looks like:
1: USE [Northwind]
4: /****** Object: Table [dbo].[Customers] Script Date: 09/04/2009 03:35:38 ******/
5: SET ANSI_NULLS ON
8: SET QUOTED_IDENTIFIER ON
11: CREATE TABLE [dbo].[Customers](
12: [CustomerID] [nchar](5) NOT NULL,
13: [CompanyName] [nvarchar](40) NOT NULL,
14: [ContactName] [nvarchar](30) NULL,
15: [ContactTitle] [nvarchar](30) NULL,
16: [Address] [nvarchar](60) NULL,
17: [City] [nvarchar](15) NULL,
18: [Region] [nvarchar](15) NULL,
19: [PostalCode] [nvarchar](10) NULL,
20: [Country] [nvarchar](15) NULL,
21: [Phone] [nvarchar](24) NULL,
22: [Fax] [nvarchar](24) NULL,
23: CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
25: [CustomerID] ASC
29: PAD_INDEX = OFF,
30: STATISTICS_NORECOMPUTE = OFF,
31: IGNORE_DUP_KEY = OFF,
32: ALLOW_ROW_LOCKS = ON,
33: ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
35: ON [PRIMARY]
We already know that SQL Azure does not support USE, so eliminate lines 1&2 and press F5. You will see that line 5 also is not supported, so eliminate that and keep going by pressing F5 again. You will see that ANSI_NULLs, PAD_INDEX, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, and ON [PRIMARY] are not supported, so you will have to eliminate them as well. Your new trimmed down SQL Azure SQL script looks like this:
1: SET QUOTED_IDENTIFIER ON
3: CREATE TABLE [dbo].[Customers](
4: [CustomerID] [nchar](5) NOT NULL,
5: [CompanyName] [nvarchar](40) NOT NULL,
6: [ContactName] [nvarchar](30) NULL,
7: [ContactTitle] [nvarchar](30) NULL,
8: [Address] [nvarchar](60) NULL,
9: [City] [nvarchar](15) NULL,
10: [Region] [nvarchar](15) NULL,
11: [PostalCode] [nvarchar](10) NULL,
12: [Country] [nvarchar](15) NULL,
13: [Phone] [nvarchar](24) NULL,
14: [Fax] [nvarchar](24) NULL,
15: CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
17: [CustomerID] ASC
19: (STATISTICS_NORECOMPUTE = OFF,
20: IGNORE_DUP_KEY = OFF)
Run this and you will have a new Customers table! Unfortunately there is no data in there, but we will get to that soon.
If you are moving a lot of tables and foreign key constraints, etc, you should use the SQL Azure Migration Wizard developed by George Huey. This tool,available on codeplex, will assist you in migrating your SQL Server schemas over to SQL Azure. Wade Wegner blogged about it here, including an instructional video.
Unfortunately there is no such tool for migrating data that I know of. Time for the next hack.
Migrating Data from SQL Server to SQL Azure
I thought that maybe I can cheat the same way I altered the connection settings and use SSIS to migrate the data. I choose the ADO.NET option and entered in all of the data, but it bombed. Then I tried my old reliable tool, Red Gate’s SQL Data Compare. No go. But it was worth a try, since it got me thinking. I created a new database locally called “Azure_Staging” and ran the same CREATE TABLE script there, creating a blank Customers table. I then ran SQL Data Compare using the full Customer table in Northwind as my source and my newly created blank Customer table in Azure_Staging as the destination.
Of course SQL Data Compare found 91 missing rows and I launched the Synchronization Wizard.
Click through it and on the 3rd page, click on the “View SQL Script…” button and copy and paste the generated SQL.
Copy and paste just the 91 INSERT INTO statements into your SQL Azure Query Window and run it. Now we have data in SQL Azure!
Unfortunately this is not the best situation, having to manually create some TSQL scripts, but this is an early CTP. I am sure that future CTPs will make this much easier.