Notes on SQL

Scripting a Publication is useful for both Disaster Recovery and copying a Publication to a test environment. If you use the Wizard each time then you have to make sure that you’re consistent with the options and choices made in Live and once again on the Test (or DR) server.  By creating the script you should get something that reproduces the Publication and Subscription in full.

However, generating the script doesn’t always give you an exact copy of what you are scripting from. Some defaults might be included that weren’t used originally and some settings may be specified that were simply not used with the original creation.

A couple of examples I can demonstrate using the Publication scripted in earlier articles (http://wp.me/p3Vxvi-3l), a more entertaining example using partition switching will have to be described.

Example 1 – There wasn’t a Publication Snapshot in the Original

But if you ask SSMS to generate a Create Script for the Publication it will include one:

To generate the scripts from an existing Publication you need to right-click on the Publication name within Replication/Local Publications branch (on the Publisher):

Specify where you want the script to go and it will be generated.

In this option I’ve chosen to have the script sent straight to a New Query Window.

Now bear in mind that this Publication does not use a Snapshot. I never specified it when I originally scripted this publication.

So what is this here for?

If I’m going to use this script as a true copy of the original then I need to remove this entry.

Example 2 – The ‘sync_type’ has been set incorrectly

From the Subscriber, generate the Create Script just as with the Publisher, to a New Query Window.

This time the comments within the script do have the good grace to warn you that a default has been use that you might not want:

 

In this case I need that to be set to ‘none’.

As an aside, in SQL Server 2000 this setting was also case-sensitive – ‘none’ wouldn’t work as expected, but ‘None’ would.

Example 3 – Partition Switching fails with Replication

I have no test example to hand with which to demonstrate this (at least, not one that I can show outside of my employer’s environment) but it is simple enough to describe, now that I know the cause and resolution.

Several databases in use at my current workplace specify different partition schemes between the Publisher and the Subscriber. This is a common practice, particularly where the Subscriber might be a ‘staging’ database used to ultimately transfer data elsewhere.  So the Publisher might keep data for a couple of weeks but the Subscriber only needs to store it for a day or two, because another system is being used to store/consume that data for other purposes (reporting, analysis or whatever).

So, in my innocence I script the Publication and Subscription via SSMS, make the alterations shown in the previous two examples and create the Publication on a test environment. All is good and Replication works fine. Data that I insert into the Publisher appears in the Subscriber and I have that warm, smug feeling of having created a Publication without incident. Something to be savoured.

However, part of creating this test environment also includes setting up the jobs that purge the data in both Publisher and Subscriber, with the use of Partition Switching (for a basic example of Partition Switching, have a look at http://wp.me/p3Vxvi-3l ).

When the job runs against the Publisher that executes Partition Switching I get the following error:

“The table ‘<schema>.<tablename>’ belongs to a publication which does not allow switching of partitions [SQLSTATE 42000] (Error 21867)”.

Just for a laugh, if you want to see just how many errors Replication can produce, go to https://technet.microsoft.com/en-us/library/cc645609(v=sql.105).aspx (and to add to the fun, they aren’t logged).

After much digging around and asking others who have more Replication scars than myself it transpires that some settings aren’t scripted out and also aren’t found by any of the guid screens associated with Replication.

In the past I have right-clicked on the Publication name, selected ‘Properties’ and looked at ‘Subscription Options’, believing that comparing these between the original and the copy would be enough.  Ah, well.

There is a Replication command ‘sp_helppublication’ which shows several setting that are not visible elsewhere. At its most basic, running this command with just the Publication name will produce a row with all of the setting associated with that Publication:

 

With the particular Publication in mind I scrolled along to the far right, and the setting for ‘allow_partition_switch’ was set to 0. As BOL specifies for this parameter – “Specifies whether ALTER TABLE…SWITCH statements can be executed against the published database”.

Executing ‘sp_helppublication’ against the Live Publication shows this value as ‘1’ – so it is permitted in Live but wasn’t scripted anywhere by the automatic process through SSMS.

To change this to the value required requires the command ‘sp_changePublication’, executed against the Publisher DB in question:

EXEC sp_changepublication @publication=N'Publication Name;', @property=N'allow_partition_switch', @value = 'true';

However, that isn’t the end of it. In executing this command it also sets ‘replicate_partition_switch’ to ‘1’, which I don’t want. The publisher and Subscriber in our environments generally have different Partition Switching schemes, so just because the Publisher decides to purge any data doesn’t mean that the Subscriber does too. So I now need to unset that parameter:

--it also sets 'replicate_partition_switch' to 'true' when the previous command executes and we want that as --'false'
EXEC sp_changepublication @publication=N'Publication Name', @property=N'replicate_partition_switch', @value = 'false';

Having jumped through these hoops I now find that Partition Switching works fine and my Publication in Test really is a copy of the Publication in Live.