Company Overview
Wiikigo Corporation is an independent company which specializes in vending software and it enjoys a worldwide customer base. The software is sold by a combination of a e-commerce Web site and a telephone-based ordering system.
Planned Changes
All the database servers of the company will be upgraded to SQL Server 2005. The company plans to have the BusinessData database redesigned to enhance the manageability. In addition, the company intends to create a new database to allow marketing analysis to engender reports from sales data, and the new database is called MarketingAnalysis. The names of the products which are sold and the cities where the customers live will be retrieved by the most common report which will be generated.
Problem Statements
The security of the company data should be enhanced and the risk of unauthorized data access or malicious attack should be cut to the least.
The database sometimes could not be accessed for an extended period of time, therefore, the order cannot be placed by the customer.
CURRENT DATA ENVIRONMENT
Databases
The employee data is included by the HRData database. And the sensitive information such as salary and employee reviews is contained by the employee data.
Product and sales data is included by the BusinessData database. It is shown from an analysis that only a subset of the tables which is associated with the department in which the user works in the database is typically accessed by the users.
And it is shown from the business analysis that every day in the BusinessData database, about 4,000 new records will be added. In addition, after the records are entered, 90 percent of the records are not changed.
At present, there is no managed object in any database.
Database Servers
At present, there is a single default instance of SQL Server 2000 in the company. And the SQL Server 2000 stores the HRData database and the BusinessData database. The server on which the SQL Server instance is installed is called SQL01.
According to the written company policy, merely default instances of the SQL Server should be utilized and no named isnatnce should be installed.
After heated discussion, a hardware RAID solution should be deployed in the next six months. Nevertheless, the company wants no disk redundancy implemented at present.
Database Client Computers
The users get access to data in the HRData through an ASP.NET application. In the security context of a Windows user named HRApp, the application is run. And Windows Authentication is utilized to get connection to SQL Server.
The users in the sales department access the BusinessData database. And Windows Authentication is utilized to connect to SQL Server. Besides that, the users in the software development team require accessing the BusnessData database from client computers, and the computers run a Microsoft Windows operating system or another operating system. SQL Server Authentication is utilized by users on client computers to connect to SQL Server.
The users are not required to browse for a server because all client applications are set to connect to the SQL Server instance.
CURRENT INFRASTRUCTURE
Network Infrastructure
A TCP/IP-based network is connected by all computers. You can see the network configuration from the following diagram.
The company utilizes network cables to have computers in the sales call center connected to the network.
Directory Services Infrastructure
There is one Active Directory domain on the company network. And Windows 2000 Server computer and Windows Server 2003 computers are included by the domain. Client computers that run Windows Professional are utilized by most of the employees. And
client computers that do not run Windows are utilized by some developers.
The company has a domain-level policy enabled so as to enforce password complexity and
expiration after a month.
Server Infrastructure
Besides SQL01, two unutilized file and print servers can be utilized so as to upgrade to
SQL Server 2005.
You can see the specification for all database servers from the table listed below.
The latest service pack is applied in all Windows Server 2003.
BUSINESS REQUIREMENTS
General Requirements
The acquisition of new hardware or servers should not be required by the upgrade project. Nevertheless, the memory, processors, and software in the current servers are allowed for upgrading when it is essential.
Performance
The performance levels of the existing implementations should be met or exceeded by the upgraded HRData database and the redesigned BusinessData database. It should be fulfilled unless the performance is affected negatively by the new security-related functionality. What's more, the performance effect by security-related enhancement should be cut to the least.
A partitioned table should be utilized so as to enhance query performance. The company wants to name the tables as Sales.orders. And the following three partitions should be included. The first one is the partition for orders which are placed in the present month. The second one is the partition for orders which are placed in the last month. And the last orders should be the one for orders which are placed in the month prior to the last month. In addition, the company wants to have the orders which are longer than data in the three partitions archived in a second partitioned table named Sales.OrderArchive. And Sales.OrderArchive should contain the following two partitions: the first is for archived order data, and the second is for an empty partition which will be utilized as staging area for the archival process.
The company will utilize the procedure listed below to archive the order data at the end of each month.
First, a SWITAH operation should be utilized to remove the data in partition 1 of the Sales.Order table to the free partition 2 of the Sales.OrderArchive table.
Second, MERGE and SPLIT operations should be utilized so as to reorganize the data in the Sales.Orders table. As a result, the last month's data will be in partition 1, this month's data will be in partition 2, while the next month's data can be in partition 3 for it is free now.
Three, MERGE and SPLIT operations should be utilized so as to remove all archived data to partition 1 of Sales.OrderArchive table, and a new free partition 2 should be created to prepare for the next month's archival process.
The most speedy query performance should be offered by the new MarketingAnalysis database, while the data for the most commonly generated reports is retrieved.
Availability When a server fails, the BusinessData database should be made accessible in the shortest time, and the least amount of the administrative intervention should be utilized. To accomplish the goal, database mirroring should be utilized.
In common conditions, no data loss should happen when s server fails. But in some exceptions, a small number of data losses can be allowed.
The company wants to implement an interim disk-availability solution for SQL01 after implementing the hardware Raid solution. Fault tolerance should be offered by the interim solution in the event of a single disk which database is contained. The time that required recovering a system disk failure should be cut to the least by the interim solution. In addition, the highest level of read performance should be offered, and the general application solution requirements should be met.
Recoverability
The following requirements should be satisfied by the backup and recovery strategy for the BusinessData database.
First, full recovery should be permitted when total hardware fails.
Second, performance overhead which is caused by backups and restore operations should be cut to the least.
Third, the database should be permitted to be restored to a specific point in time.
Four, the risk of data loss caused by damaged backup media should be reduced to the least.
At last, the contingency plans should be included to handle file corruption.
TECHNICAL REQUIREMENTS
Security
Complex passwords should be utilized by all security credentials which are utilized in all of the company's IT system, and the passwords should be expired after a month.
The company wants to encrypt sensitive data in the HRData database, and the data can only be decrypted by the login that encrypted it. And the company requires having the risk of the unauthorized access to encryption keys and passwords within the database server reduced to the least.
In addition, the company wants to encrypt all data that is passed across the network. The company wants to have an HTTP endpoint in the BusinessData utilized to offer access to two stored procedures called Sales.AddOrder and Sales.GetOrderDetails. Only the sales employees who utilize Windows XP Professional computers in the sales subnet can get access to the HTTP endpoint.
The company intends to cut the attack surface area of the database servers to the least so as to reduce the risk of security violation.
Maintainability
The company intends to implement the BusinessData database so as to make sure that they don't need to rename, drop and re-create an object when the user that created the object is dropped.
The number of the permissions should be cut to the least by the database design, and the permissions should be granted on individual database objects.
The dbo user creates all database objects. And then the built-in backup features of SQL Server should be utilized so as to perform all backups. And then, no third-party applications should require access to the SQL Server files. The scheduled tasks should be utilized so as to cut the administrative intervention to the least. And the scheduled task is created by utilizing the Database Maintenance wizard.
Through scheduled SQL Server Integration Services (SSIS) tasks, the company wants to automate the transfer of data from the BusinessData database to the MarketingAnalysis database.
