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


THIS ISSUE SPONSORED BY

FREE DOWNLOAD: Experience Real-Time Monitoring

Attention Visitors to www.sqlmag.com
(below COMMENTARY)

Build XML Applications with XMLSPY 5!
(below NEWS AND VIEWS)


SPONSOR: FREE DOWNLOAD: EXPERIENCE REAL-TIME MONITORING

Poring over event logs after the fact? Undetected failed services causing havoc? Which system resource will be your next bottleneck? TNT Software's ELM Enterprise Manager is the affordable solution that provides centralized monitoring of the health and status of your systems. Equally important, ELM alerts you by page, email, or instant message in time for you to take prompt corrective action. Download your FREE 30-day fully functional evaluation software of ELM Enterprise Manager 3.1 NOW and start experiencing the benefits of real-time monitoring.
   http://lists.sqlmag.com/cgi-bin3/DM/y/eRo20HoDNN0BRZ0BASG0Ay


July 17, 2003—In this issue:

1. COMMENTARY

  • Step on the BI Accelerator

2. SQL SERVER NEWS AND VIEWS

  • Microsoft Adopts New Hotfix Naming Schema
  • Results of Previous Instant Poll: Authentication Choices
  • New Instant Poll: Accelerating BI

3. ANNOUNCEMENTS

  • SSMU Business Intelligence Online Mini-Series
  • SQL Server Magazine Connections: 4-for-1 Offer

4. RESOURCES

  • What's New in SQL Server Magazine: BLOB Access Technologies
  • Hot Thread: Access Violation Error
  • Tip: Avoid Auto-Growing Tempdb When SQL Server Starts

5. HOT RELEASES (ADVERTISEMENTS)

  • Get High-Speed Access to Article Archives
  • SQL Server Worldwide User's Group Help Center

6. NEW AND IMPROVED

  • Ensure Optimal Network Performance from Anywhere
  • Get Dynamic Views of Your Business Information

7. CONTACT US

See this section for a list of ways to contact us.


1. COMMENTARY

  • STEP ON THE BI ACCELERATOR

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

    If you're dying to dabble with business intelligence (BI), but don't know where to start, you might want to investigate SQL Server Accelerator for Business Intelligence. On June 25, 2003, Microsoft announced the product's latest release, which includes several improvements on the older version:

    • Support for SQL Server 2000 Standard and Enterprise editions
    • Support for Office 2003
    • The ability to generate a solution from an XML file, which lets developers more easily integrate SQL Server Accelerator for BI directly into their products
    • Separation of the application-generation and deployment processes so that developers can deploy applications faster
    • Support for drillthrough actions in count measures, which enhances the reporting capabilities of generated applications

    The support for SQL Server 2000 Standard Edition is particularly nice because it opens up SQL Server Accelerator for BI to a much wider audience than the previous version, which required SQL Server Enterprise Edition. SQL Server Accelerator for BI employs industry best practices and automates the creation of the back end for a customized BI solution. These features let SQL Server customers spend less time coding and more time building unique end-user BI applications.

    Developing a complex BI and data-warehousing solution isn't easy, and SQL Server Accelerator for BI isn't going to cure all your BI problems. However, the product does provide infrastructure, back-end processing, and rich analytics that you can customize without too much effort. SQL Server Accelerator for BI is most valuable if your analytical needs closely match the core data models and analytics included in the product. You'll find out-of-the-box solutions built around retail sales and marketing, and the SQL Server Accelerator for BI Web site also includes links to download to free models for manufacturing and financial services environments.

    Don't expect SQL Server Accelerator for BI to be as simple to use as typical Microsoft wizards. The product's new version is best suited to individuals who are already experienced in using Microsoft tools to build BI solutions. But the product isn't off limits for novices; less experienced users can learn from the frameworks. And you can even learn quite a bit simply by reading the detailed "Prescriptive Architecture Guides for BI" that come with the product and reviewing the data model design and analytic measures that are included in each of the four solutions.

    As with other Microsoft products, SQL Server Accelerator for BI is "platform play;" Microsoft encourages companies to build on top of these products solutions that other SQL Server customers can use. SQL Server Accelerator for BI is designed so that you can tie into the generation tools through metadata and create your own templates for rolling out data marts. This functionality might make sense for a corporate IT department that needs to provide a starting point for internal development efforts or for a third-party software company that has rich, analytic industry knowledge and wants to package that content into a reuseable tool.

    You can download SQL Server Accelerator for BI for free at http://www.microsoft.com/solutions/bi/ssabi/nextsteps/default.asp. Notice that the download site forces you to sign in with a Microsoft Passport account. Personally, I use my passport account regularly, but I don't like the fact that Microsoft is making people get passport accounts just for the download. Don't let this requirement stop you from evaluating this handy suite of tools, though, if BI is on your horizon.


    ATTENTION VISITORS TO www.sqlmag.com

    If you've been putting off subscribing to SQL Server Magazine, now is the time to act. Starting July 1, the last 24 issues of SQL Server Magazine online are locked down and available only to subscribers. For a limited time, subscribe at the best rates ever offered online!
        http://lists.sqlmag.com/cgi-bin3/DM/y/eRo20HoDNN0BRZ0BAw30AG


    2. SQL SERVER NEWS AND VIEWS

  • MICROSOFT ADOPTS NEW HOTFIX NAMING SCHEMA

  • Microsoft adopted a standardized naming schema for all SQL Server hotfix patches or updates that the company creates or distributes. The new schema uses the format "ProductName-KBArticleNumber-BuildNumber-Language.exe." For example, a SQL Server 2000 hotfix name appears as "SQL2000-KB123456-8.00.0000-ENU.exe," and a SQL Server 7.0 hotfix name is "SQL70-KB123456-v7.00.0000-ENU.exe."

    For more information about the new naming schema, see the Microsoft article "INF: New Naming Schema for Microsoft SQL Server Hotfix Packages" at http://support.microsoft.com/default.aspx?scid=kb;en-us;822499 .

  • RESULTS OF PREVIOUS INSTANT POLL: AUTHENTICATION CHOICES

  • The voting has closed in SQL Server Magazine's Instant Poll for the question, "What kind of authentication do you use in your SQL Server environment?" Here are the results (+/- 1 percent) from the 499 votes:
    • 65% SQL Server and Windows authentication (mixed)
    • 12% Windows authentication
    • 23% Both, for different situations
    • 0% I'm not sure

  • NEW INSTANT POLL: ACCELERATING BI

  • The next Instant Poll question is "Do you use SQL Server Accelerator for BI?" Go to the SQL Server Magazine Web site and vote for 1) Yes, 2) No, but I'm interested in the new release, 3) No, and I'm not interested.
        http://www.sqlmag.com

    SPONSOR: BUILD XML APPLICATIONS WITH XMLSPY 5!

    Design, Build, Test, and Deploy XML and Web services applications with XMLSPY 5. New XSLT Debugger, WSDL editor, HTML-to-XML conversion, XML schema code generation, and much more! XMLSPY 5 is the most comprehensive XML tool for Advanced Application Development. Get your FREE download NOW!
        http://lists.sqlmag.com/cgi-bin3/DM/y/eRo20HoDNN0BRZ0BBQM0A4


    3. ANNOUNCEMENT


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

  • SSMU BUSINESS INTELLIGENCE ONLINE MINI-SERIES

  • Don't miss the Business Intelligence Mini-Series, an advanced-level online training course for SQL Server professionals. This four-part Web seminar series will be August 6, 13, 20, and 27, 2003, from 1:00 P.M. to 2:00 P.M. Eastern time. Business technology professional Scot Reagin teaches the course. Get complete details, including early-bird pricing info at
        http://lists.sqlmag.com/cgi-bin3/DM/y/eRo20HoDNN0BRZ0BBJA0Ak

  • SQL SERVER MAGAZINE CONNECTIONS: 4-FOR-1 OFFER

  • Secure your seat for SQL Server Magazine Connections. The event runs concurrently with Microsoft ASP.NET Connections, Visual Studio Connections, and Microsoft Office System Connections from October 12-15, 2003 in Palm Springs, California. Register now to receive the lowest possible registration fee plus access to all four conferences for one low price.
        http://lists.sqlmag.com/cgi-bin3/DM/y/eRo20HoDNN0BRZ0qSH0Ah

    4. RESOURCES

  • WHAT'S NEW IN SQL SERVER MAGAZINE: BLOB ACCESS TECHNOLOGIES

  • Microsoft provides several tools for working with binary large object (BLOB) data—native text, ntext, or image data types in SQL Server. In "BLOB Access Technologies," Michael Otey describes seven of the most important access technologies for working with BLOBs in SQL Server. Read this July SQL Server Magazine article at
        http://www.sqlmag.com/articles/index.cfm?articleid=39058

  • HOT THREAD: ACCESS VIOLATION ERROR

  • Sailor is copying data from eight tables in an Oracle database to SQL Server by using the Microsoft ODBC Driver for Oracle and the DTS Wizard. Although some of the data appears to copy successfully, for four of the eight tables, Sailor gets the error message "Provide generated code execution exception: EXCEPTION_ACCESS_VIOLATION." Sailor has run queries against the tables in FOCUS and Microsoft Access 2002 without getting this error; what's causing it? See what other DBAs have said, and offer your advice, on SQL Server Magazine's Security forum at the following URL:
        http://www.sqlmag.com/forums/messageview.cfm?catid=8&threadid=17371

  • TIP: AVOID AUTO-GROWING TEMPDB WHEN SQL SERVER STARTS

  • (contributed by Brian Moran, savvy@sqlmag.com))

    Most DBAs know that growing a file dynamically is an expensive operation that degrades performance because write and read activity to the file must temporarily be blocked while the file is growing. However, when managing space for the tempdb database, many DBAs forget the performance impact of auto-growing files. SQL Server rebuilds tempdb each time the database system is stopped and restarted. Tempdb files might have auto-grown, which is the default configuration, since SQL Server was last started. SQL Server doesn't remember the size of the tempdb files when it rebuilds tempdb during a restart. Instead, SQL Server resets the files to the size they were manually configured to be.

    Consider the following situation. Tempdb in SQL Server 2000 defaults to having one data file that has the logical name tempdev. The initial size of this file is 8MB. Assume that you manually increase the file size to 500MB by using the ALTER DATABASE command. The file subsequently grows to 550MB through auto-grow operations. SQL Server will reset the tempdev file to 500MB when it rebuilds tempdb. Now, assume that no one manually increased the file size from 8MB to 500MB. Instead, SQL Server auto-grew tempdb to 550MB. In this case, SQL Server resets tempdev back to 8MB when it's stopped and restarted.

    The operations that grow a database file can diminish performance because reads and writes are blocked each time the file is grown. In addition, auto-growing the file might contribute to fragmentation on the physical disks. Also, the default growth increment for tempdb is 10 percent. So, for example, SQL Server would need to invoke many auto-grow operations to grow tempdev from 8MB to 550MB. That's bad. However, it's worse if the same cycle happens whenever SQL Server is restarted, assuming that 550MB is the average high-water mark that tempdb reaches during typical operations. Even worse, you might have a hard time troubleshooting why performance is sometimes slower immediately after a server is rebooted.

    You can use the query below to find tempdb files that have auto-grown since SQL Server was last stopped and started.

       SELECT
          alt.filename
          ,alt.name
          ,alt.size * 8.0 / 1024.0 AS originalsize_MB
          ,files.size * 8.0 / 1024.0 AS currentsize_MB
       FROM
          master.dbo.sysaltfiles alt INNER JOIN tempdb.dbo.sysfiles files
       ON
             alt.fileid = files.fileid
       WHERE
          dbid = db_id('tempdb')
          AND alt.size <> files.size

    In general, manually setting tempdb files to a reasonable size will help you avoid significant auto-growth operations. Most importantly, if tempdb files do auto-grow, you need to determine whether the new file sizes are reasonable high-water marks. If they are, consider manually setting the file size to compensate. For example, if a file auto-grew from 8MB to 550MB, you could manually set the file size to 551MB. The file would then be rebuilt to a size of 551MB when SQL Server was stopped and restarted the next time.

    Send your technical questions to savvy@sqlmag.com

    5. HOT RELEASES (ADVERTISEMENTS)

  • GET HIGH-SPEED ACCESS TO ARTICLE ARCHIVES

  • The SQL Server Magazine Master CD provides realtime desktop access to the articles, code, and expertise published in SQL Server Magazine and T-SQL Solutions. Search by keyword, subject, author, or issue. Order your subscription today:
        http://lists.sqlmag.com/cgi-bin3/DM/y/eRo20HoDNN0BRZ0KrA0AT

  • SQL SERVER WORLDWIDE USER'S GROUP HELP CENTER

  • SSWUG.org (www.sswug.org) provides resources, help, articles, scripts, news, links and much more on a daily basis on the use and support of SQL Server, Oracle and XML. Sign up for the daily newsletter.
        http://lists.sqlmag.com/cgi-bin3/DM/y/eRo20HoDNN0BRZ0BAaZ0AX

    6. NEW AND IMPROVED


    (contributed by Carolyn Mader, products@sqlmag.com)

  • ENSURE OPTIMAL NETWORK PERFORMANCE FROM ANYWHERE

  • Expand Beyond announced PocketAdmin, mobile software for remote Windows administration. PocketAdmin now bundles with PocketDBA, which is software for remote SQL Server 2000 administration, and PocketAdmin Console for command-line management in the Expand Beyond Mobility Suite for Microsoft (MSM). PocketDBA provides realtime access to your database from anywhere and lets you control the database and the underlying server. PocketAdmin for Windows lets you securely manage objects in Active Directory (AD) in realtime. You can manage users, groups, printers, shares, and Microsoft Exchange 2003 and 2000. For PocketAdmin or PocketDBA pricing, contact Expand Beyond at 800-404-4059 or sales@xb.com.
        http://www.xb.com

  • GET DYNAMIC VIEWS OF YOUR BUSINESS INFORMATION

  • Panorama Software announced that it will integrate its analytics capabilities into Microsoft SharePoint Portal Server to provide users with dynamic views of business information. Panorama will integrate its business intelligence (BI) capabilities into Microsoft Excel, SharePoint, and MapPoint. The Panorama NovaView BI platform includes NovaView Web components that you can embed directly into SharePoint Portal Server to provide seamless connectivity to information in SQL Server 2000 Analysis Services. Contact Panorama Software at 905-709-5848 or 877-709-5848.
        http://www.panoramasoftware.com

    7. CONTACT US


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

    • ABOUT THE COMMENTARY — brianm@sqlmag.com
    • ABOUT THE NEWSLETTER IN GENERAL — kathy@sqlmag.com
      (please mention the newsletter name in the subject line)
    • TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
    • PRODUCT NEWS — products@sqlmag.com
    • QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
      Customer Support — sqlupdate@sqlmag.com
    • WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
      More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at bstonebanks@sqlmag.com or 800-719-8718.

    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

    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