super hybrid2016 tdc
TRANSCRIPT
Globalcode – Open4education
Murilo MirandaLead Database Consultant @ Pythian
@murilocmiranda
http://www.sqlshack.com/author/murilo-miranda/
http://pt.linkedin.com/in/murilomiranda/
http://www.pythian.com/blog/author/murilo/
Globalcode – Open4education
Agenda
Introduction
Azure Blob Storage
Backups to Azure
Managed backups
Low-cost DR
Cloud Witness
Hybrid partitioning
Other Solutions
Strech databases
AG Replica
Globalcode – Open4education
The Cloud
The first “modern” HD (1956)IBM 350
50 24-inch
1200 rpm
Capacity: 4.4 Mb
Globalcode – Open4education
The Cloud
ST-506 – First 5”25 drive (1980)
Capacity: 5 MB
Percursor for modern PC HD
Globalcode – Open4education
The Cloud
From 1990 to 2000’s
Compact Disk
Capacity: 700 Mb
Seagate Barracuda
Capacity: 2.5 Gb
First 7200 RPM HD
Flash Drive
Capacity: 8 Mb
Nowadays hundreds of GB....
Globalcode – Open4education
Backups to Azure
We can take advantage of Blob Storage to send
backups to the cloud!
Tapes? Why?
Worry about disk space? Never more!
Damaged storage? Azure is Geo-Redundant.
And more...
This is an offsite backup solution.
And you pay as you use!
Globalcode – Open4education
Backups to Azure
In summary...
Mnimizes overall costs
One more option for backup DR.
Globalcode – Open4education
Backups to Azure
Simple steps to follow:
1. Create a container under your Azure Storage account.
2. Download the Publishing Profile.https://manage.windowsazure.com/publishsettings/
3. Create the Credentials.
4. You are ready!
Globalcode – Open4education
Managed Backups
Managed Backups – what’s that?
Still about backups? Yes!
The difference: it automates and manages SQL
Server backups, based on a retention period.
Globalcode – Open4education
Managed Backups
FULL Backup Schedule
When MB is
enabled.
When log grows
more than 1 GB.
If the last FULL is 1
week older.
When log chain
breaks
Log Backup schedule
When a FULL
backup is performed
When there are no
log backups.
If t-log grows >5 MB
When last backup is
2h older.
Globalcode – Open4education
Blob Storage Tools
Azure Storage Explorerhttp://azurestorageexplorer.codeplex.com/
Globalcode – Open4education
Blob Storage Tools
Azure Web Storage Explorerhttp://azurestorage.azurewebsites.net/
Globalcode – Open4education
Database Files in Azure
We can store DB files in Azure!
SQL Server 2012 SP1 – CU2+
Supports data and log files.
AzureOn-Premises
Globalcode – Open4education
Database Files in Azure
Good for small/non-critical databases
Performance is improving but can be a barrier.
Good option to create a low-cost DR solution!
Do not forget to save a
copy of db level objects in
Azure Storage.
Globalcode – Open4education
Database Files in Azure
Steps:
1. Create a Credential in the SQL Server instance.
2. Create a credential.
• The credential name = Container Path.
• This is not a simple process.
• I used a method with PowerShell, explained at this blog:
• http://sqlcommunity.com/SQL-TEAMS/SQLCAT
• Create/attach the dabase pointing to that container.
Globalcode – Open4education
Cloud Witness
Las vegas Chicago
VoteVote
VoteVote
Vote
We need 3 votes for the cluster to keep running
Globalcode – Open4education
Cloud Witness
Las vegas Chicago
VoteVote
VoteVote
Vote
We need 3 votes for the cluster to keep running
Austin
Globalcode – Open4education
Cloud Witness
Introducing Cloud Witness
New kind of failover cluster witness
Windows vNext (2016?)
Leverages Microsoft Azure
It uses Microsoft Azure Blob Storage
Advantages:
No need for 3rd separate datacenter.
Same Storage Account can be used for multiple clusters.
One blob file per cluster.
Very low-cost (few read/write operations)
Globalcode – Open4education
Cloud Witness
Steps to configure the Cloud Witness
Create Azure Storage Account.
A container named “msft-cloud-witness” will be created.
Globalcode – Open4education
Hybrid Partitioning
Azure storage is useful for more!
Build a simple data archival model.
By partitioning a table and sending older data to Azure.
PRIMARY
FG1
FGAzure
FG2
FGn
Filegroups Files
Local MDF file
Local NDF fileLocal NDF fileLocal NDF fileNDF file in Azure
On-Prem
Cloud
Globalcode – Open4education
Hybrid Partitioning
Data archival model
Partitoned Table
Partition with old data uses the “Azure”
Filegroup
Globalcode – Open4education
Stretch Database
Stretch Databases - Data archival solution.
Based on Azure SQL Database
SQL Server 2016 feature.
Archives historical data – send to the cloud
Two modes:
Archive Table – Moves all the table.
Archive Row – Moves rowa based on rules.
Advantages:
Performance improvements.
Reduced database/backup size.
Backup run faster.
Globalcode – Open4education
Stretch Database
Stretch Databases - Data archival solution.
No changes to existing queries and client apps.
Reduced cost and complexity.
Ho
t D
ata
Co
ld D
ata
Globalcode – Open4education
Stretch Database
Requisites
Azure account.
Stretch database uses an underline Azure SQL Database.
Enable “Strech” on local instance.EXEC sp_configure 'remote data archive';GOEXEC sp_configure 'remote data archive' , '1';GORECONFIGURE;GO
After that you are good to run the wizard...
Globalcode – Open4education
Strech Database
Types
filestream
timestamp
sql_variant
XML
geometry
geography
hierarchyid
CLR user-defined types
(UDTs)
Features
Column Set
Computed Columns
Check constraints
Foreign key constraints that
reference the table
Default constraints
XML indexes
Full text indexes
Spatial indexes
Clustered columnstore indexes
Indexed views that reference the
table
Limiations
Globalcode – Open4education
Availability Groups Replica
Availability Group supports replicas in Azure
Since SQL Server 2012 SP1 CU2+
Works as a DR/LB strategy.
Offload workload (reports, backups, ...).
Low-cost DR strategy.
Globalcode – Open4education
Availability Groups Replica
Availability Group supports replicas in Azure
A “wizard” that helps on this task – SQL Server 2014+
Globalcode – Open4education
Availability Groups Replica
Availability Group supports replicas in Azure
Or you can join manually...
1. Deploy the Azure VM
2. Join into the domain
3. Join into the cluster
4. Enbale HADR feature.
5. Joint to AG.
6. Start syncing the databases.