Create data Warehouse in Azure China using PowerShell

Microsoft's Azure Data Warehouse is a distributed system based on the MPP architecture:

Control node is responsible for managing the system and accepting requests from users, Compute node is responsible for computing.

Currently, Azure Data Warehouse has landed in the country. You can use the new portal page to manage it, or you can use PowerShell to manage it.

This article describes how to manage with PowerShell. Includes creation, scale out, suspend, and resume.

1 Environment Preparation

Log in to Azure China and create the resource Group

$mycred = get-credential-username [email protected]-Message Hello Login$mycred  New   "ChinaEast"/subscriptions/xxxx-xxxx-xxxx/resourcegroups/hwdweast 

2 Creating Azure SQL Server

SQL Server created is a V12 version

$sqlcred = get-credential-username Hengwei-Message Hello New$sqlcred"China East" -serverversion 12.012.0

3 Configure the firewall rule for the newly created database server, in this case because it is a test, add all available address segments

New-azurermsqlserverfirewallrule-firewallrulename all-startipaddress 1.1.1.1-endipaddress 223.255.255.254- ServerName hwdweast-1.1.1.1223.255.255.254 firewallrulename:all

4 Creating the Data WareHouse

new-azurermsqldatabase-databasename hwdwdbeast-requestedserviceobjectivename "DW100"-ServerName Hwdwsv- Resourcegroupname hwdweast-edition datawarehouse-collationname "SQL_Latin1_General_CP1_CI_AS"-MaxSizeBytes 10995116277760

Resourcegroupname:hwdweast servername:hwdweast databasename:hwdwdbeast Location:china East databaseid:3e5f4387
    -099d-4a7d-858d-0d663381663a edition:datawarehouse collationname:sql_latin1_general_cp1_ci_as catalogcollation:maxsizebytes: /c0>1099511627776status:online CreationDate:2016/10/5 13:46:19currentserviceobjectiveid:4e63cb0e-91b9-46fd-b05c-51fdd2367618 currentserviceobjectivename:dw100 requestedserviceobjectiveid:4e63cb0e-91b9-46fd-b05c-51fdd2367618 RequestedServiceObjectiveName:ElasticPoolName:EarliestRestoreDate:0001/1/1 0:00:00Tags:resourceid:/subscriptions/xxxx-xxxx-xxxx/resourcegroups/hwdweast/providers/microsoft.sql/servers/HWDW East/databases/hwdwdbeast Createmode:

which

-edition Datawarehouse shows that this is data Warehouse

-requestedserviceobjectivename "DW100" Specifies the size of the Data warehouse, DW100 indicates that there are 1 compute nodes

-collationname "SQL_Latin1_General_CP1_CI_AS" specifies the character set

-maxsizebytes 10995116277760 Specify maximum capacity

A few minutes later, a data warehouse was created.

4 Changing the Data warehouse size

Set-azurermsqldatabase-databasename Hwdwdbeast-requestedserviceobjectivename"DW200"-servername Hwdwsv-resourcegroupname hwdweast resourcegroupname:hwdweast servername:hwdweast databasename:hwdwdbeast location:ch Ina East databaseid:3e5f4387-099d-4a7d-858d-0d663381663a edition:datawarehouse collationname:sql_latin1_general_cp1_ci_as catalogcollation:maxsizebytes: /c1>1099511627776status:online CreationDate:2016/10/5 13:46:19Currentserviceobjectiveid:99e78a92-d724-4e1b-857b-2be661f3d153 currentserviceobjectivename:dw200 requestedserviceobjectiveid:99e78a92-d724-4e1b-857b-2be661f3d153 RequestedServiceObjectiveName:ElasticPoolName:EarliestRestoreDate:0001/1/1 0:00:00Tags:resourceid:/subscriptions/xxxx-xxxx-xxxx/resourcegroups/hwdweast/providers/microsoft.sql/servers/HWDW East/databases/hwdwdbeast Createmode:

5 pausing a service for a data warehouse

Suspend-azurermsqldatabase-databasename Hwdwdbeast-servername Hwdweast-resourcegroupname hwdweast resourcegroupname:hwdweast servername:hwdweast databasename:hwdwdbeast location:ch Ina East databaseid:3e5f4387-099d-4a7d-858d-0d663381663a edition:datawarehouse collationname:sql_latin1_general_cp1_ci_as catalogcollation:maxsizebytes: /c4>1099511627776status:paused CreationDate:2016/10/5 13:46:19currentserviceobjectiveid:4e63cb0e-91b9-46fd-b05c-51fdd2367618 currentserviceobjectivename:dw100 requestedserviceobjectiveid:4e63cb0e-91b9-46fd-b05c-51fdd2367618 RequestedServiceObjectiveName:ElasticPoolName:EarliestRestoreDate:0001/1/1 0:00:00Tags:resourceid:/subscriptions/xxxx-xxxx-xxxx/resourcegroups/hwdweast/providers/microsoft.sql/servers/HWDW East/databases/hwdwdbeast Createmode:

At this point, the data is still in warehouse, but the calculation unit has stopped charging.

This model is particularly suitable for scenarios that require report statistics at the end of the month, quarter, and year end.

6 recovering a service from a data warehouse

Resume-azurermsqldatabase-databasename Hwdwdbeast-servername Hwdweast-resourcegroupname hwdweast resourcegroupname:hwdweast servername:hwdweast databasename:hwdwdbeast location:ch Ina East databaseid:3e5f4387-099d-4a7d-858d-0d663381663a edition:datawarehouse collationname:sql_latin1_general_cp1_ci_as catalogcollation:maxsizebytes: /c1>1099511627776status:online CreationDate:2016/10/5 13:46:19currentserviceobjectiveid:4e63cb0e-91b9-46fd-b05c-51fdd2367618 currentserviceobjectivename:dw100 requestedserviceobjectiveid:4e63cb0e-91b9-46fd-b05c-51fdd2367618 RequestedServiceObjectiveName:ElasticPoolName:EarliestRestoreDate:0001/1/1 0:00:00Tags:resourceid:/subscriptions/xxxx-xxxx-xxxx/resourcegroups/hwdweast/providers/microsoft.sql/servers/HWDW East/databases/hwdwdbeast Createmode:

Currently, data Warehouse can only be created via PowerShell, and in the future, Azure in China will do all of the above with a graphical interface like Global Azure.

Create data Warehouse in Azure China using PowerShell