Practical advises for Transactional Replication

Transactional replication (as well as Replication in general) is still quit famous among SQL Server customers. The borderline, though, from becoming infamous is very thin…

If you are the short-story type of reader, then you can skip the following paragraphs and go to the gist.

Some history

Replication in SQL Server goes back to version 2000 (at least) and is still supported in 2016! While many other critical features come and go, replication is still alive and kicking revealing its importance and stability.

Did I say stability? Well I meant sustainability through the years. No other data synchronization approach can provide so powerful features and fulfil specific design needs, better than replication. There are though some great misunderstandings regarding replication, and we (the Databusters) are here to knock them down.

  • Replication is NOT a disaster-recovery solution, although it has been widely used as one…
  • Replication is NOT a high availability (HA) solution, although it has been widely used as one…
  • Replication is NOT a panacea.

Instead of enumerating what Replication is NOT about, lets see what Replication IS all about. IMHO replication is your best and only choice for a partial replication of data between two databases. It can synchronise data between different databases in terms of schema, server, version, edition and even vendor! MSDN has (once again) a variety of documents regarding transactional replication, but I think that the following tells just about everything you need to know, in order to decide if it is the right choice for you.

The gist

My recommendations regarding a transactional replication setup could be summarised in the following quote:

Don’t stick to the default options! Do some customizations and they will pay off.

More specifically:

  1. Exercise with the wizard but always script the process and execute it afterwards.
  2. Don’t stick to the default tables’ options. Scan through the various options and decide whether or not you should: replicate FKs, PKs, indices, partitions, transform any datatype, drop and recreate existing objects.
  3. Consider performing the initialization from backup rather than snapshot for big databases.
  4. Specify security for the agents according to your special needs and the best practices.
  5. Learn how to add/remove articles in a publication without having to drop/recreate the underlying subscriptions (this can be achieved only through T-SQL).
  6. Read about ‘NOT FOR REPLICATION’ clause, cause it’s a great saver.
  7. Learn how to troubleshoot replication errors instead of reinitializing the subscriptions. There are some nice documents on this.
    Troubleshooting Transactional Replication
    How to skip a transaction in SQL 2005/2008 Transactional Replication

We will deal with more detail in specific aspects of Replication in the future.


Leave a Reply

Your email address will not be published. Required fields are marked *