MasayukiOzawa

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$Server = "hogehoge"
$Login = "hogehoge"
$Password = "hogehoge"

$DBName = "hogehoge"
$TableName = "hogehoge"

$SQLServer = New-Object Microsoft.SqlServer.Management.Smo.Server($Server)

MasayukiOzawa

tzutil.exe /s "Tokyo Standard Time"
$NewList = New-WinUserLanguageList -Language "ja-JP"
$NewList.Add("en-US")
Set-WinUserLanguageList $NewList -Force
Set-WinUILanguageOverride ja-JP
Set-WinHomeLocation 0x7A

Set-ItemProperty -Path registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\i8042prt\Parameters\ -Name "LayerDriver JPN" -Value "kbd106.dll" -Type String
Set-ItemProperty -Path registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\i8042prt\Parameters\ -Name "OverrideKeyboardIdentifier" -Value "PCAT_106KEY" -Type String
Set-ItemProperty -Path registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\i8042prt\Parameters\ -Name "OverrideKeyboardSubtype" -Value 2 -Type DWord

MasayukiOzawa

$Results = New-Object -TypeName System.Collections.Generic.List[PSCustomObject]
$aryBlockSize = @("4K", "8K", "64K", "128K", "256K")
$aryThread = @(1,2,8,16)
$aryPattern = @("si", "r")
$duration = 60


$TestFile = "E:\test.dat"
$DiskSpd = "C:\Diskspd-v2.0.15\amd64fre\diskspd.exe"
$BaseCmd = "$DiskSpd -c10G -b{0} -o1 -h -t{1} -{2} {3} -Rxml -d{4} $TestFile"

MasayukiOzawa

$cred = Get-Credential
$Node = @("2016-WSFC-01", "2016-WSFC-02", "2016-WSFC-03")

$script = {
$SQLSetup = "D:\setup.exe"
$InstallConfig = @'
;SQL Server 2016 CTP2.3 Configuration File
[OPTIONS]

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 

MasayukiOzawa

# https://msdn.microsoft.com/en-us/library/azure/mt619274.aspx
# https://msdn.microsoft.com/en-us/library/azure/mt603754.aspx
# http://statemachine.hatenablog.com/entry/2015/07/06/183650
# http://statemachine.hatenablog.com/entry/2015/07/22/183239
# https://azure.microsoft.com/ja-jp/documentation/articles/resource-group-overview/
# https://gist.github.com/nmackenzie/54e9fe4bb34f8d6bce2e

$azaccount = Login-AzureRmAccount

$subscription = Get-AzureRmSubscription | Out-GridView -OutputMode Single

MasayukiOzawa

SET NOCOUNT ON
GO
/*
WideWorldImporters: The new SQL Server sample database
https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/09/wideworldimporters-the-new-sql-server-sample-database/

https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
*/

-- Column store Index

MasayukiOzawa

-- クエリ情報の実行時間上位 50 件の取得 (ポータルで実行されているクエリ)
select top 50
	(case when requests.status = 'Completed' then 100
	when progress.total_steps = 0 then 0
	else 100 * progress.completed_steps / progress.total_steps end) as progress_percent,
	requests.status, 
	requests.request_id, 
	sessions.login_name, 
	requests.start_time, 
	requests.end_time, 

MasayukiOzawa

-- https://azure.microsoft.com/ja-jp/documentation/articles/sql-data-warehouse-develop-concurrency/


-- ワークロードグループのリソースと使用状況の確認
WITH rg
AS
(   SELECT  pn.name                                 AS node_name
    ,       pn.[type]                               AS node_type
    ,       pn.pdw_node_id                          AS node_id
    ,       rp.name                                 AS pool_name

MasayukiOzawa

# https://azure.microsoft.com/ja-jp/documentation/articles/sql-database-develop-direct-route-ports-adonet-v12/
# https://blogs.msdn.microsoft.com/sqlcat/2016/09/08/connect-to-azure-sql-database-v12-via-redirection/
# https://msdn.microsoft.com/en-us/library/azure/mt604439.aspx
$adTenant = "hogehoge.onmicrosoft.com"
$subscriptionid = "xxxxxxxxxxx"
$resourgegroup = "groupname"
$servername = "servername"

$user = "hogehoge@hogehoge.onmicrosoft.com"
$password = "M@sterEr0s"

MasayukiOzawa

$sqlinstancename = "MSSQL13.MSSQLSERVER"
$SQLBinPath = (Get-ItemProperty ([io.path]::combine("HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server", $sqlinstancename, "Setup"))).SQLBinRoot

$param = @{
    ExclusionExtension = "mdf", "ldf", "ndf" , "bak", "trn"
    ExclusionProcess = (Get-Item (Join-Path $SQLBinPath "sqlservr.exe"))
    ExclusionPath = "C:\Windows\Cluster"
}

Set-MpPreference @param