SQL Server Magazine UPDATE, October 9, 2003

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

THIS ISSUE SPONSORED BY

See How eWeek Labs Rates EMC ControlCenter 5.1.1

Do You Work with Different Database Platforms?
(below COMMENTARY)

Unisys Helped JetBlue Do Something Unique
(below NEWS AND VIEWS)

SPONSOR: SEE HOW EWEEK LABS RATES EMC CONTROLCENTER 5.1.1

Explore key performance features and capabilities of EMC ControlCenter 5.1.1—a family of storage management software—in this review from eWeek Labs. The article provides analysis of Automated Resource Manager, which automatically provisions storage based on user-set policies, and StorageScope, which assists in tracking usage trends to support future storage hardware acquisitions. Find out how these and other ControlCenter products make managing an ever-expanding storage infrastructure easier and more efficient. Click here for eWeek Lab's review.

October 9, 2003—In this issue:

1. COMMENTARY

Beyond SQL Server

2. SQL SERVER NEWS AND VIEWS

Avoiding "Catastrophic Failure" Message
Denial of Service in IBM DB2 Universal Database
Results of Previous Instant Poll: Index Tuning Wizard
New Instant Poll: Visual Studio .NET

3. READER CHALLENGE

October Reader Challenge Winners and November Challenge

4. ANNOUNCEMENTS

Need to Find the Best SQL Server Tips?
PDC 2003: The Buzz Is On

5. RESOURCES

What's New in SQL Server Magazine: Simply Keeping Time
Hot Thread: Tricky SQL Maneuver
Tip: Deleting Redundant Statistics

6. HOT RELEASE (ADVERTISEMENT)

SQL Server Magazine Connections: Last Week to Register

7. NEW AND IMPROVED

Audit Database Activity
Compare and Synchronize Databases

8. CONTACT US

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

1. COMMENTARY

BEYOND SQL SERVER

(contributed by Brian Moran, news editor, [email protected])

A long time ago, in a galaxy far, far away, DBAs paid attention to SQL Server and ignored other products that Microsoft released. That luxury has been disappearing as products become more complex and interdependent. And the luxury of isolation will become even more a thing of the past over the next year. Later this month, at its Professional Developers Conference (PDC), Microsoft will publicly discuss several new technologies that will affect SQL Server professionals. This week, I give you an overview of these upcoming technologies and their importance to the SQL Server community. And over the coming months, I'll discuss each technology in detail.

SQL Server Yukon

The most important new product for SQL Server professionals is the next release of SQL Server, code-named Yukon. Yukon will feature .NET Common Language Runtime (CLR) integration, T-SQL enhancements, administration and self-tuning improvements, extended business intelligence (BI) functionality, and much more. This release of SQL Server will also come with the new Reporting Services built into it, even though Microsoft is releasing the first version of Reporting Services as a SQL Server 2000 add-in. Microsoft says the Yukon release, which is currently in private beta, will ship in the second half of 2004. Microsoft held an invitation-only Yukon Tech Preview in February, but PDC, which begins October 26, will be the first time that the company will discuss Yukon publicly. Microsoft expects to release another beta edition of Yukon around the same time as PDC.

Windows Longhorn

Longhorn, the code name for Windows' upcoming release, will be a landmark OS because of a new technology called Windows Future Storage (WinFS). DBAs must be skilled with the OS, especially considering the myriad security problems that exist today. WinFS will embody concepts that have been floating around Microsoft for more than a decade. Remember Microsoft saying that the Cairo Windows NT release would include a sophisticated object-oriented file system? Well, the company never delivered the Cairo object-oriented file system, but WinFS is expected to live up to many of Cairo's design ideals. WinFS will use key database technologies from Yukon to provide a rich searching mechanism at the OS file level. The integration of WinFS and core Yukon technologies will let you store BLOB data types more efficiently and will make Yukon a more effective full-text search platform. If WinFS lives up to expectations, it will have a profound effect on the line defining database and OS administrator tasks.

Visual Studio .NET Whidbey

Whidbey is the code name for the upcoming release of Visual Studio .NET and the CLR. Developer-oriented technologies such as Visual Studio .NET and the CLR will become increasingly important to all SQL Server professionals as Yukon provides CLR integration, letting you build stored procedures and other database objects in any .NET-compliant language. In addition, Microsoft has dramatically redesigned SQL Server's toolsets in Yukon. I can't go into details yet, but we'll be saying goodbye to the Microsoft Management Console (MMC)-based SQL Server administration tools and welcoming a new suite of management and SQL Server development tools hosted within a Visual Studio-esque IDE. DBAs will need to be conversant in Visual Studio .NET, which will represent a significant change for some folks but which will also provide productivity enhancements and more features, such as Visual SourceSafe (VSS) versioning of database source code.

I'll address each of these technologies in more detail and from a uniquely SQL Server perspective during the coming months. Plus, watch SQL Server Magazine UPDATE for key SQL Server-related announcements from PDC.

DO YOU WORK WITH DIFFERENT DATABASE PLATFORMS?

Research shows that 49 percent of SQL Server Magazine readers work in multi-database environments. If this describes you, take an hour out of your day and learn how to replicate and integrate data in real-time between SQL Server and Oracle, DB2, or Sybase. In conjunction with SQL Server Magazine University, Herman Haljaard, Solutions Manager at Vision Solutions, will examine database integration and replication needs in a Web cast on Thursday, October 16, at 1:00pm EST. Registration is free; however, space is very limited and seats are filling up quickly. Click here to reserve your seat TODAY.

2. SQL SERVER NEWS AND VIEWS

AVOIDING "CATASTROPHIC FAILURE" MESSAGE

Because of a problem that affects Microsoft Data Access Components (MDAC) 2.6 and all editions of SQL Server 2000, if you use the adPromptAlways constant to open a trusted connection to SQL Server 2000, the user receives the following error message:

'Run time error '-2147418113 (8000ffff)': Catastrophic Failure'.

In Microsoft article "FIX: 'Catastrophic Failure' Error Message When You Use adPromptAlways to Connect to SQL Server 2000," the company says to not use adPromptAlways, and to pass the information in the connection string instead. This problem doesn't occur with SQL Server 7.0 and is corrected in MDAC 2.6 Service Pack 2 (SP2) and MDAC 2.7.
http://support.microsoft.com/default.aspx?scid=kb;en-us;314635&Product=sql

DENIAL OF SERVICE IN IBM DB2 UNIVERSAL DATABASE

Aaron Newman of Application Security discovered that a Denial of Service (DoS) condition exists in IBM DB2 Universal Database. IBM DB2's UDP-based discovery service, listening on port 523, shut downs when it receives more than 20 bytes of data. After the discovery service crashes, the service requires a restart. IBM has released FixPak 10a to address this vulnerability.
http://secadministrator.com/articles/index.cfm?articleid=40431

RESULTS OF PREVIOUS INSTANT POLL: INDEX TUNING WIZARD

The voting has closed in SQL Server Magazine's Instant Poll for the question, "Do you find the Index Tuning Wizard reliable for finding all the best indexes for your system?" Here are the results (+/- 1 percent) from the 296 votes (deviations from 100 percent are due to a rounding error):

6% Yes, I trust it completely
57% No, but it's still helpful
15% No, I don't find it helpful at all
10% I haven't used the wizard but plan to
13% I haven't used the wizard and don't plan to

NEW INSTANT POLL: VISUAL STUDIO .NET

3. READER CHALLENGE

OCTOBER READER CHALLENGE WINNERS AND NOVEMBER CHALLENGE

(contributed by SQL Server MVP Umachandar Jayachandran, [email protected])

Congratulations to Vadim Rapp, owner of Vadim Rapp Consulting in Part Ridge, Illinois, and Chris Hedgate, a senior DBA for Extralives in Malmo, Sweden. Vadim won first prize of $100 for the best solution to the October Reader Challenge, "A New View." Chris won second prize of $50. You can find a recap of the problem and the solution to the October Reader Challenge at
http://www.sqlmag.com/articles/index.cfm?articleid=40472

Now, test your SQL Server savvy in the November Reader Challenge, "To Manipulate Tables" (below). Submit your solution in an email message to [email protected] by October 16. SQL Server MVP 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: Peter is the database architect for a company that builds accounting applications using SQL Server 2000 and 7.0. The applications have two databases, APP and ACCT, that reside on different SQL Servers or SQL Server instances. The application code connects only to the APP database; Peter accesses the ACCT database primarily through distributed queries from stored procedures. Peter knows the ACCT database server's name only at setup or installation time, so when he initially implemented the code, he used dynamic T-SQL and distributed queries as the following example shows:

-- Get name of ACCT database server from configuration table:
   SET @AcctSrvr = '\[Name_Of_Srvr_containing_ACCT_DB\]'
   EXEC('SELECT * FROM OPENQUERY(' + @AcctSrvr + ', 
      "SELECT * FROM ACCT..users')')

During setup, Peter uses the system stored procedure sp_addlinkedserver to create and configure the linked server for the ACCT database based on user input. Peter finds that approach tedious and hard to maintain, especially for complex queries. He wants to create views on remote tables that he can easily manipulate. Help Peter do the following:

Eliminate the dependency on a linked server, keeping the same two-server approach.
Eliminate the need for dynamic SQL.
Use static T-SQL code to write distributed queries in stored procedures.

4. ANNOUNCEMENT

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

NEED TO FIND THE BEST SQL SERVER TIPS?

Visit the SQL Server Magazine Web site and take advantage of the search box and navigation toolbar to access new articles, active forums, archived articles, associated code, and more! The site features columns by such experts as Brian Moran and Itzik Ben-Gan. Click here to visit this helpful SQL Server resource:
http://lists.sqlmag.com/cgi-bin3/DM/y/ec7i0FgQMn0BRZ0KQO0Ay

PDC 2003: THE BUZZ IS ON

The Microsoft Professional Developers Conference will be in Los Angeles, CA, on October 26. With PDC 2003 less than a month away, and more content being added daily, developers everywhere are talking about what's in store. Register today and enter to win a Smartphone. Visit:
http://lists.sqlmag.com/cgi-bin3/DM/y/ec7i0FgQMn0BRZ0BCot0A4

5. RESOURCES

WHAT'S NEW IN SQL SERVER MAGAZINE: SIMPLY KEEPING TIME

In "Simply Keeping Time," Itzik Ben-Gan looks at reader solutions to two puzzles that involve tracking dates and times. First, he discusses a solution to the February 2003 "Counting the Costs" problem (InstantDoc ID 27592) sent in by Scott Smith, a principal architect at M Systems Group, Toronto. Then, he explains a solution to the January 2003 Working Days problem (from "No Table? No Problem," InstantDoc ID 27051) sent in by Sergiy Korovin of Q-Sign Pty, Greensborough, Australia. Smith's and Korovin's solutions have something in common: They both use auxiliary tables, which allow simple, efficient solutions. Read this October SQL Server Magazine article at
http://www.sqlmag.com/articles/index.cfm?articleid=39716

HOT THREAD: TRICKY SQL MANEUVER

In fastcorvette's table, Field2 can contain one of two values. Depending on the value in Field2, the value in Field3 needs to be placed in a different window in his program. Using an SQL statement, fastcorvette wants to add two "virtual" fields to the recordset that the database returns. The two new fields would contain the value from Field3 depending on what's in Field2. How would he structure an SQL statement to produce this recordset? Read more about this situation, and offer your suggestions, on SQL Server Magazine's Data Access forum at
http://www.sqlmag.com/forums/messageview.cfm?catid=8&threadid=19713

TIP: DELETING REDUNDANT STATISTICS

(contributed by Brian Moran, [email protected])

Q. In the Q&A "Real vs. Auto-Created Indexes" (May 2003, InstantDoc ID 38441), you discussed auto-created statistics and how these statistics often identify columns on which you should create a real index. I have several columns that have both auto-created statistics and real indexes on them. Why did this happen, and should I eliminate the redundant auto-created statistics?

A. By default, SQL Server automatically creates statistics on columns if the optimizer decides at runtime that the information will be helpful in processing the query more efficiently. However, SQL Server never drops an auto-created statistic, even when you create a real index on the column. Here's an example that illustrates this behavior. The query

   USE tempdb
   SELECT * INTO NewOrders
      FROM Northwind..orders

creates a table called NewOrders that you can use to test the interaction of statistics and indexes. Now run the following query, which makes SQL Server create a statistic on the OrderId column:

   SELECT * FROM NewOrders
      WHERE OrderId = 10248

By running the following query, you can verify that SQL Server created a new statistic called something like _WA_Sys_OrderID_49C3F6B7 (SQL Server dynamically generates the suffix 49C3F6B7, so the suffix will be different on your machine):

   SELECT * FROM sysindexes
      WHERE id = object_id('NewOrders')
      AND INDEXPROPERTY(id,name,'IsStatistics') = 1

Now, create an index on the OrderId column, run the query again, and check sysindexes' contents:

   CREATE UNIQUE INDEX uix_NewOrders_OrderId
      ON NewOrders(orderid)
   SELECT * FROM NewOrders
      WHERE OrderId = 10248
   SELECT * FROM sysindexes
      WHERE id = object_id('NewOrders')

The statistic still exists, even though you created a real index. This statistic is now redundant because SQL Server can retrieve its information from the real index on the same column. But because SQL Server doesn't check for redundant statistics, it won't automatically drop this statistic. However, you can use the DROP STATISTICS command to drop the statistic manually.

Maintaining statistics usually doesn't involve a significant performance overhead, and statistics don't take up much space. However, small inefficiencies can add up quickly when your tables get large, so I recommend that you delete unnecessary auto-created statistics when you create a real index on a column. SQL Server won't do it for you.

Send technical questions to [email protected].

6. HOT RELEASE (ADVERTISEMENT)

SQL SERVER MAGAZINE CONNECTIONS: LAST WEEK TO REGISTER

SQL Server Magazine Connections runs concurrently with Microsoft ASP.NET Connections, Visual Studio Connections, and Microsoft Office System Connections. Four conferences for the price of 1, plus you could win a Harley. Sign up now!
http://lists.sqlmag.com/cgi-bin3/DM/y/ec7i0FgQMn0BRZ0ggP0Ap

7. NEW AND IMPROVED

(contributed by Dawn Cyr, [email protected])

AUDIT DATABASE ACTIVITY

Lumigent Technologies announced Entegra 2.0, database-auditing software that lets you monitor who accesses and modifies your data and how. A significant enhancement in this latest release is the ability to create an audit trail of SELECT activity, which lets you see not only who modified data, but also who viewed data. Because Entegra doesn't use triggers to create audits, the software can audit a database without changing it. The audit trails and reports that the software creates satisfy security and privacy regulations and operational requirements for health care, finance, biotechnology, and other privacy-conscious industries. Entegra 2.0 supports SQL Server 2000 and 7.0. Pricing starts at $10,000. Contact Lumigent Technologies at 866-586-4436.

COMPARE AND SYNCHRONIZE DATABASES

Red Gate Software announced SQL Data Compare 3.0x, software for developers and DBAs who frequently move and verify data between SQL Server databases. The software's designers used Microsoft .NET to completely recode the latest release. Enhancements include a new UI; the ability to compare text, ntext, and image data; the ability to run SQL scripts from within SQL Data Compare; and reduced security limitations that let you compare data if you have SELECT permissions on tables. SQL Data Compare supports SQL Server 2000 and 7.0. For pricing, contact Red Gate Software at 866-733-4283 or [email protected].
http://lists.sqlmag.com/cgi-bin3/DM/y/ec7i0FgQMn0BRZ0BC4d0Ao

8. CONTACT US

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

ABOUT THE COMMENTARY — [email protected]
ABOUT THE NEWSLETTER IN GENERAL — [email protected]
(please mention the newsletter name in the subject line)

TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
PRODUCT NEWS — [email protected]
QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — [email protected]

WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
Contact Richard Resnick at [email protected] or 800-949-4007.

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

Comments

Plain text