question

WilliamD avatar image
WilliamD asked

Data replication and encryption considerations/suggestions

I have a project that has been on the backburner for a long time now, which has been moved to the frontburner. We have planned to scale out our database system to reduce network latency issues for our branch offices across the globe (11 locations, some with awful network connections). The idea being to initially setup a readonly transactional replication, initialised from a backup, so that we can get an idea of the overhead and data traffic that will be created. This would also allow us to offload some of the read intensive semi-OLAP style connections. The next step would be to "convert" the replication into a peer-to-peer replication allowing a full scale out for each branch office. We are concerned about data security, both physical server security (only solved by using locks etc.) and logical data security (at rest and in-memory). I am looking at implementing TDE or Bitlocker on the servers so that the data at rest is safe. Which of the two would be better in this case, or is there a different and better solution? Is there a way of making sure that in-memory data is also safe? As I understand it, Bitlocker and TDE work in a similar way and only encrypt data that is written to disk, not memory resident data. Do Bitlocker and TDE play nicely with Replication (I know I have to set it up per subscriber)? Does anyone have any experience with either technology? Any tips/pointers greatly appreciated!
sql-server-2008replicationencryptiontdebitlocker
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
I am running Bitlocker with Windows Server 2008 R2 and SQL Server 2008 R2 and it is completely transparent. It will have no impact on the replication functionality other than the encryption overhead for disk IO. Bitlocker won't cause SQL Server to encrypt its backups like TDE will, and it will also allow you to run an unencrypted tempdb if you put it on a non-bitlocker volume. I have not played with TDE personally but, because of these two considerations, I would lean towards TDE as it won't allow you or somebody else to overlook encryption for these related places your data might be stored. For memory encryption, I do not believe you can encrypt data in SQL Server's memory without implementing the encryption in your application layer so SQL only gets to play with pre-encrypted data. Also, although not typically recommended, you can use bitlocker to encrypt the volume with your page file to protect any data that might get paged to disk. If you take this route, you might use a hybrid bitlocker/TDE approach.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Great answer @KenJ (+1) - I was afraid that memory encryption wouldn't be on the cards. I think we may lean towards both Bitlocker and TDE to cover both database and fileserver encryption, especially as the system databases cannot be TDEd.
0 Likes 0 ·
Tim avatar image
Tim answered
I have used Bitlocker as well with no issues with applications communicating with it.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.