You are a database administrator for AIOTestKing.com. The company’s financial forecasting application uses a SQL Server 2005 database that enables business users to run both ad hoc and predefined queries. You suspect that some queries consume an excessive amount of server resources. You need to identify which queries consume the most resources. You want to achieve this goal as quickly as possible. What should you do?

You work as DBA at AIOTestKing.com. You administer a SQL Server 2005 computer named AIOTestKingA. AIOTestKing A has recently been upgraded from two to eight processors. The AIOTestKing A configuration is summarized in the following table.

You need to optimize AIOTestKing A so that longer running queries can run parallel plans.
What should you do?

You are a database administrator for AIOTestKing.com. You migrate several SQL Server 2000 Data Transformation Services (DTS) packages to a 64-bit version of SQL Server 2005 Standard Edition. After migrating the packages, you notice that they do not run.
You verify that the packages run on a 32-bit version of SQL Server 2005 Standard Edition. You need to ensure that the packages run. You must minimize the amount of additional development time required. What should you do?

You are a database administrator for AIOTestKing.com. You notice that the following Transact-SQL query is performing very slowly.
SELECT VideoTitle, UpcNum, RetailPrice, ReleaseDate FROM Srvideo.VideoTitle WITH (INDEX(0)) WHERE ReleaseDate BETWEEN ‘20050401’ AND ‘20050510’ There is a clustered index on the VideoTitle column. There is a nonclustered index on the ReleaseDate column that includes the UpcNum and RetailPrice columns. When you query the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table, the avg_fragmentation_in_percent is 10 percent. The graphical execution plan for the slow query is shown in the exhibit.

You need to ensure that the query performs quickly. What should you do?

You work as DBA at AIOTestKing.com. You administer a SQL Server 2005 computer named AIOTestKingA. Backups of all system databases are performed on Saturday nights. Backups of all user databases are performed on Sunday nights. All backups are stored in an off-site facility. AIOTestKing A unexpectedly turns off due to a power outage. When AIOTestKing A is brought back online, the SQL Server service fails to start. You view the SQL Server error log as shown in the exhibit.

You need to return AIOTestKing A to working order as quickly as possible. What should you do first?

You are a database administrator for AIOTestKing.com. Each night, you perform full backups of your SQL Server 2005 computer. During business hours, you perform hourly transaction log backups. You rebuild database indexes each night. You need to modify your backup strategy. You must ensure that the database recovers to a new server as quickly as possible. What should you do?

You are a database administrator for AIOTestKing.com. A company vendor provides product inventory data by using a Web service. Your company needs to create several reports based on this data. You need to ensure that the reports have access to the necessary data.
Your solution must provide the best reporting performance. What should you do?

You are a database administrator for AIOTestKing.com. You are configuring a linked server named AIOTestKing 2 on a local server named AIOTestKing 1. You need to configure the options so that any administrator who is a member of the LocalDBAdmins group can connect to the linked server by using the remote logon of DBAdmin with a password of DBAdmin!1. All other users should be able to connect by using the remote logon of Guest with a password of Guest!1. What should you do? To answer, drag the appropriate security group, login, or password to the correct location or locations in the answer area.

You work as DBA at AIOTestKing.com. You administer a Microsoft Windows Server 2003 computer named AIOTestKingA. AIOTestKing A runs SQL Server 2005 and is a member of a Microsoft Active Directory domain named Contoso.com. The domain is configured with all of the default settings. Several members of the information security team have requested access to AIOTestKingA. The information security team uses the Microsoft Windows XP Professional, Linux, and Macintosh operating systems to perform vulnerability assessments. The information security team members ask you to create two new logon accounts for their use. They would like the logon accounts to have the following user names and passwords: User name\password for first account:
pentest\P@ssw0rdUser name\password for second account: hacktest\password You need to provide the information security team with the requested access to AIOTestKing A.
Which Transact-SQL script should you use?

You are a database administrator for AIOTestKing.com. A database on one of the company’s SQL Server 2005 computers stores transaction information for the company’s Web-based order system. The database is configured to use the full recovery model. Your company receives very few orders between the hours of 19:00 and 04:00. Your maximum acceptable risk for data loss is one hour. Database backups must be performed as quickly as possible. You need to develop a maintenance plan to manage this risk. What should you do?