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

2. SQL SERVER NEWS AND VIEWS

3. READER CHALLENGE

4. ANNOUNCEMENTS

5. RESOURCES

6. HOT RELEASE (ADVERTISEMENT)

7. NEW AND IMPROVED

8. CONTACT US

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

1. COMMENTARY

(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

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

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

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):

Sponsored by Lumigent

Understand and control the integrity of your data assets across the enterprise.
http://lists.sqlmag.com/cgi-bin3/DM/y/ec7i0FgQMn0BRZ0BC4b0Am

The next Instant Poll question is "Are you using Visual Studio .NET?" Go to the SQL Server Magazine Web site and vote for 1) Yes, I've used it for a while now, 2) Yes, I've just started using it, 3) No, but I plan to start within the next 6 months, 4) No, but I plan to start within the next 12 months, or 5) No, and I don't plan to.
http://lists.sqlmag.com/cgi-bin3/DM/y/ec7i0FgQMn0BRZ0KQO0Ay

SPONSOR: UNISYS HELPED JETBLUE DO SOMETHING UNIQUE

Unisys helped JetBlue do something unique with their data: treat customers like people. Unisys worked with JetBlue Airways to extend its standardization on Windows to the enterprise level. Unisys brings reliability and control with our ES7000 enterprise server that scales to 32 Intel Xeon processors for massive power and efficiency. And the money JetBlue saves managing its data center, it puts back into keeping fares low and customer satisfaction high. Unisys. Imagine it. Done.
http://lists.sqlmag.com/cgi-bin3/DM/y/ec7i0FgQMn0BRZ0BChl0Ao

3. READER 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:

4. ANNOUNCEMENT

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

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

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

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

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

(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 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])

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.

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:

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