Import to Azure SQL Database from SQL 2008.

A customer asked us for guidance on how to migrate to Azure SQL Database from SQL Server 2008. The options listed below are absolutely the same for all CTP releases of SQL Server up to September 2015.

It is important to mention that SQL Server 2014 Express includes full version of SQL Server Management Studio for free. You can download Microsoft SQL Server 2014 Express below (note that the link will only work in IE)

https://msdn.microsoft.com/en-us/sqlserver2014express.aspx

Create Azure SQL Database. Performance Tier S0.

clip_image002

Enable access for our IP

clip_image003

Success!

clip_image004

Test Connectivity

For V12 database server the only option is to connect from VS 2013 community edition.

https://www.visualstudio.com/products/visual-studio-community-vs

clip_image006

Using SSMS 2008 R2

Conn string: d32gialud1.database.windows.net,1433

Can connect easily using the connection string and username/password

clip_image007

VS 2013 Community edition

Connect to Azure subscription first

clip_image008

For multiple Subscription you can use Manage Subscriptions menu item:

clip_image009

Install sample databases on SQL 2008 R2 SP1

Install Northwind and pubs, they are nice and small.

Northwind and pubs Sample Databases for SQL Server 2000

http://www.microsoft.com/en-us/download/confirmation.aspx?id=23654

clip_image010

Install Northwind. Ignore the errors, since I created Northwind database before.

clip_image011

Install pubs

clip_image012

Add clustered indexes to the tables:

ALTER TABLE CustomerDemographics ADD RecordID int IDENTITY(1,1) NOT NULL;

CREATE CLUSTERED INDEX IX_CustomerDemographics_RecordID ON dbo.CustomerDemographics (RecordID);

ALTER TABLE CustomerCustomerDemo ADD RecordID int IDENTITY(1,1) NOT NULL;

CREATE CLUSTERED INDEX IX_CustomerCustomerDemo_RecordID ON dbo.CustomerCustomerDemo (RecordID);

ALTER TABLE Region ADD RecordID int IDENTITY(1,1) NOT NULL;

CREATE CLUSTERED INDEX IX_Region_RecordID ON dbo.Region (RecordID);

ALTER TABLE Territories ADD RecordID int IDENTITY(1,1) NOT NULL;

CREATE CLUSTERED INDEX IX_Territories_RecordID ON dbo.Territories (RecordID);

ALTER TABLE EmployeeTerritories ADD RecordID int IDENTITY(1,1) NOT NULL;

CREATE CLUSTERED INDEX IX_EmployeeTerritories_RecordID ON dbo.EmployeeTerritories (RecordID);

Install Azure Storage Explorer

http://azurestorageexplorer.codeplex.com/

Create Storage Account https://ppdbstorage.blob.core.windows.net/

clip_image013

Upload bacpac to Azure Storage

clip_image015

Actual Export.

Using SQL Server Database Tools for VS

You will need to install VS2013 community edition.

https://msdn.microsoft.com/en-us/data/hh297027

https://msdn.microsoft.com/en-us/dn864412

clip_image017

After installing extensions letís find the sqlpackage.exe

clip_image019

Latest one from 18/03/2015 is here: C:\VS2013\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120

Letís run the command:

TEMPLATE: sqlpackage.exe /a:Export /ssn:SERVERNAME\sqlexpress /sdn:SOURCEDATABASENAME /su:DATABASEUSER /sp:DATABASEPASSWORD /tf:c:\SOURCEDATABASENAME.bacpac

Actual command:

sqlpackage.exe /a:Export /ssn:miniserver\sql2008r2 /sdn:Northwind /su:sa /sp:order21 /tf:c:\northwind.bacpac

clip_image021

Using SSMS 2014

clip_image022

clip_image024

Choose Deploy Database to Windows Azure SQL Database.

clip_image026

Connect to Azure SQL Database

clip_image027

Next

clip_image029

Northwind is the old database and does not have proper clustered indexes:

clip_image031

Fixed database, rerun the package:

clip_image033

Import Backup from Portal

New -> SQL Database -> Import

clip_image034

Select Backpac:

clip_image036

clip_image037

Problems

No T-SQL option

Run T-SQL queries option is missing from the database.

The feature is disabled for V12 databases as per:

https://social.msdn.microsoft.com/Forums/azure/en-US/62c6f684-983d-4725-b273-f4e596b49587/online-sql-database-management-is-unavailable?forum=ssdsgetstarted

clip_image038

Links

How to Migrate an On-Premises SQL Server 2012 Database to Windows Azure SQL Database

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

Good step by step overview on how to use bacpac import feature.

Migration cookbook now available for the latest Azure SQL Database Update (V12)

http://azure.microsoft.com/blog/2015/01/26/migration-cookbook-now-available-for-the-latest-azure-sql-database-update-v12/

Recipe 1. Migrating a compatible database using SSMS

Recipe 2. Migrating a near-compatible database using SQL Azure Migration Wizard

Recipe 3. Update database in place then deploy to Azure SQL Database

SQL Database Migration Wizard v3.15.5, v4.15.5 and v5.15.5

https://sqlazuremw.codeplex.com/

Migrating Databases to Azure SQL Database

https://msdn.microsoft.com/en-us/library/azure/ee730904.aspx

Leave a Reply