## MS BackOffice Unleashed

- Data Warehousing Applications
  - Data Volume
  - Cost of Implementation
  - Data Access Performance
  - Reduced Query Complexity
- Enterprise-Wide Applications
- Departmental Applications
- Internet and Intranet Applications
  - Using SQL Server Web Assistant
    - Step 1: Invoke SQL Server Web Assistant
    - Step 2: Build a Query
    - Step 3: Define a Schedule
    - Step 4: Specify the HTML File Options
    - Step 5: Choose an HTML File Format
    - Step 6: View the Data
- Developing Monitoring and Management Tools
- SQL Server in a Heterogeneous Distributed Environment
- Summary

#
— 33 —

##
Using SQL Server in Commercial and Local Applications

Microsoft SQL Server has shown tremendous growth in the past few years. SQL Server 6.5 is truly an industry-level, high-performance relational database engine that has set various benchmark results on Windows NT. Low cost and large number of useful features have made SQL Server an attractive choice for all kinds of applications, ranging from departmental-level applications to data warehousing solutions. In this chapter, you will read about some of the areas where SQL Server can be used effectively.

###
Data Warehousing Applications

Data warehousing is a collection of a large amount of information (usually over 20GB) stored in the database. Data warehousing systems are designed to provide a strategic viewpoint of the entire enterprise data. Data stored in the database can come from different data sources, including many of the legacy systems. Data warehousing systems are quite different from on-line transaction processing (OLTP) applications. In an on-line application, data viewed and modified by the end-users is current, databases are usually not all that large, and there might be a lot of write activity on the databases. On the other hand, data warehousing applications are mainly decision support systems (DSS) with a very high volume of data. Data stored in a warehousing system is usually not on-line, but is refreshed at regular intervals, depending on the requirements of an organization. Activity on a database is mostly read-only. Users of a data warehousing application use the system to analyze current business methodologies and use that information to chart out a strategy that is critical to the success of the core business of the enterprise.

The airline industry is a good example of a data warehousing solution. Historically, airline reservation and flight information systems are implemented on mainframes. As with any other industry, the goal of an airline is to maximize revenue while keeping the cost low. The airline can achieve this objective by keeping fares higher (to maximize revenue) on the busy flights and lowering fares (to increase capacity utilization) for the less-busy flight legs. For an airline analyst, it is critical to create a balance between airfare and capacity utilization (for example, very high fares might result in lower capacity utilization). To maximize revenue, it is important that airline analysts can accurately calculate capacity utilization for each leg of the flight and forecast the fare. To achieve this, a data warehouse can be created where relevant information can be summarized and downloaded from the mainframes and stored in a SQL Server database. Analysts can then use various cost models and perform ad hoc queries against databases to build an optimal model that will result in higher revenue generation and better capacity utilization for each flight.

Building a data warehouse is a complex task. Various factors govern the success of a warehousing solution. Some of these factors are discussed in the following sections.

####
Data Volume

Data warehousing solutions manage huge amounts of information, potentially up to hundreds of gigabytes of data. SQL Server is capable of handling a large volume of data and provides effective solutions to complex business problems with great efficiency. In a warehousing system it might be necessary to spread data across multiple databases on the same server. SQL Server can handle up to 32,767 user databases to provide such capability.

####
Cost of Implementation

To an organization, cost is always an important factor in implementing a large system. SQL Server 6.5 provides one of the best price/performance ratios on Windows NT. Unlike many other relational database vendors, Microsoft SQL Server comes with built-in data-replication capabilities. Because replication features come free with SQL Server, no additional costs are involved in implementing a distributed data warehousing solution using DBMS-provided replication techniques.

####
Data Access Performance

One of the biggest challenges of a warehouse application is providing fast response time for the end-user queries. Ad hoc queries can specify a different search criteria each time, and a database engine must respond within an acceptable response time. Following are some of the features of SQL Server that can provide fast response time for the queries:

- Indexes: Indexes are created on a table for faster retrieval of data. SQL Server enables you to create 1 clustered index and up to 249 nonclustered indexes on each table. Therefore, by anticipating the majority of end-user ad hoc queries and choosing indexes judiciously, you can achieve significant performance improvements.
- SMP Support: SQL Server supports Symmetric Multiprocessing (SMP). SMP support allows SQL Server to run multiple tasks simultaneously on different threads, resulting in excellent load balancing and throughput.
- Parallel Data Scans: In a warehousing system, a user might request information from the database in various ways. In some situations the data requested by the end-user is sequential in nature, especially when the user is retrieving a large amount of information from the database tables. SQL Server can detect such queries and issue background threads that read the data that is not yet requested but is expected at a later stage in the query. This read-ahead feature provides the capability of scanning data in parallel. Various SQL Server configuration parameters enable you to control read-ahead behavior of SQL Server in a way that best suits a specific environment.

####
Reduced Query Complexity

SQL Server 6.5 provides various aggregate functions and T-SQL operators. Many complex queries using multiple steps can be substituted by simpler queries using such operators. A number of queries in a data warehousing application compute multidimensional aggregates (called super aggregates) by using multistep queries. SQL Server 6.5 provides two T-SQL operators, called CUBE and ROLLUP, that are used with the GROUP BY clause to simplify such queries.

###
Enterprise-Wide Applications

Many organizations are moving forward with SQL Server to deploy enterprise-wide, mission-critical applications. SQL Server can support a large number of concurrent users and high transactional volume with excellent performance, without compromising data integrity. Microsoft SQL Server complies with American National Standard Institute (ANSI) SQL-92 standards. SQL Server provides a powerful T-SQL query language with an intelligent cost-based optimizer, as well as configurable parameters that can be tuned to yield high performance. SQL Server's versatile locking strategy provides high data concurrency and data integrity. One of the new locking features in SQL Server is Insert Row-Level Locking (IRL), which enables you to enable row-level locking for data inserts. This feature provides performance improvements in a high-load environment.

An enterprise-wide distributed client/server computing environment demands a highly reliable database administration. An application that is global in nature can run on multiple SQL Servers housing multiple databases. All of these servers can be placed in separate geographical locations and can be interchanging information. Managing these SQL Servers individually can be a nightmare for database administrators. SQL Server comes with a friendly graphical user interface, called SQL Enterprise Manager, that allows a centralized administration of enterprise-wide SQL Servers.

###
Departmental Applications

Low cost and high performance have made it possible for organizations to adopt SQL Server for departmental-level applications. SQL Server is now widely used in places that are traditionally mainframe oriented but have started to migrate some of the application functionality to the client/server environment. Many banking applications fall into this category. Usually, customer financial information is kept on the mainframes. However, some customer transactions (such as loans) can be done by individual branches of a bank on a local SQL Server database. At the end of the day, this information can be exported out of the SQL Server database and sent to the mainframe for consolidation.

###
Internet and Intranet Applications

With the growing popularity of the Internet and intranets, many organizations have recognized the benefits of extending the enterprise to the Internet and letting people use existing business applications over the Internet, without sacrificing reliability or compromising on security. SQL Server enables users to access databases over the Internet. For an application to access SQL Server, both the client and the server must be directly on the Internet. Using Microsoft SQL Server, it is possible to publish data in the database in the form of a standard Hypertext Markup Language (HTML) file. SQL Server comes with a graphical user interface called SQL Server Web Assistant that can be used to publish data on the Web server. These HTML files can also be created by use of SQL Server–supplied stored procedures. These files can be viewed by any Internet browser, such as Netscape or Microsoft Internet Explorer. Data can be published on a scheduled basis or upon data change (through SQL Server triggers). The following section takes you through the creation of a simple application. This exercise demonstrates how to build an application using SQL Server Web Assistant to publish data in a database on the Web.

####
Using SQL Server Web Assistant

Following are the step-by-step instructions for generating an HTML file from the data of a SQL Server database.

#####
Step 1: Invoke SQL Server Web Assistant

Run SQL Server Web Assistant from the SQL Server 6.5 program group. Enter the name of the SQL Server you want to publish data from, along with the SQL Server login ID and password (see Figure 33.1). It is also possible to connect to SQL Server using Windows Integrated Security.

FIGURE 33.1. Starting SQL Server Web Assistant.

#####
Step 2: Build a Query

This step enables you to build a query in one of the following three ways:

- Build a query using the point-and-click method
- Write your own query
- Specify a stored procedure name

Figure 33.2 shows the screen where you can write your own queries. This feature is useful when you want to display data from a complex join.

FIGURE 33.2. Building a query for data publication.

#####
Step 3: Define a Schedule

This step enables you to specify the timeframe when you want to create the Web page. Choose an appropriate schedule from the drop-down listbox, as shown in Figure 33.3.

FIGURE 33.3. Specifying the HTML file update schedule.

#####
Step 4: Specify the HTML File Options

In this step you can define the location of the HTML file. You can also create a URL to refer to an Internet location. Figure 33.4 shows the dialog box where you can enter the relevant information.

FIGURE 33.4. Specifying the HTML file location.

#####
Step 5: Choose an HTML File Format

This step lets you choose a very basic display format for the HTML file. Choose an appropriate format (see Figure 33.5) and click the Finish button to create an HTML file.

FIGURE 33.5. Defining the HTML file format.

#####
Step 6: View the Data

The data is published in the HTML file you specified in step 4, and you now can view it using any standard browser. Figure 33.6 displays the HTML file using the Netscape browser.

FIGURE 33.6. Viewing the HTML file through a browser.

###
Developing Monitoring and Management Tools

Software vendors develop tools for SQL Server using SQL Server C DB-Library Open Client Library or ODBC calls. Some of these tools provide a friendly development and debugging environment for software developers. Others provide a helpful user interface for managing and monitoring SQL Server databases. Microsoft's supplied graphical tool called SQL Enterprise Manager is an example of such a tool.

###
SQL Server in a Heterogeneous Distributed Environment

In a distributed client/server environment, sometimes it is necessary to run transactions across multiple servers. It is important to maintain the integrity and consistency of the data on each server—that is, if a transaction fails on one of the servers, the entire transaction should be rolled back. SQL Server provides a two-phase commit protocol that preserves data consistency across several SQL Servers. Distributed transactions can also be managed with Microsoft Distributed Transaction Coordinator (MSDTC), which uses a two-phase commit protocol and runs as a service under Windows NT. Using MSDTC, it is possible to review and control the state of each transaction participating in a distributed transactional environment.

SQL Server 6.5 also includes built-in data-replication capabilities. Replication is data duplication from one database to one or more databases on the same or different servers. SQL Server enables you to replicate data either on a scheduled basis or on a number-of-transactions basis. SQL Server replication can be managed with the easy-to-use SQL Enterprise Manager. SQL Server databases can also participate in two-way replication. In a two-way replication, the database that is publishing the data can also subscribe to data from various servers. SQL Server is also capable of handling data replication from ODBC subscribers other than SQL Server, such as Oracle and Sybase.

###
Summary

Microsoft SQL Server 6.5 provides robust, efficient, and effective solutions for complex business problems in the Windows NT environment. A host of features, such as the powerful T-SQL language, an intelligent cost-based optimizer, built-in replication, a versatile locking strategy, and a distributed transaction control mechanism, make Microsoft SQL Server 6.5 ideal for all kinds of client/server computing environments. SQL Server also provides tools for database administrators for centralized management of SQL Server.