최신Microsoft UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005 - 70-447무료샘플문제
문제1
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)
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,C
문제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 a SQL Server 2005 computer named SQL01. There are five databases in the company. All of them run on a single SQL Server 2005 instance. In order to minimize the amount of time required and the amount of space used in the user databases when creating and rebuilding indexes, all indexes are created with the use of the SORT_IN_TEMPDB option. You are asked to design the tempdb database for optimal performance. What action should you perform?
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 a SQL Server 2005 computer named SQL01. There are five databases in the company. All of them run on a single SQL Server 2005 instance. In order to minimize the amount of time required and the amount of space used in the user databases when creating and rebuilding indexes, all indexes are created with the use of the SORT_IN_TEMPDB option. You are asked to design the tempdb database for optimal performance. What action should you perform?
정답: A
문제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 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?
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?
정답: B
문제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 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?
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
문제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?
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?
정답: B
문제6
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?
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?
정답: B
문제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 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 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?
정답: C
문제8
정답:
Company Overview
Wiikigo Corporation is an independent company which specializes in vending software and it enjoys a worldwide customer base. The software is sold by a combination of a e-commerce Web site and a telephone-based ordering system.
Planned Changes
All the database servers of the company will be upgraded to SQL Server 2005. The company plans to have the BusinessData database redesigned to enhance the manageability. In addition, the company intends to create a new database to allow marketing analysis to engender reports from sales data, and the new database is called MarketingAnalysis. The names of the products which are sold and the cities where the customers live will be retrieved by the most common report which will be generated.
Problem Statements
The security of the company data should be enhanced and the risk of unauthorized data access or malicious attack should be cut to the least.
The database sometimes could not be accessed for an extended period of time, therefore, the order cannot be placed by the customer.
CURRENT DATA ENVIRONMENT
Databases
The employee data is included by the HRData database. And the sensitive information such as salary and employee reviews is contained by the employee data.
Product and sales data is included by the BusinessData database. It is shown from an analysis that only a subset of the tables which is associated with the department in which the user works in the database is typically accessed by the users.
And it is shown from the business analysis that every day in the BusinessData database, about 4,000 new records will be added. In addition, after the records are entered, 90 percent of the records are not changed.
At present, there is no managed object in any database.
Database Servers
At present, there is a single default instance of SQL Server 2000 in the company. And the SQL Server 2000 stores the HRData database and the BusinessData database. The server on which the SQL Server instance is installed is called SQL01.
According to the written company policy, merely default instances of the SQL Server should be utilized and no named isnatnce should be installed.
After heated discussion, a hardware RAID solution should be deployed in the next six months. Nevertheless, the company wants no disk redundancy implemented at present.
Database Client Computers
The users get access to data in the HRData through an ASP.NET application. In the security context of a Windows user named HRApp, the application is run. And Windows Authentication is utilized to get connection to SQL Server.
The users in the sales department access the BusinessData database. And Windows Authentication is utilized to connect to SQL Server. Besides that, the users in the software development team require accessing the BusnessData database from client computers, and the computers run a Microsoft Windows operating system or another operating system. SQL Server Authentication is utilized by users on client computers to connect to SQL Server.
The users are not required to browse for a server because all client applications are set to connect to the SQL Server instance.
CURRENT INFRASTRUCTURE
Network Infrastructure
A TCP/IP-based network is connected by all computers. You can see the network configuration from the following diagram.
The company utilizes network cables to have computers in the sales call center connected to the network.
Directory Services Infrastructure
There is one Active Directory domain on the company network. And Windows 2000 Server computer and Windows Server 2003 computers are included by the domain. Client computers that run Windows Professional are utilized by most of the employees. And
client computers that do not run Windows are utilized by some developers.
The company has a domain-level policy enabled so as to enforce password complexity and
expiration after a month.
Server Infrastructure
Besides SQL01, two unutilized file and print servers can be utilized so as to upgrade to
SQL Server 2005.
You can see the specification for all database servers from the table listed below.
The latest service pack is applied in all Windows Server 2003.
BUSINESS REQUIREMENTS
General Requirements
The acquisition of new hardware or servers should not be required by the upgrade project. Nevertheless, the memory, processors, and software in the current servers are allowed for upgrading when it is essential.
Performance
The performance levels of the existing implementations should be met or exceeded by the upgraded HRData database and the redesigned BusinessData database. It should be fulfilled unless the performance is affected negatively by the new security-related functionality. What's more, the performance effect by security-related enhancement should be cut to the least.
A partitioned table should be utilized so as to enhance query performance. The company wants to name the tables as Sales.orders. And the following three partitions should be included. The first one is the partition for orders which are placed in the present month. The second one is the partition for orders which are placed in the last month. And the last orders should be the one for orders which are placed in the month prior to the last month. In addition, the company wants to have the orders which are longer than data in the three partitions archived in a second partitioned table named Sales.OrderArchive. And Sales.OrderArchive should contain the following two partitions: the first is for archived order data, and the second is for an empty partition which will be utilized as staging area for the archival process.
The company will utilize the procedure listed below to archive the order data at the end of each month.
First, a SWITAH operation should be utilized to remove the data in partition 1 of the Sales.Order table to the free partition 2 of the Sales.OrderArchive table.
Second, MERGE and SPLIT operations should be utilized so as to reorganize the data in the Sales.Orders table. As a result, the last month's data will be in partition 1, this month's data will be in partition 2, while the next month's data can be in partition 3 for it is free now.
Three, MERGE and SPLIT operations should be utilized so as to remove all archived data to partition 1 of Sales.OrderArchive table, and a new free partition 2 should be created to prepare for the next month's archival process.
The most speedy query performance should be offered by the new MarketingAnalysis database, while the data for the most commonly generated reports is retrieved.
Availability When a server fails, the BusinessData database should be made accessible in the shortest time, and the least amount of the administrative intervention should be utilized. To accomplish the goal, database mirroring should be utilized.
In common conditions, no data loss should happen when s server fails. But in some exceptions, a small number of data losses can be allowed.
The company wants to implement an interim disk-availability solution for SQL01 after implementing the hardware Raid solution. Fault tolerance should be offered by the interim solution in the event of a single disk which database is contained. The time that required recovering a system disk failure should be cut to the least by the interim solution. In addition, the highest level of read performance should be offered, and the general application solution requirements should be met.
Recoverability
The following requirements should be satisfied by the backup and recovery strategy for the BusinessData database.
First, full recovery should be permitted when total hardware fails.
Second, performance overhead which is caused by backups and restore operations should be cut to the least.
Third, the database should be permitted to be restored to a specific point in time.
Four, the risk of data loss caused by damaged backup media should be reduced to the least.
At last, the contingency plans should be included to handle file corruption.
TECHNICAL REQUIREMENTS
Security
Complex passwords should be utilized by all security credentials which are utilized in all of the company's IT system, and the passwords should be expired after a month.
The company wants to encrypt sensitive data in the HRData database, and the data can only be decrypted by the login that encrypted it. And the company requires having the risk of the unauthorized access to encryption keys and passwords within the database server reduced to the least.
In addition, the company wants to encrypt all data that is passed across the network. The company wants to have an HTTP endpoint in the BusinessData utilized to offer access to two stored procedures called Sales.AddOrder and Sales.GetOrderDetails. Only the sales employees who utilize Windows XP Professional computers in the sales subnet can get access to the HTTP endpoint.
The company intends to cut the attack surface area of the database servers to the least so as to reduce the risk of security violation.
Maintainability
The company intends to implement the BusinessData database so as to make sure that they don't need to rename, drop and re-create an object when the user that created the object is dropped.
The number of the permissions should be cut to the least by the database design, and the permissions should be granted on individual database objects.
The dbo user creates all database objects. And then the built-in backup features of SQL Server should be utilized so as to perform all backups. And then, no third-party applications should require access to the SQL Server files. The scheduled tasks should be utilized so as to cut the administrative intervention to the least. And the scheduled task is created by utilizing the Database Maintenance wizard.
Through scheduled SQL Server Integration Services (SSIS) tasks, the company wants to automate the transfer of data from the BusinessData database to the MarketingAnalysis database.
Wiikigo Corporation is an independent company which specializes in vending software and it enjoys a worldwide customer base. The software is sold by a combination of a e-commerce Web site and a telephone-based ordering system.
Planned Changes
All the database servers of the company will be upgraded to SQL Server 2005. The company plans to have the BusinessData database redesigned to enhance the manageability. In addition, the company intends to create a new database to allow marketing analysis to engender reports from sales data, and the new database is called MarketingAnalysis. The names of the products which are sold and the cities where the customers live will be retrieved by the most common report which will be generated.
Problem Statements
The security of the company data should be enhanced and the risk of unauthorized data access or malicious attack should be cut to the least.
The database sometimes could not be accessed for an extended period of time, therefore, the order cannot be placed by the customer.
CURRENT DATA ENVIRONMENT
Databases
The employee data is included by the HRData database. And the sensitive information such as salary and employee reviews is contained by the employee data.
Product and sales data is included by the BusinessData database. It is shown from an analysis that only a subset of the tables which is associated with the department in which the user works in the database is typically accessed by the users.
And it is shown from the business analysis that every day in the BusinessData database, about 4,000 new records will be added. In addition, after the records are entered, 90 percent of the records are not changed.
At present, there is no managed object in any database.
Database Servers
At present, there is a single default instance of SQL Server 2000 in the company. And the SQL Server 2000 stores the HRData database and the BusinessData database. The server on which the SQL Server instance is installed is called SQL01.
According to the written company policy, merely default instances of the SQL Server should be utilized and no named isnatnce should be installed.
After heated discussion, a hardware RAID solution should be deployed in the next six months. Nevertheless, the company wants no disk redundancy implemented at present.
Database Client Computers
The users get access to data in the HRData through an ASP.NET application. In the security context of a Windows user named HRApp, the application is run. And Windows Authentication is utilized to get connection to SQL Server.
The users in the sales department access the BusinessData database. And Windows Authentication is utilized to connect to SQL Server. Besides that, the users in the software development team require accessing the BusnessData database from client computers, and the computers run a Microsoft Windows operating system or another operating system. SQL Server Authentication is utilized by users on client computers to connect to SQL Server.
The users are not required to browse for a server because all client applications are set to connect to the SQL Server instance.
CURRENT INFRASTRUCTURE
Network Infrastructure
A TCP/IP-based network is connected by all computers. You can see the network configuration from the following diagram.
The company utilizes network cables to have computers in the sales call center connected to the network.
Directory Services Infrastructure
There is one Active Directory domain on the company network. And Windows 2000 Server computer and Windows Server 2003 computers are included by the domain. Client computers that run Windows Professional are utilized by most of the employees. And
client computers that do not run Windows are utilized by some developers.
The company has a domain-level policy enabled so as to enforce password complexity and
expiration after a month.
Server Infrastructure
Besides SQL01, two unutilized file and print servers can be utilized so as to upgrade to
SQL Server 2005.
You can see the specification for all database servers from the table listed below.
The latest service pack is applied in all Windows Server 2003.
BUSINESS REQUIREMENTS
General Requirements
The acquisition of new hardware or servers should not be required by the upgrade project. Nevertheless, the memory, processors, and software in the current servers are allowed for upgrading when it is essential.
Performance
The performance levels of the existing implementations should be met or exceeded by the upgraded HRData database and the redesigned BusinessData database. It should be fulfilled unless the performance is affected negatively by the new security-related functionality. What's more, the performance effect by security-related enhancement should be cut to the least.
A partitioned table should be utilized so as to enhance query performance. The company wants to name the tables as Sales.orders. And the following three partitions should be included. The first one is the partition for orders which are placed in the present month. The second one is the partition for orders which are placed in the last month. And the last orders should be the one for orders which are placed in the month prior to the last month. In addition, the company wants to have the orders which are longer than data in the three partitions archived in a second partitioned table named Sales.OrderArchive. And Sales.OrderArchive should contain the following two partitions: the first is for archived order data, and the second is for an empty partition which will be utilized as staging area for the archival process.
The company will utilize the procedure listed below to archive the order data at the end of each month.
First, a SWITAH operation should be utilized to remove the data in partition 1 of the Sales.Order table to the free partition 2 of the Sales.OrderArchive table.
Second, MERGE and SPLIT operations should be utilized so as to reorganize the data in the Sales.Orders table. As a result, the last month's data will be in partition 1, this month's data will be in partition 2, while the next month's data can be in partition 3 for it is free now.
Three, MERGE and SPLIT operations should be utilized so as to remove all archived data to partition 1 of Sales.OrderArchive table, and a new free partition 2 should be created to prepare for the next month's archival process.
The most speedy query performance should be offered by the new MarketingAnalysis database, while the data for the most commonly generated reports is retrieved.
Availability When a server fails, the BusinessData database should be made accessible in the shortest time, and the least amount of the administrative intervention should be utilized. To accomplish the goal, database mirroring should be utilized.
In common conditions, no data loss should happen when s server fails. But in some exceptions, a small number of data losses can be allowed.
The company wants to implement an interim disk-availability solution for SQL01 after implementing the hardware Raid solution. Fault tolerance should be offered by the interim solution in the event of a single disk which database is contained. The time that required recovering a system disk failure should be cut to the least by the interim solution. In addition, the highest level of read performance should be offered, and the general application solution requirements should be met.
Recoverability
The following requirements should be satisfied by the backup and recovery strategy for the BusinessData database.
First, full recovery should be permitted when total hardware fails.
Second, performance overhead which is caused by backups and restore operations should be cut to the least.
Third, the database should be permitted to be restored to a specific point in time.
Four, the risk of data loss caused by damaged backup media should be reduced to the least.
At last, the contingency plans should be included to handle file corruption.
TECHNICAL REQUIREMENTS
Security
Complex passwords should be utilized by all security credentials which are utilized in all of the company's IT system, and the passwords should be expired after a month.
The company wants to encrypt sensitive data in the HRData database, and the data can only be decrypted by the login that encrypted it. And the company requires having the risk of the unauthorized access to encryption keys and passwords within the database server reduced to the least.
In addition, the company wants to encrypt all data that is passed across the network. The company wants to have an HTTP endpoint in the BusinessData utilized to offer access to two stored procedures called Sales.AddOrder and Sales.GetOrderDetails. Only the sales employees who utilize Windows XP Professional computers in the sales subnet can get access to the HTTP endpoint.
The company intends to cut the attack surface area of the database servers to the least so as to reduce the risk of security violation.
Maintainability
The company intends to implement the BusinessData database so as to make sure that they don't need to rename, drop and re-create an object when the user that created the object is dropped.
The number of the permissions should be cut to the least by the database design, and the permissions should be granted on individual database objects.
The dbo user creates all database objects. And then the built-in backup features of SQL Server should be utilized so as to perform all backups. And then, no third-party applications should require access to the SQL Server files. The scheduled tasks should be utilized so as to cut the administrative intervention to the least. And the scheduled task is created by utilizing the Database Maintenance wizard.
Through scheduled SQL Server Integration Services (SSIS) tasks, the company wants to automate the transfer of data from the BusinessData database to the MarketingAnalysis database.
문제9
According to the company requirement, a high-availability solution should be implemented for SQL06 and SQL07, the company assigns this task to you. You have to achieve this goal with using as little expense as possible. What action should you perform to accomplish this task?
According to the company requirement, a high-availability solution should be implemented for SQL06 and SQL07, the company assigns this task to you. You have to achieve this goal with using as little expense as possible. What action should you perform to accomplish this task?
정답: B
문제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 are a database administrator for your company. The company's manufacturing floor has three automated testing areas for finished items. Each testing area uses SQL Server 2005 to store test results. You need to develop a replication strategy to ensure that test results flow continuously from the testing areas to the central SQL Server as quickly as possible. The test results are used for reporting. What should you do?
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 are a database administrator for your company. The company's manufacturing floor has three automated testing areas for finished items. Each testing area uses SQL Server 2005 to store test results. You need to develop a replication strategy to ensure that test results flow continuously from the testing areas to the central SQL Server as quickly as possible. The test results are used for reporting. What should you do?
정답: B