Transcript [Powerpoint] - Putting_Your_Head_in_the_Cloud_

Putting Your Head in the Cloud
Working with SQL Azure
David Postlethwaite
18/06/2016
David Postlethwaite
Say thank you to our Sponsors :
About Me
 David Postlethwaite
 Liverpool Victoria LV=






SQL and Oracle DBA
MCSE 2012 Data Platform
MCITP 2008, 2005
Oracle OCA
25 years IT Experience
6 years as DBA
 [email protected]
 https://www.linkedin.com/in/davidpostlethwaite
 Blog: gethynellis.com
3 | 18/06/2016
David Postlethwaite
Who has Used Azure SQL DB?
 Tried it once
 Evaluating
 Running Production Systems
4 | 18/06/2016
David Postlethwaite
Free E-Book
 Sign up at
 www.gethynellis.com
5 | 18/06/2016
David Postlethwaite
Introduction
 Putting Your Head in the Cloud







Connecting from SSMS
Linked Server from local to Azure
SQL Agent Options
Monitoring and Alerting
Dynamic Data Masking
Always Encrypted
Stretch Database






BACPAC backup Files
Firewall Rules
Azure Server Roles
Backing up and Restoring
Auditing and Security
Scheduling Exports
6 | 18/06/2016
David Postlethwaite
Demo Microsoft Azure
 http://azure.Microsoft.com
7 | 18/06/2016
David Postlethwaite
Windows Azure Portal
8 | 18/06/2016
David Postlethwaite
Windows Azure Classic Portal
9 | 18/06/2016
David Postlethwaite
Using SSMS





Limited GUI with SSMS 2012 or SSMS 2014 RTM
SSMS 2014 SP1 and 2016 gives better support
Need to write code
Need the azure syntax
Can’t use “USE”
10 | 18/06/2016
David Postlethwaite
Linked Servers
 Need to create ODBC DSN
 System Server Native Client 11
 Linked Server
 Microsoft OLE DB Provider for ODBC Drivers
 Azure stored procedure you must enable RPC
11 | 18/06/2016
David Postlethwaite
SQL Agent
12 | 18/06/2016
David Postlethwaite
Azure Mobile Services Scheduler
13 | 18/06/2016
David Postlethwaite
App Service Web Jobs
14 | 18/06/2016
David Postlethwaite
Azure Automation
15 | 18/06/2016
David Postlethwaite
Azure Scheduler
16 | 18/06/2016
David Postlethwaite
SQL Database Monitor
17 | 18/06/2016
David Postlethwaite
Dynamic Data Masking
CREATE TABLE [Sales].[CustomerPII](
[CustomerID] [int] NOT NULL,
[FirstName] [dbo].[Name] NOT NULL,
[LastName] [dbo].[Name] MASKED WITH (FUNCTION='partial(2,"XXXX",2)') NOT NULL,
[EmailAddress] [nvarchar](50) MASKED WITH (FUNCTION = 'email()') NULL,
[PhoneNumber] [nvarchar](25) MASKED WITH (FUNCTION = 'default()') NULL,
[TerritoryID] [int] NULL
) ON [PRIMARY]
18 | 20/05/2016
David Postlethwaite
Dynamic Data Masking
19 | 18/06/2016
David Postlethwaite
Always Encrypted
 Encrypt columns using an encryption key


CREATE TABLE [Sales].[CustomerPII](
[CustomerID] [int] NOT NULL,
[SSN] [nvarchar](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY =
[CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,

[CreditCardNumber] [nvarchar](25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY =
[CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,

) ON [PRIMARY]





Unencrypted values can only be seen from computer with the certificate
installed
Need extra parameter in connection string column encryption setting=enabled
Msg 0, Level 11, State 0, Line 2
Certificate with thumbprint '8C5AE6DCC176752931B33BFE03B7E4EA3A73572C' not found in
certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path
in the column master key definition in the database is correct, and the certificate has
been imported correctly into the certificate location/store.
Parameter name: masterKeyPath
 Currently Only Supported with ADO.NET 4.6
20 | 20/05/2016
Stretch Tables
 Take advantage of “cheap” Azure storage
 Split a table in two
Place “old” data in a database in Azure
Leave current data “on premises”
 Query knows if data is in current table or archive table and returns
merged results seamlessly
 SQL will move data to Azure when it becomes “old”
 Lots of limitations
 Not as cheap as it looks (minimum of €784/month)
21 | 20/05/2016
David Postlethwaite
Create Storage Account
22 | 18/06/2016
David Postlethwaite
DACPAC and BACPAC




SSMS 2012+
BACPAC Export Data Tier Application – schema + data
DACPAC Extract Data Tier Application - schema
Azure currently only supports BACPAC
 Validates the schema before creating the file
 Save to local disk
 Copy BACPAC to Azure storage for Import from the Management
Portal
 Or
 Export straight to new database on Azure Server
23 | 18/06/2016
David Postlethwaite
Creating DACPAC and BACPAC
24 | 18/06/2016
David Postlethwaite
Connecting to SQL Azure – Firewall Rules
25 | 18/06/2016
David Postlethwaite
SQL Azure Security
 IP Security




Commands to manage firewall rules
Sp_set_database_firewall_rule
Select * from sys.database_rules
Select * from sys.firewall_rules;
 Login Security
 SQL logins only. No AD authentication – in preview
 Internal Security
 Must explicitly give access to other Azure apps
26 | 18/06/2016
David Postlethwaite
SQL Azure Security
Internet
Server Level Firewall Rules
Yes
Client IP address in
Server Level Firewall Rules
No
Yes
Access from Azure
Database Level Firewall Rules
Client IP address in
Database Level Firewall Rules
Access to any database
Yes
Login Name and Password
Access to specific database
Login Name and Password
27 | 18/06/2016
David Postlethwaite
No
No
Azure Data Sync (Preview)
28 | 18/06/2016
David Postlethwaite
Server Roles
 Not the Same as local SQL
 server-level principal account
 Loginmanager
 dbmanager
29 | 18/06/2016
David Postlethwaite
Grammar
Prem
Premise
Premises
30 | 18/06/2016
– short for premises
– proposition or assumption
– a building or property
David Postlethwaite
Any Questions
 Conclusion
 Hopefully you now ready to start creating your Azure Databases
 Q&A
 [email protected]
 Free Ebook: gethynellis.com
 Video: http://tinyurl.com/postledm
31 | 18/06/2016
David Postlethwaite
Your feedback is important!
Please let us know what you thought of this session
HTTP://SQLSATURDAY.COM/516/SESSIONS/SESSIONEVALUATION.ASPX