install2

[CmdletBinding()]
param (

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

    [string]
    $PublicationServer,

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

    [string]
    $SubscriberServer = "",

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

    [string]
    $SubscriberLogin = "",

    [string]
    $SubscriberPassword = "",

    #Optional SQL Login
    [Parameter()]
    [string]
    $PublicationDBUsername,

    #Optional SQL Login password
    [Parameter()]
    [string]
    $PublicationDBPassword,

    [string]
    $DataFolderPath = "",

    [string]
    $LogFolderPath = "",

    [string]
    $SnapshotFolderPath = "",

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

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

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

    [Parameter()]
    [string]
    $IncludeObjects,

    [Parameter()]
    [string]
    $ExcludeObjects
)

try {

    #Clear standard out
    Clear-Host

    Set-Variable MaximumHistoryCount 32767

    if ($PublicationServer -eq "") {
        $PublicationServer = $env:ComputerName
    }

    if ($SubscriberServer -eq "") {
        $SubscriberServer = $env:ComputerName
    }

    if ($PublicationServer -eq "") {
        $PublicationServer = $env:ComputerName
    }

    if ($SubscriberServer -eq "") {
        $SubscriberServer = $env:ComputerName
    }


    Write-Host "STARTING: Install.ps1 - Here are the variables..."
    Write-Host "PRINTING VARIABLES - PublicationDB: $PublicationDB"
    Write-Host "PRINTING VARIABLES - PublicationServer: $PublicationServer"
    Write-Host "PRINTING VARIABLES - PublicationName: $PublicationName"
    Write-Host "PRINTING VARIABLES - SubscriberServer: $SubscriberServer"
    Write-Host "PRINTING VARIABLES - SubscriberDB: $SubscriberDB"
    Write-Host "PRINTING VARIABLES - SubscriberLogin: $SubscriberLogin"
    Write-Host "PRINTING VARIABLES - SubscriberPassword: $SubscriberPassword"
    Write-Host "PRINTING VARIABLES - PublicationDBUsername: $PublicationDBUsername"
    Write-Host "PRINTING VARIABLES - PublicationDBPassword: $PublicationDBPassword"
    Write-Host "PRINTING VARIABLES - LogFolderPath: $LogFolderPath"
    Write-Host "PRINTING VARIABLES - DataFolderPath: $DataFolderPath"
    Write-Host "PRINTING VARIABLES - SnapshotFolderPath: $SnapshotFolderPath"
    Write-Host "PRINTING VARIABLES - ReportingUserPassword: $ReportingUserPassword"
    Write-Host "PRINTING VARIABLES - PublicationType: $PublicationType"
    Write-Host "PRINTING VARIABLES - CreateSnapshot: $CreateSnapshot"
    Write-Host "PRINTING VARIABLES - IncludeObjects: $IncludeObjects"
    Write-Host "PRINTING VARIABLES - ExcludeObjects: $ExcludeObjects"

    if (Get-Module -ListAvailable -Name SQLServer) {
        Write-Host "SUCCESS: SQLServer Module exists"
    }
    else {
        throw "FAILURE: SQLServer Module does not exist"
    }

    #Sets up the variable array for params which might not be passed explicity

    $vars = "PublicationDB=$PublicationDB",
    "PublicationServer=$PublicationServer",
    "PublicationName=$PublicationName",
    "SubscriberServer=$SubscriberServer",
    "SubscriberDB=$SubscriberDB",
    "ReportingUserPassword=$ReportingUserPassword",
    "LogFolderPath=$LogFolderPath",
    "DataFolderPath=$DataFolderPath",
    "SnapshotFolderPath=$SnapshotFolderPath",
    "PublicationType=$PublicationType",
    "CreateSnapshot=$CreateSnapshot",
    "IncludeObjects='$IncludeObjects'",
    "ExcludeObjects='$ExcludeObjects'"

    if ($SubscriberLogin -and $SubscriberLogin -ne '' -and $SubsciberPassword -and $SubscriberPassword -ne '') {
        $vars += "SubscriberLogin=$SubscriberLogin"
        $vars += "SubscriberPassword=$SubscriberPassword"
    }

    Write-Output "VARIABLE LIST IS: $vars"

    #Server name should always be the server it's running on as the package will only exist on
    #the target box.
    Write-Output "Publication Server is: $PublicationServer"

#Set up connection strings for pub
if ($PublicationDBUsername -and $PublicationDBPassword -and $PublicationDBUsername -ne '' -and $PublicationDBPassword -ne '') {
    $ConnectionString = "Data Source=$PublicationServer;Database=$PublicationDB;Integrated Security=false;User ID=$PublicationDBUsername;Password=$PublicationDBPassword;Connection Timeout=0;Trust Server Certificate=True"
}
else {
    $ConnectionString = "Data Source=$PublicationServer;Database=$PublicationDB;Integrated Security=true;Connection Timeout=0;Trust Server Certificate=True"
}

#Set up connection strings sub
if ($SubscriberLogin -and $SubscriberPassword -and $SubscriberLogin -ne '' -and $SubscriberPassword -ne '') {
    $SubConnectionString = "Data Source=$SubscriberServer;Database=$SubscriberDB;Integrated Security=false;User ID=$SubscriberDBUsername;Password=$SubscriberPassword;Connection Timeout=0;Trust Server Certificate=True"
    #Convert Subscriber login to SecureString
    [securestring]$SecSubsPassword = ConvertTo-SecureString $SubscriberPassword -AsPlainText -Force

    #Create credentials object
    [pscredential]$SubscriberCredObject = New-Object -TypeName PSCredential -ArgumentList $SubscriberLogin, $SecSubsPassword
}
else {
    $SubConnectionString = "Data Source=$SubscriberServer;Database=$SubscriberDB;Integrated Security=true;Connection Timeout=0;Trust Server Certificate=True"
}


#This function intelligently applies table to the subscriber
function Write-SRSTableDataToSubscriber {

    param
    (
        #This is a list of schema names to apply to [dbo].[SchemaCompare_SRS] on subscriber
        [Parameter(Mandatory = $true)]
        [System.Data.DataTable]
        $TableDataInputVariable,

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

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

        [string]
        $SubscriberLogin = "",

        [string]
        $SubscriberPassword = ""
    )

    Write-Host "PRINT: TableDataVariables..."
    $TableDataInputVariable | Format-Table
    Write-Host "PRINT: SubscriberServer: $SubscriberServer"
    Write-Host "PRINT: SubscriberDB: $SubscriberDB"
    Write-Host "PRINT: SubscriberLogin: $SubscriberLogin"
    Write-Host "PRINT: SubscriberPassword: $SubscriberPassword"

    #Set up connection strings sub
    if ($SubscriberUsername -and $SubscriberPassword -and $SubscriberUsername -ne '' -and $SubscriberPassword -ne '') {
        $SubConnectionString = "Data Source=$SubscriberServer;Database=$SubscriberDB;Integrated Security=false;User ID=$SubscriberUsername;Password=$SubscriberPassword;Connection Timeout=0;Trust Server Certificate=True"
    }
    else {
        $SubConnectionString = "Data Source=$SubscriberServer;Database=$SubscriberDB;Integrated Security=true;Connection Timeout=0;Trust Server Certificate=True"
    }

    #Drop and recreate the target schema
    $DropSchemaTableSQLString = "DROP TABLE IF EXISTS [dbo].[SchemaCompare_SRS];"
    $CreateSchemaTableSQLString = "CREATE TABLE [dbo].[SchemaCompare_SRS] ([Name] NVARCHAR(255));"

    foreach ($row in $TableDataInputVariable) {
        $WriteSQLString += "INSERT INTO [dbo].[SchemaCompare_SRS] ([Name]) VALUES ('$($row[0].ToString())');"
    }

    Write-Output "The SQL Schema compare string to run is..."
    Write-Output $DropSchemaTableSQLString
    Write-Output $CreateSchemaTableSQLString
    Write-Output $WriteSQLString

    try {

        #Write schema rows to subscriber
        Write-Output "STARTING: SRS WriteTable"

        Invoke-SqlCmd -ConnectionString $SubConnectionString -Query $DropSchemaTableSQLString -Verbose -ErrorAction Stop
        Invoke-SqlCmd -ConnectionString $SubConnectionString -Query $CreateSchemaTableSQLString -Verbose -ErrorAction Stop
        Invoke-SqlCmd -ConnectionString $SubConnectionString -Query $WriteSQLString -Verbose -ErrorAction Stop

        Write-Output "SUCCESS: SRS WriteTable"
    }
    catch {
        throw
    }
}

#Get publisher collation
$PublisherQuery = "SELECT [collation_name] AS Collation FROM [sys].[databases] WHERE name = '$PublicationDB'"

#Query run at publisher
Write-Output "Starting Running query on publisher: $PublisherQuery"
Write-Output "Command is: $PublicationCollation"
$PublicationCollation = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $PublisherQuery -Verbose -Querytimeout 0
Write-Output "Finished Running query on publisher"

#Get subscriber collation

$ObjectExecuted = $false
$SubscriberQuery = "SELECT [collation_name] AS Collation FROM [sys].[databases] WHERE name = '$SubscriberDB'"

#Query run at subscriber
if ($SubscriberLogin -and $SubscriberPassword -and $SubscriberLogin -ne '' -and $SubscriberPassword -ne '') {
    Write-Output "Starting Running query on subscriber SQL Auth: $SubscriberQuery"
    Write-Output "Invoke-Sqlcmd -ServerInstance $SubscriberServer -Database $SubscriberDB -Credential $SubscriberCredObject -Query $SubscriberQuery -Verbose -Querytimeout 0 -ErrorAction Stop"
    $SubscriptionCollation = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $SubscriberQuery -Verbose -Querytimeout 0 -ErrorAction Stop
    Write-Output "Finished Running query on subscriber"
}
else {
    Write-Output "Starting Running query on subscriber NTLM auth: $SubscriberQuery"
    Write-Output "Invoke-Sqlcmd -ServerInstance $SubscriberServer -Database $SubscriberDB -Query $SubscriberQuery -Verbose -Querytimeout 0 -ErrorAction Stop"
    $SubscriptionCollation = (Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $SubscriberQuery -Verbose -Querytimeout 0 -ErrorAction Stop)
    Write-Output "Finished Running query on subscriber"
}
$ObjectExecuted = $false
$SubscriberQuery = "SELECT [collation_name] AS Collation FROM [sys].[databases] WHERE name = '$SubscriberDB'"

#Query run at subscriber
if ($SubscriberLogin -and $SubscriberPassword -and $SubscriberLogin -ne '' -and $SubscriberPassword -ne '' -and !$ObjectExecuted) {
    Write-Output "Starting Running query on subscriber SQL Auth: $SubscriberQuery"
    Write-Output "Invoke-Sqlcmd -ServerInstance $SubscriberServer -Database $SubscriberDB -Credential $SubscriberCredObject -Query $SubscriberQuery -Verbose -Querytimeout 0 -ErrorAction Stop"
    $SubscriptionCollation = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $SubscriberQuery -Verbose -Querytimeout 0 -ErrorAction Stop
    Write-Output "Finished Running query on subscriber"
    $ObjectExecuted = $true
}
elseif (!$ObjectExecuted) {
    Write-Output "Starting Running query on subscriber NTLM auth: $SubscriberQuery"
    Write-Output "Invoke-Sqlcmd -ServerInstance $SubscriberServer -Database $SubscriberDB -Query $SubscriberQuery -Verbose -Querytimeout 0 -ErrorAction Stop"
    $SubscriptionCollation = (Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $SubscriberQuery -Verbose -Querytimeout 0 -ErrorAction Stop)
    Write-Output "Finished Running query on subscriber"
    $ObjectExecuted = $true
}

#Assign collation to variable
$PublisherCollation = $PublicationCollation.Collation
$SubscriberCollation = $SubscriptionCollation.Collation

Write-Output "Publisher collation is: $PublisherCollation"
Write-Output "Subscriber collation is: $SubscriberCollation"

#Compare collation
if ($PublisherCollation -ne $SubscriberCollation) {
    Write-Error -Message "FAILURE: Subscriber database has different collation than publisher."
    break
}


Write-Output "FINISHED: Getting collations.."

Write-Output "STARTING: Getting schemas.."
#Get DB schema from publisher and write it to a table on subscriber

$SchemaQuery = "SELECT [name] FROM [sys].[schemas]"

#Query run at publisher SQL Login
if ($PublicationDBUsername -and $PublicationDBPassword -and $PublicationDBUsername -ne '' -and $PublicationDBPassword -ne '') {

    Write-Output "Starting Running query on publisher SQL Auth and save results on subcriber: $SchemaQuery"
    $TableDataInputVariable = (Invoke-Sqlcmd -ServerInstance $PublicationServer -Database $PublicationDB -Username $PublicationDBUsername -Password $PublicationDBPassword -Query $SchemaQuery -OutputAs "DataTables" -Verbose -Querytimeout 0 -ErrorAction Stop)
    Write-Output "Finished Running query"
}

#Query run at publisher NTLM Login
else {

    Write-Output "Starting Running on Publisher NTLM Auth and save results on subcriber: $SchemaQuery"
    $TableDataInputVariable = (Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $SchemaQuery -OutputAs "DataTables" -Verbose -Querytimeout 0 -ErrorAction Stop)
    Write-Output "Finished Running query"
}

if ($SubscriberLogin -and $SubscriberPassword -and $SubscriberLogin -ne '' -and $SubscriberPassword -ne '') {

    Write-SRSTableDataToSubscriber -TableDataInputVariable $TableDataInputVariable -SubscriberServer $SubscriberServer -SubscriberDB $SubscriberDB -SubscriberLogin $SubscriberLogin -SubscriberPassword $SubscriberPassword

}
else {
    Write-SRSTableDataToSubscriber -TableDataInputVariable $TableDataInputVariable -SubscriberServer $SubscriberServer -SubscriberDB $SubscriberDB
}

# Create the snapshot folder if it doesn't exist
if (-not (Test-Path -Path $SnapshotFolderPath)) {
    New-Item -Path $SnapshotFolderPath -ItemType Directory -ErrorAction Stop
    Write-Output "Successfully created snapshot folder: $SnapshotFolderPath"
}

#Get the scripts
Write-Output "Retrieving Scripts..."

#**************************
#NB - Filenames containing RAS are run on the sunscriber!
#**************************


# Read the contents of the order file into an array of strings
# Set the path to the order.txt file relative to the script location
$OrderFileLocation = "$PSScriptRoot\..\sql\install_order.txt"

#Get script names from the orderfile
$Filenames = Get-Content -path $OrderFileLocation
write-host $filenames

$Filenames | ForEach-Object {

    $CurrentFileName = $_.ToString()

    $scriptfile = join-path "$PSScriptRoot\..\sql\" $CurrentFileName -Resolve

    Write-Output "STARTING: Enter the Scriptfile section..."
    Write-Output "STARTING: Variables are: $vars"

    #file is run at the publisher
    if ($scriptfile -like "*SETUP*" -and $scriptfile -notlike "*RAS*") {
        Write-Output "Starting Running on Publisher: $scriptfile"
        Invoke-Sqlcmd -ConnectionString $ConnectionString -Inputfile $scriptfile -Variable $vars -Verbose -Querytimeout 0 -ErrorAction Stop
        Write-Output "Finished Running: $scriptfile"
    }
    #File is run at subscriber
    elseif ($Scriptfile -like "*SETUP*" -and $scriptfile -like "*RAS*" ) {
        Write-Output "Starting Running on subscriber: $scriptfile"
        Invoke-Sqlcmd -ConnectionString $SubConnectionString -Inputfile $scriptfile -Variable $vars -Verbose -Querytimeout 0 -ErrorAction Stop
        Write-Output "Finished Running: $scriptfile"
    }
}


Write-Output "SUCCESS: Scriptfile section complete..."



Write-Output 'SRS Install Complete'

catch {

    Write-Output $_
    Write-Host $_.ScriptStackTrace
    Write-Error -Message "Building variable list failed" -ErrorAction Stop
    throw
    break
}