Microsoft UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005 - 70-447 FREE EXAM DUMPS QUESTIONS & ANSWERS
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 new SQL Server 2005 computer is set by you. There are five hard disks on the computer. Since you are the database administrator, you are required to make sure that the server can still function when a single hard disk fails. What will you do? (Choose more than one.)
Correct Answer: B,E
Vote an answer
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
Vote an answer
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.
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 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: C
Vote an answer
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. You utilize the SP:Recompile event class to run SQL Server Profiler. You find that a great amount of recompiles occurring. The possible causes of the large number of recompiles should be identified. From the flowing four options, which one is the most possible cause of the problem?
Correct Answer: B
Vote an answer
Since you are the technical support, you are asked to improve the performance for the following query in the internet_rentals database. SELECT videotitle, upc_no, retailprice
FROM srvideo.videotitle WHERE releasedate BETWEEN '05-01-2005' AND '05-30-2005'
The code segment below shows the scripts that were originally used to create the existing table and indexes that are used in the query.
create table srvideo.videotitle (videoid int IDENTITY(1,1) primary key nonclustered videotitle nvarchar(100) not null , description nvarchar(255) null , videolanguage nvarchar(50) null , releasedate datetime null , isbn nvarchar(25) , upc_no nvarchar(25) , format nvarchar(25) , cost money , retailprice money) go create clustered index cl_videotitle on srvideo.videotitle (videotitle);
You must not diminish the performance on other SELECT queries that are performed on a regular basis. What action should you perform?
FROM srvideo.videotitle WHERE releasedate BETWEEN '05-01-2005' AND '05-30-2005'
The code segment below shows the scripts that were originally used to create the existing table and indexes that are used in the query.
create table srvideo.videotitle (videoid int IDENTITY(1,1) primary key nonclustered videotitle nvarchar(100) not null , description nvarchar(255) null , videolanguage nvarchar(50) null , releasedate datetime null , isbn nvarchar(25) , upc_no nvarchar(25) , format nvarchar(25) , cost money , retailprice money) go create clustered index cl_videotitle on srvideo.videotitle (videotitle);
You must not diminish the performance on other SELECT queries that are performed on a regular basis. What action should you perform?
Correct Answer: C
Vote an answer
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
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 a database administrator for your company. There is a Data Transformation Services (DTS) package, and the DTS package is stored in SQL Server 2000. And you plans to have the SQL Server 2000 migrated into SQL Server 2005. The DTS package dynamically configures precedence constraints by utilizing complex Microsoft Visual Basic Scripting Edition (VBScript). The SQL Server 2005 Package Migration Wizard is run by you, but the wizard cannot convert the package from SQL Server 2000 to SQL Server 2005.
You are required to make sure that the DTS package runs in SQL Server 2005. And the package should be run in the shortest time. Which action should be utilized?
You work a database administrator for your company. There is a Data Transformation Services (DTS) package, and the DTS package is stored in SQL Server 2000. And you plans to have the SQL Server 2000 migrated into SQL Server 2005. The DTS package dynamically configures precedence constraints by utilizing complex Microsoft Visual Basic Scripting Edition (VBScript). The SQL Server 2005 Package Migration Wizard is run by you, but the wizard cannot convert the package from SQL Server 2000 to SQL Server 2005.
You are required to make sure that the DTS package runs in SQL Server 2005. And the package should be run in the shortest time. Which action should be utilized?
Correct Answer: C
Vote an answer
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
Correct Answer:
Company Overview
STE is a large express company. STE is an acronym for
Satisfied Transport and Express+-. Therefore, it aims to provide satisfied delivery service for America. This company coordinates package deliveries throughout America. It cooperates with multiple regional delivery companies to distribute packages of all sizes. The company resides in St Louis and its regional delivery partners reside in all parts of America.
Planned Changes
Specifications are being written for a new application to manage employee benefits for STE. This application will authenticate the users to SQL Server by using the Active Directory credentials of users. Some of the benefits data is sensitive and should be viewable only from the benefits management application. However, when users issue ad hoc queries against the benefits data by using Microsoft Access, they are permitted to view other, less sensitive benefits data.
In order to cut down expenditures, the company will consolidate the Promotions database and the Partners database onto a single SQL Server computer. Employees of the regional delivery companies use SQL Server-authenticated logins to access both of these databases. The Partners database is accessible to all users who have access to the Promotions database, but not all users of the Partners database are allowed to access the Promotions database. This SQL Server computer should have as few instances as possible while still meeting these requirements.
Problem Statements
Users report that in the Shipment Tracking application, browser makes slow response, which uses the Tracking database. A large number of global temporary tables are used by the Shipment Tracking application. According to the indication of application diagnostics, performance issues in the database server named SQL01 causes the delays. Results of running the Performance MMC snap-in (PerfMon) reveal high values for the following counters in the tempdb database on SQL01:
Log Flush Wait/sec
Log Growths
The main tables in the Orders database on a server named SQL05 frequently experience heavy data modification and heavy report generation at the same time, causing performance problems for the reports due to excessive blocking.
EXISTING DATA ENVIRONMENT
Database Servers
The table below shows the SQL Server computers that are running SQL Server 2005, are shown in the following table.
Weekly full database backups, nightly differential backups, and hourly log backups for all user databases are included in the existing operations schedule. System databases are backed up every night.
Databases
The Orders application uses a single SQL Server-authenticated login named OrdersApp to connect to SQL05.
The Orders database makes use of data encryption, event notification, DDL triggers, CLR stored procedures, and snapshot isolation. The database master key for the Orders database is encrypted using the service master key for SQL05.
At the time that the server starts, the size of tempdb on SQL01 is 50 MB. But it typically increases to about 1.5 GB, growing 50 MB at a time. At present this database currently has a single data file.
At present all historical customer data are hold by the CRM database from the seven latest completed fiscal years plus the current year to date.
EXISTING INFRASTRUCTURE
Network Infrastructure
STE has configured its WAN to allow browser-based extranet access for its regional delivery partners. The diagram below shows the network configuration.
Server Infrastructure
As shown in the following table, the listed protocols are enabled for inbound connections to the servers in the perimeter network.
BUSINESS REQUIREMENTS
General Requirements
Data that is held in the Tracking database must be accessible and can be updated to applications that are run by the regional delivery partners.
With the purpose of research, customer information must be kept in the CRM database for two years. With the purpose of auditing, the company has to keep monthly copies of CRM data for at least five years. This auditing requirement is the only reason the older data must be retained, this is because data older than two years will most likely never be needed. However, if the older data is ever needed, it must be made available, in its entirety, in a database other than CRM so that it can be examined separately from the current data.
Availability
SQL06 has storage area network (SAN) storage, and CRM is supported by SQL06. CRM is an important application that must be available with the least downtime. The ERP application is supported by SQL07. SQL07 must be available with the least downtime.
The company has identified the following availability requirements for SQL06 and SQL07.
Any failover from one SQL Server computer to another should happen automatically.
Each of the servers must be available at an IP address that is the same before and after a failover event.
Performance degradation for the other server cannot be caused by the failure of SQL06 or the failure of SQL07.
Before the initiation of the failover, special data copy operations or other special processing should not be required.
After a failover event, SQL Server Agent jobs must resume running, with no human intervention.
Recoverability
The company must design a process to restore a copy of the Orders database to another SQL Server instance at the end of each month. End-of-month accounting reports will be run by using this copy of the Orders database.
In order to simplify recovery operation, if any type of data loss occur CRM database, no more than five restore operations must be required.
TECHNICAL REQUIREMENTS
Security
According to the requirement of the written security policy of the company, credit card information that is stored in the Orders database should be viewable only within the Orders application.
In the ExtraNet and Partners databases, individual users must use SQL Server-authenticated logins to log in to SQL Server. Password expiration must be enforced on all accounts that can access these databases.
According to the requirement of the written security policy of the company, incoming connections to the perimeter network, and all connections from the regional delivery companies, are only allowed over HTTP and HTTPS.
Performance
For performance reasons, applications of the regional delivery companies must be able to use a local copy of the Tracking database to retrieve and update data. This local copy of Tracking database synchronizes back to the central Tracking database.
Interoperability
Some values in the Tracking database must be populated by reading the Windows registry on a Microsoft Windows Server 2003 application server named AppServer1, which resides in St Louis. In order to keep shipment tracking information up-to-date, these values must be populated once per hour.
STE is a large express company. STE is an acronym for
Satisfied Transport and Express+-. Therefore, it aims to provide satisfied delivery service for America. This company coordinates package deliveries throughout America. It cooperates with multiple regional delivery companies to distribute packages of all sizes. The company resides in St Louis and its regional delivery partners reside in all parts of America.
Planned Changes
Specifications are being written for a new application to manage employee benefits for STE. This application will authenticate the users to SQL Server by using the Active Directory credentials of users. Some of the benefits data is sensitive and should be viewable only from the benefits management application. However, when users issue ad hoc queries against the benefits data by using Microsoft Access, they are permitted to view other, less sensitive benefits data.
In order to cut down expenditures, the company will consolidate the Promotions database and the Partners database onto a single SQL Server computer. Employees of the regional delivery companies use SQL Server-authenticated logins to access both of these databases. The Partners database is accessible to all users who have access to the Promotions database, but not all users of the Partners database are allowed to access the Promotions database. This SQL Server computer should have as few instances as possible while still meeting these requirements.
Problem Statements
Users report that in the Shipment Tracking application, browser makes slow response, which uses the Tracking database. A large number of global temporary tables are used by the Shipment Tracking application. According to the indication of application diagnostics, performance issues in the database server named SQL01 causes the delays. Results of running the Performance MMC snap-in (PerfMon) reveal high values for the following counters in the tempdb database on SQL01:
Log Flush Wait/sec
Log Growths
The main tables in the Orders database on a server named SQL05 frequently experience heavy data modification and heavy report generation at the same time, causing performance problems for the reports due to excessive blocking.
EXISTING DATA ENVIRONMENT
Database Servers
The table below shows the SQL Server computers that are running SQL Server 2005, are shown in the following table.
Weekly full database backups, nightly differential backups, and hourly log backups for all user databases are included in the existing operations schedule. System databases are backed up every night.
Databases
The Orders application uses a single SQL Server-authenticated login named OrdersApp to connect to SQL05.
The Orders database makes use of data encryption, event notification, DDL triggers, CLR stored procedures, and snapshot isolation. The database master key for the Orders database is encrypted using the service master key for SQL05.
At the time that the server starts, the size of tempdb on SQL01 is 50 MB. But it typically increases to about 1.5 GB, growing 50 MB at a time. At present this database currently has a single data file.
At present all historical customer data are hold by the CRM database from the seven latest completed fiscal years plus the current year to date.
EXISTING INFRASTRUCTURE
Network Infrastructure
STE has configured its WAN to allow browser-based extranet access for its regional delivery partners. The diagram below shows the network configuration.
Server Infrastructure
As shown in the following table, the listed protocols are enabled for inbound connections to the servers in the perimeter network.
BUSINESS REQUIREMENTS
General Requirements
Data that is held in the Tracking database must be accessible and can be updated to applications that are run by the regional delivery partners.
With the purpose of research, customer information must be kept in the CRM database for two years. With the purpose of auditing, the company has to keep monthly copies of CRM data for at least five years. This auditing requirement is the only reason the older data must be retained, this is because data older than two years will most likely never be needed. However, if the older data is ever needed, it must be made available, in its entirety, in a database other than CRM so that it can be examined separately from the current data.
Availability
SQL06 has storage area network (SAN) storage, and CRM is supported by SQL06. CRM is an important application that must be available with the least downtime. The ERP application is supported by SQL07. SQL07 must be available with the least downtime.
The company has identified the following availability requirements for SQL06 and SQL07.
Any failover from one SQL Server computer to another should happen automatically.
Each of the servers must be available at an IP address that is the same before and after a failover event.
Performance degradation for the other server cannot be caused by the failure of SQL06 or the failure of SQL07.
Before the initiation of the failover, special data copy operations or other special processing should not be required.
After a failover event, SQL Server Agent jobs must resume running, with no human intervention.
Recoverability
The company must design a process to restore a copy of the Orders database to another SQL Server instance at the end of each month. End-of-month accounting reports will be run by using this copy of the Orders database.
In order to simplify recovery operation, if any type of data loss occur CRM database, no more than five restore operations must be required.
TECHNICAL REQUIREMENTS
Security
According to the requirement of the written security policy of the company, credit card information that is stored in the Orders database should be viewable only within the Orders application.
In the ExtraNet and Partners databases, individual users must use SQL Server-authenticated logins to log in to SQL Server. Password expiration must be enforced on all accounts that can access these databases.
According to the requirement of the written security policy of the company, incoming connections to the perimeter network, and all connections from the regional delivery companies, are only allowed over HTTP and HTTPS.
Performance
For performance reasons, applications of the regional delivery companies must be able to use a local copy of the Tracking database to retrieve and update data. This local copy of Tracking database synchronizes back to the central Tracking database.
Interoperability
Some values in the Tracking database must be populated by reading the Windows registry on a Microsoft Windows Server 2003 application server named AppServer1, which resides in St Louis. In order to keep shipment tracking information up-to-date, these values must be populated once per hour.