70-447 無料問題集「Microsoft UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005」
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?
正解：B
解答を投票する
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?
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?
正解：C
解答を投票する
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 recently the server performs gradually slowly. The indexing environment is troubleshooted by you. The clustered indexes are rebuilt by you, and the clustered indexes are utilized the most frequently. The most frequently accessed nonclustered index will be optimized by you. The sys.dm_db_index_usage_stats dynamic management view (DMV) is accessed by you. You can see the results from the table listed below.
When the entire index is not read, the nonclustered index which is being utilized the most frequently should be identified. From the following four object_id and index_id combinations, which one should be utilized to finish the task?
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 recently the server performs gradually slowly. The indexing environment is troubleshooted by you. The clustered indexes are rebuilt by you, and the clustered indexes are utilized the most frequently. The most frequently accessed nonclustered index will be optimized by you. The sys.dm_db_index_usage_stats dynamic management view (DMV) is accessed by you. You can see the results from the table listed below.
When the entire index is not read, the nonclustered index which is being utilized the most frequently should be identified. From the following four object_id and index_id combinations, which one should be utilized to finish the task?
正解：D
解答を投票する
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. In the company, a SQL Server 2005 database experiences frequent blocking. You are asked to minimize the impact that caused by the blocking. Therefore, the database needs to be modified. You must make sure that no dirty reads are allowed. So what action should you perform?
正解：D
解答を投票する
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 user database named Products is included by your SQL Server 2005 computer. SQL Server Integration Services (SSIS) packages are utilized to export data from the Products database to a text file.
You deliver the text file to your company's trading partners by utilizing FTP. You are required to make sure that your SSIS packages can be recovered from the occurrence of a disaster. Which action should be performed to finish the task?
You deliver the text file to your company's trading partners by utilizing FTP. You are required to make sure that your SSIS packages can be recovered from the occurrence of a disaster. Which action should be performed to finish the task?
正解：B
解答を投票する
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 are lots of complex SQL Server Integration Services (SSIS) packages for you. These SSIS packages move data among your servers. They have data imported and exported to and from trading partners of the company. Only after certain conditions are met that exist outside of SQL Server, can the SSIS packages run. A third-party tool to schedule the running of nightly scripts is scheduled to test for these conditions with the use of a third-party tool by your department. SSIS needs to be run from your scripts as seamlessly as possible. So what action should you perform?
正解：A
解答を投票する
正解：
Company Overview Simon Tea Garden is now grown into an international tea vendor which offers tea to
customers from a worldwide. At the beginning, Simon Tea garden started its small business to provide tea to local retailers and restaurants.
The company is based in California, and the head office of the company is in Oakland. The tea garden locates 60 miles to the north.
The users at the tea garden utilize an ASP.NET Web application to get access to the Sales database. The customer utilizes mails or fax to order the tea, and the details are entered manually into the database.
Planned Changes
After heated discussion, the company intends to have a customer relationship management (CPM) application created, and a SQL Server 2005 is utilized by the application. At present, the customer data is held in the Sales database, and the data will be migrated to the new CRM database. In addition, the company plans to add a stored procedure to the Sales database so as to retrieve customer data from the CRM database, and the security credentials of the original caller will be utilized.
To all the company operations, the CRM database will be the most important. And the CustomerData.Customers table will be the most regularly accessed. You can find the definition of the table from the code statement below.
CREATE TABLE CustomerData.Customers
(
Customer ID int PRIMARY KEY CLUSTERED,
FirstName nvarchar (25) NOT NULL,
LastName nvarchar (25) NULL,
MideleName nvarchar (25) NULL
)
During the company's expansion, the company has created small departmental database to have specific kinds of data stored. These databases will be moved to SQL Server 2005 and the company will centralize the database into a single data center which the Sales and CRM databases are contained.
Nowadays, a central IT department is created. A Windows Server Update Services (WSUS) server named WSUSI1 is deployed by the IT department so as to make all computers in the head office keep up with the latest service packs and patches. And the company plans to configure all computers in the head office to have updates downloaded from this server.
Problem Statements Log shipping is utilized to offer a redundant copy of the Sales database on a second server. And the company requires setting this secondary server to permit the swapping of log shipping roles. Log shipping server roles has not been swapped by the two servers.
CURRENT DATA ENVIRONMENT
Databases
The Sales database id set for log shipping. The users can manually re-enter sales orders from the paper forms. Therefore, when the server fails, the risk of minor data loss is tolerable.
The company has three small departmental databases. Only a few users are contained by each of these databases. You can see the databases description from the following table.
Database Servers
At present, the database server has three production SQL Server computers. And the three computers are respectively called SQL01, SQL02, and SQL03.
You can see the function of the three production SQL Server computers from the following table.
In addition, SQL02 will be utilized to have the CRM database hosted.
The three test servers are respectively called SQLTST1, SQLTST2, and SQLTST3. And the three servers are set identically to the respective production server. Transact-SQL scripts are stored on the three test server and the transact-SQL will be utilized to replicate core functionality. Before deploying new code or configuration to the production environment, the company should have the new code or configuration tested by these scripts.
You can see the WSUS computer groups which are contained by the WSUS configuration from the following table.
Database Client Computer
An ASP.NET application is utilized in order to interact with the Sales database. From the computers in the head office, the users can get access to the application, while the authenticated users can access the computers over the Internet at the tea garden.
The code in the ASP.NET application is run as a domain account, and the domain account is called Simon\SalesWeb. Windows authentication is utilized to connect the Sales database.
One of the most frequently utilized pages in the ASP.NET application appends details to retrieve order details. And the OrderID value is entered into a text box to the string below.
"SELECT * FROM SalesData.Order WHERE oRDERid="
Current infrastructure
Network Infrastructure
Windows Server 2003 is run by all the servers, while Windows XP Professional is run by all
client computers.
You can see the network configuration from the diagram below.
Directory Services Infrastructure There is a single Active Directory Domain on the company network. A Group Policy object (GPO) is based on the WSUS administrative template. And the company has the Croup Policy object created and assigned to all computers.
BUSINESS REQUIREMENTS
General Requirements
There is a status field in the Sales order records. And one of the settings listed below is in a status field.
A (received)
B (being processed)
C (delayed)
D (completed)
When delivery id finished, the status will be set to D by utilizing a trigger. Undone orders can be retrieved and updated by users through a view, while the orders which are already finished cannot be viewed by them.
By utilizing a stored procedure, the users can retrieve individual order records. But, with the addition of more and more sales orders, the size of the SalesData.Orders table is becoming larger and larger. The server administrator has required the company to have a data archiving solution created so as to move the orders records which are longer than half and year from the table. But the company wants to have the archived records stored in the same database for users need to retrieve and update them.
Availability
The company plans to create a high-availability strategy to protect the CRM database. And the risk of data loss should be cut to the least, when a server fails. And the automatic failover should be offered by the strategy. And the time that the failover takes shouldn't be longer than the time that it presently takes. The company will allow no additional hardware purchasing for the high-availability strategy.
Recoverability
The company utilizes a full backup every day which is enough to protect the departmental databases. However, the Sales and CRM databases must be backed up according to a schedule, and the schedule is listed below.
TECHNICAL REQUIREMENTS
Security
The user who performs the database backups should be the same one to back up the Windows Server 2003 computers. In order to meet the requirement, the company plans to create a login for the Backup Operators local group as standard in each SQL Server instance.
In addition, the company intends to reduce the risk of SQL injection attack through the ASP.NET application to the least.
Only a small amount of permissions which is essential to do the job will be assigned to the database users.
Maintainability
A proper departmental employee should continue to maintain the departmental database. And the IT department should manage all server-level operations.
The company requests to cut the amount of SQL Server instances to the least. Therefore, the server management overhead will be decreased.
The company requests to manage the server accounts which are utilized by all database services centrally.
The compatibility test with production sever configuration should be successfully performed. And only after that, the company will permit you to apply all service packs and patches. And the service packs and patches deployment should be performed in the shortest time.
Performance
In order to cut the effect of page splits during online transaction processing (OLTP) to the least, the company requests to create all indexes in the Sales and CRM database with a fill factor of 75 percent.
customers from a worldwide. At the beginning, Simon Tea garden started its small business to provide tea to local retailers and restaurants.
The company is based in California, and the head office of the company is in Oakland. The tea garden locates 60 miles to the north.
The users at the tea garden utilize an ASP.NET Web application to get access to the Sales database. The customer utilizes mails or fax to order the tea, and the details are entered manually into the database.
Planned Changes
After heated discussion, the company intends to have a customer relationship management (CPM) application created, and a SQL Server 2005 is utilized by the application. At present, the customer data is held in the Sales database, and the data will be migrated to the new CRM database. In addition, the company plans to add a stored procedure to the Sales database so as to retrieve customer data from the CRM database, and the security credentials of the original caller will be utilized.
To all the company operations, the CRM database will be the most important. And the CustomerData.Customers table will be the most regularly accessed. You can find the definition of the table from the code statement below.
CREATE TABLE CustomerData.Customers
(
Customer ID int PRIMARY KEY CLUSTERED,
FirstName nvarchar (25) NOT NULL,
LastName nvarchar (25) NULL,
MideleName nvarchar (25) NULL
)
During the company's expansion, the company has created small departmental database to have specific kinds of data stored. These databases will be moved to SQL Server 2005 and the company will centralize the database into a single data center which the Sales and CRM databases are contained.
Nowadays, a central IT department is created. A Windows Server Update Services (WSUS) server named WSUSI1 is deployed by the IT department so as to make all computers in the head office keep up with the latest service packs and patches. And the company plans to configure all computers in the head office to have updates downloaded from this server.
Problem Statements Log shipping is utilized to offer a redundant copy of the Sales database on a second server. And the company requires setting this secondary server to permit the swapping of log shipping roles. Log shipping server roles has not been swapped by the two servers.
CURRENT DATA ENVIRONMENT
Databases
The Sales database id set for log shipping. The users can manually re-enter sales orders from the paper forms. Therefore, when the server fails, the risk of minor data loss is tolerable.
The company has three small departmental databases. Only a few users are contained by each of these databases. You can see the databases description from the following table.
Database Servers
At present, the database server has three production SQL Server computers. And the three computers are respectively called SQL01, SQL02, and SQL03.
You can see the function of the three production SQL Server computers from the following table.
In addition, SQL02 will be utilized to have the CRM database hosted.
The three test servers are respectively called SQLTST1, SQLTST2, and SQLTST3. And the three servers are set identically to the respective production server. Transact-SQL scripts are stored on the three test server and the transact-SQL will be utilized to replicate core functionality. Before deploying new code or configuration to the production environment, the company should have the new code or configuration tested by these scripts.
You can see the WSUS computer groups which are contained by the WSUS configuration from the following table.
Database Client Computer
An ASP.NET application is utilized in order to interact with the Sales database. From the computers in the head office, the users can get access to the application, while the authenticated users can access the computers over the Internet at the tea garden.
The code in the ASP.NET application is run as a domain account, and the domain account is called Simon\SalesWeb. Windows authentication is utilized to connect the Sales database.
One of the most frequently utilized pages in the ASP.NET application appends details to retrieve order details. And the OrderID value is entered into a text box to the string below.
"SELECT * FROM SalesData.Order WHERE oRDERid="
Current infrastructure
Network Infrastructure
Windows Server 2003 is run by all the servers, while Windows XP Professional is run by all
client computers.
You can see the network configuration from the diagram below.
Directory Services Infrastructure There is a single Active Directory Domain on the company network. A Group Policy object (GPO) is based on the WSUS administrative template. And the company has the Croup Policy object created and assigned to all computers.
BUSINESS REQUIREMENTS
General Requirements
There is a status field in the Sales order records. And one of the settings listed below is in a status field.
A (received)
B (being processed)
C (delayed)
D (completed)
When delivery id finished, the status will be set to D by utilizing a trigger. Undone orders can be retrieved and updated by users through a view, while the orders which are already finished cannot be viewed by them.
By utilizing a stored procedure, the users can retrieve individual order records. But, with the addition of more and more sales orders, the size of the SalesData.Orders table is becoming larger and larger. The server administrator has required the company to have a data archiving solution created so as to move the orders records which are longer than half and year from the table. But the company wants to have the archived records stored in the same database for users need to retrieve and update them.
Availability
The company plans to create a high-availability strategy to protect the CRM database. And the risk of data loss should be cut to the least, when a server fails. And the automatic failover should be offered by the strategy. And the time that the failover takes shouldn't be longer than the time that it presently takes. The company will allow no additional hardware purchasing for the high-availability strategy.
Recoverability
The company utilizes a full backup every day which is enough to protect the departmental databases. However, the Sales and CRM databases must be backed up according to a schedule, and the schedule is listed below.
TECHNICAL REQUIREMENTS
Security
The user who performs the database backups should be the same one to back up the Windows Server 2003 computers. In order to meet the requirement, the company plans to create a login for the Backup Operators local group as standard in each SQL Server instance.
In addition, the company intends to reduce the risk of SQL injection attack through the ASP.NET application to the least.
Only a small amount of permissions which is essential to do the job will be assigned to the database users.
Maintainability
A proper departmental employee should continue to maintain the departmental database. And the IT department should manage all server-level operations.
The company requests to cut the amount of SQL Server instances to the least. Therefore, the server management overhead will be decreased.
The company requests to manage the server accounts which are utilized by all database services centrally.
The compatibility test with production sever configuration should be successfully performed. And only after that, the company will permit you to apply all service packs and patches. And the service packs and patches deployment should be performed in the shortest time.
Performance
In order to cut the effect of page splits during online transaction processing (OLTP) to the least, the company requests to create all indexes in the Sales and CRM database with a fill factor of 75 percent.
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 user in the Windows SalesManagers group, and he is called Simon. A Reporting Services report is required by Simon to have annual sales information displayed by territory. There is another user in the Windows Sales group, and he is called Jack. He is responsible to report to Simon. A report is required by Jack, and the report is limited to his sales territory, but the same detailed sales data is still displayed, and the detailed sales data is on Simon's report. A secure solution should be designed to meet the requirements of the users listed above, and the least amount of server resources should be utilized. Which action should be performed?
正解：B
解答を投票する