Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step

In the previous section, we discussed how to capture functional requirements, but there exists another set of requirements: operational requirements. Operational requirements are attributes that a system or solution must possess to be acceptable from the information technology (IT) perspective. Operational requirements include availability, interoperability, manageability, performance, reliability, scalability, and security. Some examples of operational requirements include:

• The payroll calculations should take less than 15 minutes.

• The application will only be available through the intranet to previously authenticated Windows users.

• The application will be available and function properly 99% of the time.

Architecture and Operational Requirements

Possessing a good understanding of operational requirements demands that you have substantial knowledge of the business information that the system will manage and understand the business need that the software will solve. Operational requirements are important because they have a deep impact on the architecture of the solution. For example, if a solution needs to support 500 concurrent users, you will probably want to use SQL Server 2005 Enterprise Edition; if only five concurrent users are needed, a SQL Server 2005 Express Edition may suffice.

One way to evaluate the operational requirements is to analyze the business impact if the requirement is not fulfilled. For example, what will be the business impact if the application is not available for three hours? For three days? These questions can help you measure the benefits of the requirements, especially for requirements that are expensive to fulfill. A good understanding of the operational requirements helps stakeholders perform a cost-benefit analysis.

Well-designed applications use features of the technology to match the operational requirements. SQL Server 2005 offers features such as failover clustering, database mirroring, and database snapshots. If used appropriately, all of these features can help you increase application availability. Good application architecture leverages technology features to satisfy operational requirements.

Availability

Availability refers to the ability of the solution, system, or component to stay operational for the required length of time. Availability can be defined as the time that a solution is available for use. Availability is usually measured as a relative operational level. Perfect availability will be 100 percent and is typically specified in nines notation. For example, 5 nines represent 99.999%, 4 nines represent 99.99%, and so on.

To understand how difficult it is to achieve a certain level of availability, Table 4-1 translates the availability percentage to a time-per-year downtime that will be permitted.

Interoperability

Interoperability requirements demand that software has the ability to communicate with other programs using common protocols or file formats. Interoperability helps businesses manage the diversity of software products and platforms.

You can achieve interoperability after your solution has been developed. You may add code to read and save files to communicate with other programs, or you may use products such as BizTalk Server 2005 to orchestrate messages between different applications. However, if you include interoperability requirements in advance, your design will have built-in interoperability. Built-in interoperability offers better performance and manageability than added interoperability.

SQL Server 2005 is designed to help you create interoperable solutions through support of industry standards such as XML, Web Services, and the .NET Framework. The following SQL Server 2005 technologies may help you interoperate with different platforms.

• SQL Server Integration Services   A platform to build data integration solutions. SQL Server Integration Services (SSIS) is a valuable tool to build extraction, transform, and load (ETL) packages for data warehousing. Use SSIS to extract data from heterogeneous sources (including .NET data providers, OLE DB data providers, Microsoft Excel, XML files, flat files, and raw files) and merge that data in a consistent database.

• SQL Server Replication   A set of technologies for copying and distributing data between databases. Replication allows you to automate the copy of data to and from other database platforms including Oracle, IBM, and Sybase.

• SQL Server Service Broker   A technology that provides message queuing and reliable messaging in an asynchronous programming model. SQL Server Service Broker allows for the creation of messages between applications for interoperability purposes. For example, a sales processing application may send a summarized sales total message to an accounting application.

• SQL Reporting Services   A server reporting technology that allows you to create reports from relational and multidimensional data sources. With Reporting Services, you may create reports from .NET, OLE DB, and ODBC ( open database connectivity) data providers, allowing your application to interact with a wide range of data sources. Because the SQL Server Report Server (the main component of SQL Server Reporting Services) is offered through a set of XML Web Services, other platforms may consume its services.

Manageability

Manageability refers to the capacity of the software to be managed or controlled. Several industry studies confirm that the cost of managing applications is a major part of the total cost of software ownership. To build a manageable solution, you must provide tools that allow the application to be administered. Manageable solutions include configuration settings, performance counters, application logs, and so on.

To design a manageable solution, you must consider a different type of user the application administrator. Application administrators usually belong to the IT department. To build a manageable solution, you must provide them with an information and configuration infrastructure. Your application should allow the application administrator to monitor processes, services, and devices. With this information, the application administrator may perform preventive and corrective actions and handle administrative tasks such as installation, configuration, and general software maintenance.

As you learned in Chapter 3, Reviewing Microsoft SQL Server 2005 Management Tools, SQL Server 2005 offers SQL Server Management Studio, an integrated environment for configuring, managing, and administering all components of SQL Server 2005. Because SQL Server Management Studio includes a graphical user interface for all common management tasks, it reduces the amount of time database administrators need to support the database.

If you want to automate even further, thus increasing the manageability of your application, SQL Server 2005 offers Server Management Objects (SMO). SMOs are objects designed to manage SQL Server 2005 programmatically. With SMO, you may satisfy the needs of nontechnical users and reduce their training costs by integrating the server and database management into your application. For example, the following Visual Basic code will back up the AdventureWorks database using SMO.

Database Backup with SMO

Imports Microsoft.SqlServer.Management.Smo Public Class BackDB Public Shared Sub BackDB() Dim back As New Backup() back.Database = "AdventureWorks" back.Devices.Add(New BackupDeviceItem(_ "C:\Lab\AdventureWorks.bak", DeviceType.File)) back.Action = BackupActionType.Database back.SqlBackup(New Server("ServerName")) MessageBox.Show("Backup Complete!") End Sub End Class

Performance

Performance requirements identify how fast the solution should complete a process under a particular workload. There are two ways to measure performance: throughput and response time. Throughput measures how much work an application can perform, and response time measures the amount of time between the user request and the outcome of the request. An example of a performance requirement is that a home page should load within five seconds under a 150-user load. You can see that the performance requirement has two measures: five seconds (response time) and an implicit throughput to handle a given amount of data for a 150-user load.

You should design your application with performance in mind because some elements that affect performance are difficult to optimize after a database is in production. The following tips should help you in the designing process.

• Establish performance requirements to manage user and developer expectations.

• Develop a testing plan that checks performance requirements.

• Design your database appropriately, and use normalization to reduce data redundancy (see Chapter 5, Designing a Database to Solve Business Needs).

• Develop and test your stored procedures while taking performance into consideration (see Chapter 9, Retrieving Data Using Programmable Objects).

• Optimize the most significant queries and design your indexes appropriately (see Chapter 6, Improving Query Performance in Microsoft SQL Server 2005: AppliedTechniques Step by Step ).

Several factors affect application performance metrics. In SQL Server 2005, the following factors may affect performance:

• Hardware (processor, memory, disk input/output (I/O) speed

• Windows operating system version and configuration

• SQL Server 2005 version and configuration

• Database physical design (file placement, database configuration, indexes, and so on)

• Database logical design

• Network (drivers, devices, topology, and so forth)

• Client application

Some performance issues can be resolved by increasing hardware resources, but applications with serious design flaws will have performance difficulties no matter how much you increase the hardware. Testing should include realistic quantities of data because metrics can change dramatically depending on the size of the input.

Reliability

Reliability requirements establish software quality goals that help you measure how users and applications depend on the solution. Reliability is measured with the mean time between failure (MTBF) metric, meaning the average time between failures. The MTBF formula is:

• MTBF = hours of operation/failure count

Reliability and availability are related , but they set requirements quite differently. From a reliability point of view, the causes of failure may include not only the lack of service, but also the provision of inaccurate services. For example, if an application incorrectly calculates the sales tax under certain conditions, it will affect the reliability of the software, but it will not affect the availability. On the other hand, if a hard disk problem causes the application to be unavailable for two or three days, the reliability will be affected with only one failure, but the availability of the software will be greatly affected (1 percent). Software failures are counted only when the software is available and executing.

A well known fact is that about 80 percent of system failures are caused by human errors or flawed processes. That is why industry studies indicate that the reliability of software is only marginally affected by hardware and software technology; it is mainly controlled by the quality of the software engineering process, the emphasis on training, and the commitment to reliability.

Scalability

Scalability is the capacity of the solution to increase the total throughput (quantity of work) when hardware resources are added. In an ideal scalable application, an increase in hardware resources increases the throughput of the application proportionally. Non-scalable applications require increasing quantities of hardware to support additional users.

Performance and scalability are easily confused because performance problems are usually detected when the application is tested with increasing loads, but they are different. Measuring the maximum workload that an application can handle while meeting the minimum response time under varying hardware conditions determines the scalability of the application.

Internet applications and applications that support business models with the possibility of significant economic growth should include scalability requirements. Internet applications frequently demand support to an increasing number of users, as shown in Figure 4-1.

Figure 4-1: Scalability

Security

Security operational requirements determine how to control user access to information. Because new threats arise daily, there is an important difference between security and other operational requirements: the security design process continues even after the solution is developed. A high-quality initial design reduces both the applications exposure to attacks and the impact to the design caused by any new threats. Issues involving some basic principles of database security will be discussed in greater detail in Chapter 6, Reading Microsoft SQL Server 2005 Data from Client Applications.