matilda16


We have an encrypted drive (that can be mounted and dismounted, a third party tool to encrypt drive path). I wanted to store the secondary file to that encrypted drive path. The secondary file stores confidential information. I separated the table from the primary to secondary file. Encryption per column is not advisable to do on that table so we decided to separate that table and put it on secondary filegroup. The physical file is stored in the mounted drive path.

I can read and write in that mounted drive path. I can also read and write if the drive is unmounted (which I believe read and write is really being done). When the drive is unmounted, the physical secondary file (.ndf) is not visible to any user logging in the server itself (this is actually the goal why we do this encrypted drive setup thing). It is kept virtually somewhere in the machine. To mount it back, a password is needed.

I'm a bit confuse, somebody can advise or give their insight on this setup. I believe that when the drive is dismounted, SQL Server stored the transactions in cache until it finds that the drive is mounted back. This means that all transactions are not comitted yet. When the drive is mounted back, I think SQL Server is smart enough to check/know that the drive is physically present and will flash all the pending transaction from the cache to the hard drive.

Is my assumption correct Is there any thing that I need to know about transaction, committed and those data flashing thing on the hard drive

Thanks in advance....




Re: Secondary file on encrypted drive path


Re: Secondary file on encrypted drive path

Raul Garcia - MS

I asked the experts in the subject, and they told me that the update succeeds since the data is already cached in memory. SQL server won¡¯t try to write it until the next checkpoint. The transaction data is committed in the log.

BTW. If you want to protect the data in disk (data at rest) you should also protect the log files.

I hope this information helps.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine







Re: Secondary file on encrypted drive path

matilda16

Thanks for sharing this idea Raul Smile