SQL Server 2014 to 2012 – Transactional Replication Setup

replicationsql-server-2012transactional-replicationtransparent-data-encryption

We need to set up transactional replication between two servers A and B, configs listed below:

Server A (Publisher)

Microsoft SQL Server 2014 – 12.0.2254.0 (X64) Enterprise Edition (64-bit)
Single database of approx 1.5 TB( this would be the database to be replicated on server B
CPU – 4 Intel Xeon CPU E7-8893 v2 @ 3.4GHZ

Server B (Subscriber & Distributor)

Microsoft SQL Server 2012 – 11.0.5058.0 (X64) Standard Edition (64-bit)
CPU – 1 AMD Operon 6378 processor 64 bit 2.40 GHz (4 Core) (more RAM and better CPU will be added when moved to better Hyper V server)

I am new to the replication concept so would like you're expertise to confirm:

  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?

  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?

Thanks in advance

Best Answer

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.

Related Question