Learn. Connect. Explore.Learn. Connect. Explore.

Auditing and Azure Automation with Azure SQL Database

Sanjay Nagamangalam

Principal Group Program Manager, SQL Server

Session Objectives And TakeawaysWe’ll cover two topics in this session

Part 1: Auditing in Azure SQL Database

Gain insight into database events and streamline compliance-related tasks

Tracking and logging database activity

Part 2: Windows Azure Automation

Use cases and scenarios for Windows Azure Automation

Your “SQL Agent in the cloud” for Azure SQL Database

SQ

L D

ata

baseAuditing - Overview

Why Auditing?

Regulatory

compliance

A massive demand for cloud application to meet regulatory

compliance recommended by regulating/auditing authorities (PCI-

DSS, SOX, HIPAA)

Security incidents DBAs and security officers wish to gain insight into discrepancies and

anomalies that could indicate business concerns or suspected

security violations

Operational Insights Stakeholders are increasingly focusing on understanding database

activity

SQ

L D

ata

baseAuditing - Overview

Where to start?

SQ

L D

ata

baseSetting up Auditing

Server Default Per Database

Combination of the two…

SQ

L D

ata

baseAuditing in Azure SQL Database

Azure SQL Database now has Auditing PREVIEW

Available in Basic, Standard, and Premium

Configurable Auditing policy and Azure storage.

At-a-glance Audit insights in the portal

Interactive, customizable and deep analysis withPower View and Power Pivot reports

Audit logs reside in your Azure Storage account

Gain insight into database events and

streamline compliance-related tasks

SQL DatabaseAuditing

Audit

log

Application

data

Azure Storage

Demo

Auditing in Azure SQL Database

Windows Azure Automation

Automation

Enable service owners to focus on work that adds business value

Reduce error-prone manual activities while lowering costs

Integration

Integrate into existing systems with PowerShell integration modules

Build additional PS modules to enable integrating into other systems

Orchestration

Accelerate time to value with flexible process workflows

Improve service reliability across multiple tools, systems, and department silos

Process automation that simplifies cloud management

Azure Automation Capabilities

Azure

Monitoring

Systems

Change

Control

Systems

Anything

Runbook Authoring in Azure:Create runbooks to automate all aspects of

cloud operations, from deployment,

monitoring, and optimizations

Highly Available Engine:Support requirements for scale and H/A.

Built on PowerShell Workflow. Isolation for

runbook jobs

Integration into other systems:Import PS modules and create additional

modules and runbooks for Azure services or to

connect into 3rd party systems

Automation

Built on PowerShell Workflow

PowerShell

Workflow

• Use Windows PowerShell syntax

• Multi-device management

• Running a single task to manage complex, end-to-

end processes

• Automated failure recovery

• Connection and activity retries

Centralized

store

• Credentials / certificates

• Global variable

• Global connection for runbooks

• Modules

• Runbooks (draft / published versioning)

• Scheduling

Highly

Available

• Runbook servers to process jobs

• Odata Web service to submit / retrieve status

• SQL Server clustering / always on

Historical

Analysis

• Historical view of runbook jobs

• Reporting through Excel PowerPivot for ROI

• View runbook used for all jobs

PowerShell Workflow

Centralized store

Highly Available

Historical Analysis

Microsoft Azure

Automation

Typical Azure Automation Scenarios

Patch Azure IaaS VMs without

downtime, leveraging Traffic

manager.

Enable regeneration of storage

account keys while avoiding

downtime in the application.

SQL Backup on a schedule.

Backup and restore IaaS VMs.

Deploy a VM on an Azure / On-

Premise cloud and enable

monitoring for the VM.

Deploy a new service to Azure

and configure the end points for

CPU and Memory alerts.

Deploy application from Git, run

validation tests, and swap to

production if tests pass.

Monitor SharePoint online for an

approval to update a service and

update the service once

approved.

Alert on a VM then turn on

tracing, collect logs, upload to

Azure Storage and make available

in Visual Studio for

troubleshooting.

Monitor for when a new service

gets created, and configure it for

the right tracing / backup policy.

Notify users of a subscription who

have underutilized VMs and

perform remediation.

Change Control &

Provisioning

Demo

Use Windows Azure Automation with Azure SQL Database

In ReviewAzure SQL Database now has Auditing PREVIEW

Gain insight into database events and streamline compliance-related tasks

Available for Basic, Standard and Premium databases

Windows Azure Automation

Integrates into Azure services and external systems

Implement your tasks using PowerShell workflow

Your “SQL Agent in the cloud” for Azure SQL Database

ReferencesRelated references for you to expand your knowledge on the subject

Get started with Auditing in Azure SQL Database

Get started with Windows Azure Automation

Channel9 Videos (4 videos): Azure SQL Database for Business-Critical Cloud Applications

technet.microsoft.com/en-in

aka.ms/mva

msdn.microsoft.com/

Tell us what you think

Scan the QR code to evaluate

this session.

< QR Code will be given a week before

Tech Ed >

Follow us online

Facebookfacebook.com/MicrosoftDeveloper.India

twitter.com/msdevindia

Twitter

Twitter: @sanagama2

Email:sanagama@microsoft.com