Azure DW

1. 安装环境
a. 安装环境
https://www.microsoft.com/web/downloads/platform.aspx

b. Input
Import-Module 'C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Azure.psd1'
具体的路径因人而异，要改成自己机器的Azure.psd1的位置

2. 安装DW
a. Login
Add-AzureRmAccount -EnvironmentName azurechinacloud
会弹出输入密码的框，输入subscription账号。

b. Create resource group
New-AzureRmResourceGroup -Name ResourceGroup -Location "China North"

c. Create DB with V12
New-AzureRmSqlServer -ServerName sqlinstance -Location "China North"
创建普通DB, 是为了升级到V12(默认V11, 而DW必须V12, instance中没有DB又无法升级)
升级完可以把这个普通DB删除

New-AzureRmSqlDatabase -DatabaseName DB -ServerName sqlinstance -Edition Standard -ResourceGroupName ResourceGroup

 

$SubscriptionName = 'name'
$ResourceGroupName = 'ResourceGroup'
$ServerName = 'dbinstance'

Start-AzureRmSqlServerUpgrade -ResourceGroupName $ResourceGroupName -ServerName $ServerName -ServerVersion 12.0 -DatabaseCollection $hint.Databases -ElasticPoolCollection $hint.ElasticPools

 

d. Create DW
New-AzureRmSqlDatabase -RequestedServiceObjectiveName "DW100" -DatabaseName "SQLDW" -ServerName "sqlinstance" -ResourceGroupName "ResourceGroup" -Edition "DataWarehouse"

 

3. 几个常用命令：
a. 通用语句
Get-AzureRmSQLDatabase -DatabaseName "SQLDW" -ServerName "dbinstance" -ResourceGroupName "ResourceGroup"

b. 查看能够设置成什么性能
Get-AzureRmSqlServerServiceObjective -ResourceGroupName "ResourceGroup" -ServerName "dbinstance" -DatabaseName "SQLDW" | foreach{write-host $_.ServiceObjectiveName}

c. 停止DW服务
注：不用时将CPU关闭，DW最费钱的是用于distribution的一堆CPU
Suspend-AzureRmSqlDatabase –ResourceGroupName "ResourceGroup1" `
–ServerName "Server01" –DatabaseName "Database02"

d. 开启DW服务
Resume-AzureRmSqlDatabase –ResourceGroupName "ResourceGroup1" `
–ServerName "Server01" -DatabaseName "Database02"

e. 设置成其他的性能
Set-AzureRmSqlDatabase -DatabaseName "MySQLDW" -ServerName "MyServer" -RequestedServiceObjectiveName "DW1000"

4. 开发环境Visual Studio 2015需要安装的插件
注：连接DW使用VS连，新建Query, 将SQL语句包含在里面，点debug执行。
a. SSDT
https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-install-visual-studio/
b. Query（下SQL执行语句）
https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-query-visual-studio/

c. SSIS（将数据导入到DW中）
https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-load-from-sql-server-with-integration-services/