You are a database administrator for AIOTestKing.com. You manage 10 SQL Server 2005 computers. The computers reside on an isolated portion of the company’s network that has been restricted from accessing the Internet. Only intranet client computers have access to the SQL Server 2005 computers. You need to audit the SQL Server computers to identify only the missing operating system or SQL Server patches, hotfixes, and service packs. What should you do?

You are a database administrator for AIOTestKing.com. Users report that a SQL Server 2005 application is performing slowly. You need to identify the queries that are experiencing slow performance due to blocking. What should you do?

You are a database administrator for a financial services company. You are configuring replication between two SQL Server 2005 computers named SQL1 and SQL2. SQL1 stores real-time transactional data. SQL2 is used for reporting. SQL2 must not change data on SQL1. The publication contains a large amount of data. You need to ensure that during replication, data is copied from SQL1 to SQL2 in as near real-time as possible to ensure accurate reporting. You need to ensure that this data is copied securely. What should you do?

You are the administrator of a SQL Server 2005 computer named SQL1. SQL1 contains two user databases. SQL1 also contains the publisher database for a transactional replication publication. Every night, a full backup is made of all databases. Transaction log backups are made twice each day for both user databases. SQL1 is configured to use SQL Server and Windows authentication. Client applications connect by using SQL Server login accounts. The disk subsystem on SQL1 experiences a complete failure. You replace the failed hardware, reinstall Microsoft Windows Server 2003, and reinstall SQL Server 2005. You need to ensure that SQL1 is operating normally as quickly as possible and with a minimum loss of data. Your solution must make the user databases available to client applications as soon as those databases are restored. What should you do first?

You are the administrator of a SQL Server 2005 computer named SQL1. The SQL Server transaction log files are stored on an 80-GB mirrored volume. New databases have been added to SQL1, and the 80-GB volume is 85 percent full. You do not currently have the ability to upgrade the volume or to add more disk space. You need to receive an alert if the unused disk space on the 80-GB volume falls below 5 GB. What should you do?

You are the administrator of a SQL Server 2005 computer named SQL1. The SQL Server transaction log files are stored on an 80-GB mirrored volume. New databases have been added to SQL1, and the 80-GB volume is 85 percent full. You do not currently have the ability to upgrade the volume or to add more disk space. You need to receive an alert if the unused disk space on the 80-GB volume falls below 5 GB. What should you do?

You are the database administrator for a SQL Server 2005 computer named SQL1. Your company has five databases running on a single SQL Server 2005 instance. All indexes are created by using the SORT_IN_TEMPDB option to reduce the amount of time required and the amount of space used in the user databases to create and rebuild indexes.
You need to design the tempdb database for optimal performance. What should you do?

You are a database administrator for AIOTestKing.com. The company uses a database named VideoStore. The SQL statements used to create the Sales table in the VideoStore database, an existing index, and the two most common queries executed against this table are shown in the exhibit. (Click the Exhibit button.)

The existing indexes have just been rebuilt. The VideoID and VideoTitle fields are highly selective. The RetailPrice, WholesalePrice and Genre fields have low selectivity. You need to optimize the performance of both queries while minimizing the size of the index. What should you do?

You are a database administrator for AIOTestKing.com. You have many complex SQL Server Integration Services (SSIS) packages that move data among your servers. The SSIS packages import and export data to and from your company’s trading partners. The SSIS packages can only run after certain conditions are met that exist outside of SQL Server. Your department uses a third-party tool to schedule the running of nightly scripts to test for these conditions. You need to run SSIS from your scripts as seamlessly as possible. What should you do?

You are a database administrator for AIOTestKing.com. You receive nightly data feeds from your consumer electronics suppliers that show their inventory levels. The data value format that you receive from each supplier is not necessarily consistent. You need to ensure that the data that you import into your database is accurate and is not duplicated.
What should you do?