Press "Enter" to skip to content

Category: Replication

Transactional Replication Procedures

Published 2017-04-12 by Kevin Feasel

Drew Furgiuele offers up warnings when thinking about rolling your own transactional replication stored procedures:

In the above picture, we can see that it did replicate the execute statement, and that it affected 19,972 rows on the replica, and it only took 67ms! Sounds awesome, doesn’t it? Here’s a way to handle large batch updates at your publishers without overwhelming your replication setup. But before you go changing everything, you should probably understand that this has some really, really bad side effects if you’re not careful. Let’s look at three really big ones.

All in all, it’s a fairly risky move but might be worth the performance improvements.

Comments closed

Replication And TDE

Published 2017-03-16 by Kevin Feasel

Drew Furgiuele looks at how replication interacts with Transparent Data Encryption:

But what happens if we set up a transactional replication publication on this database and do a snapshot? Remember that when you create a publication, your distributor and subscriber(s) need to know which network share (or FTP server) to drop all the data and schema definitions to so they can be read in by the distribution agent and recreated. In my example, I’m dropping them to a network share. Once the snapshot completes, let’s go check out our subscriber database…

Uh oh. The same query returned zero results at the subscriber. Which means no encryption! Replication won’t replicate encryption, at all. So if you have a requirement to encrypt your data at the source, you’ll need to do it on your subscribers too.

Drew points out a couple important gotchas which might lead to you exposing information you didn’t intend to make available.

Comments closed

Replication Extended Events

Published 2017-03-09 by Kevin Feasel

Drew Furgiuele goes hunting for the most dangerous creature of all, replication-related extended events:

Extended events are great; they have all the goodness of profiler except you don’t use profiler. Win/win! More to the point, extended events let you quickly and easily view, sort, and aggregate events that occur on your instances. They also have powerful filters (really, a “where” clause) to limit noise. You have way more control over what you monitor, how you store the data, and how you view and use it. This makes them perfect use to track replicated transactions, since we want to measure at both an individual level and the aggregate.

I fired up management studio and went to “New Session” looking for some replication event goodness and I found…

… nothing. I tried looking for events that had even parts of the name replication in it. No such thing, apparently.

This doesn’t deter Drew and he ends up building some interesting events to infer the correct answers.

Comments closed

Replication With SQL_Variant Datatypes

Published 2017-03-01 by Kevin Feasel

Kevin Eckart ran into an interesting issue when trying to set up transactional replication on a table with 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.

Read on for the answer.

Comments closed

Upgrading Replication To SQL Server 2016

Published 2017-02-07 by Kevin Feasel

Amit Banerjee explains the steps for upgrading replicated SQL Server instances to 2016:

  • A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher).

  • A Publisher can be any version as long as it less than or equal to the Distributor version.

  • Subscriber version depends on the type of publication:

    • A Subscriber to a transactional publication can be any version within two versions (n-2) of the Publisher version. For example: a SQL Server 2012 Publisher can have SQL Server 2014 and SQL Server 2016 Subscribers; and a SQL Server 2016 Publisher can have SQL Server 2014 and SQL Server 2012 Subscribers.

    • A Subscriber to a merge publication can be any version less than or equal to the Publisher version.

There are several options available here, and it’s worth reading through if you’re looking to upgrade soon.

Comments closed

Replication Error When Listing Directory Contents

Published 2017-02-06 by Kevin Feasel

Andrew Peterson troubleshoots a replication issue:

You’re trying to setup SQL Server Replication on a server, and it fails. Looking thru the error message you find this:

        An exception occurred while executing a Transact-SQL statement or batch.
        (Microsoft.SQLServer.ConnectionInfo)

        Destination path ………….is not valid. Unable to list directory contents. Specify a valid
            destination path.
            Changed database context to ‘master’. (Microsoft SQL Server, Error: 14430)

Read on for the solution.

Comments closed

Replication And Date Conversion

Published 2017-01-17 by Kevin Feasel

Jeffrey Verheul digs up a strange replication bug:

After a lot of different variables in the test-setup, I found out that it’s probably an old bug that wasn’t properly patched when upgrading the SQL Server engine to a newer version. Let me elaborate on that:

– The bug is reproducible on the test server, which is an upgraded engine from SQL 2012 or 2014 to SQL 2016 RTM
– The bug is reproducible on the production server, which is an upgraded engine from SQL 2014 to SQL 2016 RTM
– The bug is not reproducible on a clean install of SQL 2014
– The bug is not reproducible on a clean install of SQL 2016 RTM
– The bug is not reproducible on a clean install of SQL vNext CTP

There’s a lot of good investigative work here, so check it out.

Comments closed

Replication Support For Dropping Tables

Published 2017-01-10 by Kevin Feasel

Drew Furgiuele recovers from a case of the vapors after discovering a replication codebase update:

To enable this change though, there’s more to it than just “you need to be running this service pack level on your publisher.” It’s far, far more than that:

  • Your publisher needs to be running SQL Server 2016 SP1 or 2014 SP2. We’ve already seen that is a requirement based on the content of the KB article. Big whoop.
  • In addition, if you have the same article in more than one publication, you have to enable this feature on all of them or you’ll still get the error that a table is in replication and can’t be dropped.
  • Your subscribers don’t care about the SQL Server version (except when you have a data type that exists in the publisher that doesn’t at the subscriber. Then it cares. But for this change, it doesn’t).
  • Your distributor also needs to be running a version that supports this functionality.

I can’t make that last point any bolder; that’s not documented anywhere that I could find. I’ll explain why later, but for now, roll with it. In fact, let’s see what happens if we don’t upgrade our distributor as well. Time to go to the lab!

Read the whole thing, particularly the warning about how dropping a table on the publisher will not drop tables on the subscriber.

Comments closed

Testing Transactional Replication

Published 2017-01-03 by Kevin Feasel

Jes Borland wraps up her series on transactional replication from an on-prem Availability Group to Azure SQL Database:

Congratulations, you’ve configured a remote distributor, configured all of your AG replicas as publishers, and configured your SQL Database as a subscriber! Now you want to ensure that transactions are replicating to the database, and that they continue to do so if there is a failover in the AG.

Read on for the two testing scenarios.

Comments closed

Replication Subscriber To Azure SQL DB

Published 2016-12-30 by Kevin Feasel

Jes Borland continues her series on setting up transactional replication between an on-prem SQL Server Availability Group and Azure SQL Database:

This subscription is going to use an Azure SQL Database.

Go to the AG primary replica. (In this demo, this is SQL2014AG2.)

Expand Replication. Expand Local Publications. Right-click the publication and select New Subscription.

It turns out that this is a basic push subscription.  Jes’s post is full of screenshots, making it even easier to follow.

Comments closed