Auto-Shrink, Encryption & Recovery Model properties in SQL Server 2008 R2

Question

I am creating a new database in SQL Server 2008 Express R2. In the New Database dialog, I could see an option to set the Recovery Model, which is by default set to: Simple. If I set it to Full, what will happen?

I also see properties like: Auto-Shrink and Encryption. I checked online help for Auto-Shrink property where it shows information about the Compact Edition. But my database will reside on the Server and it is not a compact db. Should I enable it? What are the advantages of enabling it on a server-class database?

I also want to use the Encryption property but I worry that it might make the database unreadable if not handled correctly.

Please throw some light on these.

Answer 1

The simple recovery model means that you don't have the option of point in time recovery - essentially, if you take a full backup of the database each day, any data you enter after the backup might be lost if there was a problem. You'd restore to how the data was in the last good backup. As long as your data doesn't change that much between full backups, this is the simplest solution.

Full recovery means you also need to take regular transaction log backups as well - it allows you to restore to a point in time if there is a problem. Typically, you might take a daily full backup of the databases, and transaction log backups every hour. The problem is that if the transaction log backups fail, the transaction logs themselves will grow - and fill the discs if it's not fixed.

Auto-shrink is a setting where SQL Server will trying to shrink the database files when it is idle. It causes performance havoc, and will be removed in a future SQL Server release. Don't use it! (gbn added bold!)

Encryption is a bit more complicated, and best left alone unless you really understand all that is involved IMHO.

Answer 2

Enabling encryption allows your DB to use TDE (Transparent Data Encryption). This mechanism has been implemented in SQL 2008 onwards and provides better overall performance than previous encryption methods. TDE slightly increases CPU and IO usage, so it should be properly tested for systems that already use a lot of CPU and IO. There is a trade off between performance and security.

Using this encryption also involves the setup of security objects, and these objects will have to be properly backed up and retained. So loosing these security objects will pose a problem when you need to revert your encryption.

Full/Simple mode has been answered by Peter Schofield and gbn has pointed out with a link on what to (or not to) do about auto-shrink.

As for the .mdf and .ldf files, they will be working fine until something goes wrong, and that is when you will really need backups. If there are corruptions in your allocation pages, you will have problems when trying to reattach your DB. I would suggest doing a backup and restore rather than detaching and attaching. Since you don't want point-in-time recovery, you will need full backups.