Administering Microsoft SQL Server 2012 Interview Questions - Part 2 of 2 (On Applications from TK 70-462)

70-462 Application Questions

Case Scenario 1 (Chapter 1 - Case Scenario 1): Planning Deployment of SQL Server 2012

Your organization is planning to deploy SQL Server 2012 Enterprise (x64) on several servers as a production database solution. Sam works in your organization as a database developer. As part of his role, Sam needs to test all the features that will be available on the production SQL Server 2012 instance. Sam has a laptop computer with Windows 7 Enterprise (x64) installed.

  1. On which operating systems can you install the production database? Answer.

    2. You can install SQL Server 2012 Enterprise (x64) edition on computers running Windows Server 2008 R2 SP1 and Windows Server 2008 SP2 (x64).

    Hide the Answer.

  2. Which edition of SQL Server 2012 should you deploy on Sam’s computer? Answer.

    You should deploy SQL Server 2012 Developer (x64) edition because this has the same features as SQL Server Enterprise (x64) edition, the main difference being licensing and use in production environments.

    Hide the Answer.

Case Scenario 2 (Chapter 1 - Case Scenario 2): SQL Server Deployment

You work for Contoso’s Australian subsidiary. You need to deploy six SQL Server 2012 servers, two each in the cities of Sydney, Perth, and Adelaide, and you must ensure that these servers are configured in the same way.

  1. How much free space should you ensure is available on each server’s operating system disk before you attempt to install SQL Server 2012? Answer.

    2. You should ensure that there is at least 4 GB of available space on the operating system disk prior to attempting to install SQL Server 2012.

    Hide the Answer.

  2. If you were using only the SQL Server installation media, what steps could you take to ensure that the SQL servers in Sydney, Perth, and Adelaide are configured in the same way? Answer.

    You could ensure that the SQL servers in Sydney, Perth, and Adelaide are configured in the same way by generating a configuration file when installing the first server and then using that configuration file to deploy the additional servers.

    Hide the Answer.

Case Scenario 3 (Chapter 2 - Case Scenario 1): Instance Configuration

You are preparing a server running Windows Server 2008 R2 on which you have installed three SQL 2012 Database Engine instances. These are the default instance, Instance-A, and Instance-B. The host server has 32 GB of RAM. The host server has eight processor cores. You want to ensure that Instance-A is configured in the following way:

  • Minimum server memory: 512 MB
  • Maximum server memory: 8,192 MB
  • Affinity for CPUs 0 and 1
  • Default fill factor of 95%

With this information in mind, answer the following questions:

  1. What Transact-SQL code should you use to configure the minimum and maximum memory settings when connected to Instance-A in SQL Server Management Studio? Answer.

    2. Use the following Transact-SQL code when connected to Instance-A in SQL Server Management Studio to configure the instance to use a minimum server memory of 512 MB and a maximum server memory of 8192 MB. EXEC sys.sp_configure 'show advanced options', 1;
    GO

    RECONFIGURE;
    GO

    EXEC sys.sp_configure 'min server memory', 512;
    GO

    EXEC sys.sp_configure 'max server memory', 8192;
    GO

    RECONFIGURE;
    GO

    Hide the Answer.

  2. What Transact-SQL code should you use to configure the appropriate processor affinity when connected to Instance-A in SQL Server Management Studio? Answer.

    Use the following Transact-SQL code when connected to Instance-A in SQL Server Management Studio to configure processor affinity for CPU 0 and 1.

    ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,1

    Hide the Answer.

  3. What Transact-SQL code should you use to configure the appropriate fill factor when connected to Instance-A in SQL Server Management Studio? Answer.

    Use the following Transact-SQL code when connected to Instance-A in SQL Server Management Studio to set the default fill factor to 95%.

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO

    sp_configure 'fill factor', 95;
    GO

    RECONFIGURE;
    GO

    Hide the Answer.

Case Scenario 4 (Chapter 2 - Case Scenario 2): Additional Instances and Error Log Cycling

You are updating a computer hosting a SQL Server 2012 instance at a remote branch office. You want to add a new instance to the server and name this instance MELBOURNE. You also want to cycle the SQL Server error log and SQL Server Agent error log manually. With this in mind, answer the following questions:

  1. Which command-line command would you use to add the new instance if you wanted to configure the account ADATUM\sam_abolrous as a SQL Server Administrator? Answer.

    2. Use the following command-line command to install the MELBOURNE instance and set the account ADATUM\sam_abolrous as the SQL Server Administrator:

    Setup.exe /qs /Action=Install /Features=SQLEngine /InstanceName=Melbourne /SQLSYSADMINACCOUNTS="adatum\sam_abolrous" /IAcceptSQLServerLicenseTerms

    Hide the Answer.

  2. Which Transact-SQL statement should you use to cycle the SQL Server Agent error log? Answer.

    You should use the following statement, remembering that the stored procedure needs to be executed from the msdb system database, to cycle the agent error log:

    USE msdb;
    GO
    EXEC dbo.sp_cycle_agent_errorlog;
    GO

    Hide the Answer.

  3. Which Transact-SQL statement should you use to cycle the SQL Server error log? Answer.

    4. You should use the following statement to cycle the SQL Server error log:

    EXEC sp_cycle_errorlog;
    GO

    Hide the Answer.

Case Scenario 5 (Chapter 3 - Case Scenario 1): Configuring FILESTREAM and FileTable

You have recently deployed SQL Server 2012 on a server named SYDNEY-DB. You want to use this server to store a large number of image files, most of which are between 10 MB and 20 MB in size. In view of this goal, you want to configure the default instance on SYDNEY-DB to support FILESTREAM. You also want to configure FileTables to simplify the process of adding image files to the database. With this information in mind, answer the following:

  1. What general steps must you take to enable FILESTREAM on the default instance of server SYDNEY-DB? Answer.

    2. To enable FILESTREAM on the default instance of server SYDNEY-DB, you must per- form the following general steps:

    1. Edit the properties of the SQL Server Service to enable FILESTREAM.

    2. Run the sp_configure filestream_access_level, X, stored procedure, where X is 1 or 2.

    3. Restart SQL Server Services.

    4. Create a FILESTREAM filegroup.

    5. Add a file to the FILESTREAM filegroup.

    Hide the Answer.

  2. After FILESTREAM is enabled on the default instance of server SYDNEY-DB, what general steps must you take to create a FileTable? Answer.

    After FILESTREAM is enabled on the default instance of server SYDNEY-DB, you must take the following general steps to deploy a FileTable:

    1. Enable Non-Transactional Access at the database level.

    2. Specify a directory for FileTables at the database level.

    3. Create a table as a FileTable.

    Hide the Answer.

Case Scenario 6 (Chapter 3 - Case Scenario 2): Deploying Transparent Data Encryption

You want to deploy Transparent Data Encryption (TDE) to protect the ContosoCars2012 database hosted on one of your organization’s SQL Server 2012 Database Engine instances. With that in mind, answer the following questions:

  1. Which query would you use to create a master encryption key with the password P@ ssw0rd? Answer.

    2. USE master;
    GO

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
    GO

    Hide the Answer.

  2. Which query would you use to create the certificate that encrypts the database if the certificate name is ServerCertA and the subject name is Server Certificate A? Answer.

    CREATE CERTIFICATE ServerCertA WITH SUBJECT = 'Server Certificate A';
    GO

    Hide the Answer.

  3. Which query would you use to create a Database Encryption Key (DEK) for the ContosoCars2012 database if you were using the AES_128 encryption algorithm and ServerCertA? Answer.

    USE ContosoCars2012;
    GO

    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128
    ENCRYPTION BY SERVER CERTIFICATE ServerCertA;
    GO

    Hide the Answer.

  4. Which query would you use to encrypt the ContosoCars2012 database? Answer.

    ALTER DATABASE ContosoCars2012
    SET ENCRYPTION ON;
    GO

    Hide the Answer.

  5. Which query would you use to back up the server certificate to a file named CertExport and the private key to a file named PrivateKey with the password P@ssw0rd? Answer.

    BACKUP CERTIFICATE ServerCertA
    TO FILE = 'CertExport'
    WITH PRIVATE KEY
    (

    FILE = 'PrivateKeyFile',

    ENCRYPTION BY PASSWORD = 'P@ssw0rd'

    );
    GO

    Hide the Answer.

Case Scenario 7 (Chapter 4 - Case Scenario 1): Consolidation at Contoso

Contoso has 20 database servers running a mix of SQL Server 2005 and SQL Server 2008. All existing database servers are running x86 versions of SQL Server. Contoso has recently deployed 20 virtual machines that have the x64 version of SQL Server 2012 Enterprise edition in their private cloud. You want to start by deploying databases from 10 instances to an instance hosted in the private cloud used for development work. During the development phase of the project, these databases should also remain hosted on their original parent instances. Each database instance has between 10 and 100 SQL logins, which all use Windows authentication.

With this information in mind, answer the following questions:

  1. What steps should you take to migrate the databases during the development phase of the project? Answer.

    You should copy the databases from the production hosts to the virtual machines in the development private cloud. You could use the backup and restore method of copying the databases or the Copy Database Wizard by using the object model to perform the copy. The key is that your strategy should ensure that the databases on the production server remain online.

    Hide the Answer.

  2. Describe the steps that you would take to migrate the SQL logins from the original instances to the new instances. Answer.

    You should use the Script Login As method of migrating the SQL logins because the problem statement says that all logins use Windows authentication. You might also consider using the script published with Knowledge Base article KB918992, which enables you to automate the migration of both SQL Server–authenticated and Windows-authenticated SQL logins, although in this case, because you don’t have to worry about migrating passwords, the Script Login As method might be simpler. You can also use the Copy Database Wizard to migrate the logins.

    Hide the Answer.

  3. What factors will influence whether you perform a backup and restore method or a detach and attach method when it comes to migrating the databases prior to deploying them in the private cloud environment? Answer.

    A detach operation takes the database offline from its current instance. Although you can make a copy of the files and then reattach on the original instance and the desti- nation instance, using the Backup and Restore method of copying a database ensures that the database remains online on the original host during the migration.

    Hide the Answer.

Case Scenario 8 (Chapter 4 - Case Scenario 2): Tailspin Toys Bulk Data

Tailspin Toys has a number of SQL Server 2012 instances deployed on traditional hardware. It is necessary to export a substantial amount of data from the database on a regular basis. The data is the entire contents of specific tables and must be written to Excel format. It is also necessary on occasion to import a substantial amount of customer data from a specially prepared file into one of the databases.

With this information in mind, answer the following questions.

  1. You have a file that contains customer names, phone numbers, and addresses. Which command-line utility would you use to import this data into a table on a database instance? Answer.

    2. You use the bcp utility to import data into the database from the command line.

    Hide the Answer.

  2. Every week, you must perform a bulk export of data from the database. What steps could you take to automate this process? Answer.

    Use the SQL Server Import and Export Wizard to create an Integration Services package. Schedule the package to execute on a periodic basis.

    Hide the Answer.

  3. You must copy the contents of several large tables to new tables on a different database hosted on the same instance. Which method could you use to accomplish this goal? Answer.

    You could use the SELECT...INTO statement to copy the contents of existing tables quickly into new tables if the new tables are associated with a different database hosted on the same instance.

    Hide the Answer.

Case Scenario 9 (Chapter 5 - Case Scenario 1): Instance-Level Permissions for Contoso’s Accountants

You are the database administrator at Contoso, and you are deploying an instance of SQL Server 2012 that members of the accounting and finance departments will use. All members of the accounting and finance departments have accounts in the Contoso Active Directory domain. There are no accounting or finance-based domain security groups. In the accounting department are 20 users who require access to the SQL Server 2012 instance. These users require specific permissions that differ from those available in the fixed server-level roles. In the next few months, members of the finance department might need similar access and permissions. You want to configure security so that the number of SQL logins is minimized but also so that members of the accounting department do not share a SQL login with members of the finance department. With this in mind, answer the following questions:

  1. What steps should you take to minimize the number of SQL Server logins when granting access to the 20 accounting users? Answer.

    2. Create a domain group and add the 20 users in the accounting department to this group. Create a SQL Server login that uses the Windows authentication type and is mapped to this domain group.

    Hide the Answer.

  2. What approach should you take when granting the custom permissions at the Database Engine instance level? Answer.

    Create a user-defined server role. Grant the custom permissions to this user-defined server role. Add the SQL Server login mapped to the group holding the accounting department user accounts to this user-defined server role. In the future, you will be able to add other logins to this role, such as a login relating to the finance users.

    Hide the Answer.

Case Scenario 10 (Chapter 5 - Case Scenario 2): Contained Databases at Fabrikam

You are the SQL Server 2012 database administrator at Fabrikam, and you are planning a new database. One of the goals in developing this new database is for you to be able to attach and detach the database as necessary from different instances without creating orphaned users. All instances that host the database will be running SQL Server 2012. All users who will access the database have accounts in the Fabrikam Active Directory domain. They should be able to connect to the database without entering a separate password. Each user might require different permissions on the database.

With this information in mind, answer the following questions:

  1. Prior to creating the database, what steps should you take to prepare the instance? Answer.

    You should configure the instance to support contained database authentication. This enables you to attach and detach the database from different instances without creating orphaned users.

    Hide the Answer.

  2. What type of security principal should you configure in the database? Answer.

    You should create contained database users that map to domain-based user accounts.

    Hide the Answer.

Case Scenario 11 (Chapter 6 - Case Scenario 1): Configuring Database Permissions

You are developing permissions for the Production database. This database has two schema, named Accounts and Manufacturing. The database has three roles, named Finance, DataProtection, and Engineering.

  1. You must ensure that principals in the Finance role can add and change data on tables in the Accounts schema. Which permissions must you grant to accomplish this goal? Answer.

    2. They will need SELECT permission to view the data, INSERT permission to add data, and UPDATE permission to modify data.

    Hide the Answer.

  2. Members of the DataProtection role must be able to back up and recover data stored in the Protection database. Which permissions must you assign this role? Answer.

    Backup permission grants permission to back up a securable. Restore permission allows a securable to be recovered from backup.

    Hide the Answer.

  3. Which permission must the Engineering role have on the Manufacturing schema if members of that role must be able to create and drop tables in the schema? Answer.

    They must be assigned the ALTER permission to create and drop tables in the schema.

    Hide the Answer.

Case Scenario 12 (Chapter 6 - Case Scenario 2): Troubleshooting Security

You are auditing the security of a subsidiary company’s SQL Server 2012 databases. A previ- ous database administrator implemented certificates and asymmetric keys for database authentication and authorization. The database administrator has since moved on, and you must determine the properties of certificates and keys, including when certificates will expire, when private keys were last backed up, and the key lengths used when generating asymmet- ric keys. With these requirements in mind, answer the following questions:

  1. Which security catalog view would you query to determine the expiration dates of database certificates? Answer.

    2. You can query the sys.certificates catalog view to determine the expiration date of database certificates.

    Hide the Answer.

  2. Which security catalog view would you query to determine when the private key for a specific certificate was last backed up? Answer.

    You can query the sys.certificates catalog view to determine the most recent date on which a certificate’s private key was backed up.

    Hide the Answer.

  3. Which security catalog view would you query to determine the key length of the asym- metric keys? Answer.

    You can query the sys.asymmetric_keys catalog view to determine the key length of asymmetric keys.

    Hide the Answer.

Case Scenario 13 (Chapter 6 - Case Scenario 3): Auditing at Fabrikam

You are configuring auditing for several databases hosted on the default instance of server SYD-SQL-A. You are working on this configuration with a colleague. With this situation in mind, answer the following questions:

  1. Which permissions do you need to be able to create a database audit specification? Answer.

    2. You need either the ALTER ANY DATABASE AUDIT permission or the ALTER or CONTROL permissions on the database.

    Hide the Answer.

  2. Which step should you take to ensure that both successful and failed audits are logged on the default instance on SYD-SQL-A? Answer.

    Configure Login Auditing at the instance level. Enabling common criteria compliance and c2 audit tracing also accomplishes this goal.

    Hide the Answer.

  3. You want to check how your colleague has configured auditing. Which catalog view would you query to determine which actions are audited at the database level? Answer.

    You can query the sys.database_audit_specifications_details catalog view to learn detailed information about auditing at the database level.

    Hide the Answer.

Case Scenario 14 (Chapter 7 - Case Scenario 1): Database Mirroring at Coho Vineyard

You are the database administrator at Coho Vineyard. You have just migrated the Winery database from a host server running SQL Server 2005 to a new server named Sydney-SQL-A running SQL Server 2012. Your organization has purchased two additional servers, named Sydney-SQL-B and Sydney-SQL-C. You have installed SQL Server 2012 on both of these serv- ers, which will host their own databases, but you will also use them to support the Winery database in a mirrored configuration. You want to configure the mirroring session in the following ways:

  • The default instance on Sydney-SQL-A will serve as the principal instance.
  • The default instance on Sydney-SQL-B will serve as the mirroring instance.
  • The default instance on Sydney-SQL-C will serve as the witness instance.

You have configured the SQL Server service accounts on each instance as follows:

  • The SQL Server Service uses a domain account named cohovineyard\syd-sql-a.
  • The SQL Server Service uses a domain account named cohovineyard\syd-sql-b.
  • The SQL Server Service uses a domain account named cohovineyard\syd-sql-c.

With this information in mind, answer the following questions:

  1. Which logins must you create on instance Sydney-SQL-B? Answer.

    2. You must create logins for accounts cohovineyard\syd-sql-a and cohovineyard\syd-sql-b so that the principal instance and the witness instance can be granted permissions on the endpoint.

    Hide the Answer.

  2. Which mode would you set to ensure that automatic failover can occur if instance Sydney-SQL-A becomes unavailable? Answer.

    You must set high-safety mode with a witness present to enable automatic failover.

    Hide the Answer.

  3. Which Transact-SQL statement would you use to make Sydney-SQL-B the primary instance when you must apply software updates that require restarting server Sydney-SQL-A? Answer.

    Use the ALTER DATABASE Winery SET PARTNER FAILOVER; statement to make the mirror instance the primary instance when applying software updates.

    Hide the Answer.

Case Scenario 15 (Chapter 7 - Case Scenario 2): Database Replication at Tailspin Toys

Tailspin Toys has offices in the cities of Melbourne, Sydney, Perth, Adelaide, Brisbane, and Hobart. Each office has its own SQL Server 2012 Enterprise edition instance. Tailspin Toys has three databases, which must be hosted in each of these cities. These databases have the following properties:

  • Airplanes Is updated frequently at the Melbourne office. Is not updated at any other office. Other offices must stay up to date with the changes made at the Melbourne office.
  • Hovercraft Is updated between 6:00 P.M. and 7:00 P.M. each weekday at the Brisbane office. Is not updated at any other office. Other offices need the updates before start of business the next morning.
  • Multicopters All sites must be able to update this database. If conflicts occur when the same row is updated at different offices, updates applied at the Adelaide office should take precedence.

With this information in mind, answer the following questions:

  1. Which type of replication should you use for the Airplanes database? Answer.

    2. Transactional replication is suitable in this circumstance because updates occur at one location but must propagate quickly to other locations.

    Hide the Answer.

  2. Which type of replication should you use for the Hovercraft database? Answer.

    Snapshot replication is appropriate in this circumstance because updates occur at one location over a brief span of time, and a substantial amount of time elapses before other branch offices must be updated.

    Hide the Answer.

  3. Which type of replication should you use for the Multicopters database? Answer.

    Merge replication is appropriate in this circumstance because each office can update the database, and automatic conflict resolution is required.

    Hide the Answer.

Case Scenario 16 (Chapter 8 - Case Scenario 1): Failover Cluster Instances at Contoso

You are designing a failover cluster instance solution at Contoso. You will deploy a four-node failover cluster at the Melbourne site. You will also deploy a failover cluster instance that has nodes in the cities of Brisbane, Sydney, Adelaide, and Perth. Each of these cities resides on a different TCP/IP subnet.

With these facts in mind, answer the following questions:

  1. Which edition of SQL Server 2012 should you deploy to support the proposed cluster configuration at the Melbourne site? Answer.

    2. You must deploy SQL Server 2012 Enterprise edition to support the proposed cluster configuration because this is the only edition that supports four nodes.

    Hide the Answer.

  2. Which host operating systems can you use to support the proposed cluster configuration at the Brisbane, Sydney, Adelaide, and Perth sites? Answer.

    You must support multi-subnet failover clustering, which requires either Windows Server 2008 R2 Enterprise or Datacenter editions. Windows Server 2008 does not support multi-subnet failover clustering.

    Hide the Answer.

  3. Which tools can you use to perform manual failover when performing maintenance? Answer.

    You can use either the Failover Cluster manager or the Move-ClusterGroup PowerShell cmdlet to perform manual failover.

    Hide the Answer.

Case Scenario 17 (Chapter 8 - Case Scenario 2): AlwaysOn Availability Groups at Fabrikam

You are planning an AlwaysOn Availability Groups deployment at Fabrikam. Fabrikam wants to deploy replica instances in the cities of Sydney, Brisbane, Canberra, and Melbourne. Fabrikam security policy dictates that you must use local virtual accounts rather than domain security accounts for the SQL Server service accounts for each of the replica instances. The Chief Information Officer (CIO) at Fabrikam wants you to configure the AlwaysOn Availability Group so that automatic failover is possible.

With these facts in mind, answer the following questions:

  1. Which editions of Windows Server 2008 R2 could you use to support the proposed configuration? Answer.

    2. You can use the Enterprise or Datacenter editions of Windows Server 2008 R2 to support the proposed configuration.

    Hide the Answer.

  2. What factors influence the choice of authentication method for the mirroring endpoints? Answer.

    Because the SQL Server service uses local accounts, you must use certificate-based authentication for the endpoints.

    Hide the Answer.

  3. Which availability mode should you configure on the primary and secondary replicas, given the project requirements? Answer.

    You must configure the synchronous-commit availability mode, given the requirement for automatic failover.

    Hide the Answer.

Case Scenario 18 (Chapter 9 - Case Scenario 1): Identifying Poor Query Performance

You have recently deployed SQL Server 2012 Enterprise edition to a new server with 128 GB of memory. The Sales application was also upgraded as part of this deployment. The sales team has identified that the performance of the end-of-month report is no longer satisfactory after the upgrade. With this information in mind, answer the following questions:

  1. Which tool would you use to identify the query that this report is using? Answer.

    2. SQL Server Profiler can be used to help identify the slow query or queries executed by the report.

    Hide the Answer.

  2. How would you identify whether the unsatisfactory performance is caused by a missing index? Answer.

    When SQL Server processes a query, the optimizer keeps a record of the indexes it attempts to use to satisfy the query. If these indexes are not found, SQL Server creates a record of the missing index. This information can be viewed by using the sys.dm _ db_missing_index_details DMV.

    Hide the Answer.

Case Scenario 19 (Chapter 9 - Case Scenario 2): Deploying Auditing

You have upgraded the SQL Server for the Payroll database from SQL Server 2008 to SQL Server 2012. Due to new regulatory requirements, you need to audit when any updates are made to the Employee table in the database. The regulation specifies that the audit information must be stored in the Application Event Log and that all events must be audited. With this information in mind, answer the following questions:

  1. Which query would you use to create the server audit? Answer.

    2. CREATE SERVER AUDIT PayrollAudit
    TO APPLICATION_LOG
    WITH (QUEUE_DELAY = 0, ON_FAILURE = SHUTDOWN);
    GO

    Hide the Answer.

  2. How would you enable the server audit? Answer.

    ALTER SERVER AUDIT PayrollAudit
    WITH
    (

    STATE = ON )

    GO

    Hide the Answer.

  3. Which query would you use to audit the Employee table? Answer.

    CREATE DATABASE AUDIT SPECIFICATION EmployeeUpdateAudit
    FOR SERVER AUDIT PayrollAudit
    ADD
    (

    UPDATE ON HumanResources.Employees
    BY PUBLIC

    )
    WITH
    (

    STATE = ON

    )
    GO

    Hide the Answer.

Case Scenario 20 (Chapter 10 - Case Scenario 1): Fabrikam Year-Query Performance Optimized Logging

You are a database administrator for Fabrikam. You are testing a query that will be used for the year-end report. This report performs many aggregate operations such as min, max, avg, sum, and standard deviation. The performance of this query is unacceptable. You will be deploying this query on SQL Server 2012. The table that is involved with the aggregate operations contains 3.5 million rows. It has a clustered index on its primary key column.

There are several queries for which you have captured execution plans due to poor performance. When you examine those plans, you notice that one large table is often causing a table scan, which is the cause of the poor performance. You also determine that a particular query, which is being supported by a non-clustered index, is not very selective. You know that the WHERE clause contains two columns, one of which is the leading column of the nonclustered index. That column by itself is not very selective, but is very selective when combined with the second column. You cannot create an index at this time. A long-term reporting solution is required that will limit the impact on the OLTP system.

  1. Without rewriting the query, which has been tested to ensure that the proper calculations are being made, how could you improve performance of the query? Answer.

    2. Because your application will be deployed on SQL Server 2012, you perform many aggregate operations, and the table contains a large number of rows, you can create a columnstore index on the table to improve query performance for the aggregate operations.

    Hide the Answer.

  2. Which indexes could be added to improve performance? Answer.

    Examine the Missing Indexes DMVs to see whether an appropriate index exists that will remove the table scan from the execution plans.

    Hide the Answer.

  3. How can you improve the multicolumn query performance? Answer.

    Manually create statistics on the two columns. This will enable the optimizer to determine that the combination of the two columns is highly selective and choose a more optimum query plan.

    Hide the Answer.

  4. How can you design a long-term reporting solution? Answer.

    Implement Readable Secondary Replicas and have the reporting application set its connection string to include the ApplicationIntent=ReadOnly clause.

    Hide the Answer.

Case Scenario 21 (Chapter 10 - Case Scenario 2): Analyzing Concurrency at Tailspin Toys

You are a database administrator for Tailspin Toys. Users of your application are reporting that they are waiting significant amounts of time after they submit an order for it to process. During this time, you see that the Marketing team has been running its latest sales promo- tion reports. You examine sys.dm_os_waiting_tasks and sys.dm_exec_sessions and notice long blocking chains. Users are also reporting Error 1205 messages.

You cannot make source code changes at this time. What steps should you take to improve concurrency in the Tailspin Toys database?
  1. The server is under heavy load, and you cannot run a SQL Profiler trace because of this. How can you capture the necessary information to diagnose and correct the deadlocks? Answer.

    2. Because you are experiencing reader-writer blocking, you can set the Read Committed Snapshot Isolation database option to improve concurrency.

    You can enable deadlock information capture to the SQL Server activity log by execut- ing the DBCC TRACEON(1222,-1) command. This captures the information without the overhead of a SQL Server Profiler trace and does not require a service restart such as setting this trace flag as a start-up parameter.

    Hide the Answer.

  2. A maintenance job runs four times per day. Whenever this job runs, every process that needs access to the Status table is blocked until the maintenance job is complete. When the maintenance job is not executing, there are no issues. The maintenance job is reading data and copying it to a history table. Answer.

    The maintenance job might have its transaction set to SERIALIZABLE, effectively blocking all access to the Status table. Because only read access is required, you can remove the TRANSACTION ISOLATION LEVEL SERIALIZABLE statement. This will allow all connections access to the table while the maintenance job is being executed. Also, if correct, you can add a WITH NOLOCK hint to the maintenance job to improve concurrency as long as you are aware that you might get dirty reads.

    Hide the Answer.

Case Scenario 22 (Chapter 11 - Case Scenario 1): SQL Server Agent at Contoso

You are configuring automation at Contoso. Over the next week, a developer will be performing maintenance on the Hovercraft database. You have a number of alerts configured that trigger specific jobs and don’t want these jobs running while maintenance is being performed on the database. You’ve also been investigating running a Windows PowerShell script on a regular basis to perform system maintenance tasks. This Windows PowerShell script should run only if the results of a Transact-SQL query are successful. Finally, you want to ensure that your colleague, Kim, is contacted if other operators are unavailable. With these facts in mind, answer the following questions:

  1. How can you ensure that the Windows PowerShell script runs only after the Transact- SQL statement executes successfully? Answer.

    2. You can configure job steps so that the next step occurs only if the first step completes successfully.

    Hide the Answer.

  2. How can you ensure that alerts are not triggered for the next week but can be triggered after the maintenance is complete? Answer.

    Disable the alerts. You can re-enable them as necessary. While disabled, the jobs will not be triggered.

    Hide the Answer.

  3. How can you ensure that Kim is contacted if other operators are not available? Answer.

    Configure Kim as a fail-safe operator.

    Hide the Answer.

Case Scenario 23 (Chapter 11 - Case Scenario 2): Fabrikam Backup Strategy

You are developing a backup strategy at Fabrikam, which uses AlwaysOn Availability Groups as a high-availability solution. A large number of user databases are hosted on servers that are not members of availability groups. You have the following requirements:

  • Backups should be taken of secondary instances for the Victoria availability group only.
  • You want to create a backup device named file_backup on each server that uses the local file e:\store\file_backup.bak.
  • You should be able to perform a point-in-time recovery on any user database at Fabrikam.

With these facts in mind, answer the following questions:

  1. Which command should you issue to configure the Victoria availability group so that backups are taken only on the appropriate replicas? Answer.

    2. Issue the ALTER AVAILABILITY GROUP [Victoria] SET (AUTOMATED_BACKUP _PREFERENCE = SECONDARY_ONLY); command.

    Hide the Answer.

  2. Which command should you issue to create the appropriate backup device on each server? Answer.

    Execute the EXEC sp_addumpdevice ‘disk’, ‘file_backup’, ‘e:\store\file_backup.bak’ command to create the appropriate backup device.

    Hide the Answer.

  3. Which recovery model should all the databases at Fabrikam use? Answer.

    All databases will need to use the full recovery model to ensure that point-in-time recovery is possible.

    Hide the Answer.

Case Scenario 24 (Chapter 13 - Case Scenario 3): Recovery at Adatum

You are performing a recovery on several SQL Server 2012 servers that have suffered a severe malware infection, leading to database corruption. You are currently dealing with the follow- ing problems:

  • The master database on server SQL-SYD-A is corrupt, and you are unable to start the Database Engine instance.
  • You want to determine whether the HOVERCRAFT database on server SYD-SQL-D is in the emergency state.
  • One of the four files used for the NEPTUNE database has become corrupt.

With these facts in mind, answer the following questions:

  1. Which command would you use to rebuild the system databases on SYD-SQL-A? Answer.

    2. Use the following command to rebuild the system databases on the default instance of SYD-SQL-A: setup /Q /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER

    Hide the Answer.

  2. Which Transact-SQL statement can you use to check the state of the HOVERCRAFT database? Answer.

    Use the following command to check the current state of the HOVERCRAFT database: SELECT databasepropertyex ('HOVERCRAFT', 'Status');

    Hide the Answer.

  3. Under what conditions would you be able to perform an online file restore of the corrupted file of the NEPTUNE database? Answer.

    You can perform an online file restore of the corrupted file in the NEPTUNE database if you can perform a tail-log backup, the host instance is running SQL Server 2012 Enterprise edition, and the primary file group is online.

    Hide the Answer.

Case Scenario 25 (Chapter 12 - Code Case Studies)

Case Study 1

You are developing the necessary code to configure the Litware2012 database. This database will be hosted on a SQL Server 2012 instance. At present, the instance is configured in the following way:

■ FILESTREAM is not currently enabled.

■ No database master key exists.

01 USE [master]

02 GO

03 CREATE DATABASE [Litware2012]

04 GO

05 ALTER DATABASE [Litware2012] ADD FILEGROUP [Tertiary]

06 GO

07 ALTER DATABASE [Litware2012] ADD FILEGROUP [Quaternary]

08 GO

09 EXEC sp_configure filestream_access_level, 2

10 GO

11 RECONFIGURE

12 GO

13 ALTER DATABASE Litware2012 ADD FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM;

14 GO

15 ALTER DATABASE Litware2012 ADD FILE (

16 NAME = FileStrmFile,

17 FILENAME = 'C:\FSTRM')

18 TO FILEGROUP FileStreamFileGroup;

19 ALTER DATABASE Litware2012

20 SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'FTBLE');

21 USE [LitWare2012]

22 CREATE TABLE DocStore as FileTable;

23 GO

24 sp_configure 'contained database authentication', 1;

25 GO

26 RECONFIGURE;

27 GO

28 USE [master]

29 GO

30 ALTER DATABASE [Litware2012] SET CONTAINMENT = PARTIAL

31 GO

32 CREATE USER contained user WITH PASSWORD = 'Pa$$w0rd';

33 USE master;

34 GO

35 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';

36 GO

37 CREATE CERTIFICATE ServerCertificate WITH SUBJECT = 'Server Certificate';

38 GO

39 USE [LitWare2012];

40 GO

41 CREATE DATABASE ENCRYPTION KEY

42 WITH ALGORITHM = AES_128

43 ENCRYPTION BY SERVER CERTIFICATE ServerCertificate;

44 GO

45 ALTER DATABASE LitWare2012

46 SET ENCRYPTION ON;

47 GO

Questions

Answer the following questions to test your knowledge of the information in this case study.

Refer to the code sample as necessary. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the “Answers” section at the end of this chapter.

QUESTION 1

Prior to which line of code is it necessary to reconfigure and restart the Microsoft SQL Server service?

A. 01

B. 03

C. 13

D. 09

QUESTION 2

Which tool would you use to enable FILESTREAM on the SQL Server service?

A.  SQL Server Management Studio

B.  SQL Server Installation Center

C.  SQL Server Configuration Manager

D.  SQL Server Profiler

QUESTION 3

Which line configures the instance so that it supports use by database users who have no corresponding instance login?

A. 30

B. 24

C. 20

D. 32

QUESTION 4

Which Transact-SQL statement would you use to back up the database encryption key created on line 35?

A.  BACKUP MASTER KEY

B.  ALTER MASTER KEY

C.  DROP MASTER KEY

D.  CREATE MASTER KEY

QUESTION 5

Which line of code would you modify if you wanted to increase the database encryption key length to 256 bits?

A. 35

B. 37

C. 42

D. 46

QUESTION 6

Which of the following substitutions on line 42 would give you the strongest database encryption key?

A.  WITH ALGORITHM = AES_128

B.  WITH ALGORITHM = AES_192

C.  WITH ALGORITHM = AES_256

D.  WITH ALGORITHM = TRIPLE_DES_3KEY

QUESTION 7

This code creates a table mapped to a directory on the file system. You can add files to the database by copying them to this directory on the file system. What is the name of this table?

A.  FILESTREAM

B.  FTBLE

C.  FSTRM

D.  DocStore

QUESTION 8

Which line of code would you alter if you wanted to select a different directory to associate with file tables in the Litware2012 database?

A. 20

B. 22

C. 30

D. 17

QUESTION 9

After which line of code are you able to create contained users in the Litware2012 database?

A. 24

B. 09

C. 30

D. 35

QUESTION 10

Which of the following statements could you use to create a contained user mapped to the contoso\contained_user_b user in the Litware2012 database after all the code in the case study has been executed?

A.  CREATE USER contained_user_b WITH PASSWORD ‘P@ssw0rd’;

B.  CREATE LOGIN [contoso\contained_user_b];

C.  CREATE USER [contoso\contained_user_b] FOR LOGIN [contoso\contained_user_b];

D.  CREATE USER [contoso\contained_user_b];

QUESTION 11

After this statement has been executed, which of the following filegroups are associated with the Litware2012 database? (Choose all that apply.)

A.  Tertiary

B.  Secondary

C.  DocStore

D.  FileStreamFileGroup

QUESTION 12

Which of the following Transact-SQL statements would you use to remove the Tertiary filegroup?

A.  ALTER DATABASE

B.  ALTER SCHEMA

C.  DROP DATABASE

D.  DROP SCHEMA

QUESTION 13

After which line of code are you able to create databases that use contained authentication?

A. 24

B. 09

C. 30

D. 35

QUESTION 14

After you execute the code in the case study, you create a database named TailspinToys.

Which of the following statements must you execute to apply transparent data encryption to this new database? (Choose all that apply.)

A.  CREATE MASTER KEY

B.  CREATE CERTIFICATE

C.  CREATE DATABASE ENCRYPTION KEY

D.  ALTER DATABASE

QUESTION 15

Which of the following Transact-SQL statements would you use to add a file named newfile. ndf in the c:\DBFILES to the Tertiary filegroup?

A.  ALTER DATABASE

B.  ALTER ENDPOINT

C.  ALTER SCHEMA

D.  ALTER TABLE

QUESTION 16

Which statement must be executed before it is possible to configure a database hosted on the instance to use FILESTREAM?

A. 24

B. 09

C. 30

D. 35

QUESTION 17

Which line of code would you modify if you wanted to alter the folder that hosts the FILESTREAM file?

A. 22

B. 30

C. 17

D. 20

QUESTION 18

Which of the following statements would you use to make the Tertiary filegroup read-only?

A.  ALTER DATABASE

B.  ALTER ENDPOINT

C.  ALTER SCHEMA

D.  ALTER TABLE

QUESTION 19

You have executed the code presented in the case scenario, but you want to change the database master key password. Which of the following statements would you use to accomplish this goal?

A.  OPEN MASTER KEY

B.  ALTER MASTER KEY

C.  DROP MASTER KEY

D.  CLOSE MASTER KEY

QUESTION 20

After which line of code is executed against the instance is it possible to create a server certificate?

A. 24

B. 09

C. 30

D. 35

Case Study 2

You are creating users, logins, and roles to secure the AdventureWorks2012 database. With this in mind, you have developed the following code:

01 USE [master]

02 GO

03 CREATE LOGIN "CONTOSO\Account_Two" FROM WINDOWS;

04 CREATE LOGIN "CONTOSO\Group_Two" FROM WINDOWS;

05 CREATE LOGIN sql_user_a WITH PASSWORD = 'Pa$$w0rd';

06 CREATE CERTIFICATE Dan_Bacon

07 WITH SUBJECT = 'Dan Bacon certificate in master database',

08 EXPIRY_DATE = '01/01/2018';

09 CREATE LOGIN Dan_Bacon FROM CERTIFICATE Dan_Bacon;

10 CREATE ASYMMETRIC KEY sql_user_e WITH ALGORITHM = RSA_2048;

11 CREATE LOGIN sql_user_e FROM ASYMMETRIC KEY sql_user_e;

12 ALTER LOGIN sql_user_a DISABLE;

13 DENY CONNECT SQL TO [contoso\Account_Two];

14 ALTER SERVER ROLE serveradmin ADD MEMBER "contoso\Group_Two";

15 CREATE SERVER ROLE Modify_Databases;

16 GRANT ALTER ANY DATABASE TO Modify_Databases;

17 CREATE CREDENTIAL RemoteFTP with IDENTITY = 'FTP_Login', SECRET = 'Pa$$w0rd';

18 USE [AdventureWorks2012]

19 GO

20 CREATE USER "contoso\Group_Two" FOR LOGIN "contoso\Group_Two";

21 CREATE ROLE TableCreator AUTHORIZATION "contoso\administrator";

22 CREATE ROLE [Alpha-Role] AUTHORIZATION "contoso\administrator";

23 CREATE ROLE [Beta-Role] AUTHORIZATION "contoso\administrator";

24 GRANT CREATE TABLE TO TableCreator;

25 EXEC sp_addrolemember 'TableCreator', "contoso\Account_Two";

26 EXEC sp_addrolemember 'Alpha-Role', [Dan_Bacon]

27 GRANT INSERT ON [Person].[Address] TO [Alpha-Role]

28 DENY INSERT ON [Person].[Address] TO [Beta-Role]

Questions

Answer the following questions to test your knowledge of the information in this case study.

Refer to the code sample as necessary. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the “Answers” section at the end of this chapter.

QUESTION 1

Which step must you take prior to creating the Dan Bacon account?

A.  Enable contained database authentication.

B.  Execute the CREATE MASTER KEY statement.

C.  Execute the CREATE CERTIFICATE statement.

D.  Execute the CREATE DATABASE ENCRYPTION KEY statement.

QUESTION 2

What must you do before executing the CREATE ROLE TableCreator AUTHORIZATION

“ contoso\administrator” statement? (Choose all that apply.)

A.  You must create the “contoso\administrator” user by using the CREATE USER “contoso

\administrator” for LOGIN “contoso\administrator” statement.

B.  You must create the “contoso\administrator” login by using the CREATE LOGIN

“ contoso\administrator” FROM WINDOWS statement.

C.  You must create the login Administrator by using the CREATE LOGIN [Administrator]

WITH PASSWORD ‘P@ssw0rd’ statement.

D.  You must create the user Administrator by using the CREATE USER [Administrator] for

LOGIN [Administrator] statement.

QUESTION 3

Which step must you take before adding Dan Bacon to the Alpha-Role role in line 26?

A.  Create a user for Dan Bacon in the AdventureWorks2012 database.

B.  Create a user for Dan Bacon in the msdb database.

C.  Create a login for Dan Bacon in the msdb database.

D.  Create a login for Dan Bacon in the AdventureWorks2012 database.

QUESTION 4

Which statement would you use to enable the sql_user_a login?

A.  ALTER LOGIN

B.  DROP LOGIN

C.  ALTER USER

D.  DROP USER

QUESTION 5

Which line of code would you change if you wanted to ensure that Dan Bacon could not authenticate after the first of January 2017?

A. 09

B. 10

C. 08

D. 11

QUESTION 6

Which login authenticates by using an asymmetric key?

A.  contoso \Account_Two

B.  sql _user_a

C.  sql _user_e

D.  Dan_Bacon

QUESTION 7

After the case study code has been executed, which of the following logins are members of the TableCreator role?

A.  contoso \Account_Two

B.  contoso \Group_Two

C.  sql _user_a

D.  Dan_Bacon

QUESTION 8

Which of the following logins is disabled after the case study code has been executed?

A.  contoso \Account_Two

B.  sql _user_a

C.  sql _user_e

D.  Dan_Bacon

QUESTION 9

After executing the code, you determine that one login was inadvertently added to the serveradmin role. Which of the following Transact-SQL statements would you execute to remedy this situation?

A.  sp _addrolemember

B.  DROP ROLE

C.  ALTER ROLE

D.  ALTER SERVER ROLE

QUESTION 10

Which of the following logins has not been disabled but has been denied the ability to connect to the instance?

A.  contoso \Account_Two

B.  sql _user_a

C.  Dan_Bacon

D.  sql _user_e

QUESTION 11

You have discovered that the password assigned to the FTP credential is incorrect. Which line of the case study code would you modify to correct this error?

A. 12

B. 21

C. 17

D. 28

QUESTION 12

After running the case study code, to which Active Directory group could you add an Active Directory user account to grant access to the instance?

A.  Dan_Bacon

B.  Alpha-Role

C.  contoso \Group_Two

D.  Beta-Role

QUESTION 13

After executing the case study code, you want to add two users to the Beta-Role role. Which of the following statements or stored procedures could you use to accomplish this goal?

(Choose all that apply.)

A.  ALTER ROLE

B.  sp _addrolemember

C.  CREATE ROLE

D.  ALTER SERVER ROLE

QUESTION 14

Which statement would you use to modify the membership of the Modify_Databases role?

A.  CREATE ROLE

B.  ALTER ROLE

C.  ALTER SERVER ROLE

D.  CREATE SERVER ROLE

QUESTION 15

Which of the following principals could you add to the Beta-Role role? (Choose all that apply.)

A.  TableCreator

B.  ModifyDatabases

C.  contoso \Account_Two

D.  sql _user_a

QUESTION 16

Which of the following principals could you add to the Modify_Databases role? (Choose all that apply.)

A.  sql _user_a

B.  TableCreator

C.  sql _user_e

D.  Beta-Role

QUESTION 17

Which of the following statements would you use to assign the ALTER ANY LOGIN statement to the Modify_Databases role?

A.  ALTER SERVER ROLE

B.  GRANT

C.  DENY

D.  REVOKE

QUESTION 18

You want to remove the DENY INSERT ON permission assigned to the Beta-Role role on line 28. Which statement would you use to accomplish this goal?

A.  ALTER ROLE

B.  GRANT

C.  DENY

D.  REVOKE

QUESTION 19

You have created a new role named Role_Beta by using the CREATE SERVER ROLE statement.

Members of this role should be able to unlock locked SQL Server logins. Which of the following statements would you use to assign the appropriate permission?

A.  GRANT ALTER ANY CREDENTIAL TO Role_Beta

B.  GRANT ALTER ANY LOGIN TO Role_Beta

C.  DENY ALTER ANY CREDENTIAL TO Role_Beta

D.  REVOKE ALTER ANY CREDENTIAL TO Role_Beta

QUESTION 20

You want to create a database role that enables users to view data in a table but not to modify that data, remove that data, or insert new data. Which of the following database-level permissions should you assign to this database role on the table?

A.  ALTER

B.  SELECT

C.  INSERT

D.  DELETE

Case Study 3

You are planning the configuration of server-level and database-level audits. With this in mind, you have prepared the following code:

01 CREATE SERVER AUDIT [INSTANCE_AUDIT]

02 TO APPLICATION_LOG

03 WITH

04 ( QUEUE_DELAY = 1000

05 ,ON_FAILURE = SHUTDOWN

06 )

07 CREATE SERVER AUDIT SPECIFICATION [INSTANCE_SPEC]

08 FOR SERVER AUDIT [INSTANCE_AUDIT]

09 ADD (DATABASE_CHANGE_GROUP)

10 ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC]

11 ADD (DATABASE_LOGOUT_GROUP)

534 CHAPTER 12 Code Case Studies

12 CREATE DATABASE AUDIT SPECIFICATION [DATABASE_SPEC]

13 FOR SERVER AUDIT [INSTANCE_AUDIT]

14 ADD (USER_CHANGE_PASSWORD_GROUP)

15 ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC]

16 ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP)

Questions

Answer the following questions to test your knowledge of the information in this case study.

Refer to the code sample as necessary. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the “Answers” section at the end of this chapter.

QUESTION 1

Which line of code would you change if you wanted events to be written to the Security log?

A. 04

B. 02

C. 05

D. 01

QUESTION 2

If you are using a domain account for the SQL Server service account, which of the following must you ensure prior to configuring audit events to be written to the Security log? (Choose all that apply.)

A.  Add the service account to the Generate Security Audits policy.

B.  Configure the Audit Privilege Use policy for Success and Failure.

C.  Configure the Audit Object Access policy for Success and Failure.

D.  Add the service account to the Manage Auditing And Security Log policy.

QUESTION 3

After executing the case study code, you determine that you want to audit backup and restore operations at the instance level. Which of the following statements would you execute to accomplish this goal?

A.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] ADD (AUDIT_CHANGE_GROUP)

B.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] ADD (BACKUP_RESTORE_GROUP)

C.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] ADD (BACKUP_RESTORE_GROUP)

D.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] ADD (AUDIT_CHANGE_GROUP)

QUESTION 4

You execute the statements in the case study code. You then determine that you do not want to audit password change events on contained databases. Which of the following Transact-SQL statements should you execute to modify the appropriate audit specification so these events are no longer audited?

A.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] DROP (DATABASE_CHANGE_GROUP)

B.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] DROP (USER_CHANGE_PASSWORD_GROUP)

C.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] DROP (DATABASE_LOGOUT_GROUP)

D.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] DROP (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP)

QUESTION 5

Which line of code would you modify if you wanted to increase the amount of time that elapses before audit actions must be processed?

A. 04

B.  02

C. 05

D. 01

QUESTION 6

You start SQL Server Management Studio, connect to an instance, and run the case study code. In which database is the database audit specification created?

A. master

B. model

C.  msdb

D.  tempdb

QUESTION 7

After executing the statements in the case study code, you want to configure additional auditing at the database level. Specifically, you want to audit changes to auditing settings.

Which of the following statements would you execute to accomplish this goal?

A.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] ADD (AUDIT_CHANGE_GROUP)

B.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] ADD (BACKUP_RESTORE_GROUP)

C.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] ADD (BACKUP_RESTORE_GROUP)

D.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] ADD (AUDIT_CHANGE_GROUP)

QUESTION 8

You want to audit the use of DBCC commands on a specific database. Which of the following audit action groups should you add to a database audit specification?

A. FAILED_DATABASE_AUTHENTICATION_GROUP

B. DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP

C.  DBCC_GROUP

D.DATABASE_OWNERSHIP_CHANGE_GROUP

QUESTION 9

You run the statements in the case study code. After reviewing the audit logs, you determine that you no longer want to audit logout events for contained database users. Which of the following statements should you execute to accomplish this goal?

A.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] DROP (DATABASE_CHANGE_GROUP)

B.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] DROP (USER_CHANGE_PASSWORD_GROUP)

C.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] DROP (DATABASE_LOGOUT_GROUP)

D.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] DROP (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP)

QUESTION 10

The Database Engine instance is configured to shut down on audit failure. Recovery involves starting the Database Engine instance in a special mode. You want to allow the Database Engine instance to continue running if there is an auditing failure. Which line of code would you change in the case study code to accomplish this goal?

A. 04

B. 02

C. 05

D. 01

QUESTION 11

You want to audit changes to database ownership for a specific database. Which of the following audit action groups should you add to a database audit specification?

A. FAILED_DATABASE_AUTHENTICATION_GROUP

B. DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP

C.  DBCC_GROUP

D. DATABASE_OWNERSHIP_CHANGE_GROUP

QUESTION 12

You configure a server audit specification to include the DATABASE_PRINCIPAL_CHANGE_GROUP audit action group. Which of the following events will be recorded due to the inclusion of this audit action group in a database audit specification? (Choose all that apply.)

A.  Use of the CREATE LOGIN statement

B.  Use of the CREATE USER statement

C.  Use of the CREATE SERVER ROLE statement

D.  Use of the CREATE ROLE statement

QUESTION 13

You execute the case study code. After reviewing the audit logs, you determine that you no longer want to audit database creation, modification, or deletion events. Which of the following statements would you execute to accomplish this goal?

A.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] DROP (DATABASE_CHANGE_GROUP)

B.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] DROP (USER_CHANGE_PASSWORD_GROUP)

C.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] DROP (DATABASE_LOGOUT_GROUP)

D.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] DROP (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP)

QUESTION 14

After executing the statements in the case study code, you want to configure additional auditing at the instance level. You want to track changes made to auditing settings. Which of the following statements would you execute to accomplish this goal?

A.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] ADD (AUDIT_CHANGE_GROUP)

B.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] ADD (BACKUP_RESTORE_GROUP)

C.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] ADD (BACKUP_RESTORE_GROUP)

D.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] ADD (AUDIT_CHANGE_GROUP)

QUESTION 15

You have enabled contained authentication on a database. You want to track failed logins for database users. Which of the following audit action groups should you add to an existing database audit specification to accomplish this goal?

A. FAILED_DATABASE_AUTHENTICATION_GROUP

B. DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP

C.  DBCC_GROUP

D.  DATABASE_OWNERSHIP_CHANGE_GROUP

QUESTION 16

You want to track changes to the membership of fixed server roles. Which of the following audit action groups should you configure as part of a server audit specification?

A. DATABASE_ROLE_MEMBER_CHANGE_GROUP

B. SERVER_ROLE_MEMBER_CHANGE_GROUP

C. SERVER_PRINCIPAL_CHANGE_GROUP

D. DATABASE_PRINCIPAL_CHANGE_GROUP

QUESTION 17

You configure a server audit specification to include the SERVER_PRINCIPAL_CHANGE_GROUP audit action group. Which of the following events will be recorded due to the inclusion of this audit action group in the server audit specification? (Choose all that apply.)

A.  Use of the CREATE LOGIN statement

B.  Use of the CREATE USER statement

C.  Use of the CREATE SERVER ROLE statement

D.  Use of the CREATE ROLE statement

QUESTION 18

You execute the case study code. After reviewing the audit logs, you determine that you no longer want to audit successful contained database user authentication. Which of the following statements should you execute to modify SQL Server auditing so these events are no longer audited?

A.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] DROP (DATABASE_CHANGE_GROUP)

B.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] DROP (USER_CHANGE_PASSWORD_GROUP)

C.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] DROP (DATABASE_LOGOUT_GROUP)

D.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] DROP (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP)

QUESTION 19

You execute the statements in the case study code on an instance that hosts 200 databases.

You want to audit backup and restore operations on some but not all of these databases.

Which of the following Transact-SQL statements would you execute to accomplish this goal?

A.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] ADD (AUDIT_CHANGE_GROUP)

B.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] ADD (BACKUP_RESTORE_GROUP)

C.  ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] ADD (BACKUP_RESTORE_GROUP)

D.  ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] ADD (AUDIT_CHANGE_GROUP)

QUESTION 20

You want to ensure that changes to database object ownership are recorded. Which of the following audit action groups should you add to a database audit specification to accomplish this goal?

A. FAILED_DATABASE_AUTHENTICATION_GROUP

B. DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP

C.  DBCC_GROUP

D. DATABASE_OWNERSHIP_CHANGE_GROUP

Case Study 4

You are configuring Transact-SQL code that will configure options for your organization’s newly deployed database instances. The code is as follows:

01 EXEC sys.sp_configure 'show advanced options', 1;

02 GO

03 RECONFIGURE;

04 GO

05 EXEC sys.sp_configure 'min server memory', 1024;

06 GO

07 EXEC sys.sp_configure 'max server memory', 4096;

08 GO

09 sp_configure 'fill factor', 90;

10 GO

11 RECONFIGURE;

12 GO

13 USE [master]

14 GO

15 ALTER DATABASE [model] SET RECOVERY FULL WITH NO_WAIT

16 GO

17 sp_configure 'Database Mail XPs', 1;

18 GO

19 RECONFIGURE;

20 GO

21 EXECUTE msdb.dbo.sysmail_add_account_sp

22 @account_name = 'Litware2012 Administrator',

23 @email_address = 'litware2012@contoso.com',

24 @mailserver_name = 'smtp.contoso.com' ;

25 EXECUTE msdb.dbo.sysmail_add_profile_sp

26 @profile_name = 'Litware2012 Mail Profile',

27 @description = 'Profile used for administrative mail.' ;

28 EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

29 @profile_name = 'LitWare2012 Mail Profile',

30 @account_name = 'Litware2012 Administrator',

31 @sequence_number = 1;

32 EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

33 @profile_name = 'LitWare2012 Mail Profile',

34 @principal_id = 0,

35 @is_default = 1;

36 EXECUTE msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,

37 @databasemail_profile='LitWare2012 Mail Profile',

38 @use_databasemail=1;

Questions

Answer the following questions to test your knowledge of the information in this case study.

Refer to the code sample as necessary. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the “Answers” section at the end of this chapter.

QUESTION 1

Which line of code would you change if you wanted to increase the maximum amount ofmemory available to the instance?

A. 09

B.  05

C. 07

D. 17

QUESTION 2

Which of the following steps must you perform before executing lines 36 through 38 of the case study code?

A.  Start the SQL Server Browser service.

B.  Stop the SQL Server Agent service.

C.  Start the SQL Server Agent service.

D.  Stop the SQL Server Browser service.

QUESTION 3

You want to modify the address of the SMTP mail server used by the Litware2012

Administrator database mail account. Which of the following lines of code would you modify to accomplish this goal?

A. 34

B. 23

C. 24

D. 37

QUESTION 4

Which line of code would you modify if you wanted to change the minimum amount of memory allocated to the instance?

A. 09

B. 05

C. 07

D. 17

QUESTION 5

Which configuration option would you use with the sp_configure stored procedure to set the processor cores that a Database Engine instance uses for processing tasks?

A. backup compression default

B. affinity I/O mask

C. affinity mask

D. recovery interval

QUESTION 6

Which line of code would you modify if you wanted to change the default recovery model for all future databases created on the instance?

A. 15

B. 17

C. 09

D. 38

QUESTION 7

Which line of code would you modify to change the principal database mail profile from public to private?

A. 34

B. 23

C. 24

D. 37

QUESTION 8

Which line of code would you modify if you wanted to change the default fill factor used with indexes on the instance?

A. 09

B. 05

C. 07

D. 17

QUESTION 9

Which configuration option would you use with the sp_configure stored procedure to set the maximum time between automatic checkpoints at the instance level?

A. backup compression default

B. affinity I/O mask

C. affinity mask

D. recovery interval

QUESTION 10

You want to ensure that specific standard options are present in each newly created database.

Which of the system databases should you configure to accomplish this goal?

A. master

B. model

C.  msdb

D.  tempdb

QUESTION 11

Which line of code enables database mail?

A. 09

B. 05

C. 07

D. 17

QUESTION 12

Which line of code would you modify to alter the email address associated with the

Litware2012 Administrator database mail account?

A. 34

B. 23

C. 24

D. 37

QUESTION 13

You want to disable backup compression by default on the instance configured through the case study code. Which of the following configuration options used with the sp_configure stored procedure would enable you to accomplish this goal?

A. backup compression default

B. affinity I/O mask

C. affinity mask

D. recovery interval

QUESTION 14

You are creating a large number of jobs and alerts. Which of the following system databases must you back up to ensure that the settings for these jobs and alerts are also backed up?

A. master

B.  msdb

C.  tempdb

D. model

QUESTION 15

A previous DBA configured an instance so that all new databases are configured to shrink automatically to reclaim space. You want to change this option. Which of the following options would you configure with the ALTER DATABASE statement applied to the model system database?

A. ALLOW_SNAPSHOT_ISOLATION

B.  ENCRYPTION

C.  AUTO_SHRINK

D.  AUTO_CLOSE

QUESTION 16

You have created a large number of logins. Which system database should you immediately back up to ensure that the logins are also backed up?

A. master

B. model

C.  msdb

D.  tempdb

QUESTION 17

Which line of code would you alter to change the database mail profile used by SQL Server Agent?

A. 34

B. 23

C. 24

D. 37

QUESTION 18

You want to configure specific processor cores to handle disk input/output (I/O) operations.

Which of the following configuration options of the sp_configure stored procedure would you set to accomplish this goal?

A. backup compression default

B. affinity I/O mask

C. affinity mask

D. recovery interval

QUESTION 19

You are configuring an instance that will host secure databases. You have enabled transparent data encryption on several databases already and want to ensure that all future databases have this option enabled. Which of the following statements would you issue to accomplish this goal?

A.  ALTER DATABASE [tempdb] SET ENCRYPTION ON

B.  ALTER DATABASE [master] SET ENCRYPTION ON

C.  ALTER DATABASE [msdb] SET ENCRYPTION ON

D.  ALTER DATABASE [model] SET ENCRYPTION ON

QUESTION 20

A previous database administrator configured databases on an instance to shut down cleanly when the last user closes his or her connection. This is causing performance problems when new users connect. Which of the following options would you use with the ALTER DATABASE statement to ensure that databases stay online even when no users are currently connected?

A.  ALLOW_SNAPSHOT_ISOLATION

B.  ENCRYPTION

C.  AUTO_SHRINK

D.  AUTO_CLOSE