# Tuesday, December 17, 2013

For quite a while I was fortunate to share an office with Brady Gaster (@BradyGaster), currently a PM on the Windows Azure SDK team. Brady LOVES the idea of developing tools that make cloud development easy. Brady has spent the last year working on a set of management libraries that make it SUPER easy to manage your Windows Azure resources, including Windows Azure SQL Database.

Last week, Brady’s team released the SQL Management Library, loaded with features for managing your databases in Windows Azure SQL Database (WASD). I have seen and played with the SQL Database Management libraries, and I must say, Brady and his team did an OUTSTANDING job. Being a SQL guy, I have to admit that this library was well worth the wait. Some of the things you can do with library for SQL Database include:

  • Import and Export SQL Databases to and from WA Blob Storage
  • List your Windows Azure SQL Database Servers and associated databases
  • List Firewall rules for your Windows Azure SQL Database Servers and associated databases
  • Create, update, and delete Windows Azure SQL Database Servers, databases, and firewall rules

Brady blogged about this management library here.

What I like about this library is the miniscule amount of code it takes to work with WASD. For example,

SqlManagementClient _sqlManagementClient = new SqlManagementClient(
    new CertificateCloudCredentials(_host.SelectedSubscription.SubscriptionId, 
        new X509Certificate2(Convert.FromBase64String(_host.SelectedSubscription.ManagementCertificate))));
var getServerResult = await _sqlManagementClient.Servers.ListAsync();
ServerList = getServerResult.Servers;

3 LINES OF CODE! Getting a list of databases is just as simple:

var listDatabaseResult = await _sqlManagementClient.Databases.ListAsync(SelectedServer.Name);
Databases = listDatabaseResult.Databases;

Want to create a database? One line of code:

_sqlManagementClient.Databases.Create(servername, createparameters)

These libraries have full intellisense making then extremely easy to use.

What I also like about these management libraries is that, even though it requires working with X509 Certificates to authenticate, the WAML makes it extremely easy by using the *.publishsettings files that you can download from Windows Azure. You can download the .publishsettings file several ways including the PwerShell cmdlet Get-AzurePublishSettingsFile which provides a Base-64 encoded representation of an X509 certificate.

Brady created a sample app to demonstrate all the cool WAML (Windows Azure Management Library) features, and you can download it from GitHub. So, download it and play with it and let Brady and I know what you think.

Happy Coding!

Tuesday, December 17, 2013 9:18:08 AM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [3]  | 
# Tuesday, February 19, 2013

In the last post in this series I walked through the process of using the SQL Server Migration Assistant for Access to migrate an Access database to Windows Azure SQL Database. In this post I’ll walk through the process of creating a Windows Azure Mobile Service and then associating that new Mobile Service to the database imported in the last blog post.

Creating the Windows Azure Mobile Service

In the Windows Azure Management Portal, click the +New button at the bottom of the portal and select Compute –> Mobile Service –> Create, which brings up the New Mobile Service dialog. In the URL type of the name of the Mobile Service. In this example I named the service northwind. This name is important and will be used shortly.

SNAGHTML14461b5b[6]

Click Next.

On the Database Settings page, select the northwind database, then enter the username and password. Click Finish.

image

Modifying the Database Schema

At this point the Windows Azure Mobile Service is created but in order for the Mobile Service to access the SQL Database tables, the Mobile Service name must match the database schema associated to the tables imported in the previous post.

For example, the default schema is dbo and currently the existing tables are associated to the dbo schema. Since the name of the Mobile Service is northwind, the next step is to create a schema with the same name as the Mobile Service and re-associate the tables to the northwind schema. The following script does just that.

CREATE SCHEMA northwind;
GO
ALTER SCHEMA northwind TRANSFER [dbo].[Customers]
ALTER SCHEMA northwind TRANSFER [dbo].[Employees]
ALTER SCHEMA northwind TRANSFER [dbo].[InventoryTransactionTypes]
ALTER SCHEMA northwind TRANSFER [dbo].[InventoryTransactions]
ALTER SCHEMA northwind TRANSFER [dbo].[Invoices]
ALTER SCHEMA northwind TRANSFER [dbo].[OrderDetails]
ALTER SCHEMA northwind TRANSFER [dbo].[PurchaseOrderDetails]
ALTER SCHEMA northwind TRANSFER [dbo].[Orders]
ALTER SCHEMA northwind TRANSFER [dbo].[OrderDetailsStatus]
ALTER SCHEMA northwind TRANSFER [dbo].[OrdersStatus]
ALTER SCHEMA northwind TRANSFER [dbo].[OrdersTaxStatus]
ALTER SCHEMA northwind TRANSFER [dbo].[Privileges]
ALTER SCHEMA northwind TRANSFER [dbo].[Products]
ALTER SCHEMA northwind TRANSFER [dbo].[PurchaseOrderStatus]
ALTER SCHEMA northwind TRANSFER [dbo].[PurchaseOrders]
ALTER SCHEMA northwind TRANSFER [dbo].[Shippers]
ALTER SCHEMA northwind TRANSFER [dbo].[Strings]
ALTER SCHEMA northwind TRANSFER [dbo].[Suppliers]
ALTER SCHEMA northwind TRANSFER [dbo].[EmployeePrivileges]
ALTER SCHEMA northwind TRANSFER [dbo].[SalesReports]

After running the script, the database tables are now associated to the new schema.

image

The reason the Mobile Service was created first is to ensure that the Mobile Service name is available before creating the database schema. Otherwise, you might create the schema and then find out that the Mobile Service name isn’t available (already in use).

Associating the Existing Tables

Returning to the Windows Azure Management Portal, select the new Mobile Service and click the DATA tab. You will see that the tables still aren’t appearing in the portal. The next step is to “tell” Mobile Services about the existing tables. To do that, click on the ADD A TABLE link.

clip_image004

In the Create New Table dialog, enter the name of the tables as it exists in the SQL Database instance; for example, Employees. Leave the Permission options with their default. Click OK.

image

Repeat the process for the remaining tables. After each table is added, the DATA tab will automatically refresh after several seconds and display the table with the related number of indexes and number of records in the table.

image

Voila, the Microsoft Access database has been successfully migrated to Windows Azure Mobile Services. However, this is just only the beginning because the next few blog posts will walk through the process of creating a Windows Store app that connects to this Mobile Service.

Conclusion

These last three blog posts have walked through the process preparing and migrating a Microsoft Access database to Windows Azure SQL Database and creating a Windows Azure Mobile Service to use the new SQL Database instance. Depending on the complexity of the Access database, this process can be trivial or require a bit more work. The first two blog posts showed the process for an Access database that wasn’t too trivial but shows that it can be done.

On to the UI!

Tuesday, February 19, 2013 2:44:20 PM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [0]  | 
# Monday, February 18, 2013

In the last post I discussed how to prepare your Microsoft Access database for migration to Windows Azure SQL Database with the ultimate goal of putting a Windows Azure Mobile Service on it. In this post I’ll walk through the process of migrating the Access database prepped in the last blog post to Windows Azure SQL Database.

Setup and Configuration

This example is going to use the SQL Server Migration Assistant for Access (SSMA) to migrate the database from Microsoft Access to Windows Azure SQL Database. The SQL Server Migration Assistant for Access is a free supported tool from Microsoft that simplifies database migration from Access 97 and higher to SQL Server 2005 and higher, including Windows Azure SQL Database. SSMA for Access automates the conversion and migration of Access database objects to SQL Server database objects as well as migrating the data.

Installing the SSMA for Access will install both a 32-bit and 64-bit version of SSMA; you’ll see two SSMA icons on your desktop. More than likely you’re running the 32-bit version of Access (or Office), so this walkthrough will use the 32-bit version.

However, prior to running the SSMA for Access, there is another component that needs to be installed. The Microsoft Access 2010 Runtime is used by the SSMA for Access. It allows the SSMA for Access to read and interrogate any Microsoft Access database without the need to install the full version of Access 2010 or higher.

Lastly, the SSMA for Access assumes that a Windows Azure SQL Database already exists that it can migrate the Access database to. This walkthrough won’t get into how to provision a Windows Azure SQL Database server or create a SQL Database instance, but for information on that you can review the Hands-on-lab Introduction to SQL Database.

Migrating the Access Database to Windows Azure SQL Database

Start the migration process by running the 32-bit version of SSMA for Access. When SSMA launches, it will begin a wizard that walks through the migration process. First, click Next on the Welcome screen.

On the Create New Project page of the wizard, enter a project name and the location where the project will be saved. SSMA projects are files that contain metadata about the Access database you want to migrate, metadata about the target SQL Server or Windows Azure SQL Database instance you are migrating to, and project migration settings.

In the Migrate To field, select SQL Azure then click Next.

clip_image002

The second page of the wizard is where you select the Access database. Click the Add Database button to browse to the location where the NorthwindData database resides. Click Open on the Open dialog box. The Add Access Database page will show the location of the database selected.

clip_image003

Click Next.

The SSMA will then iterate through the Query and Table objects in the Access database and list them on the next page of the wizard. The tables will automatically be checked for migration. We are not concerned with the queries, so simply ensure that all of the Tables are selected and click Next.

clip_image005

The next page of the migration wizard is where the SSMA will connect to the SQL Database instance. On this page of the wizard, enter the SQL Database instance connection information. The SQL Database instance must already exist; the SSMA will not automatically create the SQL Database instance. However, clicking the Browse button will display a list of existing database plus an option to create a new database.

After entering the SQL Database connection information and clicking Next, the SSMA will try to connect to the specified SQL Database instance. If the SSMA can’t connect to the SQL Database instance, it will typically be for one (or both) of the following reasons:

  • One or more of the connection information entered is incorrect.
  • A firewall rule was not configured for the corresponding Windows Azure SQL Database server in the Windows Azure Management Portal.

The Encrypt Connection check box, checked by default, ensures that the connection to SQL Database is encrypted and secure. Leave this checkbox checked.

clip_image007

Once the SSMA has successfully connected to the SQL Database instance, the SSMA will do a quick object comparison between the objects selected for migration in the SSMA and any corresponding, existing objects in the SQL Database instance, then display the comparison results in a dialog. The dialog shows which objects will be synchronized (migrated) from Access to SQL Database.

Since the SQL Database instance was recently created there shouldn’t be any Table objects. Click OK on this dialog.

clip_image008

At this point the actual migration begins. The migration is a 3-step process. First, the SSMA will perform a conversion process which takes the object definitions from the Access metadata and converts them into equivalent T-SQL syntax. It then loads the T-SQL syntax into the project.

After the objects have been converted, the SSMA will automatically load (migrate) the schema for the selected objects to the target database, in this case, the SQL Database instance. Once the schema for the selected objects are migrated, the data is then migrated. When the migration is complete, the status of the migration will be shown.

clip_image010

During the conversion, load, and migration of the Access database to SQL Database, the SSMA will print current status to the output pane, and any errors, warnings, and information messages will be output to the Error List pane. You can use this information to determine whether you need to modify your Access database or your conversion process to address items in the Access database.

Click Close on the migration wizard, and exit the SSMA for Access. When exiting, you will be prompted to save the project. You don’t need to save the project but saving it might come in useful if you need to change some of the project settings or want to look at the generated T-SQL.

The results of the migration to Windows Azure SQL Database can be viewed by opening SQL Server Management Studio and connecting to the SQL Database instance. Expand the Tables node for the northwind database and verify that all of the tables have been migrated.

clip_image011

Summary

At this point we’re ready to create the Windows Azure Mobile service which we will do in the next post.

Monday, February 18, 2013 3:56:59 PM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [0]  | 

Windows Azure Mobile Services is a turnkey backend solution for making and delivering mobile and Windows store applications, providing an easy and efficient way to store structured data objects in the cloud. The goal behind Windows Azure Mobile Services is to allow developers to very quickly be able to connect their client applications, such as Windows 8 apps, IOs, or Android, to a cloud-based backend hosted on Windows Azure. Mobile Services provides the foundation for how you can build a richer client experience with the cloud.

Users of current Microsoft Access application should miss out on the “richer client experience”, so starting with this post, and over the next 4 or 5 posts, I will provide an end-to-end and step-by-step solution for moving the database to Windows Azure SQL Database, creating the Windows Azure Mobile Services, associating the new Mobile Service to the database, setting up and configuring security, creating a few pages in Mobile Services, and querying the underlying data.

In this first post, I’ll walk through the steps required to prepare the Access database for migration, and the database I’ll be using is the well-known Northwind database. Any Access database will work, but to get the full effect of a true migration, but the Northwind database has been around for a long time and is a widely-known sample database. 

Requirements, Differences, and Items of Interest

Before I get to the meat of this walkthrough, there are few items I need to point out when migrating a Microsoft Access database to Windows Azure SQL Database. First, Access allows spaces in table and column names. You’re thinking “Yes, but so does SQL Server”. Yes, that is true. Windows Azure SQL Database allows spaces in table and column names too. But Windows Azure Mobiles Services (WAMS) does not. As such, it is possible to import the Access database into a SQL Database instance, but Mobile Services will not recognize them without removing the spaces.

Also, each table must have a primary key column simply named ‘id’ (lower case). In fact when you create a new table in Mobile Services, the service will automatically add the ‘id’ column for you. However, for Access, this means some renaming of columns prior to the migration process.

Chances are that your Access database has table and/or column names with spaces. Meaning, instead of a column named FirstName, the column is named [First Name]. Plus, your primary keys more than likely are not simply named ‘id’. This is the other reason I am using the Northwind database. It is close to a “real-world” scenario.

The Northwind database has roughly 20 tables, and luckily a lot of them have their primary keys called ‘ID’. They are uppercase, but we can fix that. Since Access is a relational database, it has Primary Key / Foreign Key relationships which will need to be taken into consideration during the migration process.

clip_image002

Taking a quick look at the Employee table shows several things that need to be addressed:

  • Spaces: Nearly every column has a space in the column name, and some have forward slashes ‘/’ in their names.
  • Data Types: Access uses different than SQL Database. Most of the data types can be converted during the migration process. We’ll discuss the Attachments data type in another blog post.
  • Primary Key: The ID column name needs to be renamed to lower case.

clip_image003

Preparing the Access database for migration

So, with that, let’s begin. Preparing the Access tables and data for migration is a multi-step process:

  • Export the tables to text files
  • Apply the fixes in the text files
  • Import the text files into Microsoft Access

It is possible to migrate the Access tables as they currently are into Windows Azure SQL Database and then apply the fixes in SQL, but there are two main reasons why it will be easier not doing it that way. First, applying the fixes in SQL will require hundreds of sp_rename statements. Second, and more importantly, it is not as simple as calling sp_rename. If the object being renamed has a dependency on it, the rename will fail. For example, the [First Name] column in the employee table has a constraint on it. Trying to rename the column results in the following:

Msg 15563, Level 16, State , Procedure sp_rename, Line 497

Object 'dbo.[Employees].[Last Name]' cannot be renamed because the object participates in enforced dependencies

The fix would be to also script out all of the dependencies and fix those. What you will end up with is a fairly lengthy script to fix to rename the objects (to clear up the spaces) and fix the names in the dependent objects.

For this example, keeping the constraints isn’t important. I just want the data. I can always apply the constraints later when the tables and data are in the SQL Database instance.

The first thing to do is export all of the tables to text files. There are several ways to export the data. Not being an Access guy, the first thing that came to mind was to do the obvious and walk through the export wizard (right mouse click on the table and select Export -> Text File. However, Microsoft Access has Macros which help automate and group tasks, which is much easier.

NOTE: This walkthrough uses Access 2013 so if you’re using earlier versions, your menus and options might be a bit different.

The idea is to find and use a Macro that will easily export the tables to text file with a delimited format and with the column names on the first row of the text file.

Click the CREATE tab and then select the Macro button which opens the Logic Designer for creating Macros.

clip_image005

In the Logic Designer is a new Macro called Macro1 with a dropdown of available macro actions to add to the macro. The problem is that the only “export” option is called ExportWithFormatting. Also present is the Action Catalog window, to the right of the Macro, which likewise lists the macro actions available. Expanding the Data Import/Export node in the Action Catalog window shows the same list of available import and export actions that are available in the dropdown, including the ExportWithFormatting macro.

The export files needed for this walkthrough don’t need any special formatting, so the trick here is to ensure that the DESIGN tab is selected (it appears when working with macros) and click the Show All Actions button. New macro actions appear in the Action Catalog, which is perfect because the one needed for this walkthrough is called ImportExportText. Double-click the ImportExportText macro action and it will show up in the macro design surface and display argument boxes for the ImportExportText macro. Set the properties and arguments as follows:

  • Transfer Type: Export Delimited
  • Table Name: Access table name
  • File Name: Path where text file will be created
  • Has Field Names: Yes

Add the ImportExportText action to the macro design surface once for each of the tables.

clip_image007

It is important in the step to make sure that the file name entered in the File Name property does not include spaces. As shown in the above figure, the Employee Privileges file name is EmployeePrivileges.txt. Not including spaces in the file name will make the import process much easier.

Once all of the export actions have been added, click the Run button on the toolbar. This will generate a file for each table which includes the data, as well as the column names on the first row. The columns are comma separated and use the double quotes (“”) as the text qualifier.

The next step in the process is to clean up the spaces in the names. For tables, that was taken care of by specifying the file names without spaces in the export process above. For columns, the process is a bit more laborious. Open each text file, and remove all of the spaces in the column names in the first row.

Next, ensure that any primary key columns are simply called ‘id’ (lower case without the quotes). Since the primary key column in the Employee table was already called ‘ID’, the change was simply changing it to lower case.

clip_image009

The Invoices table, for example, is a little different; its primary key column is named ‘Invoice ID’. Same thing here; simply need to change that to ‘id’.

Importing the Text Files

The next step is to import the text files back into Access. There are several reasons for this. First, it will be easier to migrate the data into Windows Azure SQL Database from Access. Second, exporting the data to text files to clean them means that primary key and auto-number information was lost.

The quickest and easiest way to address this is to create a new, blank desktop Access database. For this walkthrough, I called it NorthwindData. This is the database the data will be imported into to fix the primary key and auto-number issues.

clip_image010

The process for importing the data is the same for exporting the data using the ImportExportText action in a macro. The only difference here is setting the Transfer Type property to Import Delimited (which is the default action). The import properties should be set as follows:

  • Transfer Type: Import Delimited
  • Table Name: New Access table name
  • File Name: Path to the corresponding text file
  • Has Field Names: Yes

Add the ImportExportText action to the macro design surface once for each of the tables. Once all of the import actions have been added, click the Run button on the toolbar which will import each file.

clip_image012

Once the text files are imported as tables into the new Access database, the next step is to quickly go through each table and add the primary key back. Open each table, select the ‘id’ column, and click the Primary Key button on the toolbar.

clip_image013

The AutoNumber data type cannot be set here because there is already data in the table. That’s OK because this can be addressed after the table is migrated to Windows Azure SQL Database.

Now, you’ll probably notice as you go through the tables setting the primary key that exporting to, and importing from, a text file reset some of the data types on some of the columns. For example, the Notes field in the original database is a Long Text, but in the new database it is a Short Text. The Attachments column is no longer an Attachment data type but also a Short Text.

Not to worry, because as we’ll see in the next blog post, the migration tool we will use will help us with that.

clip_image014

The last step is to reset the relationships. Select the DATABASE TOOLS tab then click the Relationships button. Add the tables to the designer and recreate the relationships as they exist in the original Northwind database.

clip_image016

Summary

The intent of this post was to focus preparing a Microsoft Access data for migration to Windows Azure SQL Database which will ultimately be used as a backend for Windows Azure Mobile Services. In the next blog post we’ll actually walk through the migration process.

Monday, February 18, 2013 3:40:57 PM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [0]  | 

Windows Azure Office Hours are Back!

Have a question about Windows Azure? Join myself and Mike Benkovich every Friday at 9:00 AM PST where we open the doors to the masses in a fast paced and deep conversation into the technologies regarding Windows Azure. Each week we’ll try and have a special guest from Microsoft from the Windows Azure product group. On Friday, February 22nd, our special guest will be Josh Twist, Program Manager on the Windows Azure team and first full-time member of the Mobile Services project. On Friday, March 1st, our guest will be Scott Hunter, Principle PM Lead for ASP.NET.

We’ll also be recording each session so you will be able to check out past Office Hours Online conversations.

Visit http://www.benkotips.com/OfficeHrs for information and to join the meeting and post questions which will be answered during the session.

Monday, February 18, 2013 11:30:00 AM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [0]  | 
# Tuesday, January 29, 2013

My Spring and Summer are quickly filling up with speaking engagements. My list so far:

  • February 9th at the South Florida .NET Code Camp. Topic: Cloud Data for the everyday developer
  • February 27th at Confoo. Topic: Cloud Data for the everyday developer
  • March 5-7 at TechDays Belgium. Topic: Kung-fu migration to Windows Azure SQL Database
  • March 8 at TechDays Netherlands. Topics: Kung-fu migration to Windows Azure SQL Database & Cloud Database scalability with SQL Federations
  • April 9 at Stacked. Topic: Kung-fu migration to Windows Azure SQL Database
  • April 17-19 at DatabaseDays 2013. Topic: Cloud Database scalability with SQL Federations

I’m waiting to see if any of my session for SqlBits were approved. Show my sessions some love and vote for them at https://www.sqlbits.com/Speakers/Scott_Klein1.

And this list doesn’t include all of the user groups I’m scheduled to speak at. I’m looking forward to speaking back in my old stomping grounds at the South Florida SQL Server users group. I think Tampa is in there somewhere as well. While in Europe I’m looking forward to seeing all my friends while speaking at Windows Azure user groups in Belgium and the UK.

See you all out on the road!

Tuesday, January 29, 2013 10:14:00 AM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [0]  | 
# Monday, December 17, 2012

We just release a new sample application for Bing Maps. This new sample application uses HTML plus the ASP.NET Web API to illustrate the use of the new DBGeometry and DBGeography support in the entity Framework. The database can be either an on-premises SQL Server database or a Windows Azure SQL Database instance. You can find the download here: HTML + ASP.NET Web API Bing Map Sample Application.

The ASP.NET Web API is a framework that allows for rapid development of HTTP services on top of the .NET Framework. The sample application combines ASP.NET MVC 4 and Web API, JQuery, Entity Framework 5, and the Spatial data type in SQL Server / Windows Azure SQL Database to highlight the use of DBGeometry and DBGeography support in the Entity Framework by using Bing Maps. This sample uses Entity Framework’s Code First feature, thus there is no need to create the supporting database before hand; the Entity Framework will automatically create the database for you when you first run the application.

We simply use two calls to place the pin on the map as well as add the location to the database. First, we add the pin to the map:

   1: function AddToMap(latitude, longitude, name) {
   2:     // Create a pin at that location, list the latitude & longitude
   3:     var location = new VELatLong(latitude, longitude);
   4:     var pin = new VEShape(VEShapeType.Pushpin, location);
   5:     pin.SetTitle(name);
   6:     pin.SetDescription("Lat:" + latitude + " Long: " + longitude);
   7:     map.AddShape(pin);
   8:  
   9:     var link =
  10:         "<a href='javascript:map.SetCenter(new VELatLong(" +
  11:         latitude +
  12:         "," +
  13:         longitude +
  14:         "));'>" +
  15:         name +
  16:         "</a>";
  17:  
  18:     $("#results").append(
  19:         $("<li>").append(link));
  20:  
  21:     $('#txtWhere').focus().select();
  22: }

Second, we add the location to the database:

   1: function SaveToDB(latitude, longitude, name) {
   2:     // Note: We're using this format to save as it's the same format returned from /api/location/1
   3:     var loc = {
   4:         Name: name,
   5:         Latitude: latitude,
   6:         Longitude: longitude,
   7:         Location: {
   8:             Geography: {
   9:                 CoordinateSystemId: 4326,
  10:                 WellKnownText: "POINT (" + longitude + " " + latitude + ")",
  11:                 WellKnownBinary: null
  12:             }
  13:         }
  14:     };
  15:     var json = JSON.stringify(loc);
  16:  
  17:     $.ajax({
  18:         url: '/api/place',
  19:         cache: false,
  20:         type: 'POST',
  21:         data: json,
  22:         contentType: 'application/json; charset=utf-8'
  23:     });
  24: }

The results are then displayed on the map. We also pull any saved locations from the database and add them to the map when the application starts.

Pic1

More information is provided in the Readme that is included in the download. We are always interested in your feedback and thoughts, so please don’t be shy.

Monday, December 17, 2012 3:53:29 PM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [0]  | 
# Saturday, December 8, 2012

Earlier this year we published a version of the AdventureWorks2012 database for Windows Azure SQL Database (AKA SQL Azure). Nearly 7000 downloads since then, but there were a few problems discovered with some of the identities being generated, along with some comments regarding the installation not working on Windows 8.

Today I published an updated version of the AdventureWorks2012 for Windows Azure SQL Database install. The updated version can be downloaded here:

AdventureWorks for Windows Azure SQL Database

This update fixes the following:

  • Identities – When the data was being BCP’d into the tables, BCP was not keeping the original identity values and thus identities were being reseeded starting at 1. This has been fixed in this release by passing the –E parameter on the BCP IN which keeps the original identity values.
  • vPersonDemographics view – This view was not installed in the previous version of the download because there was an issue with it running in Windows Azure SQL Database. The issue has been fixed and the view is now installed in this release.

Regarding the issue of the install not running on Windows 8, you need to be sure to run the command prompt as an administrator. We have successfully tested these installs on Windows 8.

As always, please send us any feedback or requests for further samples.

Saturday, December 8, 2012 1:26:00 PM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [7]  | 
# Friday, December 7, 2012

Yesterday a small team of us released three new sample applications to the Windows Azure Code Samples gallery that illustrate how to use the DAC Framework and the Windows Azure SQL Database Import/Export Service programmatically.

Windows Azure SQL Database Import/Export Service Sample Application

Illustrates how to use the Windows Azure SQL Database Import/Export service to export a Windows Azure SQL Database instance to Windows Azure Blob storage as a BACPAC, then import that same BACPAC from Windows Azure Blob storage into a new Windows Azure SQL Database instance.

Pic1

DAC Framework + SQL Database Import/Export Sample Application

Illustrates an end-to-end migration of an on-premises SQL Server database to Windows Azure SQL Database. This sample application uses the following technologies:

  • Data-Tier Application Framework (DACFx)
  • Windows Azure Storage Client Library
  • Windows Azure SQL Database Import/Export Service

Pic1

The Windows Azure SQL Database Import/Export Service provides import and export operations to copy databases between Windows Azure SQL Database servers, or to migrate databases between on-premises SQL Server and Windows Azure SQL Database. The Import/Export Service requires the use of a Windows Azure storage account, as BACPAC files are stored here during the copy/migration process. As such, this sample application exports an on-premises SQL Server database to a local artifact using the DAC Framework. The Windows Azure Storage Client Library is then used to upload the .bacpac artifact to Windows Azure Blob storage. The Import/Export Service then imports the .bacpac into a new Windows Azure SQL Database instance.

DAC Framework Direct Sample Application

Illustrates how to use the DACFx (DAC Framework) to programmatically accomplish several of the DAC features available in SQL Server Management Studio in SQL Server 2012, including:

  • Extract DACPAC - a database snapshot file (.dacpac) from a live SQL Server or Windows Azure SQL Database containing the database schema and optionally the data from the user tables inside the database.
  • Deploy DACPAC - Deploy a DACPAC to create a new database or incrementally update an existing SQL Server or Windows Azure SQL Database.
  • Export BACPAC - Creates a logical backup file (.bacpac) of a live SQL Server or Windows Azure SQL Database instance containing the database schema and user data which can then be used to migrate from on-premises SQL Server to Windows Azure SQL Database.
  • Import BACPAC - Imports the schema and data from a BACPAC package into a new SQL Server or Windows Azure SQL Database.

Pic1

We are always interested in your feedback, so please let us know if you would like to see additional migration topics, or anything related to Windows Azure SQL Database.

Friday, December 7, 2012 1:44:36 PM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [4]  | 
# Wednesday, December 5, 2012

Here is the detailed information on new, reduced pricing for both Geo and Locally redundant storage (prices below are per GB per month):

 

Geographically redundant

Locally Redundant

First 1 TB / Month

$0.095

$0.070

Next 49 TB / Month

$0.080

$0.065

Next 450 TB / Month

$0.070

$0.060

Next 500 TB / Month

$0.065

$0.055

Next 4,000 TB / Month

$0.060

$0.045

Next 4,000 TB / Month

$0.055

$0.037

Over 9,000 TB / Month

Contact Microsoft

Contact Microsoft

Windows Azure Storage accounts have geo-replication on by default to provide the greatest durability. Customers can turn geo-replication off to use what we call Locally Redundant Storage, which results in a discounted price relative to Geo Redundant. You can find more information on Geo Redundant Storage and Locally Redundant Storage here.

Wednesday, December 5, 2012 7:32:00 PM (Pacific Standard Time, UTC-08:00)  #    Disclaimer  |  Comments [3]  |