QUESTION NO: 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 the database administrator for your company. You are in charge of managing 10 SQL Server 2005 computers in the company. Microsoft Windows Server 2003, Enterprise Edition is run by all the ten computers. All Group Policy object (GPO) deployments are handled by Microsoft Active Directory administrators of the company. A security template named SQL05_Security.inf has been deployed by the Active Directory administrators. The template has the appropriate settings to meet the security policy of the company. In order to make sure that the security policy is met, you have to identify the existing configuration of each SQL Server 2005 computer. 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 are in charge of managing 10 SQL Server 2005 computers in the company. Microsoft Windows Server 2003, Enterprise Edition is run by all the ten computers. All Group Policy object (GPO) deployments are handled by Microsoft Active Directory administrators of the company. A security template named SQL05_Security.inf has been deployed by the Active Directory administrators. The template has the appropriate settings to meet the security policy of the company. In order to make sure that the security policy is met, you have to identify the existing configuration of each SQL Server 2005 computer. What action should you perform?
Correct Answer: A
Vote an answer
QUESTION NO: 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 administer a SQL Server 2005 computer named SQL01. Three instances of SQL Server are run by SQL01. Your company's disaster recovery plan of the company contains the ability to connect to the dedicated administrative connection on each instance of SQL Server. While testing this ability, you find that you are able to connect to the dedicated administrative connection on SQL1s default instance. But you fail to connect to the dedicated administrative connection on the two other instances on SQL01. You are sure that the instances are running and can be accessed by client applications. Besides, you also confirm that the dedicated administrative connection is enabled for all instances. You are sure that no other administrators are trying to connect to any dedicated administrative connections on SQL01.
You must make sure that you can connect to the dedicated administrative connection on all three instances. What action should you perform to make sure of this?
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. Three instances of SQL Server are run by SQL01. Your company's disaster recovery plan of the company contains the ability to connect to the dedicated administrative connection on each instance of SQL Server. While testing this ability, you find that you are able to connect to the dedicated administrative connection on SQL1s default instance. But you fail to connect to the dedicated administrative connection on the two other instances on SQL01. You are sure that the instances are running and can be accessed by client applications. Besides, you also confirm that the dedicated administrative connection is enabled for all instances. You are sure that no other administrators are trying to connect to any dedicated administrative connections on SQL01.
You must make sure that you can connect to the dedicated administrative connection on all three instances. What action should you perform to make sure of this?
Correct Answer: D
Vote an answer
QUESTION NO: 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 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?
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?
Correct Answer: D
Vote an answer
QUESTION NO: 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. Your job is to manage a SQL Server 2005 database, and there are several indexes in the SQL Server 2005. A stored procedure should be written, and the procedure should be utilized to check the indexes for fragmentation. From the following four Transact-SQL statements, which one should be utilized achieve the goal.
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. Your job is to manage a SQL Server 2005 database, and there are several indexes in the SQL Server 2005. A stored procedure should be written, and the procedure should be utilized to check the indexes for fragmentation. From the following four Transact-SQL statements, which one should be utilized achieve the goal.
Correct Answer: D
Vote an answer
QUESTION NO: 5
Correct Answer:
Company Overview
GoldenGenius is an international corporation which specializes in technical content development. And the major business of the company is to create documentation and training materials for different software vendors.
At present, there are 30 full-time employees working in its head office in Portland. And more than 100 external contract writers are employed by the company, and the writers are working in home in different locations all over the world.
Planned Changes
A new and web-based solution will be implemented by the company so as to simplify the submitting work process for remote contract writers.
At present, the remote contract writers submit the work by e-mail as Microsoft Word document attachments, and the file shares on the file server called FP2 stores the work. After implementing the new solution, the remote contract writers will be permitted to have their work submitted to a Web service in WordML format, and then the work will be stored in an xml column in a new database called goldengenius_projects.
The Web service should be hosted on a current Web server in the perimeter network, and the Web service will be impersonated to gain access to the database server.
You can see the design for the new application solution from the diagram listed below.
Problem Statements
A review of the company's present security policies is required by the management. And the security should be enhanced to the maximum level so as to prevent the company data form unauthorized access or inadvertent deletion.
CURRENT DATA ENVIRONMENT
Databases
At present, there is a single SQL Server 2005 database named goldengenius_biz in GoldenGenius, Inc. and the database is utilized to store the company's financial and personnel data.
Dbo has all objects in the goldengenius_biz database.
Database Servers
There are two database servers in the Portland office. They are a primary server and a warm standby server which is respectively named SQL1 and SQL2. SQL1 is utilized to store the glodengenius_biz database, while SQL2 is utilized to utilize log shipping to replicate the goldengenius_biz database.
When a server fails, the goldengenius_biz database on SQL2 should be able to be brought online manually, and the client application should be simply set to have the standby server utilized.
The hardware on both servers greatly surpasses the existing requests for database performance.
Database Client Computers The goldengenius_biz is available to a managed client/server application, and ADO.NET
2.0 is utilized to access the database server.
CURRENT INFRASTRUCTURE Network Infrastructure From the diagram listed below, you can see there are two subnets in the network.
From the table listed below, you can see the detailed description of the servers included by the network.
Directory Services Infrastructure
There is a single Active Directory named goldengenius.com in the company network.
You can see the relating organizational units(OUS), user accounts, and global groups in the domain from the diagram listed below.
The company defines the accounts for contract writers in the ContractAccts OU, and defines other user accounts in the Users container.
BUSINESS REQUIREMENTS
General Requirements
All existing projects and archived content from previously finished projects should be included by the new goldengenius_projects database. Multiple revisions of a document can be submitted by writers. And when the project is active, all types of the documents should be sustained. When a project is finished, the final version should be handed off to the customer.
The data in archived projects is available to writers and project managers so as to retrieve replications of documents that have already been handed off to customers.
Availability
The company regards the existing availability strategy for the goldengenius_biz database as sufficient, and the strategy should not be modified. However, a level of availability should be provided by the goldengenius_biz database so as to make sure the database will be completely available in the shortest time when a server or a storage array fails. Besides that, the company request to have the risk of the data loss cut to the least.
The company will not allow any new hardware purchase to implement the availability solution. Nevertheless, the presently server which is utilized to store project documents is named FP2. When the new application is implemented, FP2 will be decommissioned. And if it is necessary, the FP2 can be utilized.
The company has to insert a great number of data into the goldengenius_biz database incidentally through a custom load operation, and the custom load operation requests to place the database in single user mode. And during the operation process, the users should be enabled to retrieve project data with no failover to the warm standby server which is utilized for log shipping.
Recoverability
A combination of full, partial, and transaction log backups will be utilized to back up all system and user databases. Since you are the technical support, you are required to create a recovery plan to specify the steps which are essential to utilize the backups when recover from a server failure.
In the event of the corruption or destruction on the goldengenius_project database, the recovery strategy should be optimized so as to make sure of the following two points. First, the filegroups which present project data is included should be recovered.
Second, the database should be made available in the shortest time.
Besides the regularly backups, the goldengenius_biz database should be able to be recovered to its previous state in the event of the error during the manual bulk load process.
TECHNICAL REQUIREMENTS
Security
The logins in the goldengenius_biz database should be managed only by the database administrator.
The SQL Server logins and the access for database users to the new goldengenius_project database should be managed by the database administrator and the project managers.
The employees' salary information will be stored in the goldengenius_biz database. And the information should be prevented from unauthorized access.
The company wants to encrypt all traffic to and from the HTTP endpoint by utilizing SSL, and the HTTP endpoint is utilized by the goldengenius_project.
After the solution is implemented, you should make sure the principle of least privileges should be applied. In another word, only the permissions which are necessary will be granted to the users.
Maintainability
In order to cut the management overhead to the least, the company's database administrator intends to reduce the number of the separate database server to the least.
For improving availability, multiple servers can be utilized. However, when it is probable, the daily maintenance of the database should be primarily put on a single server.
The complete recovery model should be utilized by the planned goldengenius_projects database. And the three filegroups in the table listed below should be included.
GoldenGenius is an international corporation which specializes in technical content development. And the major business of the company is to create documentation and training materials for different software vendors.
At present, there are 30 full-time employees working in its head office in Portland. And more than 100 external contract writers are employed by the company, and the writers are working in home in different locations all over the world.
Planned Changes
A new and web-based solution will be implemented by the company so as to simplify the submitting work process for remote contract writers.
At present, the remote contract writers submit the work by e-mail as Microsoft Word document attachments, and the file shares on the file server called FP2 stores the work. After implementing the new solution, the remote contract writers will be permitted to have their work submitted to a Web service in WordML format, and then the work will be stored in an xml column in a new database called goldengenius_projects.
The Web service should be hosted on a current Web server in the perimeter network, and the Web service will be impersonated to gain access to the database server.
You can see the design for the new application solution from the diagram listed below.
Problem Statements
A review of the company's present security policies is required by the management. And the security should be enhanced to the maximum level so as to prevent the company data form unauthorized access or inadvertent deletion.
CURRENT DATA ENVIRONMENT
Databases
At present, there is a single SQL Server 2005 database named goldengenius_biz in GoldenGenius, Inc. and the database is utilized to store the company's financial and personnel data.
Dbo has all objects in the goldengenius_biz database.
Database Servers
There are two database servers in the Portland office. They are a primary server and a warm standby server which is respectively named SQL1 and SQL2. SQL1 is utilized to store the glodengenius_biz database, while SQL2 is utilized to utilize log shipping to replicate the goldengenius_biz database.
When a server fails, the goldengenius_biz database on SQL2 should be able to be brought online manually, and the client application should be simply set to have the standby server utilized.
The hardware on both servers greatly surpasses the existing requests for database performance.
Database Client Computers The goldengenius_biz is available to a managed client/server application, and ADO.NET
2.0 is utilized to access the database server.
CURRENT INFRASTRUCTURE Network Infrastructure From the diagram listed below, you can see there are two subnets in the network.
From the table listed below, you can see the detailed description of the servers included by the network.
Directory Services Infrastructure
There is a single Active Directory named goldengenius.com in the company network.
You can see the relating organizational units(OUS), user accounts, and global groups in the domain from the diagram listed below.
The company defines the accounts for contract writers in the ContractAccts OU, and defines other user accounts in the Users container.
BUSINESS REQUIREMENTS
General Requirements
All existing projects and archived content from previously finished projects should be included by the new goldengenius_projects database. Multiple revisions of a document can be submitted by writers. And when the project is active, all types of the documents should be sustained. When a project is finished, the final version should be handed off to the customer.
The data in archived projects is available to writers and project managers so as to retrieve replications of documents that have already been handed off to customers.
Availability
The company regards the existing availability strategy for the goldengenius_biz database as sufficient, and the strategy should not be modified. However, a level of availability should be provided by the goldengenius_biz database so as to make sure the database will be completely available in the shortest time when a server or a storage array fails. Besides that, the company request to have the risk of the data loss cut to the least.
The company will not allow any new hardware purchase to implement the availability solution. Nevertheless, the presently server which is utilized to store project documents is named FP2. When the new application is implemented, FP2 will be decommissioned. And if it is necessary, the FP2 can be utilized.
The company has to insert a great number of data into the goldengenius_biz database incidentally through a custom load operation, and the custom load operation requests to place the database in single user mode. And during the operation process, the users should be enabled to retrieve project data with no failover to the warm standby server which is utilized for log shipping.
Recoverability
A combination of full, partial, and transaction log backups will be utilized to back up all system and user databases. Since you are the technical support, you are required to create a recovery plan to specify the steps which are essential to utilize the backups when recover from a server failure.
In the event of the corruption or destruction on the goldengenius_project database, the recovery strategy should be optimized so as to make sure of the following two points. First, the filegroups which present project data is included should be recovered.
Second, the database should be made available in the shortest time.
Besides the regularly backups, the goldengenius_biz database should be able to be recovered to its previous state in the event of the error during the manual bulk load process.
TECHNICAL REQUIREMENTS
Security
The logins in the goldengenius_biz database should be managed only by the database administrator.
The SQL Server logins and the access for database users to the new goldengenius_project database should be managed by the database administrator and the project managers.
The employees' salary information will be stored in the goldengenius_biz database. And the information should be prevented from unauthorized access.
The company wants to encrypt all traffic to and from the HTTP endpoint by utilizing SSL, and the HTTP endpoint is utilized by the goldengenius_project.
After the solution is implemented, you should make sure the principle of least privileges should be applied. In another word, only the permissions which are necessary will be granted to the users.
Maintainability
In order to cut the management overhead to the least, the company's database administrator intends to reduce the number of the separate database server to the least.
For improving availability, multiple servers can be utilized. However, when it is probable, the daily maintenance of the database should be primarily put on a single server.
The complete recovery model should be utilized by the planned goldengenius_projects database. And the three filegroups in the table listed below should be included.
QUESTION NO: 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?
Correct Answer: B
Vote an answer
QUESTION NO: 7
The code is being reviewed, and the code is utilized to retrieve order data in the ASP.NET Web application. The application should be updated to satisfy the security requirements. Which action should you perform?
The code is being reviewed, and the code is utilized to retrieve order data in the ASP.NET Web application. The application should be updated to satisfy the security requirements. Which action should you perform?
Correct Answer: D
Vote an answer
QUESTION NO: 8
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 called SQL01. Three instances of SQL Server are run by the SQL01. After implementing the recovery plan, the dedicated administrative connection is available on each instance of SQL Server. After the ability is tested, a problem has been found that the dedicated administrative connection on SQL01s default instance can be connected, while the dedicated administrative connection on the two other instances on SQL1 cannot be connected. You confirm that the instances are running and the instances can be accessed by the client applications. In addition, the dedicated administrative connection is enabled for all instances. What??s more, there is no connection that is tried to by other administrators to any dedicated administrative connections on SQL1.
Since you are the administrator, you are required to make sure the dedicated administrative connection can be connected on all three instances. To achieve the goal, which action should be utilized?
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 called SQL01. Three instances of SQL Server are run by the SQL01. After implementing the recovery plan, the dedicated administrative connection is available on each instance of SQL Server. After the ability is tested, a problem has been found that the dedicated administrative connection on SQL01s default instance can be connected, while the dedicated administrative connection on the two other instances on SQL1 cannot be connected. You confirm that the instances are running and the instances can be accessed by the client applications. In addition, the dedicated administrative connection is enabled for all instances. What??s more, there is no connection that is tried to by other administrators to any dedicated administrative connections on SQL1.
Since you are the administrator, you are required to make sure the dedicated administrative connection can be connected on all three instances. To achieve the goal, which action should be utilized?
Correct Answer: C
Vote an answer
QUESTION NO: 9
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?
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?
Correct Answer: D
Vote an answer
QUESTION NO: 10
According to the company business and technical requirements, the changes to the existing firewall configuration settings should be specified. And the settings are required by the HTTP endpoint, and the HTTP endpoint will be utilized by the goldengenius_projects database. Which action should be performed?
According to the company business and technical requirements, the changes to the existing firewall configuration settings should be specified. And the settings are required by the HTTP endpoint, and the HTTP endpoint will be utilized by the goldengenius_projects database. Which action should be performed?
Correct Answer: C
Vote an answer