Microsoft UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005 Sample Questions:

1. According to the performance and availability requirements of the company, you are asked to implement the new read-only copy of the internet_rentals database on SQL02. What should you do?

A) A new SQL Server 2005 computer should be installed. Then a new database should be created on that server. Between internet_rentals and the new database, snapshot replication should be configured.
B) A new SQL Server 2005 computer should be installed. Then a new database should be created on that server. Between internet_rentals and the new database, merge replication should be configured.
C) On SQL02, a new database should be created. Between internet_rentals and the new database, transactional replication should be configured.
D) A new SQL Server 2005 computer should be installed. Then a new database should be created on that server. Between internet_rentals and the new database, transactional replication should be configured.
E) On SQL02, a new instance of SQL Server 2005 should be installed. A new database should be created. Between internet_rentals and the new database, transactional replication should be configured.

2. 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 administer a SQL Server 2005 computer which contains a database named DB01.A table named CustInfo is included in DB01. The table includes an identity column named CustID and an nvarchar column named CustName. According to the requirement of the company policy, rows containing duplicate CustName values should be removed from the CustInfo table once each year. But only when rows have been manually examined and verified as being duplicatesm, they can be removed from the CustInfo table. Since you are the technical support, the company asks you to provide a way to identify potential duplicate rows for manual examination. You should use as little administrative effort as possible when you try to achieve this. So what action should you perform?

A) On the CustName column, a new unique index should be created. When index is being created, Specify IGNORE_DUP_KEY = ON.
B) A foreign key constraint should be created on the CustName column. This constraint references a distinct list of CustName values.
C) Create a trigger for the CustInfo table that rolls back INSERT queries that contain duplicate data.
D) You should create a new table that contains duplicate rows from the CustInfo table by using a SELECT INTO query that includes GROUP BY and HAVING clauses.

3. DRAG DROP
Drag Drop question
The certificate-based authentication will be utilized for database mirroring of the CRM database between a principal database on SQL2 and a mirror database on SQL3. The objects listed in the table below will be created.

The SQL2MirrorCert certificate will be exported and replicated to SQL3. In addition, the
SQL3MirrorCert certificate should be exported and replicated to SQL2.
The certificate should be associated with the proper endpoints and logins.
Which action should be performed?

4. 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 mission-critical customer relationship management (CRM) database is hosted by a new SQL Server 2005 computer, and the new SQL Server 2005 computer is called SQL2. The CRM database is available to all of the company's 500 employees in the sales and marketing departments. The purchase of new hardware and software should not exceed the annual budget for your department. A monitoring strategy should be devised, and the strategy enables you to make decisions about the history of server performance. In addition, how to arrange the most efficient method to allocate funds for hardware upgrades should be suggested by the monitoring strategy. Which action should be performed?

A) A counter log should be set to capture information about %Processor Time, Average Disk Queue Length, Pages/sec, and Bytes Total/sec. And then, the log should be scheduled to execute at regular intervals, and the information should be stored in a comma-delimited text file.
B) A series of performance alerts should be created to notify you when the acceptable thresholds are exceeded by %Processor Time, Average Disk Queue Length, Pages/sec, and Bytes Total/sec.
C) SQL Server Profiler should be utilized to capture information to a SQL Server database table. And then, this task should be performed at regular intervals during periods of peak server activity.
D) System Monitor in graph mode should be utilized to display information about %Processor Time, Average Disk Queue Length, Pages/sec, and Bytes Total/sec. And then, this task should be performed at regular intervals during periods of peak server activity.

5. 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. There are SQL Server transaction log files on a mirrored volume that is 80-GB in size. 85 percent of the 80-GB volume is full. New databases have been added to SQL01. At present you are unable to to upgrade the volume or to have more disk space added. If the unused disk space on the 80-GB volume is less than 5 GB, an alert should be sent to you. What action should you perform to achieve this?

A) An alert should be created for the SQL Server in SQL Server Agent: Databases Percent Log Used object.
B) The DBCC SQLPERF(LOGSPACE) command should be run in SQL Server Management Studio.
C) An alert should be created on the Logical Disk % Free Space counter in the Performance Logs and Alerts console.
D) The Microsoft Windows Management Instrumentation (WMI) query SELECT FreeSpace FROM Win32_LogicalDisk should be executed in the Wmic.exe utility.

Solutions: