Replication

Push vs. pull: Configuring SQL Server replication

I’ve published a new artcile over on SearchSQLServer.com.  This article is titled “Push vs. pull: Configuring SQL Server replication“.  In this article I talk about the differences between the two, and when each should be used, as well as how to combine them to get the best of both.

Denny

How do I change from push to pull subscriptions in SQL Replication?

The official answer is to delete the subscriber and recreate it pushing a new snapshot to the subscriber.

The much quicker and easier method is as follows.

1. Stop the distribution agent on the machine that it’s currently running on.

2. Disable the SQL Agent job that runs the distribution agent.

3. Script out the SQL Agent job from the old server and create it on the new server.

4. Enable the job on the new server.

Done.  You have just changes replication from being a push to a pull (or from being a pull to a push).

If you wanted to you could even setup your distribution agent on a third computer, but it is easier to keep track of everything if it’s running on the distributor or the subscriber.

Happy replicating.

Denny

Pull vs. Push Subscriptions? Which one should you use?

This is a “it depends” sort of question.

These are my recommendations, your mileage may vary.

Your distributor is on the same system as your publisher - Pull is probably for you

Your Subscribers are a very high transaction count - Push is probably for you

You need to manually copy the subscription over the network to the subscriber and load it up from the local drive - Pull is probably for you

Your distributor is on a separate from the publisher - Push is probably for you

The distributor is on the same server as your subscriber - Either, as the agent will be running on the distributor either way

You have a slow network link - Either, slow networks aren’t overcome with either technique

If you have specific’s you’d like to ask about, post your questions below, or in the ITKE forum.

Denny

Decreasing Network Traffic and Network Latency When Using Replication

When dealing with a high load replication environment network latency and network traffic can become as issue. There are a few settings you can play with, within the subscription agents themselves such as the –PacketSize, -UseInprocLoader, and –SubscriptionStreams. While working with these settings may or may not help (personally I’ve had very limited success getting them to improve the data flow rate) there are some other techniques you can use.

The best technique to you is to include your stored procedures as articles within the publication. This will allow SQL Server to instead of replicating the entire record from one server to another, it will simply run the stored procedure on the subscriber with the same input settings as it was run with on the publisher. If you have very complex stored procedures, you’ll want to evaluate the increase load on the subscribers CPU against the reduction of network traffic between the distributor and subscriber.

This technique is critical to use to reduce the network traffic if you have a multi-tier replication topology. This is where your subscribers are also publishers to subscribers of their own. This is because of the way that the replication writes data to the subscriber. Replication uses stored procedures on the subscriber to update the values of the record. Assuming a table which looks like this:

CREATE TABLE Employee
(EmployeeId INT IDENTITY(1,1),
LastName NVARCHAR(50),
FirstName NVARCHAR(50),
DepartmentId INT)

The stored procedure which Microsoft uses for replication will look like this.

CREATE PROCEDURE MS_Employee_Update
@EmployeeId INT,
@LastName NVARCHAR(50),
@FirstName NVARCHAR(50),
@DepartmentId INT,
@Columns INT,
@KeyField INT
AS
UPDATE Employee
SET EmployeeId = CASE WHEN @Columns & 1 = 1 THEN @EmployeId ELSE EmployeeId END,
LastName = CASE WHEN @Columns & 2 = 2 THEN @LastName ELSE LastName END,
FirstName = CASE WHEN @Columns & 4 = 4 THEN @FirstName ELSE FirstName END,
DepartmentId = CASE WHEN @Columns & 8 = 8 THEN @DepartmentId ELSE DepartmentId END
WHERE EmployeeId = @KeyField
GO

While this technique works great for a two tier replication topology, we suddenly have a problem when we try to replicate from the second tier to the third tier. On the second tier all the columns have been updated, no matter which columns have actually been updated. This will cause an undo amount of replication traffic between the second and third tier servers as every column must now be replicated instead of just the values which have changed.

When you replicate the stored procedures as well as the tables when the stored procedure is fired to update the data on the publisher, that same stored procedure will be fired on the subscriber thereby reducing the amount of data which needs to be sent from the second tier to the third tier as again only the stored procedure execution is replicated. On very wide tables this reduction of data can reduce your network traffic load up to 70% (or higher depending on the data being changed and the width of the table).

You can verify that SQL Server is indeed using the stored procedures by using SQL Profiler to profile the distribution process on the subscriber. If you see your stored procedure names being fired then it’s using this technique. If you see procedures named by Microsoft, then it is not.

This technique will only work, if your application has no inline SQL. If you use a combination of stored procedures and inline SQL within the application, the data which is changed by stored procedure execution will be replicated using this technique; while the data which is changed by the inline code will be replicated using the traditional technique. If you have dynamic SQL within your stored procedures the SQL Server will not be able to use the replicated procedure to update the data on the subscriber. The dynamic SQL will prevent the SQL Server from recognizing that the stored procedure is what caused the data change, and the data will be replicated using the normal technique.

Denny

Getting more error data from SQL Server Replication

The error reporting in SQL Server Replication isn’t all that great.  This is a well known issue that pretty much everyone knows about.  Something that I don’t know if a whole lot of people know about, is that there is a way to get a lot more information from replication about what’s going on, and what’s going wrong.

This is most easily done by running the replication job manually from within a command window on the distributor.  This will allow you to add switches or change values as needed and easily see the output, or redirect the output to a file for analysis, sending to Microsoft, your consultant, etc.

 Replication is run by jobs, with job steps of some funky types.  These step types simply mask what is happening in the background.  SQL is shelling out and running a command line app and passing it all the switches as they are within the job step.

All the command line apps which replication uses are in the “C:\Program Files\Microsoft SQL Server\90\COM” folder by default (for SQL 2000 replace the 90 with 80, for SQL 2008 replace the 90 with 100).  In that folder you will find a few apps which are of interest.  When you run the snapshot job snapshot.exe is called.  When you run a distribution job DISTRIB.exe is called (I’ve got no idea why it’s uppercase).  When the log reader is running logread.exe is run.  When you are running merge replication replmerg.exe is run.

All of these can be run manually from a command prompt.  For starters open up the SQL Job and edit step two, the one which actually does the work.  Copy all the text in the command window and paste is after the filename in the command prompt window and press enter.  You will need to stop the SQL Agent job before you can actually run the command from the command prompt, as replication is designed so that you can only run the commands one at a time.

Now the whole point of this was to get more log info because the replication is failing.  This is done by added the “-OutputVerboseLevel” switch to the command.  This switch has between 3 and 5 logging levels depending on which command you are running.  0 (zero) is basically no logging, and as the number goes up more data will be shown.  The distrib.exe, replmerg.exe and snapshot.exe takes 0-2, while the logread.exe takes 0-4.

You should only do this when replication is failing and you can’t figure out why, and all SQL is telling you is some cryptic error message.

Hopefully you’ll find this information useful.

Denny

Log Shipping without SQL Server Enterprise Edition

Microsoft’s Log Shipping is pretty good.  But it requires that you have SQL Server Enterprise Edition on both the machines.  This makes the solution fairly expensive.  Because of this I’ve written a replacement which can be used on any edition of SQL Server including SQL Server Express Edition (as long as you use a different scheduler).

I recommend keeping the drive letters the same on the two machines, but this isn’t required.

First backup the database to log ship and restore it to the backup server using the WITH STANDYBY option.  You are now prepped to start shipping the transaction log.

Setup a job on the primary server which backs up the log every few minutes to a network share on the backup server.  I use code alone these lines.

backup log Northwind to disk='\\backupsql\BackupFolder\northwind.log' with NOINIT, NOSKIP, NOFORMAT
go

Add a second step to the job which uses osql to start a job on the backup server.  Something like this.  (The on failure action should be to Quit with Success for this step.)

osql -S BackupSQL -E -Q "msdb.dbo.sp_start_job 'Restore Northwind Log'"

Setup a restore job on the backup server (making sure that the name matches the job name in the second step of the backup job.  The restore job will have four steps in it.

Step 1 (T/SQL):

/*This first part of the code ensures that no one is using the database that we are about to restore.  If we don't do this then the restore will fail.*/
declare @spid as varchar(10)
declare @CMD as varchar(1000)
declare cur CURSOR for select spid from master.dbo.sysprocesses where dbid =
        (select dbid from sysdatabases where name = ‘Northwind’)
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
    set @CMD = ‘kill ‘ + @spid
    exec (@CMD)
    fetch next from cur into @spid
END
close cur
deallocate cur
go


Step 2 (OS Command):

del d:\RestoreFolder\Northwind.2.log
REM /*This removed the last file we processed.*/

Step 3 (OS Command):

move d:\RestoreFolder\Northwind.log d:\RestoreFolder\Northwind.2.log
REM /*This moves the current file into place for processing.*/


Step 4 (T/SQL):

declare @i int
declare @j int
set @j = 1
set @i = 0
restore headeronly from disk=’d:\RestoreFolder\Northwind.2.log’ /*This tells us how many transaction log backups there are in the file that we need to restore.*/
set @i = @@ROWCOUNT
while @i+1 > (@j)  /*This loop runs us through the file restoring the logs in order.*/
BEGIN
    restore log Northwind from disk=’d:\RestoreFolder\Northwind.2.log’
        WITH FILE = @j,
            STANDBY = ‘F:\MSSQL\Backup\RMDBArchive.sby’ /*This keeps the database in standby mode ready for more restores.*/
    set @j = @j + 1
END

If your folders don’t exist between the two servers you’ll need to add the MOVE parameter to the restore commands.

If you do being using this please post a note to the comments if you can.  I like to know who is using this log shipping code.