Transcription
Build modern database applications usingAzure SQL Database Managed InstanceTejas Shah – Senior Program ManagerMicrosoft Data Platform Teamtejasaks@microsoft.comTwitter: @mr tejsLinkedIn: https://www.linkedin.com/in/tejas-shah-72a62027/
Deadline to act beforeend of supportImpact of end ofsupportNo security updatesSQL Server 2008and 2008 R2Extended Supportends July 9, 2019Compliance concernsWindows Server2008 and 2008 R2Extended Supportends January 14, 2020201820192020Missed innovation opportunitiesFind lifecycle support deadlines at: support.microsoft.com/lifecycle
Unparalleled security and performance of SQL in a fully managed environmentSeamless andcompatibleCompetitive TCOBreakthroughproductivity &performanceIndustry-leadingsecurityThe best and most economical cloud destinationBuilt-inintelligence
Azure SQL DatabaseSingleElastic PoolManaged InstanceDatabase-scoped deploymentoption with predictable workloadperformanceShared resource model optimized forgreater efficiency of multi-tenantapplicationsInstance-scoped deployment option withhigh compatibility with SQL Server and fullPaaS benefitsBest for apps that require resourceguarantee at database levelBest for SaaS apps with multipledatabases that can share resourcesat database level, achieving bettercost efficiencyBest for modernization at scale withlow friction and effort
Surface area ofManaged InstanceMI is always on latest and greatest SQL engine versionYour code can be SQL deployment model aware ifnecessaryCombining the best of SQL Server withthe benefits of a fully-managed,intelligent serviceBuilt-in HA with Always-on99.99% SLA out of the boxBuilt-in intelligent performance and security
DatabaseCompatibility BasedCertification for AppsMicrosoft Database Compatibility Level ProtectionOverall processMicrosoft database compatibility levelprotectionEasy to use tools to help you accessmigration
App developmentmade easier! Local time zone Custom instance collation Public endpoint AAD instance logins Failover groups Small instances Dev/test offer
Roadmap - Managed InstanceQ1 Australia Central 1,2, West IndiaGeo-restore (ARM template)Q2App Compat Instance level collations (GA) Customizable time zone (preview and GA)Networking security Removed Internet and Azure DNS dependencyConnectivity choices Public endpoint for data traffic Configurable connection policy (proxy/redirect)More PaaS capabilities Geo-restore PITR for deleted DBs Create database from Azure Portal 4 vCores and MSDN subscriptionRegions Brazil South, South Africa
Identity & accessmanagementAzure Active Director yNetworksecurityDataprotectionVNET,Advanced ThreatVulnerability(Always Encr ypted)Ser vice EndpointsProtectionAssessmentFirewall Rules, NSGSQL AuditEncr yption-at-restAuthentication(TDE)Row/Column -levelsecuritySecuritymanagementEncr yption-in-useMulti-FactorSQL AuthenticationThreat DetectionEncr yption-in-flight(TLS)Audit Integration withLog Analytics andData Discover y &Event HubsClassificationDynamic Data Masking Partner Solutions: Imperva SecureSphereIntegration withAzure Security Center
(2) Possible threat toaccess / breach dataAppsAuditLogAzure SQL DatabaseThreat Detection(1) Turn on Threat Detection(3) Real-time actionable alerts
SQLi attempt - An application generated a faulty SQL statement,which may indicate a potential vulnerability of the application toSQL injection. SQLi attack - Potential exploitation of application codevulnerability to SQL Injection, which may indicate a SQL Injectionattack. Someone has logged from an unusual location - change in theaccess pattern from an unusual geographical location An unfamiliar principal successfully logged- - change in theaccess pattern using an unusual SQL user. Someone is attempting to brute force SQL credentials abnormallyhigh number of failed logins with different credentials.Someone has logged from a potentially harmful application Data exfiltration by volume - someone has extractedanomalous amounts of data in an hour or using asingle queryData exfiltration by location - someone has backupdatabase to an unusual storage location,Unsecure commands - Someone has executedunsecure commands (e.g. xp cmdshell )
Azure Traffic ManagerUser deviceEnd user trafficCapabilities Active / Standby All databases in the instance areautomatically replicated Automatic or manual failover Read-write listener for read-writedatabase connections Read-only listener for read-intendeddatabase connectionsScenarios Transparent recovery from outage Load-balancing read-only workloads Failback after outage is mitigatedIngress LBIngress LBDNS zoneGeo-replicationApplicationApplication VMApplication VMApplication VMDB trafficVNETPrimary regionFailover grouplistenerVNETSecondary region
CloudOn-premises
Running SSIS on-premisesOS: Windows/LinuxSCALABILITY: Scale-Out featureEDITION: Standard/EnterpriseTOOLS: SSDT/SSMS to design/deploy/manage/execute/monitor packagesEXTENSIBILITY: ISVs can buildcomponents/extensions on SSISOn-premisesPRICING: Bundled w/ on-prem SQL ServerData sourcesSSISSQLSQL Server
Running SSIS in the cloudLIFT & SHIFT: Use Azure SQL DB/ManagedInstance to host SSISDBAzure Data FactorySSIS Integration RuntimeSQLData sourcesSSIS ETLSQL DatabaseManaged InstanceSCALABILITY: Use ADF to provision a managedcluster of Azure VMs dedicated to run yourpackages – Azure-SSIS Integration Runtime (IR)EDITION: Standard/EnterpriseCloudTOOLS: SSDT/SSMS ADF app todesign/deploy/manage/execute/monitorpackages (activities)On-premisesData sourcesSSISSQLSQL ServerEXTENSIBILITY: ISVs can buildcomponents/extensions SaaS on SSIS in ADFvia custom setup 3rd party licensingPRICING: Pay per hour Azure Hybrid Benefitto Bring Your Own License (BYOL) ), see here
Running SSIS in the cloudHYBRID: Join Azure-SSIS IR to a VNet that isconnected to your on-prem network to enableon-prem data access, see hereAzure Data FactorySSIS Integration RuntimeSQLData sourcesSSIS ETLSQL DatabaseManaged InstanceMODERNIZATION: Schedule first-class SSISactivities in ADF pipelines via SSMS andchain/group them w/ other activities via ADFappCloudOn-premisesCOMPLEMENTARY: Splice/inject builtin/custom/Open Source/3rd party SSIS tasksand transformations in ADF pipelinesVNETREADINESS: General Availability (GA) w/ 24/7live-site supportData sourcesSSISSQLSQL Server
DocumentWhen to use itWhat is a Managed InstanceHigh level details about SQL MI – service description and positioningAzure SQL Database pricing pageBusiness model and pricing detailsAzure Hybrid Use Benefit (AHUB)Discount details for customers with SQL Server licensesFeature comparison: Azure SQL Database versus SQL ServerHigh level feature availability matrix and need comparison withSQL Server and rest of SQL DatabaseAzure SQL Database Managed Instance T-SQLdifferences from SQL ServerDetailed functional behavior of SQL MICreate Managed Instance - TutorialHow to create SQL MI and connect to it (quick getting started guide)How To: Configure a VNet for Azure SQL DatabaseManaged InstanceHow to makes sure that VNet is compliant with SQL MI requirementsHow To: Configure a Custom DNS for Azure SQL Database ManagedInstanceNetworking misconfiguration is currently the most frequent reasonthat prevents customers from deploying SQL MI successfullyConnect your application to Azure SQL DatabaseHigh level of detail how to connect app to MI (supported scenarios,high level steps, links on detailed how-to)SQL Server instance migration to Azure SQL DatabaseManaged InstanceVarious options to migrate application to SQL bscription-level quotas and official process to obtain larger quotaAzure Support plansExplore the range of Azure support options and choose the plan thatbest fits, whether you're a developer just starting your cloud journeyor a large org. deploying business-critical, strategic applicationsHow to create Azure support requestStep by step instructions to open support ticket
DocumentWhen to use itManaged Instance ARM template referenceSQL MI management through ARM templates & PowerShell(official docs and blogs)Create SQL MI using ARM templatesChange size of SQL MI using PowerShellCross-instance point-in-time restore in Azure SQL DatabaseManaged InstanceHow to restore database to another instanceCAT Blog: CPU and Memory Allocation on Azure SQL DatabaseManaged InstanceExplains how to interpret various information exposed in SSMS andDMVs regarding resource allocation is SQL MICAT Blog: Storage best practices in General PurposeIn this article, we describe database storage architecture on Azure SQLDatabase Managed Instance (MI), for General Purpose (GP) instancesspecifically. We also provide a set of best practices to help optimizestorage performanceCAT Blog: Consume SQL MI Error LogHow to filter out unnecessary info from SQL error log and focus onwhat's important to your app using sp readmierrorlogCAT Blog: Real time performance monitoring for Azure SQL DBManaged InstanceConfiguring and suing Telegraf for real-time perf. monitoring in SQLManaged InstanceBLOG: How to send emails in SQL MI using DbMailSCOM Management Pack for SQL MIThe blog announcement for SCOM MP for SQL MI and scope details
Create database from Azure Portal 4 vCores and MSDN subscription . Partner Solutions: Imperva SecureSphere Row/Column-level security Encryption-in-use (Always Encrypted) SQL Audit Data Discovery & Classification Dyna
