How Using the Windows 2000 Encrypted File System to Secure SQL Server Databases and Backups Affects SQL Server's Performance

Chris is the author of the book, SQL Server for the Oracle DBA

Those running Windows 2000 can take advantage of the EFS (encrypted file system) for encrypting SQL Server database and/or backup files. In some organizations, encrypting data is mandatory, and as you might expect, it can exact a performance penalty. In this article, I will take a brief look at how you set up encryption for SQL Server and Windows 2000, and then run some tests to see how it affects SQL Server’s performance.

How to Turn on Encryption for SQL Server Databases and Backups

To turn on the encrypted file system in Windows 2000 for use by SQL Server:

  • Shutdown your database instance.
  • Login with the service account the SQL Server instance is using.
  • Select the properties of the folder(s) in which the database files reside using Windows Explorer.
  • Select the advanced option button and follow the prompts to encrypt the files/folders.
  • Re-start the SQL Server service.
  • Verify the successful start-up of the instance and databases affected via the encryption (or create databases after the fact over the encrypted directories).
  • Verify encryption of the database files via cipher.exe.

If you attempt to start the service as any user other than the user that encrypted the database data files, the instance will not start and/or your database will be set to suspect.

Here is an example of SQL Server error log entry showing what happens if you start the service with an account other than the one used to encrypt the files:

udopen: Operating system error 5(error not found) during the creation/opening of physical device E:cktempefsefs_Data.MDF.

FCB::Open failed: Could not open device E:cktempefsefs_Data.MDF for virtual device number (VDN) 1.

udopen: Operating system error 5(error not found) during the creation/opening of physical device E:cktempefsefs_Log.LDF.

FCB::Open failed: Could not open device E:cktempefsefs_Log.LDF for virtual device number (VDN) 2.

As you might expect, encrypting your SQL Server data negatively affects SQL Server’s performance. How much it will affect speed is a tough to measure as there are so many factors to consider. For example:

  • Hardware vendor chosen
  • I/O drives and OS version and patch level
  • Disk vendor, speed, cache
  • RAID levels, channels, disk interconnect
  • RAID controller type, cache, vendor
  • Software-based caching models and/or BIOS patches
  • OS configuration parameters, service packs and registry “tweaks”

and perhaps more, can all affect SQL Server’s encrypted performance.

Testing How Windows 2000 Encryption Affects SQL Server’s Performance

The testing of EFS performance using SQL Server database files was done on the following hardware:

HP NetServer, Dual PIII 850Mhz, 512Mb RAM, SCSI 160 RAID-1 18Gb Disks

The tests were repeated five times, with the average figures shown below. No other user or application was running during the tests. The databases were identical in terms of their size, recovery model, collation and other basic settings.

Before beginning the tests, I tested to be sure that encryption was working. This can be done by typing the following at the command line:

cipher

The results of this command were:

Listing d:
New files added to this directory will be encrypted.

E EFSTEST_Data.MDF
E EFSTEST_Log.LDF

The results above indicate that encryption is on and that there are two existing files that are encrypted.

Next, from within Query Analyzer, I set I/O time and statistics to on, as follows:

set statistics io on
set statistics time on

And before each test, I ran the following code in order to clear out the memory buffer and to force a checkpoint.

dbcc dropcleanbuffers
checkpoint

Continues…

0 Replies to “How Using the Windows 2000 Encrypted File System to Secure SQL Server Databases and Backups Affects SQL Server's Performance

  1. !!!Azdmnzbzxd says:
    Your comment is awaiting moderation. This is a preview, your comment will be visible after it has been approved.

    pgpdgmdsg1fqhcof
    https://booksthroughbarsnyc.org/contact/?contact-form-id=244&contact-form-sent=232039&contact-form-hash=f087996bb230865d056ff9a6238a3956903d7ab7&_wpnonce=860cd39ea6
    https://www.prosportsnow.com/forums/showthread.php?p=19338#post19338
    http://catscoin.online/index.php/topic,8497.new.html#new
    http://www.allaboutmoms.net/forums/topic/jgdghojd/
    https://e-learning6.webnode.ru/forum/
    http://www.ourcries.org/forums/showthread.php?tid=22840
    http://bbs.51jinqu.com/forum.php?mod=viewthread&tid=16047&extra=
    https://o-mundo-e-meu-mundo.webnode.com/forum/
    https://comunagalbinasi.webnode.com/forum/
    https://mvt.com.mx/asaltan-y-golpean-a-joven-en-centro-de-toluca/?unapproved=720106&moderation-hash=26734b476b97614642e8cb79b4a68f73#comment-720106
    https://sexy-x-rated.us/showthread.php?tid=44353&pid=81401#pid81401
    http://diplomrus.ru/raboti/84260?new
    http://foruml.fearnode.net/general-discussion/900427608/gurguazs
    https://inject0r.me/viewtopic.php?f=5&t=63559
    https://www.astrade.es/phpBB3/viewtopic.php?f=8&t=131349&sid=be1a2658f7512da403eeafccf29a87ba
    http://forumw.fearnode.net/general-discussion/900427610/wwwdzsoq
    https://e-learning6.webnode.ru/forum/
    https://o-mundo-e-meu-mundo.webnode.com/forum/
    http://chatterscoop.com/2015/09/21/annoying-people-you-meet-at-your-first-job-and-how-to-deal-with-them/#comment-152468
    http://www.samaritergrosswangen.ch/fotos/ausfluege/dsc02948/?unapproved=135619&moderation-hash=c34d9b49462aead6433182949d4c878c#comment-135619
    https://tvsam.org/forum/the-100/mhpkkler/
    http://forumd.fearnode.net/general-discussion/900427614/cadynamc
    https://seckin-products.webnode.com.tr/forum/
    https://techland.me/forum/showthread.php?tid=68138
    http://forumd.fearnode.net/general-discussion/900427615/vnxlthbs
    http://fargoal.com/forums/viewtopic.php?f=3&t=442838
    https://forum.osdm.xyz/viewtopic.php?f=16&t=17559
    https://forum.iq-provision.ru/showthread.php?tid=59650
    https://dubaiescorts24forum.com/viewtopic.php?f=5&t=13248
    http://chungchitinhoc.net/showthread.php?tid=4626&pid=6525#pid6525
    https://forum.streamclix.com/punbb/viewtopic.php?pid=27512#p27512
    http://forum.sailorstation.com/bb/viewtopic.php?f=20&t=433912
    http://electericneumann.com/forum/thread-28752.html
    https://forum.iq-provision.ru/showthread.php?tid=59651
    http://sqzhcp.top/forum.php?mod=viewthread&tid=2572608&extra=
    http://forum.sailorstation.com/bb/viewtopic.php?f=20&t=433913
    http://forum.webdiplomacy.it/viewtopic.php?f=67&t=320224
    http://hubforum.club/showthread.php?tid=22993
    https://cobva.com/beachpartnerpage/showthread.php?tid=165699
    http://163.32.94.234/artcms/forum/viewtopic.php?pid=982270#p982270
    https://coinping.co/btc/viewtopic.php?pid=157700#p157700
    https://forum.tsukionline.com/index.php?/topic/89099-%D0%B4%D0%B8%D1%82%D1%8F-%D0%BF%D0%BE%D0%B3%D0%BE%D0%B4%D1%8B-%D0%BD%D0%B0-%D1%80%D1%83%D1%81%D1%81%D0%BA%D0%BE%D0%BC-%D1%81%D0%BC%D0%BE%D1%82%D1%80%D0%B5%D1%82%D1%8C/
    http://bbs.51jinqu.com/forum.php?mod=viewthread&tid=15454&extra=
    http://atheistsecularhumanist.org/agora/viewtopic.php?f=2&t=117138
    http://galb.soudi-rawhaniate.com/viewtopic.php?f=17&t=8598
    https://forum.mukhronus.com/thread-635178.html
    https://cobva.com/beachpartnerpage/showthread.php?tid=165700
    https://coinping.co/btc/viewtopic.php?pid=157702#p157702
    http://forum.l2-aquarius.ru/index.php?/topic/177531-%D0%BC%D1%83%D0%BB%D1%8C%D1%82%D1%84%D0%B8%D0%BB%D1%8C%D0%BC-%D0%B4%D0%B8%D1%82%D1%8F-%D0%BF%D0%BE%D0%B3%D0%BE%D0%B4%D1%8B-2019-full-hd/
    http://forum.ornisoft.com/viewtopic.php?f=4&t=379832&p=436232#p436232
    https://coinping.co/btc/viewtopic.php?pid=157703#p157703
    http://dd.muquyennang.vn/showthread.php?35007-%D0%A0%D0%8E%D0%A0%D1%98%D0%A0%D1%95%D0%A1%E2%80%9A%D0%A1%D0%82%D0%A0%C2%B5%D0%A1%E2%80%9A%D0%A1%D0%8A-%D0%A0%D1%95%D0%A0%D0%85%D0%A0%C2%BB%D0%A0%C2%B0%D0%A0%E2%84%96%D0%A0%D0%85-%D0%A0%E2%80%9D%D0%A0%D1%91%D0%A1%E2%80%9A%D0%A1%D0%8F-%D0%A0%D1%97%D0%A0%D1%95%D0%A0%D1%96%D0%A0%D1%95%D0%A0%D2%91%D0%A1%E2%80%B9-Tenki-no-ko-(2019)-%D0%A0%D0%86-%D0%A1%E2%80%A6%D0%A0%D1%95%D0%A1%D0%82%D0%A0%D1%95%D0%A1%E2%82%AC%D0%A0%C2%B5%D0%A0%D1%98-HD-720-p-%D0%A0%D1%94%D0%A0%C2%B0%D0%A1%E2%80%A1%D0%A0%C2%B5%D0%A1%D0%83%D0%A1%E2%80%9A%D0%A0%D0%86%D0%A0%C2%B5&p=52865#post52865
    https://forum.mukhronus.com/thread-635181.html
    http://bbs.jh1.net/forum.php?mod=viewthread&tid=24099&extra=
    http://thietkeyenphu.com.vn/4rum/viewtopic.php?f=2&t=95708
    https://forumkbrs.com/viewtopic.php?f=26&t=40680
    http://www.mcempire.net/forum/viewtopic.php?f=13&t=131107
    http://www.poultry.space/viewtopic.php?pid=385327#p385327
    http://zhangfengya.cn/forum.php?mod=viewthread&tid=1207461&extra=
    http://stecoin.net/showthread.php?tid=209358
    http://alduara.shinefish.com.tw/viewthread.php?tid=479295&extra=
    https://kqra.com/showthread.php?tid=6843&pid=40403#pid40403
    http://kicme.kz/index.php?option=com_kunena&view=topic&catid=4&id=110500&Itemid=194#125753
    http://intersavegame.co.uk/phpBB/viewtopic.php?f=3&t=912830
    http://forum.phakp.com/showthread.php?tid=14051
    http://forum.mobilew88.com/viewtopic.php?pid=200348#p200348
    https://forums.cuahsi.org/viewtopic.php?f=3&t=167568
    http://nocnekino.pl/showthread.php?tid=11979
    http://juggernautgamestudios.com/showthread.php?tid=3301&pid=18420#pid18420
    http://www.onpflegeforum.de/post/2528299/#p2528299
    https://forums.fpsgoons.org/viewtopic.php?f=4&t=62365
    http://labor-economics.org/forum/viewtopic.php?f=5&t=1371366
    http://pistonpowercarclub.co.uk/index.php?topic=8713.new#new
    http://www.greatwallowners.com/viewtopic.php?f=5&t=205589
    http://forum.weightlosslottery.com/thread-304156.html
    http://motosiklet.xyz/showthread.php?tid=35374&pid=295726#pid295726
    http://prozon.org.pl/forum/viewtopic.php?pid=38249#p38249
    https://inject0r.me/viewtopic.php?f=5&t=62223
    https://forum.konsole.space/index.php?/topic/8510-%D0%B0%D0%BD%D0%B8%D0%BC%D0%B5-%D1%84%D0%B8%D0%BB%D1%8C%D0%BC-%D0%B4%D0%B8%D1%82%D1%8F-%D0%BF%D0%BE%D0%B3%D0%BE%D0%B4%D1%8B/
    http://nsu-club.com/forum/viewtopic.php?f=10&t=1450133
    http://hker.live/viewtopic.php?f=4&t=18095
    http://gamingsiden.dk/viewtopic.php?f=7&t=111525
    http://patriotika.wezom.net/forum/viewtopic.php?pid=209803#p209803
    http://patriotika.wezom.net/forum/viewtopic.php?pid=209804#p209804
    https://forum.streamclix.com/punbb/viewtopic.php?pid=27513#p27513
    https://realm.se/board/showthread.php?tid=835267
    https://juggernautgamestudios.com/showthread.php?tid=3296&pid=18421#pid18421
    http://sat-plus.net/forum/index.php?topic=61010.new#new
    https://insigniagsdrivers.co.uk/showthread.php?tid=50092
    https://kqra.com/showthread.php?tid=2860&pid=40406#pid40406
    https://itells.ru/viewtopic.php?pid=9492#p9492
    http://mastersofwords.com/viewtopic.php?f=7&t=241379
    https://higginsgaming.com/viewtopic.php?f=8&t=187839
    https://jokerslotpulsa.xyz/viewtopic.php?f=13&t=110714
    http://jfkrfkmurdersolved.com/forum/viewtopic.php?f=6&t=21839
    http://l2overgame.com/forum/showthread.php?tid=82504
    https://newkek.com/viewtopic.php?f=12&t=14834
    http://mrssodhi.com/discussions/showthread.php?tid=149821
    https://itells.ru/viewtopic.php?pid=9493#p9493
    https://www.klucze.co.uk/forum1/Upload/showthread.php?tid=370770

Leave a Reply

Your email address will not be published. Required fields are marked *