12 Comments

The Windows Azure SDK team has been working hard to finish up more of the Windows Azure Management Libraries so you can use .NET code to manage your Windows Azure resources, and today’s blog will highlight one of the newest additions to the WAML stack. Today I’ll introduce you to the Windows Azure SQL Database Management Library. We released the SQL Management Library this week, and it’s loaded with features for managing your Windows Azure SQL Databases. Like the rest of the management libraries, the SQL library provides most of the functionality you’d previously only been able to do using the Windows Azure portal, so you’ll be able to write .NET code to do pretty much any level of automation against your SQL databases and servers.

Here’s a list of some of the features supported by the new SQL library:

  • Create, update, delete, and list your Windows Azure SQL Servers
  • Create, update, delete, and list your Windows Azure SQL Databases
  • Create, update, delete, and list your Windows Azure SQL Servers’ Firewall Rules
  • Import and Export SQL databases to & from Windows Azure Blob Storage

Let’s dive in to some of these features by exploring their usage in the sample code I’ve written to demonstrate all these cool new WAML features. I’ve put this code up in GitHub so you can fork it, add your own functionality and test cases, and [please, feel free] spruce up the UX if you’re so inclined. The code for this demonstration includes the demonstrations from my previous blog posts on WAML, so if you’ve been wanting to see that code, you can dive in as of this latest installment in the blog series.

The WPF app shown below sums up today’s code demonstration for using the SQL management library. Once I select my publish settings file and select the subscription in which I want to work WAML makes a REST call out to Windows Azure to retrieve the list of database servers in my subscription. I data-bind a list view in the WPF app with the list of servers. Next to each you’ll find buttons to allow for convenient access to the databases residing on the servers, as well as a button giving you access to the servers’ firewall rules.

SNAGHTML1a48f73

Creating the SQL Management Client

As with the other demonstrations, the first step is to create an instance of the SqlManagementClient class using a subscription ID and an X509 Certificate, both of which are available in my publish settings file. The code below is similar to the manner in which the other management clients (Compute, Infrastructure, Storage, and so on) are created.

image

SQL Server Operations

The code below shows how, in two lines of code, I can get back the list of servers from my subscription. Once I’ve got the list, I set a property that’s data-bound in the WPF app equal to the list of servers the REST API returned for my subscription.

image

Database Operations

Once a user clicks the “Databases” button in one of the server lines in the WPF app a subsequent call is made out to the Windows Azure REST API to pull back the list of databases running on the selected server. 

image

You can do more than just list databases using the SQL management library – you have full control over the creation and deletion of databases, too, using the easily-discoverable syntax. Below, you’ll see how easy it is to figure out using nothing more than the Intellisense features of Visual Studio to figure out how to create a new database.

image

Firewall Rules

You no longer need to load up a server in the portal to manage your firewall rules – you can do that using the SQL management library, too. The code below demonstrates how easy it is to retrieve the list of firewall rules for a particular SQL Servers. When the user hits the “Firewall Rules” button in the WPF app, this code runs and loads up the rules in the UX.

image

In addition to the SQL library, we’ve also released the preview libraries for Media Services and Service Bus, too. We’re continuing to improve the landscape of WAML every day, and have some convenience features we’re adding into the overall WAML functionality set, too. Keep watching this blog for more announcements and tutorials on the Windows Azure Management Libraries. As always, if you have any suggestions or ideas you’d like to see bubble up in the management libraries, feel free to post a comment below.

Happy Coding!

Comments

Comment by Barbara Doschele

This is one of the the best projects enabled by Windows. I wish they'll pay more attention to other parts of their conglomerate. They need the same passion in their lab creation dept. Did not mean to rant, your article is right on point and well presented.

Comment by Ndoba

Wonderfull.
This is so usefull.
Thanks guys, good job

Ndoba
Comment by SEO Hosting

I was looking for something like this, so that I can learn more about SQL library and then teach my student about it. I will suggest them to read this post. Thanks a lot.

Comment by Adrian

I keep getting a "ResourceNotFound: The Uri doesn't match any specific service operation.".
I am parsing the account info straight from my publish settings file and double checked the requesturi which was correct yet I can't seem to get passed this.

Comment by brady gaster

Adrian - what method are you trying to call when you see this error?

brady gaster
Comment by Chris Sidi

Brady - thanks for documenting this library. Do you know how to change the "allowed services" configuration for a SQL database server, so all Azure services can connect to database? Is there a firewall rule that's equivalent to changing that setting in the Management Portal UI?

Chris Sidi
Comment by brady gaster

Chris - Create a firewall rule with a begin and end IP address of 0.0.0.0 and you'll get what you're after.

brady gaster
Comment by Marco

I downloaded the nuget package and the sample code, I think it's a very usefull library to create a custom management tool for Azure.
I can't found documentation about this library. For example: can I export the database in the Windows azure storage ?
Thanks

Marco
Comment by Abhishek

Hey Brady : thanks for the great effort here.
We are trying to use the export dac from sql azure to Blob feature here _sqlManagementClient.Dac.Export("", exportParam) however it fails with error Microsoft.SqlServer.Management.Dac.Services.ServiceException:Unable to resolve server name juiovd0jde.; Inner exception System.Net.Sockets.SocketException:The requested name is valid, but no data of the requested type was found; </P></DIV></BODY></html>.
We have verified that provided server does exist and verified on the exportparam too.
Do you have an idea what is missing here.

Abhishek
Comment by Abhishek

Resolution of the above issue : While declaring " DacExportParameters.ConnectionInfoParameter" , server name should be qualified name with "database.windows.net".

Abhishek
Comment by Marcus

Great article! I'm trying to make a Azure DB copy via a worker role, is there a simple way of doing this with using this library?

Marcus
Comment by Jorge

Hi Brady! Congratulations on valuable information that provides your blog.
I doubt arise in implementing a policy of retries from TransientFaultHandling object.
Now, I need to create a new class (HttpTransientErrorDetectionStrategy) inherited from Microsoft.WindowsAzure.Common.TransientFaultHandling.ItransientErrorDetectionStrategy
You know if you can use the object RetryPolicy Microsoft.WindowsAzure.Management.Sql without creating additional classes as seems to happen in Microsoft.WindowsAzure.Storage.RetryPolicies
Thank you very much.

Jorge
Post comment