Microsoft UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005 : 70-447 Exam

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. Two user databases are contained in SQL01. The publisher database is also contained in SQL01 for a transactional replication publication. A full backup is made of all databases nightly. For both user databases, transaction log backups are made twice each day. The company has configured SQL01 to use SQL Server and Windows authentication. Client applications use SQL Server login accounts to connect. Since a complete failure occurs on the disk subsystem on SQL01, you replace the failed hardware; reinstall Microsoft Windows Server 2003 and SQL Server 2005. The company requires that SQL01 is operating normally as quickly as possible and with a minimum loss of data. Hence you have to make sure of this. Your must make client applications be able to access the user databases as soon as those databases are restored. What action should you perform first?

• A. First all of the system databases should be restored.
• B. First only the publisher database should be restored.
• C. First the transactional replication publication should be re-created.
• D. First only the two user databases should be restored.

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 OPENDATASOURCE command is utilized by the query.
• D. To achieve the goal, a query should be written, and an OPENQUERY command is utilized by the query.

Since you are the technical support, you are asked to improve the performance for the following query in the internet_rentals database. SELECT videotitle, upc_no, retailprice
FROM srvideo.videotitle WHERE releasedate BETWEEN '05-01-2005' AND '05-30-2005'
The code segment below shows the scripts that were originally used to create the existing table and indexes that are used in the query.
create table srvideo.videotitle (videoid int IDENTITY(1,1) primary key nonclustered videotitle nvarchar(100) not null , description nvarchar(255) null , videolanguage nvarchar(50) null , releasedate datetime null , isbn nvarchar(25) , upc_no nvarchar(25) , format nvarchar(25) , cost money , retailprice money) go create clustered index cl_videotitle on srvideo.videotitle (videotitle);
You must not diminish the performance on other SELECT queries that are performed on a regular basis. What action should you perform?

• A. On the releasedate column, a nonclustered index should be created.
• B. A clustered index should be added on the releasedate column.
• C. On the releasedate column, a nonclustered index should be created. The videotitle, upc_no, and retailprice columns should be added as included columns.
• D. On the retailprice column, a nonclustered index should be created. The releasedate and videotitle columns should be added as included columns.

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 administrator of a SQL Server 2005 computer, and the computer is named SQL1. The company has backups of all system databases performed every Saturday night, and have backups of all user databases performed every Sunday night. And the company has all backups stored in an off-site facility. Because of the power outage, SQL1 accidentally turns off. And after bringing SQL1 back online, the users cannot start the SQL Server service. You can see the SQL Server error log from the exhibit below. SQL1 should be returned to working order in the shortest time. Which action should be performed first?

• A. All of the user databases should be restored from backup.
• B. The SQL Server 2005 installation CD should be inserted and the setup utility should be run to have the default instance reinstalled.
• C. Setup.exe should be run from a command line and a rebuild of the master database should be specified.
• D. All of the system databases should be restored.

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. There is a SQL Server 2005 computer named SQL01 in the company. A stores sales history data is stored in a database on SQL01 for the company's Web-based order system. Twenty of the business analysts in the company create ad hoc queries against the database. SQL01 has a slow performance all the time. Since you work as the technical support, the company asks you to identify which business analyst is causing the problem. What action should you perform to achieve this goal? (Choose more than one.)

• A. In order to achieve the goal, you should view the activity of specific business analysts by using the Activity Monitor.
• B. In order to achieve the goal, you should use SQL Server Profiler to create a trace that uses a predefined template.
• C. In order to achieve the goal, the sp_who system stored procedure should be run.
• D. In order to achieve the goal, you should gather database activity by running SQL Trace system stored procedures.