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

Query Optimizer Strategies from Quest Software
http://lists.sqlmag.com/cgi-bin3/flo/y/ePgv0FgQMn0BRZ07uZ0A8

SQL Server Magazine Connections
http://lists.sqlmag.com/cgi-bin3/flo/y/ePgv0FgQMn0BRZ07hd0A5
(below COMMENTARY)

Find Answers to Your SQL Server Questions
http://lists.sqlmag.com/cgi-bin3/flo/y/ePgv0FgQMn0BRZ0KrA0Az
(below NEWS AND VIEWS)

SPONSOR: QUERY OPTIMIZER STRATEGIES FROM QUEST SOFTWARE

Learn the latest Query Optimizer strategies to improve your SQL Server queries from tuning expert and author Kevin Kline. This white paper discusses a number of Query Optimizing strategies to improve performance in your SQL Server queries. It will also cover topics such as: sub-query optimization, UNION vs. UNION ALL, SELECT INTO vs. INSERT...SELECT, parameters vs. variables in stored procedures, and more. Download your free white paper today and be entered into a raffle to win a free book by best-selling author and SQL Server expert Kevin Kline!
http://lists.sqlmag.com/cgi-bin3/flo/y/ePgv0FgQMn0BRZ07uZ0A8

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

Wow! It's been a while since my commentaries generated such a wide range of heated opinions. Two weeks ago, I slammed DBAs (pun intended) for failing to apply the hotfix that would have shut down the SQL Slammer worm ("After the Slammer,"). Last week, I apologized to DBAs for oversimplifying the Slammer situation and laying all the blame on their shoulders ("SQL Server DBAs Deserve an Apology,"). I also asked you, the readers, to share what you thought Microsoft could and should do to help us maintain secure systems. This week, let's look at a sampling of feedback about my Slammer columns and your suggestions about how Microsoft can help us avoid Slammer-like problems in the future.

After reading "SQL Server DBAs Deserve an Apology," some people still believe that DBAs shoulder most of the blame for SQL Slammer. The following reader noted, "Don't bow to the pressure. Your original assessment was right on the mark. The patch for MS02-039 was one file: SSNETLIB.DLL. Applying it meant copying one file and taking a 2-minute outage, depending on such things as database size. This crying about no installer is bull. People are afraid to apply patches and/or don't want to bother testing because either they don't know what to test or their application testing is an arduous manual process. These problems, however, are no excuse for not applying security patches within 6 months of their release."

Another reader wrote, "As a former military policeman, I remember one major lesson that has carried over to the civilian world: There is no room for political correctness if you must maintain real security. Security is not a gray area. [Your system] is either secure or it isn't."

Clearly, there's plenty of blame to go around when evaluating how the Slammer worm was able to attack and spread so quickly. However, SQL Slammer was simply a manifestation of the real problem: SQL Server professionals aren't applying hotfixes and service packs that fix known problems. Although many DBAs had valid reasons for not applying the patch, we can expect other SQL Slammer-like attacks unless we understand and solve the root problem. Why aren't SQL Server professionals applying hotfixes and service packs soon after their release, and how can we resolve these underlying problems? Here are some representative arguments and suggestions from the huge volume of reader mail I received.

A common theme among the feedback I received is that Microsoft needs to add SQL Server support to Windows Update and other crucial Microsoft notification services, especially because several Microsoft applications install Microsoft SQL Server Desktop Engine (MSDE) by default. As one reader pointed out, "I never thought about SQL Server service packs because I imagined that the automatic Windows Update service would recognize the need for them. [One way to make sure fixes are applied is to] ensure that the automatic Windows Update recognizes every Microsoft product on the machine and its need for service packs."

For years, Microsoft has rolled out SQL Server service packs, explaining in the readme.txt file that you can't uninstall the service pack. I received a deluge of comments saying that this practice must change. Despite the technical difficulties that Microsoft would face in changing this behavior for SQL Server, service packs and hotfixes must come with a reliable, easy-to-use uninstall feature, or people will continue to be wary of installing the latest and greatest patch. In the blunt words of one reader, "The onus is on Microsoft to provide all updates, service packs, hotfixes, and so on with an installer. The installers must log the changes and provide an uninstall capability. Period. Nothing less than this should ever be acceptable."

Another hurdle to applying patches comes from the sometimes out-of-synch mix of SQL Server and third-party products. Many readers use Commercial Off-the-Shelf (COTS) software, and their third-party vendors have strict guidelines about what versions of software they support. One reader reported, "I generally don't apply a service pack to SQL Server until I get the go-ahead from the third-party software company. It tests its software with the service pack before giving users the OK to run the new service pack. This could take 6 months or even longer." In this Catch-22 situation, DBAs must choose between rolling out a vital SQL Server patch or voiding the service policy from a vendor or even breaking the vendor's application. Microsoft can't work with every ISV to make sure their products support every patch. But Microsoft should at least ensure that top vendors, such as major ERP companies, have tested service packs in advance and will OK their installation.

Another frequent gripe is that DBAs can't afford the downtime required to install patches and service packs. As one reader noted, "Microsoft must realize that many systems run 24 x 7. The focus should be on how fixes, upgrades, and so on can be installed with almost no downtime—and without rebooting." Microsoft plans to address this problem in the upcoming Yukon release of SQL Server, but don't expect any progress on this roadblock until then.

The Slammer worm exposed a pervasive problem in the SQL Server community, and we won't solve the multiple, underlying causes of this problem overnight. However, the status quo is unacceptable. Microsoft, third-party software vendors, businesses, and IT staff must all find ways to make sure important patches are installed in a timely manner. The alternative is the likelihood of continuing Slammer-esque attacks.

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/ePgv0FgQMn0BRZ07hd0A5

2. SQL SERVER NEWS AND VIEWS

Three recent Microsoft articles address a SQL Server handle leak related to repeated connects and disconnects, a problem propagating IDENTITY columns when you use the SQLXMLBulkLoad object, and a Access Violation that occurs when you use impersonation and XML for Analysis (XMLA) connection pooling. The article "FIX: Handle Leak Occurs in SQL Server When Service or Application Repeatedly Connects and Disconnects with Shared Memory Network Library" explains why a handle leak in the SQL Server process might occur when a service or an application quickly and repeatedly connects to and disconnects from a local SQL Server 2000 database. These leaks occur in the SQL Server 2000 Service Pack 2 (SP2) version of the shared memory network library (Dbmslpcn.dll version 2000.80.534.0). To resolve the problem, you need to obtain the latest service pack for SQL Server 2000.

The article "PRB: 'No Data Was Provided for Column' Error Message When You Propagate Identity Column with SQLXMLBulkLoad" reports that when you try to use the SQLXMLBulkLoad object to propagate an IDENTITY value, you'll receive an error message. The article explains several resolutions to the problem.

The article "FIX: Access Violation May Occur When You Use Impersonation and XMLA Connection Pooling" explains that when you use connection pooling from the XMLA SDK to impersonate users who connect to Analysis Services, the connection won't work if you try to connect to the server with a previously used connection and the application might experience an Access Violation. Microsoft has a supported fix to correct the problem and recommends that you apply the fix only to systems experiencing this specific problem.

Before you install SQL Server 2000 Service Pack 3 (SP3) on a SQL Server 2000 virtual server, you must complete several essential checks to your system. Read about these checks—and why your installation might fail if you don't complete them—in the Microsoft article "INF: SQL Server 2000 Service Pack 3 Pre-Installation Checklist for SQL Server 2000 Virtual Server for Failover Clustering" at
http://support.microsoft.com/default.aspx?scid=kb;en-us;811168

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Are you considering consolidating SQL Servers in the next 12 months?" Here are the results (+/- 1 percent) from the 144 votes:

Sponsored by Precise Software Solutions

Download a FREE copy of Precise/Indepth for SQL Server and start Optimizing Your Database Performance Today!

The next Instant Poll question is "What type of backup do you use?" Go to the SQL Server Magazine Web site and submit your vote for 1) Backup to tape, 2) Disk-to-disk backup, 3) Mirroring and snapshot technologies, or 4) Other.
http://www.sqlmag.com

SPONSOR: FIND ANSWERS TO YOUR SQL SERVER QUESTIONS

Inside the new March 2003 release of the SQL Server Magazine Master CD, you'll find a gold mine of SQL Server code, tips, and information! PLUS, you'll receive the entire T-SQL Solutions newsletter article archive in addition to the entire article archive of SQL Server Magazine! Here's your opportunity for real-time access to expert information right on your desktop. Search by keyword, subject, author, or issue. Order your one-year subscription today!
http://lists.sqlmag.com/cgi-bin3/flo/y/ePgv0FgQMn0BRZ0KrA0Az

3. ANNOUNCEMENTS

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

High-quality, live, instructor-led training without leaving your desk! SQL Server Magazine University (SSMU) e-Learning Center offers training courses to help you prepare for your Microsoft exams, plus provides you with improved skills on the job now. For a complete list of course offerings and class details, go to
http://lists.sqlmag.com/cgi-bin3/flo/y/ePgv0FgQMn0BRZ067v0AY

SQL Server Magazine University (SSMU) Web seminar speakers are tried-and-true people you've come to know and trust through their articles and insights published in SQL Server Magazine. Finally, online training led by SQL Server gurus with real-life business application experience, not just theory! Get complete course info at
http://lists.sqlmag.com/cgi-bin3/flo/y/ePgv0FgQMn0BRZ07YW0Ac

4. RESOURCES

In the past 10 years, Microsoft has transformed SQL Server from a limited-scale departmental database to a leader in the enterprise database marketplace. In honor of SQL Server's 10th anniversary this year, Michael Otey tours SQL Server's six major releases, then looks at the upcoming Yukon release in his February SQL Seven column, "Past, Present, and Future." You can read this article in SQL Server Magazine or online at
http://www.sqlmag.com/articles/index.cfm?articleid=37471

Andrutek needs clarification about how SQL Server stores text and image data. SQL Server Books Online (BOL) says that "text data is stored in a collection of pages separate from the pages holding the data for the other columns of the row... All that is stored in the data row is a 16-byte pointer." Does SQL Server store this data within the same extent or .mdf file, or does it store the data in a separate file or filegroup? 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=13040

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

Q. We often add columns to replicated tables. How can we add a column without having to reinitialize the entire publication?

A. In SQL Server 2000, you can use the sp_repladdcolumn stored procedure to add a column to a replicated table without reinitializing the entire publication because the stored procedure automatically adds the column at the subscriber. For example, if the authors table in the Pubs database has already been published, you can add the newcol integer column to that table by executing the following stored procedure:

sp_repladdcolumn @source_object = 'authors'      , @column =  'newcol'      , @typetext = 'INT'      , @publication_to_add = ''

Note that you can use the stored procedure sp_repladdcolumn to add only new columns to a replicated table; you can't use it to manage a table's existing columns. To drop existing columns from a published table, you can use the sp_repldropcolumn stored procedure.

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 the retail value if purchased separately! Get the details at:
http://lists.sqlmag.com/cgi-bin3/flo/y/ePgv0FgQMn0BRZ07ub0AH

6. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

AquaFold released Aqua Data Studio, a free SQL editor and developer tool that lets you create, edit, and execute SQL scripts and browse database structures. The software provides an integrated database environment that gives you one interface to all relational databases, letting you handle multiple development tasks simultaneously from one application. The Query Analyzer features let users develop and test database scripts. Aqua Data Studio supports SQL Server 2000 and 7.0, Oracle, and IBM DB2. You can download the software at AquaFold's Web site.
http://www.aquafold.com

SRF Engineering released DBWatch, a debugging utility for developers who write programs that modify the data tables in a SQL Server or Microsoft Access database. DBWatch gives you a difference report between any two points in time on the data tables in the target database. You can find out which data in which tables recently changed. You simply load the database into DBWatch and execute the program. Under test, press a "difference" button, and view how DBWatch displays all the tables that have changes noted in red, green, and blue (modified, deleted, and added records, respectively). Pricing is $25 per license. Contact SRF Engineering at [email protected].

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.