BÂLE BERNE BRUGG DUSSELDORF FRANCFORT S.M. FRIBOURG E.BR. GENÈVE
HAMBOURG COPENHAGUE LAUSANNE MUNICH STUTTGART VIENNE ZURICH
#SDF16
Optimize your SQL Server infrastructureWith Microsoft Azure15h45-16h20
Eric Simbozel - Consultant SQL Server et AzureDaniel Tizon - Principal Consultant
TRIVADIS
#SDF16
#SDF16
Agenda
1. Introduction to Microsoft Azure
2. SQL Server on Azure IaaS
3. SQL Databases on Azure PaaS
4. SQL Server Stretch Databases
5. Backup and Restore to Azure
6. How to start?
#SDF16
Introduction to Microsoft Azure
#SDF16
What is the cloud?
Large number of devices connected through wide communication network
Benefits
Providing services at much lower cost
Flexibility on technology that gives the customer a competitive advantage
Helps IT to be more efficiency on operations
Pay as they go and for what they use
Moving from CAPEX to OPEX model
Faster deployment
Azure - Hyper scale Infrastructure34 Regions Worldwide, 28 ONLINE…huge capacity around the world…growing every year
100+ datacenters
Top 3 networks in the world
2.5x AWS, 7x Google DC Regions
G Series – Largest VM in World, 32 cores, 448GB Ram, SSD…
Operational
Announced/Not Operational
Central US
Iowa
West US
California
East US
Virginia
US Gov
Virginia
North Central US
Illinois
US Gov
Iowa
South Central US
Texas
Brazil South
Sao Paulo State
West Europe
Netherlands
China North *
Beijing
China South *
Shanghai
Japan East
Tokyo, Saitama
Japan West
Osaka
India South
Chennai
East Asia
Hong Kong
SE Asia
Singapore
Australia South East
Victoria
Australia East
New South Wales
* Operated by 21Vianet ** Data Custodian - Deutsche Telekom
India Central
Pune
Canada East
Quebec City
Canada Central
Toronto
India West
Mumbai
Germany North East **
Magdeburg
Germany Central **
FrankfurtNorth Europe
Ireland
East US 2
Virginia
United Kingdom
RegionsUnited Kingdom
Regions
Platform Services
Infrastructure Services
Web Apps
MobileApps
APIManagement
API Apps
Logic Apps
Notification Hubs
Content DeliveryNetwork (CDN)
Media Services
BizTalkServices
HybridConnections
Service Bus
StorageQueues
HybridOperations
Backup
StorSimple
Azure SiteRecovery
Import/Export
SQL Database
DocumentDB
RedisCache
AzureSearch
StorageTables
DataWarehouse
Azure AD Health Monitoring
AD PrivilegedIdentity Management
OperationalAnalytics
Cloud Services
BatchRemoteApp
ServiceFabric
Visual Studio
AppInsights
Azure SDK
VS Online
Domain Services
HDInsight MachineLearning
StreamAnalytics
Data Factory
EventHubs
MobileEngagement
Data Lake
IoT Hub
Data Catalog
Security & Management
Azure ActiveDirectory
Multi-FactorAuthentication
Automation
Portal
Key Vault
Store/Marketplace
VM Image Gallery& VM Depot
Azure ADB2C
Scheduler
#SDF16
Who Manages What?
Infrastructure (IaaS)
– Microsoft manages hardware
and virtualization technology
Platform (PaaS)
– Microsoft manages
everything except your
applications and data
#SDF16
SQL Server Cloud Continium
Low Control
Sh
are
d
Lo
wer
cost
Ded
icate
d
Hig
her
cost
High Control
Hybrid Cloud
Physical
Virtual
PaaSSaaS
IaaS
On premises Off premises
SQL ServerPhysical Machines (raw iron)
SQL Server Private CloudVirtualized Machines+ Appliances
SQL Server in Azure VMVirtualized Machines
Database Services
#SDF16
SQL Server on Azure IaaS
#SDF16
SQL Server in Microsoft Azure VMs
Microsoft Azure
Storage Blob
IMPLICIT CLOUD SERVICE
Availability Set
VM VM
Primary Secondary
IMPLICIT CLOUD
SERVICE
VM
SLA 99.95
2 3
Microsoft Azure
Storage Blob
(somewhere
else)
No SLA
ROLE VIRTUAL MACHINE1
C:\
OS Disk
RAM
Cache
Local Disk
Cache
D:\
Resource Disk
Dynamic VHD
E:\, F:\, etc.
Data Disks
Blobs
#SDF16
Getting Started with Virtual Machines
>
_
#SDF16
Bring your own image
Upload VHD to Azure storage
account, use image to instantiate VM
Use Sysprep to create template
SQL Server 2008 and up
License Mobility with Software
Assurance
You create image, patch, and
manage
SQL Server Virtual machines – how?
Use the gallery image
Currently: SQL 2008 R2 SP2, SQL 2012 SP1,
SQL 2014, SQL 2016 on different Windows
Server OS
Default options
Includes SSIS, RS, AS
License: Multiple SKUs + Pay by Minute
Image refreshed monthly (approximately)
Once deployed you patch and manage
#SDF16
SQL Server Virtual machines - pricing
The cost depends on the infrastructure size and performances you wants
Price of a VM:
Compute pricing (Cores + Memory)
Software pricing (SQL Server licences)
Storage (classic or premium)
Outbound bandwith
Gateway traffic
#SDF16
Storage costs
#SDF16
DEMO 1
SQL Server in IaaS
#SDF16
SQL Databases on Azure PaaS
#SDF16
What is Azure SQL Database (PaaS)?
A service at database level
Predictable perfs, scalable, no downtime, business continuity, data protection
Low level of administration
Automatic configuration, patches, upgrades
Geo-replication features
99.99% SLA
Performances represented by DTU (Database Transaction Unit)
New features first in Azure SQL Database
Some differences or limitations (Azure SQL Database Transact-SQL differences)
#SDF16
SQL Database performance levels
S0 S1 S2 S3 P1 P2 P4 P6 P11 P15
DTUs 5 10 10 50 100 125 250 500 1000 1750 4000
Max storage (GB) 2
Max In-memory OLTP storage (GB) N/A N/A N/A N/A N/A 1 2 4 8 14 32
Max concurrent workers 30
Max concurrent logins 30 60 90 120 200 200 400 800 1600 2400 6400
Max concurrent sessions 300
Point-in-time restore Any point last 7 days
Disaster recovery
BasicStandard Premium
60 200 2400
250 500 1024
Any point last 14 days Any point last 35 days
Active Geo-Replication, up to 4 offline (readable) secondary backups
600 30000 30000
#SDF16
SQL Database price
#SDF16
SQL Server in a VM (IaaS) and Azure SQL Database (PaaS) comparaisons
#SDF16
DEMO 2
SQL Server in PaaS
#SDF16
SQL Server 2016 Stretch Databases
#SDF16
SQL Server 2016 Stretch Databases
#SDF16
SQL Server 2016 Stretch Databases
If you're a decision maker If you're a DBA
I have to keep transactional data for a
long time.
The size of my tables is getting out of
control.
Sometimes I have to query the cold data.My users say that they want access to
cold data, but they only rarely use it.
I have apps, including older apps, that I
don’t want to update.
I have to keep buying and adding more
storage.
I want to find a way to save money on
storage.
I can’t backup or restore such large
tables within the SLA.
If you're a decision maker If you're a DBA
I have to keep transactional data for a
long time.
The size of my tables is getting out of
control.
#SDF16
Backup and restore to Azure
#SDF16
Backup and Restore to Azure
On-Prem SQL Server
Microsoft Azure
RESTORE IN AZURE
VIRTUAL MACHINE
DIRECT URL BACKUP
TO AZURE BLOB STORAGESQL Server
Management Studio
Flexibility &
Control
Low TCO for
Existing Apps
Full SQL Server
Capability
Managed
Infrastructure
SQL Server in VM
#SDF16
DEMO 3
Backup-Restore SQL databases
#SDF16
DEMO 4
Migration of a database to Azure
#SDF16
How to start?
#SDF16
How to start?
Ask Trivadis for an Azure Everywhere workshop
SQL on IaaS
3 or 4 days
SQL Server On-Prem
Direct backup
to Azure Storage
Run, Backup or Archive Database Workloads in Azure• Education, planning, guidance and assistance configuring a SQL
VM in Azure IaaS
• Guidance on best practices for configuring Azure and the VM for optimal performance SQL Workload (data warehouse or transactional)
• Planning and configuration of networking infrastructure required to extend on premises network
• Planning and configuration of workload to secure your environment
• Planning and guidance on using Azure for disaster recovery or SQL backups
Free!
#SDF16
Eric Simbozel
Consultant SQL Server et AzureTél. +41 58 459 54 54
Daniel Tizon
Principal ConsultantTél. +41 58 459 51 95