Last active
September 4, 2015 06:32
-
-
Save yungchou/14c4ccad10c040f1914d to your computer and use it in GitHub Desktop.
IT Camp Lab 4 - Application Deployment as a Service Deployment Teamplate v1.7
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#region [Customization - The only part to customize, as preferred] | |
write-host " | |
------------------------------------------------------------------------ | |
This PowerShell script and its associated files are intended for and | |
only for learning and testing in a lab environemnt. The code is not | |
optimized, nor adequately secure for a production use. | |
The content of this script is provided AS IS; with no warranties, | |
and confers no rights. | |
© 2015 Yung Chou. | |
----------------------------------------------------------------------- | |
RTM V1.7 - This script now automatically re-validates SQL after deployment. | |
This lab gives you an opportunity to see the experience of using PowerShell to drive the automation of | |
deployments. | |
You can download the full script to run at: http://aka.ms/tiy or at http://aka.ms/lab4script. It demonstrates | |
automatic deployment of Microsoft Azure Infrastructure Services using Azure PowerShell. This particular demo | |
illustrates the concept from deploying a set of VMs forming an application architecture (IaaS) to configure | |
a target runtime environment (PaaS) and create/start a two-tier application instance (SaaS). What it deploys | |
are not just VMs, but a set of VMs collectively delivering a business function. The value proposition of | |
this exercise is an end-to-end highly automated deployment of a target application instance on demand, | |
i.e. application deployment as a service. | |
The requirements to run this script are: | |
1. Have Azure PowerShell installed locally | |
Ref: http://aka.ms/AzureCmdlets | |
2. Be able to log in with an Azure account | |
Here are the steps to run the script: | |
1. Access the script at http://aka.ms/TIY and click Lab4 Script. | |
2. Click RAW to open as text. | |
3. Ctrl-a to select all and Ctrl-c to copy. | |
4. Start a PowerShell ISE session as administrator on your local desktop. | |
5. If do not see the script pane, Ctrl-r to show it. | |
6. Click the white space of the script pane and Ctrl-v paste the script. | |
7. Save the script to your intended location. | |
8. Press F5 to run the script. | |
9. Follow the screen output and respond to the prompts. | |
10. Log in the Contoso data management app with 12345 as the password and test the application. | |
11. Answer yes or no in the ISE session to delete/keep the deployment upon finishing your tests. | |
The coding style and the organization of this script are for clarity and readability, instead of efficiency and | |
optimization. Notice that in this script all credentials are provided and passed as plain text for simplicity. | |
There is very limited error handling. This script is for learning and training, and not for production use. | |
----------------------------------------------------------------------- | |
" -f yellow | |
do {$initials = (read-host 'Enter 3 chars as a prefix for the deployment').ToLower()} | |
until ( | |
($initials.length -eq 3) -and | |
($initials[0] -in [char]'a'..[char]'z') -and | |
($initials[1] -in [char]'a'..[char]'z') -and | |
($initials[2] -in [char]'a'..[char]'z') | |
) | |
#--------------------------------------------------- | |
# Validate the following information, as aplicable. | |
# If to deploy this vnet, it must pre-exist. This | |
# script does not create the following vnet. | |
#--------------------------------------------------- | |
$vnet = @{ site='fooNet'; region='West US'; subnet='app-subnet'} | |
#endregion | |
#region [DO NOT CHANGE - Function Library and Session Information] | |
$VerbosePreference = "SilentlyContinue" | |
#$VerbosePreference = 'Continue' | |
#Requires -Version 3 | |
#Requires -RunAsAdministrator | |
#Requires -Modules Azure | |
function pick-one-from { | |
param ([array]$thisList, [string]$BasedOn) | |
if ($thisList.count -eq 1) { $thisOne = $thisList[0] } | |
else { | |
$i=1; $newList = @() | |
foreach ( $item in $thisList ) { | |
$newList+="`n$i.`t$item" | |
$i++ | |
} | |
do { | |
write-host "`nHere's the $BasedOn list `n$newList" | |
$thePick = Read-Host "`nWhich $BasedOn" | |
} while(1..$newList.length -notcontains $thePick) | |
$thisOne = $thisList[($thePick-1)] | |
} | |
return $thisOne | |
} | |
function get-ImageName { | |
param ([string]$pattern) | |
# The image name with the latest published date | |
return (Get-AzureVMImage ` | |
| where {$_.Label -like '*'+$pattern+'*' } ` | |
| Sort-Object -Descending -Property PublishedDate ).ImageName[0] | |
} | |
function setup-folder { | |
param ([string]$here) | |
if (!(Test-Path $here)) { | |
New-Item $here -type directory | out-null | |
} | |
return $here | |
} | |
function open-IE-with { | |
param ([string]$thisURL,[boolean]$Visible=$true) | |
$ie = New-Object -ComObject InternetExplorer.Application | |
$ie.Navigate("http://$thisURL") | |
$ie.Visible = $Visible | |
Write-Host "`nOpened an IE session for accessing the service, http://$thisURL" -f black -b yellow | |
} | |
function clean-up { | |
param ([array]$theseServices, [string]$thisStorageAccount, [string]$thisFolder) | |
#Delete multiple-service deployments | |
foreach ($s in $theseServices) { | |
$deleteDeployment = Remove-AzureService -ServiceName $s -DeleteAll -Force | |
} | |
Remove-Item $thisFolder -Recurse -Force | |
write-host "`nDeleteded the folder, $thisFolder" -f yellow | |
do { | |
write-host "`nDisks are still being locked, wait for 60 seconds (ctrl-c to exit)" -f green | |
start-sleep 60 | |
try{ | |
write-host "Try deleting the storage account, $thisStorageAccount" -f green | |
remove-azurestorageaccount -StorageAccountName $thisStorageAccount ` | |
-ErrorAction SilentlyContinue | |
} | |
catch { write-host 'Something went very wrong, if it gets here.' -f green} | |
} until ($?) | |
} | |
#endregion | |
#region [DO NOT CHANGE - Deployment Settings] | |
Add-AzureAccount | |
$myAzureSubscription = ` | |
pick-one-from -thisList ( (Get-AzureSubscription).SubscriptionName ) ` | |
-BasedOn 'Azure subscription' | |
write-host "$(get-date -f T) - Using this subscription, $myAzureSubscription" -f green | |
#-------------------------- | |
# FY15Q4 IT Camp Specifics | |
#-------------------------- | |
$AdminUser = 'sysadmin' ; $AdminPassword = 'Passw0rd!' | |
$ref = 'ITCamp' | |
$demoRootFolder = "$home\Desktop\$ref" | |
write-host "`nSession Tag = "($tag = "$initials$(get-date -format 'mmss')") -f black -b yellow | |
write-host "`n$(get-date -f T) - Created a session folder at `n"($sessionFolder = setup-folder -here "$demoRootFolder\$tag") -f green | |
#----------------- | |
# Where to deploy | |
#----------------- | |
$deployedToSubnet ='?' | |
$deployedToSubnet ='n' # Comment this out if to offer deploying to a subnet as defined in [Customization...] | |
while ($deployedToSubnet.ToLower() -notin ('y','n')) { | |
$deployedToSubnet = read-host "`nDeploy $tag to this vnet ($($vnet.site)), this subnet ($($vnet.subnet)), and in this region ($($vnet.region)) - y/n" | |
} | |
if ($deployedToSubnet -eq 'y') { | |
$deployedToSubnet = $true | |
$region = $vnet.region | |
} | |
else { | |
$deployedToSubnet = $false | |
$region = pick-one-from -thisList ( (Get-AzureLocation).DisplayName ) -BasedOn 'Azure region' | |
write-host "`nNotice that '$tag' is to be deployed to '$region' region." -f Green -b black | |
} | |
#------------------------------------------------------------------------- | |
# At this time, $myAzureSubscription has been set in Session Information. | |
#------------------------------------------------------------------------- | |
$deployment = @{ subscription = $myAzureSubscription; region = $region; storageName = $tag } | |
#------------------------------------------------------------ | |
# SAMPLE STRING PATTERNS | |
# 'windows server 2012 r2', 'sql server 2014', 'sharepoint', | |
# 'oracle', 'visual', 'ubuntu', 'jdk', 'coreos' | |
#------------------------------------------------------------ | |
$r2Image = @{name = get-ImageName 'windows server 2012 r2 datacenter' | |
; EstimatedTime = 8 } | |
$sqlImage = @{name = get-ImageName 'sql server 2014 rtm enterprise' | |
; EstimatedTime = 12 } | |
# VM Extension | |
$bginfo = @{item=(Get-AzureVMAvailableExtension | where {$_.ExtensionName -eq 'BGInfo' }); | |
EstimatedTime = 0 } | |
#----------------------------------------------------------------------------------- | |
# This source is for IT Camps in the spring of 2015 with the last evet on 20150630. | |
#----------------------------------------------------------------------------------- | |
$sourceDir = 'https://yctest.blob.core.windows.net/goodtill20150715' | |
#--------------------- | |
# VM Custom Extension | |
#--------------------- | |
$DatadiskLabel = 'datadisk' | |
$SetupIIS = @{FileUri="$sourceDir/ITCamp.fy15q4.IISProvisionScript.ps1"; | |
Run='ITCamp.fy15q4.IISProvisionScript.ps1'; | |
Argument = "$tag c:\_$ref\$tag $sourceDir"; | |
EstimatedTime = 1 | |
} | |
$SetupSQL = @{FileUri="$sourceDir/ITCamp.fy15q4.SQLProvisionScript.ps1"; | |
Run='ITCamp.fy15q4.SQLProvisionScript.ps1'; | |
Argument = "$tag c:\_$ref\$tag $DatadiskLabel $AdminUser $AdminPassword $sourceDir"; | |
EstimatedTime = 3 | |
} | |
#------------------ | |
# App Architecture | |
#------------------ | |
$frontend = @{ | |
name='web' | |
; vm=@{ | |
name = @('webfe01') | |
; image = $r2Image | |
; admin = @{id=$AdminUser; pwd=$AdminPassword} | |
; service = $tag | |
; size = 'Small' | |
; ep = ( @{name="$tag-http" ;protocol='tcp';public='80' ;private='80' ;LB="$tag-http" }, | |
@{name="$tag-https";protocol='tcp';public='443' ;private='443' ;LB="$tag-https"} ) | |
; as = "$tag-AS" | |
; vmExtension = @( $bginfo ) | |
; customExtension = @( $SetupIIS ) | |
} | |
} | |
$backend = @{ | |
name='data' | |
; vm=@{ | |
name = @('sql01') | |
; image = $sqlImage | |
; admin = @{id=$AdminUser; pwd=$AdminPassword} | |
; service = $tag | |
; size = 'Large' | |
; vmExtension = @( $bginfo ) | |
; customExtension = @( $SetupSQL ) | |
; datadisk = ( @{label=$DatadiskLabel;size='20'} ) | |
} | |
} | |
$appArchitecture = ($frontend, $backend) | |
#endregion | |
#region [DO NOT CHANGE - Standardized Azure VM Deployment Routine] | |
write-host "`n$(get-date -f T) - Creating the storage account ($($deployment.storageName))" -f green | |
Select-AzureSubscription -SubscriptionName $deployment.subscription -Current | |
New-AzureStorageAccount -StorageAccountName $deployment.storageName -Location $deployment.region | |
Set-AzureStorageAccount -StorageAccountName $deployment.storageName -Label $tag -Type Standard_LRS | |
Set-AzureSubscription -SubscriptionName $deployment.subscription -CurrentStorageAccountName $deployment.storageName | |
write-host "`n$(get-date -f T) - Created the storage account ($($deployment.storageName))" -f green | |
#------------------------------------------ | |
# Estimating time needed for VM deployment | |
#------------------------------------------ | |
$totalVM = 0 ; $EstimatedDeploymentTime = 0 | |
foreach ($one in $appArchitecture) { | |
$totalVM = $totalVM + $one.vm.name.count | |
foreach ($n in $one.vm.name) { | |
$EstimatedDeploymentTime = $EstimatedDeploymentTime + $one.vm.image.EstimatedTime | |
if ($one.vm.vmExtension) { | |
foreach ($vmex in $one.vm.vmExtension) { | |
$EstimatedDeploymentTime = $EstimatedDeploymentTime + $vmex.EstimatedTime | |
} | |
} | |
if ($one.vm.customExtension) { | |
foreach ($cuex in $one.vm.customExtension) { | |
$EstimatedDeploymentTime = $EstimatedDeploymentTime + $cuex.EstimatedTime | |
} | |
} | |
} | |
} | |
write-host "`n$(get-date -f T) - Estimated $($EstimatedDeploymentTime) minutes to deploy $tag with $totalVM VM(s)" -f black -b yellow | |
#---------------------------- | |
# Deploying app architecture | |
#---------------------------- | |
$deployedService = @(); $deployedVM = @() | |
foreach ($tier in $appArchitecture) { | |
write-host "`n*****************************" -f green -b black | |
write-host "`tBuilding"($tier.name)'Tier' -f green | |
write-host '*****************************' -f green -b black | |
foreach ($vmName in $tier.vm.name) { | |
$thisVM = @{} | |
if ( !(Get-AzureService $tier.vm.service) ) { | |
write-host "$(get-date -f T) - Here, the above message is expected since the target service ($($tier.vm.service)) does not exist at this time. " -f green -b black | |
New-AzureService -ServiceName $tier.vm.service -Location $deployment.region | |
write-host "`n$(get-date -f T) - Created the service ($($tier.vm.service))" -f green | |
$deployedService = $deployedService + $tier.vm.service | |
} | |
#--------------------------------------- | |
# Estimated time for this VM deployment | |
#--------------------------------------- | |
$vmExtensionTime = 0 | |
foreach ($vmExtension in $tier.vm.vmExtension) { | |
$vmExtensionTime = $vmExtensionTime + $vmExtension.EstimatedTime | |
} | |
$custExtensionTime = 0 | |
foreach ($custExtension in $tier.vm.customExtension) { | |
$custExtensionTime = $ucstExtensionTime + $custExtension.EstimatedTime | |
} | |
$vmTime = $tier.vm.image.EstimatedTime + $vmExtensionTime + $custExtensionTime | |
write-host "`nThis step takes about $($vmTime) minutes." -f black -b yellow | |
#------------------------- | |
# This VM vonfiguration | |
#------------------------- | |
write-host "`n$(get-date -f T) - Configured this VM ($($vmName)) for this service ($($tier.vm.service))" -f green | |
$vmConfig = | |
New-AzureVMConfig ` | |
-ImageName $tier.vm.image.name ` | |
-InstanceSize $tier.vm.size ` | |
-Name $vmName ` | |
-DiskLabel "OS" ` | |
| Add-AzureProvisioningConfig ` | |
-Windows ` | |
-DisableAutomaticUpdates ` | |
-AdminUserName $tier.vm.admin.id ` | |
-Password $tier.vm.admin.pwd ` | |
if ($tier.vm.datadisk) { | |
$i=0 | |
foreach ($disk in $tier.vm.datadisk) { | |
$vmConfig = $vmConfig ` | |
| Add-AzureDataDisk ` | |
-CreateNew ` | |
-DiskSizeInGB $disk.size ` | |
-DiskLabel $disk.label ` | |
-LUN $i | |
write-host "`nWith LUN $i" -f green | |
$i++ | |
} | |
} | |
if ($tier.vm.ep) { | |
foreach ($ep in $tier.vm.ep) { | |
if ($ep.LB) { | |
$vmConfig = $vmConfig ` | |
| Add-AzureEndpoint ` | |
-Name $ep.name ` | |
-Protocol $ep.protocol ` | |
-LocalPort $ep.private ` | |
-PublicPort $ep.public ` | |
-LBSetName $ep.LB ` | |
-DefaultProbe | |
write-host "`nWith the endpoint ($($ep.name)) and this load-balance set ($($ep.LB))" -f green | |
} | |
else { | |
$vmConfig = $vmConfig ` | |
| Add-AzureEndpoint ` | |
-Name $ep.name ` | |
-Protocol $ep.protocol ` | |
-LocalPort $ep.private ` | |
-PublicPort $ep.public | |
write-host "`nWith this endpoint ($($ep.name))" -f green | |
} | |
} | |
} | |
if ($tier.vm.as) { | |
$vmConfig = $vmConfig ` | |
| Set-AzureAvailabilitySet -AvailabilitySetName $tier.vm.as | |
write-host "`nWith this availability set ($($tier.vm.as))" -f green | |
} | |
if ($tier.vm.vmExtension) { | |
foreach ($agent in $tier.vm.vmExtension) { | |
$vmConfig = $vmConfig ` | |
| Set-AzureVMExtension ` | |
-ExtensionName $agent.item.ExtensionName` | |
-Publisher $agent.item.Publisher ` | |
-version $agent.item.Version | |
write-host "`nWith this VM Extension ($($agent.item.ExtensionName))" -f green | |
} | |
} | |
#------------------------- | |
# This VM deployment | |
#------------------------- | |
if ($deployedToSubnet) { | |
write-host "`n$(get-date -f T) - Deploying this VM ($($vmName)) to:`n`n`tthis service ($($tier.vm.service)), this vnet ($($vnet.site)), and this subnet ($($vnet.subnet))" -f green | |
$vmConfig = $vmConfig ` | |
| Set-AzureSubnet ` | |
-SubnetNames $vnet.subnet ` | |
| New-AzureVM ` | |
-ServiceName $tier.vm.service ` | |
-VNetName $vnet.site ` | |
-WaitForBoot | |
} | |
else { | |
write-host "`n$(get-date -f T) - Deploying this VM ($($vmName)) to this service ($($tier.vm.service))" -f green | |
$vmConfig ` | |
| New-AzureVM ` | |
-ServiceName $tier.vm.service ` | |
-WaitForBoot | |
} | |
$thisVM = @{name=$vmName;service=$tier.vm.service} | |
#------------------------------------------ | |
# Applying custom extension, as applicable | |
#------------------------------------------ | |
if ($tier.vm.customExtension) { | |
$thisVM = $thisVM + @{customExtension = $true} | |
foreach ($one in $tier.vm.customExtension) { | |
if ($one.Argument) { | |
write-host " | |
$(get-date -f T) - Deploying this VM ($($vmName)) with this custom extension: | |
`t$($one.FileUri) | |
`twith this argument list: | |
`t$($one.Argument)" -f green -b black | |
Get-AzureVM -Name $vmName -ServiceName $tier.vm.service ` | |
| Set-AzureVMCustomScriptExtension ` | |
-FileUri $one.FileUri ` | |
-Run $one.Run ` | |
-Argument $one.Argument ` | |
| Update-AzureVM | |
} | |
else { | |
write-host " | |
$(get-date -f T) - Deploying this VM ($($vmName)) with this custom extension | |
`t$($one.FileUri)" -f green | |
Get-AzureVM -Name $vmName -ServiceName $tier.vm.service ` | |
| Set-AzureVMCustomScriptExtension ` | |
-FileUri $one.FileUri ` | |
-Run $one.Run ` | |
| Update-AzureVM | |
} | |
} | |
} | |
#------------------------------------------ | |
# Placing VM RDP files for later access | |
#------------------------------------------ | |
Get-AzureRemoteDesktopFile ` | |
-ServiceName $tier.vm.service ` | |
-Name $vmName ` | |
-LocalPath "$sessionFolder\$tag-$vmName.rdp" | |
$deployedVM = $deployedVm + $thisVM | |
write-host "`n$(get-date -f T) - The RDP file of this VM ($($vmName)) has been downloaded at `n $sessionFolder\$tag-$vmName.rdp" -f green | |
} | |
} | |
write-host "`nThe folder, $sessionFolder, has the following downloaded RDP files:`n"($downloadedRDPFiles = dir $sessionFolder) -f green | |
write-host "`n$(get-date -f T) - Deployment of $tag with $totalVM VM(s) ended." -f black -b yellow | |
write-host "`nNotice it may still take a few more minutes for the VMs to transition `nand finish executing included extension, as applicable." -f green | |
#---------------------------------------- | |
# Wait till all custom extensions finish | |
#---------------------------------------- | |
foreach ($vm in $deployedVM) { | |
do { | |
$CustomExtensionStatus = ((get-azurevm -Name $vm.name -ServiceName $vm.service).ResourceExtensionStatusList ` | |
| where {$_.HandlerName -eq 'Microsoft.Compute.CustomScriptExtension'}).ExtensionSettingStatus.Status | |
if ($CustomExtensionStatus -ne 'Success') { | |
write-host "`nThe custom extension of $($vm.name) has a status as '$CustomExtensionStatus', rechecking in 30 seconds..." -f green | |
Start-Sleep 30 | |
} | |
} | |
until ($CustomExtensionStatus -eq 'Success') | |
} | |
write-host "`nAll deployed custom extensions now have a status as '$CustomExtensionStatus.'" -f green | |
write-host "`nA test page, ITCamp.aspx, has been created by the custom extension, | |
`t$($SetupIIS.FileUri)" -f green | |
open-IE-with -thisURL (($frontend.vm.service)+'.cloudapp.net/itcamp.aspx'); start-sleep 15 | |
#endregion | |
#region [DO NOT CHANGE - Reconfigure SQL as needed] | |
function import-VMWinRmCert-from { | |
#----------------------------------------------------- | |
# [Reference] | |
# Originally by Ian Farr | |
# Build an Active Directory Forest in Microaost Azure | |
# http://blogs.technet.com/b/poshchap/archive/2015/01/30/new-amp-improved-formula-build-an-ad-ds-forest-in-azure.aspx | |
#----------------------------------------------------- | |
Param( | |
[parameter(Mandatory,Position=1)] | |
[ValidateNotNullOrEmpty()] | |
[String]$thisService, | |
[parameter(Mandatory,Position=2)] | |
[ValidateNotNullOrEmpty()] | |
[String]$thisVM, | |
[parameter(Mandatory,Position=3)] | |
[ValidateNotNullOrEmpty()] | |
[string]$savedAt | |
) | |
#Troubleshooting messages | |
Write-Verbose "$(get-date -f T) - Obtaining thumbprint of WinRM cert for $thisVM" | |
Write-Debug "About to obtain thumbprint of WinRM cert for $thisVM" | |
$WinRMCert = (Get-AzureVM -ServiceName $thisService -Name $thisVM).VM.DefaultWinRMCertificateThumbprint | |
If ($WinRMCert) { | |
#Troubleshooting messages | |
Write-Verbose "$(get-date -f T) - Saving $thisService Azure certificate data to cer file" | |
Write-Debug "About to save $thisService Azure certificate data to cer file" | |
#Get a certificare object for the VM's service and save it's data to a .cer file | |
(Get-AzureCertificate -ServiceName $thisService -Thumbprint $WinRMCert -ThumbprintAlgorithm sha1).Data ` | |
| Out-File "$savedAt\$thisService.cer" | |
#Error handling | |
If ($?) { | |
#Troubleshooting message | |
Write-Verbose "$(get-date -f T) - $thisService Azure certificate exported to cer file" | |
Write-Verbose "$(get-date -f T) - Importing $thisService Azure certificate to Cert:\localmachine\root" | |
Write-Debug "About to import $thisService Azure certificate to Cert:\localmachine\root" | |
#Import the certifcate into the local computer's root store | |
Import-Certificate ` | |
-FilePath "$savedAt\$thisService.cer" ` | |
-CertStoreLocation "Cert:\localmachine\root" ` | |
-ErrorAction SilentlyContinue | | |
Out-Null | |
#Error handling | |
If ($?) { | |
#Troubleshooting message | |
Write-Verbose "$(get-date -f T) - $thisService Azure certificate imported to local computer root store" | |
} #End of If ($?) | |
Else { | |
#Write Error | |
Write-Error "Unable to import certificate to local computer root store - script remoting won't be possible for $thisService" | |
} #End of | |
} | |
Else { | |
#Write Error | |
Write-Error "Unable to export certificate to cer file - script remoting won't be possible for $thisService" | |
} | |
} | |
Else { | |
#Write Error | |
Write-Error "Unable to obtain WinRM certificate thumbprint - script remoting won't be possible for $thisService" | |
} | |
} | |
function Remotely-Configure-SQL-in { | |
param ( | |
[string]$thisVMname, [string]$thisServiceName, [string]$sessionFolder, | |
[string]$myVMAdminUserName, [string]$myVMAdminPassword | |
) | |
import-VMWinRmCert-from -thisVM $thisVMname -thisService $thisServiceName -savedAt $sessionFolder | |
$SecurePassword = ($myVMAdminPassword | ConvertTo-SecureString -AsPlainText -Force) | |
$thisPSSession = ` | |
New-PSSession ` | |
-ConnectionUri (Get-AzureWinRMUri -ServiceName $thisServiceName -Name $thisVMname) ` | |
-Credential (New-Object System.Management.Automation.PSCredential($myVMAdminUserName, $SecurePassword)) ` | |
-Name "PS-to-$thisVMname" | |
write-host "`n$(get-date -f T) - PS Remote session ,PS-to-VM:$thisVMname started..." -f yellow -b black | |
$configureSQL = ` | |
Invoke-Command ` | |
-Session $thisPSSession ` | |
-ArgumentList $myVMAdminUserName, $myVMAdminPassword ` | |
-ScriptBlock { | |
param ([string]$thisUserName,[string]$thisPassword) | |
#-------------------- | |
# SQL Configuration | |
#-------------------- | |
# Mixed Mode Authentication | |
Invoke-Sqlcmd -Query "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2" | |
# Enable sa | |
Invoke-Sqlcmd -Query "ALTER LOGIN sa ENABLE" | |
Invoke-Sqlcmd -Query ("ALTER LOGIN sa WITH PASSWORD = '$thisPassword'") | |
# SQL Default Directories in Registries | |
Invoke-Sqlcmd -Query "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'F:\MSSQL\Data'" | |
Invoke-Sqlcmd -Query "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'F:\MSSQL\Logs'" | |
Invoke-Sqlcmd -Query "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N'F:\MSSQL\Backup'" | |
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" | |
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($env:ComputerName) | |
$server.Properties["BackupDirectory"].Value = "F:\MSSQL\Backup" | |
$server.Properties["DefaultFile"].Value = "F:\MSSQL\Data" | |
$server.Properties["DefaultLog"].Value = "F:\MSSQL\Logs" | |
$server.Alter() | |
# Attach Sample Database | |
$testDB = 'Test' | |
Invoke-Sqlcmd -Query "Create Database $testDB on (filename = 'F:\MSSQL\Data\adventureworks_data.mdf'), (filename = 'F:\MSSQL\Data\adventureworks_log.ldf') for attach" | |
Invoke-Sqlcmd -Query "Use Master; CREATE LOGIN [DataManagementApp] WITH PASSWORD=N'$thisPassword', DEFAULT_DATABASE=[$testDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF" | |
Invoke-Sqlcmd -Query "USE [$testDB]; CREATE USER [DataManagementApp] FOR LOGIN [DataManagementApp]" | |
Invoke-Sqlcmd -Query "USE [$testDB]; ALTER ROLE [db_datareader] ADD MEMBER [DataManagementApp]" | |
Invoke-Sqlcmd -Query "USE [$testDB]; ALTER ROLE [db_owner] ADD MEMBER [DataManagementApp]" | |
<# | |
$MyNewUserName = "$env:ComputerName\$thisUserName" | |
Invoke-Sqlcmd -Query ("USE [master]; CREATE LOGIN [$MyNewUserName] FROM WINDOWS WITH DEFAULT_DATABASE=[$testDB]") | |
Invoke-Sqlcmd -Query ("ALTER SERVER ROLE [securityadmin] ADD MEMBER [$MyNewUserName]") | |
Invoke-Sqlcmd -Query ("ALTER SERVER ROLE [serveradmin] ADD MEMBER [$MyNewUserName]") | |
Invoke-Sqlcmd -Query ("ALTER SERVER ROLE [setupadmin] ADD MEMBER [$MyNewUserName]") | |
Invoke-Sqlcmd -Query ("ALTER SERVER ROLE [sysadmin] ADD MEMBER [$MyNewUserName]") | |
Invoke-Sqlcmd -Query ("USE [$testDB] ; CREATE USER [$MyNewUserName] FOR LOGIN [$MyNewUserName]") | |
#> | |
Restart-Service -Force MSSQLSERVER | |
$SQL = Get-Service | where-Object{$_.name -eq 'MSSQLSERVER'} | |
while ($SQL.Status -ne 'Running'){ Start-Sleep 10 } | |
} | |
Get-PSSession | Remove-PSSession -ErrorAction SilentlyContinue | |
write-host "`n$(get-date -f T) - PS Remote session ,PS-to-VM:$thisVMname ended..." -f yellow -b black | |
} | |
open-IE-with -thisURL (($frontend.vm.service)+'.cloudapp.net'); start-sleep 15 | |
<# | |
$revalidate = read-host " | |
Go to the IE session of the Data Management application, | |
scroll down, and log in with 12345, and examine data. | |
If the IE session is not able to return data, enter 'y' to start a PS remote session to | |
revalidate the configuration followed by restarting SQL, then re-examine the data again (y/n)" | |
#> | |
$revalidate = 'y' # Change it to always validate automatically | |
if ($revalidate.ToLower() -eq 'y') { | |
foreach ($box in $backend.vm.name) { | |
Remotely-Configure-SQL-in ` | |
-thisVMname $box ` | |
-thisServiceName $backend.vm.service ` | |
-sessionFolder $sessionFolder ` | |
-myVMAdminUserName $backend.vm.admin.id ` | |
-myVMAdminPassword $backend.vm.admin.pwd | |
} | |
} | |
#endregion | |
#region [DO NOT CHANGE - Clean-Up Routine] | |
$action = '?' | |
while ( $action.ToLower() -notin ('y', 'n') ) { | |
$action = read-host "`nPress 'y' to delete the following then exist, or 'n' to simply exit`n`n`tService(s):`t`t$deployedService`n`tStorage:`t`t$($deployment.storageName)`n`tLocal Folder:`t$sessionFolder`n`n(y/n)" | |
} | |
if ($action.ToLower() -eq 'y') { | |
clean-up ` | |
-theseServices $deployedService ` | |
-thisStorageAccount $deployment.storageName ` | |
-thisFolder $sessionFolder | |
} | |
else { | |
write-host "`nThe deployed resoruces of $tag including the following are not deleted:`n`n`tService(s):`t`t$deployedService`n`tStorage:`t`t"$($deployment.storageName)"`n`tLocal Folder:`t$sessionFolder" -f green | |
} | |
#> | |
#endregion |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment