Client/server applications differ from file-server applications in many ways, but the key difference is that client/server applications divide the processing between two or more applications, a client and a server, which typically run on separate computers. In thischapter, you will learn a little of the history of client/server computing, as well as the features of client/server databases in general and Microsoft SQL Server in particular. You will also learn some of the advantages of client/server databases
over file-server databases.
In the beginning, there were mainframes and minicomputers. All data resided on and was processed by these often room-filling machines. All bowed down before the mighty MIS department, as all information was in their hands.
Okay, so that might be an exaggeration, but not by much. In the late 1970s, there were plenty of data processing centers with raised floors, a sea of disk drives, a wall of tape drives and an army of operators. In this host-based model, all the processing was done by themainframe, while data was entered on dumb terminals. Consider the example of Gary’s first programming project, in 1979: “The project involved trying to get data from our Data General minicomputer for a decision support system for our product managers. We wanted to give
our incredibly powerful Apple IIs, with two floppy disk drives, access to the corporate sales data so the product managers could make decisions on our product lines. We were completely at the mercy of the MIS department, who controlled all the data. No way were they going to let us have online access to it. They could be coerced into giving us a monthly dump of sales data, but that was the best we could do. Our project turned into a primitive data warehouse; modeling and reporting on the data turned out to be easy compared to getting access to it in
the first place.”
Lest you think that this is merely a history lesson, there are many of these systems still in place. The most popular American hospital billing system uses an IBM AS/400 minicomputer- based DB2 database. Many systems like this are unlikely to be replaced in the near future,but to developers they are mostly a curiosity, as new development on such systems is rare, and the growth of the mainframe market is pretty flat.
The PC revolution
Then came the personal computer. PCs allowed departments and often entire corporations to dispense with their expensive, centralized host-based systems and replace them with networks of PCs sharing files on file servers. The pendulum had swung the opposite way. Ratherthan doing all of the processing on the central scrutinizer, it was done on the workstation. Dumb terminals had been replaced by dumb file servers.
This is where most of us come in. FoxPro, along with other systems like dBase, Paradox and Access, has a local data engine. All processing is done on the workstation, and the network is used for file storage only. Throughout this book, this model is referred to as a file- server database.
Because all the processing was performed locally and because the workstation could be a powerful computer in its own right, we developers were able to give users very sophisticated user interfaces. But we could not provide them with a secure, fault-tolerant database, andwe used up a tremendous amount of network bandwidth. In fact, application server software such
as Citrix WinFrame or Windows Terminal Server, which reduce network bandwidth by running applications on a server machine, became popular primarily because of file-server databases
and their need for a big network pipeline. This is because all processing is performed on the local workstation, while only the files reside on a file server. To perform a query, all information necessary for finding the result set, such as index keys, must be downloaded inaddition to the result set itself. Rushmore is very efficient about what it brings down, but whatever it needs still has to come to the local workstation.
Furthermore, improvements in database performance often require upgrades to each workstation running the application—a potentially expensive proposition when many users are involved.
Client/server to the rescue
The client/server database is an excellent solution to the problem of delivering sophisticated applications while maintaining security and fault-tolerance of the database and reducing network overhead. Client/server databases are so named because a system consists of at least twoapplications: a client application and a server application, or service. The client application typically runs on a workstation and can provide the sophisticated user interface that users have come to expect. To interact with the database, the client sends requests to the server. The server application typically runs as a service on an application server machine and manages the database and responds to requests from clients.
The concept of a client making a request and the server responding to that request is key to understanding client/server computing. The client has absolutely no control over the data on the server. It makes a request for everything. The client doesn’t open data files; it asks theserver to log the user in to the database. This is the key to client/server security because there is no back- door access to data. The client doesn’t download index keys to perform a query; it simply sends a SELECT statement to the server, and the server sends back only thematching records. This is the key to reducing network overhead, as only a minimum of network traffic is required.
In a file-server application, performing a query requires many round trips to the server. For example, opening a table requires sending low-level file access instructions to the server and returning a handle to the file. Then the workstation sends instructions to access the file addresses of the index keys, and the server returns those keys. The workstation then processes the keys to determine the result set and sends to the server the addresses of the records to retrieve. Finally, the data itself is downloaded. Use your network monitor and a modem connection sometime to perform a simple query against FoxPro tables, and you’ll get an excellent demonstration of just how slow and inefficient this process can be. The larger the database and the lower the network bandwidth, the worse this performance is.
In a client/server application, the client merely sends a SQL statement off to the server:
SELECT * FROM employees WHERE lastname LIKE 'King'
The server responds by sending back only the records that match. Not only has the quantity of transmitted data been reduced, but the number of network round trips has, too.
The problem of improving file-server performance is also partially resolved by
client/server applications because database performance can be improved by upgrading a single machine, the server, rather than upgrading all the workstations. It is considerably less expensive to upgrade or replace a single, powerful application server than many lower-levelworkstations!
There are many client/server databases on the market today. Originally many of them, such as Oracle, Informix and Sybase, ran only on Unix. Several years ago, Microsoft and Sybase entered into an agreement whereby Microsoft would develop a version of Sybase SQL Server for the Windows NT platform, and Microsoft SQL Server was the result. Now many client/server database vendors, including the leader, Oracle, support Windows NT and/or Windows 9x.
Client/server databases are frequently referred to as SQL databases because they commonly support Structured Query Language, or SQL.
Features of client/server databases
This section summarizes the major features of client/server databases and, where appropriate, makes comparisons to similar features in file-server databases. Features and comparisons are illustrated using Microsoft SQL Server and Microsoft Visual FoxPro; however, most ofthese points apply to other client/server and file-server databases as well.
Data access
The key difference between client/server and file-server databases is in the way data is accessed. A client/server application always consists of two or more applications: a client and a server. The database server erects a wall around the physical data, and it can only be accessed by sending requests to the server application, which processes the requests and returns the results.
With a Visual FoxPro database, any machine that has VFP or the VFP ODBC driver and access to the data directory can process that data on the local workstation. All processing is actually performed on the local workstation, and all information required to perform thatprocessing must be transmitted from the server to the workstation. After the server data is copied to memory on the workstation, the user can change the data and the changes are written directly to the database on the file server.
With a SQL Server database, the client workstation runs one or more applications that make requests of the database server and accept the results of those requests. The client can make changes to the data locally, but those changes are not made directly to the database. Instead, they are packaged as requests, typically a SQL INSERT, UPDATE or DELETE statement, and sent back to the server. Just as with a request for data, these change requests
are handled by the server, which has the ultimate authority and control over how such requests are processed.
SQL Server includes a utility called Profiler that provides an excellent demonstration of just how this works. In Figure 1, you can see a trace in the Profiler. This trace was run while opening a VFP form that opens a couple dozen views. Each line in the trace shows the actual SQL statement sent to the server along with details on the number of disk reads and writes, duration of the processing, and so forth.
Figure 1. The SQL Server Profiler in action, demonstrating the request/response nature of SQL Server.
Security
A Visual FoxPro database has no security. A developer can write procedural code to enforce security, but this type of security can be circumvented.
By contrast, SQL Server databases are totally secure. All access to the database must be through the database server application. By default, no user has access to anything in SQL Server until the administrator has added the user to the system. Even then, the user has no access until the administrator specifically grants it. This system is called declarative security. Any attempt to access the data causes the server to check for a user’s login ID and password. Figure 2 illustrates an attempt to access the Northwind database from Microsoft Visual InterDev. Note the login dialog.
Code you write in your application to access a SQL Server database will also require
authentication by the server. Attempting to open a remote view of the Northwind employee table from the VFP Command Window, as shown in Figure 3, will also prompt the user with a login dialog.
Figure 2. An attempt to log in to the SQL Server Northwind database causes the user to be prompted for a login ID and password.
Figure 3. Attempting to open a remote view of SQL Server data also causes the user to be prompted for a login ID and password.
The preceding illustrations show the SQL Server login dialog, but there are actually many ways to handle logging in. For example, you can configure your ODBC connections to supply a login ID and password when connecting so that the login dialog doesn’t appear at all whenthe application runs.
SQL Server also offers a feature called Windows NT Integrated Security that can be used instead of the normal SQL Server authentication. With NT Integrated Security, SQL Server checks the name of the user logged in to NT rather than requiring a SQL Server user ID andpassword.
In addition to authenticating users for access to the database, SQL Server allows administrators to assign rights to any individual object in the database. For example, some users might have access to all columns in the employees table, while others might not be allowed to seeaddresses or salaries. See Chapter 3, “Introduction to SQL Server 7.0,” for more
information on security in SQL Server.
Database backup
A friend recently described a client’s nightmare with a VFP database. They performed an automatic tape backup of their network every night. One day, the inevitable happened and the network went down. No problem, they simply went about restoring from backup. Well, not all thetables were backed up, as some were open when the backup was performed. So they went back to the previous night’s backup, but no dice. On and on they went, but no complete backup had been performed because every night somebody had some files open because they forgotto shut down their system or a developer was working late. They were in big trouble.
SQL Server eliminates this problem by allowing live backup of a database while it is in use. An administrator can schedule backups, or an application can periodically send a T-SQL BACKUP command to the server. The database is dumped to a backup file, which is closed as soon as the backup is completed, and this backup file is copied to the backup tape. If the server goes down, the client’s nightmare isn’t a problem. This backup capability permits both 24/7 operation and reliable backup.
Point-in-time recovery
SQL Server records every transaction in a transaction log in memory. Each time a transaction is completed, it is copied from the log in memory to the log on the disk. At various intervals, the transactions in the log are written to the physical database on disk. In the case of a crash,the data can be recovered as long as the transaction log is recoverable. Of course, any updates that had not yet been written to the physical transaction log would be lost.
The transaction log itself can also be backed up. Normally, the transaction log is not emptied when the database is backed up. However, when the transaction log itself is backed up, committed transactions are removed from it to keep the log size to a minimum. So if the databaseis backed up on Tuesdays and the transaction log is backed up every day, then the worst-case scenario even when the transaction log is destroyed is to restore the weekly backup and then each daily transaction log. Only part of a day’s transactions are lost, which is a substantial improvement over the aforementioned client’s nightmare.
Backups can be performed more often; however, backups affect performance of
the system. This is one of the trade-off decisions you will have to make when designing a client/server system.
Triggers
Visual FoxPro databases support triggers. A trigger is a stored procedure that is triggered by an INSERT, UPDATE or DELETE of a record in a table. In VFP databases, triggers are used to enforce referential integrity and may be used for other purposes as well. One difficulty with VFP triggers is that the VFP ODBC driver only supports a limited subset of VFP syntax. So code in a trigger that works fine when running in a Visual FoxPro application may not work when accessing data via ODBC.
Although SQL Server can use triggers to enforce referential integrity, declarative referential integrity is the preferred method, simply because declarative integrity performs substantially better than trigger-based integrity.
Triggers are also frequently used to support business rules and are an excellent way to provide an audit trail. For example, a trigger might insert a record into an audit table containing the datetime of the change, the user making the change, and the old and new values.
Here is an example of a very simple auditing trigger. Suppose a fire department wants to keep track of any changes made to the alarm time, arrival time or cleared time for a fire
incident. Although it is entirely possible that such a change is being made legitimately to reflect correct times, it is also possible that someone might change these times to make them look
better or to cover up mistakes. Here’s the schema for a simple time logging table:
CREATE TABLE timelog (timelogkey int IDENTITY (1,1), edittime datetime,
userid varchar(100), columnname varchar(100), oldtime datetime,
newtime datetime)
The timelogkey column is an identity column and will automatically enter unique integers, beginning with one and incrementing by one. Now an update trigger is created for the incident table:
CREATE TRIGGER utrIncidentUpdate
ON incident
FOR UPDATE
AS
DECLARE @oldtime datetime,
@newtime datetime,
@ikey udtKeyField
IF UPDATE (alarmdate)
BEGIN
SELECT @oldtime = alarmdate, @ikey = incidentkey
FROM deleted
SELECT @newtime = alarmdate
FROM inserted
INSERT timelog
(incidentkey, edittime, userid, columnname, oldtime, newtime)
VALUES
(@ikey, GETDATE(), USER, 'alarmdate', @oldtime, @newtime)
END
This trigger requires a bit of explaining. SQL Server stored procedures use temporary cursors that are visible only within the stored procedure. In the case of triggers, which are a
special type of stored procedure, there are two default cursors: deleted and inserted. In delete triggers, the deleted cursor holds the values of the row being deleted, while in update triggers it holds the values of the row prior to the update. In insert triggers, the inserted cursor holdsthe values of the row being inserted, while in update triggers it contains the new values of the row being updated.
The update trigger in the preceding code checks to see whether one of the three critical times—alarmtime—has been updated. This is done with the UPDATE() function. If so, a row is inserted into the timelog table. The row includes the current datetime (returned by the SQL Server GETDATE() function), the user making the change, and the name of the column being changed. It gets the old and new values from the deleted and inserted cursors, respectively, and inserts them as well.
By extending this technique, you can see that it is possible to create a complete audit trail of every change made in the database.
Referential integrity
Visual FoxPro databases support trigger-based referential integrity. When an application or user attempts to delete, modify or insert a record, the appropriate trigger is fired. The trigger determines whether the attempted delete, modification or insert can proceed. A deletion triggermay cause cascaded deletes of child records. Similar processing occurs when an attempt is made to change a primary key value. The change may be prevented by the trigger, or the change may be cascaded through the child tables. Although such trigger-based referential integrity is adequate for some purposes, it becomes less reliable as the schema becomes more complicated, as thousands of triggers could be firing for a single deletion.
While SQL databases also support the use of triggers for the purposes described in the previous paragraph, the preferred method is declarative referential integrity. Declarative referential integrity, supported by SQL Server since version 6.0, enforces referential integrity at the engine level. Deleting a record when children exist is simply prohibited. Instead of using triggers to cascade deletes, a stored procedure is typically written to delete records from the bottom up based on a given primary key for the top-level parent record. This technique is not onlymore reliable, but it typically provides better performance, too.
Declarative referential integrity is implemented through the use of foreign key constraints. Here is an example of how to create a foreign key constraint:
ALTER TABLE EMSAdvanced ADD CONSTRAINT fk_EMSAdvanced_incident FOREIGN KEY (incidentkey) REFERENCES incident (incidentkey)
Indexes
Indexes are used in Visual FoxPro databases to display data in a particular order, to improve query performance through Rushmore optimization, to enforce unique values, and to identify unique primary keys. SQL Server essentially uses indexes for the same purposes, but SQL Server does not use Rushmore. Instead, it uses its own optimization techniques designed specifically for the SQL Server query engine.
Clustered indexes
When a new record is added to a VFP table, it is typically appended to the end of the file, as this is much more efficient than writing a record in the middle of a file. If no index order is set, then browsing a table will show the records in this native order. Sometimes it makes sense for performance reasons to occasionally sort a table based on the value of some field, such as a primary key.
In SQL Server, the physical order of records can be controlled with a clustered index. Each table may have one clustered index, and a new record will be inserted into the table in the order determined by the clustered index. Clustered indexes can improve query performance when queries need to return a range of consecutive records. However, they tend to decrease insert or update performance, since these operations could force a reorganization of the table.
A clustered index on the customerid column of the Northwind customers table is created like this:
CREATE CLUSTERED INDEX idxcustomerid
ON customers (customerid)
Unique indexes
In a VFP table, a candidate index is used to enforce the uniqueness of a value in a table. They are called candidate indexes because the unique value is a likely candidate for a primary key. In SQL Server, the same thing is accomplished with a unique index. Don’t confuse this with a unique index in VFP (i.e., INDEX ON…TAG tagname UNIQUE), which is simply an index containing only a single key even when the table contains multiple records, each of which has a key of the same value. A unique index in SQL Server, like a candidate index in VFP, prevents duplication of the value in the table. A unique index on the employeeid column of the Northwind employees table is created like this:
CREATE UNIQUE INDEX pkemployeeid
ON employees (employeeid)
Primary keys
In a VFP database, you can specify one primary index per table like this:
ALTER TABLE mytable ADD PRIMARY KEY myfield TAG mytag
Behind the scenes, VFP actually creates a candidate tag in the index file and then adds a special entry in the DBC to indicate that it is the primary key.
Primary keys in SQL Server are very similar, using primary key constraints. This code creates a primary key constraint and a clustered index on the employeeid column of the employees table:
ALTER TABLE employee ADD CONSTRAINT pkemployeeid
PRIMARY KEY CLUSTERED (employeeid)
Non-clustered indexes
Rushmore optimization in Visual FoxPro is effected with the use of index tags. Query performance is improved under most circumstances by having an index that matches the filter expression of a SELECT. Query optimization in SQL Server works in much the same way. Fields that are likely to be used in filter expressions should have non-clustered indexes. A non- clustered index on the lastname column of the Northwind employees table is created like this:
CREATE NON-CLUSTERED INDEX idxLastName ON employees (lastname)
Defaults
Both Visual FoxPro and SQL Server databases support defaults. Defaults allow you to specify a default value for a field. For example, a merchant in the state of Washington might want to assume that its customers are residents of Washington and automatically insert ‘WA’ in the state column.
CREATE DEFAULT uddWAState AS 'WA'
EXEC sp_bindefault uddWAState, 'customers.state'
Rules
Both Visual FoxPro and SQL Server databases support rules. Rules allow you to specify field- level validation in the database. Once specified, rules are enforced by the database engine.
A SQL Server rule is created using a variable, rather than a column name. Here’s a rule that requires Social Security numbers to consist of nine numeric values. Note the use of the
@social variable (the “@” always denotes a local variable in SQL Server):
CREATE RULE udrSocial AS
@social LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
By using a variable, rather than the name of a column, the same rule can be applied to any number of columns:
EXEC sp_bindrule udrSocial, 'employees.socialsecurity' EXEC sp_bindrule udrSocial, 'drivers.license'
Primary key generation
In Visual FoxPro, you can generate primary key values through one of two common
techniques. You can call a function or method that returns the next available primary key value, and then insert this value into the new record before it is committed. Alternatively, you can write a stored procedure that is invoked by the Default Value of a field, which automatically places the new value into the field. As with any other VFP stored procedure, you are restricted outside of a VFP application by the limitations of the VFP ODBC driver. The default values
you so painstakingly create may not be generated at all when a record is inserted via ODBC.
SQL Server automates primary key generation by using identity columns. An identity column may not have a value inserted into it. Instead, it will automatically be set to the next available value for that column. The initial (or seed) value can be set, as well as the amount by which the value is incremented. The identity attribute can also be turned off for a column if you need manual control over the values inserted into the column. The attribute can be turned back on and set to increment from the highest existing value. This is all handled automaticallyby the engine and requires no code on the part of the developer.
However, identity columns are no panacea. Retrieving the last value is normally handled by checking the value of the @@IDENTITY global variable. But it may be difficult to get the correct value of this variable, as insert triggers might have caused other identity columns to be incremented and thus would return the wrong value for @@IDENTITY. Identity columns can also cause a problem when databases are replicated. If you have the choice, you should avoid identity columns when you design a database. Learn to use them, though, because you mayfind yourself working on databases that use them.
Stored procedures
Visual FoxPro databases support stored procedures. However, stored procedures that run in Visual FoxPro may not run via the VFP ODBC driver, so it is very difficult to create stored procedures that are of any value outside of a VFP application. Because SQL Server storedprocedures are run by SQL Server, you never have this incompatibility issue.
One use for stored procedures in SQL Server is to provide parameterized record sets of data. VFP views support parameters that can be used to filter the rows returned by the view. For example, the customerorders view can be defined with a parameter to return only those records matching a particular customer ID:
CREATE SQL VIEW customerorders AS ;
SELECT customers.companyname, orders.orderdate, ;
orderdetails.productid, orderdetails.quantity, orderdetails.unitprice ;
FROM customers JOIN orders ;
ON customers.customerid = orders.customerid ;
JOIN orderdetails ;
ON orders.orderid = orderdetails.orderid ;
WHERE customers.customerid LIKE ?cCustomerID
The parameterized view is opened by setting the value of the parameter and opening the view with USE:
cCustomerID = 'ALFKI' USE customerorders
Parameterized views are not supported by SQL Server, but they can be simulated with a stored procedure like this:
CREATE PROCEDURE usp_customerorders
@cCustomerID nchar(5)
AS SELECT customers.companyname, orders.orderdate,
orderdetails.productid, orderdetails.quantity, orderdetails.unitprice
FROM customers JOIN orders
ON customers.customerid = orders.customerid
JOIN orderdetails
ON orders.orderid = orderdetails.orderid
WHERE customers.customerid LIKE @cCustomerID
In T-SQL code, one would access this stored procedure like this:
EXEC usp_customerorders 'ALFKI'
And from a VFP application, one would access this stored procedure like this:
SQLEXEC(lnHandle, "EXEC usp_customerorders 'ALFKI'")
Stored procedures in SQL Server are written using Transact-SQL, also known as T-SQL, SQL Server’s programming language. Although not as rich a language as Visual FoxPro and lacking any record-based data navigation (T-SQL is purely set-based), it is nonetheless a powerful procedural language and can be used for many purposes. It contains syntactical equivalents to such VFP constructs as IF..ELSE, DO WHILE, RETURN, PARAMETERS
and so on.
One powerful feature of stored procedures in SQL Server is that they can be assigned security rights just like any other object in a database. One very good use for this is to use stored procedures for database updates rather than allowing direct access to the tables. The administrator takes away INSERT, UPDATE and DELETE rights for tables and/or specific columns, and the only way for a change to be made is to call the appropriate stored procedure and pass it the values necessary for the change.
Calling SQL Server stored procedures from Visual FoxPro applications will be examined in greater detail in Chapter 6, “Extending Remote Views with SQL Pass Through.”
Views
Visual FoxPro databases support views. A view is nothing more than a predefined SQL SELECT. Views can be used to determine which columns to include in a record set or to perform multi-table joins. A useful view limiting the number of rows and columns returned in a record setmight look like this:
CREATE SQL VIEW customerorders AS ;
SELECT customers.companyname, orders.orderdate, ;
orderdetails.productid, orderdetails.quantity, orderdetails.unitprice ;
FROM customers JOIN orders ;
ON customers.customerid = orders.customerid ;
JOIN orderdetails ;
ON orders.orderid = orderdetails.orderid
This view performs a three-way join and returns only five columns. This type of view may be ideal for reporting and can also be used for data entry, as views can be made updatable. Predefining the three-way join simplifies things for users who may otherwise have to writesuch a query themselves. However, the VFP ODBC driver only supports the calling of VFP views that are not parameterized through SQL pass through, so many of your views are only available in a VFP application.
SQL Server also supports views. Here is a T-SQL definition for the same view defined previously:
CREATE VIEW customerorders AS
SELECT customers.companyname, orders.orderdate,
orderdetails.productid, orderdetails.quantity, orderdetails.unitprice
FROM customers JOIN orders
ON customers.customerid = orders.customerid
JOIN orderdetails
ON orders.orderid = orderdetails.orderid
As with VFP views, SQL Server views can be used to simplify access to the data and can be made updatable. However, SQL Server views are available to any application that can access SQL Server tables, making them more flexible than VFP views. Furthermore, security rightscan be assigned to a view in SQL Server. Many developers and DBAs enforce security by withholding rights to table objects and allowing access to views instead.
User-defined data types
When you define a table in a VFP database, you are limited to the data types that VFP supports. In SQL Server, you can create a user-defined type, based on an intrinsic (that is, built-in to SQL Server) type, and use it when you define a table. For example, the US Fire Administration defines a set of codes, called 901 Codes, that are used for reporting fire incidents. When reporting a fire incident, there are literally hundreds of fields that could contain either 901 Codes or some other type of data. Here’s how we create a user-defined type for901 Codes:
IF NOT EXISTS (SELECT * FROM systypes WHERE name = 'udtCode901') EXEC sp_addtype udtCode901, 'char(4)', 'NULL'
Now we have two ways to define a table:
CREATE TABLE mytable (myfield char(4) NULL)
or:
CREATE TABLE mytable (myfield udtCode901)
What’s the difference? What have you gained by using the user-defined type? When you look at the schema for this table, you now know not only the structure of the column, but also something about the nature, or business domain, of the data contained in it because youknow it is designed to hold 901 Codes. Many developers spend a lot more time trying to figure out existing code than they do writing new code, and anything you can do to document your design will make somebody else’s (or your own future) work easier.
An important restriction on user-defined data types is that they do not provide inheritance. In other words, if the US Fire Administration changed the codes from four characters to five, you cannot simply modify the udtCode901 data type and expect the tables to pick up the change. Instead, you must first unbind or remove the data type from any columns where it is
used, and then make the modification to the data type. After the change is made, you can re-bind the data type to the appropriate columns.
Replication
Replication is the process of synchronizing multiple copies of a database. A company may have a database in the headquarters and a copy in each regional office. Each night these databases can be replicated so the headquarters and each regional office will have a copy of the latest data.
Visual FoxPro databases have no native support for replication. If you want to replicate a
VFP database, you must write the code to do it yourself. While messages occasionally appear
in online forums declaring how “easy” it is to do this, consider a group of conference attendees who were asked whether they’ve ever attempted this. Only a small percentage said yes, and of those, almost all gave up before completing the task. Those who completed it usually saidthey wouldn’t want to do it again.
SQL Server has built-in replication, which is handled as an administrative function. However, just because the replication is built in doesn’t mean it’s easy to get it to work. You still have to ensure that primary keys uniquely identify records, even across multiple copies of thedatabase.
Transactions
Visual FoxPro supports limited transaction protection with BEGIN TRANSACTION, END TRANSACTION and ROLLBACK. SQL Server’s transaction protection is far more robust, as explained in the “Point-in-time recovery” section earlier in this chapter. In addition, by exposingits transaction process to Microsoft Distributed Transaction Coordinator (MS DTC),
SQL Server can participate in transactions across databases, servers and even database systems. With MS DTC, multiple databases on multiple servers running SQL Server, Oracle and/or MSDE can all participate in the same transaction. Transactions are covered in greater detail in Chapter 11, “Transactions.”
Scalability
The term scalability is in vogue right now. Microsoft uses the word a lot because it’s hell-bent on overtaking Sun and Oracle in the enterprise market. Scalability is what the press has often claimed Windows NT and SQL Server lack in comparison to Oracle running on a Sun server.When an application is described as scaling well, it is typically meant that it can handle very high usage.
The term scalability can also be applied to applications written for high-usage environments that can be used in smaller systems as well. Chapter 7, “Downsizing,” addresses this downward scalability.
Visual FoxPro is capable of handling very large amounts of data, with the engine supporting tables up to 2GB. However, because the processing is handled by the workstation, really large tables usually cannot be handled efficiently.
SQL Server can handle terabytes of data. (A terabyte is a trillion bytes, or 1000GB.) To get an idea of just how big a terabyte of data really is, consider this: The entire 100+ year
history of every transaction ever performed on the New York Stock Exchange is approximately
500GB, or one-half terabyte!
SQL Server is a multi-threaded application that supports multiple processors. On a single- processor system, multi-threading is the key to preemptive multi-tasking. But multiple threads don’t actually improve performance, as the processor can only do one thing at a timeanyway. In fact, the overhead of thread switching will actually slow performance slightly.
But with a multi-processor system, multi-threaded applications can improve performance dramatically, as each thread can be assigned to a processor and thus the threads can run simultaneously. A single-processor server’s performance can be essentially quadrupled by replacing it with a quad-processor server. SQL Server 7.0, the current version at the time of this writing, supports up to 32 processors in the Enterprise version, and up to four processors in the Standard, Desktop and Small Business Server versions or in MSDE.
|
Versions of SQL Server prior to 7.0 required Windows NT, but starting with version 7.0, SQL Server is compatible with Windows 95/98, too. This means that the same database that can service a terabyte of data on a multi-processor Windows 2000 server can also run fine on aWindows 98 laptop.
Reliability
Visual FoxPro databases are processed on the local workstation. If 100 users are working on a table simultaneously, then portions of a table and its index exist in the memory of 100 different computers. The phrase index corruption causes a knowing nod of the head of most every VFP developer you’ll ever meet.
Such corruption issues are not a problem with SQL Server. For one thing, the data is only open in one place, not in multiple copies all over a network. Also, the demands of the enterprise market are such that client/server databases must be absolutely reliable in high- volume,mission-critical 24/7 applications.
Advantages of client/server
The advantages of client/server systems over file-server systems follow from the main differences between the two types of systems. This is a book about client/server development, so this section will primarily deal with advantages of client/server over file-server. But it shouldbe pointed out that client/server has disadvantages, too, the primary ones being cost and complexity.
SQL Server is licensed on a per-user basis, with licenses costing roughly $150 to $200 per user. The license fee may seem quite a leap to a VFP developer who’s used to a freely distributable database engine (but note that there are many SQL databases that cost considerablymore). In addition, there are administrative costs of a client/server solution. Large client/server databases require almost constant tuning to optimize performance. A changing user base requires that security be continually updated. For this and other reasons, SQL Server systems require a database administrator (DBA). For some systems, a part-time DBA is sufficient, but other systems require one or more full-time DBAs.
There is no way to get around the fact that client/server development is more complex and more expensive than developing file-server systems. If it weren’t, you wouldn’t need this book. For any given system, you can expect it to take longer and cost more to implement as a client/server system. But consider the advantages of client/server systems...
Performance
While it is true that Visual FoxPro has a blazingly fast database engine, its performance can degrade quickly when size and number of users increase and/or network bandwidth decreases. SQL Server is also blazingly fast. In fact, with identical moderate-sized databases on
identical computers, SQL Server query performance tends to be slightly better than VFP’s in most situations.
The real performance difference appears when you reduce the size of the network pipe. Over a slow network, you’ll almost always get significantly better performance from SQL Server. And with a really low bandwidth connection, like a modem, VFP can’t even compete. Thisis because SQL Server only needs to send requests and results over the wire, while VFP requires the transfer of everything necessary to process the query.
This performance enhancement has a cost. You must carefully tune your queries with the size of the result set in mind. The point is that reducing the size of the result set with SQL Server provides the lion’s share of the performance improvements, particularly with low- bandwidth connections. That’s because only the result set comes down over the wire. But the result set itself may be only a small part of what VFP needs to perform a query; therefore, carefully tuning a query for a small result set may not gain you any performance.
Cost
We mentioned that client/server solutions typically cost more than file-server systems, but under some circumstances, the reverse may be true. A good example of the cost savings provided by client/server is in large, widely spread fire departments. Most public agencies simplycannot afford the infrastructure necessary to support high-speed connections between widely dispersed fire stations and the database server. A modem and a connection to a local ISP may be the best they can do. Not only are high-speed connections beyond the budgets of manydepartments, but those alternatives simply aren’t available outside of metropolitan areas. And phone service in rural areas is often of poor enough quality that modem connection speeds are pretty slow compared to most metropolitan areas. So a high-speed solution isn’t affordable, and a file-server system with low-speed connections is unworkable. That leaves client/server,
which, while typically more expensive than the file-server solution, ends up being cheaper than a file-server solution of adequate performance.
Another cost factor is that a great deal of performance benefit can be gained by souping up the server. It may cost a lot less to get one really high-powered server than to have hundreds of top-of-the-line workstations. One can tune such a system to put a greater burden on theserver and perform less processing on the workstations. With a file-server system, all processing is performed on the workstation.
Security
A properly managed client/server database can be almost totally secure, no matter how you access it. File-server databases, on the other hand, have no security at all other than that
provided by the network. Anybody with Visual FoxPro and network access rights can do anything they want to a Visual FoxPro database, no matter how much effort is put into an application’s security model.
Scalability
Occasionally one hears about Visual FoxPro systems with VFP databases that handle hundreds of users and millions of records. But these systems are very unusual and are extremely difficult to implement. SQL Server can handle them with ease, as it can handle thousands of usersand terabytes of data. A client/server architecture is indicated for any system that must support a very large number of users.
Summary
In this chapter, you learned about the history of database systems, the features of client/server databases in general and SQL Server in particular, and the benefits of doing client/server development. In the next chapter, we’ll take a look at Visual FoxPro as a client/server applications development tool.
Posts
No comments:
Post a Comment