SQL Server 2008 & 2008 R2 - PowerPoint Presentation

Slide1

SQL Server 2008 & 2008 R2End of supportLevel 300

Speaker Name

TitleSlide2

Current support level

End mainstream

End extendedSQL Server 2014Currently supporting all versionsJuly 9, 2019July 9, 2024SQL Server 2012SQL Server 2012 SP2+ is in mainstream support until CY 2017July 11, 2017July 12, 2022SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 and 2008 R2 are in extended support which includes security updates, paid support, and requires purchasing non-security hotfix supportJuly 8, 2014July 9, 2019SQL Server 2005SQL Server 2005 support ended on April 12, 2016April 12, 2011April 12, 2016

Learn more about the SQL Server support lifecycle: support.microsoft.com/lifecycle/

SQL Server 2008 and 2008 R2 will no longer be supported starting on

July 9, 2019

.

When will end of support happen?Slide3

Modernize to innovate

Grow your environments with data, analytics and the cloud

Mitigate risks with platform security and complianceThere will be no access to critical security updates, opening the potential for business interruptions Avoid data breaches, unplanned maintenance costs and begin modernization before the end of support

Upgrade with better cost efficiency

Maintaining

legacy systems can get expensive quicklySlide4

Security & compliance

At the end of support for SQL Server 2008 and SQL Server 2008 R2, there will be no more security updates and hotfixes from Microsoft, opening the door to potential business interruptions.

Don’t take the risk of losing precious data or having issues with your business applications performance because of potential weaknesses of your environment by not upgrading!Without security updates you may fail to comply with standards and regulations that can seriously hamper your ability to:Protect against hackers, malware with root access to machines, man in the middle scenarios, and government subpoenasSecurely purge for customer data, leaving it vulnerable to access by outside partiesUtilize E2E data protection across SQL Server and frequently used together servicesRegulatory compliance standards may no longer be met at end of support, such as:GDPR for companies who do business in the EUPCI DSS for storing and processing payment dataHIPAA for medical patient privacyGLBA for financial institutionsSlide5

Discover

Data identification

TrackingManageAccess control Granular authorization ProtectData securityPrivacy by designData Discovery & ClassificationScan, identify, and label columns containing potentially sensitive data in your database Access control Administrators can manage and govern access to personal data with Windows authentication and Azure Active Directory authentication Transparent Data EncryptionHelp secure personal data through encryption at the physical storage layer using encryption-at-restMetadata queries, SQL queries and statementsHelps you search and identify personal data using queries

Role-based access control

Apply role-based access control to help manage authorization policies in the database, and to implement the separation of duties principle

Always Encrypted

Prevent unauthorized, high-privileged users from accessing data in transit, at

rest, and while in use

Full text queries

Using full-text queries against character-based data in SQL Server tables

Row-level security

Prevent access to rows in a table (such as those that may contain sensitive information) based on characteristics of the user trying to access the data

Always On Availability Groups

Maximize the business continuity by enabling High Availability and Disaster Recovery built-in

Azure Data Catalog

Unlock tribal knowledge by sharing information about data usage and intent throughout the organization

Dynamic Data Masking

Control access to sensitive data by enabling how much data to reveal with minimal impacts to app layers

SQL Database Threat Detection

Get help detecting anomalous database activities indicating potential security threats to the database

Report

Documentation

Assessment of security

SQL Server Audit

Verify changes to data that occur in a

SQL Server table

SQL Server Audit

Understand ongoing database activities, and analyze and investigate historical activity to identify potential threats or suspected abuse and security violations

SQL Server Audit

Maintain audit trails and gain useful input for performing a Data Protection Impact Assessment (DPIA)

Vulnerability assessment

Reports that can serve as a security assessment for your database. These reports can also be used as part of a Data Protection Impact Assessment (DPIA)

DATA Security & Privacy solutions from SQL Server

Help safeguard

your business with a suite of security features to help with complianceSlide6

Cost efficiency

At the end of support for SQL Server 2008 and 2008 R2, your maintenance costs may increase as your IT staff maintains your aging hardware, keeps intrusion and firewalls up to date, and handles network segmentation.

Add six more years of product support beyond the end of Extended Support with SQL Server Extended Security UpdatesExtended supportend dateExtended Security Updates support periodWindows Server 2008/R2January 2020January 2020 – January 2026SQL Server 2008/R2July 2019July 2019 – July 2025Windows Server Extended Security Updates and SQL Server Extended Security Updates support period for 2008/R2 versionsSlide7

Modernizeto innovate

SQL Server family have evolved a lot since

SQL Server 2008 and 2008 R2. With SQL Server 2017, customers don’t just get an update – they get in-memory performance across workloads, mission-critical high availability, end-to-end mobile BI, and in-database advanced analytics with security features built-in to help protect your data at rest and in motion – now on Linux and Docker containers

HYBRID

On-premises

Private cloud

Cloud

Security

and performance

Flexibility

of choice

Reason over

any data, anywhere

Data warehouses

Data lakes

Operational databases

Data warehouses

Data lakes

Operational databases

The MODERN DATA EstateSlide8

Pick your target destinationEasy and seamless migration to your modernization target of choice with Azure Database Migration Service

On-premises:

Complete data platform with mission critical performance and real time reportingPrivate Cloud: Dedicated business resources inside the enterpriseIaaS: Keep management while reducing TCO with Azure VMsPaaS: Fully managed solution with high availability and scalabilityShared Infrastructure / Lower costDedicated Infrastructure/ Higher costHigher administrationLower administrationHybrid CloudPhysicalSQL ServerPhysical Machine (raw iron)IaaSSQL Server in Azure VMVirtualizes MachinesVirtualSQL Server Private CloudVirtualized Machine + AppliancePaaSAzure SQL DatabaseVirtualized DatabaseSQL

SQLSlide9

5 reasons SQL Server & Windows Server are better together

Security built into both

Major cost savings and unprecedented performance boostsDatabase uptime and reliability synergiesReach insights faster by running analytics at the point of creationConsistent data environment across hybrid cloud environmentsSlide10

Windows Server 2016 &SQL Server 2017Hardware innovations

Hardware innovations allow flexibility in High Availability and Disaster Recovery solutions in Windows Server 2016 with multi-domain and non-domain clusters

Leverage the latest hardware innovations for your SQL Server 2017 solutions by upgrading to Windows Server 2016Support for 24TB of memory from OS supportWindows Server 2016 enables support for 640 cores Support for Storage Class Memory (SCM) such as NVDIMM-N to enable superfast disk IOSQL Server 2016/2017 Transaction Log tail on SCM to speed up log commits and to accelerate the databaseFlexibilityFlexibility in building redundant storage for business critical databases with Storage Spaces Direct with Direct Attached Storage (DAS) replication across nodes. A cost effective way to build redundant storage sub-systems. Achieve same SAN system redundancy with better TCO and faster data IOSQL ClusterDBDB

DB

DBSlide11

SQL

more than an upgrade with

SQL server 2017With SQL Server 2017, your business doesn’t just receive an updated database. SQL Server 2017 is the industry leading data platform that provides built-in security, enhanced performance, and analytics to modernize your business. Now available also on Linux, SQL Server 2017 is the first commercial database with AI built-in and out-of-the-box.Transform your enterprise applications with a complete and robust mission critical data platform by upgrading your current SQL Server to SQL Server 2017.#1 OLTP performance¹#1 DW performance¹#1 price/performance¹#1 price/performance in TPC-H non-clustered as of 9/1/2017 - http://www.tpc.org/3323#1 TPC-H non-clustered benchmark as of 9/1/2017 - http://www.tpc.org/3323#1 TPC-E performance as of 9/1/2017 - http://www.tpc.org/4075 Slide12

Enhanced Integration Services

Data Quality Services

Master Data ServicesMaster Data Services Add-in for ExcelEnhanced productivity and performancePower ViewConfigurable reporting alertsReporting as SharePoint Shared ServiceData-tier application component project FileTable build on FILESTREAMRemote Blob Storage with SharePoint 2016Statistical Semantic SearchContained Database AuthenticationAutomatic Plan CorrectionEnhanced connectors, new transformations, object-level security, ragged hierarchies**Mobile BIEnterprise-grade Analysis ServicesIn-Memory analytics with Analysis Services Tabular ModelEnhanced multidimensional modelsModernized Reports and Dashboard with support for Mobile BICreate mobile reports using the SQL Server Mobile Report PublisherConsume with Power BI mobile appsHA for StreamInsight, complex event processingImport PowerPivot models into Analysis ServicesAdvanced tabular modeDirect QueryAlways EncryptedDynamic Data MaskingRow-Level SecurityAuditingCC certification at EAL2 level for SQL Server 2016Backup encryption supportEnhanced separation of dutiesDefault schema for groupsHybrid Transactional and Analytical Processing with real-time insights using In-Memory OLTP and Clustered Columnstore

In-Memory Data Warehouse with Clustered Columnstore IndexesUnparalleled scalability with Windows Server 2016, with 24TB memory and Windows Server

2016 max cores

Automatic failover between three synchronous replicas. Up to eight secondary replicasBuffer Pool Extension to SSDs

Adaptive Query Processing

Resource Governor with CPU, Memory and IO Governance

Delayed Durability of Memory-Optimized Tables

Query optimization

enhancements

Query Store

Local DB runtime (Express)

Machine Learning Services with built-in support for Python and R

Operationalize Machine Learning algorithms with In-Database Machine Learning supportRRE APIs with full parallelism and no memory limits for scale/performanceBuilt-in In-memory Advanced AnalyticsAdvanced data miningOLTP PerformanceSecurity

Business Intelligence

Disaster Recovery environment in Azure VMs using Always On Availability groups

Stretch database

Hybrid scenarios with SSIS

Enhanced backup to Azure

Easy migration to the cloud

Secure backups to Azure with managed backups

Hybrid Cloud

Operational analytics

Enhanced In-Memory DW with Clustered Columnstore

Big Data integration with Hadoop and Azure Blob Storage

Big Data Queries with T-SQL

Enhanced database caching

Advanced

Analytics

Support for Linux

(RHEL,

Suse

, Ubuntu)

Support for Docker containers

and Kubernetes

Enhanced Always On Availability Groups with easy GUI-based Wizard

Platform

Data Warehousing

& Big Data

What’s New in SQL Server 2017 Since

SQL Server 2008 & 2008 R2

Programmability Enhancements

JSON support

Spatial features, Full Globe and arcsSupport for Temporal TablesGraph data support

Enterprise Information Management (EIM)

High Availability & Disaster RecoverySlide13

Accelerate app modernization with sql server

Challenge

ImpactApplication certification lagging behind faster cadence of SQL Server releaseInability to offer latest version of the application on modern SQL Server platform Upgrade customers to latest version on the applicationRevenueInability of customer to leverage latest innovations in the applicationSupporting ISV customer on legacy application/SQL versionsHigher cost to support multiple application versionsHigher support cost to troubleshoot on legacy SQL ServerVersion specific scripts/methodology to monitor and troubleshootSlide14

OLTP performance

Industry leading performance in SQL Server 2017

FeaturesIn-Memory OLTPReal-time Operational Analytics (HTAP)Adaptive Query ProcessingAutomatic TuningSlide15

Optimized performance of transaction processing, data ingestion, data load, and transient data scenariosMemory-optimized tables outperform traditional disk-based tables, leading to more responsive transactional applicationsMemory-optimized tables also improve throughput and reduce latency for transaction processing, and can help improve performance of transient data scenarios such as temp tables and ETL

In-memory Online transaction processing (oltp)

UserSQL ServerApp

On-disk

In-memory

In-memory OLTPSlide16

Support for Columnstore and In-Memory allows you to generate analytics in real-time, direct from your transactional databasesAnalytics performed on operational data with minimal overheadReduce storage needs with ColumnStore column compression and up to 100x faster queries

Real-time operational analytics with hybrid transactional-analytical processing (HTAP)

0100101010110In-memoryColumnStoreIn-memory OLTP+ETLSlide17

Improved efficiency with Adaptive Query ProcessingOptimize memory grants for repeatable queries to avoid over or under allocatingAdjust data join strategy for small or large tables tospeed joins Batch mode for memory grants feedback and adaptive joins

adaptive query processing

QueryMemory grantLearnRun queryBefore101010101010101001101010010101101010010101101010010101101010101010010101101010010101101010010101101010101010010101101010After

101010101010101001010101010101001010

101010101010101001

101010101010101001

101010101010101001

101010101

101010101

101010101

101010101

101010101010101001

Adaptive

Query Processing

Spill to disk

101001010110

All in memorySlide18

Automatic tuning is a continuous monitoring and analysis process that constantly learns about the characteristic of your workload and identify potential issues and improvementsAutomatic tuning notifies you when a potential performance issue is detected, and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems.

Automatic tuning

AdaptVerifyLearnSlide19

Security

Enterprise-grade security to protect, control and manage data

FeaturesAlways EncryptedRow-Level SecurityDynamic Data MaskingSQL Server AuditTransparent Database EncryptionSlide20

Always Encrypted allows clients to encrypt sensitive data at rest and in motion without impacting your database performanceEncryption is transparent to applications, automatically encrypt and decrypt sensitive data in the client applicationsEncryption keys sit in the trusted area with the customer and are not revealed to the Database Engine

Query encrypted data without needing to first de-encrypt

ALWAYS ENCRYPTED QueryClient sideServer sideData setEnhancedSQL ServerLibraryCIPHERTEXTColumn master keyColumn encryption keyCustomerCredit card #Exp.

Denny Usher

0x7ff654ae6d

5/17

4949-8003-8473-1930

Customer

Credit card #

Exp.

Tim Irish

4839-2939-1919-3987

7/19

Denny Usher

4949-8003-8473-1930

5/17

Alicia Hodge

9000-4899-1600-1324

4/18

Credit card #

1x7fg655se2e

0x7ff654ae6d

0y8fj754ea2c

SQL

Always EncryptedSlide21

Fine-grained access control over specific rows in a databasePrevent unauthorized access when filtering in multitenant applicationsEnforcement logic inside the database and schema bound to the table

Centralize Row-Level access logic within the database

Provide end-user specific access control over sensitive data in a customer database. For example, customer call center, account manager see different dataRow-level securitySQL Server database

Customer 1

Customer 2

Customer 3Slide22

Control sensitive data access Non-privileged users cannot see or access sensitive dataApply data masking in real-time to query results based on security policy Easy configuration using Azure Portal when using Azure SQL Database

Easy configuration using DDM functions in T-SQL

Dynamic DATA maskingSQL Server 2017Real-time data masking and partial masking

Table.CreditCardNo

4465-6571-7868-5796

4468-7746-3848-1978

4484-5434-6858-6550

CreditCardNo

XXXX-XXXX-XXXX-5796

XXXX-XXXX-XXXX-1978

XXXX-XXXX-XXXX-6550Slide23

Enable, store, and view audits on various server and database objects with dedicated tools Define multiple server or database audits to run simultaneouslyTrack and log user-defined database-level audits with the help of predefined templates

SQL server audit

Retain an audit trail of selected events Report on database activitiesAnalyze reports for events and trends Slide24

All customer data encrypted at restEncryption keys managed can be managed by customer or by Azure Key VaultNo application changesTransparent encryption/decryption of data using hardware acceleration

Transparent Database Encryption

SQL DatabaseSlide25

High availability & disaster recovery

Meet HADR requirements for your SQL Server database systems

FeaturesAlways On Availability GroupsSlide26

Primary

Sync/

Async ReplicasCross-operating systemHigh availabilityWindowsLinuxSync/Async ReplicasAlways On cross-platform capabilities with High Availability and Disaster Recovery for Linux and Windows Support for clusterless Availability Groups Ultimate High Availability with OS-level redundancy and low-downtime migrationLoad balancing of readable secondariesAutomatic Failover of Business Critical workloads and application in few secondsAutomated Disaster Recovery environmentSupport for Disaster Recovery environment in Azure VMsAlways on availability groupsSlide27

Business intelligence

Powerful modelling and reporting for business insights

FeaturesSQL Server 2017 Analysis Services Tabular ModelsSQL Server 2017 Reporting ServicesPower BI DashboardsSlide28

Transform complex data into business friendly modelsCombine data from multiple data sources and apply business rules and securityMatch performance to the speed of businessExplore models and gain instant insights using your favorite visualization tool

SQL Server Analysis services

VisualizeExcelPower BI

Business logic & metrics

Data modeling

Security

Lifecycle management

In-memory cache

SQL Server Analysis

Services

BI semantic model

Third-party tools

Azure Blob

Files

Teradata

SQL

Oracle

Others

IBM

Data sources

Excel

Sybase

Data feedsSlide29

Powerful, lightweight analytical modelingModern connectors for big data sources, additional databases, Azure stores, and diverse modern data sourcesRich data transformations and mashups

In-memory performance

Fast; don’t need to build out data warehouseTabular ModelsSSASData sourcesVisualizeExcelTableauPower BIMySQLData analysisTransform

Sentiment

Churn

Revenue

SharePoint

Azure Blob

HDInsight

Files

Dynamics Online

Others…Slide30

On-premises solution for deploying and managing reportsPaginated reportsDesign beautiful, document-style reports for export to Word, Excel, PowerPoint, PDF, and printing

Mobile reports

Create responsive, interactive reports optimized for mobile devicesModern web portalAccess both types of reports in one modern,cross-browser web portalSQL Server 2017 Reporting ServicesSlide31

Get more from existing Reporting Services investments

Pin report items to Power BI dashboards

Monitor the most important metrics and trends in one placePin charts, gauges, maps, and images as tiles to Power BI dashboardsDrill through to source reportsRefresh tiles on a schedulePower bi dashboards Slide32

Data warehousing & BIG Data

Bring together data from different locations to find insights

FeaturesColumnstorePolybaseSlide33

Data stored as columns

Clustered Columnstore Indexes with wide support of data types

Online non-clustered columnstore index build and rebuild support addedReduce data warehouse storageProvides a very high level of data compression, typically 10x, to reduce your data warehouse storage cost significantly. Indexing on a column with repeated values vastly improves performance for analytics and ad hoc queriesMore data fits in memoryBatch-mode executionColumnstore

Existing Table

Row Groups

Column Segments

Columnstore

Compressed Column SegmentsSlide34

Execute T-SQL queries against relational data in SQL Server and semi-structured data in Hadoop or Azure Blob StorageLeverage existing T-SQL skills and BI tools to gain insights from different data stores

polybase

SQL ServerHadoopAzure Blob StorageQueryResultsSlide35

Advanced analytics

Discover new insights about your business

FeaturesMachine Learning Server with Python and R Slide36

High-performance machine learning and AI wherever your data lives, on-premises or in the cloudSupport for pre-trained Microsoft ML algorithmsThe best AI innovation from Microsoft andopen-source. Support for thousands of open source machine learning algorithms

Simple, secure, and high-scale operationalization and administration

Deep ecosystem engagements to deliver customer success with optimal total cost of ownershipMachine learning with built-in support for python and rSQL2017Slide37

Hybrid cloud

Extend to the cloud and get more from your data

FeaturesHigh Availability and Disaster Recovery in Azure VMsStretch Database Backup to AzureSlide38

Extend on-premises Always On Availability Groups to Microsoft Azure by provisioning one or more Azure VMs with SQL Server

Add them as replicas to your on-premises Availability Group

Disaster recovery solution for your SQL Server databases in a hybrid environmentHybrid availability and disaster recovery for SQL server in azure VM

Secondary replica

On-premise network

Primary replica

WSFC cluster

Asynchronous commit

Secondary replica

File share witness

Domain controller

Synchronous commit

VPN tunnelSlide39

Take advantage of cloud economics of lower cost compute and storage without full migration

Creates a secure linked server definition in the

on-premises SQL ServerTargets remote endpoint with linked server definitionProvisions remote resources and begins to migrate eligible data, if migration is enabledQueries against tables run against both local database and remote endpointStretch databaseOn-premises instanceAzureInternet boundaryLinked serversRemote dataRemote endpointLocal dataEligible dataLocal DBSlide40

Enhanced backup to Azure

Granular control of the backup schedule

Local staging support for faster recovery and resiliency to transient network issuesSupport for system databasesSupport for simple recovery modeBackup to Azure block blobsCost savings on storageSignificantly improved restore performanceMore granular control over Azure StorageAzure Storage snapshot backupFastest method for creating backups and running restoresSQL Server database files onAzure Blob StorageBackup to azureSlide41

platform

Flexibility of platform choice with SQL Server 2017

FeaturesSupport for Linux (RHEL, Suse and Ubuntu)Support for Docker containers and KubernetesSlide42

SQL Server on Linux provides choice across platformsBuild intelligent applications on any data, any platform, and any language on-premises and in the cloudLinux distributions including Red Hat Enterprise Linux (RHEL), Ubuntu, and SUSE Enterprise Linux (SLES)

Support for linuxSlide43

Container platforms for SQL Server provide benefits such as:Faster time to deployment

Develop on the OS of your choice

Packaging dependencies with SQL ServerHigher density, lower disk space requirementsDeploy exactly what you development and testSupport for docker and kubernetesSlide44

Enterprise information management

A rich set of features designed to improve overall relevance and value of organizational information

FeaturesEnhanced SSISMaster Data ServicesData Quality ServicesSlide45

SQL Server 2017 offers a full suite of tools for Enterprise Information ManagementHelp produce accurate, trustworthy dataDeliver credible, consistent data to the right users with end-to-end data integration, cleansing and data management

Enterprise Information Management

Data quality servicesKnowledge-based data cleansing & matchingStandalone & SSIS integratedMaster data servicesExcel UI to manage data & dimensionsRapid, easy data loadingEnhanced integration servicesIntegrated deployment & management Improved user experienceCleanseManage

IntegrateSlide46

SQL Server Integration Services (SSIS) is a platform for building enterprise-level data integration and data transformation solutions – primarily for ETL processingScenarios in which SSIS is useful include:Assessing data quality

Cleansing and standardizing data

Merging data from heterogeneous data storesImplementing ad hoc data transfersAutomating administrative tasksOrchestrating Azure data processesEnhanced sql server integration services Slide47

SQL Server Master Data Services (MDS) has been designed to deliver credible, consistent data with user-centric data governancePerformance is improved for staging millions of records using the staging stored procedureEnables organizations to:

Discover and define non-transactional lists of data

Compile maintainable, reliable master listsDeliver and manage Master Data Management (MDM) solutionsMaster data servicesSource systemEDWMDS repositoryWCF data service

InitializeETL

Master data manager

Excel with MDS add-in

Outlook

Notifications

Administration

Data changes

MDSSlide48

SQL Server Data Quality Services (DQS) is a knowledge-driven data cleansing serviceDQS can help deliver complete, valid, consistent, and accurate dataIt performs various cleansing processes:

Data correction

StandardizationEnrichmentMatching—typically for de-duplicationData quality services KnowledgemanagementDataquality projectSource data

Integrated profiling

Processed data

Knowledge base

Discover/ explore data

Manage knowledge/ matching policy

Deduplicate (match)

Correct/ standardize (cleanse)

Build

UseSlide49

Programmability enhancements

Code, programming models and interfaces

FeaturesJSON supportTemporal TablesSlide50

JSON functions in SQL Server enable you to combine NoSQL and relational concepts in the same databaseParse JSON text and read or modify valuesTransform arrays of JSON objects into table formatRun any Transact-SQL query on the converted JSON objects

Format the results of Transact-SQL queries in JSON format

Json supportNumberDateCustomerPriceQuantitySO436592011-05-31T00:00:00MSFT59.991SO436612011-06-01T00:00:00Nokia24.993[ { “Number”:“SO043659”, “Date”:“2011-05-31T00:00:00” “AccountNumber”:“AW29825”, “Price”:59.99, “Quantity”:1 }, { “Number”:“SO043661”, “Date”:“2011-06-01T00:00:00” “AccountNumber”:“AW73565”, “Price”:24.99, “Quantity”:3 },]Built-in functionsISJSONJSON_VALUEJSON_QUERYOPENJSONTransforms JSON text to tableFOR JSONFormats result set as JSON textSlide51

Built-in support for providing information about data stored in the table at any point in timeCan be used as a data recovery toolAutomatically keep history of the data in the tableAudit data changes and perform data forensics

Calculate trends over time

temporal tablesTemporal table (actual data)History table

Temporal queries

(time travel, etc.)Slide52

SQL Server on azure virtual machines

Cloud

flexibilityOperating system choiceTuned for performanceHighly-secured and trustedHybrid connectivitySupport for Windows Server, Red Hat Enterprise Linux, SUSE Linux Enterprise Server and Ubuntu LinuxSet up and manage your SQL Server deployments in minutes, and then scale up or down Get the most out of your on-premises investments, and take advantage of all that Azure offersMeet your toughest demands with virtual machines optimized by the team that built SQL ServerProtect your entire data estate with a highly-secured database on the most compliant cloud

Easily migrate your SQL Server workloads to the cloudSlide53

Fast time-to-value with cloud flexibility

Set up and manage your SQL Server deployments in minutes, then scale up or scale down on the fly

Any SQL Server version and edition, on the VM size you need with easy migration from Azure Database

Migration ServiceSQL Server is guaranteed to run the same in Azure VMs as on-premisesDifferent VM types optimized for customer use case:General purposeCompute optimizedMemory optimizedStorage optimizedGPU optimizedHigh performance computeDifferent VM sizes based on customer need:Example: G-Series which features ½ TB of RAM and 32 CPU coresCloud flexibility Slide54

Spin up a new development environment in minutes, and only pay for what you useLow virtual machine usage costs mean you pay only cents per hour, while using all the features you need for development and testing

Develop and test in the cloud

TFS in Azure

SQL Server dev tools on-premises

SQL Server

on-premises

SQL Server in an Azure VM

Dev Work Stations

DeploySlide55

Improve business continuity in the event of disaster by placing your SQL Server Always On Availability Group replicas in virtual machinesSQL Server 2017 gives you up to eight readable replicas, which you can deploy to a fast-growing list of Azure regions around the worldSet up fast failover for database applications running in Azure

Always on availability group

Azure Region 1Secondary replicasAzure Region 2Secondary replicasPrimary replicaSecondary replicaBI Scale-outAzure VPN tunnelExpress routeCross-region connectivitySynchronous commitAsynchronous commit

On-premises

networkSlide56

Azure SQL Database

Scales

on the flyIntelligent performanceWorks in your environmentAdvanced threat protectionBusiness continuityRealize automatic performance improvements from continuous assessment and innovationChange service tiers, performance levels, and storage dynamically without downtime Easily manage and monitor business critical functions for reliable operationsDevelop your app and connect to SQL Database with the tools and platforms you preferBuild security-enhanced apps with built-in protection and industry-leading compliance

The intelligent relational cloud database service Slide57

Azure SQL Database features

OLTP performance

ColumnstoreIn-Memory OLTPQuery StoreIndex OptimizationAutomatic tuning Auto query plan correction Performance Insight in OMS Adaptive Query Processing Advanced AnalyticsNative PREDICT R ServicesSQL GraphSecurityEngine AuditThreat Detection Centralized dashboard OMS SQL FirewallRLS, Dynamic data maskingAAD and MFA Encrypt in motion (TLS)Always Encrypted (equality)TDE & BYK Service endpoint Always Encrypted (secure enclave) Vulnerability assessment High Availability & Disaster Recovery99.99% SLA Geo-restore

Active geo replicas (4) Multi-AZ

Backup and restore

Backup with health check

35 days PITR

10 years data retention

Distributed application

Change Tracking

Transaction replication

Data sync

SSIS service

Biz model & SKUs

DTU/

eDTU

<=1TB

Bigger std: S4-S12

Separate compute and storage

Azure Hybrid Benefit

Cost optimization

Intelligent PaaSSlide58

Advanced Threat Protection (ATP) provides a single go-to location for discovering, classifying and protecting sensitive data, managing your database vulnerabilities, and detecting anomalous activities that could indicate a threat to the database.

With one click, you can enable ATP on your entire database server, applying to all databases on the server.

ATP includes:SQL Threat DetectionSQL Vulnerability AssessmentSQL Information Protection (currently in preview)Advanced threat protectionSlide59

Recovery from machine failure

Critical capabilities:

Create new replicaSynchronize data Stay consistentDetect failuresFailover99.99% availabilityReads are completed at the primaryWrites are replicated to secondariesHigh-availability platformPSSSingle logical databaseWriteWriteAckAckReadwriteAckvalueDBP

S

P

SSlide60

Index tuning recommendations tailored to each DBRecommendations are based on the observed usage, and evolve as the DB workload changesSupport for CREATE and DROP index, more types of recommendations underwayIntelligent service for implementing and validating index recommendations

Full-auto mode takes care of indexes for your DB

Manual “review and apply” mode for full controlAzure SQL Database Advisor improves performanceSlide61

Continuously optimized by the platformOne-click to enablePrevent and mitigate issuesNo app changes neededTuning actionsCreate missing indexesDrop unused/duplicate indexesForce last good plan

Automatic tuningSlide62

Scalability options in Azure SQL Database

Vertical: scale up or scale down

Change service tiers for a given database as capacity needs fluctuateScale out/inScale up/downHorizontal: scale out or scale inAdd or remove databases (sharded and/or in a pool) as more or less capacity is neededPremiumBasicStandardBasicBasicBasicPremiumSlide63

Read scale-out support in the Premium and Business critical service tiers for Azure SQL Database

Load balance Azure SQL Database read-only workloads using the capacity of read-only replicas

Load balance complex analytical workloads without affecting the primary OLTP workloadRead scale-outSlide64

Geo-replicated

Restore from backup

Azure SQL Database backupsAutomatic backupsFull backups weekly, differential backup daily, log backups every 5 minutes Daily and weekly backups automatically uploaded to geo-redundant Azure StorageSelf-service restorePoint-in-time up to a second granularityREST API, PowerShell, or Azure portalCreates a new database in the same logical serverTiered retention policyBasic – 7 days Standard – 35 daysPremium – 35 days *No additional cost to retain backupsPoint-in-time restoresabcp01bl21

Azure storage

sabcp01bl21Slide65

Store specified SQL database full backups in RA-GRS blob storage for up to 10 years

Restore any backup as a new database

Define parameters for backup: weekly, monthly, yearly or week of yearLong-term data retentionSlide66

A seamless, end-to-end solution for moving on-premises SQL Server, Oracle, and other relational databases to the cloudTwo modes of migration: online and offline migrationsLearn more with the Data Migration Guide

Azure database Migration service

Azure Database Migration Service(Azure DMS) OrchestrationScale migrationResilient migrationHomogeneoussourcesHeterogeneoussourcesSlide67

Take an inventory of on-premises licenses to determine potential for conversion Convert on-premises cores to vCores to maximize value of investments 1 Standard license core = 1 General Purpose core1 Enterprise license core = 1 Business Critical core1 Enterprise license core = 4 General Purpose cores (virtualization benefit)

Azure hybrid benefit for

sql serverLicense trade-in valuesGeneral purpose vCore4x General purpose vCoresBusiness critical vCore

SQL Server cores with SA license

vCores on Azure SQL Database

SQL Server Enterprise Edition

SQL Server

Standard Edition Slide68

Choice between vCores and DTUs in Azure SQL Database as a unit of measure for available CPUUnderstand your compute requirements in the cloud vs. what you use on-premises today

Easier to right-size the destination environment by removing the guesswork of DTUs

1 on-premises core = 1 vCore on Gen4 hardwarevirtual cores for Azure SQL Database82416Number of vCores On-premisesSlide69

Migrating from SQL Server 2008 & 2008 R2 to SQL Server 2017

Migrate

Use Data Migration Assistant (DMA) migrate schema and database23OptimizeRemediate applications and perform validation tests with Database Experimentation Assistant (DEA)1Discover & AssessDownload and install the MAP Toolkit for discovery and use Data Migration Assistant (DMA) to assess schema and databaseBackup and restore: Back up taken on SQL Server 2008 or SQL Server 2008 R2 can be restored to SQL Server 2017Tools: MAP Toolkit, Database Migration Assistant (DMA), Database Experimentation Assistant (DEA)Database mirroring: Database mirroring is supported if principal is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and mirror is running SQL Server 2017. If a failover, either automatic or manual, happens such that SQL Server 2017 instance becomes principal, SQL Server 2008 or SQL Server 2008 R2 instance becomes mirror and will NOT receive changes from principal. Tools and considerationsLog shipping: Log shipping is supported if primary is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and secondary is running SQL Server 2017. If a failover, either automatic or manual, happens such that SQL Server 2017 instance becomes primary, SQL Server 2008 or SQL Server 2008 R2 instance becomes secondary and will NOT receive changes from primaryBulk load: Tables can be bulk copied from SQL Server 2008 or SQL Server 2008 R2 to SQL Server 2017Slide70

Migrating from SQL server 2008 & 2008 r2 to Azure VMs

Migrate

Use Azure Database Migration Service (DMS) to migrate schema and database23OptimizeRemediate applications and perform validation tests with Database Experimentation Assistant (DEA), using Database Migration Guide as key resource1Discover & AssessDownload and install the  MAP Toolkit to identify and discover data sources and use Data Migration Assistant (DMA) to assess your source database before migrating your SQL Server instanceTools: MAP Toolkit, Database Migration Assistant (DMA), Database Experimentation Assistant (DEA)Learn how to create an instance of SQL Server on Azure VMs here.Tools and considerationsSlide71

Migrating from SQL Server 2008 & 2008 r2to Azure sql database

Migrate

Use Azure Database Migration Service (DMS) to migrate schema and database23OptimizeRemediate applications and perform validation tests with Database Experimentation Assistant (DEA), using Database Migration Guide as key resource1Discover & AssessDownload and install the MAP Toolkit to identify and discover data sources and use Data Migration Assistant (DMA) to assess your source database before migrating your SQL Server instanceTools: MAP Toolkit, Database Migration Assistant (DMA), Azure Database Migration Service (DMS)Bulk load: Tables can be bulk copied out from SQL Server 2008 or SQL Server 2008 R2 and then bulk copied into Azure SQL DatabaseTools and considerationsSlide72

Migrating from SQL Server 2008 & 2008 r2to Azure sql database managed instance

Migrate

Use Azure Database Migration Service (DMS) to migrate schema and database23OptimizeRemediate applications and perform validation tests with Database Experimentation Assistant (DEA), using Database Migration Guide as key resource1Discover & AssessDownload and install the MAP Toolkit to identify and discover data sources and use Data Migration Assistant (DMA) to assess your source database before migrating your SQL Server instanceTools: Migration supported by Azure Database Migration Service (DMS)Bulk load: Tables can be bulk copied out from SQL Server 2008 or SQL Server 2008 R2 and then bulk copied into Azure SQL Database Managed InstanceTools and considerationsSlide73

2,000,000+

Docker pulls

“It was incredibly easy to set up and move SQL Server from Windows to Linux” Dean ChenVP of Engineering, dv01“One of the easiest migration experiences that we've had” Chris AmarisCTO, Convergent Computing“It guarantees compatibility with more target customers” Ron Dunn Lead Developer, AjiliusSQL SERVER 2017 momentumSlide74

Challenge

FinTech company’s loan intelligence platform experienced performance slowdowns with PostgreSQL

Slow query speeds and time-consuming maintenance ImpactQuery times cut from 30 to <2 seconds with SQL ServerManagement time reduced 90% Simplified dev operations with LinuxDELIVERING INSIGHTS FOR INVESTORS 15X FASTERReal-time analytics with SQL Server on Linux“The biggest benefit for us was that we were able to switch SQL Server to Linux and not have degradation in other areas.” Devon Hinton, Software Engineer, dv01Slide75

Scenario

Humantech is one of the world’s largest providers of ergonomics consulting and software services. The company decided to standardize its IT environment on one operating system in order to promote its continued growth and success. However, Humantech wanted to keep its preferred Windows database, Microsoft SQL Server, because of the solution’s reliable performance and familiar technology.

SolutionTo satisfy both needs, the company upgraded from SQL Server 2008 to SQL Server 2017 on Linux. Now, Humantech saves on IT maintenance and has an updated database that offers fast operation and strong security. “During the restore process, you simply upgrade the SQL Server 2017 database along the way. As a result, it was easy to deploy and gave us confidence in the evaluation.”- Ken Lupo, Director of Technology at Humantech“We did not want to go for a walk on the wild side. Our goal was to ensure that our solutions would be as durable, off-the-shelf, and minimally customized as possible, which is why we sought to go with a flexible database that we already knew so well.”- Jim Good, President of HumantechHumantech moves to SQL Server 2017 on Linux to reduce costs and boost efficiencySlide76

AZURE IS THE MOST COST-EFFECTIVE FOR SQL SERVER

Azure Hybrid Benefit can help customers save 55%* for SQL Server by bringing their on-premises licenses (vs. pay-as-you-use)

*Pay a reduced rate on vCore-based options and save in highly-virtualized scenarios Migrate to Azure SQL DB Managed Instance or VMsAzure SQL Database Managed Instance offers a fully-managed option to eliminate challenges with end of supportAzure virtual machines include 3 more years of Extended Security Updates for SQL Server 2008 or 2008 R2 at no additional chargeModernize when readyUpgrade in Azure when readyOr transform apps and data with Azure servicesLicense IncludedWith Azure Hybrid BenefitUp to 55 percent savingsBase rateCustomer totalSoftware Assurance costSlide77

Upgradeon-premisesUpgrade to latest version SQL Server 2017Can’t meet the deadline? Protect server workloadsPurchase Extended Security Updates to get 3 more years of security updates for SQL Server 2008 or 2008 R2. Upgrade when readySlide78

Scenario

Offer/resource

Customer needs more time to plan my upgradeExtended Security UpdatesCustomer is investing in projects on LinuxSQL Server on Red Hat Enterprise Linux offerCustomer wants to move my on-premises SQL Server and Windows Server licenses to IaaSLicense mobilityAzure Hybrid BenefitCustomer wants to move my on-premises SQL Server licenses to PaaSAzure Hybrid BenefitCustomer wants to scale through private cloudUnlimited virtualization benefits100%Customer resources fordata estate modernizationSlide79

Start your MIGRATION journey today

Assess & Migrate

Make the move2Use the Database Migration Guide to obtain Technical and Business guidanceInnovate faster with SQL Server 2017 and Azure Data Services3OptimizeExplore options to unlock your app potential with Cloud and AIRun the MAP toolkit 1DiscoverIdentify your databases to upgradeSlide80