ADW3: Creating Databases in Azure SQL Data Warehouse

AzureSQLDW

Creating databases within Azure SQL Data Warehouse uses very familiar T-SQL language constructs to create them. However, there are subtle difference in the TSQL between creating an on premise SQL Server database and an Azure SQL Data Warehouse.

Let’s start with the CREATE DATABASE statement itself. This can be performed once you have connected to the SQL Server instance 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 distributed across all the available distributions, and the log is created under the hood.

You will see that there is a COLLATE and MAXSIZE option available that works in the same way as the options for on premise SQL Server. At the time of writing, the maximum size of a Data Warehouse is 245760 GB.

However, there is an EDITION clause and a SERVICE_OBJECTIVE clause. These are option that are not available with 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 can go 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 TSQL 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 ADW blog….

 

Advertisements

3 thoughts on “ADW3: Creating Databases in Azure SQL Data Warehouse

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s