PowerShellでDBサーバーの構築を自動化する方法まとめ(SQL Server)

┳Script
┃　┣InstallSQLServerManagementStudio.ps1
┃　┣InstallSQLServer2016.ps1
┃　┣ModifySQLServerConfig.ps1
┃　┣BackupAndRestore.ps1
┃　┣Config.ps1
┃　┗Functions.ps1
┣SQL
┃　┣modifyUserMapping.sql
┃　┣createLoginUser.sql
┃　┣fullBackup.sql
┃　┗restore.sql
┗Installer
　　┣SSMS-Setup-JPN.exe
　　┗SQLServer2016.iso

InstallSQLServerManagementStudio.ps1
InstallSQLServer2016.ps1

ModifySQLServerConfig.ps1
BackupAndRestore.ps1

$scriptDir=Split-Path $MyInvocation.MyCommand.Path -Parent

. $scriptDir"\Config.ps1"
. $scriptDir"\Functions.ps1"

installSSMS

$scriptDir=Split-Path $MyInvocation.MyCommand.Path -Parent

. $scriptDir"\Config.ps1"
. $scriptDir"\Functions.ps1"

installSQLServer

$scriptDir=Split-Path $MyInvocation.MyCommand.Path -Parent

. $scriptDir"\Config.ps1"
. $scriptDir"\Functions.ps1"

activateTCPIPAndFixPort
openSQLServerPort

$scriptDir=Split-Path $MyInvocation.MyCommand.Path -Parent

. $scriptDir"\Config.ps1"
. $scriptDir"\Functions.ps1"

backupOldDB
copyBackupFromOldDBToNewDB
restore
createLoginUserSQL
modifyUserMapping

# スクリプトのパス
$scriptDir=Split-Path $MyInvocation.MyCommand.Path -Parent

#-- DBサーバー設定 start --#

# SQL Serverのサービス名
$sQLServerServiceName = "SQL Server (TEST)"
# SQL Serverで使用するポート番号
$port = "1433"
# 旧DBサーバー名
$oldDBServerName = "OLD"
# 新DBサーバー名
$newDBServerName = "NEW"
# DBインスタンス
$dBInstanceName = "TEST"
# ユーザー名
$userName = "sa"
# パスワード
$password = "TEST001#"

#-- DBサーバー設定 end --#


#-- インストーラー設定 start --#

# SQL ServerのISOファイルのパス
$isoPath = $scriptDir + "\..\Installer\SQLServer2016.ISO"
# SQL Serverインストール構成ファイルのパス
$confPath=$scriptDir+"\..\Config\ConfigurationFile.ini"
# SQL Server Management Studioのインストーラーのパス
$sSMSInstallerPath = $scriptDir + "\..\Installer\SSMS-Setup-JPN.exe"

#-- インストーラー設定 end --#


#-- バックアップ設定 start --#

# 旧DBサーバーのフルバックアップファイルのディレクトリ
$oldDBBackupSrcDir = "\\OLD\E$\TEST\Backup\"
# 新DBサーバーのフルバックアップファイルのディレクトリ
$newDBBackupDstDir = "E:\TEST\Backup\"
# DBフルバックアップファイル
$BackupFileName = "TEST_FULLBACKUP"

#-- バックアップ設定 end --#


#-- SQLパス start --#

# フルバックアップを取得するSQL
$backupSQL = $scriptDir + "\..\SQL\fullBackup.sql"
# リストアするSQL
$restoreSQL = $scriptDir + "\..\SQL\restore.sql"
# ログインユーザー作成SQL
$createLoginUserSQL = $scriptDir + "\..\SQL\createLoginUser.sql"
# ユーザーマッピング修正SQL
$modifyUserMappingSQL = $scriptDir + "\..\SQL\modifyUserMapping.sql"

#-- SQLパス end --#

function installSQLServer() {
    # ISOファイルをマウントする
    Mount-DiskImage $isoPath
    $driveLetter = (Get-DiskImage -ImagePath $IsoPath | Get-Volume).DriveLetter
    $driveRootPath = "{0}:\" -f $driveLetter

    # SQL Server 2016をインストールする  
    $sQLServerSetupExe = $driveRootPath + "SETUP.exe"
    # SAPWDでsaのパスワードを指定する
    # IAcceptSQLServerLicenseTermsを有効にしないとサイレントインストールが出来ない
    # Configurationfileに事前にGUIでインストール直前まで設定した設定ファイルを指定する
    Start-Process -FilePath $sQLServerSetupExe -ArgumentList "/SAPWD=$password /IAcceptSQLServerLicenseTerms=True /Configurationfile=$confPath" -Verb runas -Wait

    # ISOファイルを取り出す  
    Dismount-DiskImage $isoPath
}

function installSSMS() {
    Start-Process -FilePath $sSMSInstallerPath -ArgumentList "/install /quiet /passive /norestart" -Verb runas -Wait
}

function backupOldDB() {
    # フルバックアップを取得する
    sqlcmd -S $oldDBServerName -d $dBInstanceName -U $userName -P $password -i $BackupSQL
}

function copyBackupFromOldDBToNewDB() {
    # 取得した旧DBサーバーから新DBサーバーにコピーする
    robocopy $oldDBBackupSrcDir $newDBBackupDstDir $backupFileName /r:0
}

function restore() {
    # 取得したフルバックアップをリストアする
    sqlcmd -S $newDBServerName -U $userName -P $password -i $restoreSQL
}

function activateTCPIPAndFixPort() {
    #TCP/IPでの接続を有効にする(SQLServerの再起動が必要)
    Set-ExecutionPolicy -Scope Process RemoteSigned -Force
    Import-Module 'sqlps'
    $uri = "ManagedComputer[@Name='{0}']/ ServerInstance[@Name='{1}']/ServerProtocol[@Name='Tcp']" -f $newDBServerName, $dBInstanceName
    $wmi = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer'

    # TCP/IPを有効にする
    $tcp = $wmi.GetSmoObject($uri)
    $tcp.IsEnabled = $true
    $tcp.Alter()

    # Port番号を1433に固定する
    $wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = $port
    $tcp.Alter()

    # SQLServerを再起動する
    net stop $sQLServerServiceName /y
    net start $sQLServerServiceName
}

function openSQLServerPort() {
    New-NetFirewallRule `
        -Name "SQLServer（1433）" `
        -DisplayName "SQLServer（1433）" `
        -Description "SQL Serverで使用するポート（1433）を開放する。" `
        -Enabled True `
        -Profile Domain,Private `
        -Direction Inbound `
        -Action Allow `
        -Program Any `
        -LocalAddress Any `
        -RemoteAddress Any `
        -Protocol TCP `
        -LocalPort 1433
}

function createLoginUserSQL() {
    # ログインユーザーを作成する
    sqlcmd -S $newDBServerName -U $userName -P $password -i $createLoginUserSQL
}

function modifyUserMapping() {
    # ユーザーマッピングを修正する
    sqlcmd -S $newDBServerName -U $userName -P $password -i $modifyUserMappingSQL
}

USE TEST
EXEC sp_change_users_login 'Update_One', 'your_user', 'your_user'
GO

use master
CREATE LOGIN [your_user] WITH 
    PASSWORD=N'your_password', 
    DEFAULT_DATABASE=[master], 
    DEFAULT_LANGUAGE=[日本語], 
    CHECK_EXPIRATION=OFF, 
    CHECK_POLICY=OFF
GO
ALTER LOGIN [your_user] ENABLE
GO

BACKUP DATABASE [TEST] TO  DISK = N'E:\TEST\Backup\TEST_FULLBACKUP' WITH NOFORMAT, NOINIT,  NAME = N'TEST-完全 データベース バックアップ', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

USE [master]
RESTORE DATABASE [TEST] FROM  DISK = N'E:\TEST\Backup\TEST_FULLBACKUP' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO