Justifying NT

NT Advanced Server and SQL Server for NT let you deploy file and database servers over a range of RISC and CISC platforms

Jon Udell

If Windows NT could run everywhere, from laptops and desktops to applications servers and file servers, Chicago would still be just a city in Illinois. No one at Microsoft pretends that cramming Win32 into a 4-MB procrustean bed--the Chicago design goal--yields optimal results. Sadly, most PCs today ship with just 4 MB of RAM--too little, really, for Windows 3.1. Even when new systems default to NT's required 16 MB (perhaps by 1995), most of the tens of millions of previously installed desktop systems will remain unable to run NT. That inescapable reality will shape NT's role in the enterprise for the next few years.

Some companies will ante up and deploy NT to the desktop anyway, just as so me have done with OS/2 and desktop Unix. Why? Everyone's mission is critical; downtime is unacceptable; multitasking matters; and real operating systems pay back the investment required to run them in ways that are hard to quantify on the comptroller's spreadsheet. When Win32 versions of BYTE's line-of-business applications arrive, I'll be the first to argue that we should run them on NT, not Chicago. Then, when I lose that argument to the finance people, I'll make a more modest proposal that defines which systems should run NT and why.

Today, you have two important options. First, Windows NT Advanced Server 3.1, the latest in a long line of SMB-/NetBIOS-based LAN operating systems from Microsoft, offers a capable, attractively priced alternative to NetWare. Second, Windows NT 3.1, without the full LAN operating-system capability of Advanced Server, is a fine platform for server applications such as Oracle and SQL Server. In these scenarios, NT delivers robustness, connectivity, ease of installation an d use, RISC and multiprocessing support, threaded multitasking, manageability, and a 32-bit Windows-compatible API. On paper, none of the competitors--OS/2, Unix, and NetWare--can match NT feature for feature. In reality, each is supported by the type of sophisticated infrastructure that comes from years of deployment in various niches.

In evaluating NT for production use, I wanted to answer two questions. First, is it the right database-server platform? Second, is it the right LAN operating system? The short answers are yes and no, respectively. The long answers are, naturally, more complex. Before diving in, I'll explain how the evaluation worked.

NSTL supplied two kinds of benchmarks--SQL database tests and file-server tests. The file-server tests, conducted at NSTL, pit NT Advanced Server against NetWare and LAN Server on a uniprocessor Intel machine. The SQL tests, performed jointly at NSTL and BYTE, compare the OS/2, NLM (NetWare loadable module), and NT versions of SQL Server on uniproces sor Intel hardware. They also assess SQL Server for NT on uniprocessor Mips and Alpha machines and on a multiprocessor Intel box (see the text boxes "How We Tested SQL Server on NetWare, OS/2, and NT" on page 150; "SQL Server for NT on CISC and RISC" on page 158; and "LAN Operating-System Testing" on page 164 for discussions of the benchmark results).

In addition to quantitative testing, I worked extensively with NT Advanced Server and SQL Server in a production environment--BYTE's own editorial LAN. Our mix of DOS, Windows, Mac, and NetWare systems typifies what you'll find in many corporations today. My work in converting a homegrown FoxPro contact manager into client/server form and fielding it on our LAN convinced me that NT is ready for prime time as an applications server and that SQL Server leverages NT's strengths. When I evaluated Advanced Server as an alternative to NetWare, considering its pros and cons for DOS, Windows, and Mac users and for network administrators, the picture that emerged was cloudier.

NT as Database Server

For the qualitative evaluation, I ran SQL Server 4.2 for NT on four machines. The version that ran on RISC hardware--an SGI/Mips Magnum and a DEC AXP 150--was beta code, scheduled to ship in final form in late January, just after completion of this review. For that reason, take the benchmark results for the Mips and Alpha systems with a grain of salt. The version that ran on the Intel platforms--a Compaq Proliant and an Everex Step 486/50--was the shipping version.

The reliable sameness of NT and SQL Server across this range of processors and system architectures represents a stunning accomplishment. That portability, along with SQL Server's 32-bit addressing and NT's 64-bit file pointers and disk-spanning capability, means you can bring nearly unlimited resources to bear on data management.

Matching Up

NT and SQL Server are a natural match in many ways. Under 16-bit OS/2, SQL Server can't make optimal use of threads, according to Microsoft. The limit of 53 threads per process made it impractical to assign one thread to each user connection. Instead, a single OS/2 thread services all connections, and SQL Server schedules simulated threads within the context of that worker thread. Under NT, however, SQL Server uses a pool of worker threads. This approach leverages native NT scheduling services and ensures even distribution of work across multiple processors in SMP (symmetric multiprocessing) systems. OS/2 2.x's per-process (and systemwide) limit of 4096 threads will support the same approach, but neither the 32-bit OS/2 version of SQL Server nor the SMP version of OS/2 2.x is yet available.

NT simplifies the management of SQL Server in many ways. With respect to SCSI peripherals, for example, NT users today enjoy many of the benefits that Chicago users with Plug and Play hardware will enjoy tomorrow (and that Mac users have taken for granted for years). NT's boot-time procedure for enumerating devices on a SCSI bus and recording information ab out them in the system registry was in fact the model for Chicago's similar mechanism. The payoff, for me, came when I ran the NSTL tests, which require that the test database and its transaction log reside on separate disks. Adding a second disk to the Alpha machine and then moving it to the Mips machine was a trivial exercise. Because NT's Disk Administrator can shuffle drive letters around--a terrific convenience--I was able to make the disk show up as drive F on both machines, and thereby avoid changing drive letters encoded in SQL scripts and batch files.

The ability of NT to stripe data across physical disks (and of Advanced Server to stripe with parity) means that you no longer have to juggle SQL Server disk devices and segments to spread out the I/O load. Managing SQL Server storage at the segment level is complex and scary. Creating stripe sets with Disk Administrator is child's play.

The instrumentation built in to NT--hundreds of counters that continuously measure the vital signs of processes, disks, memory, cache, network transports, and other system objects--reports a wealth of data that you can use to analyze the performance of an NT system using the NT Performance Monitor utility. The mechanism is open to applications, and SQL Server uses it to report its own statistics to Performance Monitor in the same way that system objects do. This integration of system and application performance data can yield crucial insights.

You can use Performance Monitor to know, not merely guess, what SQL Server's optimal memory allocation should be for a given NT configuration. You can even use Performance Monitor to set alerts (i.e., thresholds on system or application counters) and specify commands that run in response to the triggering of those alerts. NT's sophisticated and unified approach to monitoring, though not widely recognized, contributes mightily to its mission-critical capability.

Getting Connected

NT comes out of the box ready to run three network protocols: NetBEUI, IPX /SPX, and TCP/IP. (Advanced Server adds a fourth--AppleTalk.) SQL Server puts that flexibility to good use. The ability to communicate using multiple IPC (interprocess communications) mechanisms over multiple transports was formerly available in the form of SQL Server "integration kits" for NetWare and Vines. Those capabilities are now bundled with NT.

Of the most interest to me was the ability to support some DOS, Windows, NT, or OS/2 clients using named pipes over NetBEUI and other protocols--simultaneously--using SPX sockets. The target audience for my test application included Windows for Workgroups nodes running NetBEUI and plain Windows 3.1 nodes running IPX/SPX. SQL Server made deployment a snap. The client software's installer let me pick the Net-Library I needed in each case. It was not always this easy to field a SQL Server application in a predominantly NetWare environment like ours.

My only gripe is that Microsoft doesn't bundle the Mac client components. Mac (and Unix and VMS) clien ts can talk to SQL Server using TCP/IP sockets, but to make this happen you need versions of DB-Library (the SQL Server API) and Net-Library available only from Sybase.

SQL Server also now comes out of the box ready to support ODBC (Open Database Connectivity) clients. This is irrelevant to the SQL Server version of the NSTL benchmark, which is written to DB-Library and makes extensive use of stored procedures, but it isn't irrelevant to me. Unlike the benchmarks, the application I built is purely generic; the tool I used to construct it, Coromandel Industries' Integra Visual Database Builder, talks only to ODBC. In choosing that tool, I traded advanced back-end features for flexibility; thus, I can point the application not only at other servers (e.g., Oracle and Informix) but also at dBase or FoxPro files--an option that creates interesting possibilities. Any client application for NT SQL Server can run locally on LAN-attached nodes, or remotely on a node that dials in to NT's Remote Access Service. With ODBC, there's also the possibility of stand-alone use. If you export SQL Server data into, say, dBase or FoxPro files, an unconnected client can simply switch ODBC drivers and use the same application to access the snapshot data for read-only purposes.

Managing the Data

To make SQL Server's high-performance engine hum, administrators traditionally had to climb under the hood and monkey around using rather primitive tools. That began to change when version 4.2 provided SQL Administrator, a graphical tool for OS/2 and Windows that you use to create, mirror, and modify storage devices; assign databases and transaction logs to storage devices; manage users and groups; configure SQL Server options; and issue queries. The NT version of SQL Administrator adds a few enhancements. For example, the query tool can now graph query statistics (e.g., scans and logical and physical reads) and display a graphical summary of the query plan.

The big news, though, is the new SQL Object Manager, a real Swis s Army knife for managing data. (Incidentally, it is also available with the 4.2b upgrade of OS/2 SQL Server.) You use Object Manager to create and manage tables, indexes, keys, views, triggers, and stored procedures and to assign permissions to these objects.

One powerful feature that I put to use in my application is the ability to generate triggers: special stored procedures that fire on update, insert, and delete events. This let me enforce referential integrity between tables in a nearly automatic way. Object Manager makes setting up primary key/foreign key relationships a point-and-click affair, but it relies on triggers to cause changes to ripple from one table to another.

My simple contact manager, for example, uses company and contact tables related by a company-name key. When I asked Object Manager to create a new update trigger for the company table, it found the related contacts table and wrote the half-dozen lines of Transact-SQL necessary to synchronize the two tables. As a result, when I rename a company, all the related contact records snap instantly into place in the master-detail view that my application presents. If you've solved this kind of problem in a navigational database like FoxPro, you'll appreciate the economy with which SQL Server's triggers express the same logic. And if you've written triggers yourself, you'll love the fact that Object Manager automates the task.

Is the lack of true declarative referential integrity a serious flaw? In that religious debate, I side with the Microsoft/Sybase camp. Users tell me they want changes logged and business rules enforced when transactions occur. With triggers, you can write the code to do these things in a server-based, application-independent way.

Object Manager also tracks object dependencies, so you can select a table and list the triggers and stored procedures that refer to it or, conversely, select a stored procedure and list the tables it refers to. In addition, Object Manager can write out the SQL scripts th at define all the objects in a database. It even wraps a graphical shell around the venerable BCP (bulk copy program)--which imports and exports BCP-formatted and comma- or tab-delimited ASCII data. An early beta version dangled the promise of support for other formats, such as dBase, FoxPro, and Access, but unfortunately, these didn't make the cut.

Hands On

With SQL Server installed on NT, it took just a couple of days to build my test application, dividing my time about equally between server-side and client-side chores. I've been using the application ever since, because it's faster and more convenient than its FoxPro predecessor. SQL Server's newfound ease of use, and Coromandel's first-class application builder, made the development task vastly more approachable than it was even just a year ago. The barriers to client/server development are crumbling; it's fast becoming a game the average corporate programmer can play and win. I'm excited by the new vistas NT SQL Server opens up, but there are still a few items on my wish list.

For starters, backup and recovery procedures remain rather primitive and do not integrate with the (also rather primitive) backup facility of NT. NT needs something like Novell's Storage Management Services architecture.

While I'm wishing, how about an agent that can digest SQL Server's diagnostic data and form useful recommendations? Interpreting Performance Monitor screens usefully requires a lot of expertise. The same holds true for the output of ShowPlan, a utility that lets you view the deliberations of SQL Server's cost-based query optimizer. Moving some of that expertise into software would be a tremendous boon.

SQL Server and Advanced Server work together on security matters, but not as well as I'd like. The rules governing user names differ between the two systems, and smoothing out those differences proved tricky. Synchronizing the database and network directories requires manual intervention; new users added to an NT group don't automatically show up in the corresponding SQL Server group, and there's no link to the NetWare bindery.

There's also a quirk in the Sybase/Microsoft client software that needs fixing: Only one database cursor can be active per client connection. The problem showed up when I implemented a two-table, master-detail view. When a query against the master table returned hundreds of records, the dependent query against the detail table was slow to respond. According to Coromandel, that's because the client software can't keep both cursors simultaneously active. By constraining the master result set--which was the right thing to do anyway for my application--I solved the performance problem. But the underlying limitation shouldn't exist.

Despite these details, NT SQL Server gets an emphatic thumbs-up from me. Client/server computing, RISC, and multiprocessor systems have been dry seminar topics for too long. With a platform like NT and an application like SQL Server, they come alive as practical technologies that yo u can apply today in the enterprise.

NT as File Server

With the advent of Windows NT 3.1 and Windows for Workgroups 3.11, Microsoft has standardized on a rich workgroup foundation. Peers in a Windows workgroup can share files, printers, and clipboards over NetBEUI, IPX/SPX, or TCP/IP and exchange mail using simple MAPI. (For more on the use of routable protocols with NT, see "Wide-Area Windows Networking," January BYTE.) NT and Windows nodes are equal partners in workgroups and communicate effortlessly across the 16-bit/32-bit divide. Other network services available to 16- and 32-bit applications include named pipes, NetDDE, Windows sockets, mail slots, and the WNet APIs used to browse for, connect to, and share resources. This common substrate contains much of the capability that LAN Manager formerly provided.

As a result, LAN Manager's heir, Advanced Server, focuses exclusively on the issues that separate enterprise networking from workgroup networking: central administration, fault tol erance, heterogeneous client support, and remote access. To that end, Advanced Server inherits and extends LAN Manager's domain-based security model, offers disk mirroring/duplexing (RAID 1) and striping with parity (RAID 5), delivers Macintosh file and print services, and bumps NT's limit of one remote user to 64.

All this costs a lot less than NetWare. A 100-user NetWare setup lists for $6995 (for version 3.12) or $8795 (for version 4.01)--and that doesn't include Mac support. And while NetWare's licensing cost climbs as you add more users, Advanced Server's stays flat at $2995 ($1495 until June 1). Of course, there are a host of other considerations, not the least being that Advanced Server needs a bigger, faster machine to run acceptably. But when you toss in features not included (e.g., remote access and RAID 5) or not possible (e.g., RISC and multiprocessing support and local GUI application capability) with NetWare, Microsoft's latest LAN operating-system offering merits a close look.

Out of the Box

I ran Advanced Server on two machines: the SGI/Mips Magnum and an IBM PS/2 Model 90 486 XP. Each machine was a primary domain controller, accessible to Windows, OS/2, NT, and Mac clients on BYTE's Ethernet LAN. While it's faster and easier to install from CD-ROM, the Advanced Server package also includes a stack of 23 floppy disks if--as was the case with my PS/2 machine--your server lacks a CD-ROM drive.

With Advanced Server, as with NetWare, it's a breeze to set up disk partitions and configure network protocols. When you want to reconfigure these things, however, NT sometimes lacks NetWare's flexibility. For example, exploring the use of IPX/SPX and TCP/IP as alternate substrates for the basic Windows networking features (file-, printer-, and clipboard-sharing), NT forced me to reboot every time I added or even just tweaked a protocol. With NetWare, I can load or unload TCP/IP and AppleTalk stacks without downing the server or disrupting logged-in IPX/SPX clients, and I've been grate ful to be able to do that. While NT's transport drivers are, in principle, unloadable, the mechanism that binds transports is rooted deep in NT's boot process. Continuous availability is a vital issue for me. With NetWare, I can change an IP address or an AppleTalk zone name on the fly; Advanced Server needs to be equally adaptable.

The Knowledge Gap

My protocol experiments also brought to light another glitch that underscores the immaturity of NT and Advanced Server. Installation and binding of drivers, protocols, and services are highly automated in NT. Graphical utilities kick off routines that create and destroy keys in the system registry, where all configuration information lives in binary form.

This is a great feature when the installation tools work properly, and in my experience, they almost always do. But in one case, I got stuck. NT has the notion of service dependencies--for example, NWNBLink, Microsoft's NetWare-compatible NetBIOS, depends on NWLink, Microsoft's IPX/SPX transport . When I installed NWLink, NWNBLink came along for the ride. I could have removed both by uninstalling NWLink, but instead, I mistakenly uninstalled NWNBLink and, per instructions, rebooted. That left things in a state of limbo. I couldn't remove NWLink, nor could I add NWNBLink.

To compound the trouble, I next removed the network card driver, planning to reinstall all the networking pieces from scratch. But the NWLink transport remained in limbo. In retrospect, I realized that I should have reverted to the "Last Known Good" configuration at the first sign of trouble; that's how you recover from catastrophic configuration errors. But my error wasn't catastrophic. And because the undo stack is just one level deep, when I took out the network card driver and rebooted, NT marked the prior--and problematic--configuration as "Last Known Good." I tried booting from the NT panic disk and restoring the configuration stored there, but for some reason, that didn't expunge the rogue NWLink. Finally, I took a stab at deleting its registry keys, but NWLink, Lazarus-like, kept returning to haunt me. In the end, I fired up the installation CD and took a coffee break.

Don't get me wrong. On the whole, I find NT a little easier to configure than NetWare and a lot easier than OS/2 or Unix. But it has its own unique approach. Experts who fully understand that approach, books that document it, and third-party tools that complement it are, today, in scarce supply. (The invaluable three-volume Windows NT Resource Kit is the outstanding exception to this rule.) Some would-be implementors will cite this knowledge gap as reason to adopt a wait-and-see stance with respect to Advanced Server. Others will view it as a career opportunity.

Working with Advanced Server

Advanced Server does what every LAN operating system must do--share files and printers--with minimal fuss and maximal point-and-click ease of use. You share directories using File Manager; the procedure will be familiar to users of NT or Windows for Workg roups. You can share FAT (file allocation table), HPFS (High Performance File System), or CDFS (CD-ROM file system) volumes, but only with directory-level security. (Unlike NetWare, Advanced Server makes CD-ROM sharing a trivial exercise.) For full file-level security, you need to use NT's journalizing file system, NTFS.

Advanced Server's Mac file services use NTFS for two reasons: file-level security, and because the ability of an NTFS file to contain multiple-named data streams of unlimited size maps neatly to the data-fork and resource-fork components of a Mac file. Because NT's back up utility is NTFS-aware, you can backup and restore Mac files without damage to long filenames or creator/type resources (I checked; it works.) Unlike NetWare's Mac name space, which you install once to make an entire volume visible to Mac clients, Advanced Server's Mac volume maps to an individual NTFS directory. The advantage of this scheme is that you don't litter your whole volume with Mac directory entries when, a s is true on our network, Mac and PC users tend to exchange files using a few directories designated as drop boxes. The disadvantage is that you have to maintain PC shares and Mac shares separately. On our NetWare server, a single directory called Temp is available to everyone. With Advanced Server, I had to create Temp twice and deal with two sets of permissions.

Advanced Server lets you set up the systemwide auditing policy so that it records the success (or failure) of file access requests in its security event log. It also lets you fine-tune your auditing policies using File Manager. For selected files or directories, you can adjust the list of users and groups whose actions Advanced Server will audit and the list of actions that it will audit. Advanced Server dumps the audit trail into the security event log, which you can read using the same Event Viewer that you use to explore the system and application event logs.

The Win32 APIs that write and read event logs invite third-party drivers a nd applications to cooperate with current and future system management tools. Like the standard monitoring techniques exposed through Performance Monitor, the standard logging techniques exposed through Event Viewer bode well for Advanced Server's long-term manageability.

Management: The Dark Side

It's important to keep the manageability of an Advanced Server network in perspective. Widespread adoption of performance monitoring and event logging can vastly improve software maintenance and support. Because both Performance Monitor and Event Viewer are enabled by RPCs (remote procedure calls), I can point them at any NT system on my network, not just my local NT machine. Put on an administrator's hat and imagine how effective you'd be if your users' applications routinely reported trouble--low memory, file not found, unable to print--into standard logs that you could visit remotely.

Unfortunately, this rosy scenario requires NT on both sides of the RPC pipe. Event logging is a part of the Win32 API. Sadly, both Win32s and Win32c, the variant that Microsoft hopes to bring to the masses with Chicago, don't provide it. Thus, event logging isn't going to show up en masse on the client side anytime soon--a real pity because the heaviest support costs pile up on the client side. So while NT and Advanced Server offer tantalizing glimpses of holistic network management, only NT clients can enjoy the full benefits.

Another administrative benefit enjoyed only by NT clients on Advanced Server networks is the RPC-enabled Print Manager. It obviates the need to install printer drivers on clients. A driver installed once on an Advanced Server machine is available, in client/server fashion, to all NT workstations. Advanced Server's user profiles, which enable desktop settings, file and printer connections, and log-in scripts to follow users from workstation to workstation, are also NT-specific. In these cases, Windows 3.x clients are again left out in the cold, although Chicago clients, which can use the Wi n32 RPC and registry APIs, should fare better.

Even in the NT realm, even just considering server management, there are some key restrictions. A NetWare administrator can dial up a server from anywhere using a DOS laptop and manage that server with RConsole. But while an Advanced Server administrator can dial in from either Windows 3.x or NT clients, that person won't often be able to tuck an NT machine into a briefcase.

If you're one of the lucky few who can tote NT, you're still not home free. While many administrative tasks can be performed in client/server mode, others--including the control of services and the installation and configuration of drivers--require local access to the server. Lacking an X Windows System-like solution, Microsoft will not have an answer to this problem until the debut of Hermes, the suite of NT-based network management technologies due later this year. One Hermes feature is a remote screen/keyboard/mouse capability.

A Matter of Trust

A domain unites a gr oup of servers into a single administrative unit. The idea is that you can log in and supply a password just once and then access any secure shared resource on any server. The reality falls a bit short because the MS-Mail address book doesn't yet integrate with Advanced Server's account database. Of course, Novell users have the same problem. Even in NetWare 4.0, the MHS address book isn't an integral part of the NDS (NetWare Directory Service).

In other respects, though, Advanced Server's domain-based security works as advertised and benefits greatly from the new ability to set up trust relationships between domains. If the administrator of Domain A agrees to trust Domain B (and B's administrator permits A to trust B), then B's users and groups can be granted explicit rights and permissions in A. This is actually easier to do than to explain. Basic trust relationships are a lot less confusing than global groups (which can contain users from just the current domain but can be referenced in foreign doma ins) and local groups (which can contain users from foreign domains but can be referenced just in the current domain).

Trusted domains can enable a bottom-up approach to the construction of large networks that's very different from the top-down approach required by NetWare 4.0. Advanced Server domains can evolve independently and then join in trust relationships if necessary, so you can delay enterprise-wide decisions about network structure. Novell's all-encompassing NDS forces you to design the enterprise-wide structure up front.

There's a key inhibitor to the organic growth of Advanced Server networks, however. I originally set up my two Advanced Servers as controllers of separate domains so I could explore trust relationships between the domains. My plan then was to convert one server to be a backup domain controller in the other's domain. No such luck. You can easily move an NT workstation from one domain to another, but to move an Advanced Server, you have to reinstall. With that discovery , multidomain networking suddenly looked a lot less flexible.

The bottom line? NetWare 3.11's in place, and it does the job. When it's time to upgrade our two 8-MB 386-/486-class servers, I'll likely recommend NetWare 4.0. For a population of NT workstations, Advanced Server makes a lot of sense. But for DOS, Windows, and Mac users, there's no compelling advantage.

---

SQL Server for NT

PROS
-- Employs multiple clients and protocols
-- Supports ODBC
-- Automates data administration with Object Manager


CONS
-- Poor backup capabilities
-- Ill-matched security with operating system


NT Advanced Server


PROS
-- Low per-user costs
-- Easy to install and configure
-- Works great with NT clients


CONS
-- Must reboot after changing protocols
-- Limited remote administration
-- Limited flexibility in changing domain configurations

---

The Facts

SQL Server for NT
Desktop, $995; 10 users, $2995;
64 users, $7995;
 unlimited users, $14,995 (Intel) or $19,995 (RISC)


NT Advanced Server
Unlimited users/all platforms, $2995


Microsoft Corp.
1 Microsoft Way
Redmond, WA 98052
(800) 426-8661
(206) 936-8661
fax: (206) 936-7329

---

Photograph: One of NT's strengths is its ability to run on RISC-based hardware such as the Alpha-based DEC AXP 150 and the R4400-based SGI/Mips Magnum.

---

Jon Udell is a BYTE senior technical editor at large. You can reach him on the Internet at judell@bix.com .