Assessment Test

1. 

You are the database administrator of a dedicated SQL Server 2005 Enterprise Edition (x64) instance. The server has recently been upgraded from four processors to eight processors. Memory was also upgraded from 8GB to 16GB of memory. Natasha, a junior DBA, has reconfigured the SQL Server 2005 instance accordingly. The performance has not improved after the hardware upgrade as much as management would have expected. You have been asked to troubleshoot the SQL Server 2005 instance. The SQL Server 2005 instance has the following configuration:

Open table as spreadsheet

Configuration Option	Configured Value
affinity I/O mask	0
affinity mask	4
awe enabled	0
cost threshold for parallelism	5
lightweight pooling	0
max degree of parallelism	3
max server memory (MB)	8192
max worker threads	0
priority boost	1
set working size	0
user connections	0

You need to ensure that performance on this new hardware is optimal. What should you do? (Each correct answer represents part of the solution. Choose two.)

1. Change the affinity mask configuration option to 0.

2. Change the affinity mask configuration option to 8.

3. Change the affinity I/O mask configuration option to 8.

4. Change the awe enabled configuration option to 1.

5. Change the max degree of parallelism configuration option to 1.

6. Change the max server memory (MB) configuration option to 0.

7. Change the max server memory (MB) configuration option to 4096.

8. Change the priority boost configuration option to 0.

2. 

You are the database administrator of a SQL Server 2005 instance running on a quad-processor (x64) server with 4GB of RAM and a RAID-10 array. Your users have noticed degradation in performance and have run System Monitor to gather the following metrics:

Open table as spreadsheet

Counter	Value
Processor : %Processor Time	70
System : Processor Queue Length	2.5
Memory : Available Mbytes	2000
SQL Server:Buffer Manager : Cache Hit Ratio	65
SQL Server:Buffer Manager : Page Life Expectancy	5
PhysicalDisk Object : % Disk	30
PhysicalDisk Object : Avg. Disk Queue Length	0.8

What should you do to improve performance?

1. Add more processors to the server.

2. Add more memory to the server.

3. Allocate more memory to SQL Server 2005.

4. Reduce the number of processors SQL Server 2005 is using.

3. 

You are a database administrator for your company. You administer a SQL Server 2005 database solution that is used as a census for the platypus population in Australia. The database has been created using the default options. Database users have been complaining today about performance, and you suspect that indexes are heavily fragmented. You cannot rebuild all indexes because that would take too long and potentially slow down performance, which is unacceptable because there are some critical queries currently being run on other data that need to be completed as soon as possible. You plan to completely rebuild all indexes after-hours but need to perform the absolute minimum tasks to improve performance right now. You query the sys.dm_db_index_physical_stats DMV and get the following results:

Open table as spreadsheet

index_id	Name	avg_fragmentation_in_percent
1	PK_Platypus_ID	4.25
2	IX_Location	45.666
3	IX_DNA	25.324

What should you do? (Each correct answer represents part of the solution. Choose two.)

1. Reorganize the PK_Platypus_ID index.

2. Rebuild the PK_Platypus_ID index.

3. Reorganize the IX_Location index.

4. Rebuild the IX_Location index.

5. Reorganize the IX_DNA index.

6. Rebuild the IX_DNA index.

7. Update statistics for the PK_Platypus_ID index.

8. Update statistics for the IX_Location index.

9. Update statistics for the IX_DNA index.

4. 

You are the database administrator for your company. You are responsible for a SQL Server 2005 instance that is running 24/7. The SQL Server 2005 instance is rebooted only at the beginning of each quarter if required for maintenance purposes. In the last week of the current quarter, you notice that query performance has degraded, so you plan to check to see how long processes have been delayed by other processes on the next day. What command should you run first?

1. DBCC SQLPERF ('sys.dm_os_wait_stats' , CLEAR)

2. EXEC sp_updatestats

3. DBCC SQLPERF ('sys. dm_os_latch_stats', CLEAR)

4. EXEC sp_autostats

5. 

You are a database administrator for your company. A SQL Server 2005 database that you are responsible for has failed. You have identified that only the last full database backup has to be restored. You are about to restore this backup set. You want to ensure that the backup set you are restoring has no data corruptions in it; however, you still want to restore the database if the corruptions exist. What options should you use in the RESTORE DATABASE command? (Each correct answer represents part of the solution. Choose two.)

1. NO_CHECKSUM

2. CHECKSUM

3. STOP_ON_ERROR

4. CONTINUE_AFTER_ERROR

5. NORECOVERY

6. STANDBY

6. 

You are a database administrator for your company responsible for a database solution running on a SQL Server 2005 instance. The database consists of a primary, secondary, and log file, all of which are stored on three separate drives. The database is using the BULK_LOGGED recovery model.

The database is backed up weekly on Sunday at 10 P.M. Differential backups are performed at the end of each working day at 9 P.M. Transaction log backups are being performed every second hour, starting at 11 A.M. and finishing at 7 P.M. A database snapshot is taken every morning at 9 A.M.

When you come in at 10 A.M., you are informed that a contractor accidentally modified a table in the database at 9:45 A.M. The data modification completed at 9:50 A.M. Other data modifications were made to the database up until 9:45 A.M..

Upon questioning the contractor you have determined that she used a transaction with the WITH MARK clause to modify the table.

You need to recover the database as soon as possible before the contractor modified the data. What do you do?

1. Restore the latest full database backup. Restore last night’s differential backup. Then restore all remaining transaction logs using the STOPAT option with a time of 9:50 A.M. today.

2. Revert the database to the database snapshot using the using the STOPAT option with a time of 9:45 A.M. today.

3. Back up the tail-log. Restore the latest full database backup. Restore last night’s differential database backup. Then restore the transaction log using the STOPATMARK.

4. Back up the tail-log. Restore the latest full database backup. Restore last night’s differential database backup. Then restore the transaction log using the STOPBEFOREMARK option.

7. 

You are the database administrator for a financial trading company. A SQL Server 2005 instance is hosting four databases. The [market] database is used to store market data that is being sourced from a real-time external feed. The [trades] databases is used by the trading desk. The [staging] database is used as temporary storage when data from the market database is extracted, transformed, and loaded into the [trades] database. The [development] database is used by the development team for developing and testing purposes. You want to ensure that the staging tables in the staging database are never deleted. What should you use?

1. Use a DML trigger.

2. Use a DDL trigger at the database scope.

3. Use a DDL trigger at the server scope.

4. Use the TRUNCATE TABLE statement.

8. 

You are the database administrator for your company responsible for designing a strategy to monitor a SQL Server 2005 database solution. You want to establish a threshold for performance when the database engine is processor-bound. What performance object counter and threshold should you monitor?

1. Processor(_Total) : % Processor Time > 80

2. Processor(_Total) : % Processor Time > 20

3. Process(sqlservr) : % Processor Time > 80

4. Process(sqlservr) : % Processor Time > 20

9. 

You are the database administrator for your company. You are responsible for managing a SQL Server Reporting Services (SSRS) solution in SQL Server 2005. You have been notified that the SSRS solution has run out of space for permanent report snapshots. Which system database should you expand?

1. master

2. msdb

3. reportserver

4. reportservertempdb

10. 

You are the database administrator for your company responsible for a VLDB solution on SQL Server 2005 database. The database was created on the SQL Server 2005 instance using the defaults. Performance is critical during working hours for this VLDB solution. You have just been informed that the hot-swappable RAID array controller has had some sort of failure. The root cause has been determined, and replacement hardware has been swapped out. You are concerned that the database might have some corruptions. You plan to run a full DBCC CHECK command tonight outside working hours. You want to run a less resource-intensive database consistency check to ensure that there has been no database corruption because of the hardware failure. What DBCC CHECKDB option should you use?

1. ESTIMATE_ONLY

2. PHYSICAL_ONLY

3. DATA_PURITY

4. TABLOCK

11. 

Which of the following best describes SQL Server Integration Services?

1. An extract, transform, and load tool that can handle advanced workflow.

2. The new security subsystem packaged with SQL Server 2005.

3. The rebranded set of replication options packaged with SQL Server 2005.

4. It is DTS with a new name and no real changes to what DTS can do in SQL Server 2000.

12. 

Which of the following best describes the Data Flow task?

1. A task used to configure the import/export of data to/from disk

2. A control flow task that acts as a container for many other data operations

3. A task used to bulk insert data into a SQL Server table for maximum performance

4. A wizard used to create a basic import/export package to move data between two SQL Server tables

13. 

In SQL Server 2000, for DTS to be able to restart packages at or near the point of failure, it required a custom implementation. To achieve the same thing in SSIS, which would be the best choice?

1. Write the same type of custom code used in DTS, only updated to use Visual Basic .NET.

2. Write many smaller packages, and run them in order using a SQL Agent job.

3. Enable checkpoints and configure appropriate places in the control flow to restart if errors occur.

4. Wrap the package code in a try/catch block so that you can immediately handle errors and continue execution.

14. 

You are designing the Clinical database at your company to validate clinical trial study data. You must choose criteria to apply a data quality attribute. Which of the following represent criteria that you can use? (Choose all that apply.)

1. Consistency

2. Completeness

3. Timeliness

4. Recoverability

5. Accuracy

15. 

You are querying the Sales database to validate data stored in the SalesRegion and SalesPerson tables. You use the following query:

 SELECT sr.Name AS Region, sp.SalesPersonID FROM Sales.SalesRegion sr RIGHT OUTER JOIN Sales.SalesPerson sp ON sr.RegionID = sp.RegionID WHERE sp.RegionID IS NULL;

What do the results of this query tell you?

1. Every region that has a salesperson

2. Salespersons who have no region

3. Regions that have no salesperson

4. Every salesperson who has a region

16. 

Your pharmaceutical company has recently purchased a smaller company that has been developing many similar products. Eventually your two organizations will combine all their research into a main development database, but in the meantime you need to be able to combine their data, which is in an Oracle environment, with yours, which is on a SQL Server 2005 server. What is the most efficient way for you to combine the data for your users, keeping the data up-to-date and using minimal disk space?

1. Use a SQL Server Integration Services (SSIS) package to import the Oracle data to your SQL Server. Have the import run on a scheduled basis.

2. Create a transactional replication strategy between the SQL Server and the Oracle server with the SQL Server as the publisher/distributor and the Oracle server as the subscriber.

3. Create a transactional replication strategy between the SQL Server and the Oracle server with the Oracle server as the publisher/distributor and the SQL Server as the subscriber.

4. Create linked servers using the SQL Server as the local server and the Oracle server as the linked or remote server.

17. 

You have configured a transactional replication strategy using SQL Server 2005 between your main office complex and five branch offices that are located throughout the eastern United States. The main office server is configured as both a publisher and a distributor. The branch offices are configured as subscribers. You are concerned with the amount of latency to some of the branch offices so you want to use Replication Monitor to run some procedures to measure the latency. Which of the following should you use to get the best indications of latency?

1. View the Current Worst Performance statistics.

2. View the Queue Reader Agent history statistics.

3. Insert tracer tokens to gather statistics from the main office to each of the branch offices.

4. Configure and enable a latency threshold alert.

18. 

You suspect a virus infection on your SQL Server. What should be your first reaction?

1. Apply a patch from the vendor.

2. Isolate the system from other systems.

3. Research the infection.

4. Power down the server.

19. 

You want to be sure that no views are changed on your production server, but new views can be created. What should you do?

1. Use a DML trigger to prevent any CREATE VIEW statements.

2. Use a DDL trigger to prevent any CREATE VIEW statements.

3. Use a DML trigger to prevent any ALTER VIEW statements.

4. Use a DDL trigger to prevent any ALTER VIEW statements.

20. 

You are the DBA for a development team, but you do not have time to perform all the schema changes. How can you allow a senior developer to make changes on the development server?

1. Give him the sa password, and ask him to be careful.

2. Grant CREATE TABLE, CREATE VIEW, and CREATE PROCEDURE permissions to the developer.

3. Create a role that has the same permissions as the ddl_admin role, and assign the developer to it.

4. Add the developer to the ddl_admin role.

21. 

In addition to using stored procedures, what else is valuable in preventing SQL injection attacks?

1. Adding users to roles instead of granting rights

2. Validating input

3. Installing antivirus software on your computer

4. Ensuring all backups are encrypted

22. 

How should you apply service packs and patches to your production SQL Server 2005 instance?

1. Patches should be applied as soon as they are released.

2. Patches should be applied when you have downtime for the instance and sufficient time to apply the patch.

3. Patch installation should be scheduled as soon as possible after their application is tested on a test server.

4. Patches should be applied through automatic update.

23. 

You have just created a new domain account to run your SQL Server Agent service and use the Services applet in Control Panel to assign it to the service, but the service will not start. Why not?

1. Domain accounts cannot be used to run SQL Server Agent.

2. The account must be added to the local Administrators group.

3. The account must be added to the sysadmin group in SQL Server 2005.

4. The account needs to have the Log On As a Service right added to it.

24. 

You have just upgraded a SQL Server instance to SQL Server 2005 on your Windows 2000 Server. You want to be sure that all the SQL Server–authenticated logins on your database are secure. What should you do?

1. Check the Enforce Password Policy option in the server properties.

2. Check the Enforce Password Policy check box for each login.

3. Check the User Must Change Password at Next Login option for each login.

4. You cannot enforce password policy for SQL Server logins.

25. 

One of the Human Resources managers is going on maternity leave for 12 weeks, and she is not sure whether she will be returning to work. What should you do with her SQL Server login?

1. Delete the login.

2. Disable the login.

3. Rename the login.

4. Do nothing.

Answers

1.	A, F. Changing the max affinity mask configuration option to 0 will enable the SQL Server 2005 instance to utilize all eight processors. Changing the max server memory (MB) configuration option to 0 will enable the SQL Server 2005 instance to utilize as much memory as it requires. You don’t need to configure the awe enabled configuration option on a 64-bit server. Changing the affinity I/O mask, max degree of parallelism, or priority boost configuration option should have a minimal effect on SQL Server 2005’s performance. For more information, see Chapter 1, “Optimizing the Performance of Databases and Database Servers.”
2.	C. Although the operating system seems to have plenty of available memory, as indicated by the value of 2000 for the Memory : Available Mbytes performance object counter, SQL Server 2005 seems to have run out of memory. The SQL Server:Buffer Manager : Page Life Expectancy performance object counter indicates that data pages are not lasting in the buffer pool for long. This is corroborated by the low value for the SQL Server:Buffer Manager : Cache Hit Ratio performance object counter. The other performance object counters are within tolerable allowances. For more information, see Chapter 1, “Optimizing the Performance of Databases and Database Servers.”
3.	D, E. The IX_Location index needs to be rebuilt because it has a high level of fragmentation (greater than 30 percent). The IX_DNA index needs to be reorganized because it has a lower level of fragmentation (between 5 percent and 30 percent). There is no need to do anything to the PK_Platypus_ID index because it has a low level of fragmentation (less than 5 percent). There is nothing to indicate that updating statistics should improve performance because the database should be automatically creating and updating statistics. For more information, see Chapter 2, “Optimizing the Performance of Queries.”
4.	A. The DBCC SQLPERF ('sys.dm_os_wait_stats' , CLEAR) statement will reset aggregated information about all of the waits experienced by all processes connected to your SQL Server 2005 instance. For more information, see Chapter 2, “Optimizing the Performance of Queries.”
5.	B, D. The CHECKSUM option will validate the backup being restored. The CONTINUE_AFTER _ERROR option will ensure that the restore process will complete, irrespective of any errors encountered. For more information, see Chapter 3, “Optimizing and Implementing a Data Recovery Plan for a Database.”
6.	D. You will have to restore the latest full database backup and last night’s differential backup. Then you can restore the tail-log with the STOPBEFOREMARK option to stop before the transaction. The STOPAFTERMARK will include the data modification. The database snapshot cannot be used as it represents the database at 9:00 A.M. Option A will not work as you have not backed up the tail-log. For more information, see Chapter 3, “Optimizing and Implementing a Data Recovery Plan for a Database.”
7.	B. A DDL trigger at the database scope should prevent the staging tables from being deleted. The server scope is inappropriate because it will prevent tables from being deleted from the entire SQL Server 2005 instance. DML triggers will fire only for INSERT, UPDATE, and DELETE operations. The TRUNCATE TABLE statement is used to truncate tables. For more information, see Chapter 4, “Designing a Strategy to Monitor a Database Solution.”
8.	C. Process(sqlservr) : % Processor Time monitors the processor utilization of the SQL Server 2005 database engine. Values greater than 80 percent would indicate a processor bottleneck. For more information, see Chapter 4, “Designing a Strategy to Monitor a Database Solution.”
9.	C. By default, SSRS keeps permanent report snapshots in the reportserver system database. The reportservertempdb system database is used to store cached reports. For more information, see Chapter 5, “Designing a Strategy to Maintain a Database Solution.”
10.	B. The PHYSICAL_ONLY option skips all the logical checks and performs only the physical operations, which will be much faster than a full DBCC CHECKDB operation. It can be used to detect any data corruption caused by hardware failures. The ESTIMATEONLY option shows the estimated amount of tempdb space needed to run the DBCC CHECKDB command. The DATA_ PURITY option validates every column value in all rows of the tables in the database so it would consume more server resources. The TABLOCK option uses locks instead of the internal database snapshot to run the DBCC CHECKDB command. For more information, see Chapter 5, “Designing a Strategy to Maintain a Database Solution.”
11.	A. SSIS is indeed an ETL and workflow tool, with significant changes and enhancements compared to DTS. In particular, SSIS now uses the Visual Basic .NET language when needed rather than VBScript, as in DTS. SSIS uses the various SQL Server security models but does not replace them. SSIS is often used instead of or in conjunction with replication but certainly does not replace it. For more information, see Chapter 6, “Designing and Managing SSIS Packages.”
12.	B. The Data Flow task is a control flow task that acts as a container and is represented within the SSIS development environment as a separate tab, making it easy to see what work is being done inside the container. You should use a separate bulk insert task when performance is needed. Simple import/export of data to/from tables can easily be accomplished from SQL Server Management Studio, and although it does use SSIS for the transfer, there is no requirement to save the package or open it in the designer. SSIS offers many tasks for working with files, each optimized for a different type file (Excel, TXT files, and so on). For more information, see Chapter 6, “Designing and Managing SSIS Packages.”
13.	C. Checkpoints provide an easy-to-use declarative model for enabling your packages to restart at or near the point of failure and are far less work than writing custom code to accomplish the same thing. Using separate packages is a technique that could work, but it increases the complexity of the work just by having many more packages to manage compared to checkpoints. Separate packages are also a technique that makes more sense when you want to enable easy reuse of portions of the packages. Try/catch blocks are available when you write “real” code, but you cannot wrap the entire package in one. For more information, see Chapter 6, “Designing and Managing SSIS Packages.”
14.	A, B, C, E. All of the previous criteria except recoverability apply as a standard for data quality. Although a recoverability process is extremely important, it does not affect the quality of the data. For an explanation of data quality attributes and a listing of further attributes, see Chapter 7, “Designing a Database Data Management Strategy.”
15.	B. Your result set will be all the salespeople without a region. Since the Sales.SalesRegion table is in the FROM statement of the right outer join, the entire table is returned in the result set. The WHERE clause restricts the output to those salespeople who have no corresponding region in the region table. For further information about how to create queries to inspect your data, see Chapter 7, “Designing a Database Data Management Strategy.”
16.	D. You should create linked servers. Creating linked servers enables you to create a view from the distributed data that your users can query. No extra disk space is used, and both environments retain their data until it is time to merge the data. Learn more about linked server solutions in Chapter 8, “Designing a Strategy to Manage Replication.”
17.	C. Tracer tokens will not only validate that the data reached the destination but will also give you the elapsed time between the nodes. None of the other options specifies the metrics for you. The latency threshold alert can give you some information, but only if it fires. Learn more about configuring and administering replication as well as all the new enhancements to replication in SQL Server 2005 in Chapter 8, “Designing a Strategy to Manage Replication.”
18.	B. The first step should be to isolate the system before verifying the infection and applying a patch. See Chapter 9, “Protecting Your SQL Server from Attack,” for more information.
19.	D. You can use a DDL trigger to roll back changes, preventing them from taking effect. For this situation, you want to prevent ALTER VIEW statements, not CREATE VIEW statements. See Chapter 9, “Protecting Your SQL Server from Attack,” for more information.
20.	D. The best solution for granting permissions to the developers is to use roles. This way you can easily add or remove individuals from the role as their needs change. See Chapter 9, “Protecting Your SQL Server from Attack,” for more information.
21.	B. SQL injection attacks rely on input not being carefully validated, so ensuring that all input conforms to your rules is the best way to prevent SQL injection. In addition, using stored procedures further insulates you from attack. See Chapter 9, “Protecting Your SQL Server from Attack,” for more information.
22.	C. Patches to your production SQL Server 2005 instance should first be tested in an environment matching that of your servers. Their deployment should then be scheduled as soon as possible after a successful test. For more information, see Chapter 10, “Designing a Strategy to Manage and Maintain Database Security.”
23.	D. For a domain account to be used to execute the SQL Server Agent service, it must have the Log On As a Service right. If it must detect low CPU activity or a few other tasks, then it would need to be added to the local Administrators group. If the SQL Server Configuration Manager were used to assign this account, it would automatically grant this right. For more information, see Chapter 10, “Designing a Strategy to Manage and Maintain Database Security.”
24.	D. Although SQL Server 2005 does have the ability to enforce password policy, it can do so only if the host operating system is Windows 2003 Server or later. Windows 2000 hosts do not allow this to be enforced. For more information, see Chapter 10, “Designing a Strategy to Manage and Maintain Database Security.”
25.	B. One of the new features of SQL Server 2005 is the ability to disable logins. This should be used for accounts that will not be used for a long period but that may be needed again in the future. Deleting an account unnecessarily increases the administrative burden if the account is needed again. For more information, see Chapter 10, “Designing a Strategy to Manage and Maintain Database Security.”