SQL Azure Powershell for Developers on the Run!

In my last post I Announced the Windows Azure Powershell Extensions a new project that I will be iterating over when I run into tasks that are common in everyday Windows Azure Scenarios.

Being a Developer on the run, going from City to City or Coffee shop to Coffee shop, the absolute first Extension came to me naturally. Quickly create firewall rules for where ever you are by using Add-RoamingFirewallRule, when you’re ready to go remove the setting with Remove-RoamingfirewallRule.

Let’s take a look at how to use these scripts. [If you haven’t already installed the Windows Azure Powershell Cmdlets, download the Windows Azure Powershell Cmdlets]

Adding Functions to your Powershell Profile

Functions are only helpful in powershell if their accessible, if it takes you too long to set up a script there’s a good chance you will never use it. Let’s take a brief look at making sure your Powershell environment is up and running.

Open Powershell.

Create a Powershell profile

new-item -path $profile -itemtype file -force
Notepad $profile

Now that your profile has been created, and opened in notepad it’s time to copy the Add-RoamingFirewallRule and Remove-RoamingFirewallRule functions from the Windows Azure Powershell Extensions Project on GitHub and paste them into the open notepad document. After you’ve finished save and close the file. Restart Powershell.

Using Add-RoamingFirewallRule & Remove-RoamingFirewallRule

Let’s first take a look at the help files that are provided with the script.

Get-Help Add-RoamingFirewallRule

image

Get-Help Remove-RoamingFirewallRule

image

You’ll notice that both Add-RoamingFirewallRule and Remove-RoamingFirewallRule both require an EnvironmentsCsv parameter which is a path to a CSV file containing Subscription and Management Certification information.

Creating the Subscription CSV File

The CSV file ensures that you don’t need to login to the Windows Azure Portal anytime you’re looking for your SubscriptionIds [Just keep the CSV file up-to-date]. The file requires the header in order to map to the internal variables in the function. The first value is the SubscriptionId for your Windows Azure Account, the second value is the Thumbprint for a Certificate [installed in CurrentUser\My] which has been uploaded as a Management Certificate using the Windows Azure Portal. Save this CSV file in a memorable location.

SubscriptionId,Thumbnail
BD1DD4FC-E866-473A-8665-760BE7B007B0,204E4F82C76FD2586234E7064FEC1EAEB0709507
E969F512-56ED-4CF3-B59D-A60A5D394527,204E4F82C76FD2586234E7064FEC1EAEB0709507

Adding SQL Azure Firewall Rules with Add-RoamingFirewallRule

To create a Roaming Firewall Rule you simply need to call Add-RoamingFirewallRule and pass in a Rule Name and a Path to the Subscription.csv file [created above].

Add-RoamingFirewallRule 'Starbucks' C:\WindowsAzure\Subscription.csv

image

After taking a look at the Windows Azure Portal you notice that a Firewall Rule will be created for every SQL Azure Server within each of the subscriptions that were defined in the CSV file.

Removing SQL Azure Firewall Rules with Remove-RoamingFirewallRule

To remove the setting once you’re done hanging out at Starbucks, simply call Remove-RoamingFirewallSetting passing in the same Rule name and the path to your Subscriptions.csv file.

Remove-RoamingFirewallRule 'Starbucks' C:\WindowsAzure\Subscription.csv

image

Magically the firewall rule disappears!

That’s a Wrap

I hope that this helps speed up your day when working with SQL Azure at different locations or encourages you to get out of the office from time to time. If you have any requests or notice a bug please file an issue on github.

Happy Clouding!

Migrating Large Databases from On-Premise to SQL Azure

Recently, I was working on a project that required a site migration from a Shared Hosting server to Windows Azure. This application has been up and running for sometime and had acquired quite a substantially sized database.

During the course of the project I ran across a few road blocks which I wasn’t expecting, due to the experience gained in my previous blog entries: Migrate a database using the SQL Azure Data Sync Tool and Scripting a database for SQL Azure (Issues explained in previous link resolved with launch of SQL Server 2008 R2). Hopefully the following tricks will help you along your data migration.

Using Import/Export in SSMS to Migrate to SQL Azure

In addition to the SQL Azure Data Sync Tool, it is possible to use the existing Import/Export Wizard in SQL Server Management Studio to migrate data to SQL Azure. There are a number of things to keep in mind while using the Import/Export Tool:

SQL Server Native Client to .NET Data Provider for SqlServer

SQL Azure doesn’t fall under the typical SQL Server Native Client 10.0 Product SKU, this means that you’ll have to use the .NET Data Provider to migrate your data. The configuration screen for the provider is very intuitive, but there are two key settings that should be changed from their default values, Asynchronous Processing (set to true) and Connection Timeout (increase to 1500).

SQL-Azure-SMSS-IE-Tool

Without changing the Timeout value the data migration would error out after creating the fist few sets of rows. Making this an Asynchronous process was beneficial when exporting multiple tables at a time.

Work-around for SSIS Type: (Type unknown …) Error

There is a chance when you go to run the migration that you will encounter an error as described in Wayne Berry’s [@WayneBerry] blog post entitled “SSIS Error to SQL Azure with varbinary(max)” on the SQL Azure Blog.

As Wayne explains in his post, there are a number of XML files which contain data mapping information used by the Import/Export Wizard in order to map the data from the source database to the proper data type in the destination database.

Database Seeded Identity Insert Issue

I’m not sure why this happened, but when using the Import/Export even with Identity Insert on, the ID [Identity] Column was not Inserting the correct values. To get around this I used the ROW_NUMBER to generate new Identities and rebuilt the foreign key tables.

There is a lot of chatter on the Forums and other blog posts that say that BCP with the –E switch is the most effective way to do exact copying (with Identity Columns).

For more information:

Cost Effective Approach

A good thing to keep in mind while preparing your database for migration is that transactions as well as data transfer costs are applied to Queries to (and from) SQL Azure. With this in mind it would be best to set up a scenario where you would test your data migration to ensure the data migration would be performed in the least number of attempts as possible.

Happy Clouding!

Reach for the Sky… Script a Database for SQL Azure

While there are many different ways to get your database up and running into the cloud. You could read up on SQL Azure Sync Framework in my post titled “Synchronizing a Local Database with the Cloud using SQL Azure Sync Framework”.

However, nothing feels more comfortable to a developer than something that familiar. After a little bit of investigating while preparing for my talk at Confoo on SQL Azure, I managed to find a post on the MSDN website that Explains what is needed in order to use the Generate Scripts Wizard in SQL Server Management Studio.

Create the Transact-SQL Script

  1. In Object Explorer, right-click the database, point to Tasks, and select Generate Scripts.

  2. In the Script Wizard dialog box, click Next to get to the Select Database step. Select School, select Script all objects in the selected database, and then click Next.

  3. In Choose Script Options, set the following options:

    • Convert UDDTs to Base Types = True
    • Script Extended Properties = False
    • Script Logins = False
    • Script USE DATABASE = False
    • Script Data = True

    SQL Azure does not support user-defined data types, extended properties, Windows authentication, or the USE statement.

  4. Click Next, click Next, and then click Finish. The Script Wizard generates the script. Click Close when the script is completed.

  5. In the generated script, delete all instances of "SET ANSI_NULLS ON".

  6. Each CREATE TABLE statement includes a "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" clause. Delete all instances of that clause.

  7. Each CREATE TABLE statement includes the "ON [PRIMARY]" clause. Delete all instances of that clause.

The reason you need to apply these changes to the script is that SQL Azure currently doesn’t support all the features of the currently release of SQL Server 2008.  There are plans to start to incorporate some of the features that are in this outline, including the USE statement.

Hopefully this will make your life easier on your move to the cloud. Until then, Happy Coding!

Synchronizing a Local Database with the Cloud using SQL Azure Sync Framework

I’ve been looking at different aspects of Windows Azure over the past few months, and have been having a lot of fun getting a grip on Cloud Computing and Microsoft’s Cloud Computing Platform. One of my past posts gives a step-by-step tutorial on how to connect to SQL Azure using Microsoft SQL Server 2008 R2 (November CTP). If you haven’t already done so I would suggest reading Making Data Rain Down from the Cloud with SQL Azure to follow along with this tutorial.

I received an email a few days ago from Apress Books suggesting me the book to the left due to my previous purchase of Introducing Windows Azure.  Both books cover the Windows Azure Storage Services API, and SQL Azure. The Introducing Windows Azure book covers Azure .NET Services, which was renamed to the AppFabric Services which is covered in Windows Azure Platform. This name change was announced during the first keynote [View Keynote] of PDC 2009. Along with Codename “Dallas” [Data as a Service], Microsoft Pinpoint Marketplace.

Codename Dallas is built on top of Windows Azure and SQL Azure. Dallas will supply developers with Data as a Service, as part of the Open Data Movement in the United States. If this doesn’t get you excited about Microsoft’s Cloud Computing Platform, I’m not sure what will.  To ensure that you can develop on the cloud and use the SQL Azure Database Platform to your advantage read forward as I’ll be showing you how to Migrate a database to the cloud with SQL Azure Data Sync, which is an extension of the Microsoft Sync Framework.

 

To begin you will have to download the Microsoft Sync Framework 2.0 Software Development Kit (SDK) and the Microsoft Sync Framework Power Pack for SQL Azure November CTP (32-bit) and if you don’t already have a SQL Azure Account you can get one here at the SQL Azure Data Sync Developer Quick Start page.

Once you have your SQL Azure Account, you will need to provision a new database in the cloud and change the firewall settings so the local computer can connect to and modify the database.  These steps are provided in Making Data Rain Down from the Cloud with SQL Azure. Now that we’ve got our SQL Azure firewall database set up we’re ready to sync a local database into the cloud.

When you first fire up SQL Azure Data Sync you will see this Introduction screen. [Hopefully future releases of this tool will allow you to check a box to skip this screen, like other SQL Server Tools.]

SQL-Azure-Data-Sync-Intro

Hitting next on the Introduction screen will take you to the SQL Azure configuration screen where you will need to enter the details for your SQL Azure Account. For this step you will need to login to your SQL Azure account to figure out what server your database server is hosted on. Fill out the database information required, like you see below.

SQL-Azure-Data-Sync-Setup-AzureDB

A good thing to note here is that this database should not be created in the cloud already, the data sync tool will be creating the database from the local instance you will be setting the details up for in the next step. So lets click next and enter the information for our local Database.

SQL-Azure-Data-Sync-Setup-LocalDB

Once you have both databases configured [and tested] you’re ready to select the tables you wish to synchronize or create in the cloud database.

SQL-Azure-Data-Sync-Tables

Due to the fact Relational Databases have Foreign Keys to link data from one table to the next you may need to synchronize the database in a particular order to maintain data integrity. If you have data in your tables and your tables are related to one another be sure to place the Primary Key table, before the Foreign Key table. Here is the screen that will allow you to order your tables in order they should be synchronized.

SQL-Azure-Data-Sync-Tables-Order

After you have reorganized the order in which your tables are to be synchronized, you will be shuffled off to a Summary page. This page outlines the Database Server, and asks if it is to create a 1GB Database, or a 10GB Database in the cloud.

SQL-Azure-Data-Sync-Summary

After Reviewing the information and selecting the database size that should be created. Click on the Process button to Copy the local Microsoft SQL Server database to SQL Azure.

SQL-Azure-Data-Sync-Process

SQL-Azure-Data-Sync-Progress

Once this process is complete the Microsoft SQL Server has been created on the specified SQL Azure Database server. The Synchronization is carried out by SQL Server Agent, be sure to copy down the Job name from the Finish Setup screen.

SQL-Azure-Data-Sync-Results

You may need to Configure SQL Server Agent before the first time the Synchronization is run.

SQL Azure Data Sync is a great way to deploy your current databases into the cloud. You can use the cloud as a method of backing up your current infrastructure or you can start expanding into the cloud as a more economical solution of expanding your current data centers. As we count down to the New Year it seems fitting to think about moving into the future of Technology. Happy New Years!

Setting up the Development Storage Service

If you are setting up your Development Environment for Windows Azure and want to avoid installing SQL Server Express. I didn’t want to use SQL Server Express because I’ve already installed SQL Server 2008 R2 CTP so I can interact with my SQL Azure Database in the Cloud.

From the command line, Navigate to the Windows Azure SDK directory.

cd "C:\Program Files\Windows Azure SDK\v1.0\bin\devstore"

Then you will Launch the Development Storage Initialization Tool [DSInit.exe]. You will need to set the SqlInstance [/sqlinstance:] that you wish to create the tables on, and you will need to force [/forceCreate] the tool to create the tables.

DSInit.exe /sqlinstance:<YourDatabaseName>/forceCreate

This will launch the Initialization Tool.

Development Storage Initialization
If you receive the error message below you it is most likely because the instance name was improperly set. Ensure to be use only the InstanceName do not include periods or slashes.

Development Storage Initialization Error

After your Development Storage is set up make sure you  go into your Development Fabric and Start the Development Storage Service.

Happy Coding!

Making data rain down from the Cloud with SQL Azure.

Hopefully you’ve had the chance to get a token [Check for a token here] to check out Microsoft’s Upcoming Cloud computing Platform Windows Azure. I’ll be posting some more information about Windows Azure in the coming weeks, but while i am creating a new website for Guelph Coffee and Code I thought I would bring you some information on connecting to SQL Azure in order to get your database up into the cloud.

Even though it is possible to connect to a database using SQL Server 2008, I’d suggest getting the Nov CTP for SQL Server 2008R2.  Just incase you don’t want to download a CTP here’s a hint as to how you can connect to your database with SQL Server 2008. The main reason I suggest SQL Server 2008 R2 is due to the fact that the Object Explorer of the current version of SQL Server is not compatible with SQL Azure.

Oh, I see you took my hint and downloaded R2, excellent choice. Now that you have your SQL Server Management Studio set up, lets take a look at some of the configurations that we will have to do in SQL Azure in order to connect to our database. [At the time of this blog post SQL Azure was available only to those who requested a token to access it. This post assumes you have access to SQL Azure.]  Here are the steps to take to get to the SQL Azure Cloud Configuration Console.

You should now be in an area that contains an interface much like this one:

Database_Console

As you see I’ve already Created a Database, it’s a very simple process where you define a name and a size [1GB or 10GB] and click on OK to create it.  In order to allow your Azure applications or to Manage your database using your SQL Server Management Studio you will have to allow connections through the Firewall, to change the firewall settings click on the ‘Firewall Settings’ tab.

Database_Firewall_AllowMSServices

In order for your Azure applications to be able to connect to your SQL Azure Database you will need to check the ‘Allow Microsoft Services access to this server’ checkbox. This adds the ‘MicrosoftServices’ Definition in the Rules grid.  If you don’t intend on using the SQL Azure database with a Windows Azure Application, keep the box unchecked.  To Allow you to access SQL Azure using SSMS you will have to Add a Rule.

Database_Firewall_AllowIPRange

The SQL Azure team did an excellent job making the Rules to be simple to configure.  Enter a name, which is a Label to Identify the IP Range you are allowing to connect.  Then specify an IP Range that you wish to allow access to this database.  You’ll notice that the application displays your IP Address, so if you only wish to add your IP simply copy and paste the IP address into both the Start and End textboxes. Then Click Submit to Save the changes. Repeat this step if you would like to be able to connect to your SQL Azure database from Multiple locations (Work, and Home).

One last piece of information you’ll need from the Azure Services site is the Connection String for your new Database. To get the Connection String, Navigate back to the Databases Tab. Select your new database from the list and click on the ‘Connection Strings’ button.

Database_ConnectionString

From here you can either select the entire Connection String and Copy using the Right Click context menu, or click on the ‘Copy to clipboard’ link (which may cause a Security pop-up in your browser) [Note: Choose the appropriate Connection String for your Application. I have only tested the ADO.NET Connection String in SSMS].  Paste your Connection String somewhere it is easy to work with, I prefer using ‘Sticky Notes’ a feature of Windows 7, but you can use notepad if you’re more inclined to do so.

If you haven’t done so Already, Fire up your installation for SQL Server 2008 R2 Management Studio. [The SQL Server instance in the cloud uses the TCP port 1433 so you will have to make sure that your Firewall will allow outbound traffic on TCP port 1433.]  As you normally would, Select Connect to Server from the Object Explorer.

SSMS_ConnectToServer

Fill out the Server name, Login and Password information [Note: The Server name, and login are contained in your Connection String that you pasted safely into Sticky Notes or Notepad.]  Your password you would have set while accessing the SQL Azure Menu for the first time. At this point it gets a little bit different than your typical connection to the database. If you were to connect now, you will only have access to the ‘master’ database which you do not have the ability to add tables to, not to mention you’ve created a nice new database to house your data for your current application. Here’s the trick to make sure you connect to the proper database on your SQL Azure account:

  • Click on Options at the bottom of the Connect to Server Screen.
  • Select the Additional Connection Parameter Tab
  • Paste in your trusty connection string [waiting idly by in Sticky Notes]
  • Change the password in the Connection String from myPassword to your actual SQL Azure password
  • Hit the Connect button to Connect to the database.

Now that we’re up and running lets add a few tables.  Being a .NET Developer myself I know that one of the existing .NET Features I’m going to want to leverage will be the ASP.NET Membership Provider. After giving the command-line utility a try, I found out that SQL Azure does not support a number of Commands that are contained in the Utility.  This includes use [cannot select other databases in the cloud], GRANT your login cannot GRANT or DENY privileges on the tables or stored procedures, nor is it able to Query against the master table.

No I wouldn’t bring this up just to let you down, here is a work around. Open the Visual Studio 20008 Command Prompt, and type the following:

aspnet_regsql.exe -A mpr -sqlexportonly <filePath>

-A: Application Services Options

  • P – Profiles
  • M – Membership
  • R – Role Manager

Then Obviously replace <filePath> with a Path where you can Save the SQL Output like “C:\SQL\ASP_NET_MemberShip_Tables.sql”.

This will allow you to run the SQL file against the database in the SSMS Query window. It will throw warnings about the invalid keywords but the tables and stored procedures get created regardless.

I wish you luck with your Deployments to Windows Azure, Stay Tuned for more blog posts on Developing on the Windows Azure Platform.