Creating a Data Warehouse in the Cloud

Play Creating a Data Warehouse in the Cloud
Sign in to queue

Description

For a few months now, we have had a SQL Server edition in the cloud known as Azure SQL Data Warehouse. This version enables you to provision a data warehouse instance in just 3 to 5 minutes. Its main benefit is that it allows you to scale your compute in seconds to keep up with your organizations data demands. So, if you are performing heavy data loads, you can maximise the amount of compute for the duration of a data load, only to scale the compute back down once the load is complete. Furthermore, if you don't require access to your data, you can pause the compute so that you can keep control of your costs, while still retaining the data.

These are some of the business benefits of Azure SQL Data Warehouse, but how does it work?

SQL Data Warehouse is a massively parallel processing (MPP) distributed database system. Behind the scenes, SQL Data Warehouse spreads your data across many shared-nothing storage and processing units. The data is stored in a Premium locally redundant storage layer on top of which dynamically linked Compute nodes execute distributed queries. SQL Data Warehouse takes a 'divide and conquer' approach to running loads and complex queries. Requests are received by a Control node, optimized for distribution, and then passed to Compute nodes to do their work in parallel as shown in the following graphic.

SQLDW

Azure Data Warehouse is ideal for analytical workloads whether it is a small workload of GB's, to a large workload of PB's. It can also interface with unstructured data stored an Azure Blob Store. If transactional consistency and high concurrency is your requirement, then Azure SQL Data Warehouse is not the service to use. SQL Database would be a more appropriate choice.

It only takes minutes to get this up and running, and you can either do this within the Azure Portal, or alternatively you can use PowerShell.

OK, so there is information that you need to have to hand before creating an Azure SQL Data Warehouse:

DWUI

Armed with this information, you can then go ahead and create the SQL Data Warehouse Instance.

Alternatively, you can use the same information to create a PowerShell script to sign into an Azure Subscription, create a resource group and then create a SQL Server instance, and optionally a database. The following PowerShell code creates a resource group named cto_ads_prep_rg located in North Europe using the New-AzureRmResourceGroup cmdlet.  The script then creates a SQL Server instance named ctomsftadssqlsrv with an admin account named ctesta-oneill using the New-AzureRmSqlServer cmdlet.

######################################################################

PART I: Creating the Azure SQL Server

######################################################################

## Sign in to Azure and set the WINDOWS AZURE subscription to work with

$SubscriptionId = "XXXXXXXX-xxXX-XXxx-XXXX-xxxxxxxxxxxx"

Add-AzureRmAccount

Set-AzureRmContext -SubscriptionId $SubscriptionId

## CREATE A RESOURCE GROUP

$resourceGroupName = "cto_ads_prep_rg"

$rglocation = "North Europe"

New-AzureRmResourceGroup -Name $resourceGroupName -Location $rglocation

## CREATE A SERVER

$serverName = "ctomsftadssqlsrv"

$serverVersion = "12.0"

$serverLocation = "North Europe"

$serverAdmin = "ctesta-oneill"

$serverPassword = "P@ssw0rd"

$securePassword = ConvertTo-SecureString String $serverPassword -AsPlainText -Force

$serverCreds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $serverAdmin, $securePassword

$sqlDbServer = New-AzureRmSqlServer -ResourceGroupName $resourceGroupName -ServerName $serverName -Location $serverLocation -ServerVersion $serverVersion -SqlAdministratorCredentials $serverCreds

You can also use PowerShell to configure firewall settings on the SQL Server instance using the New-AzureRmSqlServerFirewallRule cmdlet. This can be performed in the Azure Portal as well.

## CREATE A SERVER FIREWALL RULE

$ip = (Test-Connection -ComputerName $env:COMPUTERNAME -Count 1 -Verbose).IPV4Address.IPAddressToString

$firewallRuleName = "Client IP address"

$firewallStartIp = $ip

$firewallEndIp = $ip

$fireWallRule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $serverName -FirewallRuleName $firewallRuleName -StartIpAddress $firewallStartIp -EndIpAddress $firewallEndIp

With the firewall rules defined, you will then be able to access to Azure SQL Server using tools such as Visual Studio and SQL Server Management Studio, where you could run T-SQL scripts to create and manage database. Although this can be done in PowerShell using the New-AzureRmSqlDatabase cmdlets as well. The following code creates a data warehouse named ContosoRetailDW.

 

## CREATE A SQL DATABASE

$databaseName = “ContosoRetailDW”

$databaseEdition = “DataWarehouse”

$RequestedServiceObjectiveName = “DW400”

$sqlDatabase = New-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -RequestedServiceObjectiveName $RequestedServiceObjectiveName -ServerName $serverName -DatabaseName $databaseName -Edition $databaseEdition.

Once a database has been created, you can then understand how to scale and Pause a database in Azure SQL Data Warehouse, but that will be the next blog.

Embed

Download

Download this episode

The Discussion

Add Your 2 Cents