VisiPsLib.psm1
|
function Truncate_Database { [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='High')] Param( $sqlInstance , $database ) Process{ $scriptPath = "$env:LOCALAPPDATA\Log\ScriptTruncateDB-$sqlInstance-$database" $scriptDrop = Join-Path $scriptPath "Drop-$(Get-Date -format "yyyyMMdd-HHmmss").sql" $scriptCreate = Join-Path $scriptPath "Create-$(Get-Date -format "yyyyMMdd-HHmmss").sql" New-Item $scriptPath -ItemType Directory -Force @($scriptDrop,$ScriptCreate) | ForEach-Object { New-Item $_ -ItemType File -Force } $opt = New-DbaScriptingOption $opt.IncludeIfNotExists = $true $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $SQLInstance $db = $SMOserver.Databases[$database] if($PSCmdlet.ShouldProcess("$SQLInstance.Database","Truncate all tables")){ # generate fk scripts Write-Output "Generating FK scripts..." $tables = $db.Tables #| select -First 100 for($i=0;$i -lt $tables.count; $i++){ $perct = ($i / $tables.count) * 100 $table = $tables[$i] #Write-Progress -Activity "Generate FK scripts" -Status "Table: $($table.Name)" -PercentComplete $perct Write-Progress -Activity "Generate FK script" -Status "Table: $($table.Schema +'.' + $($table.Name))" -PercentComplete $perct $table.EnumForeignKeys() | % { $opt.ScriptDrops = $true $db.Tables[$_.table_name, $_.table_schema].ForeignKeys[$_.Name] | Export-DbaScript -ScriptingOptionsObject $opt -FilePath $scriptDrop -Append | out-null $opt.ScriptDrops = $false $db.Tables[$_.table_name, $_.table_schema].ForeignKeys[$_.Name] | Export-DbaScript -ScriptingOptionsObject $opt -FilePath $scriptCreate -Append | out-null } } "Drop scripts saved to $scriptDrop" "Create scripts saved to $scriptCreate" "ok" # drop fk Write-Output "Dropping FK constraints..." Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $database -InputFile $scriptDrop "ok" # truncate tables Write-Output "Truncating tables..." #$db.Tables | % { $_.Name; $_.TruncateData() } $tables = $db.Tables | select -First 100 for($i=0;$i -lt $tables.count; $i++){ $perct = ($i / $tables.count) * 100 $table = $tables[$i] Write-Progress -Activity "Truncate table" -Status "Table: $($table.Schema +'.' + $($table.Name))" -PercentComplete $perct $table.TruncateData() } "ok" # recreate fk Write-Output "Recreating FK constraints..." Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $database -InputFile $scriptCreate "ok" # check table rowcount Write-Output "Checking table rowcount..." #Get-DbaDbTable -SqlInstance $sqlInstance -Database $database | ? Rowcount -gt 0 | % { throw "Table $($_.Name) not empty" } $tables = Get-DbaDbTable -SqlInstance $sqlInstance -Database $database for($i=0;$i -lt $tables.count; $i++){ $perct = ($i / $tables.count) * 100 $table = $tables[$i] Write-Progress -Activity "Check rowcount " -Status "Table: $($table.Schema +'.' + $($table.Name))" -PercentComplete $perct If ($table.Rowcount -gt 0 ) { Throw "Table not empty" } } "check tables ok" } } } Set-Alias -Name Truncate-database -Value Truncate_database function Disable-Telemetry{ [CmdletBinding(SupportsShouldProcess)] Param ($ComputerName) Process { $sb = { get-service | ? Name -match 'SQLWriter|telemetry' | % { Stop-Service -Name $_.Name -Force Set-Service -Name $_.Name -StartupType Disabled } ################################################## # Deactivate CEIP registry keys # ################################################## # Set all CustomerFeedback & EnableErrorReporting in the key directory HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server to 0 # Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\***\CustomerFeedback=0 # Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\***\EnableErrorReporting=0 # *** --> Version of SQL Server (100,110,120,130,140,...) # For the Engine # Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL**.<instance>\CPE\CustomerFeedback=0 # Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL**.<instance>\CPE\EnableErrorReporting=0 # For SQL Server Analysis Server (SSAS) # Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.<instance>\CPE\CustomerFeedback=0 # Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.<instance>\CPE\EnableErrorReporting=0 # For Server Reporting Server (SSRS) # Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.<instance>\CPE\CustomerFeedback=0 # Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.<instance>\CPE\EnableErrorReporting=0 # ** --> Version of SQL Server (10,11,12,13,14,...) ################################################## $Key = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server' $FoundKeys = Get-ChildItem $Key -Recurse | Where-Object -Property Property -eq 'EnableErrorReporting' foreach ($Sqlfoundkey in $FoundKeys) { $SqlFoundkey | Set-ItemProperty -Name EnableErrorReporting -Value 0 $SqlFoundkey | Set-ItemProperty -Name CustomerFeedback -Value 0 } ################################################## # Set HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\***\CustomerFeedback=0 # Set HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\***\EnableErrorReporting=0 # *** --> Version of SQL Server(100,110,120,130,140,...) ################################################## $WowKey = "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server" $FoundWowKeys = Get-ChildItem $WowKey | Where-Object -Property Property -eq 'EnableErrorReporting' foreach ($SqlFoundWowKey in $FoundWowKeys){ $SqlFoundWowKey | Set-ItemProperty -Name EnableErrorReporting -Value 0 $SqlFoundWowKey | Set-ItemProperty -Name CustomerFeedback -Value 0 } } # apply script block if ($PSCmdlet.ShouldProcess("[$ComputerName]", "Disable telemetry")) { Invoke-Command -ComputerName $computerName -ScriptBlock $sb -ea continue } } } function Disable-AuditTriggers { [CmdletBinding(SupportsShouldProcess)] <# .SYNOPSIS Disable or drop audit triggers .Description Disables or drops all triggers matching the name patter 'Audit' Default = Disable , DROP managed by switch parameter History 0.9.40 - created .PARAMETER SQLInstance Target server Pipeline OK .PARAMETER Database Target Database .PARAMETER DropTriggers Will performa DROP instead of DISABLE (default) .EXAMPLE Disable-AuditTriggers -SQLInstance SRV-MAG1 .EXAMPLE Get-VisiServers REC | Disable-AuditTriggers -Database ArizonaCash -DropTriggers #> Param( [parameter(Mandatory=$true,position=0,valueFromPipeline=$true )] [ValidateNotNullOrEmpty()] [alias('srv')] [string] $SQLInstance , [parameter(Mandatory=$false,position=1)] [alias('dbn')] [string] $Database = 'Arizona' , [parameter(Mandatory=$false,position=2)] [switch] $DropTriggers ) Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { Write-Verbose $SQLInstance Write-Verbose $Database $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $SQLInstance $SMOserver.databases[$Database].Tables | ForEach-Object { $triggers = $_.Triggers | Where-Object Name -match 'Audit' $triggers | ForEach-Object { Write-Verbose $_.Name if ($DropTriggers.IsPresent) { if ($PSCmdlet.ShouldProcess("[$SQLInstance].[$Database].$_", "Drop trigger")) { $_.Drop() } } else { if ($PSCmdlet.ShouldProcess("[$SQLInstance].[$Database].$_", "Disable trigger")) { $_.IsEnabled = $false $_.Alter() } } } } } } Function Restore-CaisseFromS1 { [CmdletBinding(SupportsShouldProcess)] <# .SYNOPSIS Restore ArizonaCash db from S1 to Caisse .Description .PARAMETER SQLInstance SQLInstance - caisse .EXAMPLE #> Param ( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)] [ValidateNotNullOrEmpty()] [string]$Caisse = $env:COMPUTERNAME , [parameter(Mandatory=$false,position=1)] [ValidateNotNullOrEmpty()] [string]$S1 , [parameter(Mandatory=$false,position=4)] [switch]$SkipDbRestore , [parameter(Mandatory=$false,position=5)] [string]$DbTransferNetworkShare = '\\srv-nas\backup\temp' , [parameter(Mandatory=$false,position=3)] [switch]$SkipPostRestoreDataOps , [parameter(Mandatory=$false,position=2)] [switch]$SkipConfigMacAddress ) Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { ## connect to Server $Server = Connect-DbaInstance -SqlInstance $Caisse $Database = 'ArizonaCash' ## get S1 name if ([string]::IsNullOrEmpty($s1)) { write-verbose 'get s1 name' $s1 = (invoke-sqlcmd -ServerInstance $Caisse -Query "EXEC('SELECT @@SERVERNAME as s1') AT [ArizonaCASH]").s1 write-verbose "S1 = $s1" write-verbose 'ok' } ## copy login ArizonaCASH from S1 $Login = 'ArizonaCash' Write-Verbose "Copy login ArizonaCASH from $s1" Copy-DbaLogin -Source $s1 -Destination $Caisse -Login $Login $server | Invoke-DbaQuery -Query "ALTER LOGIN $Login WITH DEFAULT_DATABASE=$Database" Write-Verbose 'ok' ## backup /restore DB if (-not($SkipDbRestore.IsPresent)) { Write-Verbose $("Restore ArizonaCASH from $s1" ) Copy-DbaDatabase -Source $s1 -Destination $Caisse -Database $Database -SharedPath $DbTransferNetworkShare -Force -BackupRestore -debug:$false Repair-DbaDbOrphanUser -SqlInstance $Caisse -Force -debug:$false Write-Verbose 'ok' } ## config MacAddress if (-not($SkipConfigMacAddress.IsPresent)) { write-verbose "--check if MAC values are not null @ $S1 " $qry = " IF EXISTS ( SELECT * FROM dbo.cr_point_of_sale WHERE CRPOS_logical_address IS NULL AND crpos_name <> 'OPTIC' ) BEGIN RAISERROR('CR_point_of_sale.CRPOS_logical_addres is NULL @ S1',16, -1) ; RETURN END IF EXISTS ( SELECT * FROM cr_pos_local_data WHERE crpld_key = 'MacAddress' AND crpld_value IS NULL ) BEGIN RAISERROR('CR_POS_local_data.CRPLD_value is NULL @ S1',16, -1) ; RETURN END " #Invoke-Sqlcmd -ServerInstance $s1 -Query $qry -Database ArizonaCASH -ea Stop $qryGetMac = " SELECT crpos_logical_address as mac FROM dbo.cr_point_of_sale WHERE CRPOS_logical_address IS NOT NULL AND crpos_name <> 'OPTIC' " $mac = (invoke-sqlcmd -ServerInstance $s1 -Database ArizonaCASH -Query $qryGetMac ).mac Write-Debug $qryGetMac Write-Debug "Mac = $mac" # write Mac Address to db $qry = " -- update CR_POS_local_data with local MAC UPDATE dbo.CR_POS_local_data SET CRPLD_value = '$mac' WHERE crpld_key = 'MacAddress' " Write-Debug $qry $server | Invoke-DbaQuery -Query $qry -Database ArizonaCash Write-Verbose 'ok' } ## remove Audit Triggers Disable-AuditTriggers -SQLInstance $Caisse -Database $Database -DropTriggers ## data ops post restore if (-not($SkipDataOps.IsPresent)) { write-verbose "DataOps" $qry = " EXEC dbo.aps_cr_pos_local_data_create; UPDATE cr_pos_local_data SET CRPLD_Value = 3 WHERE crpld_key = 'Databaselevel' -- truncate tables TRUNCATE TABLE dbo.CR_TT_Open_Ticket_List TRUNCATE TABLE dbo.cr_eft_transaction DELETE dbo.cr_sales_operation DELETE dbo.cr_sales_item DELETE dbo.cr_sales_header DELETE dbo.cr_cash_report_line DELETE dbo.cr_cash_report_version DELETE dbo.cr_cash_report_header DELETE dbo.cr_master_cash_report " Write-Verbose $qry #Invoke-Sqlcmd -Database ArizonaCASH -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 15000 $server | Invoke-DbaQuery -Database $Login -Query $qry -QueryTimeout 15000 write-verbose 'ok' } } # end process } # end function Function Deploy_VisiDB_v4 { <# .SYNOPSIS Visilab SQL Db Build .Description Builds a given releasePath ( folder or object) against a SQL database Version 0.9.23 - parameter SkipGetLatest deprecated - default behaviour will NOT get latest from TFS, -GetLAtest parameter added to force this operation Version 0.9.42 - buildInfo now returns the details of the log file .PARAMETER ReleasePath The path or object to build .PARAMETER srv Target SQL Server .PARAMETER dbn Target Database .PARAMETER BuildAction Choose among New, UpgradeDB, UpgradeBoth or PartialBuild Default Partial Build .PARAMETER SkipGetLatest Will NOT perform a tf get if specified Deprecated - replaced by -GetLatest .PARAMETER NoTranscript Will NOT save the build details in D:\MSSQL\SQLJobsLog if present .PARAMETER DisableVisiSysLog Will NOT write to Visisys.Log if present .PARAMETER BuildInfo Will return a hash containing the build result ( used for nightly build logging ) .PARAMETER GetLatest Will perform a TFS GET -recursive on the provided releasePath .PARAMETER IncludeIndex Will include any file having INDEX in the name Index files are excluded by default .EXAMPLE Build-VisiDB -srv SRV-ONECMR bla bla bla #> [cmdletbinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({test-path $_ })] [alias('RelPath')] [string] $ReleasePath , [parameter(Mandatory=$true,position=1,valueFromPipeline=$true )] [ValidateNotNullOrEmpty()] [alias('Srv')] [string] $SQLInstance , [parameter(Mandatory=$false,position=2)] [alias('dbn')] [string] $Database = 'Arizona' , [parameter(Mandatory=$false,position=3)] [ValidateSet('New','UpgradeBD', 'UpgradeSOFT', 'UpgradeBOTH', 'PartialBuild', ignorecase=$True)] [string] $BuildAction = 'PartialBuild' , [parameter(Mandatory=$false,position=4)] [switch] $GetLatest = $false , [parameter(Mandatory=$false,position=5)] [switch] $NoTranscript = $false , [parameter(Mandatory=$false,position=6)] [Alias('DisableVisiSysLog')] [switch] $NoLog = $false , [parameter(Mandatory=$false,position=7)] [hashtable]$BuildInfo = @{} , [parameter(Mandatory=$false,position=8)] [Alias('NoIndex')] [switch] $ExcludeIndex , [parameter(Mandatory=$false,position=9)] [PSCredential]$SQLCredential , [parameter(Mandatory=$false,position=10)] [Alias('ExcludeCLR')] [Switch]$NoCLR ) # end param Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } $ErrorActionPreference = 'Stop' } Process { ## attempt to stop transcription if (-not($noTranscript.IsPresent)) { try{stop-transcript|out-null} catch [System.InvalidOperationException]{} } ## init vars $log_id = $null $Global:Error.Clear() $exclude = 'NORUN' ## prepare connection $sqlParams = @{ ServerInstance = $SQLInstance } if ($SQLInstance -match 'BVT'){ $sqlParams += @{ Credential = $SQLCredential } } ## populate BuildInfo $BuildInfo.SQLInstance = $SQLInstance $BuildInfo.Database = $Database $BuildInfo.ReleasePath = $ReleasePath $BuildInfo.TimeStamp = $(get-date) ## start transcription if (-not($noTranscript.IsPresent)) { $logFile = Join-Path -Path $env:LOCALAPPDATA -ChildPath "Log\Build_$($dbn)_$(Split-Path $ReleasePath -Leaf)_$($buildAction)_$($srv).$($(Get-Date -Format s ) -ireplace ':', '-' ).log" Write-Debug $("[100] Log file = {0}" -f $logFile ) Start-Transcript $logFile $BuildInfo.LogFile = $logFile } try { ### get latest from TFS if ($GetLatest.IsPresent) { if ($VerbosePreference -eq "Continue" ) { tf get $ReleasePath /recursive Write-Debug '[200]get' } else { tf get $ReleasePath /recursive | Out-Null Write-Debug '[200] get quiet' } Write-Debug "[200]TF Get Ok " } ##get last changeSet try { Write-Debug '[300] start change set ' $changeSet = @(tf history $ReleasePath /stopafter:1 /noprompt /recursive)[2].Split(' ')[0] } catch { $changeSet = -1 } Write-Debug "[300]Get Changeset ok, changeset = $changeSet" $BuildInfo.ChangeSet = $changeSet Write-Debug "$buildAction build starting" if ($ExcludeIndex.IsPresent) { $exclude += '|INDEX' } if ($NoCLR.IsPresent) { $exclude += '|CLR' } Write-Debug "Exclude = $exclude" switch ($buildAction.ToUpper()) { 'NEW' { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -imatch '100\.New' -and $_.FullName -inotmatch $exclude } } 'PARTIALBUILD'{ $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -inotmatch $exclude } } 'UPGRADEBD' { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -imatch '800\.UpgradeBD' -and $_.FullName -inotmatch $exclude } } 'UPGRADESOFT' { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -imatch '801\.Upgrade' -and $_.FullName -inotmatch $exclude } } 'UPGRADEBOTH' { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {($_.FullName -imatch '801\.Upgrade' -or $_.FullName -imatch '800\.UpgradeBD' ) -and $_.FullName -inotmatch $exclude } } } $fileCount = ($files |Measure-Object).Count $BuildInfo.FileCount = $($fileCount) Write-Debug $("[400]Release path = {0} " -f $ReleasePath ) Write-Debug $("[410]Files OK Count = {0} " -f $fileCount) ## check if there is something to build if ($fileCount -eq 0) { Write-Warning "No files to build were found" } ### log build start on target server Write-Debug '[420]Log build start ' try { $qry = "DECLARE @log_id UNIQUEIDENTIFIER; EXEC dbo.sp_Save_VisiSys_Log @Id=@log_id OUTPUT,@Domain='DB_Build',@Module='$dbn',@Message='$ReleasePath',@note='$buildAction',@RowCount='$changeSet';SELECT @log_id as log_id;" Write-Debug $qry $log_id=(Invoke-Sqlcmd @sqlParams -Query $qry).log_id Write-Debug "[421]Log build start ok" } catch { # do not fail deploy if log fails ; this is done on purpose Write-Debug "[422]VisiSys.Log failed "; $_.Exception.Message } ## start deploy try { foreach ($file in $files | Sort-Object { $_.FullName -replace "\W", 'z' }) { #Write-Output $($SQLInstance + '.' + $Database + " " + [char]0x21e8 + " " + $(split-path $(Split-Path $file.FullName ) -leaf) + '/' + $file.BaseName) Write-Output $($SQLInstance + '.' + $Database + " " + [char]0x2192 + " " + $(split-path $(Split-Path $file.FullName ) -leaf) + '/' + $file.BaseName) #Write-Verbose $($file.DirectoryName -replace '^d\:\\sources\\\w+\\', '' ) Write-Debug "[510] Object $file" Invoke-Sqlcmd @sqlParams -database $Database -InputFile $($file.FullName) -QueryTimeout 10000 Write-Debug "[511] Object OK " } ### log build OK $qry = "EXEC dbo.sp_Save_VisiSys_Log @Id = '$log_id', @note = '$buildAction OK', @status='0'" Write-Debug $qry try { #$server | Invoke-DbaQuery -Query $qry Invoke-Sqlcmd @sqlParams -Query $qry } catch { Write-Debug "VisiSys log failed" ; $_.Exception.Message } } #end try catch { Write-Debug "[530] Object $file NOK - start build failed ops " ## gather custom error info $BuildInfo.ErrorMessage = $_.Exception.Message $BuildInfo.ErrorFile = $file.Name $BuildInfo.ErrorDir = $file.DirectoryName $BuildInfo.ErrorTimeStamp = get-date $BuildInfo.Outcome = 'Error' #### log db build error Write-Debug "[531]] Log build failed" try { $qry ="EXEC dbo.sp_Save_VisiSys_Log @Id='$log_id',@note='Build $buildAction FAILED',@status= '1',@remark='$($buildInfo.Message -replace "'", "''")',@fileName='$($file.Name)',@parent='$($file.DirectoryName)'" Write-Debug "[532] $qry " Invoke-Sqlcmd @sqlParams -Query $qry Write-Debug "[533] Log build failed ok" } catch { # again do not fail this if log failed Write-Debug "[535] Visisys log failed"; $_.Exception.Message } ##throw error Write-Debug "[555] $($BuildInfo.ErrorFile) " Write-Debug "[556] $($BuildInfo.ErrorMessage)" Write-Debug "[557] $($BuildInfo.ErrorDir) " throw "Build failed " } #end catch } # end outer try finally { # output build info for log purposes Write-Output ($BuildInfo | Out-String ) if (-not($noTranscript.IsPresent)) { Stop-Transcript } } #end outer finally } #end process } #end function Set-Alias -Name Build-VisiDb -Value Deploy_VisiDB_v4 Set-Alias -Name Deploy-VisiDB -Value Deploy_VisiDB_v4 function Find-SysadminMembers { <# .SYNOPSIS Looks for sysadmin members in ALL S1 and Caisses Saves results in ONECM.VisiSystem.Control.SysadminMembers Sends an email with results (if any). Caisses down saved in Control.CaisseDown .Description Looks for sysadmin members in ALL S1 and Caisses Saves results in ONECM.VisiSystem.Control.SysadminMembers Sends an email with results (if any). .PARAMETER MailRecipients Recipients for report email Default "listeDba@visilab.ch" .PARAMETER ExcludeList Accepted sysadmin members Default "sa|ssi-dba|ssi-sqladm|visiSQLAdmin .EXAMPLE # Find-SysadminMembers .EXAMPLE # Find-SysadminMembers -MailRecipients 'mircea.nicolescu@visilab.ch' .EXAMPLE # Find-SysadminMembers -ExcludeList 'sa|ssi-dba' #> [CmdletBinding(SupportsShouldProcess)] Param ( [parameter(Mandatory=$false,position=0)] [string] $mailRecipients = 'listeDBA@visilab.ch' , [parameter(Mandatory=$false,position=1)] [string] $excludeList = 'sa|ssi-dba|ssi-sqladm|visiSQLAdmin' ) Begin { } Process{ $srv = 'SRV-ONECM' $dbn = 'VisiSystem' $table = 'Control.SysadminMembers' $tableCaisseDown = 'Control.CaisseDown' $title = 'Non-dba Sysadmin SQL Accounts' # process servers Get-DbaServerRoleMember -SqlInstance $(gvsp) -ServerRole sysadmin | Where-Object Name -notmatch $excludeList | Write-DbaDataTable -SqlInstance $srv -Database $dbn -Table $table -AutoCreateTable -Truncate # process caisses Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query "IF OBJECT_Id('$tableCaisseDown','U') IS NOT NULL TRUNCATE TABLE $tableCaisseDown" Get-VisiCaisses -scope Prod | ForEach-Object { Write-Verbose $_.crpos_name if (Test-Connection -ComputerName $($_.crpos_name) -Quiet -Count 1 ) { Get-DbaServerRoleMember -SqlInstance $($_.crpos_name) -ServerRole sysadmin | Where-Object Name -notmatch $excludeList | Write-DbaDataTable -SqlInstance $srv -Database $dbn -Table $table -AutoCreateTable } else { $_ | Write-DbaDataTable -SqlInstance $srv -Database $dbn -Table $tableCaisseDown -AutoCreateTable } } # add time_stamp to Caisses_down $qryAlterTable = "alter table $tableCaisseDown ADD time_stamp datetime2(0) not null default getdate()" invoke-sqlcmd -serverinstance $srv -database $dbn -query $qryAlterTable # send email $qry = " IF EXISTS ( SELECT 1 FROM $dbn.$table ) BEGIN DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<H1>$title</H1>' + N'<table border=`"1`">' + N'<tr><th>Computer Name</th>' + N'<th>InstanceName</th>' + N'<th>SQL Instance</th>' + N'<th>Role</th>' + N'<th>Name</th>'+ N'</tr>' + CAST ( ( SELECT td = ComputerName, '', td = InstanceName, '', td = SQlinstance, '', td = Role, '', td = Name FROM $dbn.$table ORDER BY ComputerName ASC, [name] ASC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; IF EXISTS ( SELECT 1 from VisiSystem.Control.CaisseDown ) BEGIN DECLARE @tableHTML2 NVARCHAR(MAX) ; SET @tableHTML2 = N'<H1>Caisses down</H1>' + N'<table border=`"1`">' + N'<tr><th>CRPOS Name</th>' + N'<th>Sub short name</th>'+ N'</tr>' + CAST ( ( SELECT td = CRPOS_name, '', td = Sub_short_name FROM VisiSystem.Control.CaisseDown ORDER BY CRPOS_Name ASC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; SELECT @tableHTML = @tableHTML + @tableHTML2 END EXEC msdb.dbo.sp_send_dbmail @recipients='$MailRecipients', @subject = '$title', @body = @tableHTML, @body_format = 'HTML' END " Write-Verbose $qry Invoke-Sqlcmd -ServerInstance $srv -Query $qry } } function Compare-Procedures { <# .SYNOPSIS Compare procedures code and VisiVersion between 2 servers .Description Compare results are saved on Server1.VisiSystem.Control.Objects .PARAMETER SQLInstance1 Server1 Will persist the comparison results in VisiSystem.Control.Objects .PARAMETER SQLInstance2 Server2 .PARAMETER Database1 Database1 .PARAMETER Database2 Database12 .PARAMETER MailRecipients Recipients for report email Default "listeDbaDevSql@visilab.ch;eric.colombara@visilab.ch" .PARAMETER ProcedurePattern TSQL LIKE pattern for procedure name filter .EXAMPLE # Compare-Procedures -SQLInstance1 SRV-ONECM SQLInstance2 SRV-ONEWEB -Database1 Arizona -Database2 Arizona -ProcedurePattern '%sel%' -MailRecipients mircea.nicolescu@visilab.ch .EXAMPLE # $params = @{ SQLInstance1 = 'ONECM' SQLInstance2 = 'BVT1' Database1 = 'Arizona' Database2 = 'Arizona_ref' MailRecipients = 'mircea.nicolescu@visilab.ch' ProcedurePattern = '%' Verbose = $true } Compare-Procedures @params #> [CmdletBinding(SupportsShouldProcess)] Param ( [parameter(Mandatory=$true,position=0)] [ValidateNotNullOrEmpty()] [string] $SQLInstance1 , [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrEmpty()] [string] $SQLInstance2 , [parameter(Mandatory=$true,position=2)] [ValidateNotNullOrEmpty()] [string] $Database1 , [parameter(Mandatory=$true,position=3)] [ValidateNotNullOrEmpty()] [string] $Database2 , [parameter(Mandatory=$false,position=4)] [string] $MailRecipients = 'listeDbaDevSql@visilab.ch;eric.colombara@visilab.ch' , [parameter(Mandatory=$false,position=5)] [string] $ProcedurePattern = '%' ) Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } try { Invoke-Sqlcmd -ServerInstance $SQLInstance1 -Query "select 1" -ErrorAction Stop -ConnectionTimeout 2 | Out-Null } catch { Throw "Connection to $SQLInstance1 failed" } try { Invoke-Sqlcmd -ServerInstance $SQLInstance2 -Query "select 1" -ErrorAction Stop -ConnectionTimeout 2 | Out-Null } catch { Throw "Connection to $SQLInstance2 failed" } } Process { $ErrorActionPreference = "stop" $server1 = $SQLInstance1 $server2 = $SQLInstance2 # create results table if not exists $resultsTableQry = " IF OBJECT_ID('Control.Objects','U') IS NULL CREATE TABLE Control.[Objects] ( Id INT IDENTITY PRIMARY KEY , Server_1 SYSNAME , Server_2 SYSNAME , [Database_1] SYSNAME , [Database_2] SYSNAME , [Name] SYSNAME , [Type] CHAR(2) , Diff_type VARCHAR(50) , Modify_date_1 DATETIME2(0) , Modify_date_2 DATETIME2(0) , Visi_version_1 CHAR(10) , Visi_version_2 CHAR(10) , Hb_1 VARBINARY(8000) , Hb_2 VARBINARY(8000) , Control_time DATETIME2(0) DEFAULT CURRENT_TIMESTAMP , Control_user SYSNAME DEFAULT CURRENT_USER ) EXEC sp_save_visiVersion 'Control.Objects', '101.2'; " Invoke-Sqlcmd -ServerInstance $Server1 -Database Visisystem -QueryTimeout 5 -Query $resultsTableQry # build collect procs query $qryProcs = " DROP TABLE IF EXISTS #procs ; CREATE TABLE #procs (proc_name SYSNAME PRIMARY KEY, create_Date DATETIME2, modify_date DATETIME2, visi_version CHAR(10), [code] VARCHAR(MAX), hb VARBINARY(8000)) ; INSERT #procs SELECT CONCAT(s.name, '.', p.name) AS [proc_name] , p.create_date , p.modify_date , CONVERT(CHAR(10), v.value) AS visi_version , sm.definition AS [code] , NULL AS hb FROM sys.procedures AS p JOIN sys.schemas AS s ON s.[schema_id] = p.[schema_id] JOIN sys.sql_modules AS sm ON sm.[object_id] = p.[object_id] OUTER APPLY ( SELECT * FROM sys.fn_listextendedproperty('VisiVersion' , 'schema', s.name, 'procedure', p.name, NULL, NULL ) ) v WHERE p.name NOT LIKE 'msmerge%' AND p.is_ms_shipped = 0 --and s.name <>'Control' AND p.name LIKE '$ProcedurePattern' ; UPDATE #procs SET code = REPLACE([code],';','') ; -- eliminate false positives caused by ending semicolon UPDATE #procs SET hb = hashbytes('SHA2_256', [code]) ; ALTER TABLE #procs DROP COLUMN [code] ; SELECT * FROM #procs ; " # collect procs info # create tempdb tables - this must be done manaually as AutoCreate messes up the datatypes $qry = " DROP TABLE IF EXISTS dbo.local_procs CREATE TABLE local_procs (proc_name SYSNAME PRIMARY KEY, create_Date DATETIME2, modify_date DATETIME2, visi_version CHAR(10), hb VARBINARY(8000)) ; DROP TABLE IF EXISTS dbo.remote_procs CREATE TABLE remote_procs (proc_name SYSNAME PRIMARY KEY, create_Date DATETIME2, modify_date DATETIME2, visi_version CHAR(10), hb VARBINARY(8000)) ; " Invoke-Sqlcmd -ServerInstance $server1 -Database Tempdb -Query $qry # populate local_proce and remote_procs Write-Verbose "Collect procs info $server1" Invoke-Sqlcmd -ServerInstance $server1 -Database $database1 -Query $qryProcs -QueryTimeout 10000 | Write-DbaDbTableData -SqlInstance $server1 -Database tempdb -Table dbo.local_procs -Truncate Write-Verbose 'ok' Write-Verbose "Collect procs info $server2" Invoke-Sqlcmd -ServerInstance $server2 -Database $database2 -Query $qryProcs -QueryTimeout 10000 | Write-DbaDbTableData -SqlInstance $server1 -Database tempdb -Table dbo.remote_procs -Truncate Write-Verbose 'ok' # process results Write-Verbose "Process results" $objType = 'P' $qryDiff = " --------------------------- --delete from results table --------------------------- DELETE Visisystem.control.Objects WHERE [Type] = '$objType' AND server_1 = '$server1' AND server_2 = '$server2' AND database_1 = '$database1' AND database_2 = '$database2' ; ----------------------- --miss local (server 1) ----------------------- ;WITH x AS ( SELECT proc_name FROM tempdb.dbo.remote_procs AS rp EXCEPT SELECT proc_name FROM tempdb.dbo.local_procs AS lp ) INSERT [Visisystem].[Control].[Objects]( Server_1 , Server_2 , [Database_1] , [Database_2] , [Name] , [Type] , Diff_type , Modify_date_1 , Modify_date_2 , Visi_version_1 , Visi_version_2 , Hb_1 , Hb_2 ) SELECT '$server1' , '$server2' , '$database1' , '$database2' , rp.proc_name , '$objType' , 'MISS_$Server1' , NULL , rp.modify_date , NULL , rp.visi_version , NULL , NULL FROM tempdb.dbo.remote_procs AS rp JOIN x AS x ON x.proc_name = rp.proc_name ------------------------ --miss remote (server 2) ------------------------ ;WITH y as ( SELECT proc_name FROM tempdb.dbo.local_procs AS lp EXCEPT SELECT proc_name FROM tempdb.dbo.remote_procs AS rp ) INSERT [Visisystem].[Control].[Objects]( Server_1 , Server_2 , [Database_1] , [Database_2] , [Name] , [Type] , Diff_type , Modify_date_1 , Modify_date_2 , Visi_version_1 , Visi_version_2 , Hb_1 , Hb_2 ) SELECT '$server1' , '$server2' , '$database1' , '$database2' , lp.proc_name , '$objType' , 'MISS_$Server2' , lp.modify_date , NULL , lp.visi_version , NULL , NULL , NULL FROM tempdb.dbo.local_procs AS lp JOIN y AS y ON y.proc_name = lp.proc_name ; ------------------------ -- different visiVersion ------------------------ INSERT [Visisystem].[Control].[Objects]( Server_1 , Server_2 , [Database_1] , [Database_2] , [Name] , [Type] , Diff_type , Modify_date_1 , Modify_date_2 , Visi_version_1 , Visi_version_2 , Hb_1 , Hb_2 ) SELECT '$server1' , '$server2' , '$database1' , '$database2' , rp.proc_name , '$objType' , 'DIFF_VISIVERSION' , lp.modify_date , rp.modify_date , lp.visi_version , rp.visi_version , NULL--lp.hb , NULL--rp.hb FROM tempdb.dbo.local_procs AS lp JOIN tempdb.dbo.remote_procs AS rp ON rp.proc_name = lp.proc_name WHERE rp.visi_version <> lp.visi_version --------------------------------- --same VisiVersion different code --------------------------------- INSERT [Visisystem].[Control].[Objects]( Server_1 , Server_2 , [Database_1] , [Database_2] , [Name] , [Type] , Diff_type , Modify_date_1 , Modify_date_2 , Visi_version_1 , Visi_version_2 , Hb_1 , Hb_2 ) SELECT '$server1' , '$server2' , '$database1' , '$database2' , rp.proc_name , '$objType' , 'DIFF_CODE' , lp.modify_date , rp.modify_date , lp.visi_version , rp.visi_version , lp.hb , rp.hb FROM tempdb.dbo.local_procs AS lp JOIN tempdb.dbo.remote_procs AS rp ON rp.proc_name = lp.proc_name WHERE ISNULL(rp.hb,-1) <> ISNULL(lp.hb,-1) AND rp.visi_version = lp.visi_version " # copy to clipboard #[Windows.forms.Clipboard]::SetText($qryDiff) Invoke-Sqlcmd -ServerInstance $server1 -Database Tempdb -Query $qryDiff Write-Verbose 'ok' #send email report $qryMail = " DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<H1>Procedures Control Report</H1>' + N'<table border=`"1`">' + N'<tr><th>Server 1</th>' + N'<th>Server 2</th>' + N'<th>Database 2</th>' + N'<th>Database 2</th>' + N'<th>Object name</th>' + N'<th>Object type</th>' + N'<th>Diff type</th>' + N'<th>Modify date 1 </th>'+ N'<th>Modify date 2 </th>'+ N'<th>VisiVersion 1 </th>' + N'<th>VisiVersion 2 </th>' + N'<th>Control time </th>' + N'<th>Control user</th></tr>' + CAST ( ( SELECT td = Server_1, '', td = Server_2, '', td = Database_1, '', td = Database_2, '', td = [Name], '', td = [Type], '', td = Diff_type, '', td = ISNULL(CONVERT(CHAR(20),Modify_date_1),'NULL'), '', td = ISNULL(CONVERT(CHAR(20),Modify_date_2),'NULL'), '', td = ISNULL(Visi_version_1,'NULL'), '', td = ISNULL(Visi_version_2,'NULL'), '', td = Control_time , '', td = Control_user FROM VisiSystem.Control.Objects WHERE server_1 = '$server1' AND server_2 = '$server2' AND database_1 = '$database1' AND database_2 = '$database2' AND [Type] = '$objType' ORDER BY Id ASC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; --SELECT @tableHTML EXEC msdb.dbo.sp_send_dbmail @recipients='$mailRecipients', @subject = 'Procedures Version control report $server1 - $server2 ', @body = @tableHTML, @body_format = 'HTML' " Write-Verbose "Send email report" Invoke-Sqlcmd -ServerInstance $Server1 -Query $qryMail Write-Verbose 'ok' } } function Compare-ProcedureCode { <# .SYNOPSIS Compare Stored Procedeure code between 2 servers Requires BeyondCompare installed in "C:\Program Files\Beyond Compare 4" .Description .PARAMETER SQlInstance1 Server #1 .PARAMETER SQLInstance2 Server #2 .PARAMETER Database1 Database on server 1 - default Arizona .PARAMETER Database2 Database on server 2 - default Arizona .PARAMETER ProcedureName Procedure to compare .EXAMPLE # Compare-ProcedureCode -SQLInstance1 srv-onecm -SQLInstance2 srv-oneweb -ProcedureName p_GetNextID .EXAMPLE # $params = @{ SQLInstance1 = 'SRV-ONECM' SQLInstance2 = 'SRV-ONEWEB' Database1 = 'VisiSystem' Database2 = 'VisiSystem' ProcedureName = 'p_visi_purge_agent_history' } Compare-ProcedureCode @params #> [CmdletBinding(SupportsShouldProcess)] Param ( [string]$SQLInstance1 , [string]$SQLInstance2 , [string]$Database1 = 'Arizona' , [string]$Database2 = 'Arizona' , [string]$ProcedureName ) Begin{ If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { # split proce name - extract schema if ($ProcedureName -match '\.' ) { $schema = ($ProcedureName -split '\.')[0] $object = ($ProcedureName -split '\.')[1] } else { $schema = 'dbo' $object = $ProcedureName } Write-Debug "Schema = $schema Object = $object" #$guid1 = [guid]::NewGuid().ToString() #($file1 = Join-Path -Path $env:TEMP -ChildPath $($guid1 + '.sql')) $file1 = Join-Path -Path $env:TEMP -ChildPath $($ProcedureName + '-'+$SQLInstance1 + '.sql') Write-Debug $file1 #$guid2 = [guid]::NewGuid().ToString() #($file2 = Join-Path -Path $env:TEMP -ChildPath $($guid2 + '.sql')) $file2 = Join-Path -Path $env:TEMP -ChildPath $($ProcedureName + '-'+$SQLInstance2 + '.sql') Write-Debug $file2 #New-Item $file2 -ItemType File $srv1 = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $SQLInstance1 -ea Stop $srv1.Databases[$database1].StoredProcedures[$Object,$schema] | Export-DbaScript -FilePath $file1 -NoPrefix | Out-Null Write-Debug 'srv1 ok' $srv2 = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $SQLInstance2 -ea Stop $srv2.Databases[$database2].StoredProcedures[$object,$schema] | Export-DbaScript -FilePath $file2 -NoPrefix |Out-Null Write-Debug 'srv2 ok ' if ($PSCmdlet.ShouldProcess($ProcedureName,"Comparing on $sqlinstance1, $sqlInstance2")) { set-location "C:\Program Files\Beyond Compare 4" start-process bcompare -ArgumentList "$file1 $file2" } } } function Get-Arc8 { $archived = @('SRV-ACHATS','VI015-S1','VI016-S1','VI017-S1','VI019-S1','VI020-S1','VI021-S1','VI022-S1','VI024-S1','VI025-S1','VI026-S1','VI027-S1','VI028-S1','VI031-S1','VI032-S1','VI035-S1','VI038-S1','VI039-S1','VI040-S1','VI040-S1','VI042-S1','VI042-S1','VI046-S1','VI047-S1','VI047-S1','VI049-S1','VI049-S1','VI050-S1','VI050-S1','VI060-S1','VI512-S1','VI519-S1','VI581-S1','VI583-S1') (Get-VisiServers prod | Where-Object server_name -notin $archived | Where-Object server_name -notmatch 'SRV|PV').server_name } function Export-UDDT { [CmdletBinding(SupportsShouldProcess)] Param ( [parameter(Mandatory=$true,position=0,ValueFromPipeline=$true)] [ValidateNotNullOrEmpty()] [string]$Destination , [parameter(Mandatory=$false,position=1)] [ValidateNotNullOrEmpty()] [string]$Source = 'SRV-ONECM' , [parameter(Mandatory=$false,position=2)] [switch]$ExcludeCash ) Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } $srvx = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Source $so = New-DbaScriptingOption $so.ScriptSchema = $true $so.IncludeIfNotExists = $true } Process{ Write-Verbose "Destination: $destination" # process Arizona $srvx.Databases['Arizona'].UserDefinedDataTypes | Export-DbaScript -ScriptingOptionsObject $so -Passthru | ForEach-Object { Write-Debug $_ foreach ($tgt_dbn in @('model','tempdb')) { Invoke-Sqlcmd -ServerInstance $Destination -Database $tgt_dbn -Query $_ } } # process Cash if(-not($ExcludeCash.isPresent)){ $srvx.Databases['ArizonaCASH'].UserDefinedDataTypes | Export-DbaScript -ScriptingOptionsObject $so -Passthru | ForEach-Object { Write-Debug $_ foreach ($tgt_dbn in @('model','tempdb')) { Invoke-Sqlcmd -ServerInstance $Destination -Database $tgt_dbn -Query $_ } } } } } function New-CloneIndex{ <# .SYNOPSIS Clone indexes to another server .Description .PARAMETER .EXAMPLE #> [CmdletBinding(SupportsShouldProcess)] Param ( [parameter(Mandatory=$true,position=0)] [ValidateNotNullOrEmpty()] [string]$Source , [parameter(Mandatory=$true,position=1,ValueFromPipeline=$true)] [ValidateNotNullOrEmpty()] [string]$Destination , [parameter(Mandatory=$true,position=2)] [string]$SourceDatabase , [parameter(Mandatory=$true,position=3)] [string]$DestinationDatabase , [parameter(Mandatory=$true,position=4)] [string]$Table , [parameter(Mandatory=$false,position=5)] [switch]$SaveOldIndex ) Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } # split table name - extract schema if ($table -match '\.' ) { $schema = ($table -split '\.')[0] $object = ($table -split '\.')[1] } else { $schema = 'dbo' $object = $table } Write-Debug "Schema = $schema Object = $object" # script indexes to apply # NB having this code here willa void the indexes being scripted again for every destination in the pipe $src_srvx = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Source $guid = [guid]::NewGuid().ToString() $gPath = Join-Path -Path $env:TEMP -ChildPath $guid New-Item $gPath -ItemType Directory -Force | Out-Null $optScript = New-DbaScriptingOption $optScript.IncludeIfNotExists = $true $src_srvx.databases[$Sourcedatabase].tables[$object,$schema].Indexes | Where-Object IndexKeyType -notin (1,2) | Export-DbaScript -ScriptingOptionsObject $optScript -FilePath $(Join-DbaPath $gPath $('new index - '+$table +'.sql')) | Out-Null if ($debugPreference -eq "Continue" ) { invoke-item $gPath } } Process { $tgt_srvx = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Destination # save target indexes before ovwerwrite if ($SaveOldIndex.IsPresent) { $save_file = Join-DbaPath -Path $env:LOCALAPPDATA 'log' $($($Destination -replace '\\', '-' )+'-'+$DestinationDatabase+'-'+$schema+'.'+$object +'-indexesBeforeOverwrite.log' ) Write-Verbose "Old indexes save file = $save_file" $orig_indexes = $tgt_srvx.Databases[$DestinationDatabase].Tables[$object,$schema].Indexes | Where-Object IndexKeyType -notin (1,2) $orig_indexes | Export-DbaScript -FilePath $save_file -debug:$false | Out-Null if ($debugPreference -eq "Continue") { Invoke-Item $save_file } } # drop target indexes async #$orig_indexes | ForEach-Object { $_.Drop() } $optDrop = New-DbaScriptingOption $optDrop.IncludeIfNotExists = $true $optDrop.ScriptDrops = $true $gPathDrop = Join-Path -Path $env:TEMP -ChildPath $([guid]::NewGuid().ToString()) New-Item $gPathDrop -ItemType Directory -Force | Out-Null Write-Verbose "Idx drop path = $gpathdrop" $tgt_srvx.databases[$DestinationDatabase].tables[$object,$schema].Indexes | Where-Object IndexKeyType -notin (1,2) | Export-DbaScript -ScriptingOptionsObject $optDrop -FilePath $(Join-DbaPath $gPathDrop $('drop old index - ' + $table +'.sql')) | out-null if ($debugPreference -eq "Continue" ) { invoke-item $gPathDrop } New-AsyncIndexDeploy -SQLInstance $Destination -Database $DestinationDatabase -path $gPathDrop # apply indexes to destination asynchroneously Write-Verbose "---`n`nDestination = $destination" New-AsyncIndexDeploy -SQLInstance $Destination -Database $DestinationDatabase -path $gPath } } function New-RefactoryIndex { <# .SYNOPSIS Renames indexes on table according to Visi DB index naming .Description .PARAMETER .EXAMPLE #> [CmdletBinding(SupportsShouldProcess)] Param ( [parameter(Mandatory=$true,position=0)] [ValidateNotNullOrEmpty()] [string]$SQLInstance , [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrEmpty()] [string]$Database , [parameter(Mandatory=$false,position=3,ValueFromPipeline=$true)] [string]$table ) Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { Write-Box $table # extract schema from table name if ($table -match '\.' ) { $schema = ($table -split '\.')[0] $tableName = ($table -split '\.')[1] } else { $schema = 'dbo' $tableName = $table } write-debug "Table = $table" write-debug "Schema = $schema" write-debug "TableName = $tableName" # loop thru indexes and build string $str = '' $SMOsrv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $SQLInstance $SMOsrv.Databases[$Database].Tables[$tableName,$schema].Indexes | Where-Object { $_.IndexKeyType -notin (1,2) } | ForEach-Object { $idx_cols = $($_.IndexedColumns | Where-Object { -not $_.IsIncluded } ) -join ',' $include_cols = $($_.IndexedColumns | Where-Object { $_.IsIncluded } ) -join ',' $str += $("EXEC dbo.sp_visi_index `n`t@table = '$table', `n`t@index_cols = '$idx_cols', `n`t@unique = $([int]$_.IsUnique) " ) if ($include_cols.Length -gt 0 ) { $str += $(", `n`t@include_cols = '" + $include_cols + "'") } $str += "`n" } # proceed to drop/create indexes if ($PSCmdlet.ShouldProcess($table)) { Measure-Command { "`n" $str | Out-Default invoke-sqlcmd -ServerInstance $SQLInstance -Database $Database -Query $str -QueryTimeout 1000 | Out-Default } | Select-Object @{n="Total time elapsed";e={$_.Hours,"h" , $_.Minutes,"m",$_.Seconds,"s"-join " "}} } else { #whatIf true , print script and exit Write-Output $str } } } Function Update-StatsParallel { [CmdLetbinding()] Param ( [parameter(Mandatory=$true,position=0)] [ValidateNotNullOrEmpty()] [string] $SQLInstance , [parameter(Mandatory=$true,position=1)] [string] $Database , [parameter(Mandatory=$true,position=2,ValueFromPipeline=$true)] [string] $Table , [parameter(Mandatory=$true,position=3)] [switch]$WaitForCompletion ) # end param Process { $ErrorActionPreference = 'Stop' # check trace Flag 7471 stop if not set $qryCheckTraceFlag = " IF OBJECT_ID('tempdb.dbo.#t7471') IS NOT NULL DROP TABLE #t7471; CREATE TABLE #t7471([traceFlag] SYSNAME, [status] INT, [global] INT, [session] INT ) ; INSERT #t7471 EXEC sys.sp_executeSQL N'DBCC TRACESTATUS (7471) WITH NO_INFOMSGS ' IF NOT EXISTS ( SELECT * FROM #t7471 WHERE traceFlag = 7471 AND STATUS = 1 ) BEGIN RAISERROR('TraceFlag 7471 not set. Execution stopped', 16, -1) ; RETURN ; END " Write-Verbose $qryCheckTraceFlag Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qryCheckTraceFlag # log start $qryLog = " DECLARE @logId UNIQUEIDENTIFIER; EXEC dbo.sp_Save_VisiSys_Log @Id = @logId OUTPUT ,@Domain = 'UPDATE STATISTICS',@Module = '$table', @note = '$Database', @message = 'parallel XXL'; SELECT @logId AS [logid]; " $logId = (Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qryLog ).logId # get stats and create jobs $qryStats = " DECLARE @id BIGINT = OBJECT_ID('$table','U') SELECT s.name AS statName FROM sys.[stats] AS s WHERE s.[object_id] = @id " $stats = (Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $Database -Query $qryStats -Verbose -ErrorAction Stop ).statName foreach ($stat in $stats) { $command = " UPDATE STATISTICS $table [$stat] WITH FULLSCAN,MAXDOP=0; EXEC dbo.sp_Save_VisiSys_Log @Id = ''$logId''; " $desc = "Update statistics for table $table ; Generated by PS Script ; Contact: MNI" $job_name = "## Update STATS $table $stat " $outputFile = '$(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).log' $qry = " DECLARE @job_name SYSNAME = '$job_name' IF EXISTS ( SELECT 1 FROM msdb.dbo.sysjobs AS j WHERE j.name = @job_name) EXEC msdb.dbo.sp_delete_job @job_name=@job_name, @delete_unused_schedule=1; EXEC msdb.dbo.sp_add_job @job_name=@job_name, @enabled=1, @notify_level_eventlog=0, @notify_level_email=2, --2=on fail 3=always @notify_level_netsend=0, @notify_level_page=0, @delete_level=1, -- 0=never, 1=after run @description='$desc', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @notify_email_operator_name=N'DBA'; EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name, @step_name=N'step_1', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'$command', @database_name=N'$Database', --@output_file_name=N'$outputFile' , @flags=0; EXEC msdb.dbo.sp_update_job @job_name = @job_name, @start_step_id = 1; EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name, @server_name = N'(local)'; " ## create job $job_name Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qry -DisableVariables ## start job $start_job = "exec msdb.dbo.sp_start_job @job_name = '$job_name'" ; Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $start_job } # wait for completion if ( $WaitForCompletion.IsPresent) { while ($(Get-UpdateStatsRunning -SQLInstance $SQLInstance -table $table -database $database ) -gt 0 ) { Start-Sleep -Seconds 5 } } } #end process } #end function Function Get-UpdateStatsRunning { [CmdLetbinding()] Param ( [string]$SQLInstance , [string]$database , [string]$table ) Process{ $qry = " SELECT COUNT(*) as stats_running FROM sys.dm_exec_requests r OUTER APPLY sys.dm_exec_sql_text(sql_handle) t WHERE session_id != @@SPID -- don't show this query AND session_id > 50 -- don't show system queries AND r.command LIKE 'UPDATE STATISTICS' AND t.[text] LIKE '%$table%' " (Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $database -Query $qry ).stats_running } } function Get-AgentJobHistoryTimeline { <# .SYNOPSIS Get Agent job history using dbaTools .Description .PARAMETER SQLInstance .PARAMETER StartDate .PARAMETER EndDate .EXAMPLE #> [CmdLetbinding()] Param ( [string]$SQLInstance , [string]$StartDate = (Get-Date).Adddays(-1).toString('yyyy-MM-dd') + ' 20:00' , [string]$EndDate = (Get-Date).toString('yyyy-MM-dd') + ' 09:00' ) Process{ ($file = 'd:\temp\sqlAgentHistory.html' ) get-DbaAgentJobHistory -SqlInstance $SQLInstance -StartDate $startDate -EndDate $endDate -ExcludeJobSteps | ConvertTo-DbaTimeline | Out-File $file -Encoding ASCII Invoke-Item $file } } function New-AsyncIndexDeploy { <# .SYNOPSIS Builds all indexes in UpgradeBD asycnhroneously .Description Builds Async index from folder Creates one self delete Agent job for each file in parameter folder Path MUST contain a deploy.json file with dbn and job notification info .PARAMETER Path Where to find files .PARAMETER SQLInstance .EXAMPLE New-AsyncIndex -Path 'D:\sources\mni\Projet One\One\Release 10.1\SQL\OneCash\800.UpgradeBD\300.Indexes' -srv ONECMR #> [CmdLetbinding()] Param ( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)] [ValidateNotNullOrEmpty()] [string]$SQLInstance , [parameter(Mandatory=$false,position=1,ValueFromPipeline=$true)] [ValidateNotNullOrEmpty()] [string]$Database , [parameter(Mandatory=$true,position=2)] [ValidateScript({test-path $_ })] [string]$path , [parameter(Mandatory=$false,position=1)] [switch]$KeepJob ) Begin{ if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process{ # get files and create job $operator = 'DBA' Get-ChildItem $path -Recurse -file -filter *.sql | ForEach-Object { Write-debug "Table Name = $($_.BaseName) " $jobName = "## - $SQLInstance - Async Index - ## - $($_.BaseName)" Write-Verbose "Job Name = $jobName" $command = $(Get-Content $_.FullName) -replace "'" , "''" $outputFile = '$(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).log' if ($KeepJob.IsPresent){ $deleteLevel = 0 } else { $deleteLevel = 1 } $qry = " DECLARE @job_name SYSNAME = '$jobName' ; IF EXISTS ( SELECT 1 FROM sysjobs WHERE [name] = '$jobName') EXEC dbo.sp_delete_job @job_name = '$jobName' ; EXEC dbo.sp_add_job @job_name = @job_name, @enabled = 1, @description = null, @start_step_id = 1, @category_name = null, @category_id = null, @owner_login_name = 'sa', @notify_level_eventlog = 0, @notify_level_email = 2, @notify_level_netsend = 0, @notify_level_page = 0, @notify_email_operator_name = '$($operator)', @delete_level = $deleteLevel; EXEC dbo.sp_add_jobserver @job_name = @job_name; EXEC sp_add_jobstep @job_name = @job_name, @step_name = N'step_1', @subsystem = N'TSQL', @retry_attempts = 3, @retry_interval = 3, @database_name = '$($database)' , @command = N'$command' , @output_file_name=N'$outputFile' ; --start job EXEC msdb.dbo.sp_start_job @job_name = '$jobName'" Write-Debug $qry Invoke-Sqlcmd -ServerInstance $SQLInstance -Database msdb -Query $qry -DisableVariables } } } function Set-NewGUID_ApsVersionManager{ <# .Synopsis Generate a guid and update the file "APSVersionManagerServer.xml" for applications OneOptic or OneCash .Description Generate a guid and update the file "APSVersionManagerServer.xml" for applications OneOptic or OneCash. .Parameter server targeted server .Parameter Appli Application name (OneOptic or OneCash only) Select the GUID to modify .Example Set-NewGUID_ApsVersionManager -server Vi099-S1 -Appli OneOptic .output file's name \\$server\c$\inetpub\APSVersionManager\APSVersionManagerServer.xml #> [CmdLetbinding()] param ([String]$server, [Validateset("OneOptic", "ArizonaCash")]$Appli) $XML_path = "\\$server\c$\inetpub\APSVersionManager\APSVersionManagerServer.xml" [XML]$XML = Get-Content -Path $XML_path $1= ($xml.APSVersionManagerServerSettings.RegisteredApplication | Where-Object {$_.ApplicationName -eq $Appli} | Select-Object version) $guid = [guid]::NewGuid() $Newguid = "{"+$guid+"}" ### modification du APSVersionManagerServer.xml avec le nouveau GUID ### $XML_File = get-content -Path $XML_path $XML_File = $XML_File -replace $1.Version , $Newguid $XML_File |Out-File -FilePath $XML_path -Encoding utf8 } function Get-VisiServersProd {(Get-VisiServers -scope PROD -IncludeCentrale).server_name } Set-Alias -Name gvsp -Value Get-VisiServersProd function Get-VisiMagsProd {(Get-VisiServers -scope PROD -ExcludeAchats).server_name} Set-Alias -Name gvmp -Value Get-VisiMagsProd function Get-VisiServersRec {(Get-VisiServers -scope REC -IncludeCentrale).server_name} Set-Alias gvsr Get-VisiServersRec function Get-VisiMagsRec {(Get-VisiServers -scope REC -ExcludeAchats).server_name} Set-Alias -Name gvmr -Value Get-VisiMagsRec function Get-VisiServersDev1 {(Get-VisiServers -scope DEV1 -IncludeCentrale).server_name} Set-Alias gvsd1 Get-VisiServersDev1 function Get-VisiMagsDev1 {(Get-VisiServers -scope Dev1 -ExcludeAchats).server_name} Set-Alias -Name gvmd1 -Value Get-VisiMagsDev1 function Get-VisiServersDev2 {(Get-VisiServers -scope DEV2 -IncludeCentrale).server_name} Set-Alias gvsd2 Get-VisiServersDev2 function Get-VisiMagsDev2 {(Get-VisiServers -scope Dev2 -ExcludeAchats).server_name} Set-Alias -Name gvmd2 -Value Get-VisiMagsDev2 function Get-VisiCaissesProd {(Get-VisiCaisses -scope PROD).crpos_name} Set-Alias -Name gvcp -Value Get-VisiCaissesProd function Get-VisiCaissesRec {(Get-VisiCaisses -scope REC).crpos_name} Set-Alias -Name gvcr -Value Get-VisiCaissesRec function Get-VisiCaissesD1 {(Get-VisiCaisses -scope DEV1).crpos_name} Set-Alias -Name gvcd1 -Value Get-VisiCaissesD1 function Get-VisiCaissesD2 {(Get-VisiCaisses -scope DEV2).crpos_name} Set-Alias -Name gvcd2 -Value Get-VisiCaissesD2 Function Get-VisiServers { <# .SYNOPSIS Returns Visilab Servers Dev, Rec or Prod .Description Returns Visilab Servers .PARAMETER Scope .PARAMETER IncludeCentrale .PARAMETER ExcludeAchats .EXAMPLE Get-VisiServers -Scope REC #> [CmdLetbinding()] Param ( [parameter(Mandatory=$false,position=0)] [ValidateSet('Dev1','Dev2', 'Rec', 'Prod', ignorecase=$True)] [string] $scope = 'DEV1', [parameter(Mandatory=$false,position=1)] [switch] $IncludeCentrale , [parameter(Mandatory=$false,position=2)] [switch] $ExcludeAchats #[parameter(Mandatory=$false,position=3)] # [switch] $IncludeCaisses , #[parameter(Mandatory=$false,position=3)] # [switch] $Simple = $false , #[parameter(Mandatory=$false,position=4)] # [switch] $ExcludePreviousRelease = $false ) #end param Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process{ switch ($scope) { 'DEV1' { $srv = 'DEV1OCM'} 'DEV2' { $srv = 'DEV2OCM'} 'REC' { $srv = 'ONECMR'} 'PROD' { $srv = 'ONECM'} } $qry = "select * from Arizona.dbo.v_servers where 1=1 " if (-not($IncludeCentrale.IsPresent)) { $qry += " AND [server_name] NOT LIKE '%ONECM%' AND [server_name] NOT LIKE '%DEV[1-3]OCM%'" } if ($ExcludeAchats.IsPresent){ if ($scope -match 'DEV'){ $xaPattern = '%DEV[1-9]OA%' } else { $xaPattern = '%ACHATS%' } $qry += " AND [server_name] NOT LIKE '$xaPattern'" } #run query Write-Debug $qry Invoke-Sqlcmd -ServerInstance $srv -database Arizona -Query $qry | Sort-Object server_name } # end process } Function Add-CMAGroup { ############################################################# # # Function Add_to_CMA_Group # -------------------------------------------------------------------- # # --------------------------------------------------------------------- # History: # 2018-02-19 MNI Creation # ############################################################# [cmdletbinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 3 })] [string] $CMAsrv , [parameter(Mandatory=$true,position=1)] #[ValidateNotNull] [string] $group , [parameter(Mandatory=$true,position=2)] #[ValidateNotNull] [string] $parentGroup , [parameter(Mandatory=$true,position=3)] #[ValidateNotNull] $hostList ) # end param Process { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } Write-Debug $("Group = {0} | ParentGroup = {1} " -f $group, $parentGroup) $dbn = 'MSDB' ## resolve groupId $qry = " DECLARE @group SYSNAME = '$group' ; DECLARE @parentGroup SYSNAME = '$parentGroup'; DECLARE @groupId INT = -1; SELECT @groupId = g.server_group_id FROM dbo.sysmanagement_shared_server_groups AS g JOIN dbo.sysmanagement_shared_server_groups AS gp ON g.parent_id = gp.server_group_id WHERE g.name = @group AND gp.name = @parentGroup; IF @groupId = -1 BEGIN RAISERROR('Group [%s] not found ' , 16, -1, @group) ; RETURN ; END SELECT @groupId AS groupId; " $groupId = (invoke-sqlcmd -ServerInstance $CMAsrv -Database $dbn -Query $qry -ea Stop ).groupId Write-Debug $("GroupId = {0} " -f $groupId ) ##proceed to inserting rows foreach ($host in $($hostList | sort-object) ) { $qry = "INSERT INTO dbo.sysmanagement_shared_registered_servers_internal (server_group_id,name,server_name,[description],server_type) VALUES($groupId,'$host','$host','$host',0)" Write-Debug $qry invoke-sqlcmd -ServerInstance:$CMAsrv -Database:$dbn -Query:$qry -Verbose -ea Stop } } #end Process } #end function function Disable-LogShipping { ############################################################# # # Function Disable-LogShipping # -------------------------------------------------------------------- # # --------------------------------------------------------------------- # History: # 2017-12-22 MNI Creation # 2019-04-18 MNI Nouvelle version avec bds Arizona et Archive # ############################################################# [CmdletBinding()] Param( [parameter(Mandatory=$false,position=0)] [ValidateSet('Az','Arc', 'Both', ignorecase=$True)] [string] $scope = 'Both' ) Begin { #If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { # $DebugPreference = "Continue" } #else { # $DebugPreference = "SilentlyContinue" } } Process { $srv = 'SRV-ONEMIRROR' $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $srv -ErrorAction Stop switch ($scope) { 'Az' { $jobs = $SMOserver.JobServer.Jobs | Where-Object {$_.Name -match '^LSRestore.*Arizona$'} } 'Arc' { $jobs = $SMOserver.JobServer.Jobs | Where-Object {$_.Name -match '^LSRestore.*Archive$'} } 'Both' { $jobs = $SMOserver.JobServer.Jobs | Where-Object {$_.Name -match '^LSRestore'} } } #Write-Debug $jobs | select Name $jobs | ForEach-Object { Box $_.Name "JobName = [{0}] | Status before = [{1}] " -f $_.Name , $_.IsEnabled "Altering job status, please wait..." $_.IsEnabled = $false $_.Alter() "JobName = [{0}] | Status after = [{1}] " -f $_.Name , $_.IsEnabled } } # end process } # end function Function dbBuild_basic_v2 { <# .SYNOPSIS Visilab SQL Db Build .Description Builds a given releasePath ( folder or object) against a SQL database Version 0.9.23 - parameter SkipGetLatest deprecated - default behaviour will NOT get latest from TFS, -GetLAtest parameter added to force this operation .PARAMETER ReleasePath The path or object to build .PARAMETER srv Target SQL Server .PARAMETER dbn Target Database .PARAMETER BuildAction Choose among New, UpgradeDB, UpgradeBoth or PartialBuild Default Partial Build .PARAMETER SkipGetLatest Will NOT perform a tf get if specified Deprecated - replaced by -GetLatest .PARAMETER NoTranscript Will NOT save the build details in D:\MSSQL\SQLJobsLog if present .PARAMETER DisableVisiSysLog Will NOT write to Visisys.Log if present .PARAMETER BuildInfo Will return a hash containing the build result ( used for nightly build logging ) .PARAMETER GetLatest Will perform a TFS GET -recursive on the provided releasePath .PARAMETER IncludeIndex Will include any file having INDEX in the name Index files are excluded by default .EXAMPLE Build-VisiDB -srv SRV-ONECMR bla bla bla #> [cmdletbinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({test-path $_ })] [string] $ReleasePath , [parameter(Mandatory=$true,position=1,valueFromPipeline=$true )] [ValidateNotNullOrEmpty()] [string] $srv , [parameter(Mandatory=$false,position=2)] [string] $dbn = 'Arizona' , [parameter(Mandatory=$false,position=3)] [ValidateSet('New','UpgradeBD', 'UpgradeSOFT', 'UpgradeBOTH', 'PartialBuild', ignorecase=$True)] [string] $buildAction = 'PartialBuild' , [parameter(Mandatory=$false,position=4)] [switch] $GetLatest = $false , [parameter(Mandatory=$false,position=5)] [switch] $noTranscript = $false , [parameter(Mandatory=$false,position=6)] [switch] $disableVisiSysLog = $false , [parameter(Mandatory=$false,position=7)] [hashtable]$buildInfo = @{} , [parameter(Mandatory=$false,position=6)] [switch] $IncludeIndex = $false ) # end param Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { ## attempt to stop transcription if (-not($noTranscript.IsPresent)) { try{stop-transcript|out-null} catch [System.InvalidOperationException]{} } ## clear errors $Global:Error.Clear() ## start transcription if (-not($noTranscript.IsPresent)) { $logFile = Join-Path -Path D:\MSSQL\SQLJobsLog -ChildPath "Build_$($dbn)_$(Split-Path $ReleasePath -Leaf)_$($buildAction)_$($srv).$($(Get-Date -Format s ) -ireplace ':', '-' ).log" Write-Debug $("Log file = {0}" -f $logFile ) if ($VerbosePreference -eq "Continue" ) { Start-Transcript $logFile } else { Start-Transcript $logFile | Out-Null } } try { ### get latest from TFS if ($GetLatest.IsPresent) { if ($VerbosePreference -eq "Continue" ) { tf get $ReleasePath /recursive } else { tf get $ReleasePath /recursive | Out-Null } Write-Debug "TF Get Ok " } # end if NoGet ##get last changeSet try { $changeSet = @(tf history $ReleasePath /stopafter:1 /noprompt /recursive)[2].Split(' ')[0] } catch { $changeSet = -1 } Write-Debug "Get Changeset ok, changeset = $changeSet" ### get relPath files to build Write-Debug "$buildAction build starting" if ($IncludeIndex.IsPresent) { $exclude = [char](0) # use something that won't show up in a file name } else { $exclude = 'NORUN|INDEX' } switch ($buildAction.ToUpper()) { 'NEW' { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -imatch '100\.New' -and $_.FullName -inotmatch $exclude } } 'PARTIALBUILD'{ $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -inotmatch $exclude } } 'UPGRADEBD' { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -imatch '800\.UpgradeBD' -and $_.FullName -inotmatch $exclude } } 'UPGRADESOFT' { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -imatch '801\.Upgrade' -and $_.FullName -inotmatch $exclude } } 'UPGRADEBOTH' { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {($_.FullName -imatch '801\.Upgrade' -or $_.FullName -imatch '800\.UpgradeBD' ) -and $_.FullName -inotmatch $exclude } } } Write-Debug $("Release path = {0} " -f $ReleasePath ) Write-Debug $("Files OK Count = {0} " -f $($files.Count)) ## check if there is something to build if ($files.Count -eq 0) { Write-Warning "No files to build were found" } ### log build start on target server Write-Debug 'Log build start ' try { $qry = "DECLARE @log_id UNIQUEIDENTIFIER; EXEC dbo.sp_Save_VisiSys_Log @Id=@log_id OUTPUT,@Domain='DB_Build',@Module='$dbn',@Message='$ReleasePath',@note='$buildAction',@RowCount='$changeSet';SELECT @log_id as log_id;" write-debug $qry $log_id = (Invoke-Sqlcmd -ServerInstance:$srv -Query:$qry ).log_id } # end try catch { Write-Debug "VisiSys.Log failed "; $_.Exception.Message } ## start building try { foreach ($file in $files | Sort-Object { $_.FullName -replace "\W", 'z' }) { $($srv + '.' + $dbn + " " + [char]0x21e8 + " " + $(split-path $(Split-Path $file.FullName ) -leaf) + '/' + $file.BaseName) Write-Debug $($file.DirectoryName -replace '^d\:\\sources\\\w+\\', '' ) Invoke-Sqlcmd -ServerInstance:$srv -Database:$dbn -InputFile:$($file.FullName) -QueryTimeout:10000 Write-Debug "--- Build object OK `n" } ## gather build info $buildInfo.FileCount = $($files.Count) ### log build OK $qry = "EXEC dbo.sp_Save_VisiSys_Log @Id = '$log_id', @note = '$buildAction OK', @status='0'" write-debug $qry try { Invoke-Sqlcmd -ServerInstance:$srv -Query $qry } catch { write-debug "VisiSys log failed" ; $_.Exception.Message } } #end try catch { ## gather custom error info $buildInfo.Message=$_.Exception.Message $buildinfo.File = $file.Name $buildInfo.Dir = $file.DirectoryName $buildInfo.ts = get-date #### log db build error write-debug " --- Log build NOK" $qry ="EXEC dbo.sp_Save_VisiSys_Log @Id='$log_id',@note='Build $buildAction FAILED',@status= '1',@remark='$($buildInfo.Message -replace "'", "''")',@fileName='$($file.Name)',@parent='$($file.DirectoryName)'" Write-Debug $qry try {Invoke-Sqlcmd -ServerInstance:$srv -Query $qry } catch { write-debug "Visisys log failed"; $_.Exception.Message } ##throw error throw "Build failed | $($buildInfo.file) | $($buildinfo.Message) | $($buildInfo.Dir)" } #end catch } # end outer try finally { if (-not($noTranscript.IsPresent)) { if ($VerbosePreference -eq "Continue" ) { Stop-Transcript } else { Stop-Transcript | Out-Null } } #end if } #end outer finally } #end process } #end function Function Deploy_VisiSQLCommonTools { <# .SYNOPSIS Builds Visilab SQL common tools $/Projets BDD/70 - Common Tools and Helpers .Description Builds Visilab SQL common tools $/Projets BDD/70 - Common Tools and Helpers .PARAMETER Scope .PARAMETER IncludeCentrale .PARAMETER ExcludeAchats .PARAMETER IncludeCaisses .PARAMETER ExcludePreviousRelease .EXAMPLE Get-VisiServers -Scope REC #> [cmdletbinding()] Param ( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)] [string] $srv = $env:COMPUTERNAME , [parameter(Mandatory=$false,position=1)] [ValidateScript({test-path $_})] [string] $relPath = "D:\Projets BDD\70 - Common Tools and Helpers" ) Process { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } write-debug $relPath Build-VisiDb -srv $srv -dbn Master -ReleasePath $relPath -IncludeIndex } # end process } # end function #Set-Alias -Name Build-SQLCommonTools -value Build_VisiSQLCommonTools Set-Alias -Name Deploy-SQLCommonTools -value Deploy_VisiSQLCommonTools Function New-RefreshEnv { [CmdLetbinding()] Param ( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)] [ValidateNotNullOrEmpty() ] [string] $destination ) #end param Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process{ # log and transcript ops New-Item "$env:LOCALAPPDATA\Log" -ItemType Directory -Force $log = Join-Path -Path $env:LOCALAPPDATA -ChildPath "Log\Refresh.$($destination).$(get-date -f yyyy-MM-dd).log" try{ stop-transcript|out-null } catch [System.InvalidOperationException]{} Start-Transcript -Path $log -Append #load hash table $h = @{} $listDBMag = @('VisiTemp','Arizona','ArizonaCASH','Archive','ArizonaLD','VisiAudit') $listDbCentrale = @('Arizona','ArizonaCASH','Archive','Elvira','ElviraPV','VisiTemp','OneImport','OneShare','OneShareArchive','OneCustomer') { $h.Add('DEV1OA' , @('SRV-ACHATS' , $listDBMag)) $h.Add('DEV1OCM' , @('SRV-ONECM' , $listDbCentrale)) $h.Add('DEV1OM' , @('VI022-S1' , $listDBMag)) $h.Add('DEV1OM2' , @('PV421-S1' , $listDBMag)) $h.Add('DEV1OM3' , @('VI015-S1' , $listDBMag)) $h.Add('DEV1OW' , @('SRV-ONEWEB' , $listDBMag)) } $h.Add('DEV2OA' , @('SRV-ACHATS' , $listDBMag)) $h.Add('DEV2OCM' , @('SRV-ONECM' , $listDbCentrale)) $h.Add('DEV2OM' , @('VI024-S1' , $listDBMag)) $h.Add('DEV2OM2' , @('PV421-S1' , $listDBMag)) $h.Add('DEV2OM3' , @('SRV-ATWORK' , $listDBMag)) $h.Add('DEV2OW' , @('SRV-ONEWEB' , $listDBMag)) $h.Add('SRV-ACHATSR' ,@('SRV-ACHATS' , $listDBMag)) $h.Add('SRV-ATHOMER' ,@('SRV-ATHOME' , $listDBMag)) $h.Add('SRV-ATWORKR' ,@('SRV-ATWORK' , $listDBMag)) $h.Add('SRV-MAG1' ,@('VI022-S1' , $listDBMag)) $h.Add('SRV-MAG2' ,@('VI031-S1' , $listDBMag)) $h.Add('SRV-MAG3' ,@('VI015-S1' , $listDBMag)) $h.Add('SRV-ONECMR' ,@('SRV-ONECM' , $listDbCentrale)) $h.Add('SRV-ONEWEBR' ,@('SRV-ONEWEB' , $listDBMag)) $h.Add('SRV-PV1' ,@('PV420-S1' , $listDBMag)) $h.Add('SRV-PV2' ,@('PV421-S1' , $listDBMag)) $h.Add('STRATEGIC' ,@('SRV-STRATEGIC\STRATEGICSQL' , @('Arizona','ArizonaLD'))) write-debug "destination = $destination" if ( $destination -inotin $h.keys) { throw "Destination $destination is not valid for a refresh recette procedure" return } "Destination = {0} " -f $destination "Source = {0}" -f $h.$destination[0] ## copy database 'Copy database(s)...' $sharedPath = "\\SRV-NAS\BACKUP\RefreshEnv\$destination" New-Item $sharedPath -ItemType Directory -Force "dbList = {0} " -f $($h.$destination[1] -join ',' ) $params = @{ Source = $h.$destination[0] Destination = $destination SharedPath = $sharedPath BackupRestore = $true Force = $true Database = $h.$destination[1] WithReplace = $true NoBackupCleanUp = $true NumberFiles = 1 } Measure-Command { Copy-DbaDatabase @Params | Out-Default } | Select-Object @{n="Total time elapsed";e={$_.Hours,"h" , $_.Minutes,"m",$_.Seconds,"s"-join " "}} 'ok' ## repair orphan users 'Repair orphan users...' Repair-DbaDbOrphanUser -SqlInstance $destination -Database $h.$destination[1] | Out-File $( Join-Path -Path $env:TEMP -ChildPath "RepairOrphans.$($destination).$(get-date -f yyyy-MM-dd).log") 'ok' ## post restore ops Measure-Command{ $h.$destination[1] | ForEach-Object { "Set recovery mode and Remove replication {0}" -f $_ | Out-Default ## NB Try catch this MF next time Invoke-Sqlcmd -ServerInstance $destination -Query "exec sp_killall '$_'; alter database [$_] set recovery simple ; exec sp_removeDbReplication '$_' ; " -QueryTimeout 10000 | Out-Default ## end try catch } }| Select-Object @{n="Total time elapsed";e={$_.Hours,"h" , $_.Minutes,"m",$_.Seconds,"s"-join " "}} "ok" } # end process End { Stop-Transcript } } Function New-AggregateMissSub { [CmdLetbinding()] Param ( [parameter(Mandatory=$true,position=0,ValueFromPipeline=$true)] [string] $table , [parameter(Mandatory=$true,position=1)] [string] $srv , [parameter(Mandatory=$false,position=1)] [string] $dbn = 'Arizona' ) Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { box "$table @ $srv" ## init vars Write-Debug "Init vars" [string]$centrale = Get-Publisher -srv $srv Write-Debug "Centrale = $centrale" $pk = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query "SELECT dbo.fn_Get_Table_PK('$table') AS [pk]" ).pk if([string]::IsNullOrEmpty($pk)) { throw "Pk not found for table [$table]" return 1 } Write-Debug "Pk = $pk" $file = "d:\temp\bcp_missing_x.dat" Write-Debug "File = $file" $Publication = 'Document' $sub = (Invoke-Sqlcmd -ServerInstance $srv -Query "select Arizona.dbo.fn_get_current_sub() as [sub]" ).sub Write-Debug "Subsidiary = $sub " Write-Debug "ok" ##creer la table a la centrale Write-Debug "Process aggregate table @ $centrale" $table_centrale = "tempdb.dbo.missing_$($table)_mags" $qry = "IF OBJECT_ID('$table_centrale') IS NULL CREATE TABLE $table_centrale ($pk INT, subsidiary_id INT ) " Write-Debug "Qry create table centrale : $qry " Invoke-Sqlcmd -ServerInstance $centrale -Query $qry -vb -ea Stop Write-Debug "ok" # check table write-verbose "Check table $table" Verify-Table -srv $srv -Publication $Publication -table $table -check_missing_at_sub Write-Verbose "ok" # get missing lines and copy to temp table Write-Verbose "Get missing lines and write to temp table" $qry = " IF OBJECT_ID('tempdb.dbo.missing') IS NOT NULL DROP TABLE tempdb.dbo.missing; CREATE TABLE tempdb.dbo.missing ($pk INT, subsidiary_id INT ) ; WITH missing AS ( SELECT CAST($pk AS INT) AS $pk FROM tempdb.dbo.$table EXCEPT SELECT $pk FROM Arizona.dbo.$table ) INSERT tempdb.dbo.missing SELECT * , '$sub' FROM missing; " Write-Debug "Missing lines qry: $qry " Invoke-Sqlcmd -ServerInstance $srv -Query $qry -vb -ea Stop Write-Verbose "ok" # bcp out temp table (@ mag) write-debug 'BCP OUT' Write-Verbose "BCP out temp table (@ mag) " $arglist = @( 'dbo.missing', "out $file", "-S $srv", "-d TempDB", "-T " , "-N " , "-e d:\temp\bcp_out_error.txt" ) Start-Process -FilePath bcp.exe -ArgumentList $arglist -RedirectStandardOutput d:\temp\bcp_out.log -wait -NoNewWindow Write-Verbose "ok" # bcp in @ Centrale Write-Verbose "BCP In @ centrale" $qry = "DELETE $table_centrale WHERE subsidiary_id = $sub ; " Write-Debug $qry Invoke-Sqlcmd -ServerInstance $centrale -Query $qry -vb -ea Stop $arglist = @( "$table_centrale", "in $file", "-S $centrale", #"-d TempDB", "-T " , "-N " , "-e d:\temp\bcp_error.txt" ) Start-Process -FilePath bcp.exe -ArgumentList $arglist -RedirectStandardOutput d:\temp\bcp_in.log -wait -NoNewWindow Write-Verbose "ok" ##print info note $rc = (Invoke-Sqlcmd -ServerInstance $centrale -Query "select count(*) as rc from $table_centrale where subsidiary_id = '$sub' ").rc "{4} Missing {0} rows for {1} copied into {2} (sub {3}) " -f $srv , $table , $table_centrale, $sub , $rc } } #Set-Alias -Name Aggregate-MissSub -Value Aggregate_MissSub Function Test-EmptyString{ <# .SYNOPSIS Throws error (param #2) if string (param #1) is empty .Description Throws error (param #2) if string (param #1) is empty .PARAMETER $string String to test .PARAMETER $errorMessage Message to throw if string is empty .EXAMPLE Test-EmptyString 'abc' , 'error Msg' #> [CmdLetbinding()] Param ( [string]$string , [string]$errorMessage = 'String is empty' ) Process{ #$string.gettype().FullName #$errorMessage.GetType().FullName if ([string]::IsNullOrEmpty($string)) { Throw $errorMessage return } } } Function Enable-SSISQLAdmin { <# .SYNOPSIS Enables account GPPH\SSI_SQLAdm on Rec or Prod .Description Enables account GPPH\SSI_SQLAdm on Rec or Prod .PARAMETER Scope .EXAMPLE Enable-SSISQLAdmin -Scope REC #> [CmdLetbinding()] Param ( [parameter(Mandatory=$false,position=0)] [ValidateSet('Rec', 'Prod', ignorecase=$True)] [string] $Scope = 'Rec' ) #end param Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } #$sl = @() } Process{ # prepare qry $qry = 'IF EXISTS ( SELECT * FROM sys.server_principals AS sp WHERE sp.name = ''GPPH\SSI-SQLAdm'' AND sp.is_disabled = 1 ) EXEC sys.sp_ExecuteSQL N''ALTER LOGIN [GPPH\SSI-SQLAdm] ENABLE'' ' Write-Debug $qry #get servers $servers = Get-VisiServers -Scope $scope -IncludeCentrale if ($scope -eq 'Rec' ) { $servers += 'SRV-DISTRIBR' } else { $servers += 'SRV-DISTRIB' } # proceed to enable account $servers | ForEach-Object { Write-Verbose $_ Invoke-Sqlcmd -ServerInstance $_ -Query $qry Write-Verbose 'ok' } } } Function Get-VisiCaisses { <# .SYNOPSIS Returns caisses from Visilab environments .Description Based on view Arizona.dbo.v_caisses. *** Note: System_site and CR_point_of_sale must be correctly populated for this function to return proper results .PARAMETER Scope .EXAMPLE Get-VisiCaisses DEV1 .EXAMPLE GET-VisiCaisses REC .EXAMPLE GET-VisiCaisses PROD #> [CmdLetbinding()] Param ( [parameter(Mandatory=$false,position=0)] [ValidateSet('Dev1','Dev2', 'Rec', 'Prod' , ignorecase=$True)] [string] $scope = 'DEV1' ) #end param Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process{ switch -regex ($scope) { 'DEV1' { $srv = 'DEV1OCM';break } 'DEV2' { $srv = 'DEV2OCM';break } 'REC' { $srv = 'SRV-ONECMR';break } 'PROD' { $srv = 'SRV-ONECM';break } } Write-debug "Srv: $srv" $qry = "select * from v_caisses order by CRPOS_NAME ASC " Write-Debug $qry Invoke-Sqlcmd -ServerInstance:$srv -database Arizona -Query $qry } # end process } function Write-Box { <# .SYNOPSIS Visi Write-Box function .Description Displays a text in a box .PARAMETER str .EXAMPLE Write-Box 'myText' #> [CmdLetbinding()] Param( [parameter(Mandatory=$true, position=0)] [ValidateNotNullOrEmpty()] [string] $str ) # end param Process { $ul = '┌' $lr ='┘' $vt = '│' $ho = '─' $ll='└' $ur='┐' $cr = [char]10+[char]13 $result = $($ul + $ho * $($str.Length+2)+ $ur + $cr + $vt + ' ' + $str + ' ' + $vt +$cr +$ll + $ho * $($str.Length+2) + $lr ) #return $result Write-Output $result } # end process } #end function function Get-Ou { <# .SYNOPSIS Resolve OU name or number .Description Matches input string with OU_short_name or OU_code and returns full OU info .PARAMETER str .EXAMPLE # Get-OU 512 .EXAMPLE # Get-OU Rive #> [CmdLetbinding()] Param( [parameter(Mandatory=$false, position=0)] [string] $str ## string to match ) Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"] ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { $qry = "SELECT ou.Organizational_unit_id, ou.OU_code, ou.ou_short_name, s.SUB_code, s.SUB_short_name , s.subsidiary_id FROM Arizona.dbo.Organizational_unit AS ou JOIN Arizona.dbo.Subsidiary AS s ON s.Subsidiary_ID = ou.OU_subsidiary where ou_code like '%$str%' OR ou_short_name like '%$str%' ORDER BY ou_code ASC " Write-Debug $qry Invoke-Sqlcmd -ServerInstance SRV-ONECM -Query $qry } #end process } #end function Function New-VisiSQLAudit { <# .SYNOPSIS Deploy audit objets on a table .Description Deploy audit objets on a table .PARAMETER Table Table to be audited. Pipeline parameter. .PARAMETER SQLInstance SQL Instance where the table to be audited lives Default $env:COMPUTERNAME .PARAMETER Database Database where the table to be audited can be found Default Arizona .PARAMETER AuditDatabase Target database for Audit table. This is where the Audit_[table] will be created. Default VisiAudit .PARAMETER NoClobber When false, script will drop and recreate an existing audit table Default TRUE .EXAMPLE # New-VisiSQLAudit -SQLInstance SRV-MAG2 -table dbo.Telecom .EXAMPLE # @('Country') | New-VisiSQLAudit -SQLInstance SRV-MAG3 .EXAMPLE # default Freq = 13 (Get-Publications -Publisher SRV-ONECMR).name | Update-RepliJobSchedule -Publisher SRV-ONECMR #> [CmdLetbinding()] Param ( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)] [ValidateNotNullOrEmpty() ] [string] $Table , [parameter(Mandatory=$false,position=1)] [ValidateNotNullOrEmpty() ] [string] $SQLInstance = $env:COMPUTERNAME , [parameter(Mandatory=$false,position=2)] [ValidateNotNullOrEmpty() ] [string] $Database = 'Arizona' , [parameter(Mandatory=$false,position=3)] [ValidateNotNullOrEmpty() ] [string] $AuditDatabase = 'VisiAudit' , [parameter(Mandatory=$false,position=4)] [boolean] $NoClobber = $TRUE ) # end param Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"] ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } Write-Verbose "Visi Audit Objects 2.0" $stopwatch = [system.diagnostics.stopwatch]::StartNew() } Process { $srv = $sqlInstance $tgt_dbn = $AuditDatabase $dbn = $database if ($table -match '\.' ) { $schema = ($table -split '\.')[0] $tableShortName = ($tbl -split '\.')[1] } else { $schema = 'dbo' $tableShortName = $table } $tableLongName = $( $schema +'.' + $tableShortName ) $auditTableShortName = $('Audit_' + $tableShortName) $auditTableLongName = $($schema + '.' + $auditTableShortName) $triggerShortName = $('t_IUD_Audit_' + $tableShortName) $triggerLongName = $($schema +'.' + $triggerShortName) Write-Debug $("Schema = {0} " -f $schema ) Write-Debug $("Dbn = {0}" -f $dbn ) Write-Debug $("TableShortName = {0} | TableLongName = {1} " -f $tableShortName , $tableLongName ) Write-Debug $("auditTableShortName = {0} | auditTableLongName = {1} " -f $auditTableShortName , $auditTableLongName ) Write-Debug $("triggerShortName = {0} | triggerLongName = {1} " -f $triggerShortName , $triggerLongName ) $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $srv -ErrorAction Stop #$scriptr = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver) $enc = New-Object("System.Text.UTF8Encoding") $optDrop = New-Object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions') -Property @{ ScriptDrops = $true IncludeIfNotExists = $true SchemaQualify = $true ScriptSchema = $true } $optCreate = New-Object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions') -Property @{ ScriptDrops = $false IncludeHeaders = $false IncludeIfNotExists = $true SchemaQualify = $true ScriptSchema = $true Encoding=$enc NoCollation =$true DRIAll = $false ConvertUserDefinedDataTypesToBaseType = $true NoIdentities = $true ## do not script identity properties } [string]$qry = "--audit SCript generated by new-visisqlaudit " ##################### ## xFer schemas ##################### Write-Debug "Transfer schemas" $SMOserver.Databases[$dbn].Schemas | ForEach-Object { $qry = $_.Script($optCreate) #Write-Debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $tgt_dbn -Query $qry } Write-Debug "OK " ##################### ## audit Table ##################### Write-Debug "Create audit table" $tblx = $SMOserver.Databases[$dbn].Tables[$tableShortName, $schema] #Write-Debug $tblx.Name ## drop audit table If ( $NoClobber -eq $false) { $qry += $tblx.Script($optDrop) -replace $tableShortName , $('Audit_'+$tableShortName) $qry += "`n--`n" } ## create audit table $qry += $tblx.Script($optCreate) -ireplace [regex]::Escape("[$tableShortName]") , "[$auditTableShortName]" #audit table system columns $qry += "`nIf COL_LENGTH('$auditTableLongName','Audit_Id') IS NULL ALTER TABLE $auditTableLongName ADD Audit_Id INT IDENTITY PRIMARY KEY ;" $qry += "`nIf COL_LENGTH('$auditTableLongName','Server_name') IS NULL ALTER TABLE $auditTableLongName ADD Server_name SYSNAME NOT NULL DEFAULT @@servername ;" $qry += "`nIf COL_LENGTH('$auditTableLongName','Host') IS NULL ALTER TABLE $auditTableLongName ADD Host SYSNAME NOT NULL DEFAULT HOST_NAME();" $qry += "`nIf COL_LENGTH('$auditTableLongName','Login_name') IS NULL ALTER TABLE $auditTableLongName ADD Login_name SYSNAME NOT NULL DEFAULT SUSER_SNAME();" $qry += "`nIf COL_LENGTH('$auditTableLongName','Proc_name') IS NULL ALTER TABLE $auditTableLongName ADD Proc_name NVARCHAR(4000) NULL ;" $qry += "`nIf COL_LENGTH('$auditTableLongName','Time_stamp') IS NULL ALTER TABLE $auditTableLongName ADD Time_stamp DATETIME2(7) NOT NULL DEFAULT CURRENT_TIMESTAMP ;" $qry += "`nIf COL_LENGTH('$auditTableLongName','SPID') IS NULL ALTER TABLE $auditTableLongName ADD SPID INT NOT NULL DEFAULT @@SPID;" $qry += "`nIf COL_LENGTH('$auditTableLongName','Event_type') IS NULL ALTER TABLE $auditTableLongName ADD Event_type CHAR(2) NOT NULL; " $qry += "`nIf COL_LENGTH('$auditTableLongName','Program_name') IS NULL ALTER TABLE $auditTableLongName ADD [Program_name] VARCHAR(MAX) NULL;" $qry += "`nIf COL_LENGTH('$auditTableLongName','BMC_user_profile_id') IS NULL ALTER TABLE $auditTableLongName ADD [BMC_user_profile_id] VARCHAR(100) NULL;" Write-Debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $tgt_dbn -Query $qry -vb -ea Stop ## audit synonym Write-Verbose $("Synonym = {0}" -f $auditTableLongName ) Write-Debug $("`n"+'#'*50 + "`nAudit Synonym `n" +'#'*50) $qry = " IF OBJECT_ID('$auditTableLongName', 'SN' ) IS NULL EXEC sys.sp_executeSQL N'CREATE SYNONYM $auditTableLongName FOR VisiAudit.$auditTableLongName'" write-debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -vb -ea Stop ## audit trigger Write-Verbose $("Trigger = {0}" -f $triggerLongName ) ## column list Write-Debug $("`n"+'#'*50 + "`nCreate audit trigger`n" +'#'*50) $table_col_list_qry = " SELECT STUFF( (SELECT ',' + c.name FROM sys.[columns] AS c WHERE c.[object_id] = OBJECT_ID('$tableShortName','U') AND c.system_type_id not in (34,35,99,189) FOR XML PATH(''),TYPE ).value('.','NVARCHAR(MAX)') ,1, 1 ,'') AS col_list " Write-Debug $table_col_list_qry $table_col_list = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $table_col_list_qry -ErrorAction Stop -Verbose).col_list Write-Debug "Table col list = $table_col_list " ## audit trigger $trig_qry = " IF OBJECT_ID('$triggerLongName', 'TR') IS NULL EXEC sys.sp_executeSQL N'CREATE TRIGGER $triggerLongName ON $tableLongName FOR INSERT,DELETE, UPDATE AS RETURN ' ; GO ALTER TRIGGER $triggerLongName ON $tableLongName FOR INSERT, DELETE, UPDATE AS SET NOCOUNT ON DECLARE @event_type CHAR; IF EXISTS(SELECT * FROM inserted) IF EXISTS(SELECT * FROM deleted) SELECT @event_type = 'U' ELSE SELECT @event_type = 'I' ELSE IF EXISTS(SELECT * FROM deleted) SELECT @event_type = 'D' ELSE --no rows affected - cannot determine event SELECT @event_type = '?' -- DECLARE @table_name SYSNAME ; SELECT @table_name = OBJECT_NAME(parent_id) FROM sys.triggers AS t WHERE t.[object_id] = @@procid; DECLARE @host_name SYSNAME; SELECT @host_name = des1.[host_name] FROM sys.dm_exec_sessions AS des1 WHERE des1.session_id = @@spid; DECLARE @proc NVARCHAR(4000); DECLARE @Buffer table ( EventType nvarchar(30), Parms int, EventInfo nvarchar(4000) ) ; INSERT INTO @Buffer ( EventType, Parms, EventInfo ) EXEC ('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS '); SELECT @proc = EventInfo FROM @Buffer; DECLARE @program_name SYSNAME ; SELECT @program_name = program_name from sys.dm_exec_sessions where session_id = @@spid ; DECLARE @bmc_user_profile_Id INt = NULL; " if ($dbn -eq 'Arizona') { $trig_qry += "SELECT @bmc_user_profile_id = aci.APSCI_bmc_user_profile FROM Arizona.dbo.APS_connection_info AS aci WITH (NOLOCK) WHERE aci.APSCI_SQL_spid = @@SPID ; " } $trig_qry += "BEGIN TRY INSERT INTO $auditTableLongName ( Event_type, [Host], [Proc_name] , [Program_name] , [BMC_User_profile_Id], $table_col_list ) SELECT CASE @event_type WHEN 'U' THEN 'U1' ELSE @event_type END , @host_name, @proc, @program_name, @bmc_user_profile_id, $table_col_list FROM DELETED ; INSERT INTO $auditTableLongName ( Event_type, [Host], [proc_name] , [Program_name] , [BMC_User_profile_Id], $table_col_list ) SELECT CASE @event_type WHEN 'U' THEN 'U2' ELSE @event_type END , @host_name, @proc, @program_name, @bmc_user_profile_id, $table_col_list FROM INSERTED JOIN sys.dm_exec_sessions AS des1 ON @@spid = des1.session_id ; END TRY BEGIN CATCH --replace later when visisys.log is moved out of Arizona --EXEC dbo.sp_Save_VisiSys_Log @Domain = '$auditTableLongName', @Module = '$triggerLongName', @Message = 'audit failed', @CloseImmediately = 1 ; END CATCH GO EXEC sp_setTriggerOrder @triggerName = '$triggerLongName' , @order = 'First',@stmttype = 'UPDATE'; EXEC sp_setTriggerOrder @triggerName = '$triggerLongName' , @order = 'First',@stmttype = 'INSERT'; EXEC sp_setTriggerOrder @triggerName = '$triggerLongName' , @order = 'First',@stmttype = 'DELETE'; " #"Create audit triggers for $table_name" Write-Debug $("Creating trigger: [{0}]" -f $triggerLongName ) Write-Debug $trig_qry Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $trig_qry -ErrorAction Stop } #end process End { $stopwatch.Stop() write-verbose $("Elapsed seconds = {0} " -f ($stopwatch.Elapsed).TotalSeconds) } } #end function ### ALIASES Set-Alias -Name xou -Value Get-OU Set-Alias -Name Box -value Write-Box #### export members #### WARNING only members with a DASH will be exported , the others are PRIVATE functions ## export moved to psd1 |