SQL Server Magazine UPDATE, March 6, 2003

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com and SQL Server Magazine Connections
http://lists.sqlmag.com/cgi-bin3/flo?y=ePLT0FgQMn0BRZ0ggP0AW

THIS ISSUE SPONSORED BY

TDWI - The Data Warehousing Institute

SQL Server Magazine Connections
(below COMMENTARY)

Translate Procedural Statements with Quest
(below NEWS AND VIEWS)

SPONSOR: TDWI - THE DATA WAREHOUSING INSTITUTE

TDWI World Conference - San Francisco, May 11-16, 2003. BI & DW Professionals can take advantage of more than 50 courses taught by highly sought-after industry gurus. Don't miss out on in-depth training, industry trends, and the latest technologies! Hot Topics include: Web Services & XML for Business Intelligence, Evaluating ETL and Data Integration Platforms, Revitalizing the Mature Data Warehouse, Performance & Capacity Management, and more! Register now and save! For a complete brochure, visit:
http://lists.sqlmag.com/cgi-bin3/flo/y/ePvT0FgQMn0BRZ077T0Ag

March 6, 2003—In this issue:

1. COMMENTARY

2. SQL SERVER NEWS AND VIEWS

3. ANNOUNCEMENTS

4. RESOURCES

5. HOT RELEASES (ADVERTISEMENTS)

6. NEW AND IMPROVED

7. CONTACT US

1. COMMENTARY

(contributed by Brian Moran, news editor, [email protected])

Faster than a speeding Oracle.

More powerful than a DB2.

Able to outperform MySQL in a single DDL statement.

It's a bird—it's a plane—it's SQL Server on Windows!

Two weeks ago, Microsoft broke through a barrier that many people thought couldn't be broken. The company posted a nonclustered TPC-C benchmark faster than anything that Oracle or DB2 has ever published. Microsoft posted a tpmC score of 433,107 with a price per tpmC of $12.98. Microsoft achieved the score using a 32-CPU database server running the 64-bit versions of Microsoft Windows Server 2003 Datacenter Edition and SQL Server 2000 Enterprise Edition. Oracle's best score is 427,760 tpmC with a price per tpmC of $17.75. The SQL Server score costs about a third less per transaction than the Oracle system and is a little more than 1 percent faster.

You might have noticed that 64-bit Windows Server 2003 and 64-bit SQL Server aren't yet commercially available. The Transaction Processing Performance Council (TPC) site says that this configuration isn't expected to be commercially available until around the end of June. Foul, you say! No fair comparing a future version of SQL Server to shipping versions of Oracle. Calm down. The Oracle configuration mentioned above won't be commercially available until about the end of May, so the comparison is fair.

The new SQL Server score isn't the fastest TPC-C score ever; it's the second-fastest nonclustered TPC-C score ever published. A database called SymfoWARE achieved the top honor with a score of 455,818 tpmC at a price of $28.58 running on a Fujitsu mainframe-class server with 128 processors. That score is 5 percent faster than the new SQL Server score but required four times as many processors at more than double the per-transaction cost. Besides, do you know anyone running SymfoWARE?

IBM hasn't posted a nonclustered TPC-C score for DB2 since March 19, 2001, so I'm going to leave it out of the comparison because it wouldn't be fair to compare new scores to a score that's nearly 2 years old. Presumably, IBM doesn't think DB2 is competitive in this benchmark, or it would have posted a score more recently. By all standards, SQL Server, Oracle, and DB2 are the dominant database platforms. SQL Server has a long history of being more cost-effective than Oracle and DB2, but it's never been less expensive AND faster.

This benchmark should finally put to rest the argument that SQL Server can't scale to the same levels that UNIX platforms can. For years, I've argued that SQL Server is fast enough to meet the needs of almost any business application. However, my argument didn't convince die-hard UNIX database fans who like to speculate about the future load on their database server. The recent Slammer debacle shows that Microsoft still has some challenges ahead in the world of enterprise-class support and service. However, this most recent TPC-C score should give organizations the confidence that SQL Server on Windows can meet their businesses' performance needs as well as or better than anything UNIX offers. Chances are, you'll never need to support 7,200 transactions per second, but it's nice to know you can.

For more information about this and other TPC-C benchmark scores, see the TPC site at http://www.tpc.org .

SPONSOR: SQL SERVER MAGAZINE CONNECTIONS

Looking for 3 to 4 days of technical drilldowns into Microsoft SQL Server? Want an opportunity to interact live with SQL Server Magazine writers and with Microsoft product architects? Register today for SQL Server Magazine Connections and get FREE access to Microsoft ASP.NET Connections and Visual Studio Connections! Visit our expo hall to see the latest technology and have a chance to win a Harley-Davidson. After hours, unwind at events like "Microsoft Unplugged," where no question is out of line, or march in the Mardi Gras Parade to the House of Blues for a night to remember.
http://lists.sqlmag.com/cgi-bin3/flo/y/ePvT0FgQMn0BRZ0ggP0AE

2. SQL SERVER NEWS AND VIEWS

Microsoft has consolidated three of its security tools—SQL Server 2000 SQL Scan, SQL Check, and SQL Critical Update—into one download, the SQL Critical Update Kit. The kit also includes a Systems Management Server (SMS) deployment tool and the Servpriv.exe utility. The SQL Server 2000 security tools help update editions of SQL Server 2000 and Microsoft SQL Server Desktop Engine 2000 (MSDE 2000) that are vulnerable to the SQL Slammer worm. The tools scan instances of SQL Server 2000 and MSDE 2000 to help detect vulnerabilities to Slammer, then apply updates to the affected files. You can download the SQL Critical Update Kit at http://www.microsoft.com/downloads/details.aspx?FamilyId=9552D43B-04EB-4AF9-9E24-6CDE4D933600&displaylang=en .

Microsoft announced hotfixes for two known bugs. The article "FIX: Cannot Remove Microsoft SQL Server 2000 Desktop Engine After You Upgrade to MSDE 2000 SP2" (available at http://support.microsoft.com/default.aspx?scid=kb;en-us;317619 ) explains that all editions of SQL Server 2000 (Personal, Evaluation, Developer, Standard, and Enterprise) include the 8.00.194.01 build of Microsoft SQL Server Desktop Engine 2000 (MSDE 2000) on the products' CD-ROMs. If you originally used this build of MSDE and you upgrade to MSDE 2000 Service Pack 2 (SP2), you can't remove MSDE 2000 from the computer. This problem occurs only if you upgraded the MSDE release to manufacturing (RTM) build (194.01) directly to SP2. The problem doesn't occur on any other editions of MSDE 2000. The problem also doesn't occur if you first upgrade from the RTM version to MSDE 2000 SP1, then upgrade to MSDE 2000 SP2. You can fix the problem by obtaining SQL Server 2000 SP3, but the article provides a hotfix that was available before the company released the latest SQL Server service pack.

The article "FIX: SQL Server 7.0 Scheduler May Periodically Stop Responding During Large Sort Operation" describes a rare condition in which SQL Server gives you the error message "2003-02-10 17:02:54.39 ods The Scheduler 2 appears to be hung. PSS 0x5F8B3110, EC 0x5F8B32C0, UMS Context 0x050D07D8." When SQL Server processes a query that includes a large, long-running sort operation, this error message can repeat as frequently as once per minute while the query is running. In some cases, SQL Server might also be slow to respond to other requests. The article notes that, although Microsoft does support a fix for this problem, you should apply the fix only to specific machines that are experiencing the scheduler response problem. You can obtain the hotfix and read about the problem at http://support.microsoft.com/default.aspx?scid=kb;en-us;814693 .

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "How many hours do you work in a typical week?" Here are the results (+/- 1 percent) from the 642 votes:

The next Instant Poll question is "Do you pay attention to TPC benchmark scores?" Go to the SQL Server Magazine Web site and submit your vote for 1) Yes, we use them to optimize our own database's performance, 2) Yes, we use them to plan future database configurations, 3) Yes, we use them to plan/justify database purchases, or 4) No, we don't use TPC information.
http://www.sqlmag.com

SPONSOR: TRANSLATE PROCEDURAL STATEMENTS WITH QUEST

Are you migrating from Oracle to SQL Server? This free white paper, by SQL Server expert Kevin Kline, discusses all the basics you'll need to know including identifiers, data types, cursors, packages, and more. Download today!
http://lists.sqlmag.com/cgi-bin3/flo/y/ePvT0FgQMn0BRZ077U0Ah

3. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

Order SQL Server Magazine University (SSMU) e-Learning Center Course 2201: "Windows 2000 Return On Intelligence" Training and Certification Kit, and save more than $2000 off the retail value if purchased separately! This package deal includes live instructor-led training, CD-ROMs for self-paced study, books, tools, and exam vouchers. Go now to
http://lists.sqlmag.com/cgi-bin3/flo/y/ePvT0FgQMn0BRZ075Q0Ab

SQL Server Magazine and CSA Research are proud to introduce the Database Performance Portal. IT professionals use the Performance Portal to conduct client, server, and network scalability studies; perform ad hoc systems health analysis; identify infrastructure bottlenecks; conduct offsite diagnostics; and qualify new hardware purchases. To visit the portal, go to
http://lists.sqlmag.com/cgi-bin3/flo/y/ePvT0FgQMn0BRZ07hf0An

4. RESOURCES

Running multiple database platforms is a way of life for most businesses, and many companies are running SQL Server and Oracle at the same time. Typically, the two database platforms are independent. But SQL Server's linked servers feature lets you seamlessly integrate your SQL Server applications with the tables in Oracle databases. To learn how to set up and use a linked-server connection from SQL Server 2000 to an Oracle database, read Michael Otey's March SQL Server Magazine article "The Direct Connection," available online at
http://www.sqlmag.com/articles/index.cfm?articleid=37678

Shiv recently restored a database after a hardware failure. Since the restore, his database's performance has been poor. He used SQL Server Profiler to trace the problem to the SQL statement:

SELECT * FROM Information_Schema.Columns WHERE Table_name='XXX'

This query takes as much as 20 seconds to run. Shiv has approximately 30 applications that call the SQL statement, and two of those call the statement within a tranaction. However he uses read-only locks, so he doesn't think he should be experiencing problems with blocking. Shiv has rebuilt the indexes on the schema view and has accounted for all the system settings he needed to set after the restore. Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=13406

(contributed by Brian Moran, [email protected])

Q. A local ISP hosts our SQL Server, and I haven't been able to see or connect to named instances running on the machine. I know that SQL Server 2000 named instances don't use TCP/IP port 1433, and I've verified that the TCP/IP ports that the SQL Server instances are using are open. Do you have any suggestions?

A. Many people know how to check which ports SQL Server instances are listening to by using the SQL Server Network Utility or by simply reading the SQL Server error log. But many people seem to forget, or simply don't know, that UDP port 1434 also needs to be open to ensure that you can correctly access and browse named instances. I encourage you to read up on this requirement. Searching SQL Server Books Online (BOL) for "1434" returns three valuable hits. You'll also find information about accessing named instances in my SQL Server Savvy column from July 2001, available at http://www.sqlmag.com/articles/index.cfm?articleid=21127 . For now, here's a simple explanation. SQL Server establishes a listener service on UDP port 1434 that lets a client query the server for a list of named instances and their network configuration information. This listener service always runs on UDP port 1434 and can't be configured to run on another port.

Send your technical questions to [email protected].

5. HOT RELEASES (ADVERTISEMENTS)

Microsoft and SQL Server Magazine appreciate your support of SQL Server on the Windows platform! We're running a 20-week quiz contest testing your SQL Server knowledge, with biweekly drawings for cool prizes. Enter today!
http://lists.sqlmag.com/cgi-bin3/flo/y/ePvT0FgQMn0BRZ06ST0A8

6. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

Popkin Software released System Architect 9.0, an enterprise architecture tool that features SQL Server support. System Architect integrates support for all areas of modeling, including business process modeling, object-oriented and component modeling with Unified Modeling Language (UML), relational data modeling, and structured analysis and design. SQL Server support lets you manage multiple System Architect encyclopedias. The software lets you synchronize SQL Server 2000 and 7.0 and Oracle 8 databases with System Architect physical data models. XML design functionality lets you model XML designs in an XML hierarchy diagram and includes support for external Document Type Definition (DTD), internal DTD, XML Instance documents, and Microsoft BizTalk Server. For pricing, contact Popkin Software at 800-732-5227.
http://www.popkin.com

Lockwood Technical announced SQLDiff, a server-based database-comparison tool that can quickly analyze the differences in tables, procedures, views, and users between two SQL Server 2000 or 7.0 databases. The software lets you observe differences in the object's script when shown side by side. SQLDiff also lets you programmatically merge the databases based on the differential analysis. SQLDiff supports data differences, so you don't need to buy multiple products for structural databases and data databases. Pricing is $199 for one developer license and $597 for five developer licenses. Contact Lockwood Technical at [email protected].
http://www.lockwoodtech.com

7. CONTACT US

Here's how to reach us with your comments and questions:

(please mention the newsletter name in the subject line)

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine completely devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y

The SQL Server Magazine Connections conference—loaded with best-practices information from magazine authors and Microsoft product architects—is designed to provide you with the latest SQL Server tools, tips, and real-life examples you need to do your job.
http://lists.sqlmag.com/cgi-bin3/flo?y=ePF50FgQMn0BRZ0ggP0At

Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.
http://www.winnetmag.net/email

Thank you for reading SQL Server Magazine UPDATE.