Revised: April 1994
Technologies such as client-server networks and graphical computing have created new ways to develop applications while promising greater productivity at lower cost. Early client-server database systems promised such cost savings through "downsizing," or "reengineering." While this was sometimes achieved for small or medium-sized applications, lack of scalability limited the number of serious applications that actually benefited. In addition, software complexity and lack of management tools introduced new costs, often offsetting the advantages of downsizing in the first place.
Microsoft® SQL Server™ for the Microsoft Windows NT™ operating system addresses these needs. This powerful, scalable relational database platform offers great ease of use and manageability for distributed client-server applications. Part of a family of Microsoft client-server products, SQL Server meets the needs of organizations that are reengineering line-of-business systems to take advantage of open, standards-based computing.
With Microsoft SQL Server for Windows NT, client-server systems are powerful, to meet needs for growth, and reliable, to support mission-critical applications. They are also manageable, to reduce operational costs, and open, to protect legacy investments.
Building on the industry's leading architecture for client-server computing, SQL Server has been significantly reengineered for the Windows NT operating system and incorporates enhancements and performance improvements unavailable in previous versions. Designed for mission-critical database applications, SQL Server for Windows NT also makes extensive use of the Microsoft Windows™ user interface to improve ease of use and manageability.
SQL Server for Windows NT preserves application and data compatibility with Microsoft SQL Server for OS/2®. In addition, interoperability with SYBASE® SQL Server environments is enhanced, thus providing a true cross-platform database solution for client-server applications.
This backgrounder discusses the architectural approach, technical features, and new capabilities offered by Microsoft SQL Server for Windows NT. This paper is not an overview of SQL Server in general; it is helpful if the reader is familiar with other versions of SQL Server from Microsoft or Sybase.
In order to be successful, every major software project needs a small set of central goals. Conflicts and trade-offs are inevitable in software, but when such conflicts arise, consideration of the impact on the design goals leads to the "correct" choice. When goals are in conflict, the one of highest importance wins.
For Windows NT, we had an opportunity to completely reexamine how a database server should interact with the operating system. We knew we had to make it fast and scalable to exploit the multiprocessing architecture of Windows NT. But we also wanted to make the system much easier to install, manage, and use. Complex back-end database software of this caliber had never been designed to work in a graphical computing environment before. Therefore, we chose the following design goals, to which we've paid careful attention:
A prerequisite for all of this was to preserve application and data compatibility to the greatest extent possible with SQL Server version 4.2 for OS/2. This was both to protect customers' existing application investments as well as to make it easy for customers to migrate to the Windows NT platform.
Because different version numbers are used on different platforms, we are often asked if versions of SQL Server from Microsoft and Sybase are equivalent. It helps to understand that version 4.2 is the latest release of SQL Server generally available for most platforms, including NetWare®, OS/2, and SCO® UNIX®.
Sybase also ships a symmetric multiprocessing version of SQL Server for some UNIX systems called version 4.91. We could have based our first release for Windows NT on this version; however, version 4.91 introduces changes to the database structure and system catalogs that make it impossible to back up and restore data between 4.2 and 4.91 databases.
We chose instead to start with the general availability version 4.2 baseline to ensure compatibility for current customers. However, SQL Server for Windows NT is far from a "straight port" of the 4.2 code base. It is a significant rewrite that includes many enhancements in the layers of the software that interact directly with the operating system. Importantly, it supports symmetric multiprocessing in a new way that does not require the structural changes introduced in Sybase 4.91. Altogether, SQL Server for Windows NT is the most advanced version of SQL Server yet.
Future versions of Microsoft SQL Server will leverage the System 10 code line and will incorporate the Windows NT–specific enhancements discussed in this paper. We will continue to enhance SQL Server to closely integrate with Microsoft's emerging operating systems, tools, and workgroup computing technologies.
Perhaps the most notable difference in SQL Server for Windows NT is scalability. With SQL Server for OS/2, the performance ceiling is essentially the fastest single processor, Intel®-based hardware system available. Windows NT changes that—SQL Server for Windows NT runs on a state-of-the-art operating system that is scalable from notebook computers to symmetric multiprocessor super servers, with support for Intel and RISC hardware architectures.
Windows NT is an ideal platform for a database server because it provides a fully protected, secure, 32-bit environment on which to base mission-critical applications. Preemptive scheduling, virtual paged memory management, symmetric multiprocessing, and asynchronous I/O are the foundations needed for a great database server platform. Windows NT provides these foundations, and SQL Server uses them fully.
Under Windows NT, the system capacity and scalability of SQL Server is increased in several ways:
As an example of the importance of scalability, consider that in high transaction environments, SQL Server under OS/2 eventually becomes CPU-bound. Without the scalability offered by symmetric multiprocessing, there is little alternative but to attack the problem using multiple servers—which is difficult unless the data is easy to partition. Although dual processor computers such as the COMPAQ SystemPro exist, they are of limited benefit for OS/2, as there is no way to use both processors effectively. With SQL Server for Windows NT, that additional processor can provide a significant boost in performance, which translates to larger applications, greater numbers of users, and higher capacity.
Platform independence, high capacity, and scalability allow SQL Server to meet a broad range of enterprise computing requirements. It is especially suitable for applications that require high levels of performance and capacity.
SQL Server for Windows NT approaches multiprocessor scalability differently than other SMP database systems do. Two characteristics separate this approach from other implementations:
On a nonthreaded operating system such as UNIX, a typical SMP database server has multiple DBMS processes, each bound to a specific CPU. These processes communicate with each other using shared memory, which is used to maintain the cache, locks, task queues, and user context information. The DBMS must include complex logic that takes on the role of operating system: scheduling user tasks, simulating threads, coordinating multiple processes, and so on. Since processes are bound to specific CPUs, dynamic load balancing can be difficult.
Because one of our key design goals is to use native Windows NT facilities wherever possible, SQL Server relies on Windows NT to do as much as possible. Rather than use multiple processes, SQL Server uses a single process and multiple operating system threads. Since all threads belong to the same process, a single address space is present, and there is no need to coordinate processes through shared memory.
By using native operating system threads, task scheduling within SQL Server is preemptive, providing smoother operation and greater fault isolation. Since Windows NT schedules threads on the most available CPU dynamically, SQL Server automatically achieves optimum load balancing across multiple CPUs.
It is notable that on Windows NT, the very same SQL Server binary supports both single-processor and multiprocessor systems: no special configuration is necessary.
To understand this approach further, it is helpful to delve deeper into the threading implementations of SQL Server for OS/2 and Windows NT.
Although OS/2 is a multithreaded operating system, the threading approach in SQL Server is quite different. Under OS/2, limits on the number of threads per process required SQL Server to simulate its own threads. SQL Server manages each simulated thread using its own nonpreemptive scheduler. (This is also how SQL Server works on nonthreaded operating systems such as UNIX or NetWare.)
On OS/2, a single OS worker thread actually manages and services all simulated user threads. This thread does an extraordinary amount of work. It manages the network interface, listening for new connections and establishing them when detected. That task alone is intensive, but in addition it services all client requests. If 50 connections are using SQL Server simultaneously, one thread manages and services them all.
SQL Server employs a simple, nonpreemptive scheduling algorithm to "round robin" between the simulated client threads. Each client runs until either it blocks (by waiting for a resource such as a lock) or until a specified period of time has elapsed (the length of which is controlled by the "timeslice" configuration parameter). If a client runs without being blocked for the entire timeslice, the client is expected to yield and another client is serviced. Since each user connection needs to maintain its own separate context of execution, a separate stack is maintained for each. So every time a different connection is serviced, the thread must do a stack switch as well.
Although this implementation is fine given the constraints of OS/2, there are some drawbacks. First, this approach has a tendency to fool the operating system. Both OS/2 and Windows NT seek to run the highest-priority thread that is currently "runnable" (that is, not blocked waiting for a resource or event). If multiple threads of the same priority are all runnable, they all get their turns. However, OS/2 does not know that one SQL Server worker thread is doing the work of 50 clients. As a simplistic example, suppose there are 50 other runnable threads of the same (normal) priority as this worker thread. The worker thread gets scheduled only about 2 percent (1/51) of the time, though it is doing the work of many. It can be difficult to use other applications on the same system as SQL Server for OS/2 because of these scheduling difficulties.
A second drawback results if a page fault occurs. If the thread performs some instruction that causes the OS/2 virtual memory manager to address memory that is not currently in real (physical) memory, a page fault is generated. This triggers the OS/2 memory manager to read the page from disk. The thread is frozen until the operating system succeeds in swapping the memory from disk back into physical RAM. However, because one thread is servicing all the clients, they are all suspended while the page fault is handled.
Finally, what if the thread doesn't voluntarily yield? A hardware or software failure can result in a runaway situation in which control never returns. For this reason, another OS/2 thread exists simply as a "watchdog." If a client doesn't yield its timeslice, the watchdog thread "infects" the worker thread by lowering its priority to idle and takes over the worker thread functions. (If you have ever seen an "infected process" or "timeslice" error in the SQL Server error log, this is what has happened.) Although the runaway task is at idle priority where it cannot do any harm, you must stop and restart SQL Server to solve the problem fully.
Because OS/2 lacks true asynchronous I/O capabilities, SQL Server simulates these capabilities using threads. Each SQL Server disk device uses one thread to perform synchronous I/O for that device. Using multiple disk devices allows for some overlapping of I/O, provided the data is spread manually over the multiple disk devices.
Under Windows NT, SQL Server makes greater use of the native thread services of the operating system. Instead of one "worker thread" there are many: Separate threads handle each of the networks SQL Server simultaneously supports, another thread handles database checkpoints, and a pool of threads handles all users. The operating system schedules all threads preemptively and selects the most available CPU for each thread automatically.
Nominally, you can think of each user as having a separate OS thread, which in a majority of cases is true. However, the solution is a bit more sophisticated than that. It is inefficient to use hundreds of separate OS threads to support hundreds of users, so SQL Server establishes a pool of worker threads.
When a client issues a command, the network handler places the command in a queue, and the next available thread from the worker pool takes the request and services it. If no idle worker thread is available when a request arrives, SQL Server creates a new thread dynamically, until it reaches the maximum thread limit. As the workload decreases, SQL Server gradually eliminates idle threads to improve memory utilization.
Under this scheme, a worker thread runs each user request to completion. Because each thread has its own stack, there is no need for stack switching. If a given thread performs an operation that causes a page fault, only that thread, and hence only that one client, is blocked.
The maximum number of threads in the worker pool is configurable. The default value is generally the same as the number of user connections. However, for very large numbers of users, overall throughput may be increased by setting the maximum worker threads to a lower value, which reduces task switching within the system. You can set the maximum number of threads available in the worker pool using a new sp_configure parameter.
Windows NT does all scheduling of the threads. Because the number of SQL Server threads is much more representative of the true work going on in the system, scheduling is more equitable. In the example cited for OS/2, one thread supported 50 clients but competed with 50 other threads on the system for scheduling, which resulted in SQL Server being scheduled only 2 percent of the time. Under Windows NT, SQL Server will commonly have 50 separate threads supporting those 50 clients; on average, the thread might be scheduled 50 percent (50/100) of the time.
Since threads are no longer expected to yield, but rather are preemptively scheduled, there is no need for a watchdog thread. Even if a thread becomes a runaway, no other clients are affected. The concept of a timeslice or infected process error no longer exists with SQL Server for Windows NT.
Because Windows NT supports true asynchronous I/O, it is now more efficient for each user thread simply to issue its own I/O requests asynchronously and be called by the OS on completion. So, there are no longer separate threads for each SQL Server disk device. Using the asynchronous I/O capabilities of Windows NT allows SQL Server to make much better use of hardware technology such as intelligent disk arrays. With data striping and RAID support in Windows NT and the Windows NT Advanced Server operating system, you can achieve overlapped I/O without manually spreading the data within SQL Server.
In this section we have contrasted the multithreaded approach in existing SQL Server implementations with that of SQL Server for Windows NT, which makes extensive use of native operating system threads. The benefits of a native multithreading approach are several:
A major design goal of SQL Server for Windows NT is increased performance, even on single-processor hardware. In implementing the changes necessary for a native multithreaded architecture, Microsoft also incorporated many internal enhancements that improve performance. These enhancements fall into the following areas:
In this section, we'll examine each of these areas in detail.
The changes made to the optimizer and the cache-management subsystem improve index selection, optimize memory utilization, and increase transaction throughput.
The optimizer has been enhanced to perform more accurate costing of physical I/Os for nonclustered indexes. In addition, the optimizer will now use a nonclustered index for queries containing an ORDER BY clause if the index covers the sort column. Previously, only clustered indexes were used to optimize an ORDER BY—if no clustered index was available, SQL Server would sort the data in temporary work space. The use of nonclustered indexes significantly improves ORDER BY processing and reduces sorting and temporary table creation.
A checkpoint is an I/O-intensive process that flushes all changed database pages from memory cache to disk. In previous versions of SQL Server, a checkpoint interrupts the processing of new transactions while all pages are flushed. However, SQL Server for Windows NT spawns a thread to perform checkpoints asynchronously, writing pages in the background via a lazy-write scenario. This substantially reduces the impact of checkpoints on system throughput.
Whenever a new page must be added to cache, SQL Server scans the cache from front to back searching for free space. Previously, if the cache was full, SQL Server would scan the entire page chain before it decided to flush a page to make room for the new page in memory. With a very large memory cache, it could take longer to do a full scan than simply to flush a page. A new "free page scan" limit restricts how much of the cache SQL Server scans before electing to flush a page. This optimizes performance for large memory caches.
Significant changes have been made to the I/O and transaction log subsystems to take advantage of multithreaded, parallel operation.
Windows NT has a sophisticated asynchronous I/O subsystem. SQL Server makes extensive use of the facilities provided by the operating system to achieve a high level of parallelism in I/O processing. SQL Server performs all reads and writes asynchronously, supporting efficient use of intelligent disk controllers. This approach is optimal for all types of hardware.
In addition, SQL Server now writes multiple pages in a single I/O operation rather than a page at a time. This approach is optimized for SMP and array disk technology. The administrator can "tune" the number of pages SQL Server passes to Windows NT in each write request.
Because changes to the transaction log cannot be cached, log writes are the most I/O-intensive operations performed by a DBMS, with new pages allocated and deallocated all the time. Microsoft has enhanced SQL Server for Windows NT so that all log page allocation and deallocation occurs an extent (8 pages) at a time. This reduces overhead and increases performance.
Previously, an UPDATE affecting columns that contained variable-length data or nulls would resolve to a combined DELETE and INSERT, even if the physical length of the data was unchanged. SQL Server for Windows NT now performs all UPDATEs that do not change the physical length of the data "in place," regardless of the datatypes affected. This greatly improves throughput for many types of transactions.
To achieve the best performance it has always been possible to partition data in SQL Server across multiple physical disks using devices and segments. However, partitioning the data manually is a complex process requiring significant expertise. Now, using the built-in RAID support in Windows NT, you can achieve the benefits of data partitioning in SQL Server automatically and transparently. This approach also simplifies database restoration because segment mapping is eliminated.
Microsoft has completely rewritten the lock manager to operate in a preemptive multithreaded environment. Now fully reentrant and thread-safe, it makes use of finer-granularity lock acquisition for cache buffers, page chains, and other items, to reduce internal resource contention. This enhancement allows SQL Server for Windows NT to support higher internal transaction rates and greater numbers of concurrent users without blocking.
Data transfer operations have been significantly enhanced in SQL Server for Windows NT.
To ensure valid data structures, SQL Server "zeroes out" every byte on disk when creating databases. Previous versions of SQL Server performed this one page (2K) at a time, resulting in long CREATE DATABASE times for large databases. Under Windows NT, SQL Server performs this operation in 60K blocks, dramatically reducing the time it takes to process a CREATE DATABASE request.
To improve backup and restore processing with very large databases, SQL Server for Windows NT now performs DUMP and LOAD operations in 60K blocks rather than one page at a time. This significantly reduces the time it takes to back up and restore large databases.
SQL Server now allows you to increase the size of the network packets used to communicate between client and server from 512 bytes to 32K for named pipes networks. For large data transfers, increasing the network packet size to an optimal value (usually 4K or 8K bytes) can dramatically increase network throughput and double the performance of bulk load operations using BCP. New command-line switches are available in ISQL and BCP, and a new DB-Library™ function call allows programmers to request larger packet sizes for applications. BCP operations under Windows NT now default to the larger network packet size for best performance. All changes are forward compatible with System 10 and backward compatible with existing SQL Server platforms.
SQL Server uses the special temporary database, tempdb, as work space for sorting and for creating temporary tables in some join operations. Creating tempdb in RAM can substantially speed up these operations. Because SQL Server recreates tempdb every time it is restarted, this is a safe operation even if there is an unexpected shutdown, such as a power failure. Not all sites will want to create tempdb in RAM because this makes less memory available for SQL Server's data cache; however, this is one more tuning option a site can add to its arsenal. It can be a big win for applications that do not get high hit ratios from the data cache or that perform operations demanding substantial access to tempdb.
The administrator can establish tempdb in RAM through a new sp_configure option, tempdb_in_ram. Once configured, the next time you start SQL Server, it creates a device called IN_RAM (actually allocated memory) and moves tempdb to that device. If you enlarge tempdb while it is in RAM (using ALTER DATABASE), SQL Server adds the new space to the DEFAULT device. The new space moves to the IN_RAM device the next time you stop and restart the server.
When tempdb is in RAM and you want to move it back to the MASTER device, set the tempdb_in_ram option again using sp_configure (sp_configure 'tempdb_in_ram', 0). When switching back to the MASTER device, tempdb starts with 2MB, regardless of what its size was in RAM. To increase it again, use ALTER DATABASE.
SQL Server for Windows NT offers the administrator many new performance statistics for more intelligent tuning and configuration. A new DBCC command, SQLPERF, now provides a wealth of information such as page reads and writes, cache hit ratio, log writes, instantaneous transaction rate, and so on. SQL Server also exports this information to the Windows NT Performance Monitor (discussed more fully below), which provides a graphical view of system performance.
SQL Server is fast and scalable to exploit the multiprocessing architecture of Windows NT. However, an equally important design goal was to make the overall system much easier to manage and use. Complex back-end software of this caliber had never been engineered to work in a distributed, graphical computing environment before. We've paid careful attention to feedback from customers who have been deploying client-server systems on distributed networks. Many of the features of SQL Server for Windows NT are designed to improve operational control and manageability, particularly in distributed environments.
SQL Server for Windows NT is a full 32-bit implementation using the Microsoft Win32® APIs and runs completely within the highly protected Win32 subsystem. In addition to the server, all other components in the SQL Server family are now 32-bit and, when appropriate, multithreaded, including administration tools, programming interfaces, and utilities. SQL Server itself ships with an integrated set of management tools that make extensive use of the Windows user interface to improve ease of use. SQL Server also integrates directly with Windows NT management facilities such as the Performance Monitor, allowing remote monitoring and troubleshooting of system performance.
SQL Server runs as an integrated operating system service under the control of Windows NT. The administrator can start, stop, and pause SQL Server from the Windows NT Control Panel or from a SQL Server component called the SQL Service Manager. These operations can also be performed remotely, making centralized administration of multiple servers easier. The SQL Service Manager displays the current status of SQL Server (or SQL Monitor) even while minimized. The administrator can start many instances of the SQL Service Manager, each controlling a separate server, to get an instant visual reading of the status of each of the servers.
Pausing SQL Server is a new capability under Windows NT. If paused, SQL Server will deny requests for new connections while continuing to process all current client requests. This is useful for performing an orderly shutdown of the system, during which time you might want to limit new connections but allow existing clients to complete their work.
SQL Server maintains startup parameters in the Windows NT Registry, eliminating the need to manage multiple operating system configuration files. This approach also keeps the server configuration secure and protected from accidental modification. The SQL Server Setup program provides easy modification of startup options and shields the administrator from having to modify the registry directly.
SQL Server for Windows NT includes a graphical Setup program that simplifies installation and allows the setting of system configuration options in a much more intuitive and dynamic manner. The administrator can run Setup locally or over the network to install SQL Server on remote computers. The Setup program also functions as an automated migration tool for existing installations of SQL Server for OS/2, either version 1.1 or version 4.2. Setup installs the Windows NT binaries into the same directory tree as the OS/2 server without disturbing the existing installation. When upgrading from an existing OS/2 4.2 server, there are no structural changes made to the database, making it possible to go back and forth during a trial period simply by rebooting the computer between Windows NT and OS/2.
SQL Server integrates with the Windows NT Performance Monitor, which lets administrators and database designers quickly and easily view and tune server performance characteristics, even from a remote Windows NT-based computer. Many of these statistics are also available by using a new DBCC extension, DBCC SQLPERF. New statistics include statistics about the cache hit ratio, buffer usage, and I/O activity. Other statistics include information by user, such as CPU and I/O usage, and many levels of granularity regarding locks currently being held.
The Windows NT event log provides a single repository for information, warning, and error messages for the entire system. Windows NT uses the event log to post messages about the system, the network, and security violations. Administrators can use the Event Viewer administration tool to view the event log of any servers on the network.
Applications can also use the services of the event log through a provided API. SQL Server for Windows NT exploits this system-wide capability by posting all of its messages to the Windows NT event log as well as to the standard SQL Server error log file. The Event Viewer provides options to filter certain events, as well as options for how long to retain logs and whether to overwrite them. The Event Viewer operates across the network, making it easy for a single administrator to monitor multiple SQL Servers simultaneously.
SQL Server now supports an integrated security option that provides a single logon to both the network and database server for named pipes users. With integrated security, access to SQL Server is controlled through privileges established for users and groups of Windows NT. The administrator does not have to maintain a separate set of logon accounts and passwords in SQL Server.
Integrated security in SQL Server provides administrators with:
A mixed mode option provides additional flexibility for both trusted and nontrusted connections.
SQL Server ships with an integrated set of management tools that make extensive use of the Windows user interface to improve ease of use. All administration tools are available in both 32-bit and 16-bit Windows-based versions, allowing many administrative functions to be performed from workstations running the Windows version 3.1 operating system.
For Windows NT, Microsoft has enhanced the graphical administration tool that premiered with SQL Server version 4.2 for OS/2. It is now fully 32-bit and uses multiple threads for long-running operations, which greatly improves its responsiveness and puts an end to long waits for the hourglass. Because SQL Administrator takes advantage of the Microsoft Windows operating system, tasks such as adding users or managing disk space become a simple point-and-click activity. SQL Administrator can manage multiple SQL Servers (both Windows NT and OS/2) across the network.
SQL Object Manager is a new graphical tool that provides database change management functions and supports easier creation and maintenance of all SQL Server database objects, such as tables, indexes, triggers, views, rules, and defaults. SQL Object Manager provides an interactive data transfer function, permitting easy and rapid import and export of data. In addition, SQL Object Manager can "reverse engineer" the schema from an existing database into a Transact-SQL™ DDL script so that the database structure can be recreated on another server. Like SQL Administrator, there is a version for standard Windows 3.1 and a full 32-bit, multithreaded version for Windows NT.
We have redesigned the interactive SQL query interface shared by SQL Administrator and SQL Object Manager and also made it available as a stand-alone tool, ISQL/W. The query facility is now much faster and has been enhanced to support very large result sets. New features include a graphical display of SHOWPLAN output, with color coding to highlight table access methods (red = table scan, green = clustered index, and so on). The tool can also display I/O statistics in graphical form. These features can help programmers optimize and tune queries.
SQL Server client software can communicate over multiple networks using installable Net-Libraries™. It is now much easier to set up network support for workstations running Windows and Windows NT using an enhanced client configuration utility. You can easily change the client's default Net-Library from named pipes, IPX/SPX, or Banyan® VINES®/IP. It is also a point-and-click operation to set up an alternate Net-Library connection for specific servers.
SQL Server for Windows NT has been enhanced in a number of ways to improve reliability and availability, issues particularly important for production database platforms. We've also paid careful attention to the area of fault-recovery, ensuring that SQL Server can be brought back online without operator intervention. This capability is particularly important in a system designed for distributed operation.
SQL Server supports dynamic backup of databases and transaction logs while users are updating the database, providing the highest level of system availability. Scheduled backup capabilities are provided by SQL Monitor, a separate service that works in conjunction with SQL Server. Using SQL Monitor, it is possible for automatic backups to occur on a regular basis—weekly, daily, hourly, or any combination thereof. SQL Server for Windows NT supports more than 50 types of popular tape drives and the list is growing.
A new capability with SQL Server for Windows NT is the backup of multiple database and logs to the same tape. Combined with scheduled backup, it is now possible to perform fully unattended backups without operator intervention. SQL Server provides a tool for scanning tape headers if the DBMS is offline in a disaster recovery situation.
A server should never crash, and SQL Server has always been highly reliable. Yet, as in all complex software environments, unforeseen errors can occur. In these cases the server may generate a protection violation (known as a "trap"). Under other protected operating systems, including OS/2, UNIX, and VMS®, if a client issues some obscure command that causes SQL Server to trap, the operating system will terminate the entire SQL Server process. (This is an important feature of protected operating systems, since in an unprotected system the results are much worse—corruption of system integrity and, at least, the need to cold boot the computer.)
However, Windows NT and SQL Server uniquely go an important step further. By using a feature of Windows NT called structured exception handling, SQL Server can recover gracefully from protection violations by terminating only the thread of the client that issued the offending command. All other users can continue safely and unaware. Structured exception handling provides superior reliability and ensures the highest availability of the system in mission-critical environments.
It is now possible to monitor how full a SQL Server database log has become and to automatically dump the log or alert the administrator if log utilization reaches a specific threshold. SQL Server makes this possible by exporting a new "log % full" statistic to the Windows NT performance monitor. The administrator can establish events, such as running a batch script to dump the log, that are automatically triggered whenever this statistic reaches a certain limit. This capability can substantially reduce the risk of downtime due to a full log.
The administrator can configure SQL Server to start automatically when the system is powered on, enabling SQL Server to come back online automatically in case of power failure or other hardware fault. As before, database recovery is automatic. In addition, should the SQL Server process ever fail for any reason and the server continue to run, SQL Monitor can attempt automatic restart of SQL Server without operator intervention.
For Windows NT, Microsoft has enhanced the Transact-SQL KILL command so that even a "sleeping" process can be terminated. Because SQL Server client connections use real Windows NT threads, all resources the user may have held (such as locks) are gracefully released to maintain system integrity.
SQL Server now handles various common errors in a simpler manner. For example, if a previous version of SQL Server was configured for fifty clients and a fifty-first tried to connect, that client would get a message stating that "SQL Server is unavailable," which the user would normally interpret to mean that the DBMS was offline. Now, a specific message states that there are no additional configured connections available. Another example deals with recovery with a full master database log. Previously, SQL Server would not start if the transaction log was full. SQL Server will now start even in this situation. Network errors are better propagated to both the SQL Server and SQL Server clients. Many other error messages have also been improved.
Microsoft has also developed a new SQL Server Troubleshooting Guide that is now included with SQL Server. This book provides solutions for most common problems and is a valuable addition to the existing SQL Server documentation.
One of our goals was to exploit the unique features of Windows NT to extend SQL Server's database management capabilities. In particular, SQL Server needed to integrate well with Microsoft's Windows™ Open Services Architecture (WOSA) and workgroup computing frameworks.
SQL Server for Windows NT can function as a powerful database platform for workgroup-enabled applications. Through programmable access to external dynamic-link libraries (DLLs), SQL Server is easily integrated with MAPI, Microsoft Mail, and other workgroup technologies.
SQL Server can dynamically load and execute code in external Windows NT DLLs in a manner identical to a stored procedure. This capability provides unparalleled power to extend SQL Server in a seamless manner. Actions external to SQL Server can be easily triggered and information returned. Return status codes and output parameters identical to their counterparts in regular stored procedures are also supported.
Several useful extended stored procedures are supplied with SQL Server. One is xp_cmdshell. As its name implies, this function allows any Windows NT command or process to be executed from within SQL Server. This provides considerable power. For example, you can use xp_cmdshell from within a trigger to send a broadcast on the network about data changes:
create trigger broadcast on inventory for update
as exec master..xp_cmdshell
"net send johndoe 'Inventory Updated'"
Other, more sophisticated uses are possible. For example, a trigger could test if inventory has fallen below a certain level, automatically execute a reorder transaction, and send a Microsoft Mail message to the purchasing manager via MAPI. The possibilities are limited only by the imagination and skill of the developer. Because the extended procedures are DLLs, there is no need to stop and restart the server to add the new capability.
SQL Server safeguards itself from fault in an extended stored procedure. Only the system administrator can add extended stored procedures to the system. Once an extended stored procedure is fully tested, the system administrator can then grant EXEC privilege to use it just like any other stored procedure. Should a poorly designed procedure escape this safeguard, an exception handler within SQL Server prevents the system from being stopped should a protection violation occur. Any active transactions are gracefully rolled back just as they would be if the protection violation had occurred in the SQL Server code.
SQL Server includes three new system stored procedures to add, drop, or list extended stored procedures. They are:
sp_addextendedproc '<function_name>','<dll_name>'
sp_dropextendedproc '<function_name>'
sp_helpextendedproc ['<function_name>']
Extended stored procedures are developed with the Microsoft Open Data Services library, now available as part of the SQL Server Programmer's Toolkit.
SQL Server for Windows NT is capable of supporting clients communicating on multiple network protocols simultaneously without the need for add-on products like SQL Bridge. Server-based Net-Libraries, implemented as DLLs, handle connections concurrently over multiple transports. Several Net-Libraries can be active at once, allowing SQL Server to "listen" on multiple connection types (such as sockets or named pipes) over multiple transports (such as NetBEUI, TCP/IP, or IPX/SPX).
In its initial release, SQL Server supports named pipes connections over either NetBEUI or TCP/IP transports for clients running Windows, Windows NT, MS-DOS®, and OS/2 operating systems. Named pipes connections are also supported over IPX/SPX for Windows NT clients using NWLink. In NetWare environments, MS-DOS, Windows and OS/2 clients are supported over native IPX/SPX protocols and require no additional network software other than the Novell redirector. SQL Server also simultaneously supports the TCP/IP sockets API for communication with Macintosh®, UNIX, or VMS clients running SYBASE Open Client™ software. As Banyan releases network support for Windows NT, SQL Server will also support native VINES/IP connections.
SQL Server for Windows NT is an open platform, designed to integrate well with all major computing environments.
SQL Server for Windows NT is available for a broad base of hardware platforms. The Intel-based product is binary-compatible with 80386, 80486, and Pentium™-based computers, both single and multiprocessor.
What's more, as of this writing a version for MIPS-based computers is in beta test. SQL Server is also being ported to the DEC Alpha AXP platform and will soon be available for testing. As Windows NT moves to other popular hardware platforms you should expect SQL Server to move also.
SQL Server for Windows NT runs with equal levels of performance on either Windows NT or Windows NT Advanced Server. Windows NT Advanced Server provides additional fault tolerance, multi-server security features, and support for greater than 2-processor hardware systems. Remote Access Service, built into Windows NT Advanced Server (a limited single-port version also comes with Windows NT), allows dial-in to SQL Server over standard phone lines.
SQL Server for Windows NT supports industry-standard APIs at both the client and server to ensure an open, extensible database platform. SQL Server includes drivers to support ODBC (open database connectivity) applications, extending access to a wide variety of client software. In addition, hundreds of commercial tools and applications today support SQL Server's native DB-Library interface, ensuring broad choice for customers. SQL Server also supports applications developed with the SYBASE Open Client interface.
SQL Server is one of the few DBMS platforms to offer an open API for developing server-based gateway and connectivity applications that work in conjunction with SQL Server. Microsoft Open Data Services (ODS) is an event-driven API that provides a programmable gateway platform for server applications that can access any data source. It uses the same multithreaded architecture as SQL Server for Windows NT. Customers and systems integrators can use ODS to develop custom database gateways, data-driven event alerters, external program triggers, request auditing, and more.
ODS-based applications can function as stand-alone gateways or data-access servers supporting connections from the same client platforms as SQL Server. They can also integrate with SQL Server directly through remote stored procedure calls. For the Windows NT platform, customers also use Open Data Services for developing extended stored procedure DLLs.
Microsoft licenses Open Data Services to many third parties who provide SQL Server–compatible gateways to popular host computing platforms. Microsoft maintains cooperative development and support relationships with these vendors to ensure broad compatibility and tight integration with the SQL Server platform.
Gateways based on Microsoft Open Data Services are available for a wide variety of host platforms including IBM® DB2®, IBM SQL/DS™, CICS™, Pick, IBM AS/400®, DEC RdB™, and Teradata®.
Microsoft SQL Bridge provides transparent routing of SQL Server requests across different networks, transports, and operating system platforms. SQL Bridge can function as a gateway to Sybase platforms when the customer prefers not to install host-based network protocols on each client. For example, SQL Bridge allows a client application on a NetWare LAN to access a Sybase server on UNIX over standard IPX/SPX protocols—SQL Bridge automatically routes the request over TCP/IP. SQL Server for Windows NT also supports native TCP/IP sockets connections at the server, further increasing interoperability with Sybase environments. SQL Bridge will be available for the Windows NT platform late in 1993.
SQL Server for Windows NT is completely compatible and interoperable with SQL Server for OS/2. Migrating from OS/2 is easy because the format and structure of the database files is unchanged. The Setup program handles the upgrade automatically and it is possible to go back and forth with SQL Server version 4.2 for OS/2 during an evaluation period without recreating or reloading databases.
Existing client applications for MS-DOS, Windows, and OS/2 work unmodified with SQL Server for Windows NT. Not even a recompile or a relink is required. Windows NT-based client applications can also work with Microsoft OS/2 servers or with SYBASE SQL Server for UNIX and VMS platforms.
SQL Server for Windows NT operates in all major network environments over native protocols. SQL Server integrates directly with Novell® NetWare, Microsoft LAN Manager, Banyan VINES (in the future), IBM LAN Server, and Microsoft Windows NT–based networks. It can also interoperate with SYBASE SQL Server clients and servers over TCP/IP-based networks. SQL Server supports native protocols such as IPX/SPX and VINES/IP without requiring special network software to be installed on each client.
SQL Server supports direct call-level and embedded SQL application development in popular programming languages, including C, C++, Visual Basic®, and COBOL.
This toolkit contains development libraries and tools for writing client applications using the native DB-Library interface, and for writing server applications and extended stored procedures using the Open Data Services interface. Client applications can be written in C and C++ for MS-DOS, Microsoft Windows, Windows NT, and OS/2 with popular compilers from Microsoft and Borland. Applications can be developed with the Visual Basic Programming System for both MS-DOS and Windows. The Programmer's Toolkit also includes Microsoft Open Data Services development libraries for Windows NT.
The Embedded SQL Toolkit contains precompiler support and development libraries for writing traditional SQL applications in both C and COBOL.
—————
0893 Part No. 098-53024
© 1993 Microsoft Corporation. all rights reserved.
This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Companies, names and/or data used in screens and output are fictitious.
Microsoft, MS-DOS, Visual Basic, and Win32 are registered trademarks and Windows and Windows NT are trademarks of Microsoft Corporation. Macintosh is a registered trademark of Apple Computer, Inc. AST is a registered trademark of AST Research, Inc. Banyan and VINES are registered trademarks of Banyan Systems, Inc. COMPAQ and COMPAQ SYSTEMPRO are registered trademarks of Compaq Computer Corporation. DEC and VMS are registered trademarks of Digital Equipment Corporation. Intel is a registered trademark and Pentium is a trademark of Intel Corporation. AS/400, DB2, IBM, and OS/2 are registered trademarks and CICS and SQL/DS are trademarks of International Business Machines Corporation. MIPS is a registered trademark of MIPS Computer Systems, Inc. NCR is a registered trademark of NCR Corporation. NetWare and Novell are registered trademarks of Novell, Inc. SCO is a registered trademark of the Santa Cruz Operation, Inc. Teradata is a registered trademark of Teradata Corporation. UNIX is a registered trademark of UNIX System Laboratories. DB-Library is a trademark of Sybase.