Improving Replication

I really like replication as a technology. I think the ability to move data around to other systems, at a gross level, is extremely handy in many systems. While SSIS and other ETL tools are very flexible and powerful, they also require quite a bit of work to maintain. Being able to send a table (or a vertical/horizontal partition) to another system is just valuable.

This is why I’m constantly disappointed that SQL Server hasn’t really bolstered their replication technologies to make it more robust.  Don’t get me wrong, there have been improvements in various versions, and replication has come a long way since SQL Server 6.5, but as an overall subsystem in SQL Server, it has a long way to go. The tooling needs work, the reliability and robustness needs work. I find replication brittle, as do many others, and when there are code deployments needed, it seems that administrators often just script out the system, tear it down, make changes, and rebuild it.

That’s not a great plan. In 2017, that shouldn’t be the plan. I don’t get why Microsoft hasn’t made things better, after all, more replication options could mean more SQL Server instances installed to support disparate workloads. However, rather than complain, I’d like to give Microsoft ideas.

Today I’m curious. What would you do to improve replication? What would you like to see? Perhaps you want better monitoring of the process. Simpler setup, such as that available in Azure. Better bidirectional replication? Maybe an easier way to deploy changes? Let us know your ideas for improving SQL Server replication.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , . Bookmark the permalink.

9 Responses to Improving Replication

  1. rsterbal says:

    What are the best resources for learning about it that you have found so far?

    • way0utwest says:

      Learning about replication? Experience. I don’t see great resources and I end up searching and cobbling together knowledge from various sources.

    • Sandra says:

      Chis Skorlinski (Microsoft) is a replication enthusiast/expert and he has an excellent blog: https://blogs.msdn.microsoft.com/repltalk/ It will be a helpful resource as you start using replication. Our user group invited him to debate a Microsoft Always On expert and, in my opinion, replication “won”. That must be why Microsoft doesn’t want to call attention to it. It’s a Standard edition feature.

  2. Lance says:

    nice article Steve, I think w/ the inception of AlwaysON I feel that replication is getting left behind. It seems a bit antiquated and there definitely needs to be some improvements. We use both transactional replication and AlwaysON AG. From my standpoint it would be awesome if they could improve the way new articles get added , make it a little easier to add larger tables instead of creating a snapshot every single time you add anything to the publisher. Additionally it would be nice have an embedded more robust monitoring engine.

    • way0utwest says:

      I’d say is was getting left behind before AlwaysOn, but definitely they push people to use Repl now, with read-only secondaries and expensive licenses.

  3. Hi Steve,

    This is a timely question to be putting out there!

    I’m a fan of replication for a wide variety of challenges, but can only echo you (and many others) in the criticism of the apparent dead-end that replication is in.

    In my opinion replication should get a thorough overhaul to bring it into the modern SQL world. The basic premise can remain as far as I see it, but tooling, scriptability (PowerShell!?), current feature support, H/A infrastructure support, DMV-ification, Extended Events, Traffic compression, etc. etc. should all be addressed.

    Luckily, at PASS Summit 2015, someone from Microsoft (CSS or Engine Team I think), said that replication was going to get some of that much needed attention at some time in this or next year.

    As the most versatile/flexibile data replication feature in SQL Server, I’d be very happy if this happens.

  4. Phil Grayson says:

    I’ve replied on the SSC thread but I completely agree, replication needs a lot of attention. I don’t mind what we currently have and replicating to Azure SQL Database has been really easy, but it needs to scale better and needs to be more robust / bullet proof. I had a draft blog on indexes waiting to be completed so I’ve added my thought and published it. I’ve also added them to the SSC post.

    https://philgrayson.me/2017/06/27/replication-wish-list-and-useful-indexes/

  5. Pingback: Replication Silliness | Voice of the DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s