Microsoft UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005 (070-447) Free Practice Test
Question 1
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?
Correct Answer: A
Question 2
After check, you find that because of a careless deletion at 11:45, all inserts and updates in the internet_rentals database from the last two days were lost. Now it is 11:58.
In order to recover the data that are lost, you have to perform a point-in-point restore. After you complete the restore, you intend to limit the amount of data loss. Your database, transaction log, and all backups can be accessed. After you take the database offline, what action should you perform?
(Choose the appropriate actions and put them in the correct order.)
1.Perform the back up of the active portion of the transaction log.
2.Restore the latest full backup, and the WITH NORECOVER option should be used.
3.Restore the latest differential backup, and the WITH NORECOVER option should be used.
4.Restore the final transaction log backup, and the WITH RECOVERY and STOPAT options should be used.
5.Restore all but the final transaction log backups that have occurred since the latest full backup, and the WITH NORECOVER option should be used.
6.Restore all but the final transaction log backups that have occurred since the latest different backup, and the WITH NORECOVER option should be used.
In order to recover the data that are lost, you have to perform a point-in-point restore. After you complete the restore, you intend to limit the amount of data loss. Your database, transaction log, and all backups can be accessed. After you take the database offline, what action should you perform?
(Choose the appropriate actions and put them in the correct order.)
1.Perform the back up of the active portion of the transaction log.
2.Restore the latest full backup, and the WITH NORECOVER option should be used.
3.Restore the latest differential backup, and the WITH NORECOVER option should be used.
4.Restore the final transaction log backup, and the WITH RECOVERY and STOPAT options should be used.
5.Restore all but the final transaction log backups that have occurred since the latest full backup, and the WITH NORECOVER option should be used.
6.Restore all but the final transaction log backups that have occurred since the latest different backup, and the WITH NORECOVER option should be used.
Correct Answer: E
Question 3
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. An e-mail message should be sent to the company database auditing department, when the SQL Server service and the SQL Server Agent service are started, stopped, or paused. In addition, an e-mail message should be sent to them, when the users utilize the EXECUTE AS option with stored procedures and other commands.
Since you are the administrator, you are required to make sure that the e-mail messages that the auditing department needs should be sent to them. What should you do to finish the task? (Choose more than one.)
Since you are the administrator, you are required to make sure that the e-mail messages that the auditing department needs should be sent to them. What should you do to finish the task? (Choose more than one.)
Correct Answer: A,D
Question 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 database administrator for your company. You are in charge of 20 SQL Server 2005 computers. Other administrators are responsible for monitoring these computers. These administrators need to guarantee that query response times meet company service level agreements (SLAs). Since you are the technical support, you are asked to provide these administrators with a consistent way of monitoring query response times on the servers. What action should you perform to achieve this goal?
Correct Answer: D
Question 5
You are adding functionality for the MarketingAnalysis database, and the functionality has the results of a query in an e-mail message sent to the marketing supervisors when new sales data has been entered. A method should be implemented to satify this requirement, and the security risks should be cut to the least. Which method should be utilized to achieve the goal?
Correct Answer: D
Question 6
DRAG DROP
According to the company requirement, the backup schedule should be designed for the customer database.
Which action should be performed to achieve the goal? (Drag the proper backup type to the right location in the answer area.)
According to the company requirement, the backup schedule should be designed for the customer database.
Which action should be performed to achieve the goal? (Drag the proper backup type to the right location in the answer area.)
Correct Answer:
Question 7
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?
Correct Answer: C
Question 8
According to the company requirements, the interim disk availability solution should be designed for SQL1. Windows RAID functionality will be utilized. A most proper RAID configuration should be identified to meet the availability requirements. From the following four RAID configurations, which one should be utilized?
Correct Answer: D
Question 9
Correct Answer:
Company Overview
SuperX Video is a video rental company. Its head office and 300 video rental branch stores are in North America. It also rents videos over the Internet, with delivery by mail.
Planned Changes
In order to maintain account information for the Internet rentals business, the company intends to develop a new mission-critical accounts database at the head office. At the time that the primary server failure occurs, this database must automatically fail over. Besides, in order to incorrect updates and deletions to be corrected using the information on the additional server, the company plans to configure an additional server. The ability to correcting of data errors should not be hindered by single server failure. Four new server computers will be able to be accessed for this mission-critical accounts database.
In order to enable store employees to view the list of videos that are available at other company stores within 20 miles, the company will implement a new replication system. Store employees should be able to update only their own store's records.
The database named rental_history is quite large. As its size increases, it is becoming slower. After investigation, it is found that the appropriate indexes already exist in this database. The most common report includes the customer name and city from the customer table; the store name, city, and store number from the store table; the rental date from the store table; the rental date from the rental table; and the video name and genre from the videotitle table. According to the indication of query statistics, , the performance on this database are affected by the joins that are required to run the other common reports, which frequently include fields from all tables. The rental_history database forbids any changes to the data. There is enough time to import the data once a month.
EXISTING DATA ENVIRONMENT
Database Servers
Recently all of their SQL Server 2000 computers of the company were upgraded to SQL Server 2005 running on Microsoft Windows Server 2003. The SQL Server computers are described in the table below.
SQL01 and SQL02 are configured for only Windows Authentication mode. All store SQL Server computers are configured for SQL Server and Windows Authentication mode.
To facilitate access by any of five mobile database administrators who are responsible only for managing the store servers, for past two years, the same password for the sa login ID has been used by all store SQL Server computers. If necessary, some nonutilized production level servers can be used.
Databases
On SQL01, the largest table has a clustered index and is about 2 GB in size.
No changes have been made to the default settings for the tempdb database on SQL01.
At present the company has configured each store_nnn_rentals database to use a single
filegroup.
Queries in the internet_rentals database are frequently sorted by the videotitle column.
Database Client Computers
In the stores or in the head office, either Windows XP Professional, Windows 2000 Professional, Windows NT Workstation 4.0, or Windows 98 is run by client computers. All client computers must be able to function with all servers. It is unnecessary to upgrade the client computers at present.
EXISTING INFRASTRUCTURE
Network Infrastructure
High-speed networks connect all stores and the head office.
Active Directory Infrastructure
There is a single Active Directory domain in the SuperX Video network. This domain is
named ad. SuperXvideo.com. All user accounts and servers reside in this domain.
Windows user accounts are used to grant access to the SQL Server computers.
A Windows group named SQLManagers has login privileges on SQL01 and SQL02 and is
a member of the sysadmin role on the two SQL Servers.
Recoverability
At present full backups are performed every night and transaction log backups are performed four times per day on the store_nnn_rentals database in all rental stores.
Full backups of the internet_rentals database are performed every night and transaction log backups are performed every hour, on the hour, from 08:00 to 17:00. A differential backup is performed at 12:00 noon.
Security
On SQL01, the SQL Server Agent service is running as a domain account named SQLService at present. This domain account belongs to the local administrators group and is a member of the sysadmin role.
BUSINESS REQUIREMENTS
General Requirements
It is forbidden to perform development, testing, and error checking on the main production databases.
Performance
In order to improve the performance to the best, the process of building indexes should be optimized.
The company intends to use the SORT_IN_TEMPDB option at the time that all indexes are being created. To support this plan, the company management has agreed to purchase purchase new hard disks.
The internet_rentals database is extremely busy. In order to optimize performance, a separate read-only version of the database should be created to run reports and perform data analysis. This version must be kept synchronized with the writeable version of the internet_rentals database with the possible least latency.
vailability
All databases must have the ability of withstanding a single drive failure. That means, a single drive failure will not affect all databases.
In the read-only copy of the internet_rentals database, the information must be accessible available even if the internet_rentals database failure occurs.
Recoverability
The four busiest stores have experienced server failures. Because of the failures, business stopped until the company brought the database back online. Great financial losses are caused. Hence the company management has realized that these servers must be available 24 hours a day, seven days a week. Besides, they must automatically fail over if a server fails. All user and system databases on these servers should be fault tolerant. Even at the time that a single server fails, performance must be maintained or improved.
The company must create a database recovery policy. For guaranteeing that database successfully restores, steps that needs to be performed regularly should be included in this policy. It should also include documentation about the different possible types of restores and the costs and benefits of each type of restore are included in the policy.
TECHNICAL REQUIREMENTS
Security
At present all corporate SQL Server computers needs Windows Authentication mode.
All accounts should have as little permissions as possible and necessary.
The rental_agents Windows group should have read, insert, and update permissions on 10 of the tables in the internet_rentals database. No other permissions are required.
In the head office, database administrators must be able to create and manage databases and manage all server settings and security, including logins on SQL01 and SQL02. Server settings can be changed and create database objects can be created on servers in the head office only by these database administrators.
An e-mail message must be generated by any changes that are mode to database objects or the SQL Server configuration on any server. The message is sent to managers and the Windows account name of the user who made the change is inclued.
The five mobile database administrators who are in charge of the servers in the stores must be able to create and manage databases and to manage all SQL Server settings and security, including logins, on all store SQL Server computers.
Reads and writes of sensitive data in several tables in the acct1 database should be tracked. But this should cause the least possible effect on performance.
All data retrieved from SQL01 must be encrypted while on the network.
Maintainability
SQL Server events must be forwarded to SQL02 by SQL01.
SuperX Video is a video rental company. Its head office and 300 video rental branch stores are in North America. It also rents videos over the Internet, with delivery by mail.
Planned Changes
In order to maintain account information for the Internet rentals business, the company intends to develop a new mission-critical accounts database at the head office. At the time that the primary server failure occurs, this database must automatically fail over. Besides, in order to incorrect updates and deletions to be corrected using the information on the additional server, the company plans to configure an additional server. The ability to correcting of data errors should not be hindered by single server failure. Four new server computers will be able to be accessed for this mission-critical accounts database.
In order to enable store employees to view the list of videos that are available at other company stores within 20 miles, the company will implement a new replication system. Store employees should be able to update only their own store's records.
The database named rental_history is quite large. As its size increases, it is becoming slower. After investigation, it is found that the appropriate indexes already exist in this database. The most common report includes the customer name and city from the customer table; the store name, city, and store number from the store table; the rental date from the store table; the rental date from the rental table; and the video name and genre from the videotitle table. According to the indication of query statistics, , the performance on this database are affected by the joins that are required to run the other common reports, which frequently include fields from all tables. The rental_history database forbids any changes to the data. There is enough time to import the data once a month.
EXISTING DATA ENVIRONMENT
Database Servers
Recently all of their SQL Server 2000 computers of the company were upgraded to SQL Server 2005 running on Microsoft Windows Server 2003. The SQL Server computers are described in the table below.
SQL01 and SQL02 are configured for only Windows Authentication mode. All store SQL Server computers are configured for SQL Server and Windows Authentication mode.
To facilitate access by any of five mobile database administrators who are responsible only for managing the store servers, for past two years, the same password for the sa login ID has been used by all store SQL Server computers. If necessary, some nonutilized production level servers can be used.
Databases
On SQL01, the largest table has a clustered index and is about 2 GB in size.
No changes have been made to the default settings for the tempdb database on SQL01.
At present the company has configured each store_nnn_rentals database to use a single
filegroup.
Queries in the internet_rentals database are frequently sorted by the videotitle column.
Database Client Computers
In the stores or in the head office, either Windows XP Professional, Windows 2000 Professional, Windows NT Workstation 4.0, or Windows 98 is run by client computers. All client computers must be able to function with all servers. It is unnecessary to upgrade the client computers at present.
EXISTING INFRASTRUCTURE
Network Infrastructure
High-speed networks connect all stores and the head office.
Active Directory Infrastructure
There is a single Active Directory domain in the SuperX Video network. This domain is
named ad. SuperXvideo.com. All user accounts and servers reside in this domain.
Windows user accounts are used to grant access to the SQL Server computers.
A Windows group named SQLManagers has login privileges on SQL01 and SQL02 and is
a member of the sysadmin role on the two SQL Servers.
Recoverability
At present full backups are performed every night and transaction log backups are performed four times per day on the store_nnn_rentals database in all rental stores.
Full backups of the internet_rentals database are performed every night and transaction log backups are performed every hour, on the hour, from 08:00 to 17:00. A differential backup is performed at 12:00 noon.
Security
On SQL01, the SQL Server Agent service is running as a domain account named SQLService at present. This domain account belongs to the local administrators group and is a member of the sysadmin role.
BUSINESS REQUIREMENTS
General Requirements
It is forbidden to perform development, testing, and error checking on the main production databases.
Performance
In order to improve the performance to the best, the process of building indexes should be optimized.
The company intends to use the SORT_IN_TEMPDB option at the time that all indexes are being created. To support this plan, the company management has agreed to purchase purchase new hard disks.
The internet_rentals database is extremely busy. In order to optimize performance, a separate read-only version of the database should be created to run reports and perform data analysis. This version must be kept synchronized with the writeable version of the internet_rentals database with the possible least latency.
vailability
All databases must have the ability of withstanding a single drive failure. That means, a single drive failure will not affect all databases.
In the read-only copy of the internet_rentals database, the information must be accessible available even if the internet_rentals database failure occurs.
Recoverability
The four busiest stores have experienced server failures. Because of the failures, business stopped until the company brought the database back online. Great financial losses are caused. Hence the company management has realized that these servers must be available 24 hours a day, seven days a week. Besides, they must automatically fail over if a server fails. All user and system databases on these servers should be fault tolerant. Even at the time that a single server fails, performance must be maintained or improved.
The company must create a database recovery policy. For guaranteeing that database successfully restores, steps that needs to be performed regularly should be included in this policy. It should also include documentation about the different possible types of restores and the costs and benefits of each type of restore are included in the policy.
TECHNICAL REQUIREMENTS
Security
At present all corporate SQL Server computers needs Windows Authentication mode.
All accounts should have as little permissions as possible and necessary.
The rental_agents Windows group should have read, insert, and update permissions on 10 of the tables in the internet_rentals database. No other permissions are required.
In the head office, database administrators must be able to create and manage databases and manage all server settings and security, including logins on SQL01 and SQL02. Server settings can be changed and create database objects can be created on servers in the head office only by these database administrators.
An e-mail message must be generated by any changes that are mode to database objects or the SQL Server configuration on any server. The message is sent to managers and the Windows account name of the user who made the change is inclued.
The five mobile database administrators who are in charge of the servers in the stores must be able to create and manage databases and to manage all SQL Server settings and security, including logins, on all store SQL Server computers.
Reads and writes of sensitive data in several tables in the acct1 database should be tracked. But this should cause the least possible effect on performance.
All data retrieved from SQL01 must be encrypted while on the network.
Maintainability
SQL Server events must be forwarded to SQL02 by SQL01.
Question 10
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. There is a database named Inventory on a SQL Server 2005 computer, and the computer is named SQL01. SQL01 is utilized to aggregate manufacturing part numbers from your company's trading partners. And the company stores the manufacturing part numbers in the Product table in the Inventory database. The company requests the users to deliver data as text files from each trading partner to SQL1 every night. The data should be imported, and no duplicate manufacturing part numbers should exist in the data which is imported from the text files, and the text files are stored in the Inventory database on SQL01.
You should cut the effect on your company's trading partners and your IT department to the least. Which action should you perform?
You work as a database administrator for your company. There is a database named Inventory on a SQL Server 2005 computer, and the computer is named SQL01. SQL01 is utilized to aggregate manufacturing part numbers from your company's trading partners. And the company stores the manufacturing part numbers in the Product table in the Inventory database. The company requests the users to deliver data as text files from each trading partner to SQL1 every night. The data should be imported, and no duplicate manufacturing part numbers should exist in the data which is imported from the text files, and the text files are stored in the Inventory database on SQL01.
You should cut the effect on your company's trading partners and your IT department to the least. Which action should you perform?
Correct Answer: B