SIRIUS COMPUTER SOLUTIONS Cloudy SQL Databases Taking your
SIRIUS COMPUTER SOLUTIONS Cloudy SQL Databases Taking your SQL Workloads to the Cloud Kyle Quinby – Funky. Desk. com www. siriuscom. com 10/6/2020 1
Intro – Everything As a Service Paa S Caa. S www. siriuscom. com Iaa. S MS SQL Saa Maa. S Xaa. S S 10/6/2020 2
Intro - A Brief History of Cloudy MS SQL Paa. S • Azure • Introduced in 2010 • AWS • Introduced in 2012 • NOT “Something new” www. siriuscom. com 10/6/2020 3
Intro – The Spectrum of SQL Today www. siriuscom. com 10/6/2020 4
Cloud – Paa. S Recoverability • Azure SQL • • Built-in backup levels Automatic, 5 -10 minute recovery points 7 -35 days for free depending on tier Can extend with Recovery Vault up to 10 years www. siriuscom. com 10/6/2020 5
Cloud – Paa. S Recoverability • AWS RDS • • • Also automatic and integrated Defined 30 minute maintenance window Configurable retention policies, 1 -35 days Point-in-time within the retention period Can script longer term retention www. siriuscom. com 10/6/2020 6
Paa. S – Security • Any cloud • As secure as you make it • Can you picture an insecure on-prem SQL server? www. siriuscom. com 10/6/2020 7
Paa. S – Security • Azure • Firewall enabled by default • SQL and Azure AD Authentication • Encryption • • • TLS – In motion TDE – At rest Always Encrypted www. siriuscom. com 10/6/2020 8
Paa. S – Security • Azure • Threat Detection • Dynamic Data Masking • • Credit Card data for example XXXX-XXXX-1234 www. siriuscom. com 10/6/2020 9
Paa. S – Security • AWS • Isolate RDS instance in Virtual Private Cloud (VPC) www. siriuscom. com 10/6/2020 10
Paa. S – Security • AWS Identity and Access Management (IAM) policies • RDS Encryption for Data at Rest (TDE) • SSL for Data in Motion www. siriuscom. com 10/6/2020 11
Cloud – Paa. S High Availability • Azure SQL • Active Geo Replication (Always On tech) • Use of failover groups • Manual failover (auto in preview) • Readable secondaries www. siriuscom. com 10/6/2020 12
Cloud – Paa. S High Availability • AWS RDS • Multi-AZ deployments • Automatic Failover • Read Replicas www. siriuscom. com 10/6/2020 13
Paa. S – Measuring Performance – Azure DTU www. siriuscom. com 10/6/2020 14
Paa. S – Measuring Performance • Azure - DB Service Levels • Basic/Standard/Premium • Influence maximums for different KPIs • http: //dtucalculator. azurewebsites. net/ Maximums Basic Standard Premium Storage 2 GB 1 TB 4 TB DTUs 5 3000 5000 IOPs / DTU 2. 5 48 www. siriuscom. com 10/6/2020 15
Paa. S – Measuring Performance • Azure - DB Service Levels www. siriuscom. com 10/6/2020 16
Paa. S – Measuring Performance • Azure • Automatic Index Tuning • Automatic Plan Correction • AWS also www. siriuscom. com 10/6/2020 17
Paa. S – Measuring Performance • AWS - DB Service Levels • • • General Purpose Compute Optimized Memory Optimized Storage Optimized Accelerated Computing www. siriuscom. com 10/6/2020 18
Paa. S – Flexibility • Azure – Elastic Database Instances www. siriuscom. com 10/6/2020 19
Paa. S – Flexibility • Azure – Elastic Database Instances • Horizontal and Vertical Scaling and Auto scaling www. siriuscom. com 10/6/2020 20
Paa. S – Flexibility • AWS – Burstable Databases • T 2 family • Allows for lower cost general operation with ability to “Burst” to full CPU capacity when needed. • Horizontal scaling possible but not with MS SQL. • My. SQL, Postgre. SQL, Maria. DB can scale out to 5 read replicas • Aurora supports up to 15 read replicas www. siriuscom. com 10/6/2020 21
Paa. S – Compatibility Challenges • Azure Limitations • • • No CLR support No cross-database queries/transactions/linked servers No user initiated backups or database snapshots No No No DB mirroring Database mail Distributed Transactions (DTC) Filestream extended SPs SQL Agent www. siriuscom. com 10/6/2020 22
Paa. S – Compatibility Challenges • AWS No access to system tables No support for Reporting Server, Integration Services or Analysis Services • Must be run from a standard VM • No Bulk Insert • No Filestream • No Distributed Transactions (DTC) • No Database Mail • No Maintenance Plans • No Service Broker endpoints • • www. siriuscom. com 10/6/2020 23
Cloud – Iaa. S Considerations • Treat as another Data Center • WHERE ARE MY CLIENTS ? ? ? • Network Connectivity is huge • AWS or Azure both have same basic connectivity options • Express Route / Direct Connect • Guaranteed Performance and SLA’s • Site to Site VPN • “Cranky Uncle Internet” to deal with www. siriuscom. com 10/6/2020 24
Cloud – Iaa. S License Portability • Both platforms allow for a “BYOL” scenario • Can allow for ~30% savings on total costs • Basically pay for your compute, memory and storage only • CHECK YOUR LICENSES STATUS • Software Assurance generally covers you www. siriuscom. com 10/6/2020 25
Migration – Getting from here to there • BACPAC export/import • SQL Push Replication • Azure - Database Migration Assistant • • • Analyze or Move Identify Migration Blocking Issues Identify Partially supported or unsupported features Sources – SQL 2005 – SQL 2017 (on Windows) Targets – SQL 2012 – SQL 2017 (Windows or Linux) and Azure SQL DB www. siriuscom. com 10/6/2020 26
Migration – Getting from here to there • Azure - Database Migration Service (Preview) • • Use DMA to migrate Schema Establish network connectivity to target Vnet Can validate schema, Data consistency and performance Useful for large planned migrations www. siriuscom. com 10/6/2020 27
Migration – Getting from here to there • AWS • Native restore supported • Database Migration Service • Works in conjunction with Schema Conversion Tool • Can be set to continuously replicate data in an ongoing fashion • SQL 2005 – SQL 2017 Sources and Targets www. siriuscom. com 10/6/2020 28
Azure Database Managed Instance (Preview) • All the benefits of Paa. S with the compatibility of Iaa. S • Closer to AWS RDS offering for SQL but with better compatibility • Fully VNET isolation similar to VPC www. siriuscom. com 10/6/2020 29
Azure Database Managed Instance (Preview) www. siriuscom. com 10/6/2020 30
Azure Database Managed Instance (Preview) www. siriuscom. com 10/6/2020 31
THANK YOU www. siriuscom. com
- Slides: 32