Company Overview
SuperX Video is a video rental company. Its head office and 300 video rental branch stores are in North America. It also rents videos over the Internet, with delivery by mail.
Planned Changes
In order to maintain account information for the Internet rentals business, the company intends to develop a new mission-critical accounts database at the head office. At the time that the primary server failure occurs, this database must automatically fail over. Besides, in order to incorrect updates and deletions to be corrected using the information on the additional server, the company plans to configure an additional server. The ability to correcting of data errors should not be hindered by single server failure. Four new server computers will be able to be accessed for this mission-critical accounts database.
In order to enable store employees to view the list of videos that are available at other company stores within 20 miles, the company will implement a new replication system. Store employees should be able to update only their own store's records.
The database named rental_history is quite large. As its size increases, it is becoming slower. After investigation, it is found that the appropriate indexes already exist in this database. The most common report includes the customer name and city from the customer table; the store name, city, and store number from the store table; the rental date from the store table; the rental date from the rental table; and the video name and genre from the videotitle table. According to the indication of query statistics, , the performance on this database are affected by the joins that are required to run the other common reports, which frequently include fields from all tables. The rental_history database forbids any changes to the data. There is enough time to import the data once a month.
EXISTING DATA ENVIRONMENT
Database Servers
Recently all of their SQL Server 2000 computers of the company were upgraded to SQL Server 2005 running on Microsoft Windows Server 2003. The SQL Server computers are described in the table below.
SQL01 and SQL02 are configured for only Windows Authentication mode. All store SQL Server computers are configured for SQL Server and Windows Authentication mode.
To facilitate access by any of five mobile database administrators who are responsible only for managing the store servers, for past two years, the same password for the sa login ID has been used by all store SQL Server computers. If necessary, some nonutilized production level servers can be used.
Databases
On SQL01, the largest table has a clustered index and is about 2 GB in size.
No changes have been made to the default settings for the tempdb database on SQL01.
At present the company has configured each store_nnn_rentals database to use a single
filegroup.
Queries in the internet_rentals database are frequently sorted by the videotitle column.
Database Client Computers
In the stores or in the head office, either Windows XP Professional, Windows 2000 Professional, Windows NT Workstation 4.0, or Windows 98 is run by client computers. All client computers must be able to function with all servers. It is unnecessary to upgrade the client computers at present.
EXISTING INFRASTRUCTURE
Network Infrastructure
High-speed networks connect all stores and the head office.
Active Directory Infrastructure
There is a single Active Directory domain in the SuperX Video network. This domain is
named ad. SuperXvideo.com. All user accounts and servers reside in this domain.
Windows user accounts are used to grant access to the SQL Server computers.
A Windows group named SQLManagers has login privileges on SQL01 and SQL02 and is
a member of the sysadmin role on the two SQL Servers.
Recoverability
At present full backups are performed every night and transaction log backups are performed four times per day on the store_nnn_rentals database in all rental stores.
Full backups of the internet_rentals database are performed every night and transaction log backups are performed every hour, on the hour, from 08:00 to 17:00. A differential backup is performed at 12:00 noon.
Security
On SQL01, the SQL Server Agent service is running as a domain account named SQLService at present. This domain account belongs to the local administrators group and is a member of the sysadmin role.
BUSINESS REQUIREMENTS
General Requirements
It is forbidden to perform development, testing, and error checking on the main production databases.
Performance
In order to improve the performance to the best, the process of building indexes should be optimized.
The company intends to use the SORT_IN_TEMPDB option at the time that all indexes are being created. To support this plan, the company management has agreed to purchase purchase new hard disks.
The internet_rentals database is extremely busy. In order to optimize performance, a separate read-only version of the database should be created to run reports and perform data analysis. This version must be kept synchronized with the writeable version of the internet_rentals database with the possible least latency.
vailability
All databases must have the ability of withstanding a single drive failure. That means, a single drive failure will not affect all databases.
In the read-only copy of the internet_rentals database, the information must be accessible available even if the internet_rentals database failure occurs.
Recoverability
The four busiest stores have experienced server failures. Because of the failures, business stopped until the company brought the database back online. Great financial losses are caused. Hence the company management has realized that these servers must be available 24 hours a day, seven days a week. Besides, they must automatically fail over if a server fails. All user and system databases on these servers should be fault tolerant. Even at the time that a single server fails, performance must be maintained or improved.
The company must create a database recovery policy. For guaranteeing that database successfully restores, steps that needs to be performed regularly should be included in this policy. It should also include documentation about the different possible types of restores and the costs and benefits of each type of restore are included in the policy.
TECHNICAL REQUIREMENTS
Security
At present all corporate SQL Server computers needs Windows Authentication mode.
All accounts should have as little permissions as possible and necessary.
The rental_agents Windows group should have read, insert, and update permissions on 10 of the tables in the internet_rentals database. No other permissions are required.
In the head office, database administrators must be able to create and manage databases and manage all server settings and security, including logins on SQL01 and SQL02. Server settings can be changed and create database objects can be created on servers in the head office only by these database administrators.
An e-mail message must be generated by any changes that are mode to database objects or the SQL Server configuration on any server. The message is sent to managers and the Windows account name of the user who made the change is inclued.
The five mobile database administrators who are in charge of the servers in the stores must be able to create and manage databases and to manage all SQL Server settings and security, including logins, on all store SQL Server computers.
Reads and writes of sensitive data in several tables in the acct1 database should be tracked. But this should cause the least possible effect on performance.
All data retrieved from SQL01 must be encrypted while on the network.
Maintainability
SQL Server events must be forwarded to SQL02 by SQL01.
