Inside Microsoft SQL Server 7.0 (Mps)
SQL Server is available in three editions, each on its own CD: SBS (Small Business Server) edition, Standard edition, and Enterprise edition. Table 4-1, taken from SQL Server Books Online, summarizes the main differences between the editions.
No matter which edition you have, you can install SQL Server on either an Intel-compatible processor or a DEC Alpha_compatible processor. Any edition lets you install the full server and tools suite or just the tools. You can also install the desktop version of SQL Server from any edition if you have chosen to use per-seat licensing, which we'll discuss later in this chapter. You can install SQL Server Desktop on Windows 95, Windows 98, Windows NT Workstation, Windows NT, or Windows NT Enterprise.
Table 4-1. Features of the three SQL Server editions.
| Feature | SBS | Standard | Enterprise |
|---|---|---|---|
| Runs on Microsoft BackOffice Small Business Server | Yes | Yes | No |
| Runs on Microsoft Windows NT Server | No | Yes | No |
| Runs on Windows NT Server, Enterprise edition | No | Yes | Yes |
| Extended memory support | No | No | Yes |
| SQL Server failover support | No | No | Yes |
| Supports Microsoft Search Service, full-text catalogs, and full-text indexes | Yes | Yes | Yes |
| Supports Microsoft SQL Server OLAP Services | No | Yes (no user -defined cube partitions) | Yes (includes user-defined cube partitions) |
| Maximum database size | 10 GB | Unlimited | Unlimited |
| Number of SMP CPUs | 4 | 4 | 32 |
The desktop version does not come with all the SQL Server features, and if you use it on a Windows 95 or Windows 98 machine, you face other restrictions because of the limited capabilities of the operating system.
SQL Server Desktop does not support:
- Parallel queries
- Fiber-mode scheduling
- Failover clusters
- Extended memory addressing
- Defining publications in a transaction-based replication configuration
In addition, if SQL Server Desktop is installed on a Windows 95 or Windows 98 machine, it also does not support:
- The Named Pipes and Banyan VINES server-side Net-Libraries. (The Windows 95 or Windows 98 computer cannot accept incoming connections using these protocols, but the client tools on such a computer can connect to other SQL Servers on Windows NT using these protocols.)
- AppleTalk Net-Libraries.
- Windows NT Authentication for incoming server connections.
- The server side of using encryption with the Multiprotocol Net- Library.
- Asynchronous I/O or scatter-gather I/O. (This includes true read ahead scans . SQL Server will issue what it thinks are read ahead requests , but these need to be translated to synchronous calls, because Windows 95 and Windows 98 do not support asynchronous I/O. You will get the benefit of the larger I/Os that read ahead provides, but they will be synchronous.)
- Any component that corresponds to Windows NT Services. (The SQL Server database engine and SQL Server Agent run as executable programs on Windows 95 and Windows 98.)
- Event logs. (SQL Server uses a SQL Server Profiler_based mechanism to launch alerts on Windows 95 and Windows 98.)
- SQL Server Performance Monitor.
- The SQL Server Version Upgrade utility.
We'll discuss most of these capabilities and tools in more detail later in the book.
NOTE
Contrary to the SQL Server online documentation, hash and merge joins are possible with SQL Server Desktop. However, because these join techniques are more memory intensive and the desktop installation is likely to have less memory resources available, the SQL Server query optimizer is much less likely to choose these join techniques. (We'll examine these techniques in Chapter 14 when we look at the SQL Server query optimizer.)
User Connections
SQL Server Desktop and SQL Server SBS impose no hard and fast limits on the number of user connections, unlike earlier versions of SQL Server. SQL Server 7 uses an internal throttling mechanism that is optimized for a workload that corresponds to a certain number of users. SQL Server Desktop is optimized so that the best throughput occurs with about five users. As the number of user connections increases , the total throughput actually starts to decrease, not just for the individual connections but for the entire SQL Server. If you want more throughput, use the Standard, SBS, or Enterprise edition of SQL Server. An SBS installation of SQL Server is also throttled, so its maximum throughput occurs with about 100 user connections.
Embedded SQL Server
If you're looking for a version of SQL Server to embed in your own applications and redistribute, refer to the documentation that comes with Microsoft Access 2000 or Microsoft Office 2000. The installation of either of these products gives you the option of installing the Microsoft Data Engine (MSDE). MSDE is the server component of SQL Server and includes basically the relational engine and the storage engine. None of the usual SQL Server tools are installed; you are expected to use the visual tools that come with Microsoft Visual Studio for creating your objects. Office 2000 and Access 2000 install a tool that lets you do a minimum amount of administration. In addition, you can use all the usual tools that come with a full SQL Server installation (Desktop, SBS, Standard, or Enterprise), such as the SQL Server Enterprise Manager and the SQL Server Query Analyzer, to access the data stored in MSDE. We won't discuss MSDE further in this book; see the documentation for additional Office 2000 or Access 2000 details.