Transcript SQL Server Stretch database (Overview and demo session)

How would you rate your current organization’s enterprise storage or SAN experience?
1
5
10 
Does it go lower than one?
Is your data center footprint increasing or decreasing?
I’d like to “talk” with the person who came up with that “do more with less” thing
When you think about OLTP databases, how many rows do they have in their largest tables?
45 billion rows in a single table(~99% cold)
1.3 trillion rows in a partitioned table (4TB)
What percentage of that is cold data?
Most of it!
For those of you who have databases with very large tables
What is your maintenance (e.g. re-index, performance tuning) experience?
Is there a impact to their availability and your performance SLAs?
Re-index? That’s not funny man
Doing a full DB restore can mean we can be down one day, sometimes as much as 70-80% of the data is cold
Up to 85% of enterprise data is
considered cold…sometimes more.
Document management
Retail
Travel
Social media
…everything!
Cold data growth that’s out of control
What
are
our
customers
Budgets that are shrinking
Difficulty
remaining compliant
experiencing?
Business SLAs that are at risk
Control
their cold
data growth
What over
do our
customers
The ability to query both cold and hot data
need?
instantly
Reduced storage and maintenance costs
To remain in compliance
What is Stretch Database?
A Microsoft solution for securely migrating cold data to Azure with remote query
processing abilities
Benefits
Order History
SQL
Azure
Order History
Stretch
to cloud
Customers
Products
Order History
SQL
Server
2016
App
How does Stretch work?
Hot data
Internet boundary
SOURCE
DATABASE
REMOTE
DATABASE
Cold Data
Azure
Cold data
Trickle Migration
SQL Server 2016
Creates a secure connection between the
Source SQL Server and Azure
Provisions remote instance and begins
migration
Apps and Queries continue to run for both
the local database and remote endpoint
Security controls and maintenance remain
local
Microsoft Azure
Work without disruption
Order History
Business applications continue
working without disruption
DBA scripts and tools work as
before; all controls still held in
local SQL Server
Orders
Orders History
Developers continue building or
enhancing applications with
existing tools and methods
Support for AAD authentication
Order History
Orders
Orders History
Data in motion always via secure
channels (TLS1.1 / 1.2)
Always Encrypted supported if enabled
by user
 Encryption key remains on the source SQL server
Support for row level security
Supports SQL Server and SQL Azure
audit
DBAs only handle backup/restore of local
hot data
Order History
Auto reconcile
Orders
Upon completion of local restore, Stretch
database reconciles with the remote data
Stretch ensures remote and source data
is transactionally-consistent
Time of restore for remote not
dependent on size of data
Orders History
Trickle migration
It just works!
App
SQL
Primary
SQL
Secondary
SQL
Remote database
Seeding of secondary's is much
quicker
Data savings are multiplied across
replicas
Customers
with
TBs
of
historical
or
cold
data
Who are our target
customers?
Customers who want to take that first step
into the cloud – on their own terms!
Example: cold data from Orders table moved to Orders History using
SP, SSIS, etc.…
Orders
Order History
Example: order data in the Orders table becomes cold when the order
is closed…
Orders
Order History
Azure
Stretch the entire history table to
Azure…
Order History
Azure
Stretch just the cold rows to
Azure…
Stretch the entire history
table to Azure…
Stretch just the cold rows
to Azure…
Enabling Stretch using the SSMS Wizard
functionality
Enabling Stretch and only migrating
selected rows
Disabling Stretch Db
Monitoring migration progress
Licensing and capacity planning is really complicated
I have encrypted data on my local server
We do not trust public clouds
My hot and cold data is in the same table
I can’t change my applications
We are a federal agency (government); can’t use public
cloud
TDE support on the remote Azure DB if the Local DB has TDE enabled
Azure Stretch database edition, with support for up to 1 Petabyte
Point-in-time restore and geo-restore support
Predicate based Stretch via TSQL and SSMS
Integrated table validation during Stretch Wizard Setup
Support for Temporal tables
AlwaysOn Support
AAD and Federated account support
Contact [email protected] to find out
more about our Stretch database special incentive
program.