Migrating to Azure SQL Database Tips, Tricks and Lessons Learned
Download
Report
Transcript Migrating to Azure SQL Database Tips, Tricks and Lessons Learned
Migrating to Azure
SQL Database
Tips, Tricks and
Lessons Learned
Randolph West, Born SQL
Calgary PASS Chapter, 17 August 2016
Email: [email protected]
Twitter: @rabryst
What’s the Difference?
Azure
SQL Database
cloud,
SQL
cloud, cloud, shiny, shiny, shiny
Server
on-prem,
Microsoft
earthed, traditional, legacy
is Cloud First
Azure SQL Database vs SQL Server VM
Platform-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 costs
Azure SQL Database vs SQL Server VM
Infrastructure-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 costs
Which One Do I Choose?
Do
you have to choose?
Why?
Are
Is
you sure?
your company going to benefit from this?
Are
you sure?
Azure SQL Database Limitations
https://azure.microsoft.com/en-us/documentation/articles/sqldatabase-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, ‘\’)
Transact-SQL Differences in Azure SQL
Database
https://azure.microsoft.com/en-us/documentation/articles/sqldatabase-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.” –
Microsoft
Features Not Supported
Connection related: Endpoint statements,
ORIGINAL_DB_NAME. Windows authentication is
not available for logins or contained database
users.
Features that rely upon the SQL Server Agent or
the MSDB database: jobs, alerts, operators,
Policy-Based Management, database mail, central
management servers.
Cross database queries using three or four part
names. (Read-only cross-database queries are
supported by using elastic database query.)
FILESTREAM
Functions: fn_get_sql, fn_virtualfilestats,
fn_virtualservernodes
SET REMOTE_PROC_TRANSACTIONS
SHUTDOWN
sp_addmessagesp_configure options and
RECONFIGURE
sp_helpuser
Global temporary tables
Data Collector
Database Diagrams
Hardware related server settings: memory, worker
threads, CPU affinity, trace flags, etc. Use service
levels instead.
Database Mail
HAS_DBACCESS
DATABASEPROPERTY (use DATABASEPROPERTYEX
instead)
EXECUTE AS logins
Encryption: extensible key management
Eventing: events, event notifications, query
notifications
Service broker
Cross database ownership chaining,
TRUSTWORTHY setting
Serverless express: localdb, user instances
sp_migrate_user_to_contained
SQL Server audit (use SQL Database auditing
instead)
KILL STATS JOB
SQL Server Profiler
Linked servers, OPENQUERY, OPENROWSET,
OPENDATASOURCE, BULK INSERT, 3 and 4 part
names
SQL Server trace
Trace flags
Master/target servers
Transact-SQL debugging
.NET Framework CLR integration with SQL Server
Triggers: Server-scoped or logon triggers
Features related to database file placement, size,
and database files which are automatically
managed by Microsoft Azure.
Resource governor
USE statement: To change the database context
to a different database you must make a new
connection to the new database.
Features that relate to high availability which is
managed through your Microsoft Azure account:
backup, restore, AlwaysOn, database mirroring,
log shipping, recovery modes.
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.
Features that rely upon the log reader running on
SQL Database: Push Replication, Change Data
Capture.
Semantic search
Migrating a SQL Server Database to
Azure SQL Database
https://azure.microsoft.com/en-us/documentation/articles/sqldatabase-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)
Migrating a SQL Server Database to
Azure SQL Database
SSMS Migration Wizard
BACPAC / BCP
BACPAC: JSON format containing database schema and data
BCP: parallel insert
Resource Limits
Source: Microsoft
Understanding DTUs
Database Transaction Unit (DTU):
Azure SQL Database benchmark overview:
Combination of I/O, RAM, CPU and log writes in some magical*
formula
https://azure.microsoft.com/en-us/documentation/articles/sqldatabase-benchmark-overview/
DTU Calculator (3rd party product):
http://dtucalculator.azurewebsites.net
% Processor Time, Disk Reads/sec, Disk Writes/sec,
Log Bytes Flushed/sec
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 disk
Show Your Work
Compatibility 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 precious
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)
Acceptance
Your boss has approved the expense, so let’s migrate!
Tips and Tricks
Always 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 them
Summary
DTUs 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).
Credits
Azure website:
https://azure.microsoft.com/
Books Online:
https://technet.microsoft.com/enus/library/ms130214(v=sql.130).aspx