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

Analyzing the Results

Here are the summarized results, based on the averaged results from the above table:

Database

Operation

CPU Time (ms)

Elapsed Time (ms)

EFS (encrypted)

select * into …

3766 (6%)

4559 (2%)

NON-EFS

3547

4468

EFS (encrypted)

drop table …

16 (0)

68 (16%)

NON-EFS

16

57

EFS (encrypted)

select count(*)

93 (-50%)

4296 (62%)

NON-EFS

140

1604

EFS (encrypted)

update tc…

141 (-77%)

4861 (61%)

NON-EFS

250

1902

EFS (encrypted)

delete from …

234 (19%)

4281 (60%)

NON-EFS

188

1673

EFS (encrypted)

create index…

5078 (-3%)

12452 (32%)

NON-EFS

5250

8430

EFS (encrypted)

select …

0 (0)

28 (35%)

NON-EFS

0

18

The penalty for EFS is obvious, and therefore must be careful considered by the DBA in terms of their over-arching security framework. The figures for CPU time is somewhat strange and at times I found it to produce inconsistent figures. Elapsed time, on the other hand, seemed to be accurate.

The DBA should remember that EFS not only has a place in terms of database file security, but is equally important for database backup directories (which are naturally un-encrypted). A SLA may dictate a specific level of security for all backups which is very important when tapes are taken off-site or placed onto remote servers (duplexing). Again, given the figures above though, take care with dumping large transaction logs (although this architecture is somewhat different from the operations performed in the testing above).

At the end of the day, be very careful with EFS. It is an advanced option and having read a variety of news-group posts regarding recovery of servers and associated tape backups from EFS files, the process is far from simple. Take time to understand the EFS architecture via Microsoft Support and MSDN if you decide to use it, as this is critical in the wide gamete of recovery scenarios.

]]>

Leave a comment

Your email address will not be published.