exeGesIS Spatial Data Management

 

Moving a SQL server database to Azure

 

Not all SQL features are supported:  http://searchsqlserver.techtarget.com/feature/Why-you-should-think-twice-about-Windows-Azure-SQL-Database

Prerequisites

You need SMSS for SQL 2012.  This can be downloaded here as just the tool: http://www.microsoft.com/en-us/download/details.aspx?id=35579 (select SQLManagementStudio_x64_ENU.exe or x32 as appropriate)

This is probably easiest done from an Azure VM where you have temporarily mounted the database (meaning you hast have to push backup to Azure)

Getting your database into an Azure database

This is not as easy as it seems – especially for a big database due to bandwidth issues.

I found (working with limited bandwidth) the best solution was to place the database on a temporary Azure SQL VM (via a normal backup) from where i could then move it into Azure DB

There are (at least) 3 ways to do the migration.

Using the Deploy Database to SQL Azure Wizard

This needs SSMS 2012 – and is the easiest for a simple database that passes all Azures limitations

You need an Azure database account and this simply creates another database within that server and copies the database there.

To use this wizard, open SQL Server Management Studio (SSMS) and connect to the server. Right click the database you want to move, click Tasks, Deploy Database to SQL Azure...

NOTE:  This export will fail if you have any unsupported features or errors in your schema.  This includes:

Alternatively export your existing database to bacpac file

Right mouse click on your database. Choose Tasks | Export Data-tier Application. You will be given the opportunity to name both the bacpac file and the container name. Recall that the Bacpac file is a blob file in Windows Azure Storage.

This is similar to the previous method but gives you slightly more control

http://blogs.msdn.com/b/brunoterkaly/archive/2013/09/26/how-to-export-an-on-premises-sql-server-database-to-windows-azure-storage.aspx

Use the  codeplex export tool

http://sqlazuremw.codeplex.com/releases/view/32334

This tool will script out the structure and the database.  The main advantage is that it will try and fix up any unsupported Azure features your database is using and is by far the most flexible.

Possible issues

There are a number of these

Collations

When you create database – make sure you use the same collation value – but note that this doesn’t actually set the database collation itself as this is fixed.  This can be a serious limitation.

The default collation for character data in SQL Azure databases is SQL_Latin1_General_CP1_CI_AS. This collation is also used across the SQL Azure infrastructure to sort and compare metadata that defines database objects. The server and database level collations are not configurable in SQL Azure. However, you can use a collation of your choice at the column and expression level

http://blogs.msdn.com/b/sqlazure/archive/2010/06/11/10023650.aspx

Clustered Indexes

All tables must have a clustered index – so “SELECT INTO …” will fail.  This is likely to be an issue for much use of temporary tables and needs to be changed to a CREATE TABLE and then INSERT INTO - http://blogs.msdn.com/b/sqlazure/archive/2010/05/04/10007212.aspx

Others

Manual fix ups required for my example database 

  • Built-in function 'suser_name' is not supported in this version of SQL Server 
  • Collations all needed swapping to match database SQL_Latin1_General_CP1_CI_AS (knock on effects??) – did global replace in script to get it to upload

Azure connection string.

Username must have server name appended to it:

e.g. username@myAzureServerName

For odbc (e.g. mapserver) its fussy about the driver and I had to specify

CONNECTION "Driver={SQL Server Native Client 11.0};Server=tcp:abc.database.windows.net,1433;Database=MyDB;Uid=Me@abc;Pwd={MyPassword};Encrypt=yes;Connection Timeout=30;"

Comments

https://www.esdm.co.uk/moving-a-sql-server-database-to-azure