PowerShell: Automating Dynamics CRM Migration

This is the source code that has been used to move hundreds of Dynamics CRM Organizations (Orgs) versions 8.x & 9.x from self-hosted environments to cloud servers running ‘Microsoft Dynamics 365 Customer Engagement’ – this has not been tested toward destination of ‘Microsoft Dynamics 365’ (the cloud version).

###########################################################################################################
# Automating Dynamics CRM Migration
###########################################################################################################
# Requirements:
# Credentials:
#  - Server Administrator (usually a member of Domain Admins)
#  - Source Application Administrator 
#  - Destination Application Administrator
#  - Source SQL Admin (domain account recommended - not 'sa')
#  - Destination SQL Admin (domain account recommended - not 'sa')
# Firewall & Services
#  - WinRM reachability between Application Servers and SQL Servers
#  - PowerShell Execution Policy is Unrestricted or ByPass on Migrating Nodes

###########################################################################################################
# Migration Steps:
# 0) Reverse lookup of POD number from an URL
# 1) Pick an Org
# 2) Obtain Email router password and disable the Org
# 3) Disable Source Org Deployment
# 4) Disable integration
# 5) Update Fedederation metadata
# 6) Update Local Hosts files
# 7) Ship the database to its corresponding destination
# 8) Update Host Record on Local DNS
# 9) Update Host Record on Public DNS
# 10) Perform CRM import at Destination App Server
# 11) Update Idf at destination ADFS
# 12) Recreate Email Router at destination App Server
# 13) Validation: integration, environment admin login, and domain admin login
###########################################################################################################

###############################################################################################################
# 0) Reverse lookup of POD number from Org URL
# Before running migration, it's necessary to connect to the correct environment
###############################################################################################################

# User input variables
$GLOBAL:orgName='testOrg'
$GLOBAL:sourceServers='DEV-APP01','DEV-ADFS','DEV-ROUTER01','DEV-DC01'
$GLOBAL:destinationServers='CRM-APP01','LAX-ADFS01','LAX-ROUTER01','LAX-DC04'

# This function ensures the dynamic variables are generated in the correct sequence, lest they would be invalid
function setGlobalVariables{

$GLOBAL:emailUser='crmAdmins@kimconnect.com'
$GLOBAL:emailPass='PASSWORD'
$GLOBAL:sendEmailTo='1714XXXXXXX@tmomail.net' #9495551212@vtext.com
$GLOBAL:copy='techadmins@kimconnect.com'
$GLOBAL:emailPort=587
$GLOBAL:smtp='smtp.gmail.com'
$GLOBAL:emailSubject="This task has completed: "
$GLOBAL:emailBody="This is a workflow email.<br><br>Please respond accordingly."
#sendEmail $emailUser $emailPass $sendEmailTo $copy $emailSubject $emailBody

$selectedOrg=getOrg $orgName
$GLOBAL:orgName=$selectedOrg['orgName']
$GLOBAL:friendlyName=$selectedOrg['friendlyName']
$GLOBAL:databaseName=$selectedOrg['databaseName']
$GLOBAL:orgId=$selectedOrg['orgId'] # Manual entry for the spreadsheet
$GLOBAL:sourceSqlServer=$selectedOrg['sqlServer'] # Database shipping
$GLOBAL:reportServer=$selectedOrg['srsUrl'] # CRM Import
$GLOBAL:version=$selectedOrg['version'] # Version checking
$GLOBAL:state=$selectedOrg['state'] 

# Set logFile
$dateStamp=[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId( (Get-Date), 'Pacific Standard Time').tostring("MM-dd-yyyy-HHmm")+'_PST'
$GLOBAL:logFile="C:\migrationLogs\$orgName`_Migration_$dateStamp.txt"

# Define servers
$GLOBAL:sourceAppServer=$sourceServers[0]
$GLOBAL:destinationAppServer=$destinationServers[0]

$GLOBAL:credentials=@{
        'kimconnect\Administrator'='PASSWORD';
        'kimconnect\crmAdministrator'='PASSWORD';
        'kimconnect\crm'='PASSWORD';
        }

# Server Administrator Credential
$GLOBAL:serverAdminUsername='kimconnect\adminAccount'
$GLOBAL:serverAdminPassword=$credentials[$serverAdminUsername]
$GLOBAL:adminCredential=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $serverAdminUsername,$(ConvertTo-securestring $serverAdminPassword -AsPlainText -Force)

# Update Federation Metadata at the Destination
$GLOBAL:domain='kimconnect.com'
$GLOBAL:destinationUrl="https://$orgName.$domain"
#$GLOBAL:destinationEnvironment='dev'
$GLOBAL:destinationAdfs=$destinationServers[1]

# Variables to Update Federation Metadata at the Source
#$GLOBAL:domain='kimconnect.com'
$GLOBAL:sourceUrl="https://$orgName.$domain"
$GLOBAL:sourceEnvironment=getEnvironment $sourceUrl
$GLOBAL:sourceAdfs=$sourceServers[1]

# CRM Admin credential
$GLOBAL:environment=getEnvironment $sourceUrl
$GLOBAL:crmAdmin="kimconnect\kimconnect$environment"
$GLOBAL:crmPassword=$credentials[$crmAdmin]
#$GLOBAL:crmCredential=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $crmAdmin,$(ConvertTo-securestring $crmPassword -AsPlainText -Force)
$GLOBAL:sourceCrmAdmin="kimconnect\kimconnect$sourceEnvironment"
$GLOBAL:sourceCrmPassword=$credentials[$sourceCrmAdmin]
#$GLOBAL:sourceCrmCredential=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sourceCrmAdmin,$(ConvertTo-securestring $sourceCrmPassword -AsPlainText -Force)
$GLOBAL:destinationCrmAdmin="kimconnect\kimconnect$destinationEnvironment"
$GLOBAL:destinationCrmPassword=$credentials[$destinationCrmAdmin]
#$GLOBAL:destinationCrmCredential=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $destinationCrmAdmin,$(ConvertTo-securestring $destinationCrmPassword -AsPlainText -Force)

# Variables for Integration change
$GLOBAL:integrationServer=$sourceServers[2]
$GLOBAL:integrationFile='\\DEV-MAXIMUS01\C$\Program Files\Sync360\Applications\Instances.xml'
$GLOBAL:suffixEnv='prod'
$GLOBAL:matchString="$orgName@$suffixEnv"

# Variables for host files updating function
# Special considration: Update Local Hosts Files at the Source Servers
# At this time, ADFS01 of LUME
#$orgName=''
$GLOBAL:domain='kimconnect.com'
$GLOBAL:searchString="#$sourceEnvironment deployment"
$GLOBAL:newAppServer=$destinationServers[0]
$GLOBAL:appLocalIp=(Test-Connection $newAppServer -count 1).IPV4Address.IPAddressToString;

# Variables for database shipping function
# Set database name
#$databaseName=$orgName+'_MSCRM' # should already been set prior
$GLOBAL:dbData='mscrm' # These values pertain specifically to Microsoft Dynamics CRM
$GLOBAL:dbLog='mscrm_log'
$GLOBAL:overWriteFlag=$true
$GLOBAL:sourceEnvironment=.{[void]($sourceAppServer -match '^(.+)\-');$matches[1]} # should already been set prior
#$GLOBAL:sourceSqlServer=$sourceEnvironment+'-sql01.kimconnect.com'
$GLOBAL:sourceSa="kimconnect\crmAdministrator$environment"
$GLOBAL:sourceSaPassword=$credentials[$sourceSa]
$GLOBAL:destinationEnvironment=.{[void]($destinationAppServer -match '^(.+)\-');$matches[1]}
$GLOBAL:destinationSqlServer=$destinationEnvironment+'-sql01.kimconnect.com'
$GLOBAL:destinationSa="kimconnect\crmAdministrator$destinationEnvironment"
$GLOBAL:destinationSaPassword=$credentials[$destinationSa]
$GLOBAL:destinationSaCred=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $destinationSa,$(ConvertTo-securestring $destinationSaPassword -AsPlainText -Force)

# V8 to V9 Considerations
$GLOBAL:objectName='[dbo].[PrivilegeObjectTypeCodeView]'
$GLOBAL:objectType='view'
$GLOBAL:fromObject='[dbo].[PrivilegeObjectTypeCodes]'

# Private DNS - Active Directory
# DNS Host Record Information
# Dynamic variable
$GLOBAL:aRecord=$orgName.tolower()
$GLOBAL:zoneName='kimconnect.com'
$GLOBAL:recordName=$aRecord+'.'+$zoneName
$GLOBAL:dnsServer=$destinationServers[3]
$regexIpv4 = "\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b" 
$GLOBAL:recordIP=.{$ipResult=[System.Net.Dns]::GetHostAddresses($destinationServers[0]).IPAddressToString|?{$_ -match $regexIpv4 -and $_ -notmatch "^169."}
            if($ipResult.gettype() -eq [string]){return $ipResult}else{return $ipResult[0]}}

# Public DNS
$GLOBAL:publicDnsUrl='https://gator9000.hostgator.com:2083'
$GLOBAL:ipDictionary=@{
                'dev-web01'='1.1.1.1';
                'CRM-web01'='2.2.2.2';
                }
$GLOBAL:oldPublicIp=$ipDictionary[$sourceEnvironment]
$GLOBAL:newPublicIP=$ipDictionary[$destinationEnvironment]

# CRM Import
#$GLOBAL:reportServer="http://$destinationEnvironment-async01/ReportServer"

# Validation
$GLOBAL:testUsers=@("kimconnect\crmAdminstrator$destinationEnvironment",'kimconnect\crmAdminstrator','kimconnect\crmAdminstrator')

# Email Router
$GLOBAL:emailRouterUrl="https://dev-$destinationEnvironment.$domain/$orgName"

# Data Encryption
$GLOBAL:dataEncryptionUrl="https://$orgName.$domain/tools/sqlencryption/sqlencryption.aspx"
#$GLOBAL:crmAdmin='kimconnect\crmAdminstrator'
#$GLOBAL:crmAdminPassword=$credentials[$crmAdministrator]

# Password Safe
$GLOBAL:secRepoUrl='https://passwordsafe.kimconnect.com/'

# SSRS Custom Reports
$GLOBAL:sourceReportingUrl="http://$sourceEnvironment-async01/Reports/browse/$orgName`_MSCRM"
$GLOBAL:destinationReportingUrl="http://$destinationEnvironment-async01/Reports/browse/$orgName`_MSCRM"
$GLOBAL:sourceReportLoginId="kimconnect\crmAdminstrator$sourceEnvironment"
$GLOBAL:sourceReportPassword=$credentials[$sourceReportLoginId]
$GLOBAL:destinationReportLoginId="kimconnect\crmAdminstrator$destinationEnvironment"
$GLOBAL:destinationReportPassword=$credentials[$destinationReportLoginId]

# Documentation
$GLOBAL:documentationUrl='https://wiki.kimconnect.com/'

# PowerShell version
$GLOBAL:isPowerShellVersion5=$PSVersionTable.PSVersion.Major -ge 5
}

# To determine which host to initiate the migration, it's adviseable to run this function prior from a workstation
#$targetUrl='test.kimconnect.com'
function getEnvironment($url){
    $regexDomain='(http[s]:\/{2}){0,1}([\d\w\.-]+)\/{0,1}'
    $innerUrl=.{[void]($url -match $regexDomain);$matches[2]}
    #$publicIP=[System.Net.Dns]::GetHostAddresses($domain)[0].IPAddressToString
    $publicIP=(Resolve-DnsName -Name $innerUrl -Server '8.8.8.8' -NoHostsFile)[0].IPAddress    
    $ipDictionary=@{
                'DEV-APP01'='1.1.1.1';
                'CRM-APP01'='2.2.2.2';
                }
    $environment=$ipDictionary.keys | Where-Object {$ipDictionary["$_"] -eq $publicIP}
    if ($environment){
        #write-host "Environment is: $environment";
        return $environment
        }
    else{write-warning "No environments were matched.";return $false}
}
#getEnvironment $targetUrl

###############################################################################################################

# This function sets these variables: $orgName, $friendlyName, $databaseName, and $logFile
# This function can be skipped if variables are to be set manually
function appendContent($file,$text){
    if(!(test-path $file)){
        $folder=Split-Path $file -Parent
        $name=Split-Path $file -Leaf
        New-Item -itemType File -Path $folder -Name $name -Force
        }
    Add-Content -path $file -value $text -PassThru
    }
function selectOrg {
    function pickList($list){
    # Although it's more efficient to obtain the index and set it as display,
    # humans prefer see a list that starts with 1, instead of 0
    $display=for ($i=0;$i -lt $list.count;$i++){
        "$($i+1)`:`t$($list[$i])`r`n";
        }
    $lines=($display | Measure-Object -Line).Lines
    write-host $($display)
    $maxAttempts=3
    $attempts=0;
    while ($attempts -le $maxAttempts){
        if($attempts++ -ge $maxAttempts){
            write-host "Attempt number $maxAttempts of $maxAttempts. Exiting loop..`r`n"
            break;
            }
        $userInput = Read-Host -Prompt 'Please pick a number from the list above';
        try {
            $value=[int]$userInput;
            }catch{
                $value=-1;
                }
        if ($value -lt 1 -OR $value -gt $lines){
            cls;
            write-host "Attempt number $attempts of $maxAttempts`: $userInput is an invalid value. Try again..`r`n"
            write-host $display
            }else{
                $item=$list[$value-1];
                write-host "$userInput corresponds to $item`r`n";
                return $item
                }
        }     
    }
    # Set Org name and database name - run this prior to pasting the rest of the lines below
    if(!(get-command get-crmorganization -ea SilentlyContinue)){Add-PSSnapin Microsoft.Crm.PowerShell}
    $orgs=Get-CrmOrganization|?{$_.State -ne 'Disabled'}
    try{$orgsValue=$orgs.gettype().Name}
    catch{write-warning "No Orgs detected on $env:computername. Exiting Program"; break;}
    if ($orgsValue -ne 'Organization'){
        $orgNames=$orgs.UniqueName|sort
        $orgName=$(pickList $orgNames)
        }
    else{$orgName=$orgs.UniqueName}
    $pickedOrg=$orgs|?{$_.UniqueName -eq $orgName}
    return @{
            'orgName'=$pickedOrg.UniqueName
            'friendlyName'=$pickedOrg.FriendlyName
            'sqlServer'=$pickedOrg.SqlServerName
            'databaseName'=$pickedOrg.DatabaseName
            'srsUrl'=$pickedOrg.SrsUrl
            'orgId'=$pickedOrg=$org.Id
            'version'=$pickedOrg.Version
            'state'=$pickedOrg.State
            }
}

function getOrg ($orgName){    
    if(!(get-command get-crmorganization -ea SilentlyContinue)){Add-PSSnapin Microsoft.Crm.PowerShell}
    $org=Get-CrmOrganization|?{$_.UniqueName -eq $orgName}
    if(!$org){
        write-warning "$orgName not found. Please try selecting from this list."
        return selectOrg
    }else{
        return @{
            'orgName'=$org.UniqueName
            'friendlyName'=$org.FriendlyName
            'sqlServer'=$org.SqlServerName
            'databaseName'=$org.DatabaseName
            'srsUrl'=$org.SrsUrl
            'orgId'=$orgId=$org.Id
            'version'=$org.Version
            'state'=$org.State
            }
    }
}

# 1) Obtain Email router password and disable the Org
function sendKeysToProgram($programExe,$programTitle,$sendkeys,$waitSeconds){    
    $processes=get-process
    $processStarted=$programExe -in $processes.Path
    if($processStarted){
        $processMatch=$processes|?{$programExe -eq $_.Path}
        stop-process $processMatch -Force
        }    
    start-process $programExe|out-null
    $wshell = New-Object -ComObject wscript.shell;
    $wshell.AppActivate($programTitle)|out-null
    sleep $waitSeconds
    $wshell.SendKeys($sendkeys)    
    }

function getEmailRouter($orgName){	
    # Set CRM bin folder according to its discovered location on this host
    $possibleCrmPaths=@(
        'C:\Program Files\Microsoft Dynamics CRM\CRMWeb\bin',
        'C:\Program Files\Dynamics 365\CRMWeb\bin',
        'D:\Program Files\Microsoft Dynamics CRM\CRMWeb\bin',
        'E:\Program Files\Microsoft Dynamics CRM\CRMWeb\bin'
        )
    $possibleCrmEncryptionPaths=@(
        'C:\Program Files\Microsoft CRM Email\Service\EncryptionKey.xml',
        'D:\Program Files\Microsoft CRM Email\Service\EncryptionKey.xml',
        'E:\Program Files\Microsoft CRM Email\Service\EncryptionKey.xml'
    )
    $possibleEmailAgentPaths=@(
        'C:\Program Files\Microsoft CRM Email\Service\Microsoft.Crm.Tools.EmailAgent.xml',
        'D:\Program Files\Microsoft CRM Email\Service\Microsoft.Crm.Tools.EmailAgent.xml',
        'E:\Program Files\Microsoft CRM Email\Service\Microsoft.Crm.Tools.EmailAgent.xml'
    )    
    $crmPath=.{$possibleCrmPaths|%{if(Test-Path $_){return $_}}}
    $crmEncryptionXml=.{$possibleCrmEncryptionPaths|%{if(Test-Path $_){return $_}}}
    $emailAgentFile=.{$possibleEmailAgentPaths|%{if(Test-Path $_){return $_}}}
    if($crmPath){
	    Add-Type -Path "$crmPath\Microsoft.Xrm.Service.dll"
	    Add-Type -Path "$crmPath\Microsoft.Crm.dll"
	    $encryption = New-Object -TypeName Microsoft.Crm.Encryptor -ArgumentList $crmEncryptionXml
        [xml]$emailRouterXml = Get-Content $emailAgentFile
	    $emailRouterConfig = $emailRouterXml.Configuration.ProviderConfiguration|?{$_.CrmServerUrl -match "/$orgName$"}
        if($emailRouterConfig){
            if($emailRouterConfig.count -gt 1){write-warning "There are $($emailRouterConfig.count) records"}
            $result=$emailRouterConfig|Select EmailAuthMode,EmailUser, `
                                            @{Name="emailPassword";e={$encryption.Decrypt($_.EmailPassword)}}, `
                                            @{Name="smtpServer";e={$_.Target}}, `                                            
                                            @{Name="port";e={$_.EmailPort}}, `
                                            @{Name="useSsl";e={$_.EmailUseSSL}}     
            return $result
            }
        else{
            write-host "No email router records found for $orgName"
            return $null
            }
    }else{
	    Write-Host "CRM path was not found."
        return $false
	    }
}

function sendEmail{
    [CmdletBinding()]
    param(
    [Parameter(Mandatory)][string]$emailFrom,
    [Parameter(Mandatory)][string]$emailPassword,
    [Parameter(Mandatory)][string[]]$emailTo,    
    [Parameter(Mandatory=$false)][string[]]$cc,
    [Parameter(Mandatory=$false)]$subject="Test Email to Validate SMTP",
    [Parameter(Mandatory=$false)]$body="This is a test email.<br><br>Please disregard",
    [Parameter(Mandatory=$false)]$smtpServer=$null,
    [Parameter(Mandatory=$false)]$port=587,
    [Parameter(Mandatory=$false)]$attachments,
    [Parameter(Mandatory=$false)]$useSsl=$true,
    [Parameter(Mandatory=$false)]$anonymous=$false
    ) 
    $commonSmtpPorts=@(25,587,465,2525)
    function Check-NetConnection($server,$port,$timeout=100,$verbose=$false) {
        $tcp = New-Object System.Net.Sockets.TcpClient;
        try {
            $connect=$tcp.BeginConnect($server,$port,$null,$null)
            $wait = $connect.AsyncWaitHandle.WaitOne($timeout,$false)
            if(!$wait){
                $tcp.Close()
                if($verbose){
                    Write-Host "Connection Timeout" -ForegroundColor Red
                    }
                Return $false
            }else{
                $error.Clear()
                $null=$tcp.EndConnect($connect) # Dispose of the connection to release memory
                if(!$?){
                    if($verbose){
                        write-host $error[0].Exception.Message -ForegroundColor Red
                        }
                    $tcp.Close()
                    return $false
                    }
                $tcp.Close()
                Return $true
            }
        } catch {
            return $false
        }
    }

    function getMxRecord($emailAddress){
        $regexDomain="\@(.*)$"
        $domain=.{[void]($emailAddress -match $regexDomain);$matches[1]}
        $mxDomain=(resolve-dnsname $domain -type mx|sort -Property Preference|select -First 1).NameExchange
        $detectedSmtp=switch -Wildcard ($mxDomain){ # need to build up this list
                                "*outlook.com" {"smtp.office365.com";break}
                                "*google.com" {"smtp.gmail.com";break}
                                "*yahoodns.net" {'smtp.mail.yahoo.com';break}
                                "*inbox.com" {'my.inbox.com;break'}
                                "*mail.com" {'smtp.mail.com';break}
                                "*icloud.com" {'smtp.mail.me.com';break}
                                "*zoho.com" {'smtp.zoho.com';break}
                                default {$mxDomain}
                            }
        if($mxDomain){
            write-host "Detected MX Record`t: $mxDomain`r`nKnown SMTP Server`t: $detectedSmtp"
            return $detectedSmtp
            }
        else{
            write-warning "MX record not available for $emailAddress"
            return $null
            }
    }
    
    if($emailFrom -match '@' -and $emailPassword){
        $encryptedPass=ConvertTo-SecureString -String $emailPassword -AsPlainText -Force
        $emailCred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $emailFrom,$encryptedPass
    }elseif($anonymous){
        $nullPassword = ConvertTo-SecureString 'null' -asplaintext -force
        $emailCred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList 'NT AUTHORITY\ANONYMOUS LOGON', $pass
    }elseif($emailPassword){
        $emailCred=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $emailFrom,$emailPassword
        [string]$emailFrom=$emailTo|select -First 1
    }else{
        $emailCred=$false
        }

    $detectedSmtpServer=if($emailFrom -match '@' -and !$anonymous){getMxRecord $emailFrom}else{$null}
    $smtpServer=if($smtpServer){
                    if($smtpServer -eq $detectedSmtpServer){
                        Write-host "Detected SMTP server matches the provided value: $smtpServer"
                    }else{
                        write-warning "Detected SMTP server $detectedSmtpServer does not match given values. Program will use the provided value: $smtpServer"
                        }
                    $smtpServer
                }else{
                    write-host "Using detected SMTP server $detectedSmtpServer"
                    $detectedSmtpServer
                    }
    
    $secureSmtpParams = @{        
        From                       = $emailFrom
        To                         = $emailTo
        Subject                    = $subject
        Body                       = $body
        BodyAsHtml                 = $true
        DeliveryNotificationOption = 'OnFailure','OnSuccess'
        Port                       = $port
        UseSSL                     = $useSsl
    }

    $relaySmtpParams=@{
        From                       = $emailFrom
        To                         = $emailTo
        Subject                    = $subject
        Body                       = $body
        BodyAsHtml                 = $true
        DeliveryNotificationOption = 'OnFailure', 'OnSuccess'
        Port                       = 25
        UseSSL                     = $useSsl
    }

    if ($port -ne 25){
        write-host "Secure SMTP Parameters detected."
        $emailParams=$secureSmtpParams
    }else{
        write-host "Unsecured SMTP Parameters detected."
        $emailParams=$relaySmtpParams
        }
    write-host "$($emailParams|out-string)"

    try{
        $sendmailCommand="Send-MailMessage `@emailParams -SmtpServer $smtpServer $(if($cc){"-cc $cc"}) $(if($emailCred){"-Credential `$emailCred"}) $(if($attachments){"-Attachments `$attachments"}) -ErrorAction Stop"
        write-host $sendmailCommand
        Invoke-Expression $sendmailCommand
        write-host "Email has been sent to $emailTo successfully"
        return $true;
        }
    catch{
        #$errorMessage = $_.Exception.Message
        #$failedItem = $_.Exception.ItemName 
        #write-host "$errorMessage`r`n$failedItem" -ForegroundColor Yellow      
        Write-Warning "Initial attempt failed!`r`n$_`r`nNow scanning open ports..."
        $openPorts=$commonSmtpPorts|?{Check-NetConnection $smtpServer $_}                
        write-host "$smtpServer has these SMTP ports opened: $(if($openPorts){$openPorts}else{'None'})"
        if($detectedSmtpServer -ne $smtpServer){
            try{
                write-host "Program now attempts to use the detected SMTP Server: $detectedSmtpServer"
                Invoke-Expression "Send-MailMessage `@emailParams -SmtpServer $detectedSmtpServer $(if($attachments){"-Attachments $attachments"}) -ErrorAction Stop"
                write-host "Email has been sent to $emailTo successfully via alternative SMTP Server: $detectedSmtpServer" -ForegroundColor Green
                return $true;
            }catch{
                write-host $error[0].Exception.Message -ForegroundColor Yellow
                return $false
                }
        }else{
            return $false
            }        
        }

}

# 2) Disable Source Org Deployment
function disableOrg($appServer,$orgName,$adminCredential,$logFile){
    #$session=new-pssession $appServer -Credential $adminCredential
    
    $job=Start-Job -ScriptBlock {
        param($orgName)
        write-host "running as: $env:username"
        try{
            Add-PSSnapin Microsoft.Crm.PowerShell
            Disable-CrmOrganization -Name $orgname -ea Stop
            return $true
            }
        catch{
            write-warning "$error"
            return $false
            }
    } -Credential $adminCredential -Args $orgName
    Wait-Job $job|out-null 
    $jobResult=Receive-Job $job  

    #$disabled=Invoke-Command -ComputerName $appServer -Credential $adminCredential -ScriptBlock{
    #        param($orgName)
    #        try{
    #            Disable-CrmOrganization -Name $orgname -ea Stop
    #            return $true
    #            }
    #        catch{
    #            write-warning "$error"
    #            return $false
    #        }
    #    } -Args $orgName
    if(!$disabled){
        write-warning "$orgName cannot be disabled via PowerShell; hence, one must perform this step via GUI"
        pause
        $programExe="C:\Program Files\Dynamics 365\Tools\Microsoft.Crm.DeploymentManager.exe"
        $programTitle='Dynamics 365 Deployment Manager'
        $sendKeys='{DOWN}{DOWN}{TAB}'
        sendKeysToProgram $programExe $programTitle $sendKeys 5
        }
    appendContent $logFile "$(get-date)`t: $orgname disabled"
    #Remove-PSSession $session

    # Invoke-command it to work around this error
    #Disable-CrmOrganization : Source        : mscorlib
    #Method  : HandleReturnMessage
    #Date    : 8:57:28 PM
    #Time    : 6/12/2020
    #Error   : Message: The Deployment Service cannot process the request because one or more validation checks failed.
    #ErrorCode: -2147167645
    #Stack Trace     :
    #======================================================================================================================
    #Inner Exception Level 1 :
    #==DeploymentServiceFault Info==========================================================================================
    #Error   : The Deployment Service cannot process the request because one or more validation checks failed.
    #Time    : 6/13/2020 12:57:28 AM
    #ErrorCode       : -2147167645
    #Date    : 8:57:28 PM
    #Time    : 6/12/2020
    #Error Items:
    #        ActiveDirectoryRightsCheck raising error : The current user does not have required permissions (read/write) for
    #the
    #following Active Directory group: CN=ReportingGroup ,OU=CRM Security
    #Groups,DC=intranet,DC=domain,DC=com
    #        SysAdminCheck raising error : You do not have sufficient permission to perform this operation on the specified
    #organization database
    #======================================================================================================================
    #At line:1 char:1
    #+ Disable-CrmOrganization -Name 'Something'
    #+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    #    + CategoryInfo          : InvalidData: (Microsoft.Crm.P...anizationCmdlet:DisableCrmOrganizationCmdlet) [Disable-C
    #   rmOrganization], FaultException`1
    #    + FullyQualifiedErrorId : CRM Deployment Cmdlet Error,Microsoft.Crm.PowerShell.DisableCrmOrganizationCmdlet
}
#disableOrg $sourceAppServer $orgName $adminCredential $logFile

# 3) Disable integration
function disableIntegration($integrationServer,$adminCredential,$integrationFile,$matchString,$logFile){
    $disableIntegration=invoke-command -ComputerName $integrationServer -Credential $adminCredential {
            param($file,$match)
            $content=get-content $file
            $matchLine=$content -match $match
            if($matchLine){
                $replace=($matchLine -replace '\<add\>','<!--add>') +' -->'
                if($replace){
                    $updatedContent=$content -replace $matchLine,$replace
                    Rename-Item -Path $file -NewName "$(split-path $file -leaf)`.bak"
                    $updatedContent|set-content -path $file -Force
                    write-host "$updatedContent has been updated successfully."
                    return $true
                }else{
                    write-host "No update required for $matchLine"
                    return $false
                    }
            }else{
                write-host "no matches found with keyword: $match"
                return $false
                }
        } -args $integrationFile,$matchString
    appendContent $logFile "$(get-date)`t: integration disabled = $disableIntegration"
}
#disableIntegration $integrationServer $adminCredential $integrationFile $matchString $logFile
function enableIntegration($integrationServer,$adminCredential,$integrationFile,$matchString,$logFile){
    $enableIntegration=invoke-command -ComputerName $integrationServer -Credential $adminCredential {
            param($file,$match)
            $content=get-content $file
            $matchLine=$content -match $match
            if($matchLine){
                $replace=$matchLine -replace '!--',''
                if($replace){
                    $updatedContent=$content -replace $matchLine,$replace
                    Rename-Item -Path $file -NewName "$(split-path $file -leaf)`.bak"
                    $updatedContent|set-content -path $file -Force
                    write-host "$updatedContent has been updated successfully."
                    return $true
                }else{
                    write-host "No update required for $matchLine"
                    return $false
                    }
            }else{
                write-host "no matches found with keyword: $match"
                return $false
                }
        } -args $integrationFile,$matchString
    appendContent $logFile "$(get-date)`t: integration re-enabled = $enableIntegration"
}

# 4) Update Fedederation metadata
function invokeIfdUpdate($adfs,$cred,$environment,$url){   
    function updateIfdRelyingPartyTrust($environment,$url){    
        $targetName="$environment IFD Relying Party"
        $availableNames=(Get-AdfsRelyingPartyTrust).Name
        $match=$availableNames|?{$_ -like "$targetName*"}
        if ($match){
            Update-ADFSRelyingPartyTrust -TargetName "$match"
            $urlExists=Get-AdfsRelyingPartyTrust -Identifier "$url"
            if($urlExists){
                return "$url currently exists on ADFS server $env:computername as '$($targetName.ToUpper())'"
                }
            else{return "$url currently NOT exists on $targetName"}
            }else{
                return "$targetname not found."
                }
    }

    invoke-command -ComputerName $adfs -Credential $cred -ScriptBlock{
        param($importedFunc,$x,$y)
        write-host "Executing function on $env:computername"
        return [ScriptBlock]::Create($importedFunc).invoke($x,$y);
        } -args ${function:updateIfdRelyingPartyTrust},$environment,$url
}
function updateFederationMetadata{
    param($adfs,$adminCredential,$environment,$url)
    $sourceIfdResult=invokeIfdUpdate $adfs $adminCredential $environment $url
    return $sourceIfdResult
}

function confirmation($content,$testValue="I confirm",$maxAttempts=3){
        $confirmed=$false;
        $attempts=0;        
        $content|write-host
        write-host "Please review this content for accuracy.`r`n"
        while ($attempts -le $maxAttempts){
            if($attempts++ -ge $maxAttempts){
                write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                break;
                }
            $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
            if ($userInput.ToLower() -eq $testValue.ToLower()){
                $confirmed=$true;
                write-host "Confirmed!`r`n";
                break;                
            }elseif($userInput -like 'cancel'){
                write-host 'Cancel command received.'
                $confirmed=$false
                break
            }else{
                cls;
                $content|write-host
                write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                }
            }
        return $confirmed;
    }

function updateHostFiles{
    param(
        $appLocalIp, $orgName, $domain, $searchString, $servers,$commentOut=$false
    )
    # placing this supporting function for easy copy/paste
    function confirmation($content,$testValue="I confirm",$maxAttempts=3){
        $confirmed=$false;
        $attempts=0;        
        $content|write-host
        write-host "Please review this content for accuracy.`r`n"
        while ($attempts -le $maxAttempts){
            if($attempts++ -ge $maxAttempts){
                write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                break;
                }
            $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm";
            if ($userInput.ToLower() -ne $testValue.ToLower()){
                cls;
                $content|write-host
                write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again..`r`n"
                }else{
                    $confirmed=$true;
                    write-host "Confirmed!`r`n";
                    break;
                    }
            }
        return $confirmed;
    }
    function generateARecord{
        param(
            $newARecord,
            $domain,
            $ip
        )
        $url=$newARecord+'.'+$domain;
        $recordString="$ip`t$url";
        return $recordString;
    }    
    function updateHostRecord{    
        param(    
            $record,
            $insertAfter,
            $servers,
            $remove=$false
            )
        $hostFiles=$($servers|%{"\\$_\C$\windows\system32\drivers\etc\hosts"})
        $results=@{}
        #replaceLine -textContent $fileContent -match $url -updateLineContent $record -remove $remove
        function replaceLine($textContent,$match,$updateLineContent,$remove=$false){
            write-host "Search string: $match"
            $lineMatch = $textContent | Select-String "$match"
            $lineNumber = $lineMatch.LineNumber
            $lineContent = $lineMatch.Line
            $replaceWith=if($remove){if($lineContent[0] -eq '#'){$lineContent}else{"#$lineContent #record disabled $(get-date) by $(whoami)"}}                            
                            else{$updateLineContent}
            write-host "Replacing`t: $lineMatch`r`nWith`t: $replaceWith"            
            $textContent[$lineNumber-1]=$replaceWith
            return $textContent
        }
    
        function updateLocalHostRecord{
            param(    
            $record,
            $hostfile="c:\windows\system32\drivers\etc\hosts",
            $searchString=$false,
            $remove=$false
            )
     
            function insertAfterMatchString{
                param(
                    $content,
                    $searchString,
                    $insert,
                    $remove=$false
                )
                if($remove){$insert='#'+$insert}
                [int]$matchedIndex=.{$lines=[array]$content
                                        for ($i=0;$i -lt $lines.Count;$i++) {if($lines[$i] -like "$searchString*"){return $i}}
                                        }
                if($matchedIndex){
                    $nextAvailableSpaceIndex=.{for($i=$matchedIndex;$i -lt $content.Length;$i++){
                                                    $isEmpty=$content[$i] -match "^\s*$"
                                                    #$isEmpty=$content[$i].Trim().isEmpty()
                                                    if ($isEmpty){return $i}
                                                    }
                                                }
                    $content=$content[0..$($nextAvailableSpaceIndex-1)]+$insert+$content[$($nextAvailableSpaceIndex)..$($content.length-1)]
                    write-host "$insert will be inserted after $($content[$nextAvailableSpaceIndex-1])`r`n-----------------------`r`n"
                }else{
                    $content+=$insert
                    write-host "$searchString has not matched anything.";
                }
                return $content
            }
     
            $validPath=[System.IO.File]::Exists($hostfile) # Slightly faster than Test-Path for true positives but not true negatives
            if($validPath){
                $fileContent=Get-Content -Path $hostfile
                $url=.{[void]($record -match "([0-9A-Za-z_\.\-]*)$");$matches[1]}
                $entryExists=$fileContent|?{$_ -match "$url"}|select -First 1
                write-host "Entry exists: $entryExists"
                if(!$entryExists){
                    write-host "Search string: '$searchString'"
                    pause
                    if($searchString){
                        $fileContent=insertAfterMatchString -content $fileContent -searchString $searchString -insert $record $remove
                        }else{
                            $fileContent+=$(if($remove){'#'})+$record
                            }         
                    $confirmed=confirmation -content $fileContent
                    if ($confirmed){
                        $backupFile=$hostFile+"_backup"
                        try{
                            Rename-Item -Path $hostfile -NewName $backupFile -ErrorAction Stop
                            }
                            catch{
                                write-host "Unable to rename. Now trying to delete previous backup and retry"
                                Remove-item $backupFile -Force
                                Rename-Item -Path $hostfile -NewName $backupFile
                                }
                        # The try-catch method overcomes this error
                        #Rename-Item : Cannot create a file when that file already exists.
                        #At line:1 char:1
                        #+ Rename-Item -Path $hostfile -NewName $($hostFile+"_backup") -Force
                        #+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                        #    + CategoryInfo          : WriteError: (C:\windows\system32\drivers\etc\hosts:String) [Rename-Item], IOException
                        #    + FullyQualifiedErrorId : RenameItemIOError,Microsoft.PowerShell.Commands.RenameItemCommand
                        $fileContent|set-Content $hostfile
                        $message="$hostfile updated successfully."
                        write-host $message -ForegroundColor Green
                        return $message
                        }else{
                            $message="$hostfile update cancelled."
                            write-host $message -ForegroundColor Red
                            return $message;
                            }
                    }
                else{
                    $fileContent=replaceLine -textContent $fileContent -match $url -updateLineContent $record -remove $remove
                    $confirmed=confirmation -content $fileContent
                    if ($confirmed){
                        $backupFile=$hostFile+"_backup"
                        try{
                            Rename-Item -Path $hostfile -NewName $backupFile -ErrorAction Stop
                            }
                            catch{
                                write-host "Unable to rename. Now trying to delete previous backup and retry"
                                Remove-item $backupFile -Force
                                Rename-Item -Path $hostfile -NewName $backupFile
                                }
                        # The try-catch method overcomes this error
                        #Rename-Item : Cannot create a file when that file already exists.
                        #At line:1 char:1
                        #+ Rename-Item -Path $hostfile -NewName $($hostFile+"_backup") -Force
                        #+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                        #    + CategoryInfo          : WriteError: (C:\windows\system32\drivers\etc\hosts:String) [Rename-Item], IOException
                        #    + FullyQualifiedErrorId : RenameItemIOError,Microsoft.PowerShell.Commands.RenameItemCommand
                        $fileContent|set-Content $hostfile
                        $message="$hostfile updated successfully."
                        write-host $message -ForegroundColor Green
                        return $message
                        }else{
                            $message="$hostfile update cancelled."
                            write-host $message -ForegroundColor Red
                            return $message;
                            }
                    }
            }else{
                $message="Unable to access $hostfile. Action aborted.";
                write-warning $message
                return $message;
                }
        }
     
        for ($i=0;$i -lt $hostFiles.count; $i++){
            $hostfile=$hostFiles[$i]
            write-host "Processing $hostfile..."       
            $result=updateLocalHostRecord -record $record -hostfile $hostFile -searchString $insertAfter -remove $remove
            $results.Add($servers[$i],$result)
            }
        return $results
    }
    function updateHostRecordOnServers{
        param(
            $appLocalIp,
            $orgName,
            $domain,
            $searchString,
            $servers,
            $remove=$false
        )
        $regexIP = [regex] "\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b"
        if ($appLocalIp -match $regexIP){
            $newRecord=generateARecord -newARecord $orgName -domain $domain -ip $appLocalIp
            $result=updateHostRecord -record $newRecord -insertAfter $searchString -servers $servers -remove $remove
            }else{
                $result="Unable to retrieve the app server private IP. Cannot update host records without that information."
                write-warning $result
                }
            return $result
    }
    return updateHostRecordOnServers $appLocalIp $orgName $domain $searchString $servers $commentOut
    
    #$regexIP = [regex] "\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b"
    #$newRecord=generateARecord -newARecord $orgName -domain $domain -ip $appLocalIp
    #foreach ($server in $servers){
    #    if ($appLocalIp -match $regexIP){            
    #        #updateHostRecord -record $newRecord -insertAfter $searchString -servers $server -remove $remove                    
    #        invoke-command -ComputerName $server -credential $adminCredential -scriptblock {
    #            param ($updateHostRecord,$newRecord, $searchString, $server, $remove)
    #            [ScriptBlock]::Create($updateHostRecord).invoke($newRecord, $searchString, $server, $remove)
    #            } -Args ${function:updateHostRecord},$newRecord, $searchString, $server, $remove
    #        }
    #    else{
    #        write-host "Unable to retrieve the app server private IP. Cannot update host records without that information."
    #        }
    #}
}
# updateHostFiles $appLocalIp $orgName $domain $searchString $sourceServers $commentOut

function invokeUpdateHostFiles{
    param(
        $adminCredential,$serverLocalIp,$orgName, $domain, $searchString,$targetHosts,$remove=$false
    )
    $jobResults=start-job -credential $adminCredential -scriptblock {
        param ($updateHostFiles,$serverLocalIp, $orgName, $domain, $searchString, $targetHosts,$remove)
        [ScriptBlock]::Create($updateHostFiles).invoke($serverLocalIp, $orgName, $domain, $searchString, $targetHosts,$remove)
        } -Args ${function:updateHostFiles},$serverLocalIp, $orgName, $domain, $searchString, $targetHosts,$remove|Receive-Job -Wait
    return $jobResults
}
#invokeUpdateHostFiles $adminCredential $appLocalIp $orgName $domain $searchString $sourceServers $true

# 7) Ship the database to its corresponding destination
# Execute this on the App Server of the Source Environment
# Description: 
#   This script accepts source and destination credentials to access SMB shares of 2 servers (SMB must be accessible)
#   An copy of a backup from Source to Destination SQL servers will be the result if credentials, services, and firewall ports are working as required
#   The function assumes that it's being invoked on the Source App Server
#   And that the Source App Server has WinRM & DB connections to its associated SQL Node
function addUserToLocalGroup{
    param(
    $computername=$env:computername,
    $localAdminCred,
    [string[]]$accountToAdd,
    $localGroup='Administrators'
    )
    try{
        $session=new-pssession $computername -Credential $localAdminCred -ea Stop
	    }
    catch{
        write-warning "Unable to connect to WinRM of $computername"
        return $false
        }
    invoke-command -session $session -scriptblock{
		param($principleName,$groupName)		
        $members=get-localgroupmember $groupName
        if(!($principleName -in $members.Name)){
            try{
                write-host "Adding $principleName into $groupName";
		        Add-LocalGroupMember -Group $groupName -Member $principleName -ea Stop;
                $currentMembers=get-localgroupmember $groupName|ft|out-string
		        write-host "$principleName has been added to $groupName successfully:`r`n$currentMembers";
		        return $true
                }
            catch{
                write-warning "$error"
                return $false
                }
            }
        else{
            write-host "$principleName is already a member of $groupName."
            return $true}
		} -args $accountToAdd,$localGroup
    remove-pssession $session
}

function shipDatabase{
    param(
        $databaseName,$dbData,$dbLog,$overwrite,
        $sourceSqlServer,$sourceSa,$sourceSaPassword,
        $destinationSqlServer,$destinationSa,$destinationSaPassword
    )

    # Start the timer for this activity
    $stopWatch= [System.Diagnostics.Stopwatch]::StartNew()    
    write-host "Shipping database $databaseName..."

    function mountDriveAsUser($username,$password,$driveLetter,$uncPath){
        if(get-psdrive $driveLetter -ea SilentlyContinue){
            #Remove-PSDrive $firstAvailableDriveLetter -ea SilentlyContinue #This does not affect drives being mounted by 'net use' command
            net use /delete ($driveLetter+':') 2>&1>null
            }    
        try{
            # This command cannot persist when out of scope of function; hence, net use is required
            # New-PSDrive –Name $mountLetter –PSProvider FileSystem –Root $uncPath –Persist -Credential $mountAsCred|out-null
            net use "$driveLetter`:" "$uncPath" /user:$username $password /persistent:Yes 2>&1>null
            if(test-path "$driveLetter`:\"){
                write-host "$driveLetter`: has successfully mounted.";
                #return $true;
                }
            else{
                write-host "Unable to mount drive $driveLetter";
                #return $false
                }
            }
        catch{
            write-warning "$error"
            #return $false
            }
        }
    
    function triggerSqlBackup($sqlServer,$databaseName,$saCred){
        $ErrorActionPreference='stop'
        
        if(Test-NetConnection $sqlServer -CommonTCPPort WINRM){
            try{
                invoke-command -Credential $saCred -ComputerName $sqlServer -ScriptBlock{
                        param ($databaseName)
                        import-module sqlps
                        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
                        $database=Get-SqlDatabase -ServerInstance $env:computername|?{$_.Name -eq $databaseName}
                        $success=Backup-SqlDatabase -DatabaseObject $database -CompressionOption On -CopyOnly
                        return $success
                        } -Args $databaseName
                return $True
            }catch{
                Write-Warning $error[0].Exception.Message
                return $false
                }
        }else{
            try{
                # Ensure the the Jump Box has SQL PowerShell tools
                $moduleName='SqlServer'    
                if(!(Get-Module -ListAvailable -Name $moduleName -ea SilentlyContinue)){
                    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                    if(!(Get-PackageProvider Nuget -ea SilentlyContinue)){    
                        try{
                            [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                            Install-PackageProvider -Name NuGet -Force -ErrorAction SilentlyContinue;                    
                        }catch{
                            write-warning $error[0].Exception.Message
                            }            
                        }
                    try{
                        Install-Module -Name $moduleName -Force -Confirm:$false
                        # Install Chocolatey
                        if (!(Get-Command choco.exe -ErrorAction SilentlyContinue)) {
                        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                        Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))}

                        # Defining $ENV:ChocotaleyInstall so that it would be called by refreshenv
                        $ENV:ChocolateyInstall = Convert-Path "$((Get-Command choco).Path)\..\.."   
                        Import-Module "$env:ChocolateyInstall\helpers\chocolateyProfile.psm1"
                        Update-SessionEnvironment                
                    }catch{
                        write-warning $error[0].Exception.Message
                        return $false
                        }
                    }
                # Trigger backup of target database into the default backup directory
                $database=Get-SqlDatabase -ServerInstance $sqlServer -credential $saCred|?{$_.Name -eq $databaseName}
                Backup-SqlDatabase -DatabaseObject $database -credential $saCred -CompressionOption On -CopyOnly
                return $true
            }catch{
                Write-Warning $error[0].Exception.Message
                return $false
                }
        }
    }

    # Get default backup directory of a SQL server and mount it as the First Available Drive Letter
    function convertLocalToUnc($localPath,$computername){    
        $uncPath=.{$x=$localPath -replace "^([a-zA-Z])\:","\\$computername\`$1`$";
                    if($x -match '\\$'){return $x.Substring(0,$x.length-1)}else{return $x}
                    }
        $validLocal=if($localPath){test-path $localPath -ErrorAction SilentlyContinue}else{$false}
        $validUnc=if($uncPath){test-path $uncPath -ErrorAction SilentlyContinue}else{$false}
        if($validUnc){write-host "$uncPath is reachable, using session credentials of $(whoami)"}
        else{write-host "Advisory: $computername is unreachable, using session credentials of $(whoami)"}
        return $uncPath
    }

    function invokeGetDefaultSqlPaths($sqlServer,$saCredential){
        $defaultValues=invoke-command -ComputerName $sqlServer -Credential $saCredential -ScriptBlock{
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
            $sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername 
            $defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
            $defaultDataDirectory=$sqlConnection.Settings.DefaultFile
            $defaultLogDirectory=$sqlConnection.Settings.DefaultLog
            return @($defaultDataDirectory,$defaultLogDirectory,$defaultBackupDirectory)
            }
        if($defaultValues){return $defaultValues}
        else{return $false}
    }

    # Credentials
    $sourceSaCred=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sourceSa,$(ConvertTo-securestring $sourceSaPassword -AsPlainText -Force)
    $destinationSaCred=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $destinationSa,$(ConvertTo-securestring $destinationSaPassword -AsPlainText -Force)

    # Start the SQL dump into its default backup location
    $backupSuccess=triggerSqlBackup $sourceSqlServer $databaseName $sourceSaCred
    if(!$backupSuccess){
        Write-Warning "Backup failed."
        return $false
        }

    # Scan for next available drive letter, excluding D "CD Rom" and H "Home"
    $unavailableDriveLetters=(Get-Volume).DriveLetter|sort
    $availableDriveLetters=.{(65..90|%{[char]$_})|?{$_ -notin $unavailableDriveLetters}}
    [char]$firstAvailableDriveLetter=$availableDriveLetters[0]
    [char]$secondAvailableDriveLetter=$availableDriveLetters[1]

    # Mount Source UNC path
    $sourceUncPath=convertLocalToUnc $(invokeGetDefaultSqlPaths $sourceSqlServer $sourceSaCred)[2] $sourceSqlServer
    mountDriveAsUser $sourceSA $sourceSaPassword $firstAvailableDriveLetter $sourceUncPath
    $sourceFolder="$firstAvailableDriveLetter`:\"

    # Mount Destination UNC path
    $destinationDefaults=invokeGetDefaultSqlPaths $destinationSqlServer $destinationSaCred
    $destinationUncPath=convertLocalToUnc $destinationDefaults[2] $destinationSqlServer
    mountDriveAsUser $destinationSa $destinationSaPassword $secondAvailableDriveLetter $destinationUncPath
    $destinationFolder="$secondAvailableDriveLetter`:\"

    #if(!(test-path $destinationFolder)){mkdir $destinationFolder}
    #$exportDirectory=$(split-path $backupFile -parent)
    #$fileName=$(split-path $backupFile -leaf)
    #$fileSize=(Get-Item $backupFile).length/1GB
    #$destinationFile="$importDirectory\$fileName"
    #if(get-item $destinationFile -ea SilentlyContinue){rm $destinationFile -Force}

    # Give permissions to current user toward import directory, in case it's not already present
    #[string]$currentAccount=$(whoami)
    #$Acl = Get-Acl $destinationFolder
    #$addPermission = New-Object System.Security.AccessControl.FileSystemAccessRule("$currentAccount", "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")
    #$Acl.SetAccessRule($addPermission)
    #Set-Acl $destinationFolder $Acl
    #write-host (get-acl $destinationFolder|fl|out-string)

    # Copy backup file to destination
    $sourceBackupFile="$sourceFolder$databaseName.bak"
    $sanityPassed=(test-path $sourceFolder) -and (test-path $destinationFolder) -and (test-path $sourceBackupFile)
    if ($sanityPassed){
        # rm $destinationFolder\*.* #Purge 
        write-host "Now copying $sourceBackupFile to $destinationFolder. Please wait..."
        $fileSize=(get-item $sourceBackupFile).Length/1GB
        $transferTime=(measure-command {robocopy "$sourceFolder" "$destinationFolder" "$databaseName.bak"}).TotalHours
        $transferSpeed=[math]::round($fileSize/$transferTime,2)
        write-host "$([math]::round($fileSize,2)) GB was copied in $([math]::round($transferTime,2)) hours => Speed: $transferSpeed GB/Hour"
        }

    # Rename the file to reflect its creation date stamp
    #$pacificTime=[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId( (Get-Date), 'Pacific Standard Time')
    #$dateStamp = $pacificTime.tostring("MM-dd-yyyy-HHmm")
    #Rename-Item -Path "$destinationFolder\$databaseName.bak" -NewName "$databaseName`_$dateStamp_PST.bak"

    # This function currently only works for smaller databases due to timeout errors
    # Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
    #The backup or restore was aborted.
    #At line:16 char:9
    #+         $dbImportResult=invoke-command -Session $session {
    #+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    #    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    #    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    #    + PSComputerName        : CRM-sql01.kimconnect.com
    
    function invokeSqlImport{
        param(
            $sqlServer,
            $saCred,
            $databaseName,
            $dataFile,
            $logFile,
            $backupFile,
            $dbData='mscrm',
            $dbLog='mscrm_log',
            $overWrite
            )
        $ErrorActionPreference='stop'        
        try{
            if(!$dataFile -or !$logFile -or !$backupFile){
                $systemDefaults=.{
                    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
                    $sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
                    $defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
                    $defaultDataDirectory=$sqlConnection.Settings.DefaultFile
                    $defaultLogDirectory=$sqlConnection.Settings.DefaultLog
                    return @($defaultDataDirectory,$defaultLogDirectory,$defaultBackupDirectory)
                    }
                if(!$dataFile){$dataFile = ($systemDefaults[0]+ "\$databaseName.mdf") -replace "\\{2}",'\'}
                if(!$logFile){$logFile = ($systemDefaults[1]+ "\$databaseName.ldf") -replace "\\{2}",'\'}
                if(!$backupFile){$backupFile="$($systemDefaults[2])\$databaseName.bak"}
                }
            
            #$maxTimeout=[int]::MaxValue
            #$sessionOptions=New-PSSessionOption -OpenTimeOut $maxTimeout -OperationTimeout $maxTimeout            
            #$session=new-pssession $sqlServer -Credential $saCred -SessionOption $sessionOptions
            $session=new-pssession $sqlServer -Credential $saCred
            $dbImportResult=invoke-command -Session $session {
                param($databaseName,$backupFile,$dataFile,$logFile,$dbData,$dbLog,$overwrite)
                # Sanity check
                if (!$databaseName -or !$backupFile -or !$dataFile -or !$logFile -or !$dbData -or !$dbLog){
                    write-warning "Please check the values and try again:`n
                        DatabaseName`t: $databaseName
                        BackupFile`t: $backupFile
                        DataFile`t: $dataFile
                        LogFile`t: $logFile
                        DbData`t: $dbData
                        DbLog`t: $dbLog
                        OverWriteFlag`t: $overwrite
                        "
                    return $false
                    }
                if(!(test-path $backupFile)){
                    write-warning "$backupFile is invalid"
                    return $false
                    }

                # Ensuring that this Server has SQL PowerShell tools
                $moduleName='sqlps'    
                if(!(Get-Module -ListAvailable -Name $moduleName -ea SilentlyContinue)){
                    if(!('NuGet' -in (get-packageprovider).Name)){    
                        try{
                            Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                            }
                        catch{
                            #Set-ItemProperty -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type DWord
                            #Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type DWord
                            [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                            Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                            }            
                        }
                    $null=Install-Module -Name $moduleName -Force -Confirm:$false
                    Update-SessionEnvironment
                    }
                $null=import-module $moduleName

                function confirmation($content,$testValue="I confirm",$maxAttempts=3){
                        $confirmed=$false;
                        $attempts=0;        
                        $content|write-host
                        write-host "Please review this content for accuracy.`r`n"
                        while ($attempts -le $maxAttempts){
                            if($attempts++ -ge $maxAttempts){
                                write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                                break;
                                }
                            $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
                            if ($userInput.ToLower() -eq $testValue.ToLower()){
                                $confirmed=$true;
                                write-host "Confirmed!`r`n";
                                break;                
                            }elseif($userInput -like 'cancel'){
                                write-host 'Cancel command received.'
                                $confirmed=$false
                                break
                            }else{
                                cls;
                                $content|write-host
                                write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                                }
                            }
                        return $confirmed;
                    }

                # Trigger a restore of a database from the default backup directory
                #$relocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbData, "$dataFile")
                #$relocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbLog, "$logFile")
                #Restore-SqlDatabase -ServerInstance $sqlServer -Database $databaseName -BackupFile $backupFile -RelocateFile @($relocateData,$relocateLog)

                # Preemptively resolve this error by killing all sessions:
                #ALTER DATABASE failed because a lock could not be placed on database 'Test_MSCRM'. Try again later.
                #ALTER DATABASE statement failed.
                #    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
                #    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
                #    + PSComputerName        : SQL-TEST

                $sqlRestoreExisting = "
                USE [master]

                DECLARE @killSessions varchar(4000) = '';
                SELECT @killSessions = @killSessions + 'kill ' + CONVERT(varchar(5), spid) + ';'  
                FROM master..sysprocesses  
                WHERE dbid = db_id('$databaseName')
                EXEC(@killSessions);
                GO

                ALTER DATABASE [$databaseName]
                    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                GO

                RESTORE DATABASE [$databaseName] FROM  DISK = N'$backupFile' WITH  FILE = 1,
                    MOVE N'$dbData' TO N'$dataFile',
                    MOVE N'$dbLog' TO N'$logFile',
                    NOUNLOAD,  REPLACE,  STATS = 5;
                GO

                ALTER DATABASE [$databaseName] 
                    SET MULTI_USER;
                GO
                "
                
                $sqlRestoreOverWrite = "
                USE [master]

                ALTER DATABASE [$databaseName]
                    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                GO

                RESTORE DATABASE [$databaseName] FROM  DISK = N'$backupFile' WITH  FILE = 1,
                    MOVE N'$dbData' TO N'$dataFile',
                    MOVE N'$dbLog' TO N'$logFile',
                    NOUNLOAD,  REPLACE,  STATS = 5;
                GO

                ALTER DATABASE [$databaseName] 
                    SET MULTI_USER;
                GO
                "

                $sqlForceRestore="
                USE [master]
                GO
                RESTORE DATABASE [$databaseName]
                    FROM DISK = N'$backupFile'
                    WITH REPLACE, RECOVERY --force RESTORE
                GO
                "
                $dbExists=(invoke-sqlcmd "SELECT CASE WHEN DB_ID('$databaseName') IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END").Column1         
                $maxQueryTimeout=[int]::MaxValue
                if(!$dbExists){
                    write-host "$databaseName is now being added to $env:computername..."
                    $confirmed=confirmation -content $sqlRestoreOverWrite
                    if($confirmed){
                        try{
                            invoke-sqlcmd -Query $sqlRestoreOverWrite -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0 -Verbose
                        }catch{
                            write-warning $error[0].Exception.Message
                            write-warning "Normal restore failed. Now forcing DB-restore sequence..."
                            $confirmForcedRestore=confirmation -content $sqlForceRestore
                            if($confirmForcedRestore){
                                invoke-sqlcmd $sqlForceRestore -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0 -Verbose
                            }else{
                                write-warning "Forced-restore sequence has been declined. No changes were made."
                                }
                            }
                    }else{
                        write-host "Database import was not confirmed. No changes were made."
                        }
                    }
                elseif($dbExists -and $overwrite){
                    write-host "$databaseName exists and over-write flag is True. Executing..."
                    $confirmed=confirmation -content $sqlRestoreExisting
                    if($confirmed){
                        try{
                            invoke-sqlcmd $sqlRestoreExisting -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0 -Verbose
                            }
                        catch{
                            write-warning $error[0].Exception.Message                            
                            write-warning "Normal restory failed. Now forcing DB-restore sequence..."
                            $confirmForcedRestore=confirmation -content "Force restore using this T-SQL:`r`n$sqlForceRestore"
                            if($confirmForcedRestore){
                                invoke-sqlcmd $sqlForceRestore -QueryTimeout $maxQueryTimeout -ConnectionTimeout 0 -Verbose
                            }else{
                                write-warning "Database $databaseName was NOT successfully imported!"
                                }
                            }              
                    }else{
                        write-warning "Database import was not confirmed. No changes were made."
                        }
                    }
                else{
                    write-host "$databaseName currently Exists and over-write flag has been set as False"
                    }

                # Validation
                Function databaseExists{                                                                                                                    param(
                    [Parameter(Mandatory=$true)][string]$sqlServer,
                    [Parameter(Mandatory=$true)][string]$dbName,
                    [Parameter(Mandatory=$false)][string]$port=1433,
                    [Parameter(Mandatory=$false)][string]$dbUser,
                    [Parameter(Mandatory=$false)][string]$dbPassword
                    )
                    $ErrorActionPreference='stop'
                    $dbExists = $false
                                                                                                                                                                                                                                                                                                                                                                                                                                                    try{
                    $moduleName='sqlps'    
                    if(!(Get-Module -ListAvailable -Name $moduleName -ea SilentlyContinue)){
                        if(!('NuGet' -in (get-packageprovider).Name)){    
                            try{
                                Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                                }
                            catch{
                                [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                                Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                                }            
                            }
                        $null=Install-Module -Name $moduleName -Force -Confirm:$false
                        Update-SessionEnvironment
                        }
                    $null=import-module $moduleName  
                    $conn = New-Object system.Data.SqlClient.SqlConnection
                    if($dbUser -and $dbPassword){
                        $conn.ConnectionString=[string]::format("Server={0};Database={1};User ID={2};Password={3};","$sqlServer,$port",$dbName,$dbUser,$dbPassword)
                    }else{
                        $conn.connectionstring=[string]::format("Server={0};Database={1};Integrated Security=SSPI;","$sqlServer,$port",$dbName)
                        #$conn.ConnectionString=”Server={0};Database={1};Integrated Security=True” -f $ServerInstance,$Database
                        }
                    $conn.open()
                    $conn.close()
                    return $true
                }catch{
                        Write-Error $error[0].Exception.Message
                        return $false
                        }
                }
                $databaseExists=databaseExists $env:computername $databaseName
                if ($databaseExists){
                    Set-location SQLSERVER:\SQL\$env:computername\DEFAULT
                    $currentDb=Get-SqlDatabase -Name $databaseName|select Name,RecoveryModel,CompatibilityLevel,CreateDate,DataSpaceUsage,LastBackupDate,Owner,PrimaryFilePath
                    return ($currentDb|out-string).Trim()
                }else{
                    write-warning "Database $databaseName does not exist on $env:computername"
                    return $false
                    }
                } -Args $databaseName,$backupFile,$dataFile,$logFile,$dbData,$dbLog,$overwrite
            Remove-PSSession $session
            return $dbImportResult
        }catch{
            Write-warning $error[0].Exception.Message
            return $false
            }
    }

    $importClock= [System.Diagnostics.Stopwatch]::StartNew()
    $dataFile = ($destinationDefaults[0]+ "\$databaseName.mdf") -replace "\\{2}",'\'
    $logFile = ($destinationDefaults[1]+ "\$databaseName.ldf") -replace "\\{2}",'\'
    $destinationBackupFile="$($destinationDefaults[2])\$databaseName.bak"
    
    $importResult=invokeSqlImport $destinationSqlServer $destinationSaCred $databaseName $dataFile $logFile $destinationBackupFile $dbData $dbLog $overwrite
    write-host "Import result`: $importResult"
    $importHours=$importClock.Elapsed.TotalHours;    
    $importClock.Stop();

    # Cleanup Routine
    if($importResult){
        write-host "Import was successful!" -ForegroundColor Green
        write-host "Cleaning up backup files at $destinationSqlServer..."
        $null=remove-item -path $($destinationFolder+"$databaseName.bak") -force -ea SilentlyContinue 
    }else{
        write-warning "Import was NOT successful.`r`nPlease manually import the $databaseName at $destinationSqlServer`r`nUsing backup file`t: $destinationBackupFile"
        }
    write-host "Cleaning up backup files at $sourceSqlServer..."
    $null=remove-item -path $sourceBackupFile -force -ea SilentlyContinue
    net use /delete $($firstAvailableDriveLetter+':') 2>&1>null
    net use /delete $($secondAvailableDriveLetter+':') 2>&1>null

    # Total duration
    $hoursElapsed=$stopWatch.Elapsed.TotalHours;
    $stopWatch.Stop();
    $shippingResult="Database shipping result`r
----------------------------------------------`r
Export stats`r
------------`r
Source SQL Server`t: $sourceSqlServer`r
Backup successful`t: $backupSuccess`r
Database size`t: $([math]::round($fileSize,2)) GiB`r
File transfer duration`t: $([math]::round($transferTime,2)) hours`r
----------------------------------------------`r
Import stats`r
------------`r
Destination SQL Server`t: $destinationSqlServer`r
DB import duration`t: $([math]::round($importHours,2)) hours`r
Import Result`t: $(if($importResult){"`r+$importResult"}else{'Failed'})
----------------------------------------------`r
Overall stats`r
------------`r
Total time`t: $([math]::round($hoursElapsed,2)) hours`r
Aggregate speed`t: $([math]::round($hoursElapsed/$fileSize,2)) GB/Hour`r
----------------------------------------------"
    return $shippingResult
}


function fixIsRequired{
    param(
        [Parameter(Mandatory=$true)][String[]]$sqlServer=$env:computername,
        [Parameter(Mandatory=$true)][String[]]$databaseName,
        [Parameter(Mandatory=$true)][String[]]$objectName,
        [Parameter(Mandatory=$true)][String[]]$objectType,
        [Parameter(Mandatory=$true)][String[]]$fromObject,
        [Parameter(Mandatory=$true)]$saCred
        )
    # Validation
    $validatedObjectName=!(checkDatabaseObject $sqlServer $databaseName $objectName $objectType $saCred)
    $validatedModelObject=checkDatabaseObject $sqlServer $databaseName $fromObject $null $saCred
    if(!$validatedObjectName -or !$validatedObjectType -or !$validatedModelObject){
        write-warning "Some items does NOT require fixing:
            New Object Not Exists`t: $objectName ($validatedObjectName)
            Source Object`t: $fromObject ($validatedModelObject)
            "
        return $false
    }else{
        return $true
        }
}

function checkDatabaseObject{
    param(
        [Parameter(Mandatory=$true)][String[]]$sqlServer=$env:computername,
        [Parameter(Mandatory=$true)][String[]]$databaseName,
        [Parameter(Mandatory=$true)][String[]]$objectName,
        [Parameter(Mandatory=$false)][String[]]$objectType,
        [Parameter(Mandatory=$false)]$saCred
        )
    $ErrorActionPreference='stop'  

    function includeSqlPs{
        if(!(get-command invoke-sqlcmd)){
            if(!('NuGet' -in (get-packageprovider).Name)){    
                try{
                    #Preempt this error: Unable to resolve package source 'https://www.powershellgallery.com/api/v2'
                    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                    Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                    #Resolve this error: PackageManagement\Install-Package : No match was found for the specified search criteria and module name 'sqlps'. Try Get-PSRepository to see all available registered module repositories.
                    #Register-PSRepository -Default
                    #Register-PSRepository -Name PSGallery -InstallationPolicy Trusted -Verbose
                    }
                catch{
                    write-warning $error[0].Exception.Message
                    }            
                }
            Install-Module -Name 'sqlserver' -Force -Confirm:$false
            try{
                Update-SessionEnvironment -ea stop
                }
            catch{
                # Prempt these errors: Install Choco to peruse its prepackaged libraries
                # Update-SessionEnvironment : The term 'Update-SessionEnvironment' is not recognized as the name of a cmdlet
                # The term 'refreshenv' is not recognized as the name of a cmdlet, function, script file, or operable program
                # Install Chocolatey
                if (!(Get-Command choco.exe -ErrorAction SilentlyContinue)) {
                [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))}

                # Defining $ENV:ChocotaleyInstall so that it would be called by refreshenv
                            $ENV:ChocolateyInstall = Convert-Path "$((Get-Command choco).Path)\..\.."   
                            Import-Module "$env:ChocolateyInstall\helpers\chocolateyProfile.psm1"
                            Update-SessionEnvironment
                }            
            }
            import-module 'SQLPS'
        }  

    try{
        $objectTypeAbbrevations=@{
            'view'='V'
            'procedure'='P'
            'table'='U'
            }
        $sqlCheckCommand =  if(!$objectType){"
                                USE [$databaseName]
                                GO
                                SELECT CASE
                                    WHEN (OBJECT_ID('$objectName')) IS NULL
                                    THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)
                                END
                                "
                            }else{"
                                USE [$databaseName]
                                GO
                                SELECT CASE
                                    WHEN (OBJECT_ID('$objectName','$($objectTypeAbbrevations[$objectType])')) IS NULL
                                    THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)
                                END
                                "
                                }        
      
        $objectExists=invoke-command -Credential $saCred -ComputerName $sqlServer -ScriptBlock{
                            param($includeSqlPs,$sqlCheckCommand)
                            [ScriptBlock]::Create($includeSqlPs).invoke()                          
                            return (invoke-sqlcmd $sqlCheckCommand -ServerInstance $env:computername).Column1
                            } -Args ${function:includeSqlPs},$sqlCheckCommand
                                              
        return $objectExists
    }catch{
        # $ErrorActionPreference = "Stop" in conjunction with Write-Error = terminating error
        write-error $error[0].Exception.Message
        return $false
        }
}

function tsqlCommandToCreateObject{
    param(
        [Parameter(Mandatory=$true)][String[]]$sqlServer=$env:computername,
        [Parameter(Mandatory=$true)][String[]]$databaseName,
        [Parameter(Mandatory=$true)][String[]]$objectName,
        [Parameter(Mandatory=$true)][String[]]$objectType,
        [Parameter(Mandatory=$true)][String[]]$fromObject,
        [Parameter(Mandatory=$true)]$saCred
        )
    # Validate input
    $validObjectTypes='view','procedure','table'
    $validatedObjectType=!(!($validObjectTypes|?{$_ -eq $objectType}))
    if(!$validatedObjectType){
        write-warning "Object type $objectType is invalid."
        return $null
        }
    $objectTypeAbbrevations=@{
        'view'='V'
        'procedure'='P'
        'table'='U'
        }
    $prepTSql="
        USE [$databaseName]
        GO
        DECLARE @sqlCmd nvarchar (1000000000)
        BEGIN
            IF (OBJECT_ID('$objectName', '$($objectTypeAbbrevations[$objectType])')) IS NULL          
            BEGIN 
                SELECT @sqlCmd = 'CREATE $objectType $objectName as SELECT * FROM $fromObject'
                EXEC sp_executesql @sqlCmd
            END
        END
  
        "
    $tSql="
        USE [$databaseName]
        GO
        DECLARE @sqlCmd nvarchar ($($prepTSql.Length +100))
        BEGIN
            IF (OBJECT_ID('$objectName', '$($objectTypeAbbrevations[$objectType])')) IS NULL          
            BEGIN 
                SELECT @sqlCmd = 'CREATE $objectType $objectName as SELECT * FROM $fromObject'
                EXEC sp_executesql @sqlCmd
            END
        END
  
        "
    return $tSql
}

function invokeTsql{
    param(
        [Parameter(Mandatory=$true)][String[]]$sqlServer=$env:computername,
        [Parameter(Mandatory=$true)][String[]]$databaseName,
        [Parameter(Mandatory=$true)][String[]]$tSql,
        [Parameter(Mandatory=$false)]$saCred
        )
    $ErrorActionPreference='stop'

    function includeSqlPs{
        if(!(get-command invoke-sqlcmd)){
            if(!('NuGet' -in (get-packageprovider).Name)){    
                try{
                    #Preempt this error: Unable to resolve package source 'https://www.powershellgallery.com/api/v2'
                    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                    Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                    #Resolve this error: PackageManagement\Install-Package : No match was found for the specified search criteria and module name 'sqlps'. Try Get-PSRepository to see all available registered module repositories.
                    #Register-PSRepository -Default
                    #Register-PSRepository -Name PSGallery -InstallationPolicy Trusted -Verbose
                    }
                catch{
                    write-warning $error[0].Exception.Message
                    }            
                }
            Install-Module -Name 'sqlserver' -Force -Confirm:$false
            try{
                Update-SessionEnvironment -ea stop
                }
            catch{
                # Prempt these errors: Install Choco to peruse its prepackaged libraries
                # Update-SessionEnvironment : The term 'Update-SessionEnvironment' is not recognized as the name of a cmdlet
                # The term 'refreshenv' is not recognized as the name of a cmdlet, function, script file, or operable program
                # Install Chocolatey
                if (!(Get-Command choco.exe -ErrorAction SilentlyContinue)) {
                [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))}

                # Defining $ENV:ChocotaleyInstall so that it would be called by refreshenv
                            $ENV:ChocolateyInstall = Convert-Path "$((Get-Command choco).Path)\..\.."   
                            Import-Module "$env:ChocolateyInstall\helpers\chocolateyProfile.psm1"
                            Update-SessionEnvironment
                }            
            }
            import-module 'SQLPS'
        }

    try{
        if ($saCred){
            $session=new-pssession $sqlServer -Credential $saCred
        }else{
            $session=new-pssession $sqlServer
            }
        if(!$session){
            write-warning "Unable to create a WinRM session toward $sqlServer"
            return $false
            }

        $sqlExecResult=invoke-command -Session $session {
            param($includeSqlPs,$databaseName,$tSql)        
            $ErrorActionPreference='stop'

            function confirmation($content,$testValue="I confirm",$maxAttempts=3){
                    $confirmed=$false;
                    $attempts=0;        
                    $content|write-host
                    write-host "Please review this content for accuracy.`r`n"
                    while ($attempts -le $maxAttempts){
                        if($attempts++ -ge $maxAttempts){
                            write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                            break;
                            }
                        $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
                        if ($userInput.ToLower() -eq $testValue.ToLower()){
                            $confirmed=$true;
                            write-host "Confirmed!`r`n";
                            break;                
                        }elseif($userInput -like 'cancel'){
                            write-host 'Cancel command received.'
                            $confirmed=$false
                            break
                        }else{
                            cls;
                            $content|write-host
                            write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                            }
                        }
                    return $confirmed;
                }

            # Include SQL PowerShell tools
            [ScriptBlock]::Create($includeSqlPs).invoke()              

            $dbExists=(invoke-sqlcmd "SELECT CASE WHEN DB_ID('$databaseName') IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END").Column1         
            if($dbExists){
                $confirmed=confirmation "Please confirm this T-SQL statement. Ctrl+C to Cancel`r`n$tSql"
                if($confirmed){        
                    try{
                        invoke-sqlcmd -query $tSql
                        write-host "T-SQL has been committed successfully." -ForegroundColor Green
                        return $true
                    }catch{
                        write-host $error[0].Exception.Message -ForegroundColor Red
                        return $false
                        }
                }else{
                    write-host "T-SQL has been cancelled by $(whoami)" -ForegroundColor Yellow
                    return $false
                    }
            }else{
                write-warning "Database $databaseName does not match any valid DB on $env:computername"
                return $false
                }
            } -Args ${function:includeSqlPs},$databaseName,$tsql
        Remove-PSSession $session
        return $sqlExecResult
    }catch{
        Write-Warning $error[0].Exception.Message
        return $false
        }
}

# 8) Update Host Record on DNS
function updateHostRecordOnDns{
    param(        
        $dnsServer,
        $adminCred,
        $record,
        $ip,
        $zone
    )
    try{
    $session=if($adminCred){new-pssession -ComputerName $dnsServer -credential $adminCred}else{new-pssession -ComputerName $dnsServer}
    $result=invoke-command -ComputerName $dnsServer -Session $session -ScriptBlock{
        param($aRecord,$ip,$zone)
        import-module dnsserver
        $resolve=Get-DnsServerResourceRecord -Name $aRecord -ZoneName $zone -ea SilentlyContinue
        if($resolve.HostName -ne $null){
            $previousIp=$resolve.RecordData.IPv4Address.IPAddressToString
            if($previousIp -ne $ip){                
                $newRecord = $resolve.Clone()
                $newRecord.RecordData.IPv4Address=[ipaddress]$ip
                Set-DnsServerResourceRecord -NewInputObject $newRecord -OldInputObject $resolve -ZoneName $zone -PassThru
                $advisory="$aRecord previous ip $previousIp has been changed to $ip!"
                write-warning $advisory
                return $True
                }
            }else{
                try{
                    Add-DnsServerResourceRecordA -Name $aRecord -IPv4Address $ip -ZoneName $zone -AllowUpdateAny -TimeToLive 01:00:00 -Confirm:$false -EA Stop
                    $msg="$aRecord $ip has been added to zone $zone on $env:computername"
                    write-host $msg -ForegroundColor Green
                    return $true
                    }
                catch{
                    write-warning "Unable to add record $aRecord to DNS server $env:computername`r`r$($Error[0].Exception.Message)"
                    return $false
                    }
                }
        } -Args $record,$ip,$zone
        remove-pssession $session
        return $result
    }catch{
        write-warning $Error[0].Exception.Message
        if($session){remove-pssession $session}
        return $false
        }
}

# 13) Validation
function testLogin{
    param(
        $url,
        $username,
        $password,
        $usernameElementId='userNameInput',
        $passwordElementId='passwordInput',
        $submitButtonId='submitButton'
    )
    $ErrorActionPreference = "SilentlyContinue"
    
    function killInternetExplorer{
        $ieInstances=(New-Object -COM 'Shell.Application').Windows()|?{$_.Name -like '*Internet Explorer*'} 
        $ieInstances|%{$_.Quit()
        [Runtime.Interopservices.Marshal]::ReleaseComObject($_)
        }
        [GC]::Collect()
        [GC]::WaitForPendingFinalizers()
    }
    killInternetExplorer|out-null
    try{
        $ie = New-Object -ComObject 'internetExplorer.Application'
        $ie.Visible= $true # Make it visible
        $ie.Navigate("$url")
        while($ie.ReadyState -ne 4) {start-sleep -m 100}
        try{
            $usernamefield = $ie.Document.IHTMLDocument3_getElementById($usernameElementId)
            $usernamefield.value = "$username"
            $passwordfield = $ie.Document.IHTMLDocument3_getElementById($passwordElementId)
            $passwordfield.value = "$password"
            $ie.Document.IHTMLDocument3_getElementById($submitButtonId).click()
            }
        catch{
            try{
                $usernamefield = $ie.document.getElementByID($usernameElementId)
                $usernamefield.value = "$username"
                $passwordfield = $ie.document.getElementByID($passwordElementId)
                $passwordfield.value = "$password"
                $Link = $ie.document.getElementByID($submitButtonId).click
                }
            catch{
                try{
                    $document = $ie.Document
                    $form =  $document.forms[0]
                    $inputs = $form.getElementsByTagName("input")
                    ($inputs | where {$_.name -eq "username"}).value = $username
                    ($inputs | where {$_.name -eq "Password"}).value = $password
                    ($inputs | where {$_.name -eq "Submit"}).click()
                    }
                catch{
                    write-warning "Funky site you haz. Me can't login."
                    }
                }
            }

        #$ieContent=$ie.document.documentelement.innerText
        While ($ie.Busy -eq $true) {Start-Sleep -Seconds 1;}
        $title=$ie.Document.Title
        $not404=$title -notmatch '^404'        
        write-host "Page reached: $title"
        $ie.Quit()
        killInternetExplorer|out-null
        return $not404
        }
    catch{
        #write-warning "$Error"
        killInternetExplorer|out-null
        return $false
        }
    }

function autologinSe{
    param(
        $url,
        $username,
        $password,
        $usernameElementId='userNameInput',
        $passwordElementId='passwordInput',
        $submitButtonId='submitButton',
        $exitIeWhenDone=$false
    )
    $ErrorActionPreference = 'continue'

    # Initial validation
    if(!$url){write-warning "No URL specified.";return $false}

    function killInternetExplorer{
        $ieInstances=(New-Object -COM 'Shell.Application').Windows()|?{$_.Name -like '*Internet Explorer*'} 
        $ieInstances|%{$_.Quit()
        [Runtime.Interopservices.Marshal]::ReleaseComObject($_)
        }
        [GC]::Collect()
        [GC]::WaitForPendingFinalizers()
        }

    function enableIeProtectedMode{
        # $hives = 0..4|%{"HKLM:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\$_"}
        $hives = 0..4|%{"HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\$_"}
        $keyName='2500' # Key Name '2500' corresponds to 'Protected Mode' in IE
        
        #Skipping zone 0 as that is the default local machine zone
        $hives[1..4]|%{Set-ItemProperty -Path $_ -Name $keyName -Value 0}
        $keys=$hives|%{Get-ItemProperty -Path $_}|select DisplayName, `
                                                        @{name='status';e={
                                                                        if($_.$keyName -eq 0){'enabled'}
                                                                        elseif($_.$keyName -eq 3){'disabled'}
                                                                        else{'n/a'}                                                                                        
                                                                        }}
        write-host "IE Protected Mode Standardized Values:`r`n$($keys|out-string)" 
    }

    function disableIeProtectedMode{
        # $hives = 0..4|%{"HKLM:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\$_"}
        $hives = 0..4|%{"HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\$_"}
        $keyName='2500' # Key Name '2500' corresponds to 'Protected Mode' in IE
        
        #Skipping zone 0 as that is the default local machine zone
        $hives[1..4]|%{Set-ItemProperty -Path $_ -Name $keyName -Value 3}
        $keys=$hives|%{Get-ItemProperty -Path $_}|select DisplayName, `
                                                        @{name='status';e={
                                                                        if($_.$keyName -eq 0){'enabled'}
                                                                        elseif($_.$keyName -eq 3){'disabled'}
                                                                        else{'n/a'}                                                                                        
                                                                        }}
        write-host "IE Protected Mode Standardized Values:`r`n$($keys|out-string)" 
    }

    function allowActiveX($zone='Trusted'){
        #Source: http://support.microsoft.com/KB/182569
        $zoneCode=switch($zone){
            'My Computer'{0;break}
            'Local Intranet'{1;break}
            'Trusted'{2;break}
            'Internet'{3;break}
            'Restricted Sites'{4;break}
            default{2}
            }
        #Reference table:
        #Value    Setting
        #------------------------------
        #0        My Computer
        #1        Local Intranet Zone
        #2        Trusted sites Zone
        #3        Internet Zone
        #4        Restricted Sites Zone
        $hashMap=@{
            '2702'=0 #ActiveX controls and plug-ins: Allow ActiveX Filtering = Enable (2702)
            '1208'=0 #ActiveX controls and plug-ins: Allow previously unused ActiveX controls to run without prompt = Enable (1208)
            '1209'=0 #ActiveX controls and plug-ins: Allow Scriptlets = Enable (1209)
            '2201'=3 #ActiveX controls and plug-ins: Automatic prompting for ActiveX controls = Disable (2201)
            '2000'=0 #ActiveX controls and plug-ins: Binary and script behaviors = Enable (2000)
            '120A'=0 #Display video and animation on a webpage that does not use external media player = Enable (120A)
            '1001'=0 #ActiveX controls and plug-ins: Download signed ActiveX controls = Enable (1001)
            '1004'=0 #ActiveX controls and plug-ins: Download unsigned ActiveX controls = Enable (1004)
            '1201'=0 #ActiveX controls and plug-ins: Initialize and script ActiveX controls not marked as safe for scripting = Enable (1201)
            '120B'=3 #Only allow approved domains to use ActiveX without prompt = Disable (120B)
            '1200'=0 #ActiveX controls and plug-ins: Run ActiveX controls and plug-ins = Enable (1200)
            '1405'=0 #ActiveX controls and plug-ins: Script ActiveX controls marked as safe for scripting = Enable (1405)
            }        
        
        $trustedDomains="HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\zones\$zoneCode"
        $currentValues=Get-ItemProperty $trustedDomains
        foreach ($item in $hashMap.GetEnumerator()) {
            $key = $item.Key
            $value = $item.Value
            if($currentValues.$key -ne $value){
                    New-ItemProperty -Path $trustedDomains -Name $key -Value $value -PropertyType DWORD -Force
                }
        }
    }

    function addDomainToTrustedSites($url){
        $httpType=.{[void]($url -match '^(https{0,1})');$matches[1]}
        $domain=([uri]$url).Host
        #$rootDomain=$domain.split('.')[-2..-1] -join '.' # This is assuming that the TLD is one-dotted (e.g. .com) not two-dotted (e.g. co.uk)
        $rootDomain=.{$fragments=$domain.split('.')
                    $fragments[1..$($fragments.count)] -join '.'
                    }
        write-host "Root domain detected`t: $rootDomain"        
        # The more advanced function to retrieve this value is at https://kimconnect.com/powershell-extract-root-domain-from-url
        if ($rootDomain -notmatch '\.' -or $rootDomain -eq $env:USERDNSDOMAIN){
            write-host "There's no need to add $url to the Trusted zone as it is local to this domain."
            return $true
            }
        $dwordValue=2 # value of true correlates to 'enable'
        $domainRegistryPath='HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains'
        $domainRegistryPath2='HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains' #EscDomains key applies to those protocols that are affected by the Enhanced Security Configuration (ESC)
        $null=New-Item -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap' -ItemType File -Name 'EscDomains' -Force
        $null=New-Item -Path "$domainRegistryPath" -ItemType File -Name "$rootDomain" -Force
        $null=New-Item -Path "$domainRegistryPath2" -ItemType File -Name "$rootDomain" -Force
        $null=Set-ItemProperty -Path "$domainRegistryPath\$rootDomain" -Name $httpType -Value $dwordValue
        $null=Set-ItemProperty -Path "$domainRegistryPath2\$rootDomain" -Name $httpType -Value $dwordValue

        # Also add {about:blank} record as that doesn't seem to have been added by default
        if (!(test-path "$domainRegistryPath\blank")){
            #New-ItemProperty -Path $trustedDomains -Name $key -Value $value -PropertyType DWORD -Force
            $null=New-Item -Path "$domainRegistryPath" -ItemType File -Name 'blank'
            $null=Set-ItemProperty -Path "$domainRegistryPath\blank" -Name 'about' -Value $dwordValue
            }
        if (!(test-path "$domainRegistryPath2\blank")){
            $null=New-Item -Path "$domainRegistryPath2" -ItemType File -Name 'blank'
            $null=Set-ItemProperty -Path "$domainRegistryPath2\blank" -Name 'about' -Value $dwordValue
            }                     

        # Also add {about:internet} record since it will stop a login when missing
        if (!(test-path "$domainRegistryPath\internet")){
            $null=New-Item -Path "$domainRegistryPath" -ItemType File -Name 'internet'
            $null=Set-ItemProperty -Path "$domainRegistryPath\internet" -Name 'about' -Value $dwordValue
            }
        if (!(test-path "$domainRegistryPath2\internet")){
            $null=New-Item -Path "$domainRegistryPath2" -ItemType File -Name 'internet'
            $null=Set-ItemProperty -Path "$domainRegistryPath2\internet" -Name 'about' -Value $dwordValue
            } 
            
        $valueAfterChanged=(Get-ItemProperty "$domainRegistryPath\$rootDomain")."$httpType"
        $value2AfterChanged=(Get-ItemProperty "$domainRegistryPath2\$rootDomain")."$httpType"
        if ($valueAfterChanged -eq 2 -and $value2AfterChanged -eq 2 ){
            write-host "$rootDomain has been added to Internet Explorer"
            return $true
            }
        else{
            write-warning "$rootDomain has NOT been added to Internet Explorer."
            return $false
            }
    }

    function includeSelenium{
        Import-Module Selenium -ea SilentlyContinue
        if (!(get-module selenium -EA SilentlyContinue)){
            Start-job {
                [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                if(!(Get-PackageProvider Nuget -ea SilentlyContinue)){Install-PackageProvider -Name NuGet -Force}
                # Defining $ENV:ChocotaleyInstall so that it would be called by refreshenv
                $ENV:ChocolateyInstall = Convert-Path "$((Get-Command choco).Path)\..\.."   
                Import-Module "$env:ChocolateyInstall\helpers\chocolateyProfile.psm1"
                Install-Module Selenium -Force -Confirm:$False
                } |Receive-Job -Wait
            Update-SessionEnvironment
            Import-Module Selenium
            }
        }

    function invokeSelenium($url,$userName,$password,$usernameElementId,$passwordElementId,$submitButtonId){
        $ErrorActionPreference = "Stop"
        function closeSelenium($selenium){
            if($selenium){
                $selenium.close()
                $selenium.quit()
                }
            }
        
        function noLogin($url){
            $seleniumIe = New-Object "OpenQA.Selenium.IE.InternetExplorerDriver"
            $seleniumIe.Navigate().GoToURL($url)
            $title=$seleniumIe.Title
            write-host "Page reached: '$title'"
            $trustedSiteError=$title -match '^Error'
            if($trustedSiteError){
                write-host "An site trust issue has been detected. Adding root domain to the trusted sites list to resolve this issue."
                addDomainToTrustedSites $url              
                closeSelenium $seleniumIe                
                return $false
                }
            else{
                return $seleniumIe                
                }
            }

        function login($url,$username,$password,$usernameElementId,$passwordElementId,$submitButtonId){
            $seleniumIe = New-Object "OpenQA.Selenium.IE.InternetExplorerDriver"
            $seleniumIe.Navigate().GoToURL($url)
            $userField=$seleniumIe.FindElementById($usernameElementId)
            $userField.clear()
            $userField.SendKeys($username)
            $passwordField=$seleniumIe.FindElementById($passwordElementId)
            $passwordField.clear()
            $passwordField.SendKeys($password)
            $submitButton=$seleniumIe.FindElementById($submitButtonId)
            $submitButton.Click()
            $title=$seleniumIe.Title
            write-host "Page reached: '$title'"
            $trustedSiteError=$title -match '^Error'
            if($trustedSiteError){
                write-warning "A site trust issue has been detected."                
                closeSelenium $seleniumIe                
                return $false
            }else{
                return $seleniumIe                
                }
            }
        
        try{
            $null=allowActiveX
            $isLogin=$userName,$password,$usernameElementId,$passwordElementId,$submitButtonId|?{!(!$_)}
            if($isLogin){
                write-host "Login to $url as $userName..."
                $ie=login $url $userName $password $usernameElementId $passwordElementId $submitButtonId
            }else{
                write-host "Accesing $url without login..."
                $ie=nologin $url
                }
            return $ie
            }
        catch{            
            Write-Warning $Error[0].Exception.Message
            return $false
            }
        }

    try{
        write-host "Username`t: $username`r`nPassword`t: $(!(!$password))`r`nusernameElementId`t: $usernameElementId`r`npasswordElementId`t: $passwordElementId`r`nsubmitButtonId`t: $submitButtonId"
        $null=includeSelenium
        $null=disableIeProtectedMode
        $null=addDomainToTrustedSites $url                   
        if(get-module selenium -ea SilentlyContinue){
            $isLogin=$userName,$password,$usernameElementId,$passwordElementId,$submitButtonId|?{!(!$_)}
            if($isLogin){                
                $selenium=invokeSelenium $url $userName $password $usernameElementId $passwordElementId $submitButtonId
            }else{
                write-host "No username or password are given. Proceeding to access only the provided URL."
                $selenium=invokeSelenium $url
                }
        }else{
            write-warning "Please manually verify that the Selenium module is installed before retrying this function."
            }
        if($selenium){            
            if($exitIeWhenDone){
                $null=killInternetExplorer
                return $true
            }else{
                return $selenium
                }
        }else{
            write-warning "There were errors preventing a successful login."
            return $false
            }
        }
    catch {
        write-warning "$_"        
        return $false
        }
    
    # Note on a common error:
    #New-Object : Exception calling ".ctor" with "0" argument(s): "Unexpected error launching Internet Explorer. Protected
    #Mode settings are not the same for all zones. Enable Protected Mode must be set to the same value (enabled or
    #disabled) for all zones. (SessionNotCreated)"
    #At line:1 char:15
    #+ $seleniumIe = New-Object "OpenQA.Selenium.IE.InternetExplorerDriver"
    #+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    #    + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodInvocationException
    #    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand
    # Solution: either DISABLE or ENABLE Protected mode for ALL ZONES
}

# getEncryptionKey $dataEncryptionUrl $destinationCrmAdmin $destinationCrmPassword
function getEncryptionKey($dataEncryptionUrl,$username,$password){
    $command="autologinSe '$dataEncryptionUrl' '$username' '$password'"
    $ie=Invoke-Expression $command
    #$ie.Navigate().GoToURL($dataEncryptionUrl)
    $key=$ie.FindElementById('txtDisplayEncryptionKeyHidden').getAttribute('value')
    $ie.close()
    return $key
}

function setEncryptionKey($dataEncryptionUrl,$crmAdmin,$crmAdminPassword,$encryptionKey){
    $command="autologinSe '$dataEncryptionUrl' '$crmAdmin' '$crmAdminPassword' -exitIeWhenDone `$false"
    write-host $command
    pause
    $ie=Invoke-Expression $command
    #$ie.Navigate().GoToURL($dataEncryptionUrl)
    #$ie.FindElementById('txtDisplayEncryptionKeyHidden').getAttribute('value')
    $encryptionKeyField=$ie.FindElementById('txtRestoreEnterEncryptionKey')
    $activateButton=$ie.FindElementById('butRestoreEncryptionKey')
    $encryptionKeyField.sendKeys($encryptionKey)
    $activateButton.click()
    $null=$activateButton.click()
    return $true
    # $showEncryptionCheckbox=$ie.FindElementById('chkShowEncryptionKey').click()
    <#
    $setKeyValue=$ie.FindElementById('txtDisplayEncryptionKeyHidden').getAttribute('value')
    if($setKeyValue -eq $encryptionKey){
        write-host "Encryption key has been set successfully" -ForegroundColor Green
        return $true}
    else{
        write-warning "Encryption key set is $setKeyValue does not match the intended value of $encryptionKey"
        return $false}
    #>
}

function validateLogins{
    param(
        $url,$testUsers,$credDictionary
        )
    $validationResults=@{}
    foreach ($user in $testUsers){
        $testPassword=$credDictionary[$user]
        $ie=autoLoginSe $url $user $testPassword -exitIeWhenDone $true
        if($ie){$validationMessage="Success: $user is able to login to $url"}
        else{$validationMessage="Failure: $user is NOT able to login to $url"}
        write-host $validationMessage
        $validationResults.Add($user,$validationMessage)
    }
    return $validationResults
}

# Import CRM
#function sendKeysToProgram($programExe,$programTitle,$sendkeys,$waitSeconds){    
#    $processes=get-process
#    $processStarted=$programExe -in $processes.Path
#    if($processStarted){
#        $processMatch=$processes|?{$programExe -eq $_.Path}
#        stop-process $processMatch -Force
#        }    
#    start-process $programExe|out-null
#    $wshell = New-Object -ComObject wscript.shell;
#    $wshell.AppActivate($programTitle)|out-null
#    sleep $waitSeconds
#    $wshell.SendKeys($sendkeys)    
#    }
function importCrm{
    param(
        $destinationSqlServer,
        $databasename,
        $friendlyName,
        $reportServer,
        $mappingMethod='ByAccount'
    )

    $deploymentManagerExe="C:\Program Files\Dynamics 365\Tools\Microsoft.Crm.DeploymentManager.exe"
    $deploymentManagerTitle='Dynamics 365 Deployment Manager'
    $deploymentManagersendKeys='{DOWN 2}%{A}{DOWN}{ENTER}'
    $deploymentManagersendKeys=5
  
    try{
        Add-PSSnapin Microsoft.Crm.PowerShell
        Import-CrmOrganization -DatabaseName $databaseName `
                                            -SqlServerName $destinationSqlServer `
                                            -SrsUrl $reportServer `
                                            -DisplayName $friendlyName `
                                            -UserMappingMethod $mappingMethod `
                                            -Verbose
        }
    catch{
        write-warning "$error"
        write-host "Please proceed to import $databasename via the GUI"
        sendKeysToProgram $deploymentManagerExe $deploymentManagerTitle $deploymentManagersendKeys $deploymentManagersendKeys
        }
}

# Recreate Email Router
# To be executed on Destination App server
# Retrieve the email router config from the DB shipping log prior to executing this
function startEmailRouterGui{
    $emailRouterExecutable="C:\Program Files\Microsoft CRM Email\Service\Microsoft.Crm.Tools.Email.Management.exe"
    $emailRouterTitle='Email Router Configuration Manager'
    $emailRouterSendKeys="{TAB}{TAB}{ENTER}"
    $emailRouterWaitSeconds=3
    write-host "Now accessing Email Router Configuration Manager..."
    accessEmailRouterConfigManager $emailRouterExecutable $emailRouterTitle $emailRouterSendKeys $emailRouterWaitSeconds
}

function outputFunctionText($function){
    $functionObject=get-command $function
    $functionName=$functionObject.Name
    $functionBody=$functionObject.Definition
    return "Function $functionName{$functionBody}"
}

function generateRandomPassword{
    param(
        $minLength = 25,
        $maxLength = 40,
        $nonAlphaChars = 2,
        $excludeRegex='[:\$\%\&]',
        $replaceExclusionWith=@(',',';','!','/','{','^','+','-','*','_')
    )
    add-type -AssemblyName System.Web
    $randomLength = Get-Random -Minimum $minLength -Maximum $maxLength   
    $randomPassword = [System.Web.Security.Membership]::GeneratePassword($randomLength, $nonAlphaChars)
    $sanitizedPassword = $randomPassword -replace $excludeRegex,"$(Get-Random -InputObject $replaceExclusionWith)"
    $fixedRepeating = .{$rebuiltString=''
                        for ($i=0;$i -lt $sanitizedPassword.length;$i++){
                        $previousChar=$sanitizedPassword[$i-1]
                        $thisChar=$sanitizedPassword[$i]
                        $nextChar=$sanitizedPassword[$i+1]
                        if($thisChar -eq $nextChar){
                            do{
                                $regenChar=[char](Get-Random (65..122) )
                                }until($regenChar -ne $previousChar -and $regenChar -ne $nextChar)
                            $rebuiltString+=$regenChar
                            }
                        else{$rebuiltString+=$thisChar}
                        }
                        return $rebuiltString
                        }
                             
    return $fixedRepeating
}

function includePrerequisites{
    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

    # Prempt these errors: Install Choco to peruse its prepackaged libraries
    # Update-SessionEnvironment : The term 'Update-SessionEnvironment' is not recognized as the name of a cmdlet
    # The term 'refreshenv' is not recognized as the name of a cmdlet, function, script file, or operable program
    if (!(Get-Command choco.exe -ErrorAction SilentlyContinue)) {
        Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))
        # Defining $ENV:ChocotaleyInstall so that it would be called by refreshenv
        }
    $ENV:ChocolateyInstall = Convert-Path "$((Get-Command choco).Path)\..\.."   
    Import-Module "$env:ChocolateyInstall\helpers\chocolateyProfile.psm1" 
    if($PSVersionTable.PSVersion.Major -lt 5){choco install powershell -y}
    Update-SessionEnvironment
    if(!(Get-PackageProvider Nuget -ea SilentlyContinue)){Install-PackageProvider -Name NuGet -Force -confirm:$false}    
    if(!(Get-Module -ListAvailable -Name 'sqlserver' -ea SilentlyContinue)){Install-Module -Name 'sqlserver' -Force -Confirm:$false}
    import-module selenium
    if(!(get-module Selenium -ea SilentlyContinue)){Install-Module Selenium -Force -Confirm:$False}


}

function programCompletionWatcher{
    param(
        $targetMachine=$env:computername,
        $exeName='Microsoft.Crm.MultiTenantPackageDeploymentExecutor', #$exeName='mmc'
        $credential
        )
    $wizardWatcherClock=[System.Diagnostics.Stopwatch]::StartNew()
    if($credential){
        $destinationAppServerSession=New-PSSession $targetMachine -Credential $credential -SessionOption $(new-pssessionoption -IncludePortInSPN)
    }else{
        $destinationAppServerSession=New-PSSession $targetMachine -SessionOption $(new-pssessionoption -IncludePortInSPN)
        }

    if($destinationAppServerSession){      
        $checkCPU="Invoke-Command -Session `$destinationAppServerSession {return (Get-Process '$exeName' -EA SilentlyContinue).CPU}"        
        write-host "Checking CPU Usage..."
        $sameMeasurementCount=0
        $completionMarkerCount=10
        $almostComplete=$false
        $completionMarker=$false
        $previousCpuConsumption=0
        While (!$completionMarker) {            
            $currentCpuConsumption=invoke-expression $checkCPU -ea SilentlyContinue
            write-host $currentCpuConsumption
            $cpuConsumptionChanged=$currentCpuConsumption -gt $previousCpuConsumption
            $exeStarted=$currentCpuConsumption -ne $null
            if(!$exeStarted){
                write-host "$exeName has not started." -NoNewline
                }
            elseif(!$cpuConsumptionChanged){                               
                if ($sameMeasurementCount++ -ge $completionMarkerCount){
                    $almostComplete=$true
                    Write-Host "Almost complete: " -ForegroundColor Yellow -NoNewline
                    }                                
            }elseif($cpuConsumptionChanged -and $almostComplete){
                write-host "Completion marker reached!" -ForegroundColor Green -NoNewline
                $completionMarker=$true
                break
                }
            sleep -Seconds 10
            $previousCpuConsumption=$currentCpuConsumption
            }
        Remove-PSSession $destinationAppServerSession
        $minutesElapsed=$wizardWatcherClock.Elapsed.TotalMinutes
        return $minutesElapsed
    }else{
        write-warning "Unable to open a WinRM session to $targetMachine.`r`nPlease monitor it's progress manually."
        return $false
        }
}

################################### The functions below will be dependent on GLOBAL variables ###################################

function performValidation{
    # Validation Phase
    # required values: 
    # Logins
    write-host "Performing validation..."
    $validationResults=validateLogins $destinationUrl $testUsers $credentials|out-string|%{$_.trim()}
    appendContent $logFile "Validation:`r`n================================`r`n$(get-date)`t: test login results`r`n$validationResults" 

    # Encryption key
    write-host "Checking encryption key..."
    $existingEncryptionKey=getEncryptionKey $dataEncryptionUrl $destinationCrmAdmin $destinationCrmPassword
    if($existingEncryptionKey -eq $originalEncryptionKey){
        write-host "Encryption key matches."
        appendContent $logFile "$(get-date)`t: encryption key validated as $originalEncryptionKey"
    }else{
        write-warning "Encryption keys DO NOT MATCH."
        appendContent $logFile "$(get-date)`t: encryption keys NOT MATCHED.`r`nCurrent Key`t: $existingEncryptionKey v.s. Expected Key`t: $originalEncryptionKey"
        }
        
    # Email router
    if($validateEmailRouter){
        write-host "Checking email router at destination $destinationAppServer..."
        $destinationAppServerSession=New-PSSession $destinationAppServer -Credential $adminCredential -SessionOption $(new-pssessionoption -IncludePortInSPN)
        if($destinationAppServerSession){
            while(($destinationEmailRouter|out-string) -ne ($validateEmailRouter|out-string)){                
                $destinationEmailRouter=invoke-command -session $destinationAppServerSession -scriptblock{
                                            param($getEmailRouter,$orgName)
                                            return [ScriptBlock]::Create($getEmailRouter).invoke($orgName);
                                            } -Args ${function:getEmailRouter},$orgName|Select-Object -Property * -ExcludeProperty PSComputerName,RunspaceId,PSShowComputerName
                $identicalEmailRouters=($destinationEmailRouter|out-string) -eq ($validateEmailRouter|out-string)
                if($destinationEmailRouter -and $identicalEmailRouters){                        
                    $message='Source and destination email routers are identical.'
                }else{
                    $message="Source and destination email routers settings are different.`r`n$($destinationEmailRouter|out-string).trim())`r`nV.S.`r`n"
                    $message+="$(($validateEmailRouter|out-string).trim())`r`nPlease reconfigure email router to match."
                    write-host $message
                    pause
                    }
            }
            appendContent $logFile "$(get-date)`t: $message"
            Remove-PSSession $destinationAppServerSession
        }

        # Test sending email at destination
        write-host "Simulating send-mail at Destination server $destinationAppServer..."
        $destinationAppServerSession=New-PSSession $destinationAppServer -Credential $adminCredential -SessionOption $(new-pssessionoption -IncludePortInSPN)
        if($destinationAppServerSession){
            $emailUser=$validateEmailRouter.EmailUser
            $emailPassword=$validateEmailRouter.emailPassword
            $smtpServer=$validateEmailRouter.smtpServer
            $port=$validateEmailRouter.port
            $emailTo='testadmin@kimconnect.com'
            $cc=$null
            $subject="Test Email to Validate SMTP"
            $body="This is a test email.<br><br>Please disregard"                
            $testEmail=invoke-command -Session $destinationAppServerSession -ScriptBlock{
                            param($importedFunction,$a,$b,$c,$d,$e,$f,$g,$h)
                            [ScriptBlock]::Create($importedFunction).invoke($a,$b,$c,$d,$e,$f,$g,$h)                                
                            } -Args ${function:sendEmail},$EmailUser,$emailPassword,$emailTo,$cc,$subject,$body,$smtpServer,$port
            appendContent $logFile "$(get-date)`t: sendmail test at $destinationAppServer result = $testEmail" 
            Remove-PSSession $destinationAppServerSession
        }
        
        $startEmailRouter="Please set the email router at: $destinationAppServer`r`n$($validateEmailRouter|out-string).trim())`r`nEmail Router URL`t: $emailRouterUrl`r`nContinue when that process has completed"
        write-host $startEmailRouter -ForegroundColor Yellow
        pause
        appendContent $logFile "$(get-date)`t: Email routers have been recreated at the destination CRM server $destinationAppServer"
            
    }else{
        write-host 'No email routers to validate.'
        }

    # Simulate user email sending routines
    write-host "Send test email from inside CRM..."
    $ie=autologinSe $destinationUrl $destinationReportLoginId $destinationReportPassword
    $ie.FindElementById('TabGQC').click()
    $ie.FindElementById('4202').click()
    }

function obtainOriginalEncryptionKey{
    # Obtaining orignal encryption key
    write-host "Obtaining Data Encryption key..."
    return $(getEncryptionKey $dataEncryptionUrl $sourceCrmAdmin $sourceCrmPassword)
    #write-host "Found`t: $originalEncryptionKey"    
}

function obtainEmailRouter{
    write-host "Checking Email Router..."
    $emailRouterInfo=$originalEmailRouter=getEmailRouter $orgName
    if ($originalEmailRouter){            
            while ($emailRouterInfo){            
                if ($emailRouterInfo){
                    write-host "Email router detected."
                    write-host "Now accessing Email Router Configuration Manager > Deployments > select the Org name $orgname > Disable"
                    $sendkeys='{RIGHT}{TAB}'
                    $programExecutable="C:\Program Files\Microsoft CRM Email\Service\Microsoft.Crm.Tools.Email.Management.exe"
                    $programTitle='Email Router Configuration Manager'
                    sendKeysToProgram $programExecutable $programTitle $sendKeys 5
                    write-host "Continue when email router has been disabled."
                    pause
                    $emailRouterInfo=getEmailRouter $orgName
                }else{                   
                    appendContent $logFile "$(get-date)`t: Email router disabled."
                    }
            }
        }else{
        write-host "No email routers detected."
        }
    return $originalEmailRouter
}

function disableThisOrg ($orgName){
    write-host "Disabling Org $orgName..."
    disableOrg $sourceAppServer $orgName $adminCredential $logFile
    while ((getorg $orgname).State -eq 'Enabled'){
        write-host "Org name $orgname is currently 'enabled.' Please manually ensure that $orgName is disabled via GUI" -ForegroundColor Yellow
        pause
        }
    return $True
}

function exportDatabase{
    addUserToLocalGroup $sourceSqlServer $adminCredential $sourceSa # Ensuring that SA has access to SQL servers
    addUserToLocalGroup $destinationSqlServer $adminCredential $destinationSa

    $shipDatabaseCommand="shipDatabase -databaseName $databaseName -dbData $dbData -dbLog $dbLog -overwrite $overwriteFlag `
                -sourceSqlServer $sourceSqlServer -sourceSa $sourceSa -sourceSaPassword $sourceSaPassword `
                -destinationSqlServer $destinationSqlServer -destinationSa $destinationSa -destinationSaPassword $destinationSaPassword"
    write-host $shipDatabaseCommand
    write-host "Press Ctrl+C To Cancel"
    pause
    $dbShipResult=shipDatabase $databaseName $dbData $dbLog $overwriteFlag `
                                $sourceSqlServer $sourceSa $sourceSaPassword `
                                $destinationSqlServer $destinationSa $destinationSaPassword
    return $dbShipResult
}

function createMissingDbView{
    
    # Set these variables
    #$sqlServer=$destinationSqlServer
    #$saCred=$destinationSaCred
    #$databaseName
    $objectName='[dbo].[PrivilegeObjectTypeCodeView]'
    $objectType='view'
    $fromObject='[dbo].[PrivilegeObjectTypeCodes]'
    $orgVersion=$version
    
    $requireAddMissingView=$version -lt [version]'9.0'
    if(!$requireAddMissingView){
        return "Migrating from Version $orgVersion does not require generating $objectName"
        }

    $port5985Open=Test-NetConnection $destinationSqlServer -CommonTCPPort WINRM -InformationLevel Quiet -ea SilentlyContinue
    if ($port5985Open){
        $fixIsRequired=fixIsRequired -sqlServer $destinationSqlServer `
            -databaseName $databaseName `
            -objectName $objectName `
            -objectType $objectType `
            -fromObject $fromObject `
            -saCred $destinationSaCred
        if($fixIsRequired){
            $tsql=tsqlCommandToCreateObject -sqlServer $destinationSqlServer `
                        -databaseName $databaseName `
                        -objectName $objectName `
                        -objectType $objectType `
                        -fromObject $fromObject `
                        -saCred $destinationSaCred
            invokeTsql -sqlServer $destinationSqlServer `
                        -databaseName $databaseName `
                        -tSql $tsql `
                        -saCred $destinationSaCred
        }else{
            return "No fixes are required for $orgName"            
            }
    }else{
        return "Unable to create missing view due to WinRm connection failures."
        }
}

function version8to9Fixes{
    param(
        $fromVersion='8.0',
        $toVersion='9.0',
        $destinationSqlServer,
        $databaseName,
        $objectName='[dbo].[PrivilegeObjectTypeCodeView]',
        $objectType='view',
        $fromObject='[dbo].[PrivilegeObjectTypeCodes]',
        $destinationSaCred,
    )
 
    function tsqlCommandToCreateObject{
        param(
            [Parameter(Mandatory=$true)][String[]]$sqlServer=$env:computername,
            [Parameter(Mandatory=$true)][String[]]$databaseName,
            [Parameter(Mandatory=$true)][String[]]$objectName,
            [Parameter(Mandatory=$true)][String[]]$objectType,
            [Parameter(Mandatory=$true)][String[]]$fromObject,
            [Parameter(Mandatory=$true)]$saCred
            )
        # Validate input
        $validObjectTypes='view','procedure','table'
        $validatedObjectType=!(!($validObjectTypes|?{$_ -eq $objectType}))
        if(!$validatedObjectType){
            write-warning "Object type $objectType is invalid."
            return $null
            }
        $objectTypeAbbrevations=@{
            'view'='V'
            'procedure'='P'
            'table'='U'
            }
        $prepTSql="
            USE [$databaseName]
            GO
            DECLARE @sqlCmd nvarchar (max)
            BEGIN
                IF (OBJECT_ID('$objectName', '$($objectTypeAbbrevations[$objectType])')) IS NULL          
                BEGIN 
                    SELECT @sqlCmd = 'CREATE $objectType $objectName as SELECT * FROM $fromObject'
                    EXEC sp_executesql @sqlCmd
                END
            END
      
            "
        $tSql="
            USE [$databaseName]
            GO
            DECLARE @sqlCmd nvarchar (max)
            BEGIN
                IF (OBJECT_ID('$objectName', '$($objectTypeAbbrevations[$objectType])')) IS NULL          
                BEGIN 
                    SELECT @sqlCmd = 'CREATE $objectType $objectName as SELECT * FROM $fromObject'
                    EXEC sp_executesql @sqlCmd
                END
            END
      
            "
        return $tSql
    }

    function invokeTsql{
        param(
            [Parameter(Mandatory=$true)][String[]]$sqlServer=$env:computername,
            [Parameter(Mandatory=$true)][String[]]$databaseName,
            [Parameter(Mandatory=$true)][String[]]$tSql,
            [Parameter(Mandatory=$false)]$saCred
            )
        $ErrorActionPreference='stop'
    
        function includeSqlPs{
            if(!(get-command invoke-sqlcmd)){
                if(!('NuGet' -in (get-packageprovider).Name)){    
                    try{
                        #Preempt this error: Unable to resolve package source 'https://www.powershellgallery.com/api/v2'
                        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                        Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                        #Resolve this error: PackageManagement\Install-Package : No match was found for the specified search criteria and module name 'sqlps'. Try Get-PSRepository to see all available registered module repositories.
                        #Register-PSRepository -Default
                        #Register-PSRepository -Name PSGallery -InstallationPolicy Trusted -Verbose
                        }
                    catch{
                        write-warning $error[0].Exception.Message
                        }            
                    }
                Install-Module -Name 'sqlserver' -Force -Confirm:$false
                try{
                    Update-SessionEnvironment -ea stop
                    }
                catch{
                    # Prempt these errors: Install Choco to peruse its prepackaged libraries
                    # Update-SessionEnvironment : The term 'Update-SessionEnvironment' is not recognized as the name of a cmdlet
                    # The term 'refreshenv' is not recognized as the name of a cmdlet, function, script file, or operable program
                    # Install Chocolatey
                    if (!(Get-Command choco.exe -ErrorAction SilentlyContinue)) {
                    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                    Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))}
    
                    # Defining $ENV:ChocotaleyInstall so that it would be called by refreshenv
                                $ENV:ChocolateyInstall = Convert-Path "$((Get-Command choco).Path)\..\.."   
                                Import-Module "$env:ChocolateyInstall\helpers\chocolateyProfile.psm1"
                                Update-SessionEnvironment
                    }            
                }
                import-module 'SQLPS'
            }
    
        try{
            if ($saCred){
                $session=new-pssession $sqlServer -Credential $saCred
            }else{
                $session=new-pssession $sqlServer
                }
            if(!$session){
                write-warning "Unable to create a WinRM session toward $sqlServer"
                return $false
                }
    
            $sqlExecResult=invoke-command -Session $session {
                param($includeSqlPs,$databaseName,$tSql)        
                $ErrorActionPreference='stop'
    
                function confirmation($content,$testValue="I confirm",$maxAttempts=3){
                        $confirmed=$false;
                        $attempts=0;        
                        $content|write-host
                        write-host "Please review this content for accuracy.`r`n"
                        while ($attempts -le $maxAttempts){
                            if($attempts++ -ge $maxAttempts){
                                write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                                break;
                                }
                            $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
                            if ($userInput.ToLower() -eq $testValue.ToLower()){
                                $confirmed=$true;
                                write-host "Confirmed!`r`n";
                                break;                
                            }elseif($userInput -like 'cancel'){
                                write-host 'Cancel command received.'
                                $confirmed=$false
                                break
                            }else{
                                cls;
                                $content|write-host
                                write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                                }
                            }
                        return $confirmed;
                    }
    
                # Include SQL PowerShell tools
                [ScriptBlock]::Create($includeSqlPs).invoke()              
    
                $dbExists=(invoke-sqlcmd "SELECT CASE WHEN DB_ID('$databaseName') IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END").Column1         
                if($dbExists){
                    $confirmed=confirmation "Please confirm this T-SQL statement. Ctrl+C to Cancel`r`n$tSql"
                    if($confirmed){        
                        try{
                            invoke-sqlcmd -query $tSql
                            write-host "T-SQL has been committed successfully." -ForegroundColor Green
                            return $true
                        }catch{
                            write-host $error[0].Exception.Message -ForegroundColor Red
                            return $false
                            }
                    }else{
                        write-host "T-SQL has been cancelled by $(whoami)" -ForegroundColor Yellow
                        return $false
                        }
                }else{
                    write-warning "Database $databaseName does not match any valid DB on $env:computername"
                    return $false
                    }
                } -Args ${function:includeSqlPs},$databaseName,$tsql
            Remove-PSSession $session
            return $sqlExecResult
        }catch{
            Write-Warning $error[0].Exception.Message
            return $false
            }
    }

    $requireAddMissingView=$fromVersion -lt $toVersion
    if(!$requireAddMissingView){
        return "Migrating from Version $fromVersion does not require generating $objectName"
    }else{
		$tsql=tsqlCommandToCreateObject -sqlServer $destinationSqlServer `
                        -databaseName $databaseName `
                        -objectName $objectName `
                        -objectType $objectType `
                        -fromObject $fromObject `
                        -saCred $destinationSaCred
        invokeTsql -sqlServer $destinationSqlServer `
                    -databaseName $databaseName `
                    -tSql $tsql `
                    -saCred $destinationSaCred    
    }   
}

function migrateOrg{    
#### Initializing
    set-location C:\
    setGlobalVariables
    includePrerequisites
    if(!$isPowerShellVersion5){write-warning "Detected PowerShell version $($PSVersionTable.PSVersion.Major) is does not meet requirements of this program. Many warnings will be thrown during runtime."}

#### Checkpoint
    $confirmedProceed=confirmation "Are You Sure That $orgName Is To Be Disabled and Exported to $destinationSqlServer"    
    if (!$confirmedProceed){
        write-host "$orgName migration cancelled."
        return $false
        }
    
#### Proceeding
    $clock=[System.Diagnostics.Stopwatch]::StartNew()
        
#### Setting up log file
    if(!(test-path $logFile)){
        write-host "creating log file`t: $logFile"
        if(test-path $(Split-Path $logFile -Parent)){New-Item -itemType File -Path $(Split-Path $logFile -Parent) -Name $(Split-Path $logFile -Leaf) -Force}
        }
    appendContent $logFile "$(get-date)`t: Migration Org name $orgName started by $(whoami)`r`n===============================================================`r`nOrg ID`t: $orgId"

#### Original Encryption Key - requires selenium & PowerShell 5.1+
    $GLOBAL:originalEncryptionKey=obtainOriginalEncryptionKey
    appendContent $logFile "Data Encryption Key`t: $(if($originalEncryptionKey){$originalEncryptionKey}else{'N/A'})"
        
#### Obtaining email Router
    write-host "Checking Email Router..."
    $GLOBAL:validateEmailRouter=obtainEmailRouter
    if($validateEmailRouter){
        appendContent $logFile "$(get-date)`t: Email Router Information checked`r`n-------------------------`r`n$(($validateEmailRouter|out-string).Trim())`r`n-------------------------"
        }

#### Disable Org
    disableThisOrg $orgName
    appendContent $logFile "$(get-date)`t: Org $orgname disabled"

#### Metadata
    write-host "Updating Federation Meta Data for $orgName..."
    $sourceIfdResult=updateFederationMetadata $sourceAdfs $adminCredential $sourceEnvironment $sourceUrl 
    appendContent $logFile "$(get-date)`t: $sourceIfdResult"       
 
#### Database shipping
    $exportResult=exportDatabase
    appendContent $logFile "$(get-date)`t: $exportResult"        

#### V8 to V9 Considerations
    $missingViewCreationResult=createMissingDbView
    appendContent $logFile "$(get-date)`t: $missingViewCreationResult" 
    
#### Email DB Shipping notifications
    $databaseShipSubject=$emailSubject+"Dabase Export & Import"
    $databaseShipDetails=$emailBody+$dbShipResult
    sendEmail $emailUser $emailPass $sendEmailTo $copy $databaseShipSubject $databaseShipDetails
  
#### Updating Internal DNS
    $dnsUpdateResult=updateHostRecordOnDns -dnsServer $dnsServer -adminCred $adminCredential -record $aRecord -ip $recordIp -zone $zoneName;
    appendContent $logFile "$(get-date)`t: $dnsUpdateResult"
        
#### Updating External DNS
    write-host "Please update the sub-domain host record $orgName with this $newPublicIP at: $publicDnsUrl`r`nContinue when that process has completed"
    pause
    appendContent $logFile "$(get-date)`t: $orgName old public ip of $oldPublicIp has been updated with new public IP of $newPublicIP at $publicDnsUrl"
        
#### Importing CRM at Destination
    $importCrmCommmand="importCrm $destinationSqlServer $databasename '$friendlyName' $reportServer"
    #$importCrmCode=$(outputFunctionText 'sendKeysToProgram')+"`r`n"+$(outputFunctionText 'importCrm')+"`r`n"+$importCrmCommmand
    cls
    write-host $importCrmCommmand
    write-host "`r`nPlease use the code above to perform the CRM Import at the destination App server $destinationAppServer using this report server value $destinationReportingUrl`r`nThe DB must be set exactly as`t: $orgName`r`nPress Enter (Continue) when that process has been initiated."
    pause        
    
#### Check for CRM import completion
    #$destinationAppServerSession=New-PSSession $destinationAppServer -Credential $destinationSaCred -SessionOption $(new-pssessionoption -IncludePortInSPN)
    $wizardCompleted=programCompletionWatcher $destinationAppServer 'Microsoft.Crm.MultiTenantPackageDeploymentExecutor' $destinationSaCred
    if($wizardCompleted){
        # Email CRM Import completion
        $crmImportCompletionSubject=$emailSubject+"CRM Import"
        $crmImportCompletionBody=$emailBody+"Org $orgName import completed with a duration of $wizardCompleted minutes."
        sendEmail $emailUser $emailPass $sendEmailTo $copy $crmImportCompletionSubject $crmImportCompletionBody
        appendContent $logFile "$(get-date)`t: CRM Import has been completed at $destinationAppServer with a duration of $wizardCompleted minutes."
    }else{
        appendContent $logFile "$(get-date)`t: CRM Import has been completed at $destinationAppServer with a duration of unknown minutes."
        }

#### Updating IDF at destination
    $destinationIdfUpdateResult=invokeIfdUpdate $destinationAdfs $adminCredential $destinationEnvironment $destinationUrl
    if($destinationIdfUpdateResult){
        appendContent $logFile "$(get-date)`t: destination IDF result $destinationIdfUpdateResult"
    }else{
        $ifdErrorMessage="There's an error while updating IDF on $destinationAdfs"
        write-warning $ifdErrorMessage
        appendContent $logFile "$(get-date)`t: $ifdErrorMessage`r`n$destinationIdfUpdateResult"
        }
    pause           
      
#### Disabling integration
    write-host "Disabling integration for $orgName..."
    $toReEnableIntegration=disableIntegration $integrationServer $adminCredential $integrationFile $matchString $logFile

#### Local host files
    write-host "Updating Local Host Files on $sourceServers..."
    $hostFileUpdateResults=invokeUpdateHostFiles $adminCredential $appLocalIp $orgName $domain $searchString $sourceServers $true $logFile
    write-host $hostFileUpdateResults
    appendContent $logFile "$(get-date)`t: Host files update results`r`n--------------------------$(($hostFileUpdateResults|out-string).trim())--------------------------"      
        
#### Check if public DNS has been updated with the new IP
    $resolvedPublicIp=(Resolve-DnsName $recordName).IpAddress        
    if($resolvedPublicIp -ne $newPublicIP){
            write-warning "Cannot proceed while the $recordName resolved ip $resolvedPublicIp vs $newPublicIP don't match`r`nPlease verify that the public DNS server is set."
            # Will try to wait for DNS update for 10 minutes
            $retries=0
            while ($resolvedPublicIp -ne $newPublicIP -and $retries -lt 10){                      
                $retries++
                write-host "$retries`t: Attempting to clear DNS cache..."
                $dns1=(Get-WmiObject win32_networkadapterconfiguration | Where IPEnabled -eq $true).DNSServerSearchOrder[0]
                $dns1ServerName=([System.Net.Dns]::GetHostByAddress($dns1).HostName)
                invoke-command -Credential $adminCredential -ComputerName $dns1ServerName -ScriptBlock{Clear-DnsServerCache -Force;Clear-DnsClientCache}
                Clear-DnsClientCache
                $resolvedPublicIp=(Resolve-DnsName $recordName).IpAddress
                write-host "Resolved: $resolvedPublicIp vs Expected: $newPublicIP"
                start-sleep 60                
                }
            # Temporarily set host file
            updateHostFiles $newPublicIP $orgName $domain $searchString $sourceServers[0] $false
        }else{
        write-host "Public IP $resolvedPublicIp has been verified with destination environment successfully."
        }
        
#### Setting encryption at Destination
    write-host "Setting Data Encryption key..."
    # some old keys are in Chinese characters, which will not display properly on the Shell window
    $encryptionKey=if($originalEncryptionKey){$originalEncryptionKey}else{generateRandomPassword}
    write-host "Now applying this password as Encryption key: $encryptionKey"
    $setEncryptionCommand="setEncryptionKey '$dataEncryptionUrl' '$destinationCrmAdmin' '$destinationCrmPassword' '$encryptionKey'"
    $successfulEncryption=Invoke-Expression $setEncryptionCommand
    if(!$successfulEncryption){
            write-host "Program is unable to set encryption key. Please run this command from a different computer`t:`r`n$setEncryptionCommand`r`n`r`nContinue at this terminal when done..."
            pause
            }
    appendContent $logFile "$(get-date)`t: New encryption key has been set as $encryptionKey"

#### Check for whether there are custom reports
    write-host "Checking for custom reports..."
    write-host "Please manually check to see if there are reports and download them, if necessary.`r`nPlease copy this password before proceeding`r`nLogin as`t: $sourceReportLoginId`r`nPassword`t: $sourceReportPassword"
    pause
    $null=autologinSe $sourceReportingUrl
    pause
    write-host "Please manually check to see if there are reports to, if necessary.`r`nPlease copy this password before proceeding`r`nLogin as`t: $destinationReportLoginId`r`nPassword`t: $destinationReportPassword"
    pause
    $null=autologinSe $destinationReportingUrl
    pause
    appendContent $logFile "$(get-date)`t: custom reports checked."
    
    #$ie=autologinSe $sourceReportingUrl # this is broken due to IE auth popups
    #$hasReports=.{$ErrorActionPreference='stop'
    #                    try{
    #                        $ie.FindElementByCssSelector("a[href*='report/']")
    #                        return $true
    #                    }catch{
    #                        return $false
    #                        }
    #                }
    #if($hasReports){
    #        $reportMessage="Custom reports are detected."
    #        write-host $reportMessage -ForegroundColor Yellow
    #        write-host "`r`nPlease ensure that they are downloaded before proceeding" -ForegroundColor Yellow -NoNewline
    #        pause
    #        write-host "Please import these custom reports by opening this link on $destinationAppServer`t: $destinationReportingUrl`r`nResume when that has been completed" -ForegroundColor Yellow -NoNewline
    #        pause
    #    }else{
    #    $reportMessage="No custom reports to download."
    #    write-host $reportMessage            
    #    }
    #if($ie){try{$ie.close()}catch{}}

    performValidation
                          
    # Documentation
    write-host "Please manually update documentation at $documentationUrl using this information:`r`nName`t: $friendlyName`r`nURL`t: $sourceUrl`r`nOrgID`t: $orgId`r`n-------------------------------`r`n"
    pause
    write-host "Please access $secRepoUrl to create this pin: $encryptionKey"        
    pause
    appendContent $logFile "$(get-date)`t: Encryption key, POD IPs, and SSL Information Documentation has been updated."

#### Reanable Integration
    if($toReEnableIntegration){
        enableIntegration $integrationServer $adminCredential $integrationFile $matchString $logFile
        }

#### Wrapping up
    $totalHours=[math]::round($clock.Elapsed.TotalHours,2)
    $clock.stop()        
    $completionMsg="$(get-date)`t: $orgName Migration completed.`r`nMigration total duration`t: $totalHours hours`r`n==============================================================="
    write-host $completionMsg
    appendContent $logFile $completionMsg

    # Email migration results
    $migrationResultSubject=$emailSubject+"Migration Results"
    $migrationResultBody=$emailBody+ "Org $orgName migration."
    sendEmail $emailUser $emailPass $sendEmailTo $copy $migrationResultSubject $migrationResultBody -attachments $logFile

    Return $true
}


######################################################
migrateOrg

Leave a Reply

Your email address will not be published. Required fields are marked *