Beginning SQL Programming: Pt. 2 - Page 5

Back Ends

The front end will contain or create a SQL statement and have a way in which to connect to a back end, as we discussed in the sections above. We noted that virtually every front end can send a SQL statement. Likewise, virtually every DBMS sold in the last ten years can receive and process SQL statements. The heavy-duty products designed to support enterprises generally have the term "Server" in their product title in some way. Lightweight back ends are typically desktop applications, which also contain code to support receiving SQL Statements. They can all be used as back ends to receive and process a SQL statement.

Microsoft

The flagship database management system from Microsoft is SQL Server (versions 7 and 2000). The product is optimized to run on Windows NT (or Windows 2000) and has done well in speed test against competing products. Microsoft SQL Server is often less expensive to deploy than competing DBMS. Furthermore, an aggressive training and certification program is increasing the number of professionals qualified to program and run Microsoft SQL Server.

For product information on Microsoft SQL Server 7 or 2000, visit http://www.microsoft.com/sql/productinfo/prodover.htm.

Although SQL Server is Microsoft's enterprise scale DBMS product (it falls under the umbrella of their .Net enterprises servers), there are many other Microsoft products that can use SQL.

Two DBMS products have similar names: Microsoft SQL Server and Sybase SQL Server are both specific DBMS products. These are two of many products that can receive SQL statements and thus function as back ends.

Oracle

Oracle offers one of the world's most widely deployed DBMS, currently named Oracle9i. The Oracle product has proven itself in many of the largest datastores supporting entire corporations. Oracle has established itself as a leader for web sites with very high traffic. The explosion of data access requests from the web has pushed Oracle to further increase speed, capacity and reliability.

To check out Oracle DBMS products see http://www.oracle.com/ip/deploy/database/index.html.

Sybase

Sybase currently offers three DBMS products. Adaptive Server Enterprise is optimized for enterprise computing and web site support. SQL Anywhere Studio provides a suite of tools for rapid development and deployment of business solutions. Adaptive Server IQ provides improved support for the analysis of data and ad hoc queries.

Information about Sybase products can be found at http://www.sybase.com/products/databaseservers/.

IBM

IBM offers the DB2 family of products, which run on the hardware and operating systems of both IBM and non-IBM machines, including a developers kit for their products on Linux platforms. The DB2 Universal Version 7 includes features that move more operations to memory and thus improve performance.

Go to http://www-4.ibm.com/software/data/db2/ for information on IBM's DB2.

Desktop DBMS

You do not need to buy and maintain a Data Server in order to have a back end. Several desktop applications can support SQL requests and will run in memory space on your laptop.

Oracle offers an 8i Personal Edition that can be installed on a desktop. This version is fully capable of storing data and then accepting SQL statements against that data. However, it is not intended to scale to many simultaneous users. As of early 2001, a free trial download is available from www.oracle.com.

Microsoft also offers products for the desktop. First is Access, a DBMS that is easy to learn and powerful but was not designed to scale to more than a few users at once. Access has a drag and drop interface which translates to SQL, and displays both translations on the monitor for most points in the design screens. Note that Access can function as both a front end with the forms and reports objects and as a back end with its table and query objects.

Note that we have mentioned Access under both a front and back end. Some of Access's tools such as forms and reports function as a front end. Other tools such as queries and Tables function as back ends. For people using Access as an entire desktop database solution the distinction is not important. But the front and back end tools can be used separately in conjunction with products from other vendors. For example, an Access front end can connect to an IBM DB2 datastore. Or to switch hands, an ASP page can connect to Access queries and Tables offered as backends. In the case of these cross-software connections, SQL is the language for communication.

There are several places within Access where you can use SQL statements instead of using the names of objects like tables or queries. We discuss these in Chapter 2.

Microsoft also offers the MSDE (Microsoft Data Engine) which is essentially the Microsoft SQL Server data engine without the rest of the DBMS features (such as Query Analyzer), and without the ability to scale beyond a few users. You can use MSDE to manage your data and to run SQL statements against that data, but you do not have the tools for performance monitoring, replication, security and other features found in SQL Server. MSDE is, at the time of this writing, a free download from www.microsoft.com. It also ships with editions of Microsoft Office 2000 which include Access (Microsoft Office 2000 Premium or developer editions).