Migrating to Azure SQL Database - PowerPoint Presentation

Slide1

Migrating to Azure SQL DatabaseTips, Tricks andLessons Learned

Randolph West, Born SQL

Calgary PASS Chapter, 17 August 2016

Email: r@ndolph.ca

Twitter: @

rabrystSlide2

What’s the Difference?Azure

SQL Database

cloud, cloud, cloud, shiny, shiny, shiny

SQL Server

on-

prem

, earthed, traditional, legacy

Microsoft

is Cloud FirstSlide3

Azure SQL Database vs SQL Server VMPlatform-as-a-Service (PaaS)

Mostly compatible with SQL Server 2014 and 2016

Pay-as-you-go

Scale-up or scale-out instantly*

Automatically configured, patched, and upgraded

Automated backups, Point-In-Time Restore, Geo-Restore, and Active Geo-Replication

Pay per hour, per service tier and performance level

Pay for data transfer

Total cost of application = Highly minimized administration costs + software development costs + SQL Database service costsSlide4

Azure SQL Database vs SQL Server VMInfrastructure-as-a-Service (IaaS)

SQL Server 2014 or 2016, installed on a virtual machine

Per-minute

licencing

cost (Web, Standard, Enterprise)

Or bring your own existing

licence

and only pay for Windows VM

Fully

customisable

You are responsible for managing it (patching, maintenance, backups)

Pay for premium storage (SSD drives: P10, P20, P30)

Total cost of application = Minimized software development/modification costs + administration costs + SQL Server and Windows Server licensing costs + Azure Storage costsSlide5

Which One Do I Choose?Do you have to choose?Why?

Are you sure?

Is your company going to benefit from this?

Are you sure?Slide6

Azure SQL Database Limitationshttps://azure.microsoft.com/en-us/documentation/articles/sql-database-general-limitations/

Highlights:

No Windows Authentication (Azure AD is supported)

TCP/IP on port 1433 only

No SQL Server Agent jobs (on-premises only)

Fixed Collation

(default SQL_Latin1_General_CP1_CI_AS

)

Restricted usernames (

admin, administrator, guest, root,

sa

, ‘\’

)Slide7

Transact-SQL Differences in Azure SQL Databasehttps://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/

“Azure SQL Database is designed to isolate features from any dependency on the master database. As a consequence many server-level activities are inappropriate for SQL Database and are unsupported.” –

MicrosoftSlide8

Features Not SupportedConnection related: Endpoint statements, ORIGINAL_DB_NAME. Windows authentication is not available for logins or contained database users.

Cross database queries using three or four part names. (Read-only cross-database queries are supported by using elastic database query.)

Cross database ownership chaining, TRUSTWORTHY setting

Data Collector

Database Diagrams

Database Mail

DATABASEPROPERTY (use DATABASEPROPERTYEX instead)

EXECUTE AS logins

Encryption: extensible key management

Eventing

: events, event notifications, query notifications

Features related to database file placement, size, and database files which are automatically managed by Microsoft Azure.

Features that relate to high availability which is managed through your Microsoft Azure account: backup, restore,

AlwaysOn

, database mirroring, log shipping, recovery modes.

Features that rely upon the log reader running on SQL Database: Push Replication, Change Data Capture.

Features that rely upon the SQL Server Agent or the MSDB database: jobs, alerts, operators, Policy-Based Management, database mail, central management servers.

FILESTREAM

Functions:

fn_get_sql

,

fn_virtualfilestats

,

fn_virtualservernodes

Global temporary tables

Hardware related server settings: memory, worker threads, CPU affinity, trace flags, etc. Use service levels instead.

HAS_DBACCESS

KILL STATS JOB

Linked servers, OPENQUERY, OPENROWSET, OPENDATASOURCE, BULK INSERT, 3 and 4 part names

Master/target servers

.NET Framework CLR integration with SQL Server

Resource governor

Semantic search

Server credentials

Sever-level items: Server roles, IS_SRVROLEMEMBER,

sys.login_token

. Server level permissions are not available though some are replaced by database-level permissions. Some server-level DMV's are not available though some are replaced by database-level DMVs.

Serverless

express:

localdb

, user instances

Service broker

SET REMOTE_PROC_TRANSACTIONS

SHUTDOWN

sp_addmessagesp_configure

options and RECONFIGURE

sp_helpuser

sp_migrate_user_to_contained

SQL Server audit (use SQL Database auditing instead)

SQL Server Profiler

SQL Server trace

Trace flags

Transact-SQL debugging

Triggers: Server-scoped or logon triggers

USE statement: To change the database context to a different database you must make a new connection to the new database.Slide9

Migrating a SQL Server Database to Azure SQL Databasehttps://azure.microsoft.com/en-us/documentation/articles/sql-database-cloud-migrate/

SQL Server Data Tools for Visual Studio (SSDT):

“SSDT uses the most recent compatibility rules to detect SQL Database V12 incompatibilities. If incompatibilities are detected, you can fix detected issues directly in this tool. This is currently the recommended method to test and fix SQL Database V12 compatibility issues.” –

Microsoft

Other tools can also help, but are not recommended:

SqlPackage

, Export Data Tier application wizard (SSMS), SQL Server 2016 Upgrade Advisor, SQL Azure Migration Wizard (SAMW)Slide10

Migrating a SQL Server Database to Azure SQL DatabaseSSMS Migration WizardBACPAC / BCP

BACPAC: JSON format containing database schema and data

BCP: parallel insertSlide11

Resource Limits

Source

: MicrosoftSlide12

Understanding DTUsDatabase Transaction Unit (DTU):

Combination of I/O, RAM, CPU and log writes in some magical* formula

Azure SQL Database benchmark overview:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-benchmark-overview/

DTU Calculator (3

rd

party product):

http://dtucalculator.azurewebsites.net

% Processor Time, Disk Reads/sec, Disk Writes/sec,

Log Bytes Flushed/secSlide13

Monthly Cost (CAD)Azure SQL Database (by DTUs)

Basic – 5 DTU – 2GB max storage – $7

Standard S3 – 100 DTU – 250GB max storage – $219

Premium P6 – 1000 DTU – 500GB max storage – $5,428

Premium P15

–

4000 DTU

–

1TB max storage – $23,350

SQL Server on Azure Windows VM (by

licence

/ resources)

DS13 instance (Web) – 8 cores – 56GB RAM – 400GB drive – $1,119

DS13 instance (Standard) – 8 cores – 56GB RAM – 400GB drive – $1,785

DS13 instance (Enterprise) – 8 cores – 56GB RAM – 400GB drive – $3,775

Additional storage – P30 SSD – 5,000 IOPS – 1TB – $164 per diskSlide14

Show Your WorkCompatibility tool (SSDT) → Can I even migrate to Azure SQL Database?

DTU

Calculator

→ What service tier should I get? Do I need an elastic pool?

BACPAC EXPORT

→ Export database schema and data

BACPAC IMPORT

→ Insert database schema and data

Azure Portal

→ Look at the new preciousSlide15

DTU Calculator (Cry Big Ugly Tears)Important to capture over a busy period, for a long enough period.

BE REALISTIC

!

First run of this tool, with 10 minutes of data, showed S3 ($219/

mo

).

Second run of this tool, with an hour of data, showed P2 ($1,357/

mo

).

Difference of $1,138 per month ($13,656 per year)Slide16

AcceptanceYour boss has approved the expense, so let’s migrate!Slide17

Tips and TricksAlways use the latest version of SQL Server Management Studio

BACPAC files must be saved to standard Azure Blob Storage, not premium

Make sure your database is compatible with SQL Server 2016 features

Watch out for hidden costs when scaling out SQL Databases, or running VMs

Elastic pools can get expensive

De-allocate

VMs, and delete databases, if

not using themSlide18

SummaryDTUs do not map directly to

resources,

and require a workload to evaluate

against (time-based evaluation).

Some important on-premises features are not available in Azure SQL Database.

For smaller databases, web-based applications, and specifically new development, Azure SQL Database may be appropriate.

SQL Server 2016 on Azure VM is more appropriate for existing applications, and more cost-effective for larger databases, even with an Enterprise

licence

.

YMMV (your mileage may vary).Slide19

CreditsAzure website:https://azure.microsoft.com/

Books Online:

https://technet.microsoft.com/en-us/library/ms130214(v=sql.130).aspx