NTS.Tools.MSSQL.psm1

function Get-SQLISO {
    <#
        .Description
        downloads sql iso from microsoft
 
        .Parameter Version
        version of the iso
 
        .Parameter Outpath
        path where the iso is stored
 
        .Example
        # stores the iso to $Outpath
        Get-SQLISO -Outpath $Outpath
 
        .NOTES
        downloads the sql enterprise eval setup and with it the iso
    #>


    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateSet("2019", "2022")]
        [string]
        $Version,

        [Parameter(Mandatory = $true)]
        [string]
        $Outpath
    )

    switch ($Version) {
        "2019" { $DownloadURL = "https://download.microsoft.com/download/4/8/6/486005eb-7aa8-4128-aac0-6569782b37b0/SQL2019-SSEI-Eval.exe" }
        "2022" { $DownloadURL = "https://download.microsoft.com/download/4/1/b/41b9a8c3-c2b4-4fcc-a3d5-62feed9e6885/SQL2022-SSEI-Eval.exe" }
        Default { throw "no version was selected or not supported" }
    }

    New-ItemIfNotExists -Path $Outpath -ItemType Directory
    $Outpath = (Get-Item -Path $Outpath).FullName
    $SQLEvalSetupPath = "$($Version)-SSEI-Eval.exe"
    $SQLEvalSetupFullPath = "$($Outpath)\$($SQLEvalSetupPath)"

    # downloading eval setup
    try {
        Start-FileDownload -DownloadURL $DownloadURL -FileOutPath $SQLEvalSetupFullPath
    }
    catch {
        throw "error downloading eval setup: $($PSItem.Exception.Message)"
    }

    # downloading iso
    try {
        $ISOPath = (Get-ChildItem -Path $Outpath | Where-Object -FilterScript { $PSItem.Name -like "SQL*.iso" }).FullName
        if ($null -ne $ISOPath -and (Test-Path -Path $ISOPath) -eq $true) {
            if ((Get-Item $ISOPath).LastWriteTime -gt (Get-Date).AddHours(-2)) {
                # juenger als zwei stunden
                # do nothing
                Write-Output "$($env:COMPUTERNAME): found sql iso at $($ISOPath), will use it"
            } 
            else {
                Write-Output "$($env:COMPUTERNAME): found sql iso at $($ISOPath), removing the files because too old"
                Remove-Item -Path $ISOPath -Recurse -Force | Out-Null
            }            
        }
        Write-Output "$($env:COMPUTERNAME): downloading sql iso with eval setup"
        $Arguments = "/ACTION=Download /MEDIAPATH=$($Outpath)\ /MEDIATYPE=ISO /LANGUAGE=en-US /QUIET"
        Start-Process $SQLEvalSetupFullPath -ArgumentList $Arguments -Wait -NoNewWindow

        Remove-Item -Path $SQLEvalSetupFullPath -Force
    }
    catch {
        throw "error downloading iso: $($PSItem.Exception.Message)"
    }
    Write-Output "$($env:COMPUTERNAME): finished download - check folder $($Outpath)"
}

function Initialize-SQLSetup {
    <#
        .Description
        prepares the sql installation files for i
 
        .Parameter Outpath
        path where the data should be cached
 
        .Example
        # stores the files to $TempFolderForSQL
        Initialize-SQLSetup -Outpath $TempFolderForSQL
 
        .NOTES
        downloads the sql install files and stores them extracted to the outpath
    #>


    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        [ValidateSet("2019", "2022")]
        [string]
        $Version = "2022",

        [Parameter(Mandatory = $true)]
        [string]
        $Outpath
    )
    
    New-ItemIfNotExists -Path $Outpath -ItemType Directory
    $Outpath = (Get-Item -Path $Outpath).FullName

    # checks
    $SQLFileFolder = "$($Outpath)\SQLFiles\"
    if ((Test-Path -Path $SQLFileFolder) -eq $true ) {
        throw "$($env:COMPUTERNAME): folder $($SQLFileFolder) already exits, please cleanup or skip"
    }

    # downloading iso
    try {
        Get-SQLISO -Version $Version -Outpath $Outpath
    }
    catch {
        throw "error during download - $($PSItem.Exception.Message)"
    }
    
    # copying files
    try {
        if ((Test-Path -Path $SQLFileFolder) -eq $true) {
            if ((Get-Item $SQLFileFolder).LastWriteTime -gt (Get-Date).AddHours(-2)) {
                # juenger als zwei stunden
                # do nothing
                Write-Output "$($env:COMPUTERNAME): found setup files at $($SQLFileFolder) will use it"
            } 
            else {
                Write-Output "$($env:COMPUTERNAME): found setup files at $($SQLFileFolder), removing the files because too old"
                Remove-Item -Path $SQLFileFolder -Recurse -Force | Out-Null
            }
        }
        Write-Output "$($env:COMPUTERNAME): mounting iso"
        $ISO = Get-ChildItem -Path $Outpath | Where-Object -FilterScript { $PSItem.Name -like "SQL*.iso" }
        $MountedISOs = Mount-DiskImage -PassThru -ImagePath $ISO.FullName
        $Volume = Get-Volume -DiskImage $MountedISOs
        Write-Output "$($env:COMPUTERNAME): copy files from iso to $($SQLFileFolder)"
        Copy-Item -Path "$($Volume.DriveLetter):\" -Destination $SQLFileFolder -Recurse -Force
        Write-Output "$($env:COMPUTERNAME): finished copy job"
        Dismount-DiskImage $MountedISOs.ImagePath | Out-Null
        Write-Output "$($env:COMPUTERNAME): iso was dismounted"
    }
    catch {
        throw "error during mount or copy - $($PSItem.Exception.Message)"
    }
}

function Install-SQLADServiceAccount {
    <#
        .Description
        adds the local server to a group with permission to the serviec account and then installs it
 
        .Parameter SQLServiceAccount
        name of the group managed service account
 
        .Parameter GroupWithPermissions
        name of the group with permissions to retrieve the password of the group managed service account
 
        .Example
        # this installs a gMSA
        Install-SQLADServiceAccount -SQLServiceAccount $SQLEngine.Name -GroupWithPermissions $SQLEngine.GroupWithPermissions
 
        .NOTES
        - the server needs to be member of a domain
        - the group managed service account must exit
        - the user which runs this command must have permissions to add the local device to the group
    #>


    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]
        $SQLServiceAccount,

        [Parameter(Mandatory = $true)]
        [string]
        $GroupWithPermissions
    )

    if ((Get-CimInstance -ClassName Win32_ComputerSystem).PartofDomain -eq $false) {
        throw "$($env:COMPUTERNAME): not a member of a domain"
    }

    Install-WindowsFeature -Name RSAT-AD-PowerShell | Out-Null

    if ($null -eq (Get-ADServiceAccount -Identity $SQLServiceAccount -ErrorAction SilentlyContinue)) {
        throw "service account $($SQLServiceAccount)$ could not be found"
    }

    # adding device to group
    try {
        Write-Output "$($env:COMPUTERNAME): adding to group $($GroupWithPermissions)"
        $Self = Get-ADComputer -Identity $env:COMPUTERNAME
        Add-ADGroupMember -Identity $GroupWithPermissions -Members $Self
    }
    catch {
        throw "not able to add to group $($GroupWithPermissions): $($PSItem.Exception.Message)"
    }
    
    # installing and testing service account
    try {
        Write-Output "$($env:COMPUTERNAME): installing $($SQLServiceAccount)"
        Start-Process -FilePath klist -ArgumentList "purge -lh 0 -li 0x3e7" -NoNewWindow -Wait
        Start-Sleep -Seconds 5
        Start-Process -FilePath klist -ArgumentList "purge -lh 0 -li 0x3e7" -NoNewWindow -Wait
        Start-Sleep -Seconds 10
        Install-ADServiceAccount -Identity ($SQLServiceAccount + "$")
        if ((Test-ADServiceAccount -Identity ($SQLServiceAccount + "$") -WarningAction SilentlyContinue) -eq $false) {
            throw "service account $($SQLServiceAccount)$ is not installed, please verify"
        }
    }
    catch {
        throw "could not install $($SQLServiceAccount) - $($PSItem.Exception.Message)"
    }
}

function Install-SQLInstance {
    <#
        .Description
        adds the local server to a group with permission to the serviec account and then installs it
 
        .Parameter Name
        name of group managed service account
         
        .Parameter UseLocalAccount
        specify if local accounts are used
 
        .Parameter UseGmSA
        specify if group managed service accounts are used
         
        .Parameter EngineAccountName
        service account name for sql engine
 
        .Parameter AgentAccountName
        service account name for sql agent
 
        .Parameter UseMixedAuth
        when used, the instance will use sql and windows authentication
 
        .Parameter SAPWD
        sa pwd
         
        .Parameter INSTALLSQLDATADIR
        specifies the data directory for SQL Server data files
         
        .Parameter INSTANCEDIR
        specifies nondefault installation directory for instance-specific components
         
        .Parameter SQLBACKUPDIR
        backup file path for the sql database
         
        .Parameter SQLUSERDBDIR
        user file path for the sql database
 
        .Parameter SQLUSERDBLOGDIR
        user log file path for the sql database
         
        .Parameter SQLTEMPDBDIR
        temp file path for the sql database
         
        .Parameter SQLTEMPDBLOGDIR
        temp log file path for the sql database
 
        .Parameter Features
        features that should be installed, like 'SQLENGINE,FULLTEXT,CONN'
         
        .Parameter SQLSYSADMINACCOUNTS
        accounts that should be admin on that instance
 
        .Parameter SQLMinRAM
        minimum ram for instance
 
        .Parameter SQLMaxRAM
        maximum ram for instance
 
        .Example
        # this installs an sql instance with the name PB1 which uses gmsa with customized file paths
        Install-SQLInstance -Name "PB1" `
            -Features "SQLENGINE" `
            -SQLSYSADMINACCOUNTS ('"' + "IC\T1-CMAdmin" + '" "' + "IC\T1-CMAdmins" + '"') `
            -SQLBACKUPDIR "S:\SQL\PB1\BACKUP\DATA" `
            -SQLUSERDBDIR "S:\SQL\PB1\USERDATA\DATA" `
            -SQLUSERDBLOGDIR "S:\SQL\PB1\USERLOG\LOG" `
            -SQLTEMPDBDIR "S:\SQL\PB1\TEMPDATA\DATA" `
            -SQLTEMPDBLOGDIR "S:\SQL\PB1\TEMPLOG\LOG" `
            -UseGmSA `
            -EngineAccountName $SQLEngine `
            -AgentAccountName $SQLAgent
 
        .Example
        # this installs an sql instance with the name INSTANCE and local accounts to default file paths
        Install-SQLInstance -Name "INSTANCE" -UseLocalAccount
 
        .NOTES
         
    #>


    [CmdletBinding(DefaultParameterSetName = 'local')]
    param (
        [Parameter(ParameterSetName = 'local', Mandatory = $false)]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false)]
        [string]
        $SetupPath = ".\SQLFiles\setup.exe",

        [Parameter(ParameterSetName = 'local', Mandatory = $true)]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $true)]
        [string]
        $Name,

        [Parameter(ParameterSetName = 'local')]
        [switch]
        $UseLocalAccount,

        [Parameter(ParameterSetName = 'gmsa')]
        [switch]
        $UseGmSA,

        [Parameter(ParameterSetName = 'gmsa', Mandatory = $true)]
        [string]
        $EngineAccountName,

        [Parameter(ParameterSetName = 'gmsa', Mandatory = $true)]
        [string]
        $AgentAccountName,

        [Parameter(ParameterSetName = 'local')]
        [Parameter(ParameterSetName = 'gmsa')]
        [switch]
        $UseMixedAuth,

        [Parameter(ParameterSetName = 'local')]
        [Parameter(ParameterSetName = 'gmsa')]
        [string]
        $SAPWD,

        [Parameter(ParameterSetName = 'local', Mandatory = $false)]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false)]
        [string]
        $INSTALLSQLDATADIR,
        
        [Parameter(ParameterSetName = 'local', Mandatory = $false)]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false)]
        [string]
        $INSTANCEDIR,

        [Parameter(ParameterSetName = 'local')]
        [Parameter(ParameterSetName = 'gmsa')]
        [string]
        $SQLBACKUPDIR,

        [Parameter(ParameterSetName = 'local')]
        [Parameter(ParameterSetName = 'gmsa')]
        [string]
        $SQLUSERDBDIR,

        [Parameter(ParameterSetName = 'local')]
        [Parameter(ParameterSetName = 'gmsa')]
        [string]
        $SQLUSERDBLOGDIR,

        [Parameter(ParameterSetName = 'local')]
        [Parameter(ParameterSetName = 'gmsa')]
        [string]
        $SQLTEMPDBDIR,

        [Parameter(ParameterSetName = 'local')]
        [Parameter(ParameterSetName = 'gmsa')]
        [string]
        $SQLTEMPDBLOGDIR,

        [Parameter(ParameterSetName = 'local', Mandatory = $false)]
        [Parameter(ParameterSetName = 'gmsa', Mandatory = $false)]
        [string]
        $Features,

        [Parameter(ParameterSetName = 'local')]
        [Parameter(ParameterSetName = 'gmsa')]
        [string]
        $SQLSYSADMINACCOUNTS,

        [Parameter(ParameterSetName = 'local')]
        [Parameter(ParameterSetName = 'gmsa')]
        [string]
        $SQLMinRAM,

        [Parameter(ParameterSetName = 'local')]
        [Parameter(ParameterSetName = 'gmsa')]
        [string]
        $SQLMaxRAM
    )

    # define arguments
    if ($UseLocalAccount -eq $false -and $UseGmSA -eq $false) {
        throw "you have to specify 'UseLocalAccount' or 'UseGmSA'"
    }
    if ($UseMixedAuth -eq $false -and $null -eq $SAPWD) {
        throw "you have to specify 'SAPWD' when 'UseMixedAuth' is used"
    }
    if ($uselocalaccount) {
        $AGTSVCACCOUNT = 'NT Service\SQLAgent$' + $Name
        $SQLSVCACCOUNT = 'NT Service\MSSQL$' + $Name
    }
    elseif ($UseGmSA) {
        $AGTSVCACCOUNT = $env:USERDOMAIN + "\" + (Get-ADServiceAccount -Identity $AgentAccountName).SamAccountName
        $SQLSVCACCOUNT = $env:USERDOMAIN + "\" + (Get-ADServiceAccount -Identity $EngineAccountName).SamAccountName
    }
    $SQLTELSVCACCT = 'NT Service\SQLTELEMETRY$' + $Name
    if ($Features -eq "") {
        $Features = "SQLENGINE"
    }
    if ($null -eq $SQLBACKUPDIR) {
        $SQLBACKUPDIR = $INSTALLSQLDATADIR + "\BACKUP\DATA"
    }
    if ($null -eq $SQLUSERDBDIR) {
        $SQLUSERDBDIR = $INSTALLSQLDATADIR + "\USER\DATA"
    }
    if ($null -eq $SQLUSERDBLOGDIR) {
        $SQLUSERDBLOGDIR = $INSTALLSQLDATADIR + "\USERLOG\LOG"
    }
    if ($null -eq $SQLTEMPDBDIR) {
        $SQLTEMPDBDIR = $INSTALLSQLDATADIR + "\TEMP\DATA"
    }
    if ($null -eq $SQLTEMPDBLOGDIR) {
        $SQLTEMPDBLOGDIR = $INSTALLSQLDATADIR + "\TEMPLOG\LOG"
    }
    if ($SQLSYSADMINACCOUNTS -eq "") {
        $SQLSYSADMINACCOUNTS = "`"$($env:COMPUTERNAME)\Administrator`""
    }

    # check dependencies
    try {
        if ($UseGmSA) {
            if ($env:USERDOMAIN -eq $env:COMPUTERNAME) {
                throw "you are logged in with a local user, domain user required"
            }
            if ((Test-ADServiceAccount -Identity "$($EngineAccountName)$") -eq $false) {
                throw "gmsa $($EngineAccountName) not installed"
            }
            if ((Test-ADServiceAccount -Identity "$($AgentAccountName)$") -eq $false) {
                throw "gmsa $($AgentAccountName) not installed"
            }
        }
        $InstalledInstances = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server' -ErrorAction SilentlyContinue).InstalledInstances
        if ($InstalledInstances -contains $Name) {
            throw "SQL instance is already installed $($Name)"
        }
    }
    catch {
        throw "error during prerequesits check : $($Psitem.Exception.Message)"
    }

    # start installation
    try {
        Write-Output "$($env:COMPUTERNAME): starting install of instance $($Name)"
        $Arguments = @(
            '/IACCEPTSQLSERVERLICENSETERMS="True"'
            '/IACCEPTPYTHONLICENSETERMS="False"'
            '/ACTION="Install"'
            '/ENU="True"'
            '/IACCEPTROPENLICENSETERMS="False"'
            '/SUPPRESSPRIVACYSTATEMENTNOTICE="False"'
            '/QUIET="True"'
            '/QUIETSIMPLE="False"'
            '/UpdateEnabled="True"'
            '/USEMICROSOFTUPDATE="False"'
            '/SUPPRESSPAIDEDITIONNOTICE="False"'
            '/UpdateSource="MU"'
            ('/FEATURES=' + $Features)
            ('/INSTANCENAME="' + $Name + '"')
            ('/INSTALLSHAREDDIR="' + $env:ProgramFiles + '\Microsoft SQL Server"')
            ('/INSTALLSHAREDWOWDIR="' + ${env:ProgramFiles(x86)} + '\Microsoft SQL Server"')
            ('/INSTANCEID="' + $Name + '"')
            ('/SQLTELSVCACCT="' + $SQLTELSVCACCT + '"')
            '/SQLTELSVCSTARTUPTYPE="Automatic"'            
            ('/AGTSVCACCOUNT="' + $AGTSVCACCOUNT + '"')
            '/AGTSVCSTARTUPTYPE="Automatic"'
            '/SQLSVCSTARTUPTYPE="Automatic"'
            '/SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"'
            ('/SQLSVCACCOUNT="' + $SQLSVCACCOUNT + '"')
            ('/SQLSYSADMINACCOUNTS=' + $SQLSYSADMINACCOUNTS)
            ('/SQLBACKUPDIR="' + $SQLBACKUPDIR + '"')
            ('/SQLUSERDBDIR="' + $SQLUSERDBDIR + '"')
            ('/SQLUSERDBLOGDIR="' + $SQLUSERDBLOGDIR + '"')
            ('/SQLTEMPDBDIR="' + $SQLTEMPDBDIR + '"')
            ('/SQLTEMPDBLOGDIR="' + $SQLTEMPDBLOGDIR + '"')
            '/TCPENABLED="1"'
            '/NPENABLED="0"'
            '/BROWSERSVCSTARTUPTYPE="Automatic"'
        )
        if ($UseMixedAuth -eq $true) {
            $SAArguments = @(
                '/SECURITYMODE="SQL"'
                '/SAPWD="' + $SAPWD + '"'
            )
            $Arguments = $Arguments + $SAArguments
        }
        if ($INSTALLSQLDATADIR -ne "") {
            $INSTALLSQLDATADIR_Arguments = @(
                ('/INSTALLSQLDATADIR="' + $INSTALLSQLDATADIR + '"')
            )
            $Arguments = $Arguments + $INSTALLSQLDATADIR_Arguments
        }
        if ($INSTANCEDIR -ne "") {
            $INSTANCEDIR_Arguments = @(
                ('/INSTANCEDIR="' + $INSTANCEDIR + '"')
            )
            $Arguments = $Arguments + $INSTANCEDIR_Arguments
        }
        if ($SQLMinRAM -ne "") {
            $MinRAMArguments = @(
                ('/SQLMINMEMORY="' + $SQLMinRAM + '"')
            )
            $Arguments = $Arguments + $MinRAMArguments
        }
        if ($SQLMaxRAM -ne "") {
            $MaxRAMArguments = @(
                ('/SQLMAXMEMORY="' + $SQLMaxRAM + '"')
            )
            $Arguments = $Arguments + $MaxRAMArguments
        }

        $Process = Start-Process $SetupPath -ArgumentList $Arguments -Wait -NoNewWindow -PassThru
        if ($Process.ExitCode -ne 0 -and $Process.ExitCode -ne 3010) {
            $Message = Search-SQLSummaryLog -SearchString "Exit message"
            throw "check logs of sql setup - $($env:ProgramFiles)\Microsoft SQL Server\*\Setup Bootstrap\Log `nexit code: $($Process.ExitCode)`nmessage found: $($Message)"
        }
        elseif ($Process.ExitCode -eq 3010) {
            if (Test-RebootPending) {
                Write-Warning "A reboot is pending, please reboot before procceeding"
            }
        }
    }
    catch {
        throw "error during installation of sql instance $($Name): $($Psitem.EXception.Message)"
    }
    # service customization
    try {
        if ($UseGmSA) {
            Write-Output "$($env:COMPUTERNAME): configuring SQLAgent`$$($Name) service to autostart delayed"
            $Agent = (Get-Service -Name "SQLAgent`$$($Name)").Name
            Start-Process "sc" -ArgumentList ('config "' + $($Agent) + '" start=delayed-auto') -NoNewWindow

            Write-Output "$($env:COMPUTERNAME): configuring MSSQL`$$($Name) service to autostart delayed"
            $Engine = (Get-Service -Name "MSSQL`$$($Name)").Name    
            Start-Process "sc" -ArgumentList ('config "' + $($Engine) + '" start=delayed-auto') -NoNewWindow
        }
    }
    catch {
        throw "error during service customization : $($Psitem.EXception.Message)"
    }
    Write-Output "$($env:COMPUTERNAME): finished install of instance $($Name)"
}

function Set-SQLInstanceStaticPort {
    <#
        .Description
        configures the sql instance to use a static tcp port for all ips
 
        .Parameter InstanceName
        sql instance name
         
        .Parameter StaticPort
        port which should be used
 
        .Example
        # this installs an sql instance with the name PB1 which uses gmsa with customized file paths
         
        .Example
        # this installs an sql instance with the name INSTANCE and local accounts to default file paths
        Set-SQLInstanceStaticPort -InstanceName "INSTANCE" -StaticPort "1503"
 
        .NOTES
        Support is only available for SQL 2019 Standard / Enterprise
    #>

    
    param (
        [Parameter(Mandatory = $true)]
        [string]
        $InstanceName,

        [Parameter(Mandatory = $true)]
        [string]
        $StaticPort
    )

    $RegPath = (Resolve-Path -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL*$($InstanceName)\MSSQLServer\SuperSocketNetLib\Tcp\IPAll\").Path
    
    if (Test-Path -Path $RegPath) {
        try {
            $SQLSettings = Get-ItemProperty -Path $RegPath
            if ($SQLSettings.TcpDynamicPorts -ne $StaticPort) {
                Write-Output "$($env:COMPUTERNAME): set tcp port of $($InstanceName) to $($StaticPort)"
                Set-ItemProperty -Path $RegPath -Name TcpPort -Value $StaticPort
                Set-ItemProperty -Path $RegPath -Name TcpDynamicPorts -Value ""
            }
            Write-Output "$($env:COMPUTERNAME): restarting $($InstanceName) to apply change"
            Get-Service "*$($InstanceName)*" | Restart-Service -Force -WarningAction SilentlyContinue
        }
        catch {
            throw "could not configure the static port on $($InstanceName) - $($PSItem.Exception.Message)"
        }
    }
    else {
        throw "could not find the registry path for the instance"
    }
}

function Test-SQLDatabaseConnection {
    [CmdletBinding()]
    param (
        [Parameter(Position = 0, Mandatory = $True, ValueFromPipeline = $True)] 
        [string] 
        $Server,

        [Parameter(Position = 1, Mandatory = $false)] 
        [string] 
        $Database = "master",

        [Parameter(Position = 2, Mandatory = $True, ParameterSetName = "SQLAuth")] 
        [pscredential] 
        $SACredential,

        [Parameter(Position = 2, Mandatory = $True, ParameterSetName = "WindowsAuth")] 
        [switch] 
        $UseWindowsAuthentication
    )

    if ($Server -notlike "*\*") {
        $Server = "$($env:COMPUTERNAME)\$($Server)"
    }

    # connect to the database, then immediatly close the connection. If an exception occurrs it indicates the conneciton was not successful.
    $dbConnection = New-Object System.Data.SqlClient.SqlConnection
    if (!$UseWindowsAuthentication) {
        $dbConnection.ConnectionString = "Data Source=$($Server); uid=$($SACredential.UserName); pwd=$($SACredential.GetNetworkCredential().Password); Database=$($Database);Integrated Security=False"
    }
    else {
        $dbConnection.ConnectionString = "Data Source=$($Server); Database=$($Database);Integrated Security=True;"
    }
    try {
        Measure-Command { $dbConnection.Open() } | Out-Null
        $Success = $true
    }
    # exceptions will be raised if the database connection failed.
    catch {
        $Success = $false
    }
    Finally {
        # close the database connection
        $dbConnection.Close()
    }

    return $Success
}

function Get-SQLCU {
    <#
        .Description
        downloads sql cu from microsoft
 
        .Parameter Version
        version of the cu
 
        .Parameter Outpath
        path where the cu is stored
 
        .Example
        # stores the cu to $Outpath
        Get-SQLISO -Version 2019_latest -Outpath $Outpath
 
        .NOTES
        downloads the sql enterprise eval setup and with it the iso
    #>


    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateSet("2019_latest", "2022_latest")]
        [string]
        $Version,

        [Parameter(Mandatory = $true)]
        [string]
        $Outpath
    )

    $ErrorActionPreference = 'Stop'
    if ($Outpath[-1] -eq "\") {
        $Outpath = $Outpath.Substring(0, $Outpath.Length - 1)
    }
    $SQLCUFilePath = "$($Outpath)\SQL-$($Version)-cu.exe"

    switch ($Version) {
        "2019_latest" { $DownloadURL = "https://www.microsoft.com/en-us/download/confirmation.aspx?id=100809" }
        "2022_latest" { $DownloadURL = "https://www.microsoft.com/en-us/download/confirmation.aspx?id=105013" }
        Default { throw "no version was selected or not supported" }
    }

    try {
        $Content = Invoke-WebRequest -UseBasicParsing -Uri $DownloadURL
        $UpdateLink = ($Content.Links | Where-Object -FilterScript { $PSItem.href -like "*download.microsoft.com*" -and $PSItem.outerHTML -like "*download manually*" }).href

        Start-FileDownload -DownloadURL $UpdateLink -FileOutPath $SQLCUFilePath
    }
    catch {
        throw "$($env:COMPUTERNAME): error getting sql cu files - $($PSItem.Exception.Message)"
    }

    Write-Output "$($env:COMPUTERNAME): finished download - check folder $($Outpath)"
}

function Install-SQLCU {
    <#
        .Description
        installs sql 2019 latest cu to all local instances
 
        .Parameter Version
        version string of sql cu, like 2019_latest
 
        .Parameter TempFolder
        folder, where files will be stored temporally
 
        .Example
        # this installes sql 2019 latest cu to all instances
        Install-SQLCU -Version "2019_latest"
 
        .NOTES
 
    #>


    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateSet("2019_latest", "2022_latest")]
        [string]
        $Version,

        [Parameter(Mandatory = $false)]
        [string]
        $TempFolder = "$($env:ProgramData)\NTS\SQL\CU"
    )

    $ErrorActionPreference = 'Stop'
    $SQLCUFilePath = "$($TempFolder)\SQL-$($Version)-cu.exe"
    $SQLCUUnpackedFilePath = "$($TempFolder)\$($Version)-cu\"

    try {
        Get-SQLCU -Version $Version -Outpath $TempFolder

        # unpacking sql cu to file system
        if ((Test-Path -Path $SQLCUFilePath) -eq $false) {
            throw "cannot find cu package file"
        }
        Write-Output "$($env:COMPUTERNAME): unpacking sql cu"
        $Arguments = "/X:$($SQLCUUnpackedFilePath)"
        $Process = Start-Process $SQLCUFilePath -ArgumentList $Arguments -Wait -NoNewWindow -PassThru
        if ($Process.ExitCode -ne 0) {
            throw "there was an error unpacking cu files"
        }
    }
    catch {
        throw "$($env:COMPUTERNAME): error getting sql cu files - $($PSItem.Exception.Message)"
    }
    
    try {
        # installation
        Write-Output "$($env:COMPUTERNAME): installing sql cu"
        $Arguments = "/ACTION=Patch /ALLINSTANCES /QUIET /IACCEPTSQLSERVERLICENSETERMS /ENU"
        $Process = Start-Process ($SQLCUUnpackedFilePath + "setup.exe") -ArgumentList $Arguments -Wait -NoNewWindow -PassThru
        if ($Process.ExitCode -ne 0) {
            $FinalResult = Search-SQLSummaryLog -SearchString "Final result"
            $ExitMessage = Search-SQLSummaryLog -SearchString "Exit message"

            if ($ExitMessage -like "*No features were updated during the setup execution. The requested features may not be installed or features are already at a higher patch level*") {
                Write-Output "$($env:COMPUTERNAME): no updates were applied, because already at a higher or equal patch level"
            }
            elseif ($FinalResult -like "*Passed but reboot required, see logs for details*") {
                Write-Output "$($env:COMPUTERNAME): sql cu install but reboot required"
            }
            else {
                throw "there was an error installing sql cu - $($ExitMessage)"
            }
        }
    
        # cleanup
        Start-FolderCleanUp -FolderToRemove $TempFolder
    }
    catch {
        throw "$($env:COMPUTERNAME): error sql cu installation - $($PSItem.Exception.Message) - see logs at C:\Program Files\Microsoft SQL Server\*\Setup Bootstrap\Log\"
    }
}

function Install-SQLSSMS {
    <#
        .Description
        this installs the latest version of sql sql management studio
 
        .Example
        # this installs the latest version of sql ssms
        Install-SQLSSMS
 
        .NOTES
        always installs the latest version from https://aka.ms/ssmsfullsetup
    #>


    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $false)]
        [ValidateSet("Latest","19.1","19.0.2","19.0.1","18.12.1","17.9.1")]
        [string]
        $Version = "Latest"
    )

    switch ($Version) {
        "Latest" { $SetupURL = "https://aka.ms/ssmsfullsetup" }
        "19.1" { $SetupURL = "https://download.microsoft.com/download/a/c/a/aca4e29f-6925-4d50-a06b-5576c6ea629f/SSMS-Setup-ENU.exe" }
        "19.0.2" { $SetupURL = "https://download.microsoft.com/download/9/f/8/9f8197f4-0f71-42a3-8717-b2817c77b820/SSMS-Setup-ENU.exe" }
        "19.0.1" { $SetupURL = "https://download.microsoft.com/download/a/3/2/a32ae99f-b6bf-4a49-a076-e66503ccb925/SSMS-Setup-ENU.exe" }
        "18.12.1" { $SetupURL = "https://download.microsoft.com/download/8/a/8/8a8073d2-2e00-472b-9a18-88361d105915/SSMS-Setup-ENU.exe" }
        "17.9.1" { $SetupURL = "https://download.microsoft.com/download/D/D/4/DD495084-ADA7-4827-ADD3-FC566EC05B90/SSMS-Setup-ENU.exe" }
        Default { throw "no version was specified or not supported" }
    }
    
    $TempFolderPath = "$($env:ProgramData)\NTS\SQL\SSMS\"
    $SetupFilePath = $TempFolderPath + "SSMS-$($Version).exe"

    # download
    try {
        New-ItemIfNotExists -Path $TempFolderPath -ItemType Directory
        Write-Output "$($env:COMPUTERNAME): downloading SSMS"
        Start-FileDownload -DownloadURL $SetupURL -FileOutPath $SetupFilePath
    }
    catch {
        throw "error downloading the setup - $($PSItem.Exception.Message)"
    }

    # install
    try {
        Write-Output "$($env:COMPUTERNAME): installing SSMS"
        Start-Process -FilePath $SetupFilePath -ArgumentList "/quiet /norestart" -Wait -NoNewWindow
        Write-Output "$($env:COMPUTERNAME): finished installing SSMS"
    
        Start-FolderCleanUp -FolderToRemove $TempFolderPath
    }
    catch {
        throw "error installing - $($PSItem.Exception.Message)"
    }
}

function Get-SQLSSRSSetup {
    <#
        .Description
        this downloads the sql reporting services setup
 
        .Parameter Version
        version of sql reporting services setup
 
        .Parameter Outpath
        where should the setup file be stored
 
        .Example
        # this will download the setup to $SSRSTempFolder
        Get-SQLSSRSSetup -TempFolder $SSRSTempFolder
 
        .NOTES
        setup file name will be "$($TempFolder)\SQL$($Version)_ReportingServices-latest.exe"
    #>


    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateSet("2022","2019")]
        [string]
        $Version,

        [Parameter(Mandatory = $false)]
        [string]
        $Outpath = "$($env:ProgramData)\NTS\SQL\ReportingServices"
    )
    
    try {
        switch ($Version) {
            "2022" { $DownloadURL = "https://download.microsoft.com/download/8/3/2/832616ff-af64-42b5-a0b1-5eb07f71dec9/SQLServerReportingServices.exe" }
            "2019" { $DownloadURL = "https://download.microsoft.com/download/1/a/a/1aaa9177-3578-4931-b8f3-373b24f63342/SQLServerReportingServices.exe" }
            Default { throw "no version was specified or not supported" }
        }

        $SQL_RPServices_FilePath = "$($Outpath)\SQL$($Version)_ReportingServices-latest.exe"
        New-ItemIfNotExists -Path $Outpath -ItemType Directory

        # $Content = Invoke-WebRequest -UseBasicParsing -Uri $DownloadURL
        # $SetupLink = ($Content.Links | Where-Object -FilterScript { $PSItem.href -like "*download.microsoft.com*" -and $PSItem.outerHTML -like "*download manually*" }).href
        Write-Output "$($env:COMPUTERNAME): downloading sql reporting services"
        Start-FileDownload -DownloadURL $DownloadURL -FileOutPath $SQL_RPServices_FilePath
    }
    catch {
        throw "$($env:COMPUTERNAME): $($PSItem.Exception.Message)"
    }
}

function Install-SQLSSRS {
    <#
        .Description
        this will install sql reporting services
 
        .Parameter Version
        version of sql reporting services setup
 
        .Parameter SetupFilePath
        path to setup file
 
        .Example
        # this will install ssrs to the local server
        Install-SQLSSRS -Version 2019
 
        .NOTES
        setup file name must be at "$($TempFolder)\SQL$($Version)_ReportingServices-latest.exe"
    #>


    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateSet("2022","2019")]
        [string]
        $Version,

        [Parameter(Mandatory = $false)]
        [string]
        $SetupFilePath = "$($env:ProgramData)\NTS\SQL\ReportingServices\SQL$($Version)_ReportingServices-latest.exe",

        [Parameter(Mandatory = $false)]
        [string]
        $SQL_RPServices_LogPath = "$($env:ProgramData)\NTS\SQL\ReportingServices\install.log"
    )

    try {
        if (Test-Path -Path $SetupFilePath) {
            Write-Output "$($env:COMPUTERNAME): installing sql reporting services"
            $Arguments = "/quiet /IAcceptLicenseTerms /Edition=Eval /norestart /log $($SQL_RPServices_LogPath)"
            $Process = Start-Process $SetupFilePath -ArgumentList $Arguments -Wait -NoNewWindow -PassThru
            
            if ($Process.ExitCode -eq 3010) {
                if (Test-RebootPending) {
                    Write-Warning "A reboot is pending, please reboot before procceeding"
                }
            }
            elseif ($Process.ExitCode -ne 0 -and $Process.ExitCode -ne 3010) {
                throw "error installing ssrs - exit code $($Process.ExitCode) `ncheck logs at $($SQL_RPServices_LogPath)"
            }
        }
        else {
            throw "setup file not found at $($SetupFilePath)"
        }
    }
    catch {
        throw "$($env:COMPUTERNAME): - $($PSItem.Exception.Message)"
    }
}

function Initialize-SQLSSRS {
    <#
        .Description
        this will configure the sql reporting services
 
        .Parameter Version
        version of sql reporting services setup
 
        .Parameter SQL_Instance
        mssql instance
 
        .Parameter SSRS_ServiceAccountCredentials
        credentials for the service account of ssrs, must be an domain user
 
        .Parameter SQL_DB_Name
        name of ssrs db
 
        .Example
        # this will configure the ssrs to use a domain account as service
        Initialize-SQLSSRS -SQL_Instance "$($env:COMPUTERNAME)\$($using:CM_SQL_RPT_InstanceName)" -SSRS_ServiceAccountCredentials $using:CM_SQL_SSRS_ServiceAccountCredentials -TempFolder $SSRSTempFolder
 
        .NOTES
        https://blog.aelterman.com/2018/01/03/complete-automated-configuration-of-sql-server-2017-reporting-services/
        https://github.com/mrsquish/AutomationScripts/blob/main/ConfigureSSRS.ps1
        https://gist.github.com/SvenAelterman/f2fd058bf3a8aa6f37ac69e5d5dd2511
    #>


    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateSet("2022","2019", "2017")]
        [string]
        $Version,

        [Parameter(Mandatory = $true)]
        [string]
        $SQL_Instance,

        [Parameter(Mandatory = $true)]
        [pscredential]
        $SSRS_ServiceAccountCredentials,

        [Parameter(Mandatory = $false)]
        [string]
        $SQL_DB_Name = "ReportServer"
    )

    switch ($Version) {
        "2022" { $WMI_Namespace = "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v16\Admin" } #?????
        "2019" { $WMI_Namespace = "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v15\Admin" }
        "2017" { $WMI_Namespace = "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin" }
        Default { throw "no version was selected or the version is unsupported" }
    }

    if ($SQL_Instance -notlike "*\*") {
        $SQL_Instance = "$($env:COMPUTERNAME)\$($SQL_Instance)"
    }

    # test sql instance connection
    if ((Test-SQLDatabaseConnection -Server $SQL_Instance -UseWindowsAuthentication) -eq $false) {
        throw "could not connect to sql instance $($SQL_Instance) using integrated security"
    }

    try {
        $RSConfig = Get-WmiObject -Class "MSReportServer_ConfigurationSetting" -Namespace $WMI_Namespace
        If ($RSConfig.IsInitialized -eq $true) {
            throw "ssrs is already initialized, stopping"
        }
        Write-Output "$($env:COMPUTERNAME): configuring sql reporting services"
    
        # set service account
        $RSConfig = Get-WmiObject -Class "MSReportServer_ConfigurationSetting" -Namespace $WMI_Namespace
        $useBuiltInServiceAccount = $false
        Write-Output "$($env:COMPUTERNAME): configuring $($SSRS_ServiceAccountCredentials.UserName) as service account for ssrs"
        $RSConfig.SetWindowsServiceIdentity($useBuiltInServiceAccount, $($SSRS_ServiceAccountCredentials.UserName), $($SSRS_ServiceAccountCredentials.GetNetworkCredential().Password)) | out-null
    
        # need to reset the URLs for domain service account to work
        Write-Output "$($env:COMPUTERNAME): configuring http urls"
        $HTTPport = 80
        $RSConfig.RemoveURL("ReportServerWebService", "http://+:$($HTTPport)", 1033) | out-null
        $RSConfig.RemoveURL("ReportServerWebApp", "http://+:$($HTTPport)", 1033) | out-null
        $RSConfig.SetVirtualDirectory("ReportServerWebService", "ReportServer", 1033) | out-null
        $RSConfig.SetVirtualDirectory("ReportServerWebApp", "Reports", 1033) | out-null
        $RSConfig.ReserveURL("ReportServerWebService", "http://+:$($HTTPport)", 1033) | out-null
        $RSConfig.ReserveURL("ReportServerWebApp", "http://+:$($HTTPport)", 1033) | out-null
     
        # restart SSRS service for changes to take effect
        # Restart-Service -Name $RSConfig.ServiceName -Force
    
        # retrieve the current configuration
        $RSConfig = Get-WmiObject -Class "MSReportServer_ConfigurationSetting" -Namespace $WMI_Namespace
        # get the ReportServer and ReportServerTempDB creation script
        [string]$dbscript = $RSConfig.GenerateDatabaseCreationScript($SQL_DB_Name, 1033, $false).Script
    
        # import the SQL Server PowerShell module
        Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Force -Scope Process
        Import-Module "sqlps" -DisableNameChecking | Out-Null

        # establish a connection to the database server
        # $ErrorActionPreference = 'SilentlyContinue'
        # $RunCount = 0
        # do {
        Write-Output "$($env:COMPUTERNAME): connecting to instance $($SQL_Instance)"
        $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $SQL_Instance
        $conn.ApplicationName = "SSRS Configuration Script"
        $conn.StatementTimeout = 0
        $conn.Connect()
        # Start-Sleep -Seconds 5
        # $RunCount++
        # }
        # while ($conn.IsOpen -eq $false -and $RunCount -lt 6)
        # $ErrorActionPreference = 'Continue'

        if ($conn.IsOpen -ne $true) {
            throw "could not connect to $($SQL_Instance)"
        }
        $smo = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $conn

        # create the ReportServer and ReportServerTempDB databases
        Write-Output "$($env:COMPUTERNAME): generating databases in instance $($SQL_Instance)"
        $db = $smo.Databases["master"]
        $db.ExecuteNonQuery($dbscript)
    
        # set permissions for the databases
        $dbscript = $RSConfig.GenerateDatabaseRightsScript($RSConfig.WindowsServiceIdentityConfigured, $SQL_DB_Name, $false, $true).Script
        $db.ExecuteNonQuery($dbscript)
    
        # set the database connection info # check hresult auf wert 0
        Write-Output "$($env:COMPUTERNAME): configuring db connection for ssrs to instance $($SQL_Instance) with db $($SQL_DB_Name)"
        $RSConfig.SetDatabaseConnection($SQL_Instance, $SQL_DB_Name, 2, "", "") | Out-Null
        $RSConfig.InitializeReportServer($RSConfig.InstallationID) | Out-Null
    
        # restart services # check hresult auf wert 0
        $RSConfig.SetServiceState($false, $false, $false) | Out-Null
        Restart-Service $RSConfig.ServiceName
        $RSConfig.SetServiceState($true, $true, $true) | Out-Null
    }
    catch {
        throw "$($env:COMPUTERNAME): $($PSItem.Exception.Message)"
    }
}

function Search-SQLSummaryLog {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]
        $SearchString
    )
    
    try {
        $SummaryLogFilePath = (Get-Item -Path "$($env:ProgramFiles)\Microsoft SQL Server\*\Setup Bootstrap\Log\Summary.txt" | Sort-Object -Property LastWriteTimeUtc -Descending | Select-Object -First 1).FullName
        $Content = Get-Content -Path $SummaryLogFilePath

        if ($null -eq $Content) {
            throw "file could not be found"
        }
        else {
            $FilteredContent = $Content | Select-String -Pattern $SearchString
            if ($null -eq $FilteredContent) {
                return "no message found with selected search pattern"
            }
            else {
                $Message = $FilteredContent[0].Tostring().Replace($SearchString, "").Trim()
                if ($Message -like "*:*") {
                    $Message = $Message.Replace(":", "").Trim()
                }
                return $Message
            }
        }
    }
    catch {
        throw $PSItem.Exception.Message
    }
}

function Add-SQLDBRole {
    <#
        .Description
        this function can be used to add a role to db for an sql login
 
        .Parameter InstanceName
        name of the instance
 
        .Parameter SQLogin
        sql login name
 
        .Parameter DBName
        database name
 
        .Parameter DBRole
        role that should be granted to the sql login
 
        .Example
        # adds db_owner to local system on susdb
        Add-SQLDBRole -InstanceName "$($env:COMPUTERNAME)\$using:CM_SQL_WSUS_InstanceName" -SQLogin "NT AUTHORITY\SYSTEM" -DBName "SUSDB" -DBRole 'db_owner'
 
        .NOTES
        https://www.sqlservercentral.com/blogs/use-powershell-to-add-a-login-to-a-database-role-in-all-databases
    #>

    
    param (
        [Parameter(Mandatory = $true)]
        [string]
        $InstanceName,

        [Parameter(Mandatory = $true)]
        [string]
        $SQLogin,

        [Parameter(Mandatory = $true)]
        [string]
        $DBName,

        [Parameter(Mandatory = $true)]
        [string]
        $DBRole
    )
    
    try {
        # Load the SMO assembly
        [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO') | Out-Null

        # Connect to the instance using SMO
        $SQLServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName

        # Get the defined login - if it doesn't exist it's an error
        $SQLoginObject = $SQLServer.Logins[$SQLogin]
        if ($null -eq $SQLoginObject) {
            throw "$($SQLogin) is not a valid SQL Server Login on this instance."
        }

        $SQLLoginName = $SQLoginObject.Name
        $SQLDatabaseObject = $SQLServer.Databases[$DBName]

        # Check to see if the login is a user in this database
        $SQLUserObject = $SQLDatabaseObject.Users[$SQLLoginName]
        if ($null -eq $SQLUserObject) {
            # Not present, so add it
            $SQLUserObject = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($SQLDatabaseObject, $SQLLoginName)
            $SQLUserObject.Login = $SQLLoginName
            $SQLUserObject.Create()
        }

        # Check to see if the user is a member of the db_owner role
        if ($SQLUserObject.IsMember($DBRole) -ne $True) {
            # Not a member, so add that role
            $SQLConnection = new-object system.data.SqlClient.SqlConnection("Data Source=$($InstanceName);Integrated Security=SSPI;Initial Catalog=$($DBName)");
            $SQLConnection.Open()
            $SQLQuery = "EXEC sp_addrolemember @rolename = N'$($DBRole)', @membername = N'$($SQLLoginName)'"
            $SQLCommand = new-object "System.Data.SqlClient.SqlCommand" ($SQLQuery, $SQLConnection)
            Write-Output "$($env:COMPUTERNAME): adding db role $($DBRole) to $($SQLogin) on instance $($InstanceName) for db $($DBName)"
            $SQLCommand.ExecuteNonQuery() | out-null
            $SQLConnection.Close()
        }    
    }
    catch {
        throw "could not grant sql db role - $($PSItem.Exception.Message)"
    }
}

function Add-SQLMountPoint {
    <#
        .Description
        this function creates a mountpoint
 
        .Parameter DiskNumber
        number of disk which should be configured
 
        .Parameter SQLBasePath
        where should be mountpoint be created
 
        .Parameter DiskLabel
        label for the volume and the underlaying folders
 
        .Example
        # this checks if hyper-v is installed
        Confirm-HyperV
 
        .NOTES
         
    #>


    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]
        $DiskNumber,

        [Parameter(Mandatory = $false)]
        [string]
        $SQLBasePath = "C:\SQL\",

        [Parameter(Mandatory = $true)]
        [string]
        $DiskLabel
    )
    $MountPath = $SQLBasePath + $DiskLabel
    Write-Output "$($env:COMPUTERNAME): formating disk for $($DiskLabel)"
    Set-Disk -Number $DiskNumber -IsOffline $false
    Set-Disk -Number $DiskNumber -IsReadOnly $false
    Get-Disk -Number $DiskNumber | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -UseMaximumSize | Format-Volume -NewFileSystemLabel $DiskLabel -FileSystem ReFS -AllocationUnitSize 65536 -SetIntegrityStreams $false | Out-Null
    New-Item -ItemType Directory -Path $MountPath | Out-Null
    Get-Partition -DiskNumber $DiskNumber | Add-PartitionAccessPath -AccessPath $MountPath -ErrorAction SilentlyContinue

    if ($DiskLabel -like "*LOG*") {
        New-Item -ItemType Directory -Path "$($SQLBasePath)\$($DiskLabel)\LOG" -ErrorAction SilentlyContinue | Out-Null
    }
    else {
        New-Item -ItemType Directory -Path "$($SQLBasePath)\$($DiskLabel)\DATA" -ErrorAction SilentlyContinue | Out-Null
    }
    Start-Sleep -Seconds 2
}