SQL Server Magazine UPDATE, November 11, 2004--64-Bit Extravagance

SQL Server Magazine Web ExclusiveNovember 11, 2004SQL Server Magazine UPDATE, November 11, 2004-64-Bit ExtravaganceInstantDoc ID 44515by Various authors

This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for SQL Server Magazine UPDATE.

This Issue Sponsored By

Free White Paper on Data Recovery Best Practices
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrb0A5

Just Released—Altova MapForce 2005
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMkK0Aa

Microsoft Business Intelligence Webcast Series
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrc0A6

November 11, 2004

1. Perspectives

2. News and Views

3. Reader Challenge

4. Announcements

5. Peer to Peer

6. Events Central

7. New and Improved

Sponsor: Free White Paper on Data Recovery Best Practices
Planning for data recovery is about more than just making sure your database is backed up. There are many things to consider. In his paper, Stephen Wynkoop, MVP and Founder SSWUG, discusses the best approaches to a solid data-recovery solution. He outlines key planning points and investigates how select tools can help you accomplish a plan for success. Download it today, compliments of Lumigent Technologies:
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrb0A5

1. Perspectives

by Brian Moran, [email protected]

Is a 64-bit SQL Server in your future? Check out this advertisement for an upcoming Microsoft Webcast series about 64-bit computing:

"Did you know the majority of new server shipments today contain 64-bit processing power? The shift to mainstream 64-bit computing is underway, and Microsoft wants to help you gear up for this important milestone in our industry. 64-bit computing support is available today in our next wave of 64-bit products, including Windows Server 2003 x64, Windows XP Professional x64 Edition, SQL Server 2005, Visual Studio 2005, and the .NET Framework 2.0."

I was surprised that most new server shipments contain 64-bit processing power because that's not my experience in the SQL Server space. Some clarification from Microsoft helped me understand that this statement in based on the fact that AMD's newest line of 64-bit processors provide the ability to natively run both 32-bit and 64-bit versions of Windows and other OSs. So, many server shipments might have 64-bit technology, but that doesn't mean all those servers are running a 64-bit OS. You can find information about the 64-bit Webcast series at http://www.microsoft.com/seminar/events/series/msdn64bitwin.mspx. Most of the live Webcasts will be over by the time you read this because the series started November 1. However, you can view archived Webcasts.

I got to thinking a bit (pun intended ) about 64-bit computing and SQL Server. When SQL Server 2000 first provided 64-bit support, I was intrigued but not terribly excited. I figured that the primary reason for running 64-bit SQL Server would be to provide a power boost for large data warehouses that needed huge amounts of memory, or for big, high-end online transaction processing (OLTP) systems like the ones a NASDAQ or Barnes & Noble would use. Don't get me wrong; meeting those needs is incredibly important. But I didn't think the new technology would affect the low- to mid-range server space. Since then, I've come to realize that 64-bit SQL Server can provide surprising performance benefits for high-end OLTP systems that have lots of stored procedures because 32-bit SQL Server's artificial limitations on the procedure cache's size can constrain mid-range OLTP systems—even those that have large amounts of memory.

I think 64-bit computing provides a compelling argument to consider buying AMD processors today, even if you don't plan on shifting to 64-bit computing for a year or more. In the past, SQL Server customers haven't given that much consideration to AMD-based boxes. There simply wasn't a compelling enough argument to leave the "safe" Intel route. However, AMD now offers the Opteron line of processors, which can run native 32-bit and 64-bit systems. These processors are priced considerably less than Itanium-based systems. SQL Server 2000 64-bit doesn't run on the 64-bit native mode of the Opteron, but 64-bit SQL Server 2005 will run on Opteron servers you can buy today. SQL Server 2000 32-bit will run on those same servers.

I was speaking to a reader at SQL Server Magazine Connections in Las Vegas this week. The reader's company is new servers in the next few weeks, and they know they won't be able to replace them for at least 3 years. After all, not everyone has the budgets to buy new servers every 6 months. They're taking a hard look at 4-CPU Opteron boxes from Hewlett-Packard because this solution will give them a cost-effective way to run 32-bit today and 64-bit next year on the same hardware. Are you buying a server today but don't need 64-bit or the extra memory right now? Perhaps there's value in considering an AMD server. Your new server will run your 32-bit applications today, and next year, when SQL Server 2005 ships, you can simply install a new OS and SQL Server 2005 on your existing hardware. Intel doesn't offer that option today.

I remember years ago when servers with 4 CPUs and 4GB of memory seemed extravagant. Today, I tell most of my mid-range customers that doing the performance study to see whether you can get by with a less expensive server will often cost more than simply going with a 4X 4GB box in the first place. 64-bit technology seems extravagant today, but I suspect that at some point in the not-so-distant future we'll reach the tipping point when most new servers will run 64-bit platforms.

Just Released—Altova MapForce 2005
MapForce 2005 is the definitive tool for data integration and information leverage. Map any combination of XML, DB, flat file, and EDI data into XML, DBs and/or flat files. Connect to a DB & import/export data based on DB schemas. Generate code in XSLT 1.0/2.0, XQuery, Java, C++, and C#. Give your data direction...Download a FREE trial now!
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMkK0Aa

2. News & Views

Arindam Sen, a DBA with American Power Conversion in West Kingston, Rhode Island, won top honors in the 2004 SQL Server Magazine Innovator Awards, presented Monday at the SQL Server Magazine Connections Conference in Las Vegas. The awards program, sponsored by Idera, also recognized three runners-up and eight honorable mentions. Serving as judges for the awards were SQL Server Magazine contributing editors Douglas McDowell of Intellinet, Brian Moran of Solid Quality Learning, and Steven Wynkoop of the SQL Server Worldwide User Group (SSWUG).

"Three years ago, SQL Server Magazine launched the Innovator Awards to recognize the most creative and effective uses of SQL Server technology to solve business problems—and to share those solutions with the community," noted SQL Server Magazine Editor Kathy Blomstrom. "This year, we received perhaps the highest quality of entries ever. We are inspired by the work you're doing to make IT a vital part of your organizations' success."

Sen, as the grand-prize winner, received a trophy and an all-expenses-paid trip to the conference for his high-availability monitoring, alerting, and reporting system. He increased systems availability, boosted revenue, improved the end-user experience, satisfied Sarbanes-Oxley auditors, and polished the image of the SQL Server/Windows platform with a centralized monitoring/alerting tool that monitors all tiers of the Web and CRM infrastructure-including hardware load balancers, Web servers, the OS, and the database server. Judges commended the solution—which used T-SQL, Visual Studio .NET, Data Transformation Services (DTS), and Reporting Services-as significant and creative.

Michael Rhodes, senior technical architect with Teksouth in Gardendale, Alabama, was named first runner-up for an advanced military decision-support system (DSS). The Air Force DSS system evaluates, scrubs, relates, and publishes data in near realtime 24x7 from 19 different systems and 450 locations worldwide-publishing gigabytes of data per day, making terabytes of data available for ad hoc queries, and returning results in seconds. Judges noted the solution's creative use of SQL Server 2000 scale-out technology and distributed queries and the huge impact on users, calling the solution "innovative and technically challenging."

Zewei Song, application developer with World Publishing Company in Tulsa, Oklahoma, and formerly with e-Money Systems, was second runner-up, winning for a seamless database server-side encryption solution. Song used .NET and T-SQL extended stored procedures to build an encryption system that strengthens customer privacy and data protection with minimum performance impact.

Mike Smith, data warehouse developer with Cendian Corporation in Atlanta, took third runner-up honors, winning with a high-performance extraction, transformation, and loading (ETL) summary solution that cut business intelligence (BI) table-load times in half, slashed table-reload times from hours to minutes, and used prebuilt results to draw more users to the BI system.

All winners received a discount to the SQL Server Magazine Connections Conference and a 1-year subscription to SQL Server Magazine.

"What's the employment outlook for IT pros at your company in the coming year?" Here are the results from the 115 votes (deviations from 100 are due to a rounding error):

"Did you attend SQL Server Magazine's Connections conference in Las Vegas?" Go to the SQL Server Magazine home page ( http://www.sqlmag.com ) and submit your vote for

3. Reader Challenge

by Umachandar Jayachandran, [email protected]

Congratulations to Nonoy Casin and Niko Vrdoljak, a senior developer for Teched d.o.o. in Zagreb, Croatia. Nonoy won first prize of $100 for the best solution to the November Reader Challenge, "Preventing Deletes." Niko won second prize of $50. You can read a recap of the problem and the solution to the November Reader Challenge at
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrd0A7

Now, test your SQL Server savvy in the December Reader Challenge, "A Dashboard Application" (below). Submit your solution in an email message to [email protected] by November 18. 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: Terry, a systems analyst for the IT department, is developing a Web-based management dashboard. The data for the dashboard is stored in a SQL Server 2000 database. As part of one administrative function in the dashboard, the application requires the database to store user-defined attributes. The following code shows the table's schema, including the relevant columns to store the attribute values for various objects in the system.

  CREATE TABLE AttribVals (      Id int NOT NULL,      Val varchar(30) NOT NULL,      PRIMARY KEY( Id, Val )   )

The column Id in the AttribVals table represents generic objects in the dashboard application. The following script creates sample data for the table.

 INSERT INTO AttribVals (Id, Val)   VALUES ( 1, 'A' )   INSERT INTO  AttribVals (Id, Val)   VALUES ( 1, 'B' )   INSERT INTO  AttribVals (Id, Val)   VALUES ( 1, 'C' )   INSERT INTO  AttribVals (Id, Val)   VALUES ( 2, 'B' )   INSERT INTO  AttribVals (Id, Val)   VALUES ( 3, 'A' )   INSERT INTO  AttribVals (Id, Val)   VALUES ( 3, 'C' )

Terry is designing a stored procedure that the Web page will call to manage the user-defined attributes. The Web page needs to pass a list of attributes and get the corresponding objects' Id value. Help Terry define the stored procedure with the required parameters. The stored procedure has to handle any number of attributes. For simplicity, assume that all values will be less than 8000. The stored procedure has to retrieve the Id only if all the specified attributes are configured for the Id value. If you use the sample data in the previous script, specifying A and C as attribute values should return Ids 1 and 3; specifying B should return Ids 1 and 2.

    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrd0A7

4. Announcements

Windows & .NET Magazine is now Windows IT Pro! Act now to get the November issue, which features a Linux primer for Windows administrators, the how-tos of making NTBackup work, and a checklist for Sarbanes-Oxley compliance. You'll save 30% off the cover price and receive exclusive subscriber-only access to our entire online library with your paid subscription! This is a limited-time offer, so click here to order today!
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMdM0AV

Hundreds of free tips and articles on SQL Server performance tuning and clustering. And get quick and accurate answers to your performance- and cluster-related question in our forum. All from the SQL Server performance authority: SQL-Server-Performance.Com.     http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BKEZ0AB

5. Peer to Peer

by Microsoft's SQL Server Development Team, [email protected]

Q. How can I find out when a database has auto-grown? I'd like to know how often a certain database has automatically grown without any DBA intervention and, if possible, which files have auto-grown and to what size. With this information, I could more easily decide whether to let a database auto-grow and how much to let it grow.

Read the answer to this question today at
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMre0A8

A data warehouse contains data that has been aggregated for data analysis. You aggregate measures (such as quantities and amounts) for different entities (such as employees, customers, and products). You also determine the time units (e.g., day, month, year) for the aggregations according to your analysis needs. To allow fast query response, you might keep multiple summary tables, each containing a different time unit for the aggregations—for example, a table for daily aggregations, another for monthly aggregations, and a third for yearly aggregations. In his December T-SQL Black Belt column, "Accumulating Aggregations," Itzik Ben-Gan uses five examples to show you how to query aggregated data over time. Read this article today at
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrf0AA

    http://www.windowsitpro.com/sqlserver/forums/

Replacing a Cursor With a While Loop
http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrg0AB
How Do I Import a Dbase 5 File Into SQL Server 2000?
http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrh0AC
SQL Server Agent Skipping Jobs
http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMri0AD
Testing for Fractional Values in IN Parameter
http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrj0AEBest Practice for Building an Audit Trail into Tables?
http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrk0AF
Permissions for Viewing Reporting Services Reports
http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrl0AG

Hot Spot

IT Professionals, access and share data in real time. Help your company make faster, smarter, more informed decisions. Microsoft provides enterprises with powerful, cost effective business intelligence and performance management solutions for enterprise data integration, analytics, and reporting.
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrc0A6

6. Events Central

SQL Server Summit in Dallas, TX—January 18, 2005
This year's summit will focus on exciting new features of the next release—SQL Server 2005. Summit features four tracks: Business Solutions, DBA, Developer-DBA, and Business Intelligence. Keynote delivered by Bill Baker, Microsoft General Manager of SQL. Call 1-877-MSEVENT or register online (reference event code: 1032256747) at:
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BMrm0AH

See the complete Windows IT Pro Network guide to Web and live events
    http://www.winnetmag.com/events/

7. New & Improved

by Dawn Cyr, [email protected]

ApexSQL announced ApexSQL Doc 1.15, a SQL Server documentation tool that lets you create HTML Help-formatted documentation of every aspect of your SQL Server database. The product's wizard-based interface lets you automatically document a database in minutes. You can immediately publish the documentation for use in your organization and easily generate updates. The product also features a command-line interface that further automates the documentation process. ApexSQL Doc works with SQL Server 2000 and 7.0 and will be available for SQL Server 2005. A free, 14-day trial download is available at the ApexSQL Web site. Pricing for single-user licenses starts at $199, and ApexSQL offers free support and free product upgrades. For more information or other pricing options, contact ApexSQL at 866-665-5500 or [email protected].
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BGQt0Aj

JNetDirect announced JSecureConnect 2.0, secure Java component driver technology that provides secure and firewall tunneling Java Database Connectivity (JDBC) to SQL Server, Microsoft Access, Oracle, IBM DB2, and MySQL databases. The product provides secure JDBC access over SSL and HTTPS protocols and firewall tunneling through HTTP and HTTPS protocols. For the latest release, JNetDirect has greatly optimized the product's code to reduce performance costs and provide HTTP and secure tunneling that's significantly faster than in previous releases. Pricing for JSecureConnect starts at $225 for a single-user, single-connection license. For more information, contact JNetDirect at 703-880-3800 or 800-995-8534, or send an email message to [email protected] (existing customers) or [email protected] (new customers).
    http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BF5M0Ab

Panorama Software announced Panorama Enterprise Reporter, a reporting solution that integrates ad hoc query capabilities, analysis, and report authoring. The product gives end users the ability to create enterprise reports such as invoices, production reports, and financial statements from any Web browser. The company also announced the latest release of Panorama Performance Dashboard, which includes new tools to help end users communicate and collaborate better. The Web-based dashboard is personalized and automates the delivery of crucial business information from multiple data sources to any user inside or outside the corporate firewall. The integration of reporting, analysis, and key performance indicators (KPIs) in one interface makes it easy for users to move from one application to another. The product supports a variety of user-authentication and authorization protocols, including Anonymous Authentication for extranet deployments to partners, suppliers, and customers, and works with various data sources, including SQL Server, Oracle, IBM DB2, and Teradata. For pricing and other information, contact Panorama at 877-709-5848 or [email protected].     http://list.windowsitpro.com/cgi-bin3/DM/y/eiHR0McMHC0Kj40BHlJ0AV

Contact Us

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.
https://secure.pentontech.com/nt/sql/index.cfm?promocode=00

Manage Your Account You are subscribed as xxxxx. To unsubscribe from this email newsletter, click here mailto:[email protected]

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://email.winnetmag.com/winnetmag/winnetmag_prefctr.asp

View the SQL Server Magazine Privacy Policy.
http://www.winnetmag.com/aboutus/index.cfm?action=privacy

SQL Server Magazine is a division of Penton Media, Inc., 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

Copyright 2004, Penton Media, Inc. All Rights Reserved.