1

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

Improve this question
2
  • Encryption - what encryption TDE, column level ?
    – Kin Shah
    Commented May 4, 2015 at 12:10
  • @Kin, per the requirements we've been asked to encrypt at database level like files and backups , so considering that we are looking to go with TDE
    – KASQLDBA
    Commented May 4, 2015 at 12:14

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • Thanks @Kin, that's helpful. However, is it a good practise to set up distributor on the same server as publisher? Not sure if its going to eat lot of resources as we have about 150 + users on server A and max 15-20 on server B? May be i am thinking other way around, but need to be sure :)
    – KASQLDBA
    Commented May 4, 2015 at 14:46
  • Depending on how active your publisher is going to be and how much data you are replicating, you should see a possibility of separating distribution on its own server (or instance) <-- this is best practice. In my company, we do a lot of replication and depending on how busy is our publication, we separate distribution database on its own server and in some cases, we have it on the same server as Publisher database. Taking that into consideration, make sure Pub and dist are on same versions of sql server.
    – Kin Shah
    Commented May 4, 2015 at 15:01
  • @kin, +1 for better understood publisher & distributor server.
    – Md Haidar Ali Khan
    Commented Aug 13, 2015 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.