Microsoft SQL Server 6.5

No Longer Just a Sybase Port, Microsoft's RDBMS Enjoys a Tight Relationship With Windows NT.

Microsoft SQL Server has come a long way since it was first released as a Microsoft port of the popular Sybase SQL Server (then version 4.2). Many architectural and syntactic elements of the two systems remain similar, although they are slowly drifting a part when it comes to the detailed implementation of features. In recent releases of Microsoft SQL Server (6.0 and 6.5), Microsoft has made significant efforts to integrate the DBMS with Microsoft's BackOffice suite. SQL Server 6.5 is now very different from Microsoft's original SQL Server 4.2.

Because Microsoft SQL Server runs on only the Windows NT operating system, you can integrate the operating system and the DBMS on a much lower level. This arrangement not only brings performance benefits but also makes administration and other tasks easi er. For example, a set of built-in stored procedures enables application programs, DBMS triggers, and other stored procedures to send email.

Relational Data Model

When it comes to declarative referential integrity, Microsoft SQL Server supports the SQL-92 standard syntax. It does not, however, support the cascade keyword. For cascading referential integrity and customized error messages, you must revert to a manua l implementation using database triggers.

The database owner can define views on base tables or other views. The view definition can also be encrypted to protect the definition text. Microsoft SQL Server now supports the WITH CHECK OPTION feature, which ensures that updated values still comply w ith the view's original WHERE clause.

Microsoft SQL Server supports large objects in the form of TEXT and IMAGE data types. These data types can be almost 2GB long. You can browse these objects from the client using the ODBC API functions for manipulating large objects.

You have no control over the physical storage structure of the database tables and indexes, but you can specify tuning information such as fill factors and default behavior if duplicate data is encountered. One index per table can be a clustered index; t his index physically sorts the records.

As a descendant of Sybase SQL Server, Microsoft SQL Server also supports stored procedures. In SQL Server, a stored procedure can return multiple row data sets to an application. Stored procedures on one server can invoke remote stored procedures on anot her server, if the required permissions have been granted on both servers.

There are few restrictions on the type of SQL statements a stored procedure can execute. Stored procedures can start a transaction, create a table, manipulate the table's records, and then commit the transaction. They are very powerful in environments in which the application is a "thin" software layer that simply displays data and interacts with the user. Stored procedures can then be used to process each transaction on the server. In this way, all manipulation statements can be encapsulated within the database itself.

Stored procedures are usually defined using Transact-SQL statements but can also be specified as a function in an external dynamic link library (DLL). You can therefore add a lot of functionality to the server by adding customized functions defined in a DLL. These procedures are called extended stored procedures.

Microsoft's Open Data Services (ODS) provides another way to extend the DBMS. ODS lets you define a new instance of SQL Server that can be fully customized using a C application programming interface; it's a powerful tool for brave DBAs who have always d reamed of writing their own DBMS server. In this scenario, the ODS supplies only a skeleton server that accepts connections. The rest of the server must be implemented using C code.

SQL Server supports three triggers for each table: one each for INSERT, UPDATE, and DELETE operations. Triggers execute after each statement that changes data. The changed data is available through two virtual tables called Insterted and Deleted. Before SQL Server adopted the check constraints defined in the SQL-92 standard, these triggers were the only method available to let the server implement integrity checking.

One of Microsoft SQL Server's powerful new features is its ability to manipulate server-side cursors within stored procedures and triggers. A trigger or stored procedure can define, open, and traverse a whole cursor without sending the resulting rows bac k to the client. This capability enables a trigger to process each of the changed rows in turn, thus simulating row-at-a-time validation.

Queries

Microsoft SQL Server is compliant with Entry Level ANSI SQL-92, and it already contains many features specified on the higher levels of the standard. SQL Server 6.0 began supporting forward, backward, absolute, and relative-position server cursors. A new addition in version 6.5 is the ANSI SQL syntax for full and partial outer joins.

Microsoft SQL Server provides a complete implementation of the ODBC API. Developers can also use the native API called DBLibrary.

Database Administration

The same functionality lies at the fingertips of the application programmer in the form of the SQL Distributed Management Objects. These objects are a set of 32-bit OLE automation objects that let an application access the same management information ava ilable to the Enterprise Manager - which means that eager DBAs can write their own management applications, and sophisticated applications can incorporate management information in the application itself. For example, before running a very intensive proc ess, an application may check the amount of connected sessions to warn the user of possible effects on other users. Alternatively, an application may interrogate the locking information to see which user is blocking its access to a specific resource.

SQL Server 6.5 adds a new SQL Trace utility that can tell a DBA exactly which SQL statements are being executed by any particular user. You can define filters to restrict the amount of information displayed. The Enterprise Manager lets system administrat ors use the Database Maintenance Plan Wizard to enable all of the required routine maintenance tasks on the database. Also new in SQL Server 6.5 is the ability to define fallback servers that take over from primary servers in case of hardware failure.

When configuring SQL Server, you can choose to use standard security, NT integrated security, or mixed security. Standard security means that each user must be defined within SQL Server, each with a separate SQL Server password; thus a user may have two different sets of usernames and passwords, one for NT and one for SQL Server. With integrated security, the system manager can map the NT usernames directly to a SQL Server login. A single sign-on provides access to both Windows NT and SQL Server. Integr ated security can be easier for end users and system managers because fewer usernames and passwords must be created, maintained, and remembered. Mixed security lets the user choose whether to use a separate SQL Server login.

The SQL Server executive service provides a powerful scheduling mechanism for almost any job the system manager might want to run on a regular basis. It includes built-in jobs such as replication tasks and Transact-SQL commands; you can also specify oper ating system commands, which opens up just about anything you can think of. You can run jobs on a scheduling basis (for example, run the job every five minutes between 12:00 and 18:00 on Mondays) or on an ad hoc basis. You can also set the job to run whe n a specific alert is activated. Alerts can be defined on any SQL Server error or on user-defined errors. These alerts can also activate email to specific operators, or these operators can be paged when specific errors occur.

Microsoft SQL Server 6.5 adds the ability to back up individual tables. This new capability makes it easier to manage the more dynamic tables in a database. Database backup operations can run while users access the database, so SQL Server minimizes downt ime for system maintenance.

SNMP (Simple Network Management Protocol) Support, added in version 6.5, makes it possible to monitor and view server installations and their databases from any SNMP monitoring application. You can set conditions to send alert messages to any workstation running SNMP monitoring applications.

Connectivity and Distribution

Remote procedure calls (RPCs) have always been a strong feature in SQL Server. With an RPC, results can be retrieved from any SQL Server on the network. You can also make changes to remote databases using RPCs. The latest release adds the ability to auto matically coordinate changes made by RPCs by using an automated two-phase commit protocol, enforced by the Microsoft Distributed Transaction Coordinator.

Replication

You can also customize propagated changes by specifying stored procedures to execute when updating the target database, rather than the default SQL INSERT, UPDATE, and DELETE statements. This feature can be applied for more than just replication; you cou ld use it, say, to write your own audit trails of changes made to the database.

When specifying check constraints on base tables, a user can use the notfor replication keywords, which will cause the checks to be validated only if the changes are not made by the replicator on the target database. Data that is checked at the source da tabase does not have to be checked again in the target database.

Because the changes to the database are recorded using the standard transaction log, this replicator has very little effect on the source database. Large transactions may, however, affect other users accessing the target database, because the replicator executes standard SQL to make the changes in the target database. The SQL Server replicator uses ODBC to connect to the target database, so you can replicate to a wide range of heterogeneous databases.

Internet Support

A DBMS in its Own Right

Microsoft intends to make its SQL Server a leading DBMS for implementing new technology such as distributed processing and Internet access. Microsoft SQL Server is also one of the fastest solutions for connecting a desktop or laptop computer with a datab ase using ODBC. By including SQL Server as an integral part of the Microsoft BackOffice suite, BackOffice users automatically receive runtime licenses for SQL Server, making it easier to use SQL Server in other home-grown applications.

By making Windows NT and SQL Server available on a large number of scalable hardware options, the same technology can be used on computers ranging from the laptop to huge data warehouses on RISC machines. This scalablity makes SQL Server a viable DBMS op tion for the Microsoft Windows NT network operating system, independent of the size or hardware architecture of the server machine.