Azure SQL Database Migration Success Stories: Bing Ads & Dynamics 365

2. Customer Success: How Bing Ads and Microsoft Dynamics 365 successfully migrated their workload to Azure SQL Database Avinash Jaiswal - Software Engineer (BingAds R&R) Gana Sadasivam - Principal Engineering Manager (Dynamics365/AX) Sunil Agarwal – Principal Program Manager (Azure SQL Database) BRK3210

3. Agenda Azure SQL Database Migration Overview (15 minutes) BingAds – Success with Azure SQL Database (30 minutes) Dynamics 365 FO – Success with Azure SQL Database (30 minutes)

4. Azure Database Migration Overview Sunil Agarwal

5. Azure SQL Database - Focus on your Business PaaS takes care of your database chores!

6. Azure SQL Databases Azure SQL Database Singleton SQL Elastic Pool Managed Instance SQL Standalone managed database for predictable and stable workloads Instance-scoped programming model with high compatibility to SQL Server Shared resource model for greater efficiency through multi-tenancy Best for apps that require resource guarantee at database level Surface area limitations: Example: CLR, CrossDB, Linked Server, FIleStream, Polybase Best for SaaS apps with multiple databases that can share resources at database level, achieving better cost efficiency Surface Area Limitations: Same as Singleton Best for modernization at scale with low cost and effort Recommended for Lift/Shift Surface area limitations: Example: CLR, CrossDB, Linked Server, FIleStream, Polybase

7. Application Modernization Journey - PLatform Traditional Application multi-tier Architecture Presentation Layer Presentation Layer Presentation Layer Web Client Custom UI Web Client Custom UI Web Client Custom UI Load Balancer Load Balancer Load Balancer Load Balancer Load Balancer Load Balancer Azure Site Recovery Azure IaaS Application Tier Application Tier Application Tier On-Premise Data Center Azure IaaS Data Tier Database Migration Service (DMS) Data Tier Data Tier Azure SQL Database (PaaS)

8. Application Modernization Journey - SAAs Presentation Layer Presentation Layer Customer-N Customer-N Customer-1 Customer-1 Load Balancer Load Balancer Load Balancer Load Balancer Re-Architect Application Tier Application Tier Multi-tenant application code Micro Services Micro Services Data Tier Data Tier

9. BingAds Analytics – Success with Azure SQL Database Avinash Jaiswal

10. BingAds Analytics – Agenda Overview of the BingAds Analytics Platform Motivation to migrate to SQL Azure Migration challenges Post migration benefits and recommendations

11. BingAds Analytics – Application Overview

12. BingAds Analytics – Application Overview Search Query Advertisement

13. BingAds Analytics – Application Overview UI based interactive platform providing key insights Revenue, Top search queries for a date range, etc. Expected response time: 95th percentile < 1s 99th percentile < 10s Supports slicing and dicing of dimensions Data volume a big challenge Multiple years of data >50 TB total raw data Daily data load > 20 GB

14. BingAds Analytics – On-Premise Architecture Presentation Tier Application Tier • Major On-Prem Data Tier Challenges: • Scattered database infrastructure • Multiple SQL Server versions • Cross Database queries • Large databases and cubes • Operational overheads • ~20-25% engineering resources • Upgrades, Disk monitoring, etc. Data Tier Bing Ads Logs ETL process Cube

15. BingAds Analytics – Why move to SQL Azure / Cloud? Scaleson the fly Minimal App changes • Mission critical availability Automatic Upgrades Low learning curve. Same SQL Server technology but on cloud. Always on latest SQL build. Low operational overhead implies higher productivity. Change service tiers, performance levels, and storage dynamically. Built-in high availability with 99.99% SLA. Automatic backups and point in time restore.

16. Azure SQLDB Challenge – High Data Volume Challenge: • Largest table’s volume: 15 TB • SQL Azure Database (Singleton) size limit 4TB! Solution: • We tried sharding the databases, but query performance was very slow! • Leverage latest enhancements in SQL Server • Clustered Columnstore Indexes (CCI)– 10x data compression, 10x query perf. Improvement. • Eliminate unnecessary Rowstore (B-Tree) Indexes • Page compression for Rowstore Indexes Final data volume: 2.5 TB!

17. Azure SQLDB Challenge – Cross Database Queries Challenge: Cross db. queries not supported in Azure SQLDB Solution: • Merge databases to avoid cross database queries • Required changes to the schema and ETL process

18. Azure SQLDB Challenge – Slow Data Load During ETL ETLProcess: Load data daily in batches. Millions of rows per batch. Challenge: High Data Volume causing long data load/delete time. Solution: • Use Table Partitioning for Fact tables • Prepare data in staging and switch partitions • Truncate partitions instead of ‘deleting’ data

19. BingAds Analytics – Deployment In Azure SQL Database Timeline: Assessment, Migration, Optimizations: 3 months • Presentation/Application Tier • changes • Data Movement • Schema Deployment 1. Copy on premise data to files. 2. Upload to SQL Azure using BCP. Deploy modified database project to SQL Azure using SSDT • No changes apart from data tier • No downtime: Only connection string • change.

20. BingAds Analytics- On-Premise Architecture Presentation Tier Application Tier Data Tier Bing Ads Logs ETL process Cube

21. BingAds Analytics- Architecture with SQL Azure Presentation Tier Data Tier Application Tier Bing Ads Logs ETL process Elastic Db Pool • Cloud Data Tier Properties: • All Databases on the same logical server • Clubbed multiple databases into Elastic Db. Pool • Merged databases to eliminate cross Db. queries • Cubes removed – Analytics with CCI met our SLA

22. Azure SQL Database: Post Migration Experience/Benefits Query Performance Improvement • Downtime Operational Overheads Storage Reduction 10x <50% >70% 0

23. BingAds Analytics – Learnings and Recommendations Migrate to SQL Azure: • Reduce operational costs • High scalability Try and test latest SQL Database features: • Columnstore Indexes • QueryStore Time flies faster in the cloud! • Data Migration Services • SQL Managed Instance • Azure Hyperscale* for large databases (*preview)

24. Dynamics 365 Finance and Operations  – Success with Azure SQL Database Gana Sadasivam

26. Old Architecture

27. Dynamics 365 FO – Why cloud migration? Goals • Allow the customers to focus on their core business needs • Take away the pain of maintenance • To attract the new customers • To continue to be a technology leader in Enterprise Business Applications • To address the growing demand for a SaaS Solution Here is a (from Customer in Financial Industry) quote that resonates: “We were looking for an ERP solution that was capable, scalable, and easy-to-use. Dynamics 365 Operations fit the bill perfectly because it comes as a managed service, has a fast & friendly web interface, and offers great flexibility via Flow, PowerApps, and Power BI integration”

28. Dynamics 365 FO – Conceptual architecture Devices(Cross platform: Windows, Android, iOS, OS X) ALM – Lifecycle services AX ERP application(based on the AX 2012 R3 CU9 App) Partner Apps Trial & signup Trial & signup Provisioning Provisioning Deployment Dynamics AX Platform Diagnostics, Telemetry & Monitoring Visual Studio developer experience Office (Office 365, Yammer, Skype) Client Server Configuration Integration Analytics & Reporting Implementation Experience Upgrade Experience Azure SQLDB Support Experience Microsoft Platform (including Commerce/Marketplaces)

29. Dynamics 365 FO – Why Azure SQL Database? Azure SQL Database offered • Reduced Administration worries • Flexible sizing options • Consistent Performance • Fast turnaround time for fixes and improvements • Inherent High Availability and Disaster Recovery Protection • Would Managed Instance have worked? • Of course • But not available when we started in 2016.

30. Azure SQLDB Challenges

31. Azure SQLDB Challenges – Rogue User Session Challenge • A runaway process can potentially hurt the whole system • Critical processes starving for resources. Solution: • SQL Resource Governance * ( In private preview) • RG min/max definition for workload

32. Solution - SQL Resource Governance

33. Azure SQLDB Challenges – TempDB and TempTables Challenge • No ## (global) Temp Tables initially on any skews • 3 part reference (Tempdb.<schema>.Table) – not supported Solution • D365FO “TempDB” Temp Tables (Moved to AXDB) Alternative approaches • Hekaton (memory optimized) tables • Striped work tables Future Use new database scoped configuration (“GLOBAL_TEMPORARY_TABLE_AUTO_DROP”)

34. Azure SQLDB Challenges: Gaps in tooling Challenge • Monitoring of errors, performance and usage at scale Solution • Extended telemetry • Internal reliance of QueryStore • Continued usage of DMV • Emergence of WatchDog and Optimization Adviser

35. LCS Monitoring and SQL Insight

36. Learning and Recommendations • Find ways to workaround the issue • Use Azure SQLDB (ElasticPool) to reduce cost and operational overhead • Using Readable Secondary for scaling

37. Summary Azure SQL Database Migration Overview BingAds – Success with Azure SQL Database DynamicsAX 365 – Success with Azure SQL Database

38. Please evaluate this sessionYour feedback is important to us! Please evaluate this session through MyEvaluations on the mobile appor website. Download the app:https://aka.ms/ignite.mobileApp Go to the website: https://myignite.techcommunity.microsoft.com/evaluations