SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Creating Azure SQL Data Warehouse Databases

By Chris Testa-O'Neil,

Azure SQL Data Warehouse uses familiar T-SQL language constructs to create databases. However, there are differences between the create database syntax for on premise SQL Servers and Azure SQL Data Warehouse. Let’s examine the differences that are important for a developer to know.

We will start with the CREATE DATABASE statement itself. This can be performed once you have connected to the SQL Server instance – that was described in this post --in Visual Studio and run the following command:

CREATE DATABASE EquityDB COLLATE SQL_Latin1_General_CP1_CI_AS
(
       EDITION                    = 'DataWarehouse'
,      SERVICE_OBJECTIVE          = 'DW100'
,      MAXSIZE                    = 1024 GB
)
;

You will note that the command does not define the creation of data files and log files. This is not required when creating a database in SQL Data Warehouse, as the data will be spread across all of the 60 available distributions that we discussed in the previous post, and the log is created under the hood.

You will see that there are COLLATE and MAXSIZE options available that work in the same way as the options for an on premise SQL Server. At the time of writing, the maximum size of an Azure SQL Data Warehouse is 245760GB (24TB).

However, there is an EDITION clause and a SERVICE_OBJECTIVE clause. These are options that are not available with an on premise SQL Server, but are specific to Azure version of SQL Server services such as SQL Database and SQL Data Warehouse. When creating an Azure Data Warehouse, you must specify the edition as DataWarehouse, and the SERVICE_OBJECTIVE as the capacity of your data warehouse. This service_objective can go from DW100 to a maximum of DW6000. This determines the compute level that is being used by the data warehouse.

You can also create a database using a PowerShell script as shown in the following code:

# CREATE A SQL DATABASE

$resourceGroupName = "cto_ads_prep_rg"
$serverName = "ctomsftadssqlsrv"
$databaseName = "ContosoRetailDW"
$databaseEdition = "DataWarehouse"
$RequestedServiceObjectiveName = "DW400"

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

The following video shows you how to create a database in Azure SQL Data Warehouse.

It is important to note that within Azure SQL Data Warehouse, cross database queries are not permitted. In scenarios where you would typically do this, your design should change to incorporate the creation of database schemas to hold data within the schema that you would typically hold in separate databases. This is performed using the CREATE SCHEMA T-SQL statement that works in the same way as on premise SQL Server.

With the database created, you can then proceed to create tables. That’s the next installment of this series….

Previous Articles

In this series, we have also covered the following topics:

 
Total article views: 565 | Views in the last 30 days: 4
 
Related Articles
BLOG

Creating a large data warehouse in Azure

Microsoft Azure provides you two options when hosting your SQL Server-based data warehouse: Microsof...

FORUM

New to Data Warehouse - Setup data warehouse environment

Need to setup data warehouse environment for two databases resided on two servers

BLOG

Data Virtualization vs Data Warehouse

Data virtualization goes by a lot of different names: logical data warehouse, data federation, virtu...

ARTICLE

Azure SQL Data Warehouse with PowerShell

We will teach how to create a new SQL Azure Data Warehouse using Powershell

FORUM

How to create SSIS package in SQL Server Database System

How to create SSIS package in SQL Server Database System

 
Contribute