SQL Server Magazine UPDATE, February 27, 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

SQL Guide from NetIQ

Get Real-Time, Real Answers, Really Fast!
http://lists.sqlmag.com/cgi-bin3/flo/y/ePoy0FgQMn0BRZ0KrA0AC
(below COMMENTARY)

AGS SQL Scribe Documentation Builder
(below NEWS AND VIEWS)

SPONSOR: SQL GUIDE FROM NETIQ

Is SQL a pain in your DBA? Capacity problems slowing database performance? Learn how to eliminate common SQL Server issues permanently. Get NetIQ's FREE guide, "The 10 Pains Every SQL DBA Wished They Could Forget," to learn how to deliver great SQL performance and availability.
http://anon.doubleclick.speedera.net/anon.doubleclick/PentonMedia/netiq022503.html

February 27, 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])

Last week, I revealed the SQL Slammer worm as a symptom of a larger problem: Many SQL Server professionals aren't promptly applying service packs and hotfixes. After exploring the many reasons contributing to this problem and the level of responsibility a vendor such as Microsoft has in making it easier to apply patches, I shared reader suggestions about how Microsoft could help us stay current and secure. Rome wasn't built in a day, and I don't expect Microsoft to fully address this problem overnight. But I'd like to close this series of Slammer commentaries with a statement that Microsoft Vice President of SQL Server Gordon Mangione made during a keynote address at a recent SQL Server conference: "Success can't be measured by whether or not a patch had been released and was available to our customers. Success needs to be measured by whether or not our customers were affected."

You might think that Mangione just stated the obvious. But I doubt Microsoft would have expressed such a sentiment in the not-so-distant past. Not long ago, the company would likely have taken the "we had a patch, so it's not our fault" position. In fact, that's exactly the stand Microsoft initially adopted last year when a series of email viruses hit the world—before tremendous community pressure forced the company to reevaluate its commitment to security. So Mangione's simple statement reflects a big shift in Microsoft's policy toward supporting the enterprise market.

Of course, Microsoft officials can easily make such a statement, but nice words don't guarantee that anything is fixed or will be fixed soon. SQL Server Magazine UPDATE readers shared myriad reasons why trained DBAs chose not to apply SQL Server 2000 Service Pack 3 (SP3) or the patch that would have kept Slammer from spreading. Microsoft must address these problems and initiate an ongoing dialogue with the user community to understand the pain that DBAs experience as they try to keep their systems up-to-date and secure.

I suspect that Microsoft will make a valiant effort to address these needs, though not out of any noble or philanthropic desire. Instead, I'm giving Microsoft credit for recognizing that SQL Server will fail as an enterprise-class database platform unless the company fully addresses this problem. Microsoft's new Transaction Processing Performance Council (TPC) TPC-C benchmark scores, which beat any single-server scores ever published by Oracle or IBM (see "64-bit SQL Server Clocks Fastest 32-Way TPC-C Result" in the News and Views section below), should put questions about SQL Server's scalability to rest once and for all. But bet on Oracle and IBM to start beating the drum, "Sure it's fast, but you can't count on it."

Perception often trumps reality. In some ways, it doesn't matter whether or not SQL Server is trustworthy; it matters whether or not people believe that SQL Server is trustworthy. Microsoft doesn't want a Slammer repeat, but other attacks are almost guaranteed to happen unless the company makes it less painful for part-time and full-time DBAs to keep their SQL Servers patched and up-to-date. I'm trusting Microsoft to tackle this problem head-on—not because it's the right thing to do (which it is) but because failure to do so will doom SQL Server to second-class status. And too much money is at stake in the enterprise database market for Microsoft to sit idly by and let that happen.

GET REAL-TIME, REAL ANSWERS, REALLY FAST!

Here is your opportunity for real-time, high-speed access to all of the articles, code, and expertise from SQL Server Magazine and T-SQL Solutions—available right on your desktop. Access the entire collection of SQL Server Magazine and T-SQL Solutions articles since their premiere issues, and get answers fast by searching by keyword, subject, author, or issue. Order your one-year subscription today! It will include the current CD of the complete archive through March 2003 and a second CD, which will be shipped following the release of the September 2003 issue. Place your order now at:
http://lists.sqlmag.com/cgi-bin3/flo/y/ePoy0FgQMn0BRZ0KrA0AC

2. SQL SERVER NEWS AND VIEWS

Microsoft's 64-bit computing platform continues to claim performance records on the way to its launch, expected in April. Last week, NEC published a Transaction Processing Performance Council (TPC) TPC-C benchmark result that used the 64-bit versions of SQL Server and Windows Server 2003 to clock the fastest 32-way online transaction processing (OLTP) scores in the world. SQL Server 2000 and Windows Server 2003 now hold world records for performance for 2-way, 4-way, 8-way, and 32-way systems.

The NEC Express5800/1320Xc—running the 64-bit version of Windows Server 2003, Datacenter Edition, and SQL Server 2000 Enterprise Edition (64-bit)—recorded 433,107 transactions per minute (tpmC). The one system that's posted a higher TPC-C non-clustered score achieved only 5 percent more performance using four times as many processors and cost more than twice as much per transaction.

At $12.98 per tpmC, this new result features the third best price-performance of any result among the top 10 performers. The two systems that claim better price-performance also run Windows Server 2003 and SQL Server 2000 Enterprise Edition, giving Windows-based systems the top three price-performance results in this category. For more information about this and other TPC-C benchmark scores, see the TPC site at
http://www.tpc.org

SQL Server Magazine and CSA Research have launched an online tool called the Database Performance Portal. You can use the Database Performance Portal to study client, server, and network scalability. You can also use the portal to analyze system health, identify infrastructure bottlenecks, conduct offsite diagnostics, and qualify new hardware purchases. To see what the portal can help you with, go to
http://csaperform.sqlmag.com

The Microsoft BizTalk Adapter for SQL Server, which lets you read and write to the database from BizTalk Server 2002, is now available for download. Microsoft says the BizTalk Adapter for SQL Server gives application developers, Web publishers, and line-of-business managers a cost-effective and manageable way to integrate data that's stored in SQL Server with solutions built on BizTalk Server 2002. The solution doesn't require coding; data, in the form of XML documents, is automatically transferred between SQL Server and BizTalk without the need to write and compile new business logic.
http://www.microsoft.com/downloads/details.aspx?FamilyID=9f420a48-edc2-416f-b7c0-28d15f747e83&DisplayLang=en

Sponsored by Precise Software Solutions
http://lists.sqlmag.com/cgi-bin3/flo/y/ePoy0FgQMn0BRZ071p0AY

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "What type of backup do you use?" Here are the results (+/- 1 percent) from the 354 votes (deviations from 100 percent are due to rounding errors):

The next Instant Poll question is "How many hours do you work in a typical week?" Go to the SQL Server Magazine Web site and submit your vote for 1) Less than 40 hours, 2) 40 hours, 3) 41 to 50 hours, 4) 51 to 60 hours, or 5) More than 60 hours.
http://www.sqlmag.com

SPONSOR: AGS SQL SCRIBE DOCUMENTATION BUILDER

SQL Scribe Documentation Builder is a complete documentation system for Microsoft SQL Server 7.0 and SQL Server 2000. This is an essential tool for administrators and developers alike. Create active documentations automatically in minutes. Download your trial at:
http://lists.sqlmag.com/cgi-bin3/flo/y/ePoy0FgQMn0BRZ071o0AX

3. ANNOUNCEMENTS

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

Now is the time to start thinking of storage as a strategic weapon in your IT arsenal. Come to our 10-city Network Storage Solutions Road Show, and learn how existing and future storage solutions can save your company money—and make your job easier! There is no fee for this event, but space is limited. Register today!
http://lists.sqlmag.com/cgi-bin3/flo/y/ePoy0FgQMn0BRZ07cD0Ae

The industry's best instructors have teamed with SQL Server Magazine University (SSMU) to bring you the finest live online SQL Server training! Whether you're at the advanced level or just beginning, you'll find training to meet your needs. Plus, you don't have to leave your desk; events are delivered live through the Internet! Click here:
http://lists.sqlmag.com/cgi-bin3/flo/y/ePoy0FgQMn0BRZ067v0Aj

4. RESOURCES

One of the most dangerous system attacks—SQL Injection—is code-based, and defending against it falls squarely on the application developer's shoulders. A form of network attack, SQL Injection works by passing unexpected SQL code into an application. Unlike many security exploits you read about, SQL Injection isn't just a Microsoft or SQL Server problem; all SQL-based databases are open to SQL Injection attack. Learn how to defend against SQL Injection by reading Michael Otey's Editorial "Coding Defensively," published in the March issue of SQL Server Magazine and available online at
http://www.sqlmag.com/articles/index.cfm?articleid=37813

Papaluco has a column that stores text or varchar data, and he wants to be able to select only the first 20 characters from that column to see generally what it holds and avoid having to select the column's full and possibly very large contents. 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=3&threadid=13248

(contributed by Microsoft's SQL Server Development Team, [email protected])

Q. I want to update a large table in 5000-row batches, but I don't know how to split up the data. The table doesn't include an incremental numeric or integer primary key. How can I perform the update while maintaining good performance?

A. If you know which rows haven't been updated and you can exclude updated rows by using a simple predicate, the ROWCOUNT setting can help you divide your update into batches. The following code shows how to use this setting:

      SET ROWCOUNT 1000      WHILE (1=1) BEGIN         BEGIN TRANSACTION         UPDATE...set ...,MyLastUpdate='date',...WHERE           MyLastUpdate < 'date'             — Update 1000 nonupdated rows         IF @@ROWCOUNT = 0         BEGIN            COMMIT TRANSACTION            BREAK         END         COMMIT TRANSACTION      END

ROWCOUNT causes SQL Server to stop processing the query after the specified number of rows is returned. This technique is useful because it avoids the concurrency hits that large updates incur; the smaller the x (the number of rows in the updates), the less likely that the update task will prevent other users from accessing the data. Combined with transaction-log backups, this method can also keep your transaction-log size to a minimum.

If you don't have a mechanism for identifying the rows that have been updated, you can use a cursor to iterate through the data and commit every x values. However, cursors usually hold server resources longer than set-based statements do.

Send your technical questions to [email protected].

5. HOT RELEASES (ADVERTISEMENTS)

Order SQL Server Magazine University (SSMU) e-Learning Center Course 2201, "Windows 2000 Return On Intelligence" Training and Certification Kit, and save over $2,000 off of the retail value if purchased separately! Get the details at:
http://lists.sqlmag.com/cgi-bin3/flo/y/ePoy0FgQMn0BRZ07ub0AS

Want technical drilldowns into SQL Server, plus interaction with SQL Server Magazine writers and Microsoft product architects? Register for SQL Server Magazine Connections and get FREE access to Microsoft ASP.NET Connections and Visual Studio Connections!
http://lists.sqlmag.com/cgi-bin3/flo/y/ePoy0FgQMn0BRZ07hd0AH

6. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

Protegrity released Secure.Data F3, a database-security solution for SQL Server 2000 that uses nCipher's cryptographic Federal Information Processing Standard (FIPS) 140 Level 3-certified hardware. The integrated Secure.Data solution with nCipher nShield Hardware Security Model (HSM) helps customers address strict government, industry, and internal auditing standards to protect database information from internal and external threats. Secure.Data enhances key management to let you securely store, dispose, archive, and recover the master key. For pricing, contact Protegrity at 203-326-7200 or [email protected].
http://www.protegrity.com

Digital Lenz released Code Storm Professional 2003, software that examines your database and produces SQL code and program source code. The script-driven software lets you generate any code you want. Currently available as a Visual Basic (VB) add-in and standalone Windows executable, Code Storm Professional ships with the most common tasks you would need, such as building stored procedures and the source code to execute the stored procedures. The software comes with its own Script Manager and Script Editor. Code Storm Professional supports SQL Server 2000 and 7.0, Oracle, and Sybase and costs $235. Contact Digital Lenz at [email protected].
http://www.digital-lenz.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.