Azure SQL Data Warehouse

Azure SQL Data Warehouse
Azure SQL Data Warehouse is a warehousing service in the cloud that’s available on Azure as a Platform as a Service (PaaS) offer. Azure SQL Data Warehouse is also a Massively Parallel Processing (MPP) system. It’s actually a distributed system where different computers, called nodes, work together to supply the data for your queries. The splitting of Azure SQL Data Warehouse into multiple machines makes it dependent on distributed storage as well as distributed compute.

Azure SQL Data Warehouse works well for with analytical workloads typical in Big Data scenarios. It can store large volumes of data, perform query analysis and ad-hoc reporting across large datasets. It can also consolidate disparate data into a single location and shape, model, transform, and aggregate data, using simple SQL constructs.

Azure SQL Database is a Massively Parallel Processing (MPP) system. A Massively Parallel Processing (MPP) system is composed of multiple machines, not just one large machine. Usually these machines will all have a slice of the data from the database, and when a query comes in, the query execution is distributed across each machine to generate the query results you require. When you need to perform query analysis against large datasets, an MPP system scales very well as the amount of data increases.

Data Warehousing Unit (DWU)
In Azure SQL Data Warehouse, there is a concept called a Data Warehousing Unit (DWU). This is a measure of the underlying compute power of the database.

How the System Orchestrates Queries
Control Node and Compute Nodes
The Control Node is a special category of node that receives all the connections and orchestrates the queries. It sends the different queries to different nodes and receives back the results. Therefore it is always in communication with the different Compute Nodes that are part of the MPP system.

The Compute Nodes, on the other hand, read data off the distribution, perform all the processing, and scale with the DWUs. This is an important distinction to grasp. The Control Node will always be the same regardless of how many DWUs you add. However you will get more Compute Nodes as your DWUs increase.Control Node and Compute Nodes

Methods for Loading Data

Databases in Azure – Auditing

Azure SQL Database Auditing tracks database events and writes them to an audit log in your Azure Storage account. Auditing can help you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.

SQL Database Auditing allows you to:
Retain an audit trail of selected events
Report on database activity
Analyze reports

Threat Detection
Threat Detection detects anomalous database activities indicating potential security threats to the database.
You can use auditing to explore the suspicious events and determine if they result from an attempt to access, breach or exploit data in the database.

Alert Rules
You can receive an alert based on monitoring metrics or events. When the value of an alert rule crosses an assigned threshold, the alert rule becomes active and can send a notification. For an alert rule on events, a rule can send a notification on every event, or, only when a certain number of events happen.

SQL Database alert values and thresholds

Dynamic Management Views
Microsoft Azure SQL Database enables a subset of dynamic management views to diagnose performance problems.

Use dynamic management views to detect a few common query performance problems:
Monitor query performance
Monitor connections
Monitor query plans

Databases in Azure – Active Geo-Replication

Active Geo-Replication

Active Geo-Replication lets you to configure up to four readable secondary databases in the same or different data center locations (regions). Secondary databases are available for querying and for failover in the case of a data center outage or the inability to connect to the primary database.

Active geo-replication must between databases within the same subscription.

Configure active geo-replication for Azure SQL Database
Failover groups and Active Geo-Replication

Databases in Azure – Database Export for Archive

Database Export for Archive
When you need to create an archive of an Azure SQL database, you can export the database schema and data to a BACPAC file. A BACPAC file is simply a ZIP file with an extension of .bacpac.

Considerations when exporting your database:
You must ensure that no write activity is occurring during the export.
The maximum size of a BACPAC file archived to Azure Blob storage is 200 GB.
Archiving to Azure premium storage by using a BACPAC file is not supported.
If the export operation exceeds 20 hours, it may be cancelled.

Exporting a bacpac file is not the same as backing up the database. Read Built-in Backups vs Import/Export

Self-Service Restore
SQL Database automatically creates database backups.
Backup Types: SQL Server technology is used to create full, differential, and transaction log backups.
Backup Storage: SQL Database provides up to 200% of your maximum provisioned database storage as backup storage at no additional cost.
Backup Schedule: The first full backup is scheduled immediately after a database is created. After the first full backup, all further backups are scheduled automatically and managed silently in the background. The exact timing of all database backups is determined by the SQL Database service as it balances the overall system workload.
Backup Retention: Each SQL Database backup has a retention period that is based on the service-tier of the database.

Databases in Azure – Managing Databases

Tools for Azure SQL Database:
SQL Server Management Studio
SQLCMD Utility
Visual Studio – SQL Server Object Explorer
AzureRM.SQL PowerShell Module

Azure SQL Database Security

Security Center for SQL Server Database Engine and Azure SQL Database
Server-level Security Features
Server Firewall Rules
Master Database Roles

Database-level Security Features
Database Firewall Rules
Database Roles

Managing Database Access
Managing Role Membership

Databases in Azure – Deploying Databases

Choose a cloud SQL Server option: Azure SQL (PaaS) Database or SQL Server on Azure VMs (IaaS)

PaaS: Azure SQL Database
SQL Database, based on Microsoft SQL Server technologies.
MySQL Database, based on the ClearDB MySQL Database cloud service
Azure SQL Database architecture starts with an Azure subscription. Within the subscription, there is a resource group and within the resource group there are server instances and databases.

Azure SQL Database Architecture
Azure SQL Database Architecture

Planning Azure SQL Databases
Planning Azure SQL Databases

IaaS: SQL Server on Azure Virtual Machines

Azure SQL Database vs. SQL Server

DocumentDB Databases
Azure DocumentDB is a NoSQL document database service designed from the ground up to natively support JSON and JavaScript directly inside the database engine. It’s an ideal and highly scalable solution for cloud apps that have predictable throughput, low latency, and flexible querying. With this system, you can implement rich queries over a schema-free JSON-based data model.

Microsoft Azure Virtual Network Services : Reading #4 Intersite Connectivity

Intersite Connectivity Options

Intersite Connectivity

Considerations for Intersite Connections
Azure supports a maximum of 30 VPN tunnels per VPN gateway. Each point-to-site VPN, site-to-site VPN, or VNet-to-VNet VPN counts as one of those VPN tunnels. A single VPN gateway can support up to 128 connections from client computers.
Address spaces must not overlap. Carefully plan the address spaces that you want to use in virtual networks and any connected on-premises networks to avoid IP conflict.
VNet-to-VNet VPNs can connect virtual networks in the same or different Azure subscriptions. Similarly, they can connect virtual networks in the same or different Azure regions.
Redundant tunnels are not supported.
Cloud services cannot span virtual networks, even when those virtual networks are connected with a VPN.
All VPN tunnels to a virtual network share the available bandwidth on the Azure VPN gateway. This includes point-to-site VPNs.
VPN devices must meet certain requirements. About VPN devices for Site-to-Site VPN Gateway connections and a list of compatible third-party VPN devices.

Four type of Intersite connectivity
Point-to-Site VPN
Site-to-Site VPN Connections
VNet-to-VNet Connections
ExpressRoute Connections

Microsoft Azure Virtual Network Services : Reading #4 Distributing Network Traffic

There are different options to distribute network traffic using Microsoft Azure. These options work differently from each other, have different feature sets, and support different scenarios. They can each be used in isolation or in combination.

Network Distribution Comparison

Azure Load Balancer
Works at transport layer (level 4 of OSI network stack)
Provides network-level distribution of traffic across instances of the application running in the same Azure datacenter
Use Load Balancer to:
Balance incoming traffic to VMs from the Internet (Internet-facing load balancing)
Balance traffic between VMs in the VNets or cloud services (Internal Load Balancer)
Balance traffic between on-premises servers and VMs (Internal Load Balancer)
Forward traffic to specific VM

Configure Load Balancer Requirement
configure load balancer requirement

Distribution mode for Azure load balancer
Hash-based distribution mode
Source IP affinity mode / session affinity

Configure Load Balancer:
Port Forwarding
Automatic Reconfiguration
Service Monitoring to probe the health of the various server instances (see LoadBalancerProbe Schema)
Source NAT
Deploy Network Load Balancer: Azure Classic vs Azure Resource Manager

Traffic Manager
The most important point to understand is that Traffic Manager works at the DNS level.
Uses DNS responses to direct end-user traffic to globally distributed endpoints running in different datacenters around the world.
Clients connect directly to endpoints

Application Gateway
Azure-managed HTTP load-balancing solution based on layer-7 load balancing.
Works as a reverse-proxy service
Client connections are terminated at gateway and then forwarded to application

Application Gateway supports layer-7 application delivery for the following:
HTTP load balancing
Cookie-based session affinity
Secure Sockets Layer (SSL) offload
URL-based content routing
Multi-site routing

Application Gateway HTTP layer 7 load balancing is useful for:
Applications that require requests from the same user/client session to reach the same back-end virtual machine.
Applications that want to free web server farms from SSL termination overhead.
Applications, such as a content delivery network, that requires multiple HTTP requests on the same long-running TCP connection to be routed or load balanced to different back-end servers.

Application Gateway Health Monitoring
Azure Application Gateway by default monitors the health of all resources in its back-end pool and automatically removes any resource considered unhealthy from the pool. Application Gateway continues to monitor the unhealthy instances and adds them back to the healthy back-end pool once they become available and respond to health probes.

Configuring Application Gateway

Configuring Application Gateway Requirements