Migrating to Azure SQL Database Tips Tricks and

Migrating to Azure SQL Database Tips, Tricks and Lessons Learned Randolph West, Born SQL Calgary PASS Chapter, 17 August 2016 Email: r@ndolph. ca Twitter: @rabryst

What’s the Difference? Azure SQL Database cloud, SQL cloud, shiny, shiny Server on-prem, Microsoft earthed, traditional, legacy is Cloud First

Azure SQL Database vs SQL Server VM Platform-as-a-Service (Paa. S) 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 (Iaa. S) 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: 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

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 No on port 1433 only 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, ‘’)

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 Features that rely upon the SQL Server Agent or the MSDB database: jobs, alerts, operators, Policy -Based Management, database mail, central management servers. Connection 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. ) FILESTREAM Functions: fn_get_sql, fn_virtualfilestats, fn_virtualservernodes Service broker SET REMOTE_PROC_TRANSACTIONS SHUTDOWN sp_addmessagesp_configure options and RECONFIGURE 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) KILL STATS JOB EXECUTE AS logins Encryption: extensible key management Eventing: events, event notifications, query notifications Serverless express: localdb, user instances Cross database ownership chaining, TRUSTWORTHY setting are replaced by database-level DMVs. sp_helpuser sp_migrate_user_to_contained SQL Server audit (use SQL Database auditing instead) Linked servers, OPENQUERY, OPENROWSET, OPENDATASOURCE, BULK INSERT, 3 and 4 part names SQL Server Profiler SQL Server trace Master/target servers Trace flags . NET Framework CLR integration with SQL Server Transact-SQL debugging Features related to database file placement, size, and database files which are automatically managed by Microsoft Azure. Resource governor Triggers: Server-scoped or logon triggers Semantic search Features that relate to high availability which is managed through your Microsoft Azure account: backup, restore, Always. On, database mirroring, log shipping, recovery modes. Server credentials USE statement: To change the database context to a different database you must make a new connection to the new database. 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 Features that rely upon the log reader running on SQL Database: Push Replication, Change Data Capture.

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 V 12 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 V 12 compatibility issues. ” – Microsoft Other tools can also help, but are not recommended: Sql. Package, 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 (3 rd 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 – 2 GB max storage – $7 Standard S 3 – 100 DTU – 250 GB max storage – $219 Premium P 6 – 1000 DTU – 500 GB max storage – $5, 428 Premium P 15 – 4000 DTU – 1 TB max storage – $23, 350 SQL Server on Azure Windows VM (by licence / resources) DS 13 instance (Web) – 8 cores – 56 GB RAM – 400 GB drive – $1, 119 DS 13 instance (Standard) – 8 cores – 56 GB RAM – 400 GB drive – $1, 785 DS 13 instance (Enterprise) – 8 cores – 56 GB RAM – 400 GB drive – $3, 775 Additional storage – P 30 SSD – 5, 000 IOPS – 1 TB – $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 S 3 ($219/mo). Second run of this tool, with an hour of data, showed P 2 ($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/ms 130214(v=sql. 130). aspx