VisiRepli.psm1
|
Function New-Publication { ## -------------------------------------------------------------------------------- ## Create Publication v 4 ## -------------------------------------------------------------------------------- [cmdletbinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance:$_ -query:"select 1" })] [string] $Publisher , [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrempty()] [string] $Publication , [parameter(Mandatory=$false,position=2)] [switch]$start_snapshot_agent=$true , [parameter(Mandatory=$false,position=3)] [boolean]$dynamic_filters=$true ) # end param Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } $sw = [Diagnostics.Stopwatch]::StartNew() } Process { Box "Create Publication $Publication at $Publisher " # get dbn switch -regex ($Publication) { 'cash' { ($dbn = 'ArizonaCASH');break } 'VisiSys' { ($dbn = 'VisiSystem');break } default { ($dbn = 'Arizona') } } Write-debug "DBN: $dbn " # build query $qry = "EXEC sp_visi_create_Publication @Publication = '$Publication' , @drop = 0" if ( $dynamic_filters -eq $false ) { $qry = $qry + ', @dynamic_filters = ''false'' ' } Write-Debug $qry # create Publication Invoke-Sqlcmd -ServerInstance $Publisher -Database $dbn -Query $qry -QueryTimeout 100000 # start snapshot agent if ($start_snapshot_agent.IsPresent) { $qry = $("exec sp_startPublication_snapshot @Publication = '" + $Publication + "';") Write-Debug "Starting snapshot agent for $Publication on $Publisher" invoke-sqlcmd -Serverinstance $Publisher -database $dbn -Query $qry } #end if snapshot agent } End { #collect time stats $sw.Stop(); "Total Time elapsed: {0} " -f $($sw.Elapsed) } } Function Add-Subscription { <# .SYNOPSIS Adds a subscriber to a Publication .Description .PARAMETER Publication .PARAMETER Subscriber (pipeline) .PARAMETER Publisher .PARAMETER start_job default Value = $true .EXAMPLE Get-VisiCaisses DEV1 .EXAMPLE Add-Subscription -srv SRV-MAG2 -Publication Address -Publisher SRV-ONECMR .EXAMPLE Get-VisiServers DEV2 | Add-subscription -Publication Address -Publisher SRV-SQLDEV2\DEV2OCM #> [CmdletBinding()] Param( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)] [ValidateScript({invoke-sqlcmd -serverInstance:$_ -query:"select 1" })] [string] $subscriber = $env:COMPUTERNAME, [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrempty()] [string] $Publication , [parameter(Mandatory=$true,position=2)] [ValidateScript({invoke-sqlcmd -serverInstance:$_ -query:"select 1" })] [string] $Publisher , [parameter(Mandatory=$false,position=3)] [switch]$start_job = $true ) # end param Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } Write-Debug "Subscriber = $subscriber" $freq = 17 } Process { # get dbn $dbn = (Get-Publications -Publisher $Publisher | ? name -eq $Publication).database Test-EmptyString $dbn Write-Debug "dbn = $dbn" # resolve overloaded host_name if ($Publication -match 'cash') { write-debug 'cash #1 ' $qry_cash = 'SELECT cr_organizational_unit_guid as hst_name FROM ArizonaCASH.dbo.CR_organizational_unit cou WHERE crou_locally_used =1 ; ' $hst_name = (invoke-sqlcmd -ServerInstance:$subscriber -Query:$qry_cash ).hst_name } else { switch -regex ($Publication) { '(ItemComp|ConfigComp)' { $hst_name = (invoke-sqlcmd -ServerInstance:$subscriber -Database:$dbn -Query:'select dbo.fn_get_current_company() as hst_name' ).hst_name break } '(Stock|Archive|Document|Address|Azconfig|ConfigSub|Inventory|Item)' { $hst_name = [math]::Abs((invoke-sqlcmd -ServerInstance:$subscriber -Database:$dbn -Query:'select dbo.fn_get_current_sub() as hst_name' ).hst_name) break } '.*CASH' { write-debug 'cash' $qry_cash = 'SELECT cr_organizational_unit_guid as hst_name FROM ArizonaCASH.dbo.CR_organizational_unit cou WHERE crou_locally_used =1 ; ' $hst_name = (invoke-sqlcmd -ServerInstance:$subscriber -Query:$qry_cash ).hst_name break } '(VisiSys)' { $hst_name = $subscriber ; break } default {Throw "Publication name $Publication not valid"; exit 1; } } # end switch write-debug "host_name = $hst_name" } #check $hst_name if ([string]::IsNullOrEmpty($hst_name) ) { Throw "Host name could not be determined"; EXit 1; } box $("Host-name for [{0}] on [{1}] ([{2}]) : {3} " -f $Publication, $subscriber, $Publisher , $hst_name ) #build query $qry_add_sub = " exec sp_addmergesubscription @Publication = '$Publication' , @subscriber = '$subscriber' , @subscriber_db = '$dbn' , @subscription_type = N'Push' , @sync_type = N'None' , @subscriber_type = N'local' , @subscription_priority = 75 , @description = null , @use_interactive_resolver = N'False' , @hostname = '$hst_name' ; exec sp_addmergepushsubscription_agent @Publication = '$Publication' , @subscriber = '$subscriber' , @subscriber_db = '$dbn' , @job_login = null , @job_password = null , @subscriber_security_mode = 1 , @Publisher_security_mode = 1 , @frequency_type = 4 , @frequency_interval = 1 , @frequency_relative_interval = 1 , @frequency_recurrence_factor = 1 , @frequency_subday = 4 , @frequency_subday_interval = " + $freq +" , @active_start_time_of_day = 100 , @active_end_time_of_day = 215959 , @active_start_date = 20121219 , @active_end_date = 99991231 , @enabled_for_syncmgr = N'False' ; " Write-Debug $qry_add_sub #exec qry invoke-sqlcmd -Serverinstance $Publisher -Database $dbn -Query $qry_add_sub -QueryTimeout 1500 -Ea Stop -ConnectionTimeout 15 if ($start_job.IsPresent) { $qry_job = " SELECT ma.name FROM MSmerge_agents AS ma WHERE Publication = '$Publication' AND subscriber_name = '$subscriber'; " $qry_distributor = " SELECT data_source FROM sys.servers WHERE NAME = 'repl_distributor'; " $distributor = (Invoke-Sqlcmd -ServerInstance $Publisher -Query $qry_distributor -ea Stop ).data_source Write-Debug $("Distributor = {0} " -f $distributor ) $job_name = (invoke-sqlcmd -ServerInstance $distributor -Database Distribution -Query $qry_job -ea Stop ).Name Write-Debug $("Job Name = {0} " -f $job_name ) Write-Debug "Starting job $job_name @ $distributor" $qry_start_job = "exec dbo.sp_start_job '$job_name'" Invoke-Sqlcmd -ServerInstance $distributor -Database MSDB -Query $qry_start_job -ea Stop } # end if start_job } # end process } Function Remove-Publication { [cmdletbinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance:$_ -query:"select 1" })] [string] $Publisher , [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrempty()] [string] $Publication ) Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { $sw = [Diagnostics.Stopwatch]::StartNew() Write-Debug "Attempting to drop Publication [$Publication] @ [$Publisher]..." # get dbn $dbn = (Get-DbaRepPublication -PublicationType Merge -SqlInstance $publisher | ? publicationName -eq $publication ).database Test-EmptyString $dbn "Publication not found" Write-debug "DBN: $dbn " # proceed to dropping Publication $qry = "if exists (select 1 from sysmergePublications where name ='$Publication') EXEC sp_dropmergePublication @Publication='$Publication'" Write-Debug $qry Invoke-Sqlcmd -ServerInstance:$Publisher -Database:$dbn -Query: $qry -ErrorAction Stop -verbose -QueryTimeout:10000 } #end Process } Function Update-RepliJobSchedule { <# .SYNOPSIS Stagger repli job schedule .Description .PARAMETER Publication Value from Pipeline = $true .PARAMETER Freq Default = 13 .PARAMETER Publisher Default = SRV-ONECM .PARAMETER Start Timespan - first execution of the day .PARAMETER Stop Timespan - last execution of the day .EXAMPLE # Update-RepliJobSchedule -Publication Document -freq 17 .EXAMPLE # $params = @{ start = $(New-Object System.TimeSpan(3,0,0)) stop = $(New-Object System.TimeSpan(5,0,0)) Publication = 'VisiSys' freq = 60 Publisher = 'SVR-ONECM' } Update-RepliJobSchedule @params .EXAMPLE # default Freq = 13 (Get-Publications -Publisher SRV-ONECMR).name | Update-RepliJobSchedule -Publisher SRV-ONECMR #> [CmdLetbinding()] Param ( [parameter(Mandatory=$true,position=0,ValueFromPipeline=$true)] [ValidateNotNullOrEmpty() ] [string] $Publication , [parameter(Mandatory=$false,position=1)] [ValidateScript({ $_ -gt 0 })] [int] $freq = -1 #frequence in minutes , [parameter(Mandatory=$false,position=2)] [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 10 })] [string] $Publisher='SRV-ONECM' , [parameter(Mandatory=$false,position=3)] [timespan] $start = $(New-Object System.TimeSpan(5,0,0)) , [parameter(Mandatory=$false,position=4)] [timespan] $stop = $(New-Object System.TimeSpan(22,59,0)) ) Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } # end begin Process { Write-Debug "Publication : $Publication " # get dbn $dbn = (Get-Publications -Publisher $Publisher| ? Name -eq $Publication ).Database Test-EmptyString $dbn "Table not found in any database (dbn could not be determined) " Write-debug "DBN: $dbn " # get distributor $distributor = Get-Distributor -Publisher $Publisher Test-EmptyString $distributor "Distributor not found" Write-Debug $("Distributor = {0}" -f $distributor ) # get job names $qry = "SELECT s.application_name as job_name FROM dbo.sysmergesubscriptions AS s JOIN dbo.sysmergePublications AS p ON p.pubid = s.pubid WHERE p.name = '$Publication' AND s.subscriber_server <> @@servername " #Write-Debug $("Qry = {0}" -f $qry ) $jobNames = (Invoke-Sqlcmd -ServerInstance $Publisher -Database $dbn -Query $qry -Verbose -ErrorAction Stop ).job_name # get smo jobs $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $distributor -ErrorAction Stop $jobs = $SMOserver.JobServer.Jobs| Where-Object {$_.Name -in $jobNames } Write-Debug $("Jobs found = {0}" -f $($jobs).count) $delta = New-Object System.TimeSpan (0,0,$(Get-Random -Maximum 30)) $ActiveStartTimeOfDay = $start foreach ($job in $jobs) { $ActiveStartTimeOfDay = $ActiveStartTimeOfDay.Add($delta) $sch = $job.JobSchedules[0] $sch.FrequencySubDayTypes = [Microsoft.SqlServer.Management.Smo.Agent.FrequencySubDayTypes]::Minute $sch.ActiveStartTimeOfDay = $ActiveStartTimeOfDay $sch.FrequencySubDayInterval = $freq $sch.ActiveEndTimeOfDay = $stop $sch.IsEnabled = $true $sch.ActiveStartDate = Get-Date $sch.Alter() "Job:{4}`n`tStart:{0} `tEnd:{1} `tFrequency:{2} `tFrequency unit:{3} `tDelta:{5} `n" -f $sch.ActiveStartTimeOfDay, $($sch.ActiveEndTimeOfDay),$($sch.FrequencySubDayInterval) ,$sch.FrequencySubDayTypes, $job.Name , $delta } #end foreach job } # end process } # end function Set-Alias -Name Stagger-RepliJobSchedule -Value Update-RepliJobSchedule Function Get-RepliCmd { <# .SYNOPSIS Get repli command .Description Get repli command .PARAMETER $subscriber_name_patter Enough to match a subscriber .EXAMPLE Bla bla bla #> Param ( [parameter(Mandatory=$true,position=0)] [ValidateNotNullOrEmpty() ] [string]$subscriber , [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrEmpty() ] [string] $Publication_name_pattern #, #[parameter(Mandatory=$false,position=2)] #[switch] $recette = $false , [parameter(Mandatory=$false,position=3)] [switch] $high_verbose_level = $false , [parameter(Mandatory=$false,position=4)] [switch] $high_query_timeout = $false ) # end param Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { # get Publisher ($Publisher = Get-Publisher $subscriber) Test-EmptyString $Publisher Write-Debug $("Publisher = {0}" -f $Publisher) # get distributor ($distributor = Get-Distributor $Publisher) Test-EmptyString $distributor Write-Debug $("Distributor = {0}" -f $distributor) # get Publication #($Publication = Get-Publications -Publisher $Publisher | Where-Object { $_.name -imatch $Publication_name_pattern } ) ($Publication = Get-DbaRepPublication -SqlInstance $Publisher | ? PublicationName -imatch $Publication_name_pattern) Test-EmptyString $($Publication.PublicationName) Write-Debug $("Publication = {0}" -f $Publication.PublicationName) # get dbn ($dbn = $Publication.Database) Test-EmptyString $Publication.DataBase Write-Debug $("Dbn = {0}" -f $dbn) # get job_name $qry = "SELECT s.subscriber_server, p.name, s.application_name FROM dbo.sysmergesubscriptions AS s JOIN dbo.sysmergePublications AS p ON p.pubId = s.pubId WHERE s.subscriber_server = '$subscriber' and p.name = '$($Publication.PublicationName)'" Write-Debug $qry $job_name = (Invoke-Sqlcmd -ServerInstance $Publisher -Database $dbn -Query $qry ).application_name Test-EmptyString $job_name Write-Debug $("Job name = {0}" -f $job_name) # get command line $smoSrv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $distributor Write-Debug $("SMO job count : {0}" -f $($($smoSrv.JobServer.Jobs).Count)) $job = $smoSrv.JobServer.Jobs| ? Name -eq $job_name $job_command = $job.JobSteps["Run agent."].Command Test-EmptyString $job_command Write-Debug $("job_command = {0}" -f $job_command) # process final string to be returned $str = "c:`r`ncd\replmerg`r`nreplmerg.exe $job_command " # add output and timeout options if ($high_verbose_level.isPresent) { $str += " -outputVerboseLevel 2 " } else { $str += " -outputVerboseLevel 1 " } if ($high_query_timeout -eq $true ) { $str += " -QueryTimeout 15000 " } # copy to clipboard [Windows.forms.Clipboard]::SetText($str) Write-Host `n$str `n } # end process } Set-Alias -Name grc -Value Get-RepliCmd -Force Function Get-Publications{ [CmdLetbinding()] Param ( [string]$Publisher = 'SRV-ONECM' , [string]$dbn = 'Arizona' ) Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process{ $qry = " IF OBJECT_ID('tempdb.dbo.##p') IS NOT NULL DROP TABLE ##p; CREATE TABLE ##p ([Name] SYSNAME, [Database] SYSNAME DEFAULT DB_NAME() ) EXEC sp_msforeachDB ' USE ? ; IF OBJECT_ID(''dbo.sysmergePublications'',''U'') IS NOT NULL EXEC sys.sp_executeSQL N'' INSERT ##p ([name]) SELECT NAME FROM sysmergePublications'' ' SELECT * FROM ##p ORDER BY [name] ASC , [database] ASC ; " Write-Debug $qry $Publications = invoke-sqlcmd -ServerInstance $Publisher -Query $qry -Database $dbn # return objects return $Publications } } Function Enable-RepliJobSchedule { [CmdLetbinding()] Param ( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)] [string]$Publication , [string]$Publisher = 'SRV-ONECM' ) Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process{ $Publication -split ',' | % { Write-Debug $("Publication = {0}" -f $_ ) Alter_RepliJobSchedule -Publication $_ -Publisher $Publisher -isEnable $true } } } Function Disable-RepliJobSchedule { [CmdLetbinding()] Param ( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)] [string]$Publication , [string]$Publisher = 'SRV-ONECM' ) Begin { if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process{ $Publication -split ',' | % { Write-Debug $("Publication = {0}" -f $_ ) Alter_RepliJobSchedule -Publication $_ -Publisher $Publisher -isEnable $false } } } Function Alter_RepliJobSchedule { [CmdLetBinding()] Param ( [string] $Publication , [string] $Publisher = 'SRV-ONECM', [boolean]$isEnabled ) Begin { if (-not $PSBoundParameters.ContainsKey('DebugPreference')) { $debugPreference = $PSCmdlet.GetVariableValue('DebugPreference') } } Process{ # get dbn #$dbn = InferDbnFromPublication -Publication $Publication $dbn = Get-PublicationDbn -Publisher $Publisher -Publication $Publication Write-Debug $("Dbn = {0}" -f $dbn ) #get distributor $qry = " SELECT p.distributor FROM dbo.sysmergePublications AS p join dbo.sysmergesubscriptions AS s ON s.pubid = p.pubid WHERE p.name = '$Publication' AND s.application_name IS NULL " $distributor = (invoke-sqlcmd -ServerInstance $Publisher -Database $dbn -Query $qry).distributor Write-Debug $("Distributor = {0} " -f $distributor) #get job names $qry = " SELECT s.application_name as jobName FROM dbo.sysmergePublications AS p join dbo.sysmergesubscriptions AS s ON s.pubid = p.pubid WHERE p.name = '$Publication' AND s.application_name IS NOT NULL " $jobNames = (invoke-sqlcmd -ServerInstance $Publisher -Database $dbn -Query $qry).jobName Write-Debug $("job names count = {0}" -f $jobNames.Count) # get repli jobs $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $distributor -ErrorAction Stop foreach ($job in $SMOserver.JobServer.Jobs |Where-Object {$_.Name -iin $jobNames} ) { #$job.Name foreach ($sched in $job.JobSchedules) { $sched.IsEnabled = $isEnabled $sched.Alter() Write-Verbose $("Job = {0} Schedule = {1} IsEnabled = {2} " -f $job.Name , $sched.Name , $sched.IsEnabled ) } } } } Function Get-Distributor { <# .SYNOPSIS Get distributor .Description Get distributor .PARAMETER $Publisher bla bla bla .EXAMPLE Get-Publisher 'SRV-MAG1' #> [CmdLetbinding()] Param( [parameter(Mandatory=$true,position=0,ValueFromPipeline=$True)] [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 3 })] [string] $Publisher ) # end Param Process { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } Write-Debug $("Publisher = {0} " -f $Publisher ) $qry = "SELECT data_source as ds FROM sys.servers AS s WHERE s.is_distributor = 1 " (Invoke-Sqlcmd -ServerInstance $Publisher -Query $qry ).ds } # end process } Function Get-Publisher { <# .SYNOPSIS Get Publisher .Description Get Publisher .PARAMETER srv Server for which the Publisher will be returned .EXAMPLE Get-Publisher 'SRV-MAG1' #> [CmdLetbinding()] Param( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$True)] [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 10 })] [string] $srv = $env:COMPUTERNAME ) # end Param Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { Write-Debug $("Srv = {0} " -f $srv ) $qry = " BEGIN TRY EXEC ('select @@serverName as server_name') at ArizonaCASH ; END TRY BEGIN CATCH RAISERROR ('Checking Publisher using linked server Arizona CASH failed', 16, -1) END CATCH "; Write-debug $qry; (Invoke-Sqlcmd -ServerInstance $srv -Query $qry -ErrorAction stop).server_name } # end process } Function Remove-Subscription { [cmdletbinding()] Param( [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)] [ValidateNotNullOrempty()] [string] $subscriber , [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrempty()] [string] $Publisher , [parameter(Mandatory=$true,position=2)] [ValidateNotNullOrempty()] [string] $Publication , [parameter(Mandatory=$false,position=3)] [switch]$cleanAtSubscriber = $true ) # end param Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { $sw = [Diagnostics.Stopwatch]::StartNew() Box "Remove subscription to $Publisher.$Publication on $subscriber " Write-Debug "Publisher = $Publisher" Write-Debug "Publication = $Publication" Write-Debug "Subscriber = $subscriber" # get dbn $dbn = (Get-DbaRepPublication -PublicationType Merge -SqlInstance $srv | ? publicationName -eq $publication ).database if ([string]::IsNullOrEmpty($dbn)) { Throw "Database for Publication [$Publication] could not be found or Publication does not exists " return } Write-Debug "Database for Publication [$Publication] = [$dbn] " # check if Publication exists ## a faire # proceed to dropping subscription $qry = " IF EXISTS ( SELECT p.name, * FROM sysmergesubscriptions s JOIN sysmergePublications p ON s.Pubid = p.Pubid WHERE subscriber_server = '$subscriber' AND p.name = '$Publication' AND [DB_NAME] = '$dbn' AND s.[status] <> 2 ) exec dbo.sp_dropmergesubscription @Publication = '$Publication' , @subscriber = '$subscriber' , @subscriber_db = '$dbn' , @subscription_type = 'both' " Write-Debug $qry Invoke-Sqlcmd -ServerInstance:$Publisher -Database:$dbn -Query:$qry -Verbose -QueryTimeout:4500 #-------------------------------- #cleanup subscription @ subscriber #-------------------------------- if ($cleanAtSubscriber.ispresent) { Write-Verbose "Cleaning up replication objects @ [$subscriber]" $qry= "exec sp_mergesubscription_cleanup @Publisher='$Publisher', @Publisher_db='$dbn',@Publication ='$Publication'" Write-Debug "`n$qry " Invoke-Sqlcmd -ServerInstance:$subscriber -Database:$dbn -Query:$qry -Verbose -QueryTimeout:4500 } # end if clean at subscriber else { Write-Verbose "No Cleanup @ $subscriber executed. Do not omit to run sp_mergesubscription_cleanup @ $subscriber" } # end else #collect time stats $sw.Stop(); "Total Time elapsed: {0} " -f $($sw.Elapsed) } #end Process } <# Function Remove-AllSubscriptions { [CmdLetbinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 10 })] [string] $Publisher , [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrEmpty() ] [string] $Publication ) Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process { Get-AllSubscribers -Publisher $Publisher -Publication $Publication | % { $params = @{ Publisher = $Publisher Subscriber = $_.subscriberName Publication = $Publication } Remove-Subscription @params } } } #> Function Get-AllSubscribers { [CmdLetbinding()] Param ( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" })] [string] $Publisher , [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrEmpty() ] [string] $Publication ) #end param Begin { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } } Process{ Write-Debug $("Publisher = {0} Publication = {1} " -f $Publisher, $Publication ) # get dbn $dbn = (Get-Publications -Publisher $Publisher| ? Name -eq $Publication ).Database Test-EmptyString $dbn "Table not found in any database (dbn could not be determined) " Write-debug "DBN: $dbn " # start RMO server $RMOserver = New-Object ('Microsoft.SqlServer.Replication.ReplicationServer') -argumentlist $Publisher write-debug "RMOServer = $($RMOserver.Name)" write-debug "Publications = $($RMOserver.Publications)" # get subscribers $RMOserver.ReplicationDatabases[$dbn].MergePublications[$Publication].MergeSubscriptions | Select SubscriberName } # end process } # end function Function Get-PublicationDbn { [CmdLetbinding()] Param ( [string]$Publisher = 'SRV-ONECM' , [string]$Publication = 'Arizona' ) #end param Process { (Get-Publications -Publisher $Publisher | ? name -eq $Publication ).DataBase } } Function Get_RowCount { Param ( $data_set ) Process { [int] $row_count=-1 if ($data_set -eq $null ) { $row_count = 0 } else { if ($data_set.Count -eq $null) { $row_count=1 } else { $row_count = $data_set.Count } } return $row_count } } Function Verify_Publication { ############################################################# # # Function Verify_publication # -------------------------------------------------------------------- # # --------------------------------------------------------------------- # History: # 2017-12-19 MNI Creation # ############################################################# ## -------------------------------------------------------------------------------- ## Verify publication v1 ## -------------------------------------------------------------------------------- [cmdletbinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance:$_ -query:"select 1" })] [string] $subscriber, [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrempty()] [string] $publication , [parameter(Mandatory=$false,position=2)] [switch]$push_to_sub=$false , [parameter(Mandatory=$false,position=3)] [switch]$push_to_pub=$false , [parameter(Mandatory=$false,position=4)] [switch]$hide_zero_values=$false , [parameter(Mandatory=$false,position=5)] $exclude_tables = @() , [parameter(Mandatory=$false,position=6)] [switch]$bulk_fix = $false # use bulk fix ) # end param Process { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } # get publisher $publisher = Get-Publisher -srv $subscriber Test-EmptyString $publisher "Publisher not found" Write-Debug "Publisher: $publisher" # get dbn $dbn = (Get-Publications -publisher $publisher| ? Name -eq $publication ).Database Test-EmptyString $dbn "Table not found in any database (dbn could not be determined) " Write-debug "DBN: $dbn " #validate publication # to do later $articles_qry = "exec sp_visi_Get_repli_articles @publication = '$publication' " $articles = Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $articles_qry foreach ($article in $articles | Where-Object {$_.article_name -inotin $exclude_tables} ) { Write-Debug $("`nArt: {0} Bidir = {1}" -f $article.article_name , $(-not $article.upload_options)) if ($bulk_fix.IsPresent) { Compare_publisher_subscriber_bulkFix -srv:$subscriber -publication:$publication -table:$article.article_name -check_missing_at_pub:$(-not $article.upload_options) -check_missing_at_sub -hide_zero_values:$hide_zero_values -push_to_pub:$push_to_pub -push_to_sub:$push_to_sub -ErrorAction Stop } else { Compare_publisher_subscriber -srv:$subscriber -publication:$publication -table:$article.article_name -check_missing_at_pub:$(-not $article.upload_options) -check_missing_at_sub -hide_zero_values:$hide_zero_values -push_to_pub:$push_to_pub -push_to_sub:$push_to_sub -ErrorAction Stop } } # end foreach article } #end Process } Set-Alias -Name Verify-Publication -Value Verify_publication function Compare_publisher_subscriber_bulkFix { ############################################################# # # # Function Compare_publisher_subscriber bulk fix # mni 2018-03-22 - createed ############################################################# [CmdletBinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 3 })] [string] $srv , [parameter(Mandatory=$false,position=1)] [ValidateNotNullOrEmpty()] [string] $publication , [parameter(Mandatory=$true,position=2)] [ValidateNotNullOrEmpty()] [string] $table , [parameter(Mandatory=$false,position=3)] [switch] $check_missing_at_pub = $false , # check rows missing at publisher (cm) [parameter(Mandatory=$false,position=4)] [switch] $check_missing_at_sub = $false , # check rows missign at subscriber(mag) [parameter(Mandatory=$false,position=5)] [switch] $push_to_pub = $false , # push rows to publisher (mdu @ sub ) [parameter(Mandatory=$false,position=6)] [switch] $push_to_sub = $false , # push rows to subscriber (mdu @ pub ) [parameter(Mandatory=$false,position=7)] [switch] $hide_zero_values = $false ) # end param Process { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } #check switches if (-not($check_missing_at_pub.IsPresent) -and -not($check_missing_at_sub.IsPresent)) { Write-Output "No check parameters have been specified.`nExiting routine" ; Return ; } #legacy var name -- too lazy to fix $pub = $publication #infer dbn $dbn = InferDbnFromPublication -publication $publication Write-debug "DBN: $dbn " if ([string]::IsNullOrEmpty($dbn)) { throw "Table not found in any database (dbn could not be determined) " ; exit 1; } #get publisher $publisher = Get-Publisher $srv -debug:$false if ([string]::IsNullOrEmpty($publisher)) { throw "Publisher not found" ; exit 1; } Write-Debug "Publisher: $publisher" #get objid at publisher ?? Is this still necessary ? $qry = "select object_id('$table', 'U') as objid " $objid = (Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $qry ).objid Write-Debug $("Objid = {0} " -f $objid ) if ([string]::IsNullOrEmpty($objid)) { throw "Table objectId could not be determined" ; exit 1; } #get sub if ($dbn -eq 'ARIZONACASH') { #get current ou from CASH $qry = "SELECT cou.CR_organizational_unit_GUID as sub FROM CR_organizational_unit AS cou WHERE cou.CROU_locally_used = 1 ; " } else { #get current sub from AZ $qry = " declare @sub int ; select @sub = dbo.fn_get_Current_Sub() ; if @@servername like 'SRV-ACHATS%' select @sub = 1 select @sub as sub ; " } $sub = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -Verbose).sub if ([string]::IsNullOrEmpty($sub)) { "Server = {0} ; Dbn = {1} ; Qry = {2} " -f $srv , $dbn, $qry throw "Subsidiary not found" ; exit 1; } # end if sub is empty Write-Debug "HostName override: $sub" #generate repli meta data @ pub $qry = "EXEC sp_get_repli_metadata @in_publication='$pub',@in_subsidiary_id='$sub',@in_table='$table'" Write-Debug $qry $repliData = (Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $qry -ErrorAction Stop ) $pk = ($repliData | Where-Object {$_.object_id -eq $objid } ).pk $schema = ($repliData | Where-Object {$_.object_id -eq $objid } ).schema $tableLongName = ($repliData | Where-Object {$_.object_id -eq $objid } ).article_long_name $article = ($repliData | Where-Object {$_.object_id -eq $objid } ).article if([string]::IsNullOrEmpty($pk)) { Throw "Pk could not be determined for table $table" ; Exit 1; } Write-Debug "Table: $tableLongName" Write-Debug "Pk: $pk" $from = ($repliData | Where-Object {$_.object_id -eq $objid } ).from_clause if([string]::IsNullOrEmpty($from)) { Throw "From clause could not be determined for table $table" ; Exit 1; } Write-Debug "From clause: $from" # pull cm data 2 mag $qry = " ---------------------------- -- begin extract data query -- SET QUOTED_IDENTIFIER OFF; IF OBJECT_ID('tempdb.dbo.$article') IS NOT NULL DROP TABLE tempdb.dbo.$article; SELECT TOP 0 $pk INTO tempdb.dbo.$article FROM $dbn.$schema.$article; DECLARE @sql NVARCHAR(MAX) = `" SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; USE $dbn; SELECT $pk $from ;`" INSERT tempdb.dbo.$article EXEC(@sql) At ArizonaCASH; -- -- end extract data query ---------------------------- " $qry = $($qry -ireplace "INNER", "`nINNER" ) $qry = $($qry -ireplace " AND " , "`nAND " ) Write-Debug "Extract data qry=`n $qry " #proceed to execute @ mag Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -QueryTimeout 100000 -verbose:$false Write-Debug "End pull cm data to mag" #get rows missing @ pub if ($check_missing_at_pub -eq $true) { Write-Debug "Check missing @ Pub" $qry = " ------------------------ -- begin missing @ Pub query -- ;WITH missing_at_cm AS ( SELECT $pk $from EXCEPT SELECT $pk FROM tempdb.dbo.$article ) SELECT x.ROWGUIDCOL as rgd_ , x.$pk as pk_ FROM missing_at_cm AS m JOIN $dbn.$schema.$article AS x ON m.$pk = x.$pk -- -- end missing @ Pub query -------------------------- " Write-Debug $qry $rows = $(Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop ) $rc = Get_RowCount($rows) if (-not( $hide_zero_values.IsPresent -and ($rc -lt 1 ))) { "[{0}].[{1}].[{2}] rows missing at [{3}]: {4} " -f $srv, $dbn, $table, $publisher, $rc } #push2pub (mdu@mag) if ($push_to_pub.IsPresent -and $rc -gt 0 ) { #select candidate col for update to avoid oop error $qry = " SET NOCOUNT ON ; DECLARE @table SYSNAME = '$tableLongName'; DECLARE @xc TABLE ( c SYSNAME) ; INSERT @xc SELECT CONCAT('%', ja.name, '%') FROM dbo.sysmergearticles AS a JOIN dbo.sysmergesubsetfilters AS f ON f.artid = a.artid JOIN dbo.sysmergearticles AS ja ON f.join_nickname = ja.nickname WHERE a.objid = OBJECT_ID(@table,'U') ; IF NOT EXISTS ( SELECT 1 FROM @xc) INSERT @xc SELECT COL_NAME(OBJECT_ID(@table,'U'),1); SELECT TOP 1 c.name as colName FROM sys.tables AS t JOIN sys.[columns] AS c ON t.[object_id] = c.[object_id] JOIN sys.schemas AS s ON s.[schema_id] = t.[schema_id] WHERE t.name LIKE PARSENAME(@table, 1) AND c.column_id > 1 AND c.name LIKE '%%' AND s.name LIKE IsNull(PARSENAME(@table,2),'dbo') AND t.is_ms_shipped = 0 AND c.name NOT LIKE ( SELECT c FROM @xc) " write-debug $qry $xcol = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 10000 ).colName Write-Debug $( "col used for bulk update = {0}" -f $xcol ) # proceed to update @ subscriber $qry = " ------------------------------- -- bulk update query BEGIN TRAN ;WITH missing_at_cm AS ( SELECT $pk $from EXCEPT SELECT $pk FROM tempdb.dbo.$article ) UPDATE $tableLongName SET $xcol = $xcol FROM $tableLongName AS x JOIN missing_at_cm AS mac ON x.$pk = mac.$pk --ROLLBACK TRAN COMMIT TRAN ------------------------------- " write-debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 10000 } } #get rows missing @ Sub if ($check_missing_at_sub -eq $true ) { Write-Debug "Check missing @ SUB " $qry = " -- -- begin missing @ Sub query ----------------------------------------------------------- SELECT $pk as pk_ FROM tempdb.dbo.$article EXCEPT SELECT $pk from $table -- -- end missing @ Sub query -----------------------------------------------------------" Write-Debug $qry $rows = Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -QueryTimeout 10000 $rc = Get_RowCount($rows) $rows_missing_at_subscriber = $rc #save for closing statements if (-not( $hide_zero_values.IsPresent -and ($rc -lt 1 )) ) { "[{0}].[{1}].[{2}] rows missing at [{3}]: {4} " -f $publisher, $dbn, $table, $srv, $rc } #push rows 2 sub (mdu@pub) ######################### if ($push_to_sub.IsPresent -and $rc -gt 0) { $qry = " DECLARE @table SYSNAME = '$tableLongName'; SELECT TOP 1 c.name as colName FROM sys.tables AS t JOIN sys.[columns] AS c ON t.[object_id] = c.[object_id] JOIN sys.schemas AS s ON s.[schema_id] = t.[schema_id] WHERE t.name LIKE PARSENAME(@table, 1) AND c.name LIKE '%%' AND s.name LIKE IsNull(PARSENAME(@table,2),'dbo') AND t.is_ms_shipped = 0 AND c.name NOT LIKE '$pk' AND c.name not like '%subsidiar%'" #$qry $xcol = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop ).colName Write-Debug $( "col used for bulk update = {0}" -f $xcol ) <# $qry = " BEGIN TRAN SET XACT_ABORT ON; ;WITH mx AS ( SELECT $pk FROM tempdb.dbo.$article EXCEPT SELECT $pk from $tableLongName ) UPDATE ArizonaCASH.$dbn.$tableLongName SET $xcol = $xcol FROM ArizonaCASH.$dbn.$tableLongName AS a JOIN mx ON a.$pk = mx.$pk --ROLLBACK TRAN COMMIT TRAN " write-debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 15000 #> # create temp table at publisher $qry = "IF OBJECT_ID('$tableLongName') IS NOT NULL DROP TABLE $tableLongName; CREATE TABLE $tableLongName ($pk SYSNAME) " write-debug $qry Invoke-Sqlcmd -ServerInstance $publisher -Database TempDB -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 10000 # populate tenp table at publisher $qry = "INSERT ONECM.tempdb.$tableLongName SELECT $pk FROM tempdb.dbo.$article EXCEPT SELECT $pk from $tableLongName " write-debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 10000 # proceed to update at publisher $qry = " UPDATE $tableLongName SET $xcol = $xcol FROM $tableLongName AS a JOIN tempdb.$tableLongName as mx ON a.$pk = mx.$pk " write-debug $qry Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -Verbose -ErrorAction Stop -QueryTimeout 15000 } } #end if check_missing_at_mag ####################### #closing statements ####################### #$note = "Rows missing at publisher: $rows_missing_at_publisher; Rows missing at subscriber: $rows_missing_at_subscriber" #$qry = " #EXEC dbo.sp_Save_VisiSys_Log #@Id = '$log_id', #@note = '$note', #@status= '0' , #@rowCount = $($rows_missing_at_publisher + $rows_missing_at_subscriber) " #Invoke-Sqlcmd -ServerInstance $srv -Query $qry -Verbose -QueryTimeout 10000 -ErrorAction stop } # end process } # End function function Compare_publisher_subscriber { ############################################################# # # # Function Compare_publisher_subscriber # Now using sp_get_repli_metaData # mni 2017-03-20 # # mni 2018-03-22 - add publication parameter # - using infer dbn from publication function # - adapt to new pub Archive ( same table Name in 2 diff pubs) ############################################################# [CmdletBinding()] Param( [parameter(Mandatory=$true,position=0)] [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 3 })] [string] $srv , [parameter(Mandatory=$true,position=1)] [ValidateNotNullOrEmpty()] [string] $publication , [parameter(Mandatory=$true,position=2)] [ValidateNotNullOrEmpty()] [string] $table , [parameter(Mandatory=$false,position=3)] [switch] $check_missing_at_pub = $false , # check rows missing at publisher (cm) [parameter(Mandatory=$false,position=4)] [switch] $check_missing_at_sub = $false , # check rows missign at subscriber(mag) [parameter(Mandatory=$false,position=5)] [switch] $push_to_pub = $false , # push rows to publisher (mdu @ sub ) [parameter(Mandatory=$false,position=6)] [switch] $push_to_sub = $false , # push rows to subscriber (mdu @ pub ) [parameter(Mandatory=$false,position=7)] [switch] $hide_zero_values = $false ) # end param Process { If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } else { $DebugPreference = "SilentlyContinue" } Write-Debug "Compare_publisher_subscriber Version 3 " [int] $rc = -1 ####################### #check switches ####################### if (-not($check_missing_at_pub.IsPresent) -and -not($check_missing_at_sub.IsPresent)) { Write-Output "No check parameters have been specified.`nExiting routine" ; Return ; } ####################### #write log ####################### $qry = " DECLARE @log_id UNIQUEIDENTIFIER DECLARE @domain SYSNAME = 'Controle_replication' EXEC dbo.sp_Save_VisiSys_Log @Id = @log_id OUTPUT, @Domain = 'Controle_replication', @Module = '$table'; SELECT @log_id as log_id " $log_id = (Invoke-Sqlcmd -ServerInstance $srv -Database 'master' -Query $qry -Verbose -QueryTimeout 10000 -ErrorAction stop ).log_id Write-Debug "Log id = $log_id " ####################### #get publisher ####################### $publisher = Get-Publisher -srv $srv Test-EmptyString $publisher "Publisher not found" Write-Debug "Publisher: $publisher" ####################### # get dbn ####################### $dbn = (Get-Publications -publisher $publisher| ? Name -eq $publication ).Database Test-EmptyString $dbn "Table not found in any database (dbn could not be determined) " Write-debug "DBN: $dbn " ####################### #get publication ####################### $pub = $publication Write-Debug "Publication: $pub " ####################### #get objid at publisher ####################### $qry = "select object_id('$table', 'U') as objid " $objid = (Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $qry ).objid Test-EmptyString $objid "Table objectId could not be determined" Write-Debug $("Objid = {0} " -f $objid ) ####################### #get sub ####################### #sub (ou if CASH) if ($dbn -imatch 'CASH') { #get current ou from CASH $qry = "SELECT cou.CR_organizational_unit_GUID as sub FROM CR_organizational_unit AS cou WHERE cou.CROU_locally_used = 1 ; " } else { if ($pub -imatch 'Arizona_OU') { $qry = "SELECT dbo.fn_Get_CV_Value('cvCurrentOrganizationalUnit', null) as sub " } else { #get current sub from AZ $qry = " declare @sub int ; select @sub = dbo.fn_get_Current_Sub() ; if @@servername like 'SRV-ACHATS%' select @sub = 1 select @sub as sub ; " } } #Write-Debug $qry $sub = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -Verbose).sub if ([string]::IsNullOrEmpty($sub)) { "Server = {0} ; Dbn = {1} ; Qry = {2} " -f $srv , $dbn, $qry throw "Subsidiary not found" ; exit 1; } # end if sub is empty Write-Debug "HostName override: $sub" ####################### #generate repli meta data @ pub ####################### $qry = "EXEC sp_get_repli_metadata @in_publication='$pub',@in_subsidiary_id='$sub',@in_table='$table'" Write-Debug $qry $repliData = (Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $qry -ErrorAction Stop ) #write-debug $($replidata |select * ) $pk = ($repliData | Where-Object {$_.object_id -eq $objid } ).pk $schema = ($repliData | Where-Object {$_.object_id -eq $objid } ).schema $tableLongName = ($repliData | Where-Object {$_.object_id -eq $objid } ).article_long_name $article = ($repliData | Where-Object {$_.object_id -eq $objid } ).article if([string]::IsNullOrEmpty($pk)) { Throw "Pk could not be determined for table $table" ; Exit 1; } Write-Debug "Table: $tableLongName" Write-Debug "Pk: $pk" $from = ($repliData | Where-Object {$_.object_id -eq $objid } ).from_clause if([string]::IsNullOrEmpty($from)) { Throw "From clause could not be determined for table $table" ; Exit 1; } Write-Verbose "From clause: $from" ####################### # pull cm data 2 mag ####################### #prepare extract data query $qry = " ---------------------------- -- begin extract data query -- SET QUOTED_IDENTIFIER OFF; IF OBJECT_ID('tempdb.dbo.$article') IS NOT NULL DROP TABLE tempdb.dbo.$article; SELECT TOP 0 $pk INTO tempdb.dbo.$article FROM $dbn.$schema.$article; DECLARE @sql NVARCHAR(MAX) = `" SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; USE $dbn; SELECT $pk $from ;`" INSERT tempdb.dbo.$article EXEC(@sql) At ArizonaCASH; -- -- end extract data query ---------------------------- " $qry = $($qry -ireplace "INNER", "`nINNER" ) $qry = $($qry -ireplace " AND " , "`nAND " ) Write-Debug "Extract data qry=`n $qry " #proceed to execute @ mag Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -QueryTimeout 100000 -verbose:$false write-debug "End pull cm data to mag" ######################### #get rows missing @ pub ######################### if ($check_missing_at_pub -eq $true) { Write-Debug "Check missing @ Pub" $qry = " ------------------------ -- begin missing @ Pub query -- ;WITH missing_at_cm AS ( SELECT $pk $from EXCEPT SELECT $pk FROM tempdb.dbo.$article ) SELECT x.ROWGUIDCOL as rgd_ , x.$pk as pk_ FROM missing_at_cm AS m JOIN $dbn.$schema.$article AS x ON m.$pk = x.$pk -- -- end missing @ Pub query -------------------------- " Write-Debug $qry $rows = $(Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop ) $rc = Get_RowCount($rows) if (-not( $hide_zero_values.IsPresent -and ($rc -lt 1 ))) { "[{0}].[{1}].[{2}] rows missing at [{3}]: {4} " -f $srv, $dbn, $table, $publisher, $rc } $rows_missing_at_publisher = $rc #save for closing statements ######### #push2pub (mdu@mag) ######### if ( $push_to_pub -and $rc -gt 0 ) { foreach ($row in $rows) { $qry = "exec sp_mergeDummyUpdate '$table' , '$($row.rgd_)'" if ($rc -gt 1) { $rowIndex = $rows.IndexOf($row) + 1 } else { $rowIndex = 1 } "[{0}] {1}/{2} -- {3} " -f $srv, $rowIndex, $rc ,$qry ### proceed to mdu row Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop -Verbose } #end foreach row } #end if push_2_pub } #end if $check_missing_at_pub ######################### #get rows missing @ Sub ######################### if ($check_missing_at_sub -eq $true ) { Write-Debug "Start check missing @ SUB " $qry = " -- -- begin missing @ Sub query ----------------------------------------------------------- SELECT $pk as pk_ FROM tempdb.dbo.$article EXCEPT SELECT $pk from $table -- -- end missing @ Sub query -----------------------------------------------------------" Write-Debug $qry $rows = Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -ErrorAction Stop $rc = Get_RowCount($rows) $rows_missing_at_subscriber = $rc #save for closing statements if (-not( $hide_zero_values.IsPresent -and ($rc -lt 1 )) ) { "[{0}].[{1}].[{2}] rows missing at [{3}]: {4} " -f $publisher, $dbn, $table, $srv, $rc } Write-Debug "End check missing @ SUB " ######################### #push rows 2 sub (mdu@pub) ######################### if ($push_to_sub -eq $true ) { foreach ($row in $rows) { $qry = "DECLARE @rgd UNIQUEIDENTIFIER;SELECT @rgd=ROWGUIDCOL FROM $table WHERE $pk='$($row.pk_)';IF @rgd IS NOT NULL EXEC sp_mergeDummyUpdate '$table' , @rgd " if ($rc -gt 1) { $rowIndex = $rows.IndexOf($row) + 1 } else { $rowIndex = 1 } "[{0}] {1}/{2} -- {3} " -f $publisher, $rowIndex, $rc ,$qry ### proceed to mdu row Invoke-Sqlcmd -ServerInstance $publisher -Database $dbn -Query $qry -ErrorAction Stop -Verbose } #end foreach row } #end if } #end if check_missing_at_mag ####################### #closing statements ####################### $note = "Rows missing at publisher: $rows_missing_at_publisher; Rows missing at subscriber: $rows_missing_at_subscriber" $qry = " EXEC dbo.sp_Save_VisiSys_Log @Id = '$log_id', @note = '$note', @status= '0' , @rowCount = $($rows_missing_at_publisher + $rows_missing_at_subscriber) " Invoke-Sqlcmd -ServerInstance $srv -Query $qry -Verbose -QueryTimeout 10000 -ErrorAction stop } # end process } # End function #New-Alias -Name Compare_publisher_subscriber -Value Compare_publisher_subscriber_v3 Set-Alias -Name Verify-Table -Value Compare_publisher_subscriber Export-ModuleMember -alias * -function * |