You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions, monitoring the database server, and design deployment. In the company, you major job is the deployment, maintenance and support of Microsoft SQL Server 2005. You work as the database administrator for your company. You receive report from users saying that some reports are performing poorly. You are sure that your tables have the appropriate indexes and that your queries should be performing optimally. After a SHOWPLAN statement is executed on the poorly performing reports, you find that incorrect indexes are being used in some cases. You want the SQL Server 2005 query optimizer to select the correct indexes automatically. So what action should be performed?

• A. A maintenance plan should be created. An Update Statistics task should be included in this plan.
• B. All poorly performing queries should be rewritten so that they use query hints.
• C. A maintenance plan should be created. A Check Database Integrity task should be included in this plan.
• D. The indexes should be rebuilt and the server should be restarted.

Company Overview
LuckStone Research is an international organization which is not aim to earn profit, and the organization specializes in offering research support to a lot of organizations all over the United States.
There is a single office for the company, and the single office is in St Louis.
Planned changes
A new SQL Server 2005 failover cluster will be implemented by the company. Each of the three databases will be deployed with one instance on the cluster. The three databases are presently on a mainframe computer. And they are respectively called Research, Parts, and Engineering. Mission-critical data will be held by the data. An N+1 configuration is offered by utilizing a four-node failover cluster. And the company will call the cluster SQLCLUST.
After migrating these databases to SQLCLUST, the company requests to add a new identity column to most table in all of the databases so as to have a surrogate primary key supported. At the time the databases turn to production, the company requests to reset the seed value for all identity columns.
In order to get access to the Parts, Engineering, Customer, and Bids databases, the new Web services-based applications will be implemented. And the users will utilize their Windows credentials to access these applications.
The company intends to consolidate current SQL Server computers to minimize the database administration expense. The quantity of SQL Server computers and SQL Server instances should be cut to the least.
A Bid_Archive database will be created by the company. And the company plans to remove some row instantly from the Bids database to Bis_Archive. The row should meet the following requirements. The information on completed bids should be included by the row. In addition, the completed bids should not result in a new or extended contract.
You need to make sure the users can have the information retrieved, and the Bid_Archive database should not be modified.
CURRENT DATA ENVIRONMENT
Databases
From the table listed below, you can see the eight databases which are researched presently by them.

The users read data in the Contractor database more regularly than modifying it.
A Completed column is contained by the Bid_Tracking table in the Bids database. The Completed column is set to 1 when the bid is not active no more. In addition, a Successful column is contained by the table. And when the bid results in a new or extended contract, the Successful column is set to 1.
Database Servers There are three current SQL Server 2005 computers in the company, and the comaputers run with default instances, and the following databases is included by the instances.

Database Client Computers
Microsoft Windows XP Professional is run by all database client computers. Client computers utilize different custom.NET Framework applications to get access to the databases.
EXISTING INFRASTRUCTURE
Active Directory Infrastructure
There is a single Active Directory domain on the LuckStone Research network. Windows user accounts are utilized by all users to get access to SQL Server.
BUSINESS REOUIREMENTS
General Requirements
Completed annual performance review forms will be stored in the HR database. And each review form is bigger than 9.000 characters. Multiple languages and alphabets should be handled by the column which holds the review form to assist the prospective future international improvement. In addition, the data should be available in a single column.
The present state of the data in the Accounting data base should be viewed by the accounting managers at the close of any day during a week. In addition, modification can be made by them if it is necessary. A single field will be updated more than once a day, but it probably will be updated on several days continuously.
Performance
All information should be migrated to a Customer_Archive database so as to have the performance on the Customer database enhanced. And the database will be accessible for running long reports which are presently leading to the contention problems. Network traffic between the Customer database and the distribution database should be cut to the least in the management intention. What's more, the Customer_Archive database will be utilized as an added layer of fault tolerance. Management would like to utilize current servers, though additional servers are accessible.
Availability
In the event of the failure of a single hard disk, the Customer database should not fail. Hotswappable drives should be supported by the solution. And additional hardware can be utilized to support the solution.
Recoverability
A large number of modifications are received by the customer database every day in the business hours from 09:00 to 17:00. Most of the modifications happened in the afternoon. And they seldom receive modification when it is not business hours. One hour of data loss is permitted by the business requirements. The company will not permit more than six backups for any recovery. After testing on the performance of various backup scenarios, the company concluded that in business hours, the full database backups should not be performed, while the differential backups should be performed merely once in business time.
TECHNICAL REQUIREMENTS
Security
The Accounting and HR databases administrators are members of a domain global group, and the group is called SQL1_DBA. And their job is to manage all databases and SQL Server configurations on SQL01.
Some of the users are members of the DBAdmins global group, and their job is to manage the SQL Server instances on SQL02 and SQL03.
The physical computers and the Windows operating system are managed by the central IT department, and the SQL Server computers are on the Windows operating system. The Windows administrative privileges are not given to members of the DBAdmins.
Merely the least permissions should be given to all employee user accounts. And the permission is necessary and sufficient to perform their work.
According to the company's written security policy, delegation should be utilized when users of one database request to get access to another database on a separate SQL Server computer or SQL Server instance.
Except in the absolutely essential conditions, the company doesn't want to have the services installed.
At present, read access to each table in the HR database is required by all employees in the human resources (HR) department. In addition, the hiring managers should be enabled so as to have the data in five tables in the HR database read, added, deleted, and modified. Additional tables will be added by the company, and the read access to table is only permitted to a few employees. What's more, the read access to one table will be required by the future employees.
A new custom application is deployed by the company so as to have the duplication administrated by the new Customer_Archive database. Common language runtime (HR) integration and CLR stored procedures in the Customer database will be utilized by the company so as to offer limited and controlled access to the shares and files on SQL2.
Dbo should be made as the owner of all the assemblies which are created.
Maintainability
The company intends to have SQL Server Agent schedules on all servers for sustenance. And the company will utilize Database Mail to send alert. And the users will not utilize Net send to send messages. SQL Server computer will make a small number of Windows shares accessible for duplication and management.
Interoperability
The company requests to have all namespaces that are utilized for Web services applications reserved. Therefore, when the SQL Server service is not running, the same name cannot be reserved by another program on the computer.

You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions, monitoring the database server, and design deployment. In the company, you major job is the deployment, maintenance and support of Microsoft SQL Server 2005. You administer a SQL Server 2005 computer named SQL01. Backups of all user databases are performed on Sunday nights. Backups of all system databases are performed on Saturday nights. All backups are stored in an off-site facility. SQL01 shuts down unexpectedly because of a power failure. The SQL Server service fails to start when SQL01 is brought back online. The SQL Server error log is shown in the exhibit below. Since you are the technical support, you have to return SQL01 to working order in a minimum possible time. What action should you perform first?

• A. Setup.exe should be run from a command line and a rebuild of the master database
  should be specified.
• B. After the SQL Server 2005 installation CD is inserted, you should reinstall the default instance by running the setup utility.
• C. All of the user databases should be restored from backup.
• D. All of the system databases should be restored.

According to the company requirement, you are asked to implement encryption for SQL01 on the basis of the technical requirements of the company. What action should you perform?

• A. On SQL01, the Server (Request Security) IPSec policy should be implemented.
• B. The acct1 database should be created, and encrypt all data should be encrypted.
• C. On SQL01, the use of Kerberos authentication should be enforced.
• D. For the SQL Server instance, encrypted connections should be enabled with the ForceEncryption option enabled.

You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions, monitoring the database server, and design deployment. In the company, you major job is the deployment, maintenance and support of Microsoft SQL Server 2005. You work as a database administrator for your company.
A vendor company's application is utilized, and the application is based on a proprietary database. Data is queried from the application database and the data is imported into a SQL Server 2005 database. A new OLE DB driver is offered by the application vendor, and the new OLE DB driver will be utilized when the application database is queried. According the company requirements, the connections between the SQL Server database and the application database is prohibited, except when this data is queried. You should think out a method to have data queried from the application database.
Which action should be performed to achieve the goal?

• A. To achieve the goal, a Remote Server connection to the application database should be configured.
• B. To achieve the goal, a Linked Server connection should be configured to the application database.
• C. To achieve the goal, a query should be written, and an OPENQUERY command is utilized by the query.
• D. To achieve the goal, a query should be written, and an OPENDATASOURCE command is utilized by the query.