1. COMMENTARY

GETTING TO THE ROOT OF SLAMMER

(contributed by Brian Moran, news editor, brianm@sqlmag.com)

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 "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.

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:

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

3. ANNOUNCEMENTS

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

SSMU WEB SEMINAR SPEAKERS MAKE THE DIFFERENCE!

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

WHAT'S NEW IN SQL SERVER MAGAZINE: PAST, PRESENT, AND FUTURE

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

HOT THREAD: HOW DOES SQL SERVER STORE TEXT AND IMAGE DATA?

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

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:

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.

6. NEW AND IMPROVED

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

FIND OUT WHICH DATA IN WHICH TABLES CHANGED

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 richardfen@cox.net.

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

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More