DATA MIGRATIONTO THE CLOUDGeorge Huey Principal Architect Evangelist

Lynn Langit Senior Developer Evangelist

SESSION OBJECTIVES AND TAKEAWAYS

• Overview• Windows Azure Platform• SQL Azure

• Application Migration • Data Access Tier Considerations• Security• Connectivity• Collation Model

• SQL Database Migration• Migration Scenarios• One-off migration tools• Moving Schema and Data into SQL Azure• Transferring Large Data into SQL Azure

WINDOWS AZURE PLATFORMOVERVIEW

Microsoft is offering a Windows Azure platform 30 day pass, so you can try Windows Azure and SQL Azure for free.

For more information go to http://bit.ly/eAnP8C and enter SQLAZURE for your promotion code.

WINDOWS AZURE PLATFORM

GENERAL PURPOSE PROGRAMMING LANGUAGES

Compute

Storage

Management

Marketplace

App FabricCDN

Windows Azure Platform

Relational

databaseData Sync

SQL AZURE DATABASETHE FIRST AND ONLY TRUE RELATIONAL DATABASE AS A SERVICE

Elastic Scale Developer Agility

Self-managed

• Database utility; pay as you grow

• Business-ready SLAs• Enable multi-tenant

solutions• World-wide presence

• Easy provisioning and deployment

• Auto high-availability and fault tolerance

• Self-maintaining infrastructure; self-healing

• No need for server or VM administration

• Build cloud-based database solutions on consistent relational model

• Leverage existing skills through existing ecosystem of developer and management tools

• Explore new data application patterns

DATA TIER ACCESSAPPLICATION MIGRATION

DATA ACCESS APIS

• Supported APIs

• Connection String• ADO .NET

• Encrypt=True and add @servername to User ID

• ODBC• Encrypt=yes and add @servername to Uid

Supported Unsupported

ADO.NET .NET 3.5 SP1 and 4.0 ODBC - SNAC 10Entity Framework .NET 3.5 SP1 , 4.0, 4.1SQL Server 2008 Driver for PHP v1.1

OleDBODBC driver “SQL Server”

DATA ACCESS APIS CONT.

• Protocols

• Authentication Mode

Supported Unsupported

TCP/IP over port 1433 Dynamic portsNamed pipesShared memory

Supported Unsupported

SQL Authentication Windows Authentication

CONNECTIVITY

• Robust Connection Management through Retry Logic• Great Read: Connection Management in SQL Azure

http://bit.ly/kWHfIJ • Flaky network connectivity

• Code-Far topology

• Failover• HW Failures or Upgrades

• Resource Management Operations• Throttling and Load Balancing

• ALTER DATABASE (T-SQL)• Changes to Database Name or MAXSIZE/EDITION

ALTER DATABASE [TestDb] MODIFY (MAXSIZE=50GB, EDITION='business')GO

SECURITY MANAGEMENT

• Manage Logins - Authentication - SQL Server (only)• CREATE LOGIN [testlogin] WITH PASSWORD='password'• Common login names such as ‘sa’, ‘admin’, ‘administrator’,

‘root’, and ‘guest’ are not allowed,

• Manage User• CREATE USER testuser FROM LOGIN testlogin;

• Manage Server Roles• Two new database role (only in master)• EXEC sp_addrolemember 'dbmanager', 'testuser';

• Similar to [dbcreator]

• EXEC sp_addrolemember 'loginmanager', 'testuser'; • Similar to [securityadmin]

• Views• sys.sql_logins. sys.databases, sys.database_principal

FIREWALL MANAGEMENT

• Portal Access• Programmatic Access

• System Views and Stored Procedures – http://bit.ly/mzhBjw• View: sys.firewall_rules• Stored Procedure: sp_set_firewall_rule• Stored Procedure: sp_delete_firewall_rule

• API Implementation Example – http://bit.ly/mCKzqV

COLLATIONS

• Default Collation• Set to SQL_LATIN1_GENERAL_CP1_CI_AS• Applies to…

• Metadata, TempDb and Default for User Data – Object Names (tables, sprocs etc), String

variables and Columns when no collation is specified.

• Use column and/or expression level collation when needed

CREATE TABLE [dbo].[ErrorLog]([ErrorLogID] [int] IDENTITY(1,1) NOT NULL,[ErrorTime] [datetime] NOT NULL,[UserName] [nvarchar(4000)] COLLATE Turkish_100_CI_AI NOT NULL,[ErrorNumber] [int] NOT NULL,[ErrorMessage] [nvarchar](4000) COLLATE Japanese_CI_AS NOT

NULL, CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ([ErrorLogID] ASC) )

DECLARE @str nvarchar(128) COLLATE Greek_CS_AI

FEATURE PARITY

• Administration Surface• Physical Server Properties does not apply in SQL Azure

• You have a master database but no access to server level constructs such as sp_configure, endpoints, DBCC commands, server level DMVs and System Views

• Programmability Surface• Certain Features are partially available today

• USE, XML processing, deprecated T-SQL etc.• List is available here;

http://msdn.microsoft.com/en-us/library/ee336267.aspx • Some features are not available today

• Full-text Search, Remote Data Access and Linked Servers, Distributed Transactions, Change Tracking, Service Broker etc.

• List is available here; http://msdn.microsoft.com/en-us/library/ee336253.asp

SUMMARY – MIGRATING APPLICATIONSApplication Data Access Tier Just Works Need to

Investigate

Data Access APIs Support ADO.NET, ODBC, EF and PHP

OleDB

Security Model SQLAuthentication

Windows Authentication

Connection Management Apps with transaction retry logic, 3-tiered,

stateless or partially statefull

apps

Legacy Apps2 tiered apps with online connections

Admin and Programmability Surface Support

Tables, Views, Sprocs, Functions, Triggers, built-in data types etc

Server level options, FTS,

Service Broker, Distributed

Transactions

Collation Behavior Apps explicit with collation options

Non-English apps depending on

db/server collation defaults

MOVING SCHEMA AND DATADATABASE MIGRATION

SCENARIO OVERVIEW

One-Off

On-Going

• Migration to/from SQL Server

• Transfer between SQL Azure servers/databases

• Migration from Oracle, Sybase, MySQL, Access

• Publish to cloud• Share between

on-prem and cloud apps

• Some SQL Azure scale-out cases

• Geo-locality• One-way or two-

way

• SQL Azure Migration Wizard

• SSMS Generate Script• SSMS Import & Export• BCP• SSIS• Data-tier Application

DAC• DAC Logical

Import/Export• SQL Server Migration

Assistant

• SQL Azure Data Sync

• Sync Framework• SSIS

Scenarios Tools

SQL AZURE DATA SYNC SCENARIOS

On-Premises

Retail Stores

SQL Azure

Database

Sync

SQL Azure Data Sync

Branch Offices

HQ

Sync

Sync

Sync

Sync

Sync

Sync

Sync

Sync

Cloud

SQL SERVER MIGRATION ASSISTANT (SSMA)

• Scenario• Migration of schema and/or data :

• Oracle• Microsoft Access• MySQL• Sybaseto SQL Server / SQL Azure.

• SSMA• Map data types• Migrate objects• Migrate data

SQL SERVER MIGRATION ASSISTANTMIGRATING MYSQL TO SQL AZURE

demo

SUMMARY – DATABASE MIGRATIONONE-OFF MIGRATION TOOLS FOR SQL SERVER

Tools Schema Data

Data Transfer Efficienc

y

SQL Azure

Compat Checks

Notes

BCP No Yes Good No • Efficient transfer of data to existing table

SSMS Generate Scripts wizard

Partial Yes Poor Some• Good for smaller databases• No support for spatial• Stops analyzing on first incompatibility

SQL Server Import & Export Data

No Yes Good No

• Simple UI on top of SSIS; also available in SSMS• Tables created without clustered index,

therefore data transfers fail

SSIS No Yes Good No• Most flexibility, but more involved• Transfer SQL Server Objects task fails to

create schema

SQL Azure Migration Wizard

Yes Yes Good Yes• Great capabilities; e.g. evaluate trace files• Open source on CodePlex; not supported

by MSFT

DAC Partial No N/A Some

• No DAC upgrade on SQL Azure yet• No support for spatial• Must fix source database before

proceeding

DAC Logical Import/Export

Yes Yes Good Some

• Export/import of package with DAC plus data• Available in next few months• Must fix source database before

proceeding

SQL Azure Database Copy

Yes Yes Good N/A• Between SQL Azure only databases• Transactional consistency of copy• Limited to within a data center

SQL SERVER MANAGEMENT STUDIOGENERATE SCRIPT WIZARD

• Scenario• Migration of schema and/or data with fine grain control.

• Pros• Native support for SQL Azure Schema options: “Engine Type = SQL

Azure”• Ensures correct options and settings are applied for the TSQL script

generation.• Requires explicit action on unsupported objects.

• Cons• Verbose – INSERT Statements instead of raw data• Data is scripted with a fixed 100 row batch size.

• Edit “GO” statements between small batches

• Round-trip Efficiency• Use “SET NOCOUNT ON”

• Stops processing on first incompatibility

GENERATE SCRIPT WIZARDMIGRATING DATABASES TO SQL

AZURE

demo

DATA-TIER APPLICATION (DAC) PACKAGES• Scenarios

• Self contained package for moving schema easily through the development lifecycle

• What is a DAC Pack?• Single unit for authoring, deploying, and managing the data-tier

objects

• Development Lifecycle (VS 2010)– Editing DACs

» Schema and DB Code Development, » Code Analyses, Deployment Policy Settings, » Schema Comparison and more…

– Building DACs – the self contained database package

• Management Lifecycle (SSMS 2008 R2)– Managing DACs

» Registering existing database as DACs» Deploying and Upgrading databases using DACs,

SQL SERVER MANAGEMENT STUDIOIMPORT AND EXPORT WIZARD• Scenario

• Simplified wizard for migrating schema and data through SSIS

• Pros• Great performance out of the box• Allows ‘save as package’ for full control

SSMS DATA-TIER APPLICATION & IMPORT / EXPORT

MIGRATING DATABASES TO SQL AZURE

demo

DACPAC IN VISUAL STUDIOMIGRATING DATABASES TO SQL

AZURE

demo

SQL SERVER INTEGRATION SERVICES

MIGRATING DATABASES TO SQL AZURE

demo

SQL AZURE MIGRATION WIZARD

• Scenario• Migration of schema and/or data with fine grain control.

• Pros• SQL Azure Compatibility Analysis• Uses SMO and BCP• Built in retry and chunking• Migrate from:

• SQL Server to SQL Azure• SQL Azure to SQL Azure• SQL Azure to SQL Server

• First try clustered index creation• Create package (great for Test & QA)• SQLAzureMWBatchBackup• SQLAzureMWBatchUpload

• Cons• Not supported by Microsoft• Not a full SQL Engine TSQL Parser

SQLAZUREMWMIGRATING DATABASES TO SQL

AZURE

demo

SQL AZURE COMMUNITY TECHNOLOGY PREVIEW (CTP) IMPORT AND EXPORT

• Scenarios• Command line process that can import and

export data from SQL Server and SQL Azure

DAC IMPORT / EXPORT CLIMIGRATING DATABASES TO SQL

AZURE

demo

SUMMARY – DATABASE MIGRATIONONE-OFF MIGRATION TOOLS FOR SQL SERVER

Tools Schema Data

Data Transfer Efficienc

y

SQL Azure

Compat Checks

Notes

BCP No Yes Good No • Efficient transfer of data to existing table

SSMS Generate Scripts wizard

Partial Yes Poor Some• Good for smaller databases• No support for spatial• Stops analyzing on first incompatibility

SQL Server Import & Export Data

No Yes Good No

• Simple UI on top of SSIS; also available in SSMS• Tables created without clustered index,

therefore data transfers fail

SSIS No Yes Good No• Most flexibility, but more involved• Transfer SQL Server Objects task fails to

create schema

SQL Azure Migration Wizard

Yes Yes Good Yes• Great capabilities; e.g. evaluate trace files• Open source on CodePlex; not supported

by MSFT

DAC Partial No N/A Some

• No DAC upgrade on SQL Azure yet• No support for spatial• Must fix source database before

proceeding

DAC Logical Import/Export

Yes Yes Good Some

• Export/import of package with DAC plus data• Available in next few months• Must fix source database before

proceeding

SQL Azure Database Copy

Yes Yes Good N/A• Between SQL Azure only databases• Transactional consistency of copy• Limited to within a data center

SQL Azure Labs – http://www.sqlazurelabs.com SQL Azure Migration Wizard –

http://sqlazuremw.codeplex.com SQL Azure Team Blog – http://blogs.msdn.com/sqlazure SQL Azure Videos – http://bit.ly/fbqcwF SQL Azure Data Sync – http://bit.ly/lfyuqR SQL Server Migration Assistant – http://

bit.ly/SQLServer_SSMA SQL Azure Labs Import / Export – http://bit.ly/hToaWf Windows Azure Platform Training Kit – http://bit.ly/ikvTHy Follow us on Twitter – @azure, @sqlazure, @llangit Lynn’s blog – http://blogs.msdn.com/SoCalDevGal

Microsoft is offering a Windows Azure platform 30 day pass, so you can try Windows Azure and SQL Azure for free.For more information go to http://bit.ly/eAnP8C. Enter SQLAZURE for your promotion code.

ADDITIONAL RESOURCES

Q&A

© 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after

the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.