How to check if Replication components are installed on your SQL Server instance

If you need to check as to whether or not SQL Replication components are installed on a specific instance of your SQL Server, executing the following command on that instance will give you the answer.

EXEC sp_MS_replication_installed

Not installed:

Installed:

SQL Replication on a Table That Contains a SQL_Variant Datatype

I recently tasked with setting up Transactional Replication in SQL 2008 R2. While this in and of itself isn't necessarily complicated, I did run into an issue that kept the initial snapshot from being created. One of the articles (tables) in the publication had two columns that were defined with a SQL_Variant type and the snapshot agent could not convert those columns to create the snapshot. I tried the various column convert settings in the article properties, but they did not help. Only after changing the snapshot format from Native SQL Server to Character was the snapshot able to be created. All of the other article properties were left as default. Since I want all the other articles in this publication to retain the Native SQL Server snapshot format, I created a specific publication for tables that contain SQL_Variant columns.

Sending Availability Group replication traffic through a dedicated network.

We recently set up a SQL 2012 HADR solution that took advantage of Availability Groups (AG’s). In this instance, we utilized a multi-subnet cluster to allow for the primary replica to live in our primary data center, while having the secondary replica in another data center.

Note: In this setup, only asynchronous replicas are allowed.

On each of the servers in the cluster, we have 3 network connections.

Public: All user traffic will flow through this connection

Private: For internal cluster communication

Replication: All AG traffic will be routed through this

After those dedicated connections are setup, database mirroring endpoints are used to receive connections other instances in the AG. You can read more about database endpoints here. Usually, when the endpoints are created in an AG, the following script is executed without specifying a specific IP to use.

--Replica1
CREATE ENDPOINT Hadr_Endpoint
AS TCP(LISTENER_PORT = 5022)
FOR DATA_MIRRORING(ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

--Replica2
CREATE ENDPOINT Hadr_Endpoint
AS TCP(LISTENER_PORT = 5022)
FOR DATA_MIRRORING(ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

Without specifying an IP that the endpoint is listening on, the listener will accept a connection on any valid IP. But, if we want to make sure our AG and user traffic are segregated, we must specify the IP of the Replication network connection. So the script would look like so:

--Replica1
CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.10.89)) --<-- Your Replication IP here
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

--Replica2
CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.20.89)) --<-- Your Replication IP here
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

You can reference the BOL article for creating an endpoint here

NOTE: Equally important when using the CREATE AVAILABILITY GROUP statement is to specify the replication IP in the ENDPOINT_URL section. By default, when scripting the AG, the FQDN will show in that section.

CREATE AVAILABILITY GROUP MyAG   
FOR       DATABASE MyDB1, MyDB2   
REPLICA ON      
'COMPUTER01' WITH         
(        ENDPOINT_URL = 'TCP://10.10.10.89:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL          ),      
'COMPUTER02' WITH         
(        ENDPOINT_URL = 'TCP://10.10.20.89:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL          );

You can read up on the CREATE AVAILABILITY GROUP statement here.

After this done, and you’ve completed the rest of the AG setup, replication traffic will be routed through your Replication network. A quick and easy test of this is to open up Windows Task Manager and watch the traffic on the three different Ethernet connections.

Note the adapter name for each connection.

To test this open up an SSMS connection to the AG listener and send a large amount of DML statements to a database within the AG. If you setup the endpoint with the default LISTENER_IP = ALL, you’ll most likely see a high amount of send and receive traffic on Public interface. But, if you’ve setup the endpoint to listen on the Replication IP, you’ll see a large amount of receive traffic on the Public interface (DML) and a high amount of send traffic on the Replication interface (sending the changes to the secondary replica).

SQL Error in the Post Snapshot File for Transactional Replication

When using replication, it is sometimes useful to run a set of commands on the subscribing database either before or after the initial snapshot has been applied. By using a pre-snapshot file, a DBA could remove any dependencies in the subscribing database that would keep the table from being dropped in the event of a re-initialization. We use post-snapshot files to create indexes on the subscribing database that do not exist on the publisher. To find out whether or not pre/post snapshot files are being used, right click on the publication and choose properties. From there, select snapshot and look under the “Run additional scripts” section.

 

So when the subscription is initialized, a snapshot is prepared and placed in the default folder. Along with the bulk copy files, any pre or post snapshot files you are using are placed in there too. So the snapshot is applied to the subscriber and, in this case, a post-snapshot script is ran against the subscribing database. While the snapshot and the post-snapshot scripts are being ran, updates to the replicated tables are being queued for replication in the distribution database. These will be replicated to the subscriber once the snapshot has been applied and the post-snapshot script has completed.

But what happens in the event that the post-snapshot script has an error? The distribution agent considers this to be a failure and retries the post-snapshot script over and over again. While this is happening, updates that are queued for distribution are not being applied to the subscriber. How can we fix this?

One option is to open the post-snapshot file at the location under “Run additional scripts”, fix the error, reinitialize the subscription and re-create the snapshot. This might not be the best option depending on the size of your snapshot and the time it takes to generate it.

The second option is to directly open the post-snapshot file that was copied into the folder when the snapshot was created. By editing the post-snapshot file in the folder specified in the “Put files in the default folder” option, we are directly modifying the SQL that the distribution agent is executing and failing on. If time is critical, you may want to remove all SQL from the file and save it. Note: If you do this, you will have to manually apply the SQL to the subscriber. Once the error in the file either is fixed or the file is cleaned out, the distribution agent will open the file and complete successfully. After that, all the queue updates, inserts and deletes can flow from the publisher to the subscriber.

Remove an Article from Transactional Replication without dropping the Subscription

          While using Transactional Replication, there may come a time when you need to drop an article from a publication, a publication that may have one or more subscriptions. You would think that the process would be very similar to adding an article to the publication; open up the article and uncheck the box. Now, if there were no subscribers to the publication, this would not be an issue, but odds are that this publication has been used for some time and there are one or more subscribers to it. If this is the case, when you go to uncheck the article to remove it, you may receive an message stating that since there are subscriptions on this table, you will have to reinitialize this subscription and generate a new snapshot.

          So how can we get around re-initialization of the subscriber and new snapshot generation? We manually execute some of the replication stored procedures to remove the article and keep the snapshot from being invalidated.

First we must use sp_dropsubscription to remove the subscription to the individual article.

EXEC sys.sp_dropsubscription

    @publication = '<PublicationName>',
    @article = '<ArticleToDrop>',
    @subscriber = '<SubscribingServer>',
    @destination_db = '<DestinationDatabase>'
 
Next, we drop the article from the publication without invalidating the snapshot. We do that by executing sp_droparticle with the force_invalidate_snapshot set to 0.

EXEC sys.sp_droparticle @publication = '<PublicationName>',
    @article = '<ArticleToDrop>',
    @force_invalidate_snapshot = 0

Finding Stored Procedures Related to Replication

If you have transactional or merge replication configured in your environment, you may want to know what objects were create by replication in your subscribing database. For Stored Procedures, there’s nothing in the sys.objects or sys.procedures catalog views that denotes that a procedure is used with replication. Fortunately, there is a system table created in the subscribing database called MSreplication_objects. The query below will give you the Stored Procedures created by replication and the articles associated with them.

SELECT [publisher]
      ,[publisher_db]
      ,[publication]
      ,[object_name]
      ,[object_type]
      ,[article]
  FROM [MSreplication_objects]
 WHERE [object_type] = 'P'

Now that we know the procs that are related to replication, we can exclude them from sys.objects or sys.procedures when looking for user procs in the database.

SELECT *
 FROM   sys.procedures
 WHERE  name NOT IN (SELECT [object_name]
                     FROM   MSreplication_objects
                     WHERE  [object_type] = 'P')