Blog

Migrating Spatial Data from SQL Server 2008 to SQL Azure

[Infragistics] Mihail Mateev / Tuesday, September 21, 2010

Introduction

SQL Azure now supports Spatial Data. Unfortunately not all  tools offer migration of spatial data..

Microsoft SQL Management Studio for SQL Server 2008 R2 is released when SQL Azure didn’t support spatial data and now they are no updated to
support this feature.

One possibility is to use SQL Azure Migration Wizard: http://sqlazuremw.codeplex.com/

The actual version is SQL Azure Migration Wizard v3.3.7.

SQL Azure Migration Wizard offers a simple way to import Sql Server data, including a spatial data (from geometry or geography type).
More detailed information how to publish a Windows Azure you could find in the article:
“How to publish your Windows Azure application right from Visual Studio 2010”

This post explains mainly how to migrate spatial data and change configuration changes to be possible
to use a spatial data in SQL Azure from Windows Azure Application

Sample Application

Demo application is a Silverlight application with Infragistics XamMap control, that uses spatial data from SQL Azure.

The sample application is based on application from article “Using Infragistics XamMap Silverlight/WPF control with SQL Server Spatial”

 

Requirements to build a sample application:

  1. Visual Studio 2010
  2. SQL Server 2008 R2 Express or higher license.
  3. NetAdvantage for Silverlight Data Visualization 2010 vol.2
  4. http://ko.infragistics.com/dotnet/netadvantage/silverlight/data-visualization.aspx#Downloads
  5. SQL Azure Migration Wizard v3.3.7.
  6. The most recent version of Windows Azure Tools for Visual Studio
  7. Windows Azure account

 

Steps to create the sample application

  1. Create a new SQL Azure database
  2. Migrate a sample database – SqlSpatialDemo from Local SQL Server  to SQL Azure
  3. Create a new Windows Azure Storage Account and a Hosted Service
  4. Add a Web Role project in Solution
  5. Edit settings in Web.config and ServiceReferences.ClientConfig
  6. Publish the Windows Azure Cloud Service:
  7. Run the demo application

Create a new SQL Azure database

Log in Windows Azure with your account

 

Select SQL Azure->Database to see a list with existing databases

 

Select “Create Database” and choose the name and size of
the new database.

 

A new database is added in the list with existing

 

Migrate a sample database – SqlSpatialDemo from Local SQL Server  to SQL Azure

Edit configuration of the SQL Migration Wizard in SQLAzureMW.exe.config

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <configuration>
   3:     <appSettings>
   4:         <add key="SourceConnectNTAuth" value="true"/>
   5:         <add key="SourceServerName" value=".\SQLEXPRESS" />
   6:         <add key="SourceUserName" value="" />
   7:         <add key="SourcePassword" value="" />
   8:  
   9: <add key="TargetConnectNTAuth" value="false"/>
  10: <add key="TargetServerName" value="tcp:MyDatabaseServerName.database.windows.net"/>
  11: <add key="TargetUserName" value="DatabaseUser@MyDatabaseServerName"/>            <!-- NOTE BCP requires that you have the SQL Azure server name the end of the user name ie. UserSA@jy8kadhrma -->
  12: <add key="TargetPassword" value="MyPassword"/> 
  13:  
  14:  <add key="BCPFileDir" value="c:\SQLAzureMW\BCPData" />         <!-- Input / Output file location for BCP table data.  If blank, then will use temp directory -->
  15:  <add key="DelOldBCPFiles" value="true" />                      <!-- Deletes old BCP files if exists, otherwise, it will add a unique numeric ext -->
  16:  <add key="BCPFileExt" value="dat"/>                            <!-- BCP output file extension -->
  17:  <add key="DeleteBCPOutputFiles" value="false"/>                <!-- If true, SQLAzureMW will delete the temporary BCP output files after successfully uploading to target server --> 
  18:  
  19: ….
  20:  
  21:     </appSettings>
  22: </configuration>

To work migration wizard properly need to set TargetUserName with user@ServerName format:
<add key="TargetUserName" value="DatabaseUser@MyDatabaseServerName"/>

Start SQL Migration Wizard and select “Analyze an Migrate option

 

Log to the local SQL server

 

Select the database that want to migrate

 

For “Choose Objects” select “Script all database objects”.

  

In “Advanced Settings” ensure that for “Script Table / Data” is selected option: “Table Schema with Data”.

 

Connect to SQL Azure server.

 

Select the created database where plan to migrate the local database.

 

Select “Next” and wait until migration is complete.

 

 

 Open Microsoft SQL Management Studio, connect to SQL Azure server, select
migrated database and create a new query.

Write select * from world

 

In the “Spatial results” tab there is a map of the world.

 

Create a new Windows Azure Storage Account and a Hosted Service.

Select “New Service”

 

Create a storage account

 

 

 

Create a Hosted Service in the same way.

 

 

Add a Web Role project in Solution

Add a new Windows Azure Cloud Service project.

 

Skip creating of new Web Role in this project.

Add a Web Role project in Solution.

 

If you want to add new Web Role project there are additional setting that must be done.
Steps are describe in the article: Windows Azure Web Role Deployment Checklist

In this sample there is no need to do that.

Edit settings in Web.config and ServiceReferences.ClientConfig

Open Web.config and change the connection string:

   1: <appSettings>
   2:   <add key="token" value="[YourToken here]"/>
   3:   <!--<add key="connectionString" value="Data Source=.\SQLEXPRESS;Initial Catalog=SqlSpatialDemo;Integrated Security=True"/>-->
   4:  
   5: </appSettings>
   6:   <connectionStrings>
   7:     <add name="SqlSpatial"
   8:           connectionString="Server=tcp:[MyDatabaseServerName].database.windows.net;Database=[Database Name];User ID=[DatabaseUser]@[MyDatabaseServerName];Password=[MyPassword];Trusted_Connection=False;Encrypt=True;Min pool size = 5; Max pool size = 100"
   9:           providerName="System.Data.SqlClient"
  10:          />   
  11:   </connectionStrings>

 

In the Silverlight application modify the ServiceReferences.ClientConfig file:

Change the endpoint address with an expected one when application is published:
for example: http://MyAzureApplication.cloudapp.net/SqlDbService.svc

where MyAzureApplication.cloudapp.net is the endpoint  address of your application.

   1: <client>
   2:     <!--<endpoint address="http://localhost:4159/SqlDbService.svc" binding="customBinding"
   3:         bindingConfiguration="CustomBinding_SqlDbService" contract="SqlDbServiceReference.SqlDbService"
   4:         name="CustomBinding_SqlDbService" />-->
   5:   <endpoint address="http://MyAzureApplication.cloudapp.net/SqlDbService.svc" binding="customBinding"
   6:                             bindingConfiguration="CustomBinding_SqlDbService" contract="SqlDbServiceReference.SqlDbService"
   7:                             name="CustomBinding_SqlDbService" />                    
   8: ...
   9: </client>

 

 Publish the Windows Azure Cloud Service:

Add a reference to Microsoft.SqlServer.Types library from SQL Server 2008 R2 SDK.

 

Change in properties option “Copy Local” to true.



We need it because Windows Azure doesn’t know about Spatial types
(geometry and geography).

Create a certificate for authentication: for more details you could look at the article: “How to publish your Windows Azure application right from Visual Studio 2010”

Publish the Windows Azure Cloud Service

 

Run the published application and enjoy!

 

Source code of the demo application you could find here: