Planned Changes A newly purchased customer relationship management (CRM) application is being installed, and it uses SQL Server 2005 as its data store. Some database backup jobs on a SQL Server computer named CompanyA take too long to run. Additional disk drives will be made available to increase the speed of these backups. The company plans to implement two new SQL Server computers named COMPANY6 and COMPANY7. Problem Statements COMPANY7 is currently being tested with the CRM application. A full database backup of the CRM data takes five hours, even though a fast disk volume is being used for the backups. Database backups on a quality assurance (QA) server named COMPANY5 are occasionally not usable dur to sporadic hardware problems that have not yet be resolved. EXISTING DATA ENVIRONMENT Database Servers The company�s central IT department currently supports four production SQL Server computers, one QA server, and one development server, as shown in the following table The inventory and shipping databases that are located on Company2 and Company3 have identical data base schemas. However, Company2 contains only information that is related to operations in Denver, and Company3 contains only information that is related to operations in Boston. All SQL Server computers were recently upgraded to SQL Server 2005 Enterprise Edition running on Microsoft Windows Server 2003. Company1 stores data on a RAID-5 volume and stores log files on a RAID-1 volume. Database backups from Company1 are stored on the same drives as the log files. All drives are located in local hardware-based RAID storage. The company has not invested in storage area network (SAN) or network attached storage (NAS) technology. The orders application is under ongoing development, and new database objects are added on a regular basis. The marketing database contains a role named marketing-sql-users. Members of this role can access the Marketing application. Users in the research department frequently perform data analysis by using data that is stored in the Marketing and Accounting databases. In the Accounting database, the users access only a few of the larger tables, working with data from prior months. This data needs to be refreshed only on a monthly basis. In the Marketing database, the users access approximately half of the tables, and they read the most current information that is available. The user in the research department have read-only access to all of the information they access in both database. Ninety percent of the space in the Doclib database is allocated for storing research documents as binary data and full-text indexes that are used to search those documents. This database is set to use the simple recovery model and has nightly full backups. The documents in this database are relatively volatile, with three percent of them changing each day. Once per day the company updates the prices that are listed on its Web site by running a stored procedure as a SQL Server Agent job. The stored procedure is owned by a user named Andy, and the stored procedure examines confidential data that is held on COMPANY4. Andy does not own the tables that contain this data. However, Andy is a member of a group that has been granted permission to read this data. Overall performance of queries against the two largest tables in the Orders database has been slow. Investigation reveals that only a few types of Transact-SQL batches account for most of these poorly performing queries. Specifically, the investigation revealed the following results. * Users in the sales department tend to execute a large number of shot-running Transact-SQL batches in the Orders database. These batches perform a high proportion of writes, and they use data that is mostly from the current month. 2005 * By contrast, users in the accounting department execute Transact-SQL batches in the Orders database that are long-running and very read-intensive. These batches use data that is mostly from prior months. EXISTING INFRASTRUCTURE Directory Services Infrastructure The network consists of two Active Directory forests. Each forest contains a single domain. The two domains and the organizational units (OUs) in the wideworldimporters.com domain are shown in the following diagram. Each department is mapped to an OU, and each departmental OU contains a group that has administrative rights for that OU. For example, there is a Marketing OU for the marketing department, and this OU contains a group named marketing-admin that has administrative rights in that OU. The Marketing OU in the wideworldimporters.com domain contains a universal group named marketing-sqlaccess. The server named SQL99 is in the stand-alone dev.wideworldimporters.com Active Directory forest, which shares no trust relationship with the production wideworldimporters.com forest. Quality Assurance The quality assurance (QA) department has a lab with a production-grade server named COMPANY5 that runs SQL Server. The QA department has given approval for COMPANY5 to function as a production server if an existing production server encounters hardware failures. COMPANY5 uses the same Active Directory domain as the production servers. 2005 To investigate data-related problems, the QA department frequently needs to restore temporary copies of the Orders database onto COMPANY5. These restores are usually point-in-times that capture the Orders database as it existed at the end of the prior business day. However, the QA department occasionally needs to investigate problems that involve the current day�s activity. On these occasions, the database administrators take an unscheduled backup of the Orders database for the QA department, and this backup is restored onto COMPANY5. BUSINESS REQUIRMENTS Availability Currently, users in the Denver and Boston processing centers can view inventory data from SQL Server computers at only their respective locations. In the future, all of this data must be made available to users at both locations. The backups on COMPANY7 need to take longer than two hours each evening from Monday through Saturday, so that they will not compete for resource with other jobs that run on those evenings. Point-in-time recovery is required for the CRM application. The CRM database contains six different schemas and is currently configured to use the full recovery model. The orders database on CompanyA needs to be configured for high availability. The QA department wants to be able to leverage this change so that point-in-time images of the Orders database on COMPANY5 can be constructed more easily and efficiently. Recoverability Recover of the Orders database on CompanyA should require no more than 10 RESTORE commands in the event of a database failure, and no more than 10 minutes of work should be lost in the event of hardware failure. The Windows administrators are implementing procedures that will automatically copy backup files to tape after the backups are complete. The disk volumes that store the backups do not need to be fault tolerant. Data Access Occasionally, new employees are not able to access the company�s database for up to two weeks after their start date. Active Directory administrators in each department have time to set up employee access, but only database administrators have permission to give employees access to the database. The company wants all new employees to be able to use the database no later than two days after their start date. TECHNICAL REQUIRMENTS Maintainability Volatile document data in the Doclib database should be backed up more frequently than the current daily backups, without unnecessarily backing up unchanged data. The full-text indexes that are used to search the documents should also be backed up when they change. Long management should be as simple as possible for the Doclic database. Data Security The company�s written security policy states that the change history of all data in the HR database must be audited. The auditing should be done in the database so that the auditing cannot be bypassed by employees who use various programs to work with the data. The audit records must show who performed each change, when the change occurred, and what the actual was prior to and after the change. Because COMPANY4 stores confidential research data, the company needs to ensure that the data is passed to and from this SQL Server computer cannot be read by unauthorized users if the data is intercepted on the network. 2005 Secure Communication To enable users in the Boston and Denver processing centers to view other�s inventory data, a secure communication method must be implemented that allows remote stored procedures to be invoked instead of ad hoc SQL being sent across the network. Portions of the Orders application run on remote client computers that run Linux and that do not support the Microsoft.NET Framework. The communication method that is implemented must also function for those computers. Application Functionality Some of the employees in the marketing department must be able to occasionally run unscheduled pricing updates. The development team wants database snapshots of the HR database to be available on SQL9 Case Study 3, World Wide Importers (Questions) You need to ensure that all communication with COMPANY4 is as secure as possible. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)">

Case Study 3, World Wide Importers, Scenario Company Overview Wide World Importers imports clothing from all over the world. The company sells the clothing to retailers that are located through North America. These retailers place orders based on price lists that are maintained on the Wide World Importers Web Site. The company has 4,500 employees. The company has three offices, which are connected as shown in the following diagram. (The diagram can also be viewed by clicking the Case Study Exhibits button). Planned Changes A newly purchased customer relationship management (CRM) application is being installed, and it uses SQL Server 2005 as its data store. Some database backup jobs on a SQL Server computer named CompanyA take too long to run. Additional disk drives will be made available to increase the speed of these backups. The company plans to implement two new SQL Server computers named COMPANY6 and COMPANY7. Problem Statements COMPANY7 is currently being tested with the CRM application. A full database backup of the CRM data takes five hours, even though a fast disk volume is being used for the backups. Database backups on a quality assurance (QA) server named COMPANY5 are occasionally not usable dur to sporadic hardware problems that have not yet be resolved. EXISTING DATA ENVIRONMENT Database Servers The company�s central IT department currently supports four production SQL Server computers, one QA server, and one development server, as shown in the following table The inventory and shipping databases that are located on Company2 and Company3 have identical data base schemas. However, Company2 contains only information that is related to operations in Denver, and Company3 contains only information that is related to operations in Boston. All SQL Server computers were recently upgraded to SQL Server 2005 Enterprise Edition running on Microsoft Windows Server 2003. Company1 stores data on a RAID-5 volume and stores log files on a RAID-1 volume. Database backups from Company1 are stored on the same drives as the log files. All drives are located in local hardware-based RAID storage. The company has not invested in storage area network (SAN) or network attached storage (NAS) technology. The orders application is under ongoing development, and new database objects are added on a regular basis. The marketing database contains a role named marketing-sql-users. Members of this role can access the Marketing application. Users in the research department frequently perform data analysis by using data that is stored in the Marketing and Accounting databases. In the Accounting database, the users access only a few of the larger tables, working with data from prior months. This data needs to be refreshed only on a monthly basis. In the Marketing database, the users access approximately half of the tables, and they read the most current information that is available. The user in the research department have read-only access to all of the information they access in both database. Ninety percent of the space in the Doclib database is allocated for storing research documents as binary data and full-text indexes that are used to search those documents. This database is set to use the simple recovery model and has nightly full backups. The documents in this database are relatively volatile, with three percent of them changing each day. Once per day the company updates the prices that are listed on its Web site by running a stored procedure as a SQL Server Agent job. The stored procedure is owned by a user named Andy, and the stored procedure examines confidential data that is held on COMPANY4. Andy does not own the tables that contain this data. However, Andy is a member of a group that has been granted permission to read this data. Overall performance of queries against the two largest tables in the Orders database has been slow. Investigation reveals that only a few types of Transact-SQL batches account for most of these poorly performing queries. Specifically, the investigation revealed the following results. * Users in the sales department tend to execute a large number of shot-running Transact-SQL batches in the Orders database. These batches perform a high proportion of writes, and they use data that is mostly from the current month. 2005 * By contrast, users in the accounting department execute Transact-SQL batches in the Orders database that are long-running and very read-intensive. These batches use data that is mostly from prior months. EXISTING INFRASTRUCTURE Directory Services Infrastructure The network consists of two Active Directory forests. Each forest contains a single domain. The two domains and the organizational units (OUs) in the wideworldimporters.com domain are shown in the following diagram. Each department is mapped to an OU, and each departmental OU contains a group that has administrative rights for that OU. For example, there is a Marketing OU for the marketing department, and this OU contains a group named marketing-admin that has administrative rights in that OU. The Marketing OU in the wideworldimporters.com domain contains a universal group named marketing-sqlaccess. The server named SQL99 is in the stand-alone dev.wideworldimporters.com Active Directory forest, which shares no trust relationship with the production wideworldimporters.com forest. Quality Assurance The quality assurance (QA) department has a lab with a production-grade server named COMPANY5 that runs SQL Server. The QA department has given approval for COMPANY5 to function as a production server if an existing production server encounters hardware failures. COMPANY5 uses the same Active Directory domain as the production servers. 2005 To investigate data-related problems, the QA department frequently needs to restore temporary copies of the Orders database onto COMPANY5. These restores are usually point-in-times that capture the Orders database as it existed at the end of the prior business day. However, the QA department occasionally needs to investigate problems that involve the current day�s activity. On these occasions, the database administrators take an unscheduled backup of the Orders database for the QA department, and this backup is restored onto COMPANY5. BUSINESS REQUIRMENTS Availability Currently, users in the Denver and Boston processing centers can view inventory data from SQL Server computers at only their respective locations. In the future, all of this data must be made available to users at both locations. The backups on COMPANY7 need to take longer than two hours each evening from Monday through Saturday, so that they will not compete for resource with other jobs that run on those evenings. Point-in-time recovery is required for the CRM application. The CRM database contains six different schemas and is currently configured to use the full recovery model. The orders database on CompanyA needs to be configured for high availability. The QA department wants to be able to leverage this change so that point-in-time images of the Orders database on COMPANY5 can be constructed more easily and efficiently. Recoverability Recover of the Orders database on CompanyA should require no more than 10 RESTORE commands in the event of a database failure, and no more than 10 minutes of work should be lost in the event of hardware failure. The Windows administrators are implementing procedures that will automatically copy backup files to tape after the backups are complete. The disk volumes that store the backups do not need to be fault tolerant. Data Access Occasionally, new employees are not able to access the company�s database for up to two weeks after their start date. Active Directory administrators in each department have time to set up employee access, but only database administrators have permission to give employees access to the database. The company wants all new employees to be able to use the database no later than two days after their start date. TECHNICAL REQUIRMENTS Maintainability Volatile document data in the Doclib database should be backed up more frequently than the current daily backups, without unnecessarily backing up unchanged data. The full-text indexes that are used to search the documents should also be backed up when they change. Long management should be as simple as possible for the Doclic database. Data Security The company�s written security policy states that the change history of all data in the HR database must be audited. The auditing should be done in the database so that the auditing cannot be bypassed by employees who use various programs to work with the data. The audit records must show who performed each change, when the change occurred, and what the actual was prior to and after the change. Because COMPANY4 stores confidential research data, the company needs to ensure that the data is passed to and from this SQL Server computer cannot be read by unauthorized users if the data is intercepted on the network. 2005 Secure Communication To enable users in the Boston and Denver processing centers to view other�s inventory data, a secure communication method must be implemented that allows remote stored procedures to be invoked instead of ad hoc SQL being sent across the network. Portions of the Orders application run on remote client computers that run Linux and that do not support the Microsoft.NET Framework. The communication method that is implemented must also function for those computers. Application Functionality Some of the employees in the marketing department must be able to occasionally run unscheduled pricing updates. The development team wants database snapshots of the HR database to be available on SQL9 Case Study 3, World Wide Importers (Questions) You need to ensure that all communication with COMPANY4 is as secure as possible. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)


A. Set the Windows IP Security Policy on the SQL Server computers to require security.
B. Use SQL Server Configuration Manager to set the ForceEncryption option.
C. Use SQL Server Configuration Manager to specify that SQL Server will listen on port 443.
D. Require client computers to possess X509 certificates from a trusted certificate authority (CA).

Answer:

****

(Show Answer)

Download



Vendors

  • 3com
  • cisco
  • adobe
  • microsoft
  • emc2
  • hp
  • ibms
  • citrix
  • sun