You manage an instance of SQL Server 2008 named SQL1. SQL1 contains a database named CorpData that isconfigured for full recovery.
You want to compress the primary filegroup and transaction logs for the CorpData database. What should you do?
A.
Change the recovery model of the database to simple.
B.
Set the database to read-only.
C.
Configure Transparent Data Encryption (TDE) on the database.
D.
Change the recovery model of the database to bulk-logged.
Explanation:
You should set the database to read-only. SQL Server 2008 allows you to mark filegroups as read-only. You canmark any existing filegroup as read-only except the primary filegroup. You can also compress read-onlyfilegroups. To compress data in filegroups, you can only use Windows NTFS compression. You cannot compressthe primary filegroup and transaction logs unless the database itself is configured as read-only. Therefore, to beable to compress the primary filegroup and transaction logs, you should set the database as read-only. You should not change the recovery model of the database to simple or bulk-logged. Recovery models aredesigned for controlling the maintenance of transaction logs. Changing the recovery model of the database willnot allow you to compress the primary filegroup and transaction logs for a database. You should not configure Transparent Data Encryption (TDE) on the database. In SQL Server 2008, TDE is usedto encrypt the contents of an entire database. TDE does not allow you to compress the primary filegroup andtransaction logs for a database.
Objective:
Performing Data Management TasksSub-Objective:
Implement data compression.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Databases > Designing Databases > Designing Files andFilegroups > Read-Only Filegroups and Compression