You are the database administrator of your company. You manage an instance of SQL Server 2008 that stores adatabase named UserData.
UserData is configured with the full recovery model and contains a large amount ofstatic data.
To minimize the amount of space required for the data, you create a new read-only filegroup named UserDataFileGrp and move the data to that filegroup.
You also compress the UserDataFileGrp filegroup using NTFS compression.
After several days, you receive a request to update the data for one of the users in the UserData database.
Toachieve this, you want to modify the compressed data stored in UserDataFileGrp.
Which action or actions must you perform to be able to complete this task using minimum administrative effort?
(Choose all that apply. Each correct answer represents part of the solution.)
A.
Uncompress the .ndf files for the UserDataFileGrp filegroup.
B.
Move the data to the primary filegroup of the UserData database.
C.
Set the UserDataFileGrp filegroup to read/write.
D.
Set the recovery model of the UserData database to simple.
Explanation:
You should uncompress the .ndf files for the UserDataFileGrp filegroup and set the UserDataFileGrp filegroupto read/write. SQL Server 2008 allows you to mark filegroups as read-only. You can mark any existing filegroupas read-only except the primary filegroup. You can also compress read-only filegroups. Compressing data storedin read-only filegroups is useful when you have a large amount of historical or static data that must be available tousers for limited read-only access or when you want to save disk space. To compress data in read-onlyfilegroups, only Windows NTFS compression can be used. When you mark a filegroup as read-only, it cannot bemodified. To be able to modify data in a compressed read-only filegroup, you must ensure that the files areuncompressed and the filegroup is set to read/write. You should not move the data to the primary filegroup of the UserData database. Although this will allow you tomodify the data, it will require a considerable amount of administrative effort. You should not set the recovery model of the UserData database to simple. Setting the recovery model of the UserData database to simple will not be of any use in this scenario. To be able to modify data in a compressedread-only filegroup, you must ensure that the files are uncompressed and the filegroup is set to read/write.
Objective:
Performing Data Management Tasks
Sub-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