Stretch db sql server 2016 (sn0028)

28th
SQL Night
WEBINAR
Athens May14,2016

PresenterInfo
1982 I started working with computers
1988 I started my professional career in computers
industry.
1996 I started working with SQL Server 6.0
1998 I earned my first certification at Microsoft as
Microsoft Certified Solution Developer (3rd in
Greece)
I started my career as Microsoft Certified Trainer
(MCT) with more than 25.000 hours of training until
now!
2010 I became for first time Microsoft MVP on SQL
Server
I created the SQL School Greece www.sqlschool.gr
2012 I became MCT Regional Lead by Microsoft Learning
Program.
2013 I was certified as MCSE : Data Platform & Business
Intelligence
Antonios Chatzipavlis
Database Architect,
SQL Server Evangelist
MCT, MCSE, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS,
MCAD, MCP, OCA, ITIL-F

SQLschool.gr
Team
Antonios Chatzipavlis
SQL Server Evangelist • Trainer
Vassilis Ioannidis
SQL Server Expert • Trainer
Fivi Panopoulou
System Engineer • Speaker
Sotiris Karras
System Engineer • Speaker

Followus
insocialmedia
@antoniosch / @sqlschool
fb/sqlschoolgr
yt/c/SqlschoolGr
SQL School Greece group

Presentation
Content
 Introduction of Stretch Database?
 Limitations and blocking issues
 Stretch Database Pricing
 Backup and Restore of Stretch DB
 FQA

Introduction of Stretch Database
 What is the Stretch database?
 What are the benefits of Stretch database?
 What does Stretch database do?
 Is Stretch database for you?
 What kind of databases and tables are candidates for
Stretch database?
 Stretch database Architecture
 Security and Data Access

On-premises
SQL Server
WhatistheStretch
Database?
Hot/Online
Business data
Cold/Closed
Business data
Trickle data move
Transparent remote
query processing

 Provides cost-effective availability for cold data
 Doesn’t require changes to queries or applications
 Streamlines on-premises data maintenance
 Keeps your data secure even during migration
What are the benefitsof StretchDatabase?

 Can migrate the entire table
 If you store historical data in a separate table
 Can specify a filter predicate to select
the rows to migrate
 If your table contains both historical and current data
 Ensures that no data is lost
 If a failure occurs during migration.
 It also has retry logic to handle connection issues that may occur
during migration.
 A dynamic management view provides the status of migration.
What does Stretchdatabase do?
 You can pause data migration
 To troubleshoot problems on the local server
 To maximize the available network bandwidth.
 You don't have to change existing queries and client apps.
 You continue to have seamless access to both local and remote data, even during data migration.
 There is a small amount of latency for remote queries, but you only encounter this latency when you query the historical
data.

IsStretchDatabaseforyou?
As Decision Maker As 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
historical data.
My users say that they want access
to historical 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.

 Transactional databases with large amounts of historical
data
 Temporal table feature of SQL Server
 Use Stretch Database to migrate all or part of the associated history table to
cost-effective storage in Azure.
 Use Stretch Database Advisor to identify databases and
tables for Stretch Database.
What kind of databasesand tables are candidatesfor StretchDatabase?

Security and Data Access
 Only system processes can access the linked server
definition behind Stretch Database.
 User logins can't issue queries through the linked server definition to the remote
endpoint.
 Stretch Database does not change the permissions model
of an existing database.
 User logins can query the data in a table with Stretch Database enabled through the
local database.
 The local database performs permission checks for any
actions initiated by the user in the same way as it does for
any other objects.
 If you're authorized to access the table with Stretch Database enabled, you have
access to all its contents for which you're authorized regardless of where the data
physically resides

Limitations and blocking issues
 Blocking issues
 Limitations

Blockingissues Table properties • More than 1,023 columns
• More than 998 indexes
• Tables that contain FILESTREAM data
• FileTables
• Replicated tables
• Tables that are actively using Change Tracking or Change Data Capture
• Memory-optimized tables
Data types and
column
properties
• text, ntext and image
• timestamp
• sql_variant
• XML
• geometry
• geography
• hierarchyid
• CLR user-defined types (UDTs)
Column types • COLUMN_SET
• Computed columns
Constraints • Check constraints
• Default constraints
• Foreign key constraints that reference the table
• In a parent-child relationship (for example, Order and Order_Detail), you can stretch the child table
(Order_Detail) but not the parent table (Order).
Indexes • Full text indexes
• XML indexes
• Spatial indexes
• Indexed views that reference the table

 UNIQUE constraints and PRIMARY KEY constraints are not
enforced in the Azure table that contains the migrated data
from a Stretch-enabled table.
 You can't UPDATE or DELETE rows in the Azure table that
contains the migrated data from a Stretch-enabled table
 You can't INSERT rows into a Stretch-enabled table on a
linked server.
 You can't create an index for a view that includes Stretch-
enabled tables.
 Filters on SQL Server indexes are not propagated to the
remote table.
Limitations

StretchDatabasePricing
DSU PRICE
100 €1.0541/hr (~€784/mo)
200 €2.1083/hr (~€1,569/mo)
300 €3.1624/hr (~€2,353/mo)
400 €4.2165/hr (~€3,137/mo)
500 €5.2706/hr (~€3,921/mo)
600 €6.3248/hr (~€4,706/mo)
1000 €10.54/hr (~€7,843/mo)
1200 €12.65/hr (~€9,411/mo)
1500 €15.81/hr (~€11,764/mo)
2000 €21.08/hr (~€15,685/mo)
What is a DSU ?
(Database Stretch Unit)
As part of providing a more predictable
performance experience for customers, SQL
Stretch Database is introducing the Database
Stretch Unit (DSU).
A DSU represents the power of the query and
data migration performance and is quantified by
workload objectives: how fast rows are migrated
to Azure, written, read and computed against.
• Storage rates are based on standard RA-GRS Page Blob rates.
No preview discounts are applied to the storage charges.
See storage pricing page for details.
• Storage transactions are not billed; customers only pay for
data stored, not storage transactions.
• Outbound data transfers are charged at regular data
transfer rates.

Backup and Restore of Stretch DB
 Backup Stretch database
 Restore Stretch database

 You can continue to use the methods that you currently
use
 A backup of a Stretch-enabled database is a shallow
backup that does not include the data migrated to the
remote server.
 The backups for Azure SQL Databases for Basic, Standard,
and Premium service tiers are taken every hour.
 The backup retention period varies depending on the service tier level.
 Basic -> 7 days
 Standard -> 14 days
 Premium -> 35 days.
 You can restore Azure SQL Databases by using the Microsoft Azure web portal.
Backup StretchDatabase

 Restore the database from a backup.
 Run the stored procedure sys.sp_rda_reauthorize_db to
reconnect the local Stretch-enabled database to Azure.
 Provide the existing database scoped credential as a sysname or a varchar(128)
value. Don't use varchar(max)
 You can look up the credential name in the view sys.database_scoped_credentials
 Specify whether to make a copy of the remote data and connect to the copy.
Restore StretchDatabase
DECLARE @credentialName nvarchar(128);
SET @credentialName = N'<existing_database_scoped_credential_name>';
EXEC sp_rda_reauthorize_db @credential = @credentialName, @with_copy = 0;

Stretch db sql server 2016 (sn0028)

Open the catalog views sys.databases and sys.tables to see
info about Stretch-enabled SQL Server databases and
tables.
Get infoabout local databases and tables enabledfor StretchDatabase

Open the catalog views sys.remote_data_archive_databases
and sys.remote_data_archive_tables to see info about the
remote databases and tables in which migrated data is
stored.
Get infoabout remotedatabases and tables used by StretchDatabase

Open the catalog view sys.remote_data_archive_tables and
check the value of the filter_predicate column to identify
the function that Stretch Database is using to select rows to
migrate.
If the value is null, the entire table is eligible to be migrated.
Checkthe filterpredicateappliedto a table

Stretch Monitor for a database in SSMS
Or
sys.dm_db_rda_migration_status DMV to see how many
batches and rows of data have been migrated
Checkthe status of data migration

Queries that include my Stretch-enabled table are slow
If query performance degrades significantly, review the
following possible problems.
Is your Azure server in a different geographical region than your SQL
Server? Configure your Azure server to be in the same geographical region as
your SQL Server to reduce network latency.
Your network conditions may have degraded. Contact your network
administrator for info about recent issues or outages.
About queryperformance

Stretch db sql server 2016 (sn0028)

Recommended

More Related Content

What's hot (20)

Viewers also liked (13)

Similar to Stretch db sql server 2016 (sn0028) (20)

More from Antonios Chatzipavlis (15)

Recently uploaded (20)

Stretch db sql server 2016 (sn0028)