Company Overview Simon Tea Garden is now grown into an international tea vendor which offers tea to
customers from a worldwide. At the beginning, Simon Tea garden started its small business to provide tea to local retailers and restaurants.
The company is based in California, and the head office of the company is in Oakland. The tea garden locates 60 miles to the north.
The users at the tea garden utilize an ASP.NET Web application to get access to the Sales database. The customer utilizes mails or fax to order the tea, and the details are entered manually into the database.
Planned Changes
After heated discussion, the company intends to have a customer relationship management (CPM) application created, and a SQL Server 2005 is utilized by the application. At present, the customer data is held in the Sales database, and the data will be migrated to the new CRM database. In addition, the company plans to add a stored procedure to the Sales database so as to retrieve customer data from the CRM database, and the security credentials of the original caller will be utilized.
To all the company operations, the CRM database will be the most important. And the CustomerData.Customers table will be the most regularly accessed. You can find the definition of the table from the code statement below.
CREATE TABLE CustomerData.Customers
(
Customer ID int PRIMARY KEY CLUSTERED,
FirstName nvarchar (25) NOT NULL,
LastName nvarchar (25) NULL,
MideleName nvarchar (25) NULL
)
During the company's expansion, the company has created small departmental database to have specific kinds of data stored. These databases will be moved to SQL Server 2005 and the company will centralize the database into a single data center which the Sales and CRM databases are contained.
Nowadays, a central IT department is created. A Windows Server Update Services (WSUS) server named WSUSI1 is deployed by the IT department so as to make all computers in the head office keep up with the latest service packs and patches. And the company plans to configure all computers in the head office to have updates downloaded from this server.
Problem Statements Log shipping is utilized to offer a redundant copy of the Sales database on a second server. And the company requires setting this secondary server to permit the swapping of log shipping roles. Log shipping server roles has not been swapped by the two servers.
CURRENT DATA ENVIRONMENT
Databases
The Sales database id set for log shipping. The users can manually re-enter sales orders from the paper forms. Therefore, when the server fails, the risk of minor data loss is tolerable.
The company has three small departmental databases. Only a few users are contained by each of these databases. You can see the databases description from the following table.
Database Servers
At present, the database server has three production SQL Server computers. And the three computers are respectively called SQL01, SQL02, and SQL03.
You can see the function of the three production SQL Server computers from the following table.
In addition, SQL02 will be utilized to have the CRM database hosted.
The three test servers are respectively called SQLTST1, SQLTST2, and SQLTST3. And the three servers are set identically to the respective production server. Transact-SQL scripts are stored on the three test server and the transact-SQL will be utilized to replicate core functionality. Before deploying new code or configuration to the production environment, the company should have the new code or configuration tested by these scripts.
You can see the WSUS computer groups which are contained by the WSUS configuration from the following table.
Database Client Computer
An ASP.NET application is utilized in order to interact with the Sales database. From the computers in the head office, the users can get access to the application, while the authenticated users can access the computers over the Internet at the tea garden.
The code in the ASP.NET application is run as a domain account, and the domain account is called Simon\SalesWeb. Windows authentication is utilized to connect the Sales database.
One of the most frequently utilized pages in the ASP.NET application appends details to retrieve order details. And the OrderID value is entered into a text box to the string below.
"SELECT * FROM SalesData.Order WHERE oRDERid="
Current infrastructure
Network Infrastructure
Windows Server 2003 is run by all the servers, while Windows XP Professional is run by all
client computers.
You can see the network configuration from the diagram below.
Directory Services Infrastructure There is a single Active Directory Domain on the company network. A Group Policy object (GPO) is based on the WSUS administrative template. And the company has the Croup Policy object created and assigned to all computers.
BUSINESS REQUIREMENTS
General Requirements
There is a status field in the Sales order records. And one of the settings listed below is in a status field.
A (received)
B (being processed)
C (delayed)
D (completed)
When delivery id finished, the status will be set to D by utilizing a trigger. Undone orders can be retrieved and updated by users through a view, while the orders which are already finished cannot be viewed by them.
By utilizing a stored procedure, the users can retrieve individual order records. But, with the addition of more and more sales orders, the size of the SalesData.Orders table is becoming larger and larger. The server administrator has required the company to have a data archiving solution created so as to move the orders records which are longer than half and year from the table. But the company wants to have the archived records stored in the same database for users need to retrieve and update them.
Availability
The company plans to create a high-availability strategy to protect the CRM database. And the risk of data loss should be cut to the least, when a server fails. And the automatic failover should be offered by the strategy. And the time that the failover takes shouldn't be longer than the time that it presently takes. The company will allow no additional hardware purchasing for the high-availability strategy.
Recoverability
The company utilizes a full backup every day which is enough to protect the departmental databases. However, the Sales and CRM databases must be backed up according to a schedule, and the schedule is listed below.
TECHNICAL REQUIREMENTS
Security
The user who performs the database backups should be the same one to back up the Windows Server 2003 computers. In order to meet the requirement, the company plans to create a login for the Backup Operators local group as standard in each SQL Server instance.
In addition, the company intends to reduce the risk of SQL injection attack through the ASP.NET application to the least.
Only a small amount of permissions which is essential to do the job will be assigned to the database users.
Maintainability
A proper departmental employee should continue to maintain the departmental database. And the IT department should manage all server-level operations.
The company requests to cut the amount of SQL Server instances to the least. Therefore, the server management overhead will be decreased.
The company requests to manage the server accounts which are utilized by all database services centrally.
The compatibility test with production sever configuration should be successfully performed. And only after that, the company will permit you to apply all service packs and patches. And the service packs and patches deployment should be performed in the shortest time.
Performance
In order to cut the effect of page splits during online transaction processing (OLTP) to the least, the company requests to create all indexes in the Sales and CRM database with a fill factor of 75 percent.
