locked
Test disk encryption performance on SQL 2008 R2 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    3
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    3
    Sign in to vote

    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