Prior to completing each task in the following practices, list the steps you would take to accomplish the task. After completing the task, assess how accurately you predicted the nec-essary steps.
■ Practice 1 Encrypt the new database on the ALTERNATE instance of SQL-A by using TDE.
■ Practice 2 Add a second filegroup and files to the new custom database. Create a partitioned table that uses the primary and second filegroup.
Answers CHAPTER 3 149
Answers
This section contains the answers to the lesson review questions and solutions to the case scenarios in this chapter.
Lesson 1
1. Correct Answer: D
A. Incorrect. You cannot use SQL Server Management Studio to give a user access to Integration Services.
B. Incorrect. You cannot use SQL Server Configuration Manager to give a user access to Integration Services.
C. Incorrect. SQL Server Data Tools enables you to create Analysis Services, Integration Services, and Report Server projects, but it cannot be used to give a user access to Integration Services.
D. Correct. Dcomcnfg.exe is the tool you can use to give non-administrative users access to Integration Services.
2. Correct Answer: B
A. Incorrect. You cannot use SQL Server Management Studio to change the Reporting Services execution account.
B. Correct. You use Reporting Services Configuration Manager to change the Reporting Services execution account.
C. Incorrect. You cannot use SQL Server Configuration Manager to change the Reporting Services execution account.
D. Incorrect. You cannot use SQL Server Installation Center to change the Reporting Services execution account.
3. Correct Answer: A
A. Correct. The maximum number of full-text indexes for a table is 1.
B. Incorrect. The maximum number of full-text indexes for a table is 1. You cannot have 32 full-text indexes for a partitioned table.
C. Incorrect. The maximum number of full-text indexes for a table is 1. You cannot have 1,024 full-text indexes for a partitioned table.
D. Incorrect. The maximum number of full-text indexes for a table is 1. You cannot have 2,048 full-text indexes for a partitioned table.
4. Correct Answers: A and C
A. Correct. To enable FILESTREAM, you must edit the properties of the SQL Server service in SQL Server Configuration Manager.
B. Incorrect. You do not have to edit the properties of the Analysis Services service in SQL Server Configuration Manager to enable FILESTREAM.
C. Correct. You must run the sp_configure filestream_access_level, X, with a non-zero value of X to enable FILESTREAM.
D. Incorrect. Running sp_configure filestream_access_level, 0, disables FILESTREAM.
Lesson 2
1. Correct Answer: A
A. Correct. You can add a filegroup to an existing database by using the ALTER DATABASE statement.
B. Incorrect. The CREATE DATABASE statement enables you to create a new database but not to alter an existing database.
C. Incorrect. The ALTER TABLE statement enables you to modify a table but not a database.
D. Incorrect. The CREATE TABLE statement enables you to create a table but not to modify a database.
2. Correct Answer: B
A. Incorrect. Implementing page-level compression will provide a greater reduction in the amount of space used than implementing row-level compression will, given the properties of the table.
B. Correct. Unless the table data is unique, page-level compression provides the greatest compression but has a cost in CPU usage.
C. Incorrect. In this case, compressing the index using row compression will not pro-vide the space savings that compressing the entire table will.
D. Incorrect. In this case, compressing the index using page compression will not provide the space savings that compressing the entire table will.
3. Correct Answers: A, B, and C
A. Correct. You must create a master encryption key prior to enabling TDE on a database.
B. Correct. You must create a certificate prior to enabling TDE on a database.
C. Correct. You must create a database encryption key prior to enabling TDE on a database.
D. Incorrect. You do not need to enable page-level compression prior to enabling TDE on a database.
4. Correct Answer: B
A. Incorrect. DBCC CHECKFILEGROUP verifies the allocation and structural integrity of indexed views and tables for a specific filegroup but not for the entire database.
B. Correct. DBCC CHECKDB checks the physical and logical integrity of all objects within a specific database.
Answers CHAPTER 3 151 C. Incorrect. DBCC SQLPERF provides transaction log space statistics.
D. Incorrect. DBCC SHRINKDATABASE shrinks the size of all data and log files for the specified database.
Case Scenario 1
1. To enable FILESTREAM on the default instance of server SYDNEY-DB, you must per-form the following general steps:
A. Edit the properties of the SQL Server Service to enable FILESTREAM.
B. Run the sp_configure filestream_access_level, X, stored procedure, where X is 1 or 2.
C. Restart SQL Server Services.
D. Create a FILESTREAM filegroup.
E. Add a file to the FILESTREAM filegroup.
2. After FILESTREAM is enabled on the default instance of server SYDNEY-DB, you must take the following general steps to deploy a FileTable:
A. Enable Non-Transactional Access at the database level.
B. Specify a directory for FileTables at the database level.
C. Create a table as a FileTable.
Case Scenario 2
1. Use the following query to create a master encryption key with the password P@ ssw0rd:
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO
2. Use the following query to create the certificate ServerCertA with the subject name
‘Server Certificate A’:
CREATE CERTIFICATE ServerCertA WITH SUBJECT = 'Server Certificate A';
GO
3. Use the following query to create a DEK for database ContosoCars2012 by using the AES_128 encryption algorithm and certificate ServerCertA:
USE ContosoCars2012;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCertA;
GO
4. Use the following statement to encrypt the ContosoCars2012 database:
ALTER DATABASE ContosoCars2012 SET ENCRYPTION ON;
GO
5. Use the following statement to back up the server certificate to a file named CertExport with a private key file named PrivateKey with the password P@ssw0rd:
BACKUP CERTIFICATE ServerCertA TO FILE = 'CertExport'
WITH PRIVATE KEY (
FILE = 'PrivateKeyFile',
ENCRYPTION BY PASSWORD = 'P@ssw0rd' );
GO
153
C H A P T E R 4