locked
Test disk encryption performance on SQL 2008 R2 RRS feed

  • Question

  • Hi

    Im testing disk encryption and I would like to to see the performance with SQL Server 2008 R2.

    I have encrypted the primary disk using TruCrypt.

    What is the best way to test SQL to see how it perfoms on the encrypted disk?  Is there are script that I can run on a restored database? Is there a built in tool that I can use specifically to test performance?

    Thanks

    Marc Collins


    Marc Collins www.QGate.co.uk

    • Moved by Papy Normand Thursday, November 14, 2013 9:41 PM Coming from SQL Server Data Access forum : SQL Server Security seems a best forum
    Thursday, November 14, 2013 10:51 AM

Answers

  • Note that TDE or disk encryption will affect some operations more than others.  If you have a small OLTP database, most operations on the database are performed in memory.  Encryption will cost on startup, and committing transactions.  Checkpoint and Lazy Writer will be impacted too, but these are background operations.

    If you have a large database and/or small amounts of memory and your workload requires lots of IO, then the cost of encryption will be much higher.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, November 18, 2013 4:31 PM
  • I habve heard of other people using TrueCrypt with SQL Server and having very little performance drops

    If you want to measure it for yourself, the usual methods apply.

    i.e. You can use PerfMon disk counters and SQL Server counters to measure throughput before & after


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Marked as answer by Marc J Collins Monday, November 18, 2013 3:58 PM
    Monday, November 18, 2013 3:33 PM

All replies

  • Hello ,

    According http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx , it is possible to use disk encryption with any edition only if you provide a key.

    But the transparent database encryption is only available for the Datacenter and Entreprise edition.

    Anyway , as your thread is not related to SQL Server Data Access , I will move it towards the SQL Server Security forum which , according to me , is one of the more appropriate forum. I hope you will get a full and quick answer.

    Some links which could help you :

    http://msdn.microsoft.com/en-us/library/bb669072(v=vs.110).aspx

    http://msdn.microsoft.com/en-us/library/ms189586(v=sql.105).aspx

    What I don't know whether it is possible to install SQL Server and to create databases on disks encrypted by another provider different from Microsoft.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    • Proposed as answer by Sofiya Li Friday, November 15, 2013 6:19 AM
    Thursday, November 14, 2013 9:40 PM
  • I habve heard of other people using TrueCrypt with SQL Server and having very little performance drops

    If you want to measure it for yourself, the usual methods apply.

    i.e. You can use PerfMon disk counters and SQL Server counters to measure throughput before & after


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Marked as answer by Marc J Collins Monday, November 18, 2013 3:58 PM
    Monday, November 18, 2013 3:33 PM
  • Hi Andreas

    I do not need exact measurements on perfomance.  All I need to do at this point is to make sure that there isn't any major performance hits when using SQL.  Unfortunately due to time contraints, I will not be able to check before and after.

    If you say that there is little performance drop then this will be enough for me to proceed.

    Thanks


    Marc Collins www.QGate.co.uk

    Monday, November 18, 2013 3:58 PM
  • I understand

    Yes, it's probably comparable to TDE, which is usually around 5% performance drop in terms of mainly CPU


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, November 18, 2013 4:05 PM
  • Note that TDE or disk encryption will affect some operations more than others.  If you have a small OLTP database, most operations on the database are performed in memory.  Encryption will cost on startup, and committing transactions.  Checkpoint and Lazy Writer will be impacted too, but these are background operations.

    If you have a large database and/or small amounts of memory and your workload requires lots of IO, then the cost of encryption will be much higher.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, November 18, 2013 4:31 PM