MIGRATING TO AZURE SQL DATABASE TIPS TRICKS AND

MIGRATING TO AZURE SQL DATABASE: TIPS, TRICKS, AND LESSONS @bornsql RANDOLPH WEST bornsql. ca/blog LEARNED DATA PLATFORM MVP

MIGRATING TO AZURE SQL DATABASE PREPARE FOR OBSOLESCENCE ▸ Everything I was going to tell you today was obsolete on Wednesday. This is how quickly things change. ▸ Azure SQL Database is always getting better: ▸ three ways to migrate ▸ speed was doubled across the board earlier this year ▸ databases larger than 1 TB are supported ▸ all the things missing, are coming soon. 2

3 THIS IS A MARKETING SLIDE WHAT IS AZURE SQL DATABASE? ▸ Azure SQL Database is a cloud-based relational database service, built on the Microsoft SQL Server engine, designed to deliver predictable performance and scalability, with virtually no downtime, and nearzero administration. Source: Microsoft

THIS IS ANOTHER MARKETING SLIDE, WHICH IS NOW OBSOLETE 4 COMMON BUSINESS DIFFERENTIATORS (TODAY) Azure SQL Database SQL Server You’re building new cloud-based applications to take advantage of cost savings and performance. You want to build enterprise applications larger than 1 TB
(4 TB). You need instant scalability. You want databases replicated in different regions of the world for backup. You have existing IT resources and can maintain stewardship over backups and database high availability. You want Microsoft to handle common management operations.

MIGRATING TO AZURE SQL DATABASE WHY ARE YOU MIGRATING? ▸ Do you have to choose? ▸ Why? ▸ Are you sure? ▸ Is your company going to benefit from this? ▸ Are you sure? 5

MIGRATING TO AZURE SQL DATABASE PLATFORM AS A SERVICE (PAAS) ▸ Mostly compatible with SQL Server 2014, 2016 and 2017 ▸ 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 6

MIGRATING TO AZURE SQL DATABASE 7 INFRASTRUCTURE AS A SERVICE (IAAS) ▸ SQL Server 2014, 2016, or 2017, installed on a virtual machine ▸ Per-minute licensing 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: P 10, P 20, P 30) ▸ Total cost of application = Minimized software development/modification costs + administration costs + SQL Server and Windows Server licensing costs + Azure Storage costs

MIGRATING TO AZURE SQL DATABASE LIMITATIONS (TODAY) ▸ https: //docs. microsoft. com/en-us/azure/sql-databasefeatures/ ▸ 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_Latin 1_General_CP 1_CI_AS) ▸ Restricted usernames (admin, administrator, guest, root, sa, ‘’) 8

MIGRATING TO AZURE SQL DATABASE 9 TRANSACT-SQL DIFFERENCES IN AZURE SQL DATABASE (TODAY) ▸ https: //docs. microsoft. com/en-us/azure/sql-database/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

10 THIS IS A SLIDE YOU CAN’T READ FEATURES NOT SUPPORTED (TODAY) ▸ Collation of system objects ▸ Connection related: Endpoint statements, ORIGINAL_DB_NAME. SQL Database does not support Windows authentication, but does support the similar Azure Active Directory authentication. Some authentication types require the latest version of SSMS. For more information, see Connecting to SQL Database or SQL Data Warehouse By Using Azure Active Directory Authentication. ▸ Cross database queries using three or four part names. (Readonly cross-database queries are supported by using elastic database query. ) ▸ Cross database ownership chaining, TRUSTWORTHY setting ▸ DATABASEPROPERTY Use DATABASEPROPERTYEX instead. ▸ EXECUTE AS LOGIN Use 'EXECUTE AS USER' instead. ▸ Encryption is supported except for extensible key management ▸ Eventing: Events, event notifications, query notifications ▸ File placement: Syntax related to database file placement, size, and database files that are automatically managed by Microsoft Azure. ▸ High availability: Syntax related to high availability, which is managed through your Microsoft Azure account. This includes syntax for backup, restore, Always On, database mirroring, log shipping, recovery modes. ▸ Log reader: Syntax that relies upon the log reader, which is not available on SQL Database: Push Replication, Change Data Capture. SQL Database can be a subscriber of a push replication article. ▸ Functions: fn_get_sql, fn_virtualfilestats, fn_virtualservernodes ▸ Server credentials: Use database scoped credentials instead. ▸ Server-level items: Server roles, IS_SRVROLEMEMBER, sys. login_token. GRANT, REVOKE, and DENY of server level permissions are not available though some are replaced by database-level permissions. Some useful server-level DMVs have equivalent database-level DMVs. ▸ SET REMOTE_PROC_TRANSACTI ONS ▸ SHUTDOWN ▸ Global temporary tables ▸ sp_addmessage ▸ Hardware: Syntax related to ▸ sp_configure options and hardware-related server settings: such as memory, worker threads, CPU affinity, trace flags. Use service levels instead. ▸ HAS_DBACCESS ▸ KILL STATS JOB ▸ OPENQUERY, OPENROWSET, OPENDATASOURCE, and fourpart names ▸. NET Framework: CLR integration with SQL Server ▸ Semantic search RECONFIGURE. Some options are available using ALTER DATABASE SCOPED CONFIGURATION. ▸ sp_helpuser ▸ sp_migrate_user_to_contained ▸ SQL Server Agent: Syntax that relies upon the SQL Server Agent or the MSDB database: alerts, operators, central management servers. Use scripting, such as Azure Power. Shell instead. ▸ SQL Server audit: Use SQL Database auditing instead. ▸ SQL Server trace ▸ Trace flags: Some trace flag items have been moved to compatibility modes. ▸ 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.

MIGRATING TO AZURE SQL DATABASE MIGRATING A SQL SERVER DATABASE TO AZURE SQL DATABASE ▸ https: //docs. microsoft. com/en-us/azure/sql-databasecloud-migrate ▸ Check compatibility with Data Migration Assistant (DMA) ▸ Choose a Service Level and Performance Tier ▸ Three primary methods to migrate to Azure SQL Database: ▸ Migrate with downtime during migration (demo ��) ▸ Transactional Replication (no demo ��) ▸ Live migration through Azure Portal (coming soon) 11

NO DEMO, SO HERE’S A PICTURE 12 TRANSACTIONAL REPLICATION METHOD ▸ Configure your Azure SQL Database as a subscriber to the SQL Server instance that you wish to migrate. ▸ The transactional replication distributor synchronizes data from the database to be synchronized (the publisher) while new transactions continue occur. Source: Microsoft

NO DEMO, SO HERE’S A PICTURE 13 LIVE MIGRATION THROUGH THE AZURE PORTAL Source: Microsoft

NO DEMO, SO HERE’S A PICTURE 14 LIVE MIGRATION THROUGH THE AZURE PORTAL Source: Microsoft

NO DEMO, SO HERE’S A PICTURE 15 LIVE MIGRATION THROUGH THE AZURE PORTAL Source: Microsoft

NO DEMO, SO HERE’S A PICTURE 16 LIVE MIGRATION THROUGH THE AZURE PORTAL Source: Microsoft

NO DEMO, SO HERE’S A PICTURE 17 LIVE MIGRATION THROUGH THE AZURE PORTAL Source: Microsoft

18 WANT TO SPEND SOME MONEY? HERE’S HOW SERVICE LEVEL AND PERFORMANCE TIER (CAD) Level Basic Standard Premium Tier Basic S 0 S 1 S 2 S 3 P 1 P 2 P 4 P 6 P 11 P 15 DTUs 5 10 20 50 100 125 250 500 1000 1750 4000 Size Cost p. m. 2 GB $6. 06 250 GB $18. 28 250 GB $36. 46 250 GB $91. 19 250 GB $182. 38 500 GB $565. 40 500 GB $1, 130. 79 500 GB $2, 261. 58 500 GB $4, 523. 15 4 TB $8, 512. 57 4 TB $19, 458. 59

THIS SLIDE POSES A PUZZLING QUESTION WHAT THE HECK IS A DTU? ▸ Database Transaction Unit (DTU): ▸ Combination of I/O, RAM, CPU and log writes in some magical* formula ▸ Azure SQL Database benchmark overview: ▸ https: //docs. microsoft. com/en-us/azure/sql-databasebenchmark-overview/ ▸ DTU Calculator (3 rd party product): ▸ http: //dtucalculator. azurewebsites. net ▸ % Processor Time, Disk Reads/sec, Disk Writes/sec, Log Bytes Flushed/sec 19

ADD-ON QUESTION 20 CAN I SHARE RESOURCES BETWEEN AZURE SQL DATABASES? ▸ Elastic pools are useful for managing and scaling multiple databases that have varying and unpredictable usage demands. ▸ The databases in an elastic pool are on a single Azure SQL Database server and share a set number of resources at a set price. ▸ Elastic pools thus have larger limits, but are for sharing between databases. One single database cannot exceed the limits noted previously.

21 DEMO

THIS IS A TL; DR SLIDE 22 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 storage ▸ 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

THIS IS WHAT’S COMING SOMETHING AMAZING THIS WAY COMES … ▸ Migrate SQL Server to SQL as a Service with no changes ▸ SQL Agent, 3 -part names, DBMail, CDC, Service Broker ▸ Cross-database and cross-instance querying ▸ Extensibility: CLR and R Services ▸ SQL Profiler (ugh!), additional DMVs support, XEvents ▸ Native backup and restore, log shipping, transactional replication 23

ANOTHER SCREENSHOT 24 HERE’S THE SCREENSHOT! Source: Microsoft

THIS IS A SUMMARY SLIDE 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 (YET!) ▸ For smaller databases, web-based applications, and specifically new development, Azure SQL Database may be appropriate ▸ SQL Server 2016 on Azure VM may be more appropriate for existing applications, and more cost-effective for larger databases, even with an Enterprise licence ▸ YMMV (your mileage may vary) 25

LOTS OF LINKS HERE FIND OUT MORE ▸ Azure website: ▸ https: //azure. microsoft. com/ ▸ Books Online: ▸ https: //docs. microsoft. com/en-us/sql-server/sql-server-technicaldocumentation ▸ John Sterrett’s Virtual PASS Chapter presentation: ▸ https: //goo. gl/Ff 6 Hyg ▸ Andy Mallon Reverse Engineered the DTU Calculator: ▸ https: //sqlperformance. com/2017/03/azure/what-the-heck-is-a-dtu 26