introducing azure databases

INFORMATION PERSISTENCE IN AZURE (AKA: DATABASES)

Grant Fritchey | Redgate Software

GOALS

• Understand the various database options available on Azure

• Explore how to set up, control, monitor and maintain a

database on Azure

LET’S TALK

scarydba.com

grant@scarydba.com

@gfritchey

Grant Fritchey

DATA ON AZURE• Relational

ꟷ SQL Database (SQL Server in PaaS)

ꟷ SQL Data Warehouse (massively parallel SQL Server)

ꟷ SQL Elastic Pool (management resource for SQL DB)

ꟷ SQL Server on VM

ꟷ 3rd Party

ꟷ More to come

• Non-Relational

ꟷ DocumentDB (document database)

ꟷ MongoDB (PaaS)

ꟷ Redis Cache

ꟷ 3rd Party

ꟷ More to come

• Flat file storage

DATA ON AZURE

• Today

ꟷ Azure SQL Database

• With a little bit of pooling

ꟷ DocumentDB

DATA ON AZURE

• You pay for

ꟷ Storage

ꟷ Transferring data out of Azure

ꟷ Data movement across data centers

ꟷ Processing speed and power

AZURE SQL DATABASE

• Relational data storage

• SQL Server, and all it implies

• Scalable on the fly

• Built-in automated tuning

• Monitoring and alerts

• Built-in backups

• Automation through PowerShell

ELASTIC POOLS

• Multiple Azure SQL Databases

• Pay for databases as a unit

• Manage multiple databases

• Business solution, not a technical one

DOCUMENT DB

• Structureless or semi-structured data storage

• ID/Value using JSON

• Scalable, including across regions

• Multiple consistency levels

LET’S PLAY!

NEW RESOURCE GROUP

• Click “New” on the portal

• Type “resource group” hit enter

• Select “Resource group” published by “Microsoft”

• Click Create

• Define the name, subscription and location, pin to dashboard

AZURE SQL DATABASE

• Click Add

• Type “sql database”

• Select “SQL Database” from Publisher “Microsoft”

• Click “Create”

AZURE SQL DATABASE

• Fill in required info, use Resource Group

• Source should be “Sample (AdventureWorksLT)”

• Click “Configure required settings” for server

ꟷ Click “Create a new server”

ꟷ Fill in required info, remember your login & password

• Do not use SQL elastic pool

• Pricing tier, select Basic

EXPLORING SQL DATABASE

• Start with server

• Update firewall (not needed for today)

• Switch to database

• Click on tools

• Click on Query editor (preview)

• Login (you have that user name & password, right)

EXPLORING SQL DATABASE

• select * from SalesLT.SalesOrderHeader as soh

join SalesLT.SalesOrderDetail as sod

on soh.salesorderid = sod.salesorderid

where sod.unitprice > 1000

EXPLORING SQL DATABASE

• Enable GEO Replication

• Change pricing tier

• Examine auditing

• Enable Diagnostics (may have to create file store)

• Examine performance recommendations

• Examine Query Performance insight

• Go look at Diagnostics (had time to collect data)

DOCUMENTDB

• Click “plus”

• Select “Databases”

• Select “NoSQL (DocumentDB)”

• Fill in required info

• Note option for MongoDB

EXPLORING DOCUMENTDB

• Wizard may(should) start, if so, let’s follow it

• Create “Items” collection for selected language

• Download and open app

• Build & run the app

EXPLORING DOCUMENTDB

• Replicate data globally

• Consistency

• Firewall (!)

• Document Explorer

• Query Explorer (tastes like SQL)

• Restore (DevOps)

BACK TO SQL DATABASE

• Click “Restore”

• Restore creates new database

• Select point in time for restore

• Click OK

A MOMENT OF POWERSHELL

POWERSHELL & AZURE DATABASES

• Get SQLPS installed (no need for full SQL Server)

• Manage SQL Database

• Automate DocumentDB

• SQL Data Warehouse

MONITOR & MAINTAIN SQL DATABASE

• Using Dynamic Management Views

• Performance Monitoring

• Azure SQL Database System Center Management Pack

• Azure SQL Database Maintenance

• Azure Automation

MONITOR AND MAINTAIN DOCUMENTDB

• Monitor DocumentDB

• DocumentDB Core Concepts

• DocumentDB Studio

• DocumentDB Data Explorer (just was announced 4/19)

• Performance Tips for DocumentDB

GOALS

• Understand the various database options available on Azure

• Explore how to set up, control, monitor and maintain a

database on Azure

LET’S TALK

scarydba.com

grant@scarydba.com

@gfritchey

Grant Fritchey