Case Study 3, World Wide Importers, Scenario
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
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 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
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
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
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
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.
* 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.
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
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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).