Transcript Slide

Why you should be thinking
hybrid with SQL Server 2016
Scott Klein
M324
www.scottLklein.com
[email protected]
@sqlscott
XML ● KPIs
SQL Server
2000
Management Studio ● Mirroring
SQL Server
2005
Compression ● Policy-Based Mgmt ● Programmability
SQL Server
2008
PowerPivot ● SharePoint Integration ● Master Data Services
SQL Server
2008 R2
AlwaysOn ● ColumnStore Index ● Data Quality Services ● Power View ● Cloud Connectivity
SQL Server
2012
In-Memory Across Workloads ● Performance & Scale ● Hybrid Cloud Optimized ● HDInsight ● Cloud BI
SQL Server
2014
Operational Analytics ● StretchDB ● PolyBase ● R Integration ● Temporal ● Query Data Store ● RLS ● Always Encrypted
SQL Server
2016








Stretch SQL Server into Azure
Stretch warm and cold tables to Azure with remote query processing
Microsoft Azure
Jim Gray
Order history
Name
ox7ff654ae6d
3/18/2005
Stretch to cloud
John Smith
Date
Customer data
cm61ba906fd 2/28/2005
Product data
ox7ff654ae6d 3/18/2005
Order History
i2y36cg776rg 4/10/2005
Bill Brown
nx290pldo90l
Jane Doe
Jim Gray
SSN

4/27/2005
Query
App
PolyBase
Provides a scalable, T-SQL compatible query processing
framework for combining data from both worlds
Query
Results












Technologies Supported
Technology
SQL Server
Version
Zero Data Loss
Scope
# Secondaries
Automatic
Failover
Readable
Secondaries
Availability
Groups
SQL Server 2012
SQL Server 2014
SQL Server 2016
Yes*
(Sync Mode)
DB(s)
4
Yes
Yes
(w/ additional
cluster
member)**
Database
Mirroring
SQL Server 2008
R2
SQL Server 2012
Yes*
(High Safety
Mode)
DB
1
Yes
Limited
(w/ Witness)** (database
snapshots)
Log
Shipping
SQL Server 2008
R2
SQL Server 2012
No
DB
N
No
Limited
(standby
state)
* Most customers use Async Mode (High Performance) to avoid impacting primary performance
**Async Mode only supports Force Failover to make DR (and potential data loss) a conscious decision


 https://www.windowsazure.com/en-us/manage/services/networking/cross-premises-connectivity/




http://msdn.microsoft.com/en-us/library/jj870959.aspx


http://msdn.microsoft.com/en-us/library/jj870964.aspx


http://msdn.microsoft.com/en-us/library/jj889442.aspx
redmond.corp.microsoft.com
Availability Group
uswest.internal.cloudapp.net
Virtual
Network
Windows Cluster

















Windows Azure
VM
On-premise box
Windows Azure Storage
• Backup and restore times are
proportional to bandwidth
• Backup times fairly constant up to
1000ms latency
• Restore times proportional to
latency
Example Backup Size is: 138215424 (131.8MB)







CREATE CREDENTIAL mystoragecred
WITH IDENTITY = ‘mystorage',
SECRET = ‘<your storage access key>
BACKUP DATABASE mydb TO URL ='https://mystorage.blob.core.windows.net/backupcontainer/mydb-20130411.bak'
WITH CREDENTIAL = ‘mystoragecred',
using Microsoft.SqlServer.Management.Smo;
FORMAT, COMPRESSION,
STATS = 5,
MEDIANAME…= ‘mydb backup 20130411', MEDIADESCRIPTION = 'Backup of mydb'
…
Backup mybackup = new Backup();
mybackup.CredentialName = strCredential;
mybackup.Database = strDatabase;
$url = "https://[storage].blob.core.windows.net/backups/db.bak"
mybackup.CompressionOption = BackupCompressionOptions.On;
$credential = "mycredential"
mybackup.Devices.AddDevice(desturl,
DeviceType.Url);
mybackup.SqlBackup(myLocalServer);
$server = "SQLSERVER:\SQL\[computer]\DEFAULT"
CD $server
Backup-SqlDatabase -Database AdventureWorks2012
-backupFile $url
-SqlCredential $credential -CompressionOption On






Native support
for SQL Server
database files
stored as
Windows Azure
blobs.
DB1
DB2
DB3
Windows Azure VM
Windows Azure
Storage
DB3
DB4
DB5
DB6
On-premise box
DB6
DB1
Node1
Windows Azure
Storage
DB1
DB1
Migrate Database to another
machine
Basic Disaster Recovery
Separation of Compute and
Storage
For Azure VM, expand drive and
size of Xdrive
Node2
Better together: IaaS VM per
minute billing





CREATE CREDENTIAL [https://mystorage.blog.core.windows.net/data]
WITH IDENTITY = ‘Shared Access Signature',
SECRET = ‘<your SAS key>
CREATE DATABASE mydb ON
( NAME = mydb_dat,
FILENAME = 'https://mystorage.blob.core.windows.net/data/mydb.mdf' )
LOG ON
( NAME = foo_log,
FILENAME = 'https://mystorage.blob.core.windows.net/data/mydblog.ldf')











1
Azure Consistent Service
Delivery Overview
NZ1 Wed 10:00am
2
Server Virtualisation Overview
3
Networking Overview
4
Storage Overview
NZ2 Wed 1:30pm
SKYCITY Theatre Thu 11:00am
SKYCITY Theatre Thu 3:30pm
5
Security and Assurance Overview
6
What’s New in System Centre
for Management
NZ4 Fri 9:00am
NZ1 Fri 11:00am
Find me later at…
 Hub Happy Hour Wed 5:30-6:30pm
 Hub Happy Hour Thu 5:30-6:30pm
 Closing drinks Fri 3:00-4:30pm
Free Online Learning
http://aka.ms/mva
Subscribe to our fortnightly newsletter
http://aka.ms/technetnz
http://aka.ms/msdnnz
Sessions on Demand
http://aka.ms/ch9nz
© 2015 Microsoft Corporation. All rights reserved.
Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.