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 Upgrade_NightlyBuild { Param ( [parameter(Mandatory=$true,position=0)] [string] $ReleasePath , [string] $RestoreFrom = 'SRV-ONEWEBR' , [string] $Database , [string] $SQLInstance = 'BVT12' , [PSCredential]$SQLCredential , [switch] $GetLatest , [switch] $SkipRestore ) 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 # fGetLatest $ReleasePath try { Deploy-VisiDB @DeployParams -BuildInfo $buildInfo -debug } catch { # build failed #$to = @('listedbadevsql@visilab.ch') $to = @('mircea.nicolescu@visilab.ch') $mailParams = @{ From = 'Nightly Build <noreply@visilab.ch>' To = $to 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 Upgrade_NightlyBuild 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 } } |