This section focuses on maximizing the benefits of Microsoft SQL Server server-side resources. There are several areas on the server-side that relate closely to the overall performance of your application system. Although these topics fall out of the scope of mainstream application development, when used effectively, they provide benefits that are just as important to the overall performance of the system.
Three of the most versatile and powerful server-side resources are triggers, declarative referential integrity (DRI), and views. Triggers, DRI, and views are often used to reduce the complexity of the client and application. Used effectively, these features can improve the performance of your application.
Triggers are special stored procedures that go into effect when data is modified in a specific table. They are often used to enforce business rule consistency across logically related data in different tables. Triggers are executed automatically when data modification occurs, regardless of the application interface that is used. Therefore, triggers can maintain business rule integrity even when ad-hoc data modification occurs.
Referential integrity refers to the constraints used to manage parent-child relations among tables where records cannot exist in the child table without a corresponding record in the parent table. For example, using an order item and order-line item scenario, records should not exist in an order-line table if there isn't a corresponding record in the orders table. SQL Server enforces referential integrity automatically at the server using FOREIGN KEY constraints defined in the CREATE TABLE or ALTER TABLE statements DRI. Because Btrieve does not offer this feature, the Btrieve application performs all referential integrity at the client. Using DRI eliminates this processing from the client and provides slight performance improvements. Triggers and DRI also eliminate the need to change the application in multiple places if table schemas or relationships change. These modifications can be made at the server by updating the triggers and DRI settings that are involved.
A view is an alternate way of looking at data in one or more tables. Views allow users to focus on data that fits their particular needs. Views simplify the way users can look at and manipulate the data. Frequently used joins and/or selections can be defined as views so that users can avoid respecifying conditions and qualifications each time additional data operations are performed. Views also provide logical data independence because they help to shield users from changes in the structure in the base tables. In many cases, if a base table is restructured, only the view has to be modified, instead of each individual reference to the table.
For more information about views and triggers, see the Microsoft SQL Server Administrator's Companion and various articles available in the Microsoft Knowledge Base.
The Btrieve environment may seem simpler to use and administer than Microsoft SQL Server. However, this simplicity comes at a price. Btrieve has several architectural shortcomings that add complexity to applications that access it.
Although SQL Server is a more complex DBMS than Btrieve, it is much more powerful and offers many substantial performance benefits and features, such as an extremely robust query processor, advanced locking, and transaction capabilities. SQL Server provides the following features and tools that can be used to reduce the complexity of administering the system:
Many aspects of database administration can be automated using the scheduling capabilities of SQL Enterprise Manager and through the Windows NT scheduler. Ideal candidates are those administration processes that need little or no interaction for execution. For example, the following administrative candidates can be automated using these scheduling services in most environments: database and transaction log backup and restore, DBCC command execution (a feature that checks to see if there are corruption issues within a database), batch processes, and so on. By automating these processes, you increase the ease of use of the application system because you limit the amount of hands-on maintenance needed to keep the system up and running.
An extremely powerful aspect of SQL Server is the ability to generate a Windows NT Performance Monitor alert. Alerts can be used to notify administrative personnel of crucial or unusual events. When the alert is triggered, it can send e-mail or page administrative staff or run a program or batch file. This allows administrators to react immediately to the event so that the appropriate action can be taken. Alerts can also be used to automate database administration duties. For example, an alert can be set to dump the transaction log of a database once it becomes 80-percent full.
For more information on Performance Monitor and alerts, see the Microsoft Windows NT 3.5 Server System Guide. For more information on SQL Server related Performance Monitor counters, see the Microsoft SQL Server Administrator's Companion.
SQL Server maintains a write-ahead transaction log for each database. The transaction log feature is designed to provide the ability to recover transactions and other data modifications in the event of a problem. The default configuration is to fill the transaction log until it is backed up to tape or another disk. This allows incremental backups and point-in-time recovery. However, this requires that some automated process or an administrator must perform these backups on a regular basis before the transaction log becomes full. If the application developer does not need or want to take advantage of this SQL Server feature, the trunc. log on chkpt. database option forces the database transaction log to be flushed by SQL Server at certain intervals and eliminates the need to back up the database transaction log and to deal with transaction log space management issues.
For more information on transaction logs and the trunc. log on chkpt. database option, see the Microsoft SQL Server Administrator's Companion.
SQL Server provides a Database Maintenance Plan Wizard that can be used to automatically schedule daily or weekly routine database maintenance tasks, including database and transaction log dumps, database consistency checks, index rebuilds, and statistics updates. The Maintenance Wizard can be run from SQL Server Enterprise Manager or the Sqlmaint.exe utility that ship with Microsoft SQL Server.
For more information about the Maintenance Plan Wizard, see the Microsoft SQL Server Administrator's Companion.
As your application grows, the database it references may need to grow with it. The database may need to grow because more data is being added to it. The transaction log many need to grow because there are larger numbers of uncommitted transactions associated with larger numbers of users who are accessing the system. SQL Server databases and transaction logs are not designed to automatically expand themselves beyond the space that has been allocated to them. Therefore, the database administrator or the application developer must perform this task. The following lists these sizing issues and presents a few ways to address them:
You can perform these tasks using alerts or by adding functionality to the application itself. For example, the following stored procedure can be used to expand a database and can be called from within an application as needed. The stored procedure expands the database and its database devices for the dbname database passed to the stored procedure. Assuming the database resides on one device, the stored procedure calculates the new size needed to contain the expanded database and expands the database device. Once this has been accomplished, the stored procedure then expands the database itself.
create proc dbexpand (@dbname sysname, @dbdevice sysname, @increment int) as
set nocount on // Turns off message indicating number of
// rows an operation affected
declare @current_size int // Variable to store current database size
declare @new_size varchar(12) // Variable used to store new database size
declare @cmd varchar(100) // Variable to hold the ALTER DATABASE
// statement string
/* Get current size of the database device for this database from sysdevices system table in the master database, add @increment converted to pages. SQL Server stores data in page units so we must determine the number of pages to be added */
/* Get current database device size from the sysdevices master database table for the desired database */
select @current_size=high-low+1 from master..sysdevices where name = @dbdevice
/* Determine the number of pages needed for the new database size. Convert this to megabytes units by multiplying the number of pages by 512 */
select @new_size = convert(varchar, (@current_size + (@increment * 512)))
/* Build the statement string to expand the database device and execute the command */
select @cmd='DISK RESIZE name = ' + @dbdevice + ', size = '+@new_size
--select @cmd
EXEC (@cmd)
/* Build the statement string to expand the database and execute the command */
select @cmd='ALTER DATABASE '+ @dbname + ' ON '+ @dbdevice +' = ' +
convert(varchar, @increment)
EXEC (@cmd)
For more information on database devices and how they are used with databases, see the Microsoft SQL Server Administrator's Companion.