Script Replication with PowerShell

I promised you some PowerShell scripts this year and this post is the first one.  I can see the value PowerShell can bring, but find it equally frustrating.  I don’t know the syntax very well or all the command lets and personally for 99.9% of my daily tasks I can achieve the desired results in T-SQL.  I would say that PowerShell has much more of an appeal to Domain Administrators, specifically those who manage large domains with tens or hundreds of thousands of objects.  Having said that, I have stuck with PowerShell and do try to have a dabble when the opportunity arises.

The script in this post will script your replication topology to a file for use in the event of DR.  Why? Well, having a solid DR plan is by no means an easy task, and without testing your DR plan I can say with some certainty that there will be something you have missed, whether it is a login, firewall port or out of date replication topology, that is where this post comes in.  I am a massive fan of replication, having used it in every single one of my DBA positions to achieve a multitude of requirements but that is not to say it hasn’t caused me any problems, I have spent many a late night reinitialising subscriptions and troubleshooting seemingly unknown problems.

Replication is one area that is often overlooked in a DR scenario, do you have up to date scripts to recreate your replication topology?  No?  You’re not telling me that you are going to recreate all those publications using the GUI during DR are you?  All those filtered articles, all those keep existing object unchanged settings and not to forget the articles with do not replicate delete statements set.

The PowerShell script is below, simply save it to a file called ScriptReplication.ps1;

#Load command-line parameters - if they exist

param ([string]$sqlserver, [string]$filename)

#Reference RMO Assembly

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | out-null

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo") | out-null

function errorhandler([string]$errormsg)

{

    writetofile ("-- Replication Script Generator run at: " + (date)) $filename 1

    writetofile ("-- [Replication Script ERROR] " + $errormsg) $filename 0

}

function writetofile([string]$text, [string]$myfilename, [int]$cr_prefix)

{

    if ($cr_prefix -eq 1) { "" >> $myfilename }

    $text >> $myfilename

}

function initializefile([string]$myfilename)

{

    "" > $myfilename

}

trap {errorhandler($_); Break}

#Deal with absent parameters

[string] $hostname=hostname

if ($sqlserver -eq "") {$sqlserver = read-host -prompt "Please enter the server name or leave blank for Hostname"}

if ($filename -eq "")  {$filename = read-host -prompt "Please enter the file name (eg 'c:\ReplicationBackupScript.sql')..."}

if ($sqlserver -eq "")   {$sqlserver = $hostname}

if ($filename -eq "")   {$filename = "c:\ReplicationBackupScript.sql"}

# Clear file contents

if (Test-Path  ($filename)) {Clear-Content $filename}

$repsvr=New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $sqlserver

initializefile $filename

# if we don't have any replicated databases then there's no point in carrying on

if ($repsvr.ReplicationDatabases.Count -eq 0)

{

    writetofile ("-- Replication Script Generator run at: " + (date)) $filename 0

    writetofile "-- ZERO replicated databases on $sqlserver!!!" $filename 1

    EXIT

}

# similarly, if we don't have any publications then there's no point in carrying on

[int] $Count_Tran_Pub = 0

[int] $Count_Merge_Pub = 0

foreach($replicateddatabase in $repsvr.ReplicationDatabases)

{

        $Count_Tran_Pub = $Count_Tran_Pub + $replicateddatabase.TransPublications.Count

        $Count_Merge_Pub = $Count_Merge_Pub + $replicateddatabase.MergePublications.Count

}

if (($Count_Tran_Pub + $Count_Merge_Pub) -eq 0)

{

    writetofile ("-- Replication Script Generator run at: " + (date)) $filename 0

    writetofile "-- ZERO Publications on $sqlserver!!!" $filename 1

    EXIT

}

# if we got this far we know that there are some publications so we'll script them out

# the $scriptargs controls exactly what the script contains

# for a full list of the $scriptargs see the end of this script

$scriptargs = [Microsoft.SqlServer.Replication.scriptoptions]::Creation `

-bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles `

-bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions `

-bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions

writetofile ("-- Replication Script Generator run at: " + (date)) $filename 0

writetofile "-- PUBLICATIONS ON $sqlserver" $filename 1

writetofile "-- TRANSACTIONAL PUBLICATIONS ($Count_Tran_Pub)" $filename 1

foreach($replicateddatabase in $repsvr.ReplicationDatabases)

{

    if ($replicateddatabase.TransPublications.Count -gt 0)

    {

        foreach($tranpub in $replicateddatabase.TransPublications)

        {

            writetofile "/********************************************************************************" $filename 0

            writetofile ("***** Writing to file script for publication: " + $tranpub.Name) $filename 0

            writetofile "********************************************************************************/" $filename 0

            [string] $myscript=$tranpub.script($scriptargs) 

            writetofile $myscript $filename 0

        }

    }

}

writetofile "-- MERGE PUBLICATIONS ($Count_Merge_Pub)" $filename 1

foreach($replicateddatabase in $repsvr.ReplicationDatabases)

{

    if ($replicateddatabase.MergePublications.Count -gt 0)

    {

        foreach($mergepub in $replicateddatabase.MergePublications)

        {

            writetofile "/********************************************************************************" $filename 0

            writetofile ("***** Writing to file script for publication: " + $mergepub.Name) $filename 0

            writetofile "********************************************************************************/" $filename 0

            [string] $myscript=$mergepub.script($scriptargs) 

            writetofile $myscript $filename 0

        }

    }

}

You can then copy this to a Server you want to script your replication topology from and create a SQL Agent job to execute the script.  I have just the script below, It will execute the PowerShell script and email an operator called DBA in the event of a failure.  You will need to;

·         Update the operator

·         Update ServerNameHere with the name of the server you are scripting replication from

NOTE - If this is a named instance then this is simply in the form of ServerName\InstanceName

·         Update the schedule accordingly

·         Update the path to the PowerShell script accordingly

·         Update the path to the CreateReplication.sql file accordingly

Note - I run this at 06:00 every Monday Wednesday and Friday, I also run this manually if a change to any of the publications has changed.  I also have a synchronisation process which copies this over to the DR Server.

USE [msdb]

GO

/****** Object:  Job [Maintenance - ScriptReplication]    Script Date: 02/14/2013 13:39:27 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 02/14/2013 13:39:27 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Maintenance - ScriptReplication',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=2,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'This job will script the replication topology to G:\Powershell\ScriptReplication\CreateReplication.sql for use in DR',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'sa',

            @notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Script Replication]    Script Date: 02/14/2013 13:39:28 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Script Replication',

            @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'PowerShell',

            @command=N'G:\Powershell\ScriptReplication\ScriptReplication.ps1 -sqlserver ''ServerNameHere'' -FileName ''G:\Powershell\ScriptReplication\CreateReplication.sql''',

            @database_name=N'master',

            @flags=40

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'MWF - 06:00',

            @enabled=1,

            @freq_type=8,

            @freq_interval=42,

            @freq_subday_type=1,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=1,

            @active_start_date=20121024,

            @active_end_date=99991231,

            @active_start_time=60000,

            @active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Enjoy!

Chris

Replication Latency

I've used Replication a lot over the years and contrary to the opinions of most DBA’s I know, I think it is a brilliant feature.  Replication I feel gets a bad reputation for causing a number of problems for DBA’s, in most cases it is something they inherit when they change roles.  The fact of the matter as with anything, when implemented correctly it works fantastically well.  Whatever your implementation of Replication is, be it for High Availability or Disaster Recovery one of the biggest challenges is monitoring it.  I've used and continue to use Replication Monitor, system tables and system procedures in various implementations to keep an eye on a number of areas of Replication. 

The most recent addition to my Replication monitoring arsenal is a script I wrote using system procedures to monitor replication latency.  It inserts a tracer token waits while this is delivered then gets the latency information of said token and writes the results to a table.  This results table can then be queried to produce reports and or alerts.

The script for the table is below;

/*

      -----------------------------------------------------------------

      Create ReplicationLatency table

      -----------------------------------------------------------------

   

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database Context

USE master;

GO

-- Create ReplicationLatency table

IF NOT EXISTS ( SELECT  1

                FROM    sys.objects

                WHERE   [object_id] = OBJECT_ID('dbo.ReplicationLatency')

                        AND [type] = 'U' )

CREATE TABLE dbo.ReplicationLatency

(

      ReplicationLatencyID INT IDENTITY(1,1) CONSTRAINT [PK_ReplicationLatency:ReplicationLatencyID] PRIMARY KEY CLUSTERED,

      DistributorLatency INT NULL,

      SubscriberServerName VARCHAR(255) NOT NULL,

      SubscriberDatabaseName VARCHAR(255)NOT NULL,

      SubscriberLatency INT NULL,

      OverallLatency INT NULL,

      PublicationName [varchar](255) NOT NULL CONSTRAINT [DF_ReplicationLatency:PublicationName] DEFAULT ('x'),

      RunDateTime DATETIME CONSTRAINT [DF_ReplicationLatency:RunDateTime] DEFAULT (GETDATE()),

      IsArchived BIT CONSTRAINT [DF_ReplicationLatency:IsArchived] DEFAULT (0)

);

GO

The IsArchived BIT column is used solely for an archive strategy I have to move “old” data out of production databases on expensive fast disk arrays to archive databases on less expensive SATA arrays.  Yes I have created the table in master, it would be a good idea to move it to another database which you see fit.

The script below does all the work.  I simply use this in a SQL Agent Job to execute every 10 minutes to record the results.  I have tested this on SQL Server 2008 R2 only.

/*

      -----------------------------------------------------------------

      Replication latency Monitor

      -----------------------------------------------------------------

   

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database context

USE distribution;

GO

-- Declare variables

DECLARE @publication AS SYSNAME

DECLARE @publisher_db AS SYSNAME

DECLARE @tokenID AS INT

DECLARE @MinPubID INT

DECLARE @MaxPubID INT

DECLARE @SQL VARCHAR(4000)

DECLARE @Delay VARCHAR(24)

-- Build @Delay string.  This is to give the tracer token time to reach the subscriber and may need to be updated depending on your environment

SET @Delay = 'WAITFOR DELAY ''00:00:05''';

-- Drop temporary table if exists

IF OBJECT_ID('tempDB.dbo.#Publications') IS NOT NULL

    DROP TABLE #Publications ;

IF OBJECT_ID('tempDB.dbo.#tokens') IS NOT NULL

    DROP TABLE #tokens ;

-- Create temporary tables

CREATE TABLE #Publications

    (

      ID INT IDENTITY(1, 1),

      publisher_db SYSNAME,

      publication SYSNAME

    );

CREATE TABLE #tokens

      (

            tracer_id INT,

            publisher_commit DATETIME

      );

-- Insert publications

INSERT INTO #Publications ( publisher_db, publication )

SELECT publisher_db, publication

FROM distribution.dbo.MSpublications

WHERE publication_type = 0; -- Transactional publications  

-- Setup loop Control

SELECT @MinPubID = MIN(ID) FROM #Publications;

SELECT @MaxPubID = MAX(ID) FROM #Publications;

WHILE @MinPubID <= @MaxPubID

BEGIN

     

      -- Set Publication

      SELECT @publication = Publication FROM #Publications WHERE ID = @MinPubID;

      SELECT @publisher_db = publisher_db FROM #Publications WHERE ID = @MinPubID;

     

      -- Truncate #tokens ready for next publication

      TRUNCATE TABLE #tokens;

     

      -- Build @SQL string

      SET @SQL = 'EXEC ' + @publisher_db + '.sys.sp_posttracertoken

        @publication = ' + @publication

     

      -- Insert a new tracer token in the publication database 

      EXEC ( @SQL ); 

      -- Wait 10 seconds for the token to make it to the Subscriber

      EXEC ( @Delay );

      -- Build @SQL string

      SET @SQL = 'INSERT #tokens (tracer_id, publisher_commit)

      EXEC ' + @publisher_db + '.sys.sp_helptracertokens @publication = ' + @publication

     

      -- Return tracer token information to a temp table

      EXEC ( @SQL );

     

      -- Get tracer token ID

      SET @tokenID = (SELECT TOP 1 tracer_id FROM #tokens

      ORDER BY publisher_commit DESC);

      -- Build @SQL string

      SET @SQL = ' INSERT master.dbo.ReplicationLatency (DistributorLatency, SubscriberServerName, SubscriberDatabaseName, SubscriberLatency, OverallLatency)

      EXEC ' + @publisher_db + '.sys.sp_helptracertokenhistory

        @publication = ' + @publication + ',

        @tracer_id = ' + CONVERT(varchar,@tokenID)

       

      -- Get history for the tracer token

      EXEC ( @SQL )

      -- Update the PublicationName for most recently inserted record

      UPDATE SSIS.dbo.ReplicationLatency

      SET PublicationName = @publication

      WHERE PublicationName = 'x';     
 

      -- Incriment loop control

      SELECT @MinPubID = @MinPubID + 1

END

Enjoy!

Chris