A Big O for OData- Mix Keynote Day Two

Oh Hungry? Oh Oh Oh, OData! Today Microsoft announced the Open Data Protocol [Read more at odata.org] at the MIX Conference. The Open Data Protocol is an extension of the ATOMPub format.  The OData Information can be can be represented in ATOM or JSON Format.

One of the key features of OData is that each element contains a datatype so that the data can be consumed by a number of different platforms including Java, javascript, Python, Ruby, PHP, and .NET without running into type safety issues, or misrepresenting the data in a string format.

The Open Data Protocol complements the new SQL Azure Platform nicely, allowing developers of popular website development languages expose data to their applications with ease.  Today’s Keynote at Mix showed the web development world that Microsoft has enabled “Checkbox Driven Development” as SQL Azure allows you to expose an OData representation of your Data with a few simple clicks of a Checkbox, and a series of Drop Down Lists. Of course the term “Checkbox Driven Development” is a joking term by Speaker Doug Purdy, this Solution is a streamlined process, but there is still a lot of work to be done to consume the exposed OData Source.

Microsoft’s Codename “Dallas” uses the OData Protocol to expose the Data as a Service Information. If you want more information about Codename “Dallas” please refer to one of my previous posts named “Synchronizing a Local Database with the Cloud using SQL Azure Sync Framework”.

For Additional Resources please refer to the links below

• OData interoperability with .NET, Java, PHP, iPhone and more
• Mix10 Resources
• Open Data for the Open Web

Contests Regarding Open Data

• For The Web Competition
• Juicy Ideas Competition

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.]

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.

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.

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.

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.

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.

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.

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.

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!