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

1. The DeliveryVans, AirlinePreferences, and SeasonalContractors databases should be removed into the data center. Which action should be performed?

A) A separate instance of SQL Server 2005 should be installed for each of the databases on a single server.
B) A default instance of SQL Server 2005 should be installed on three separate servers. And then, one server should be utilized for each of the databases.
C) The data should be imported from each of the databases into a single database in an instance of SQL Server 2005.
D) A SQL Server database should be created for each of the databases in a single instance of SQL Server 2005.

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 administrator of a SQL Server 2005 computer, and the SQL Server 2005 computer is called SQL01. SQL01 is in a Microsoft Active Directory domain. No rights or privileges are assigned to you to have domain administration performed. However, the company has granted you membership in the local Administrators group on SQL01. Most of the management of SQL01 is performed from your administrative workstation. However, according to the company security requirements, you intend to have all attempts for interactive logons and network connections to SQL1 tracked. Which action should be performed?

A) The SQL Server Profiler should be run and a standard default template should be utilized.
B) A Group Policy object (GPO) should be created, and the GPO is set to audit success and failure of the Audit account logon events setting. And then, the domain administrator should be requested to link the GPO to the object including SQL01.
C) The local security policy of SQL1 should be edited. Then, success and failure auditing should be set on the Audit logon events setting.
D) The SQL Server service should be set on SQL01 to audit all successful and failed logon attempts.

3. According to the company requirement, tempdb performance should be improved. Therefore, you have to decide on the best configuration for the tempdb database on SQL01 after reviewing the current storage system. Company requirements and policies must be satisfied in your solution. So what should you do? (choose more than one)

A) After a RAID-1 set is created, tempdb should be moved to this drive set.
B) After a RAID-0 set is created, tempdb should be moved to this drive set.
C) The starting size of tempdb should be set to 3 GB.
D) On tempdb, automatic file growth should be disabled.
E) After a RAID-0 set is created, tempdb and the user database with the largest index should be moved to this drive set.

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 the administrator of a SQL Server 2005 computer, and the computer is named SQL1. Microsoft Windows Server 2003 is run by all servers. All the servers belong to a Microsoft Active Directory domain. According to the company written security policy, strong passwords are mandated for all domain, local, and application accounts. The company utilizes a Group Policy object (GPO) to deploy the proper domain password policy. MSDOS-based or UNIX-based operating systems are run by many of the company's computers. Varying levels of access to a database named Services on SQL1 is requested by the computers. Since you are the database administrator, you are required to confirm that the Services database can be accessed by these computers.
And the company's written security policy should not be violated. Which action should be performed by you?

A) A virtual directory should be configured through Internet Information Services (IIS) for access to SQL1. And then, users should be directed to the virtual directory for data access.
B) One SQL Server logon account should be created for all of the company's computers running MS-DOS or UNIX-based operating systems. And then, the Enforce password policy option should be enabled for users' accounts. At last, the logon accounts should be provided with access to the Services database.
C) A domain account should be created for each of the company's users. And then, each domain account should be offered with access to SQL1 and the Services database.
D) A local logon account should be created on SQL1 for each of the company's users. And then, each logon account should be offered with access to SQL1 and the Services database.
E) A SQL Server logon account should be created for each of the company's users. And then, the Enforce password policy option should be enabled for each logon account. At last, each logon account should be offered with access to the Services database.

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 work as the database administrator for your company. 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 have to make sure that the data that you import into your database is accurate and is not duplicated. What action should be performed?

A) A query that uses the CHARINDEX function should be written to use the SQL Server Import Wizard to import the data. It should be configured to ignore duplicate values.
B) On the table that contains the imported data, CHECK constraints should be configured.
C) A query that includes the HAVING clause should be written to remove duplicate values before the data is imported into your database.
D) The data should be imported into your database by using a Fuzzy Lookup transform in SQL Server Integration Services (SSIS).

Solutions: