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 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 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.
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.
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:
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.
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.
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.
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.
Following are the step-by-step instructions for generating an HTML file from the data of a SQL Server database.
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.
This step enables you to build a query in one of the following three ways:
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.
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.
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.
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.
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.
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.
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.
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.
