Sql-server – Issues setting up a publisher for transactional replication on a SQL Server with a custom instance

1) If we can set up the transactional replication between server A of version 2014 and server B of version 2012. Tried to Google out but could not find the exact documents because may be am looking somewhere wrong. Please suggest if it can be set up?

For your replication to work, the PUBLISHER and DISTRIBUTOR should be on same version. The subscriber can be on lower version.

From BOL :

For all types of replication, the Distributor version must be no earlier than the Publisher version. (Frequently, the Distributor is the same instance as the Publisher.)

For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version.

So, essentially, your SERVER A should be PUBLISHER and DISTRIBUTOR and your SERVER B should be SUBSCRIBER.

2) We need to implement encryption on server A, but this encrypted data needs not to go on server B as that is SAN encrypted. Is there a way we can do this, if replication can be successfully implemented as asked in point 1?

For your scenario, you should enable TDE on PUBLISHER database ONLY.

From BOL :

Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted intermediate files; for example, the bcp files. During transactional or merge replication, encryption can be enabled to protect the communication channel.

First, you can set up Canary table and other detailed monitoring of transactional replication per Kendra Little'ss article Monitoring SQL Server Transactional Replication. Canary tables, the easiest step in the article, are described as:

It’s easy to build your own system for tracking replication latency for each publication. Here are the ingredients for the simplest version:

• Add a table named dbo.Canary_PubName to each publication

• dbo.Canary_PubName has a single row with a datetime column in it

• A SQL Server Agent job on the publisher updates the datetime to the current timestamp every minute

• A SQL Server Agent job on the subscriber checks dbo.Canary_PubName every minute and alerts if the difference between the current time and the timestamp is greater than N minutes

Then take a look at the activity happening on the tables being replicated. It sounds like most of your problems are related to very high activity - your log size moving from 3GB with only 13-20% used, to 3.5GB (meaning all 3GB was used) is an indicator of big transactions, which may be saturating your replication bandwidth.

Also, if they take 20-30 minutes to replicate when reinitialized, that's a combination of their size and your free bandwidth.

So, the general fixes are:

• Increase the bandwidth

• Reduce the amount of data

  • Use the smallest integer based keys you can, and simply replicate lookup tables with descriptions, rather than have descriptions in your tables.

    • BIT when possible, then TINYINT, then SMALLINT, then INT, then BIGINT where you're forced to.

  • Replicate indexed views instead of actual tables (publisher = indexed view, subscriber = table) - send over only the required columns, and only the required rows.

  • Don't use NCHAR/NVARCHAR unless you need to - use CHAR/VARCHAR when you know you only have, or perhaps even should have, no UCS-2 "Unicode" data.

    • Use CHAR when the average size of data is within 2 bytes of the maximum field size, since VARCHAR has a 2 byte overhead to store length

• Reduce the number of transactions on the replicated data

  • Whatever you've got happening that causes all that activity on the replicated tables, analyze it. Is it required? Could it be re-written to minimize the amount of data being replicated?

    • Don't touch the same row more than once, i.e. instead of INSERT, UPDATE, UPDATE, UPDATE, just a single INSERT to the final table - perhaps work in #temp tables before then? And so on and so forth.

  • If they don't absolutely need up to the minute data on any given table, then don't replicate your the core tables that get all this activity.

    • For each non-realtime-required table, create reporting tables on the publisher that get updated only every so often (once a week, once a day, once an hour, whatever), and replicate those.

    • And make sure to carefully use only the UPDATEs, INSERTs, and DELETEs that are required - the goal is to minimize replication load.

    • Don't use MERGE; it has too many issues.

• Split the tables up into multiple publications, so you aren't taking a snapshot of, and reinitializing, every table every time you have to add or remove one.

  • I.e. If you add/drop/alter an article in Publication C, you need to reinitialize and snapshot that publication, but publications A, B, D, E, and F aren't affected by this.

    • And thus it moves less data, which in turn takes less time.

    • And the subscriber retains access to all the articles in all the other publications

    • So split them up along functional usage lines; if tables A, E, and G are used together most of the time, while C and D aren't involved in most of those queries, that's a natural split that will leave your client happier; at least some functions continue unobstructed during re-initialization of either set.

• Increase the bandwidth