PLEASE NOTE: The authors based their SQL Server 2000 articles for this issue on pre-Beta 2 versions, so you might notice some differences between Beta 2 and the behavior or interfaces we describe in this issue. In particular, please note that the indexed views feature will be available only in SQL Server 2000 Enterprise Edition. However, you can install Enterprise Edition on NT 4 Server and Windows 2000 (Win2K) Server. You don't need NT 4.0 Enterprise or Win2K Advanced Server.
Imagine that you have an opportunity to build the dream house you plan to raise your family in for the next 20 years. Money isn't a concern, but you want to move in as soon as possible. In this case, you'll make trade-offs between having everything perfect before you move in and having your new house as soon as possible. You'll spend time carefully designing every foundational piece of your home, knowing that you can always finish the basement or add a big deck after you move in.
Let's compare this process to building SQL Server. Several years ago, Microsoft gathered some of the best database engineers worldwide (inside and outside of Microsoft) and asked them to build their dream database. Think of SQL Server 7.0 as the foundational result of that effort. SQL Server 7.0 was a major rewrite of the code base, but DBAs and developers couldn't see or touch many of the improvements. SQL Server 7.0 had a slew of important new and visible features (such as OLAP Services), but much of the hard-core reengineering was at the page and storage level. I first saw SQL Server 2000 at Microsoft's SQL Server Most Valuable Professional (MVP) conference last October. Initial briefings covered the internal architecture of the relational and storage engines, and I left with an important insight: SQL Server 2000 leverages the SQL Server 7.0 foundation and adds some plumbing and wiring. But in many ways, SQL Server 2000 is the finished basement, the huge deck, and the home movie studio you've always wanted.
So Many Features
It was impossible for me to choose the most important SQL Server 2000 improvements to write about in this article. This issue of SQL Server Magazine contains plenty of SQL Server 2000 information (see Kalen Delaney, "Introducing Indexed Views," page 32, and Paul Burke, "XML and SQL Server 2000," page 39), and we'll continue to cover SQL Server 2000 in upcoming issues.
Although SQL Server 2000 offers many improvements over earlier versions, the most important message about SQL Server 2000 is that it can now go toe-to-toe against any of the competing database platforms for applications of almost any imaginable size or complexity. Consider this unprecedented endorsement that Microsoft secured from SAP: "SAP is convinced that SQL Server 2000 on Windows 2000 would now meet the needs of every R/3 customer around the world. Moreover, the upcoming support of 16- and 32-processor machines as well as 64-bit platforms guarantees users the necessary growth path."
I've been a SQL Server person for 8 years, since Microsoft's version of SQL Server was licensed from Sybase and ran on OS/2. Today, SQL Server 2000 is the fastest database in the world, according to Transaction Processing Performance Council (TPC) TPC-C and TPC-H benchmarks at press time. (See the TPC's Web site at http:// www.tpc.org for current benchmarking scores.) Just as impressive, since early 1997, SQL Server's TPC-C scores have improved annually by 200 percent, whereas its TPC-C price-per-transaction costs have dropped 65 percent per year. Recent scores are based on commodity 8-way Intel servers, so the impending 16- and 32-node boxes from companies such as NEC and Unisys will provide plenty of headroom to continue those trends.
I've written about SQL Server 2000's improved pedigree in enterprise scalability, fault tolerance, and high availability in several issues of the weekly email newsletter SQL Server Magazine UPDATE (http://www.sqlmag.com/resources/email/update/main.cfm). Microsoft's dream database isn't finished, but the hot tub and indoor heated pool are ready, and this house is starting to feel like home.
Cool and Important New Features
Distributed partitioned views. Microsoft achieved the ground-breaking TPC-C benchmarks that it's been publishing since mid-February by combining multiple, high-end SMP machines into a federated database that scales out through the use of updateable, distributed partitioned views. Microsoft first introduced partitioned views in SQL Server 7.0, but SQL Server 2000 takes this feature a step further by letting you distribute these views across multiple servers and letting you update the base tables directly through the views. This capability gives you a taste of what will come when SQL Server supports true shared-nothing clusters, which Microsoft has promised in the release after SQL Server 2000, code-named Yukon.
Today, you can establish data location independence through distributed views, but the system architects still need to spend considerable time planning data layout and distribution to ensure maximum scalability. Shared-nothing clusters will take this process a step further by making data distribution administration transparent and by making data location transparent from a programmer's perspective.
Multiple instance support. With SQL Server 2000, you can run multiple instances of SQL Server on the same machine. Earlier versions could host multiple databases on the same server, but in practice, only one server instance could be active at a time. Multiple instance support lets you do basic tasks, such as maintain two independent SQL Server environments for development and testing teams on the same physical machine. More important, this feature makes it easier for Microsoft to support SQL Server in a fully clustered environment. Earlier problems with SQL Server clustering stemmed from the fact that Microsoft had to trick SQL Server into running two instances on the same node of a cluster. The Registry-hacking gymnastics required to perform this trick are no longer necessary. In SQL Server 2000, cluster functionality is more stable and easier to manage than in earlier versions.
Log shipping. Log shipping lets you constantly transmit transaction logs from one database to another. Continually backing up the transaction logs from a source database then copying and restoring them to a destination database keeps the destination database in sync with the source database. The log shipping feature lets you have a warm standby server and provides a way to offload query processing from the main computer (the source server) to read-only destination servers. Log shipping works only with registered servers that are running SQL Server 2000. Log shipping isn't rocket science, and people have been devising their own versions of it for years. For many months, Microsoft has included log shipping as a product with the BackOffice 4.5 Resource Kit (BORK—see SQL Seven, "BackOffice Resource Kit Tools," March 2000). But integration of the log shipping feature into SQL Server's Enterprise Manager will compel DBAs to begin using the feature in a significant way. Now that SQL Server has the world's fastest TPC-C and TPC-H numbers (as of February), other database vendors need to shift their battle cry to "We're more reliable." Log shipping, combined with multiple-instanced, enhanced SQL Server clusters, gives Microsoft serious ammunition in the fault-tolerance and high-availability wars.
Kerberos and delegated security. The integration of Kerberos and delegated security brings us much closer to the golden age of true, one-login integration between Windows 2000 (Win2K) and SQL Server. Security enhancements let you delegate security credentials to downstream servers so that users need to be authenticated at the SQL Server level only once.
XML support. SQL Server 2000 is a powerful XML server. XML functionality is important because it positions SQL Server as a strong competitor in the emerging business-to-business integration market. (For more information about XML functionality, see "XML and SQL Server 2000." Also, you can subscribe to the biweekly email newsletter, SQL Server Magazine XML UPDATE, at http://www.win2000mag.com/ourproducts/email/index.cfm#.)
Cascading Declarative Referential Integrity (DRI). Finally! For years, Access developers have been amazed that SQL Server didn't support cascading DRI, and SQL Server developers have had to hang their heads in shame when chatting with experts in every other major relational database management system (RDBMS). No longer! SQL Server 2000 also supports cascading updates.
User-defined functions. UDFs have been on the SQL Server DBA wish list for years. At one point, I thought that SQL Server 2000 might include support for language-independent UDFs, such as a UDF written in VBScript. Unfortunately, the SQL Server 2000 implementation is limited to T-SQL-based UDFs. But UDFs are a huge improvement, letting you write various T-SQL batches in an elegant manner.
The Little Things
For years, Microsoft has shipped administration tools with generally acknowledged best-of-breed GUIs, especially compared with its UNIX-based RDBMS competitors, and SQL Server 2000 continues this tradition. You'll find many big features in the tools, but you might enjoy even more the fit-and-finish enhancements, such as the Object Browser. The Object Browser provides scaled-back Enterprise Manager functionality directly from Query Analyzer. You can now graphically navigate database objects and perform functions against them. A simple option, Script Object to New Window as Insert, lets you right-click a table name and automatically generate a template insert statement that names all the columns and provides place holders in the VALUES clause. It didn't take a PhD in computer science to think up this feature. But anyone who's wasted five minutes cutting and pasting column names to build a long insert statement will appreciate the power of this feature. You'll also like the dozens of other niceties bundled into Enterprise Manager and the administrative GUI tools.
With the impending release of SQL Server 2000, there's never been a better time to be a SQL Server professional, and future releases look even better. Look for ongoing coverage in SQL Server Magazine of how you can optimize SQL Server 2000's functionality in your organization.
