VisiNightlyBuild

0.9.3

VisiNightlyBuild.psm1


Function Add-LKSBVT { 
    Param ( $SQlInstance )
    Process { 
         switch ($SQLInstance) { 
         'BVT11' { $port = '37011'; break } 
         'BVT12' { $port = '37012'; break } 
         } 

         #create LKS
         # in BVT LKS points towards itself 37011 for BVT1 , 37012 for BVT2
                                                                                                                                                                                                                                                                                                 $qry = "
         IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ONECM')
         EXEC master.dbo.sp_dropserver @server=N'ONECM', @droplogins='droplogins'
         EXEC master.dbo.sp_addlinkedserver
              @server = N'ONECM', -- destination server Name
              @srvproduct = N'',
              @provider = N'SQLNCLI',
              @datasrc = N'SRV-SQLDOCKER,$port', -- IP address of the destination server
              @catalog = N'master'; -- db Name ;
         EXEC master.dbo.sp_addlinkedsrvlogin
              @rmtsrvname = N'ONECM',-- destination server Name
              @useself = N'False',
              @locallogin = NULL,
              @rmtuser = N'sa', -- remote login name
              @rmtpassword = 'abcd1234##'; -- remote login password
 
         IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ARIZONACASH')
         EXEC master.dbo.sp_dropserver @server=N'ARIZONACASH', @droplogins='droplogins'
         EXEC master.dbo.sp_addlinkedserver
              @server = N'ARIZONACASH', -- destination server Name
              @srvproduct = N'',
              @provider = N'SQLNCLI',
              @datasrc = N'SRV-SQLDOCKER,$port', -- IP address of the destination server
              @catalog = N'master'; -- db Name
         EXEC master.dbo.sp_addlinkedsrvlogin
              @rmtsrvname = N'ARIZONACASH',-- destination server Name
              @useself = N'False',
              @locallogin = NULL,
              @rmtuser = N'sa', -- remote login name
              @rmtpassword = 'abcd1234##'; -- remote login password
 
         IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ACHATS')
         EXEC master.dbo.sp_dropserver @server=N'ACHATS', @droplogins='droplogins'
         EXEC master.dbo.sp_addlinkedserver
              @server = N'ACHATS', -- destination server Name
              @srvproduct = N'',
              @provider = N'SQLNCLI',
              @datasrc = N'SRV-SQLDOCKER,$port', -- IP address of the destination server
              @catalog = N'master'; -- db Name
         EXEC master.dbo.sp_addlinkedsrvlogin
              @rmtsrvname = N'ARIZONACASH',-- destination server Name
              @useself = N'False',
              @locallogin = NULL,
              @rmtuser = N'sa', -- remote login name
              @rmtpassword = 'abcd1234##'; -- remote login password
 
 
         IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'TRIAONE')
         EXEC master.dbo.sp_dropserver @server=N'TRIAONE', @droplogins='droplogins'
         EXEC master.dbo.sp_addlinkedserver
              @server = N'TRIAONE', -- destination server Name
              @srvproduct = N'',
              @provider = N'SQLNCLI',
              @datasrc = N'SRV-STRATEGICR\STRATEGICSQL,52454', -- IP address of the destination server
              @catalog = N'master'; -- db Name
         EXEC master.dbo.sp_addlinkedsrvlogin
              @rmtsrvname = N'TRIAONE',-- destination server Name
              @useself = N'False',
              @locallogin = NULL,
              @rmtuser = N'nightlybuild', -- remote login name
              @rmtpassword = 'abcd1234##'; -- remote login password
 
         IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'DATAPROXY')
         EXEC master.dbo.sp_dropserver @server=N'DATAPROXY', @droplogins='droplogins'
         EXEC master.dbo.sp_addlinkedserver
              @server = N'DATAPROXY', -- destination server Name
              @srvproduct = N'',
              @provider = N'SQLNCLI',
              @datasrc = N'SRV-SQLDOCKER,37011', -- IP address of the destination server
              @catalog = N'master'; -- db Name
         EXEC master.dbo.sp_addlinkedsrvlogin
              @rmtsrvname = N'ARIZONACASH',-- destination server Name
              @useself = N'False',
              @locallogin = NULL,
              @rmtuser = N'sa', -- remote login name
              @rmtpassword = 'abcd1234##'; -- remote login password
 
         "
         $server | Invoke-DbaQuery -Query $qry 
         Test-DbaLinkedServerConnection -SqlInstance $SQLInstance -SqlCredential $SQLCredential 
    } 
} 

function Start-NightlyBuildUpgrade { 
    Param (
        [parameter(Mandatory=$true,position=0)]
        [string] $ReleasePath , 
        [string] $RestoreFrom = 'SRV-ONEWEBR' , 
        [string] $Database ,
        [string] $SQLInstance = 'BVT12' , 
        [PSCredential]$SQLCredential , 
        [switch] $GetLatest ,
        [switch] $SkipRestore , 
        [string] $MailRecipient =  'listedbadevsql@visilab.ch'

    ) 
    Begin   {
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
            $DebugPreference = "Continue" 
        } 
        else { 
            $DebugPreference = "SilentlyContinue" 
        } 
    } 
    
    Process {
        $buildinfo = @{} 
        # restore bd from prod
        if (-not($SkipRestore.IsPresent)) {  
            Restore-DatabaseNightlyBuild -source $RestoreFrom -database $Database -destination $SQLInstance -SQLCredential $SQLCredential 
        } 

        # drop DDL trigger
        Invoke-DbaQuery -SqlInstance $SQLInstance -Database $Database  -SqlCredential $SQLCredential -Query "DROP TRIGGER IF EXISTS [trddl_create_object_control] ON DATABASE" -debug:$false

        # build
        $DeployParams = @{ 
            SQLInstance = $SQLInstance
            BuildAction = 'PartialBuild' 
            SQLCredential = $SQLCredential
            NoCLR = $true 
            Database = $Database
            IncludeIndex = $true 
            ReleasePath = $ReleasePath
        } 
        Write-Verbose $DeployParams 
        # get latest
        # moved to dedicated job step
        try { 
            Deploy-VisiDB @DeployParams -BuildInfo $buildInfo -debug 
        }
        catch { 
            # build failed
            $mailParams = @{ 
                From = 'Nightly Build <noreply@visilab.ch>' 
                To = MailRecipient 
                Subject = 'Nighlt build (upgrade) break' 
                Priority = "Normal"
                SmtpServer  = "smtp.visilabgroup.ch" 
                Body = "$($buildInfo.Message) $($buildInfo.file) $($buildInfo.dir)" 
            } 
            Send-MailMessage @mailParams 
            throw 'build failed' 
        }
    }    
} 

Set-Alias -Name Upgrade-NightlyBuild -Value Start-NightlyBuildUpgrade 
function Restore-DatabaseNightlyBuild { 
    Param (
        [string] $source , 
        [string] $database ,
        [string] $destination , 
        [PSCredential]$SQLCredential 
    ) 
    Begin   {
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
            $DebugPreference = "Continue" 
        } 
        else { 
            $DebugPreference = "SilentlyContinue" 
        } 
    } 
    Process{ 
        # set up paths - win <> linux
        $backupPath  = "\\srv-nas\backup\temp\nightlyBuildUpgrade\$database" 
        $restorePath  = "/var/opt/mssql/temp/nightlybuildupgrade/$database"  
        # cleanup old backups
        #if ( test-path $backupPath) {
            gci $backupPath -file | ? Name -match $database  | Remove-Item 
        #}
        #backup db
        Backup-DbaDatabase -SqlInstance $source -Database $database -Path $backupPath  -CreateFolder -CopyOnly -CompressBackup 
        #restore db
        Restore-DbaDatabase -SqlInstance $destination -SqlCredential $SQLCredential -Path $restorePath -DatabaseName $database -WithReplace  
    } 
} 


function Start-NightlyBuildNew { 
    Param (
        [string]$relPath ,
        [string]$Database , 
        [switch]$IncludeIndex, 
        [switch]$DisableVisiSysLog,
        [switch]$SkipGetLatest 
    )
    Process { 
        $buildInfo = @{} 
        $Params = @{ 
            SQLInstance= $SQLInstance 
            Database = $Database
            NoCLR = $true 
            ReleasePath = $relPath 
            SQLCredential = $SQLCredential 
        } 
        If ($IncludeIndex.IsPresent) { 
            $Params += @{IncludeIndex = $true } 
        } 
        If ($DisableVisiSysLog.IsPresent) { 
            $Params += @{DisableVisiSysLog = $true } 
        } 
        Write-Debug $params 

        # get latest moved to a dedicated job step

        try { 
            Deploy-VisiDB @params -BuildInfo $buildInfo 
        }
        catch { 
            # build failed
            $to = @('mircea.nicolescu@visilab.ch')
            $mailParams = @{ 
                From = 'Nightly Build <noreply@visilab.ch>' 
                To = $to 
                Subject = 'Nighlt build break' 
                Priority = "Normal"
                SmtpServer  = "smtp.visilabgroup.ch" 
                Body = "$($bi.Message) $($bi.file) $($bi.dir)" 
            } 
            Send-MailMessage @mailParams 
            throw "Build failed" 
        }
    }
}