SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections


THIS ISSUE SPONSORED BY

Timesaving SQL Server Tips at Your Fingertips

InstallShield X
(Below COMMENTARY)


June 10, 2004—In this issue:

1. SQL Server Perspectives

  • Run Your SQL Servers Through the Best Practices Analyzer

2. News and Views

  • Microsoft Fixes UPDATE Statement Index Scan Error
  • Results of Previous Instant Poll: SQL Server Systems
  • New Instant Poll: SQL Server 2005

3. Reader Challenge

  • Winners of the June Reader Challenge: Averaging Employee Salaries
  • July Reader Challenge: Disabling Protocols

4. Announcements

  • Have You Created an Innovative SQL Server Solution?
  • Microsoft SQL Server Reporting Services LoadFest

5. Resources

  • What's New in SQL Server Magazine: Heterogeneous Management Tools
  • Hot Thread: Splitting Static Data
  • Tip: Turn Off AUTO_CLOSE

6. Events Central

  • Upcoming Webcast on SQL Server Yukon CLR for the DBA
  • SQL Server Magazine Connections: Fall Dates

7. New and Improved

  • Connect Directly Through the CLR
  • Keep IT in Tune with Business
  • Administer and Tune SQL Server

Sponsor: Timesaving SQL Server Tips at Your Fingertips

SQL Server Magazine delivers quality content relevant to all SQL Server professionals including developers, DBAs, and business intelligence architects. Choose from an array of helpful expertise that focuses on such hot topics as SQL Server 2005, Reporting Services, security, and much more. Each issue is packed full of hot-topic discussions, savvy advice, and timesaving tips that can be incorporated into your everyday work life. Subscribe today and get a free System Table Map Poster! Click here


1. SQL Server Perspectives

  • Run Your SQL Servers Through the Best Practices Analyzer

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

    Technology solutions have become so complex over the past few years that it's impossible for one person to know and understand every detail of every tool that an end-to-end solution includes. Unfortunately, businesses can't afford to maintain an army of world-class experts—a master for each solution component. That's why published best practices, which outline how to easily, efficiently, and effectively deploy solutions, are so important. What's even better is to have tools that do some of the hard work for you. Microsoft's new SQL Server Best Practices Analyzer (BPA) combines these aides, providing both best-practices information and verification tools to help you implement solutions in the best possible way.

    BPA is a database-management tool that lets you verify the implementation of common best practices on your servers. The best practices, typically focusing on SQL Server database resource utilization and administration factors, help you manage and operate your SQL Servers securely and efficiently. Microsoft released the beta version of BPA 6 months ago, and now BPA 1.0 is available for download from the Microsoft SQL Server home page and from Microsoft's Download Center. Note that Microsoft doesn't support upgrading from the beta version. You need to uninstall the beta before installing BPA 1.0 and make sure that no files remain from the beta-release installation if you plan to install version 1.0 in the same folder you installed the beta in. Also, Microsoft changed the BPA Repository format from the beta to the final release, so the beta BPA Repository isn't supported.

    I won't mislead you into thinking that a quick installation of this tool will magically solve all your SQL Server problems. Although BPA might not find a problem on your system, that doesn't mean there isn't room for improvement. I don't advise relying entirely on a tool to find problems on your servers without further investigation on your part. However, I think you'll find BPA a valuable addition to your SQL Server best-practices arsenal because it can detect more places for best-practices implementation than any one person or team. (For more information about best-practices resources, read my commentary "Top Best Practice? Learning Best Practices.")

    BPA isn't the definitive word about whether your servers and databases are perfectly configured. The tool won't tell you anything about your system's performance—a database that adheres to best practices isn't immune to subtle performance problems. But the tool also suggests that Redmond is starting to make larger investments in providing the SQL Server community with best-practices information and tools. Microsoft became a database leader by providing intuitive, easy-to-use graphical tools that make it easier for customers to install and manage databases. The next generation of tools needs to take another step and help us install and manage our databases not only easily but most efficiently and according to best practices. Time will tell whether Microsoft will continue to invest in BPA if the tool captures the SQL Server community's support. Check out the new BPA release, and let me know what you think and how you're using it.


    Sponsor: InstallShield X

    Simplify SQL Server configuration. The new InstallShield X revolutionizes the way installations are built. This powerful tool is the most comprehensive all-in-one installation-authoring solution for every platform, operating system, and device. InstallShield X lets you easily create Windows Installer (MSI), InstallScript(TM), or cross-platform installations and extend them to configure SQL Servers, Web services, and mobile devices. With InstallShield X, you can extend your installation capabilities to configure all aspects of SQL Server and run SQL scripts as part of your installation. The new SQL Server View lets you easily connect to SQL Servers and import database schema into your project. Edit SQL Scripts in the IDE and analyze using Microsoft Query Analyzer to simplify and accelerate your server-side installation projects.
         http://lists.sqlmag.com/cgi-bin3/DM/y/eA0FgQMn0A0BI460A2


    2. News and Views

  • Microsoft Fixes UPDATE Statement Index-Scan Error

  • Microsoft has released a hotfix for an index-scan error in UPDATE operations. When you run an UPDATE statement and the connection's isolation level is set to READ UNCOMMITTED, you might receive the error message "Error: 644, Severity: 21, State: 6. Could not find the index entry for RID '%.*hs' in index page % S_PGID, index ID %d, database '%.*ls'." This problem only occurs when you use an index scan as part of the UPDATE operation and perform the index scan as a parallel operation. When the isolation level is set to READ UNCOMMITTED, locks aren't held on rows that qualify for the scan from the time of the scan to the update. This behavior lets other connections modify a row that's already qualified for the update before the UPDATE operation is complete. In this situation, you can safely ignore the 644 error message. The UPDATE statement correctly reports that a specific row can't be found and terminates--any changes that might have been made are rolled back. The hotfix now available from Microsoft ensures that the locks are held on qualifying rows from the time of the scan to the update. To learn more about the problem and the supported hotfix, read the Microsoft article "FIX: You receive a 644 error message when you run an UPDATE statement and the isolation level is set to READ UNCOMMITTED" at
        http://support.microsoft.com/?kbid=834290

  • Results of Previous Instant Poll: SQL Server Systems

  • The voting has closed in SQL Server Magazine's Instant Poll for the question, "What's the main type of SQL Server system you have deployed?" Here are the results (+/- 1 percent) from the 256 votes (deviations from 100 are due to a rounding error):
    • 6% 8-way
    • 26% 4-way
    • 55% Dual-processor
    • 14% Single-processor

  • New Instant Poll: SQL Server 2005

  • The next Instant Poll question is "What SQL Server 2005 new feature or enhancement are you most excited about?" Go to the SQL Server Magazine Web site and vote for 1) Common Language Runtime (CLR) integration, 2) T-SQL enhancements, 3)rewritten Data Transformation Services (DTS), 4) new management tools and messaging middleware, or 5) security improvements.
        http://www.sqlmag.com

    3. Reader Challenge

  • June Reader Challenge Winners

  • contributed by Umachandar Jayachandran, challenge@sqlmag.com
    Congratulations to Nico De Greef, a software architect for Denco in Belgium, and Zivan Karaman, the head of the biostatistics unit for Limagrain in France. Nico won first prize of $100 for the best solution to the June Reader Challenge, "Averaging Employee Salaries." Zivan won second prize of $50. You can find a recap of the problem and the solution to the June Reader Challenge at
         http://www.sqlmag.com/articles/index.cfm?articleid=42894

  • July Reader Challenge: Disabling Protocols

  • Now, test your SQL Server savvy in the July Reader Challenge, "Disabling Protocols" (below). Submit your solution in an email message to challenge@sqlmag.com by June 17. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

    Here's the challenge: Sean is a systems administrator in a corporate IT department. He recently received some SQL Server security bulletins about attacks that exploit vulnerabilities in various network protocols. Sean wants to update each SQL Server 2000 installation in the network with the necessary patches. As an added security measure, Sean also wants to disable unnecessary protocols on the server. However, in Sean's company, the standard protocol for communications between SQL Server and clients is TCP/IP. Help Sean write a script that disables all protocols except TCP/IP for his SQL Server installation.

    4. Announcements

  • Have You Created an Innovative SQL Server Solution?

  • If so, you qualify to enter this awards program. Announcing the 3rd annual SQL Server Magazine Innovator Awards. The grand-prize winner will win a free trip to the 2004 SQL Server Magazine Connections Conference in Las Vegas, Nevada, and a special trophy. Fill out an entry form today to get the recognition you deserve. Click here

  • Microsoft SQL Server Reporting Services LoadFest

  • Don't miss the Microsoft SQL Server 2000 Reporting Services LoadFest Event in your area. Install and configure a 120-day evaluation copy of Reporting Services on your own server with the help of Reporting Services specialists plus receive hands-on training with Reporting Services. Register today for only $50—a $400 value.
         http://lists.sqlmag.com/cgi-bin3/DM/y/eA0FgQMn0A0BI470A3

    5. Resources

  • What's New in SQL Server Magazine: Heterogeneous Management Tools

  • SQL Server has a great set of management tools. Enterprise Manager, Query Analyzer, and SQL Server Profiler are powerful and easy to use. Although these tools bring valuable benefits, they have some limitations as well. First, the tools manage only SQL Server. Second, the management tools don't work across releases. In his June SELECT TOP(X) column, "Heterogeneous Management Tools," Michael Otey talks about three cross-platform management suites that help bridge the gap in heterogeneous database environments. The tools leverage DBAs' skills by making different database platforms' management tasks similar and let you manage multiple release levels of each database platform. Read this article today

  • Hot Thread: Splitting Static Data

  • NR75's company's tables are too big to query efficiently. The company wants to split the tables into smaller and more manageable structures, but it needs a solution that uses minimal space and efficient querying capabilities. Offer your advice and see what other people have said on SQL Server Magazine's Database Design forum at
         http://www.winnetmag.com/sqlserver/forums/messageview.cfm?catid=1673&threadid=121946

  • Tip: Turn Off AUTO_CLOSE

  • by Brian Moran, savvy@sqlmag.com

    Q. My SQL Server logs contain messages such as "2003-12-01 21:34:56 50 spid51 Starting Up Database 'IhateSpam'." Is it typical for SQL Server to constantly restart all my databases?

    A. The AUTO_CLOSE option is probably enabled for the databases that SQL Server is regularly restarting. AUTO_CLOSE closes the database when the last user closes his or her connection and all processes in the database have completed. When a user then tries to connect to the database, SQL Server reopens the database, generating the message you describe.

    By default, AUTO_CLOSE is enabled for Microsoft SQL Server Desktop Engine (MSDE) and disabled on all other SQL Server editions. MSDE is a small-footprint database, so this default makes sense in many situations. Closing the database lets SQL Server conserve memory resources, for example. However, you rarely want to enable this option on a database that multiple users regularly access because reopening the database causes delays for the users and constantly opening and closing a database consumes more resources than simply leaving the database open.

    You can enable or disable the AUTO_CLOSE option by using the ALTER DATABASE command. Note that sometimes when customers move a database from MSDE to SQL Server Standard Edition, the AUTO_CLOSE option remains enabled. You can find out whether this option is on by examining the DATABASEPROPERTYEX() function's IsAutoClose property.

    6. Events Central


    For a complete guide to Web and live events, see
       http://www.winnetmag.com/events

  • Upcoming Webcast on SQL Server Yukon CLR for the DBA

  • Join the Professional Association for SQL Server for a Webcast on the new .NET Common Language Runtime (CLR) integration with SQL Server 2005 presented by Michael Otey. In addition, we'll also explore how to use Visual Studio to create stored procedures and triggers. Registration is required for this Webcast, so sign up today:
         http://lists.sqlmag.com/cgi-bin3/DM/y/eA0FgQMn0A0BI480A4

  • SQL Server Magazine Connections: Fall Dates

  • The SQL Server Magazine Connections Conference is coming to Las Vegas on November 7-10 along with concurrently running events Microsoft ASP.NET Connections and Visual Studio Connections. Register early and receive access to all three conferences for one low price and get the best early-bird discount. Call 203-268-3204 or 800-438-6720.
         http://lists.sqlmag.com/cgi-bin3/DM/y/eA0FgQMn0A0ggP0Ai

    7. New and Improved


    (contributed by Dawn Cyr, products@sqlmag.com)

  • Connect Directly Through the CLR

  • DataDirect Technologies announced DataDirect Connect for .NET 2.1, DataDirect Connect for JDBC 3.4, and DataDirect Connect for ODBC 5.0, the latest releases in the company's suite of managed-code connectivity products. The products provide connectivity directly through the .NET Common Language Runtime (CLR), eliminating the need to process any unmanaged code--and thus eliminating an entire level of processing. DataDirect Connect for .NET 2.1 provides SQL leveling features that include standardized parameter markers and standardized error handling. Other new features include connection failover, client-side load balancing, and support for no-touch deployment. DataDirect Connect for JDBC 3.4 includes such new features as OS authentication and support for Kerberos, load balancing and connection failover, and developer-productivity enhancements. DataDirect Connect for IDBC 5.0 includes clientless wire protocol technology and support for multiple releases of all major databases. All the suite's drivers are CPU bound, which shifts bottleneck problems from the network to the CPU, improving performance and scalability. The product suite, which lets you connect any application on any platform using any API to any data source, works with SQL Server 2000 and 7.0. For pricing and more detailed product information, contact DataDirect Technologies at 800-876-3101.
         http://lists.sqlmag.com/cgi-bin3/DM/y/eA0FgQMn0A0BHyI0AU

  • Keep IT in Tune with Business

  • Heroix announced Heroix eQ Management Suite 3.0, monitoring software that helps keep your IT department in tune with your organization's business needs. In the latest release of the product suite, Heroix has added Line of Business Views, a feature that lets you not only identify database problems, but identify which of your organization's departments the problems affect. You can map infrastructure components to the business activities they support and get detailed information about problems so that you can quickly solve them. Graphical icons represent lines of business, making it easy to define and identify different business lines through the software's Web UI. You can customize lines of business to meet your organization's business and management needs. The new release of the suite also features expanded reporting and graphing capabilities as well as enhanced scalability and security. Heroix eQ Management Suite 3.0 supports SQL Server 2000 and 7.0. For pricing and other information, contact Heroix at 800-229-6500 or info@heroix.com.
         http://lists.sqlmag.com/cgi-bin3/DM/y/eA0FgQMn0A0BI5A0AF

  • Administer and Tune SQL Server

  • Quest Software announced Quest Central for SQL Server and Toad for SQL Server, freeware products that help DBAs and developers optimize SQL Server database performance. Quest Central for SQL Server is an integrated workbench of tools that let DBAs diagnose and solve performance problems, perform multiserver group administration, and tune database performance. The tools automate database administration, performance diagnostics, tuning, testing, and space management. Toad for SQL Server is a database-development tool that lets developers and DBAs write and test scripts and queries. Users can view and edit tables, indexes, and stored procedures. Quest Central for SQL Server and Toad for SQL Server are available free. For customers who want technical support and maintenance, a supported version of Quest Central for SQL Server is available, with pricing starting at $1995 per server. For more information, contact Quest Software at 949-754-8000.
         http://lists.sqlmag.com/cgi-bin3/DM/y/eA0FgQMn0A0BI5B0AG

    SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine 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

    CONTACT US


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

    • About SQL Server Perspectives — brianm@sqlmag.com
    • About the newsletter — kathy@sqlmag.com
      (please mention the newsletter name in the subject line)
    • About technical Questions — http://www.sqlmag.com/forums
    • About Product News — products@sqlmag.com
    • About your subscription — sqlupdate@sqlmag.com
    • About sponsoring SQL SERVER MAGAZINE UPDATE? — Kate Silvertooth (ksilvertooth@sqlmag.com

    Manage Your Account


    You are subscribed as #EmailAddr#

    To unsubscribe from this email newsletter, send an email message to mailto: #Mailing:UnsubEmail#.

    To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.
    http://www.winnetmag.com/email

    Copyright 2004, Penton Media, Inc.