SQL Server | Dutch Data Dude

Automatically building a Microsoft BI machine using PowerShell – Installing System Center Endpoint Protection (post #9)

on December 15, 2015

This post is #9 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy

Although in the last step we configured a very permissive password policy we need a bit of security, so that is why I opted to install System Center Endpoint Protection. Now, in Azure you can also have extensions for security (both with Microsoft and 3^rd party security products) so probably you will never install System Center Endpoint protection yourself, but for the sake of reference, here is how to install it using PowerShell.

Function InstallSystemCenterEndpointProtection
{
    Write-Log -Verbose  "Step 4: Install System Center Endpoint Protection"
    Start-Process .\Resources\SystemCenterEndpointProtection\scepinstall.exe -Wait -ArgumentList "/s /q" #-NoNewWindow
    Write-Log -Verbose  "System Center Endpoint Protection Installed"
    if ($global:DoAllTasks) {
        Set-Restart-AndResume $global:script "6"
        }
}

Function InstallSystemCenterEndpointProtection

{

Write-Log -Verbose "Step 4: Install System Center Endpoint Protection"

Start-Process .\Resources\SystemCenterEndpointProtection\scepinstall.exe -Wait -ArgumentList "/s /q" #-NoNewWindow

Write-Log -Verbose "System Center Endpoint Protection Installed"

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "6"

}

Next step: installing SQL Server

Automatically building a Microsoft BI machine using PowerShell – Password policy (post #8)

on December 8, 2015

by Jeroen ter Heerdt 0 Comments

This post is #8 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

In this step we will configure a very permissive password policy. This of course requires that the previous step (setting up Active Directory) has successfully completed. The password policy set using this script is only suitable for demo environments since it is very, very (did I say very?) permissive; it sets a minimal password length of 0, does not record any history of passwords (you can re-use your password again and again), passwords never expire and do not have to follow complexity rules. So, even an empty password is allowed (although not recommended since your Windows services will then not start). However, having ‘1234’ as password would work perfectly under this policy (and no, this is not the password I use for my demo machines).

Function ConfigurePasswordPolicy {
    Param(
        [Parameter(Mandatory=$true,HelpMessage="Domain name required, please specify in format yyy.zzz")]
        [ValidateNotNullOrEmpty()]
        $DomainName
    )
    Write-Log -Verbose  "Step 3: Configure Password Policy"
   try {
    Set-ADDefaultDomainPasswordPolicy -Identity $DomainName -MinPasswordLength:0 -PasswordHistoryCount:0 -MaxPasswordAge:0 -MinPasswordAge:0 -ComplexityEnabled:$false
    Write-Log -Verbose  "Password Policy Configured"
    if ($global:DoAllTasks) {
        Set-Restart-AndResume $global:script "5"
    }
    }
    catch {
    Write-Log -Verbose  "Failed to configure Password Policy. Error: $_.Exception.Message"
    }
}

Function ConfigurePasswordPolicy {

Param(

[Parameter(Mandatory=$true,HelpMessage="Domain name required, please specify in format yyy.zzz")]

[ValidateNotNullOrEmpty()]

$DomainName

)

Write-Log -Verbose "Step 3: Configure Password Policy"

try {

Set-ADDefaultDomainPasswordPolicy -Identity $DomainName -MinPasswordLength:0 -PasswordHistoryCount:0 -MaxPasswordAge:0 -MinPasswordAge:0 -ComplexityEnabled:$false

Write-Log -Verbose "Password Policy Configured"

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "5"

}

catch {

Write-Log -Verbose "Failed to configure Password Policy. Error: $_.Exception.Message"

}

Next step: installing System Center Endpoint protection

Automatically building a Microsoft BI machine using PowerShell – Active Directory Setup (post #7)

on December 1, 2015

by Jeroen ter Heerdt 3 Comments

This post is #7 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

In this step we will set up Active Directory. This script has been inspired on http://blogs.technet.com/b/ashleymcglone/archive/2013/04/18/touch-free-powershell-dcpromo-in-windows-server-2012.aspx.

#Set up Active Directory
#source: http://blogs.technet.com/b/ashleymcglone/archive/2013/04/18/touch-free-powershell-dcpromo-in-windows-server-2012.aspx
Function SetupActiveDirectory {
    Param(
        [Parameter(Mandatory=$true,HelpMessage="Domain name required, please specify in format yyy.zzz")]
        [ValidateNotNullOrEmpty()]
        $DomainName
    )
    Write-Log -Verbose  "Step 2: Set up Active Directory"
    
    try {
        Install-WindowsFeature -Name AD-Domain-Services -IncludeManagementTools
        if ($global:DoAllTasks) {
            Set-Restart-AndResume $global:script "3"
        }
    }
    catch {
        Write-Log -Verbose  "Failed to set up Active Directory. Error: $_.Exception.Message"
    }
}
Function SetupActiveDirectoryPart2 {
    Param(
        [Parameter(Mandatory=$true,HelpMessage="Domain name required, please specify in format yyy.zzz")]
        [ValidateNotNullOrEmpty()]
        $DomainName
    )
    Write-Log -Verbose  "Step 2: Set up Active Directory"
    
    try {
        Import-Module ADDSDeployment
        $dotposition = $DomainName.LastIndexOf('.')
        $netbiosname = $DomainName.Substring(0,$dotposition)
        $result = Install-ADDSForest -DomainName $DomainName -InstallDNS:$true -Confirm:$false -NoRebootOnCompletion:$true -Force:$true -DatabasePath "C:\Windows\NTDS" -DomainMode Win2012R2 -ForestMode Win2012R2 -LogPath "C:\Windows\NTDS" -SysvolPath "C:\Windows\SYSVOL" -DomainNetbiosName $netbiosname
        Write-Log -Verbose  "Active Directory set up done"
        if ($global:DoAllTasks) {
            Set-Restart-AndResume $global:script "4"
        }
    }
    catch {
        Write-Log -Verbose  "Failed to set up Active Directory. Error: $_.Exception.Message"
    }
}

#Set up Active Directory

#source: http://blogs.technet.com/b/ashleymcglone/archive/2013/04/18/touch-free-powershell-dcpromo-in-windows-server-2012.aspx

Function SetupActiveDirectory {

Param(

[Parameter(Mandatory=$true,HelpMessage="Domain name required, please specify in format yyy.zzz")]

[ValidateNotNullOrEmpty()]

$DomainName

)

Write-Log -Verbose "Step 2: Set up Active Directory"

try {

Install-WindowsFeature -Name AD-Domain-Services -IncludeManagementTools

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "3"

}

catch {

Write-Log -Verbose "Failed to set up Active Directory. Error: $_.Exception.Message"

}

Function SetupActiveDirectoryPart2 {

Param(

[Parameter(Mandatory=$true,HelpMessage="Domain name required, please specify in format yyy.zzz")]

[ValidateNotNullOrEmpty()]

$DomainName

)

Write-Log -Verbose "Step 2: Set up Active Directory"

try {

Import-Module ADDSDeployment

$dotposition = $DomainName.LastIndexOf('.')

$netbiosname = $DomainName.Substring(0,$dotposition)

$result = Install-ADDSForest -DomainName $DomainName -InstallDNS:$true -Confirm:$false -NoRebootOnCompletion:$true -Force:$true -DatabasePath "C:\Windows\NTDS" -DomainMode Win2012R2 -ForestMode Win2012R2 -LogPath "C:\Windows\NTDS" -SysvolPath "C:\Windows\SYSVOL" -DomainNetbiosName $netbiosname

Write-Log -Verbose "Active Directory set up done"

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "4"

}

catch {

Write-Log -Verbose "Failed to set up Active Directory. Error: $_.Exception.Message"

}

Next step: configuring a very permissive password policy.

Automatically building a Microsoft BI machine using PowerShell – Disabling Internet Explorer Enhanced Security Configuration (post #6)

on November 24, 2015

by Jeroen ter Heerdt 2 Comments

This post is #6 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

In this step we will disable the Internet Explorer Enhanced Security Configuration. In general IEESC is a great idea, but on demo machines it is not very useful and makes the demo less usable. This script comes from http://itproctology.blogspot.nl/2013/09/powershell-to-disable-ie-enhanced.html:

#Disables Internet Explorer Enhanced Security Configuration
#source: http://itproctology.blogspot.nl/2013/09/powershell-to-disable-ie-enhanced.html
Function DisableIEESC {
    Write-Log -Verbose "Step 1: Disable Internet Explorer Enhanced Security"
    try {
        $AdminKey = “HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A7-37EF-4b3f-8CFC-4F3A74704073}”
        $UserKey = “HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A8-37EF-4b3f-8CFC-4F3A74704073}”
        Set-ItemProperty -Path $AdminKey -Name “IsInstalled” -Value 0
        Set-ItemProperty -Path $UserKey -Name “IsInstalled” -Value 0
        Stop-Process -Name Explorer
        Write-Log -Verbose  "IE ESC succesfully disabled"
        if ($global:DoAllTasks) {
            Set-Restart-AndResume $global:script "2"
        }
    }
    catch {
        Write-Log -Verbose  "Failed to disable IE ESC. Error: $_.Exception.Message"
    }
}

#Disables Internet Explorer Enhanced Security Configuration

#source: http://itproctology.blogspot.nl/2013/09/powershell-to-disable-ie-enhanced.html

Function DisableIEESC {

Write-Log -Verbose "Step 1: Disable Internet Explorer Enhanced Security"

try {

$AdminKey = “HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A7-37EF-4b3f-8CFC-4F3A74704073}”

$UserKey = “HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A8-37EF-4b3f-8CFC-4F3A74704073}”

Set-ItemProperty -Path $AdminKey -Name “IsInstalled” -Value 0

Set-ItemProperty -Path $UserKey -Name “IsInstalled” -Value 0

Stop-Process -Name Explorer

Write-Log -Verbose "IE ESC succesfully disabled"

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "2"

}

catch {

Write-Log -Verbose "Failed to disable IE ESC. Error: $_.Exception.Message"

}

Next step: set up Active Directory.

Automatically building a Microsoft BI machine using PowerShell – master script (post #5)

on November 17, 2015

by Jeroen ter Heerdt 0 Comments

This post is #5 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Now that we have our preparation completed, it is time to present the master script. This script will be called by the user with parameters specifying what to install; also this script will call other scripts to install components and potentially reboot the machine and resume working. My master script is called ‘SetupMSBIDemoMachine.ps1’. It has one master switch called -DoAllTasks, what does as it says. Also, it provides switches to just executed a part of the total install, such as just installing SQL Server by specifying –InstallSQLServer. Optionally, this script can do automatic reboots of the server and auto-resume working after the reboot; very useful when –DoAllTasks is specified.

A sample call that would complete the full install with a certain domainname and passphrase (for SharePoint) and also auto reboots the machine would look like this:

.\SetupMSBIDemoMachine -DoAllTasks -DomainName mydomain.local -passphrase pass@word1 -AutoReboot

1	.\SetupMSBIDemoMachine -DoAllTasks -DomainName mydomain.local -passphrase pass@word1 -AutoReboot

Just running .\SetupMSBIDemoMachine -? returns the following info, which shows all the parameters available. The parameters map to the steps outline in the start of this series. Again, -DoAllTasks would mean just executing these steps in turn.

NAME
    C:\Users\jterh\OneDrive - Microsoft\Demo Machine\SetupMSBIDemoMachine.ps1
    
SYNOPSIS
    Installs and sets up a MSBI Demo Machine in a number of steps
    
    
SYNTAX
    C:\Users\jterh\OneDrive - Microsoft\Demo Machine\SetupMSBIDemoMachine.ps1 [-DisableIEESC] 
    [-SetupActiveDirectory] [[-DomainName] ] [-ConfigurePasswordPolicy] 
    [-InstallSystemCenterEndpointProtection] [-InstallSQLServer] [-InstallSharePoint] 
    [-InstallPowerPivot] [-ConfigurePowerPivot] [-ConfigurePowerPivotPart2] [[-passphrase] ] 
    [-DoAllTasks] [[-Password] <String>] [[-Step] <String>] [-AutoReboot] [<CommonParameters>]
    
    
DESCRIPTION
    

RELATED LINKS

REMARKS
    To see the examples, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo 
    Machine\SetupMSBIDemoMachine.ps1 -examples".
    For more information, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo 
    Machine\SetupMSBIDemoMachine.ps1 -detailed".
    For technical information, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo 
    Machine\SetupMSBIDemoMachine.ps1 -full".

NAME

C:\Users\jterh\OneDrive - Microsoft\Demo Machine\SetupMSBIDemoMachine.ps1

SYNOPSIS

Installs and sets up a MSBI Demo Machine in a number of steps

SYNTAX

C:\Users\jterh\OneDrive - Microsoft\Demo Machine\SetupMSBIDemoMachine.ps1 [-DisableIEESC]

[-SetupActiveDirectory] [[-DomainName] ] [-ConfigurePasswordPolicy]

[-InstallSystemCenterEndpointProtection] [-InstallSQLServer] [-InstallSharePoint]

[-InstallPowerPivot] [-ConfigurePowerPivot] [-ConfigurePowerPivotPart2] [[-passphrase] ]

[-DoAllTasks] [[-Password] <String>] [[-Step] <String>] [-AutoReboot] [<CommonParameters>]

DESCRIPTION

RELATED LINKS

REMARKS

To see the examples, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo

Machine\SetupMSBIDemoMachine.ps1 -examples".

For more information, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo

Machine\SetupMSBIDemoMachine.ps1 -detailed".

For technical information, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo

Machine\SetupMSBIDemoMachine.ps1 -full".

Part 1: Parameter binding

[CmdletBinding()]
Param(
[switch]$DisableIEESC,
[switch]$SetupActiveDirectory,
[string]$DomainName,
[switch]$ConfigurePasswordPolicy,
[switch]$InstallSystemCenterEndpointProtection,
[switch]$InstallSQLServer,
[switch]$InstallSharePoint,
[switch]$InstallPowerPivot,
[switch]$ConfigurePowerPivot,
[switch]$ConfigurePowerPivotPart2,
[string]$passphrase,
[switch]$DoAllTasks,
[string]$Password="pass@word1",
[string]$Step="1",
[switch]$AutoReboot=$false
)

[CmdletBinding()]

Param(

[switch]$DisableIEESC,

[switch]$SetupActiveDirectory,

[string]$DomainName,

[switch]$ConfigurePasswordPolicy,

[switch]$InstallSystemCenterEndpointProtection,

[switch]$InstallSQLServer,

[switch]$InstallSharePoint,

[switch]$InstallPowerPivot,

[switch]$ConfigurePowerPivot,

[switch]$ConfigurePowerPivotPart2,

[string]$passphrase,

[switch]$DoAllTasks,

[string]$Password="pass@word1",

[string]$Step="1",

[switch]$AutoReboot=$false

)

This part of the script binds to the parameters and specifies defaults for the password to be used for service accounts and the internal $Step variable. Also, note that by default AutoReboot is disabled.

Part 2: Imports

# -------------------------------------
# Imports
# -------------------------------------
$global:script = $myInvocation.MyCommand.Definition
$scriptPath = Split-Path -parent $global:script
. (Join-Path $scriptpath RestartAndResumeFunctions.ps1)
. (Join-Path $scriptpath DisableIEESC.ps1)
. (Join-Path $scriptPath Set-Restart-AndResume.ps1)
. (Join-Path $scriptPath SetupActiveDirectory.ps1)
. (Join-Path $scriptPath ConfigurePasswordPolicy.ps1)
. (Join-Path $scriptPath InstallSystemCenterEndpointProtection.ps1)
. (Join-Path $scriptPath CreateServiceAccount.ps1)
. (Join-Path $scriptPath InstallSQLServer.ps1)
. (Join-Path $scriptPath InstallSharePoint.ps1)
. (Join-Path $scriptPath InstallPowerPivot.ps1)
. (Join-Path $scriptPath ConfigurePowerPivot.ps1)

# -------------------------------------

# Imports

# -------------------------------------

$global:script = $myInvocation.MyCommand.Definition

$scriptPath = Split-Path -parent $global:script

. (Join-Path $scriptpath RestartAndResumeFunctions.ps1)

. (Join-Path $scriptpath DisableIEESC.ps1)

. (Join-Path $scriptPath Set-Restart-AndResume.ps1)

. (Join-Path $scriptPath SetupActiveDirectory.ps1)

. (Join-Path $scriptPath ConfigurePasswordPolicy.ps1)

. (Join-Path $scriptPath InstallSystemCenterEndpointProtection.ps1)

. (Join-Path $scriptPath CreateServiceAccount.ps1)

. (Join-Path $scriptPath InstallSQLServer.ps1)

. (Join-Path $scriptPath InstallSharePoint.ps1)

. (Join-Path $scriptPath InstallPowerPivot.ps1)

. (Join-Path $scriptPath ConfigurePowerPivot.ps1)

This part join-paths to make sure we have all the items we need; the script uses restart and resume functions as an include, these functions enable auto restart and resume of the tasks (available in RestartAndResumeFunctions.ps1). The other scripts included here are the scripts that actually do the work of installing and configuring services.

Part 3: Parameter passing

$global:DoAllTasks = $DoAllTasks
$global:AutoReboot = $AutoReboot
Set-Location $scriptPath

#get the passed parameters
$Myparameters = $myinvocation.BoundParameters
#remove step from the list
$Myparameters.Remove("Step")
#build parameter string
$global:line = ""
foreach ($key in $Myparameters.keys)
{
    $value = (get-variable $key).Value 
    #is this a switch
    if($value -eq $true) {
        $global:line+= " -"+$key
    }
    else
    {
        $global:line+=" -"+$key+" "+$value
    }
}

$global:DoAllTasks = $DoAllTasks

$global:AutoReboot = $AutoReboot

Set-Location $scriptPath

#get the passed parameters

$Myparameters = $myinvocation.BoundParameters

#remove step from the list

$Myparameters.Remove("Step")

#build parameter string

$global:line = ""

foreach ($key in $Myparameters.keys)

{

$value = (get-variable $key).Value

#is this a switch

if($value -eq $true) {

$global:line+= " -"+$key

}

else

{

$global:line+=" -"+$key+" "+$value

}

This part is used to pass parameters between the master script and downstream scripts, even after auto reboot.

Part 4: Setting global variables

#Set the hostname
$global:HostName = hostname
$global:HostNameFull = $HostName
$global:HostNameFull += ".cloudapp.net"
$global:httpHostName = "http://"
$global:httpHostName += $HostName
#Set current user name
$global:currentUserName = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name;
#Path to SQL ISO
$global:pathToSQLISO = ".\Resources\SQLServer2014DeveloperEdition\en_sql_server_2014_developer_edition_x64_dvd_3940406.iso"
$global:pathToSQLISO = Resolve-Path $global:pathToSQLISO
#Path to SHarePoint ISO
$global:pathToSharePointISO = ".\Resources\SharePoint2013\en_sharepoint_server_2013_with_sp1_x64_dvd_3823428.iso"
$global:pathToSharePointISO = Resolve-Path $global:pathToSharePointISO
#Path to SharePoint Prerequisites
$global:SharePoint2013Path = ".\Resources\SharePoint2013"
$global:SharePoint2013Path = Resolve-Path $global:SharePoint2013Path
#Domain Vars
#$global:path = "CN=Managed Service Accounts,"
$global:path = "CN=Users,"
$global:root = [ADSI]''
$global:dn = $global:root.distinguishedName
$global:path += $global:dn
$global:domainpart = (gwmi Win32_NTDomain).DomainName
#SPFarm Account Name
$global:spAccount = "SPFarm"

#Set the hostname

$global:HostName = hostname

$global:HostNameFull = $HostName

$global:HostNameFull += ".cloudapp.net"

$global:httpHostName = "http://"

$global:httpHostName += $HostName

#Set current user name

$global:currentUserName = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name;

#Path to SQL ISO

$global:pathToSQLISO = ".\Resources\SQLServer2014DeveloperEdition\en_sql_server_2014_developer_edition_x64_dvd_3940406.iso"

$global:pathToSQLISO = Resolve-Path $global:pathToSQLISO

#Path to SHarePoint ISO

$global:pathToSharePointISO = ".\Resources\SharePoint2013\en_sharepoint_server_2013_with_sp1_x64_dvd_3823428.iso"

$global:pathToSharePointISO = Resolve-Path $global:pathToSharePointISO

#Path to SharePoint Prerequisites

$global:SharePoint2013Path = ".\Resources\SharePoint2013"

$global:SharePoint2013Path = Resolve-Path $global:SharePoint2013Path

#Domain Vars

#$global:path = "CN=Managed Service Accounts,"

$global:path = "CN=Users,"

$global:root = [ADSI]''

$global:dn = $global:root.distinguishedName

$global:path += $global:dn

$global:domainpart = (gwmi Win32_NTDomain).DomainName

#SPFarm Account Name

$global:spAccount = "SPFarm"

Here some items are set up, such as the hostname of the machine, the current user name, the paths to ISO files for SharePoint and SQL. Also, the account name for the SharePoint farm account is specified here.

Part 5: the actual program

#ACTUAL PROGRAM

#STEP 1 - Disable IE ESC
if ($DisableIEESC -or ($DoAllTasks -and (Should-Run-Step "1"))) {
    DisableIEESC
}
#Step 2 - Setup AD
if ($SetupActiveDirectory -or ($DoAllTasks -and (Should-Run-Step "2"))) {
    SetupActiveDirectory -DomainName $DomainName
}
#Step 3 - Configure Password Policy
if ($ConfigurePasswordPolicy -or ($DoAllTasks -and (Should-Run-Step "3"))) {
    ConfigurePasswordPolicy -DomainName $DomainName
}
#Step 4 - Install System Center Endpoint Protection
if($InstallSystemCenterEndpointProtection -or ($DoAllTasks -and (Should-Run-Step "4"))) {
    InstallSystemCenterEndpointProtection
}
#Step 5 - Install SQL Server
if($InstallSQLServer -or ($DoAllTasks -and (Should-Run-Step "5"))) {
    InstallSQLServer -Password $Password
}
#Step 6- Install SharePoint
if($InstallSharePoint -or ($DoAllTasks -and (Should-Run-Step "6"))) {
    InstallSharePoint
}
#Step 7- Install PowerPivot
if($InstallPowerPivot -or ($DoAllTasks -and (Should-Run-Step "7"))) {
    InstallPowerPivot -Password $Password
}
#Step 8 - Configure PowerPivot
if($ConfigurePowerPivot -or ($DoAllTasks -and (Should-Run-Step "8"))) {
    ConfigurePowerPivot -passphrase $passphrase -Password $Password
}
#Step 9 - Configure PowerPivot Part 2
if($ConfigurePowerPivotPart2 -or ($DoAllTasks -and (Should-Run-Step "9"))) {
    ConfigurePowerPivotPart2 -passphrase $passphrase -Password $Password
}

#ACTUAL PROGRAM

#STEP 1 - Disable IE ESC

if ($DisableIEESC -or ($DoAllTasks -and (Should-Run-Step "1"))) {

DisableIEESC

}

#Step 2 - Setup AD

if ($SetupActiveDirectory -or ($DoAllTasks -and (Should-Run-Step "2"))) {

SetupActiveDirectory -DomainName $DomainName

}

#Step 3 - Configure Password Policy

if ($ConfigurePasswordPolicy -or ($DoAllTasks -and (Should-Run-Step "3"))) {

ConfigurePasswordPolicy -DomainName $DomainName

}

#Step 4 - Install System Center Endpoint Protection

if($InstallSystemCenterEndpointProtection -or ($DoAllTasks -and (Should-Run-Step "4"))) {

InstallSystemCenterEndpointProtection

}

#Step 5 - Install SQL Server

if($InstallSQLServer -or ($DoAllTasks -and (Should-Run-Step "5"))) {

InstallSQLServer -Password $Password

}

#Step 6- Install SharePoint

if($InstallSharePoint -or ($DoAllTasks -and (Should-Run-Step "6"))) {

InstallSharePoint

}

#Step 7- Install PowerPivot

if($InstallPowerPivot -or ($DoAllTasks -and (Should-Run-Step "7"))) {

InstallPowerPivot -Password $Password

}

#Step 8 - Configure PowerPivot

if($ConfigurePowerPivot -or ($DoAllTasks -and (Should-Run-Step "8"))) {

ConfigurePowerPivot -passphrase $passphrase -Password $Password

}

#Step 9 - Configure PowerPivot Part 2

if($ConfigurePowerPivotPart2 -or ($DoAllTasks -and (Should-Run-Step "9"))) {

ConfigurePowerPivotPart2 -passphrase $passphrase -Password $Password

}

This part of the script calls the right downstream execution script with the right parameters.

Up next: the script that disables Internet Explorer Enhanced Security Configuration.

Automatically building a Microsoft BI machine using PowerShell – preparation: logging infrastructure (post #4)

on November 10, 2015

by Jeroen ter Heerdt 2 Comments

This post is #4 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service

Our final step in preparation is setting up a logging infrastructure. I found a very simple to use function online, see the code below:

Function Write-Log {
    [cmdletbinding()]

    Param(
    [Parameter(Position=0)]
    [ValidateNotNullOrEmpty()]
    [string]$Message
    )
    
    #Pass on the message to Write-Verbose if -Verbose was detected
    Write-Verbose $Message
    
    Write-Output "$(Get-Date) $Message" | Out-File -FilePath $global:LogFile -Append
    

} #end function

Function Write-Log {

[cmdletbinding()]

Param(

[Parameter(Position=0)]

[ValidateNotNullOrEmpty()]

[string]$Message

)

#Pass on the message to Write-Verbose if -Verbose was detected

Write-Verbose $Message

Write-Output "$(Get-Date) $Message" | Out-File -FilePath $global:LogFile -Append

} #end function

Including this function in the script enables any step to write to a log by passing a $Message to this function.

Next post will be our master script.

SQL Server 2005 is End-of-life in april volgend jaar

on August 28, 2015

by Harry Tolsma 0 Comments

Voor iedere DBA-er en database manager is het belangrijk om alvast voor te sorteren op het feit dat het support van Microsoft op SQL 2005 eindigt in april volgend jaar.

Dit lijkt ver weg, maar omdat je als beheerder te maken hebt met een applicatie eigenaar per applicatie en dus per database moet er nog veel communicatie komen voor de daadwerkelijke migratie plaats kan vinden.

Op onderstaande link vind je een artikel van mij hierover op Webwereld:

Artikel SQL 2005 EOL op Webwereld.nl

Comparing Datazen, SSRS and Power View

on June 30, 2015

by Jeroen ter Heerdt 0 Comments

It is a difficult task, but it can be done… comparing Datazen, SSRS and Power View. See http://www.sqlchick.com/entries/2015/6/20/comparison-of-datazen-vs-ssrs-reporting-services-vs-power-view for a in-depth comparison!

CTP2 of the new SQL Server 2016 has been released to public

on May 28, 2015

by Harry Tolsma 0 Comments

With this new beta release of SQL Server we get a better insight in what we can expect in the full product.
Some of the top capabilities in SQL Server 2016 CTP2 are:

Real-time Operational Analytics & In-Memory OLTP, enhanced for up to 30x faster transactions for a greater number of applications, customers can configure the in-memory columnstore to work on top of a transactional database to achieve real-time operational analytics with breakthrough OLTP performance.
Always Encrypted helps protect data at rest, in motion and while in use, on-premises and in the cloud.
With Always Encrypted, SQL Server can perform operations on encrypted data.
Best of all the encryption key resides with the application in the customer’s trusted environment.
Stretch Database technology keeps historical data at users’ fingertips by transparently stretching warm and cold data in a more secure manner to Microsoft Azure on demand without application changes.

I advise you to take a special look into the stretched databases.

It is a technique originating in Azure where it’s already very easy to scale up or down on a working database.
And not only on a single database!
Also a set of databases (called Elastic Pool) can be tailored to meet the per time different demand of power for each individual database.
A brand new concept which you could use in use-cases like having a database per customer or project which scale needs are different in time and in database.

In my next blogpost I will dig deeper on the new concept of elastic database.

Harry

Automatically building a Microsoft BI machine using PowerShell – preparation: install files using Azure File Service (post #3)

on May 26, 2015

by Jeroen ter Heerdt 0 Comments

This post is #3 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk

In our last post we looked a one way of working with the install files required for automating the installation of a BI machine, using disks. This post will focus on sharing the install files using Azure File Service. The Azure File Service exposes file shares using the standard SMB 2.1 protocol. It is in some ways an addition to storage accounts. See http://blogs.msdn.com/b/windowsazurestorage/archive/2014/05/12/introducing-microsoft-azure-file-service.aspx for more information. This service is in beta at the moment, so you will need to subscribe to the beta using the Azure Preview portal: http://azure.microsoft.com/en-us/services/preview/. Look for ‘Azure Files’ in the list and click on ‘Try it’ to get your account activated for the preview.

The Azure File Service is not exposed in any portal, probably since it is in preview. Also, keep in mind that while the service is in preview existing storage accounts will not have access to the File Service, so we will need to create a new storage account as well. To do this login into the portal and click on ‘New’ and create a new storage account. After the storage account has been created, you will need to use PowerShell to create a file share. Make sure you have the latest version of Azure PowerShell installed and then run the following in Azure PowerShell or use ISE:

$storageAccountName  = "YourStorageAccountName"
$storageAccountKey = "YourStorageAccountKey" 
$ctx = New-AzureStorageContext $storageAccountName $storageAccountKey
$s = New-AzureStorageShare YourShareName -Context $ctx

$storageAccountName = "YourStorageAccountName"

$storageAccountKey = "YourStorageAccountKey"

$ctx = New-AzureStorageContext $storageAccountName $storageAccountKey

$s = New-AzureStorageShare YourShareName -Context $ctx

After this runs you should be able to access the file share in multiple ways, but the easiest way I found is mapping the share as a folder in a VM by running:

Net use e: \\YourStorageAccountName.file.core.windows.net\YourShareName /u:YourStorageAccountName YourStorageAccountKey

1	Net use e: \\YourStorageAccountName.file.core.windows.net\YourShareName /u:YourStorageAccountName YourStorageAccountKey

Now you can download and store files on the share just as you can with disks, as discussed in post #2 on using install files using Azure disks.

Next post will be our final step of the preparation: logging.

Category Archives

Automatically building a Microsoft BI machine using PowerShell – Installing System Center Endpoint Protection (post #9)

Like this:

Automatically building a Microsoft BI machine using PowerShell – Password policy (post #8)

Like this:

Automatically building a Microsoft BI machine using PowerShell – Active Directory Setup (post #7)

Like this:

Automatically building a Microsoft BI machine using PowerShell – Disabling Internet Explorer Enhanced Security Configuration (post #6)

Like this:

Automatically building a Microsoft BI machine using PowerShell – master script (post #5)

Like this:

Automatically building a Microsoft BI machine using PowerShell – preparation: logging infrastructure (post #4)

Like this:

SQL Server 2005 is End-of-life in april volgend jaar

Like this:

Comparing Datazen, SSRS and Power View

Like this:

CTP2 of the new SQL Server 2016 has been released to public

Like this:

Automatically building a Microsoft BI machine using PowerShell – preparation: install files using Azure File Service (post #3)

Like this:

Category Archives

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: