SQL Server Magazine UPDATE, May 9, 2002

CHECK OUT THE SQL SERVER PLAYBACK PROGRAM

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

Microsoft announced a new SQL Server quality assurance program last year called the SQL Server Playback Program, which I wrote about in my SQL Server Magazine UPDATE article "Participate in the SQL Server Playback Program!" (which is available online at the URL at the end of this paragraph). Microsoft is always looking for customers with interesting application workloads that fit the program criteria, and the company is now actively seeking a new round of SQL Server Playback customers to get ready for the Yukon rollout in 2003. So, I'd like to revisit the unique opportunity this program presents for the right SQL Server customers.http://www.sqlmag.com/articles/index.cfm?articleid=21372

What will the SQL Server Playback Program do for you? Participation in the program:

Interested? If so, keep reading!

You've probably used SQL Server Profiler to examine the commands your SQL Server is processing. However, you might not realize that Profiler also provides a foundation for "playing back" a trace file later. Profiler lets you create repeatable workloads that you can run again and again, tweaking certain configuration parameters or indexes as you go. Microsoft uses this capability to create robust test suites based on live customer applications. SQL Server 2000 is very reliable, but the SQL Server test team is always looking for ways to push SQL Server to the next level, and the best way to do that is to benchmark the performance and behavior of real applications.

Microsoft needs your help to find the busiest servers, the largest databases, and the most complex queries the team can use to stress SQL Server to the limit. In the Microsoft labs, the team will replay these workloads on future versions of SQL Server to stress the server while applying concurrent workloads. The team will look for reliability and performance problems. If Microsoft accepts your workload for testing, you'll have extra assurance that your database code will work and perform marvelously with the next SQL Server release.

Microsoft will require a few commitments from you if you participate in the Playback Program. You must provide a backup and Profiler trace of your production database and workload. Microsoft will provide nondisclosure agreements (NDAs) to secure your data for use only in this program. The engineers who manage this program have told me that the biggest hurdle some customers have to cross before deciding to join the program is allaying their own concerns about how well Microsoft will guard their data and application code. Speaking about security practices in the SQL Server Playback Program, a Microsoft representative said, "Security is naturally of utmost importance. Rest assured that customer databases will be used only by the SQL [Server] team for testing purposes and will not be distributed to anyone else whatsoever, for any purpose. All playbacks are held on a secure storage server where only the SQL [Server] team has access. Furthermore, all media (tapes, CDs, etc.) are stored in a locked cabinet room. We can also provide two-way signed nondisclosure agreements upon request, if it eases your concern."

What types of workloads will Microsoft select? All Playback Program submissions must be from SQL Server 2000 on production servers. The team is looking for Profiler traces that include some of the following characteristics (but your application doesn't have to include all these items):

If you want to participate in the SQL Server Playback Program, email your company name, company contact, email address, and phone number to [email protected]. Microsoft will review your application and respond if you qualify. Also include

Microsoft will accept submissions for the current round of SQL Server Playback testing until August 31. In addition to the technical benefits you'll get if you participate, you'll receive a cool SQL Server polo shirt, and a Microsoft representative told me that workloads that "help us find issues" will earn "a little something as a reward."

SPONSOR: SQL SERVER MAGAZINE - NO RISK OFFER!

Hands-on, how-to articles covering Database Modeling, ADO.NET, XML, Performance Turning, Security and all of the issues database developers and administrations need to know to manage SQL Server. Subscribe to SQL Server Magazine with this NO RISK offer and, if for any reason, you are not satisfied with your first issue, just write cancel on the invoice and return it to us. Keep the first issue with our compliments - no questions asked. Subscribe today at:
http://lists.sqlmag.com/cgi-bin3/flo?y=eLtF0COfyP0BRZ01qi0Ah

2. SQL SERVER NEWS AND VIEWS

MAGAZINES NAME TECHNICAL EXCELLENCE AWARD WINNERS

The Unisys ES7000 server and ProClarity Analytics Platform 4.0 each won a Technical Excellence Award at the SQL Server Magazine LIVE! and Windows & .NET Magazine LIVE! event this week in Palm Springs, California.

Michael Otey, senior technical editor for SQL Server Magazine and Windows & .NET Magazine, said that all the products presented at the event expo were excellent in their fields. "The final winners were chosen based on the products' strategic importance to the market, the competitive advantages, and the competitive features as compared to other products in the market," he said.

Otey said that the Unisys ES7000 server is unique because of the hardware platform's ability to scale up. "The ES7000 allows scaling up to 32 processors, whereas the nearest competitor scales to only eight processors," he said. "It is the only platform that can work with Microsoft Datacenter, and it allows servers to be partitioned to work with multiple OSs."

The ProClarity Analytics Platform benefits the enterprise by enhancing SQL Server Analysis Services through a reporting capability that increases understanding and awareness of internal business practices, according to Otey.

"Analytics Platform 4.0 provides the ability to scale up throughout the enterprise and is flexible enough to be used by everyone from a power user to the occasional administrator," he said.

RESULTS OF PREVIOUS INSTANT POLL: TIME SPENT CODING

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "How much time per day do you spend coding?" Here are the results (+/- 1 percent) from the 637 votes:

NEW INSTANT POLL: BETA PROGRAM PARTICIPATION

The next Instant Poll question is, "Has your company participated in Microsoft SQL Server beta programs?" Go to the SQL Server Magazine Web site and submit your vote for 1) Yes, several times, 2) Yes, but only once or twice, 3) No, but we'd like to, or 4) No, and we're not interested.
http://www.sqlmag.com

SPONSOR: NEED 24 X 7 AVAILABILITY?

High-availability networks, systems, and applications are critical to every business. Sign up for our (free!) Webinar taking place on May 14 (sponsored by MKS), and find out how to achieve 24 x 7 availability on Windows 2000. Windows & .NET Magazine author Tim Huckaby shares his expertise on load balancing, monitoring, and more. Register today!
http://lists.sqlmag.com/cgi-bin3/flo?y=eLtF0COfyP0BRZ0qQh0AE

3. READER CHALLENGE

MAY READER CHALLENGE WINNERS AND JUNE CHALLENGE

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

Congratulations to Peter Lin, software engineer at Energistic in Port Klang, Malaysia, and Walid Rifai, software developer for Engineering Design and Manufacturing in Beirut, Lebanon. Peter won first prize of $100 for the best solution to the May Reader Challenge, "Simplifying the Search." Walid won second prize of $50. You can find a recap of the problem and the solution to the May Reader Challenge on the SQL Server Magazine Web site.
http://www.sqlmag.com/articles/index.cfm?articleid=25147

Now, test your SQL Server savvy in the June Reader Challenge, "Passing the Values" (below). Submit your solution in an email message to [email protected] by May 15. 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: As the database architect for a company that uses SQL Server 2000, Michael has been assigned the task of designing routines for auditing changes that the company's Web application makes to the data. He decides to use triggers to implement the auditing routines and to use stored procedures for performing all data-manipulation operations in the application. Let's simulate Robert's scenario by using the following four CREATE statements to create two sample tables with relevant columns, a trigger, and a stored procedure:

CREATE TABLE Test ( i int primary key )CREATE TABLE TestAudit ( i int, Source varchar(10) )GOCREATE TRIGGER AuditTestInsertON TestAFTER INSERTASBEGIN   DECLARE @CallingProc varchar(128)   — Robert needs to initialize this variable      DECLARE @Source varchar(30)   — Robert needs to initialize this variableINSERT INTO TestAudit ( i )   SELECT i from insertedENDGO

CREATE PROCEDURE InsertTest ( @Source varchar(30), @i int )ASINSERT INTO test values( 1 )GO

For a specific business purpose, Michael wants to learn two kinds of information: from within the trigger code, he wants to find out the name of the stored procedure that fires the INSERT trigger, and from the audit trigger, he wants to discover the value of the @Source parameter that the application passes to the stored procedure each time the trigger fires. Michael expects that after the Web application calls the stored procedure with the following parameters:

EXEC InsertTest 'App1', 1

the audit table will contain the newly inserted row with its App1 source value. However, the row appears in the audit table without the information he needs, so Michael has to modify the code—preferably with minimal changes—so that it populates all the necessary columns. Help Michael design an efficient solution for passing values from the stored procedure to the trigger.

4. ANNOUNCEMENTS

IMMEDIATE ACCESS TO T-SQL SOLUTIONS!

Exclusive in-depth articles, tips, tricks, and code samples all at your fingertips. Content you can't get anywhere else—brought to you by the SQL Server experts you trust such as Kalen Delaney, Itzik Ben-Gan, and others. Increase your productivity today! Go to the following URL.
http://lists.sqlmag.com/cgi-bin3/flo?y=eLtF0COfyP0BRZ0Kqz0AQ

VENDORS TO UNVEIL NEW SOLUTIONS AT XML EUROPE 2002

More than 40 solution providers will showcase their latest innovations this month at XML Europe 2002, May 20 through 23, 2002, in Barcelona, Spain. See the latest advances in XML products and services through hands-on demonstrations and displays at the Expo, or take advantage of 3 multi-track days of presentations from experts in the XML field. For complete details and to register, call 703-837-1070 or visit
http://lists.sqlmag.com/cgi-bin3/flo?y=eLtF0COfyP0BRZ0zNL0Aq

5. HOT RELEASE (ADVERTISEMENT)

RICH, INTERACTIVE WEB-BASED REPORTING!

IntelliVIEW is an elegant, XML-based Reporting Solution to query, view and publish SQL Server data. IntelliVIEW is lightweight and scalable and with its incredible price-performance, offers an ROI 180% greater than our nearest competitor (Crystal Reports/Brio/BO etc.)

Download the free IntelliVIEW client now!
http://lists.sqlmag.com/cgi-bin3/flo?y=eLtF0COfyP0BRZ0qM80AM

6. RESOURCES

WHAT'S NEW IN SQL SERVER MAGAZINE: IN THE PICTURE

Sharpen your T-SQL skills by learning how to use T-SQL to create an abstract drawing. Itzik Ben-Gan demonstrates this mind bender in "In the Picture," which appears in the May 2002 issue of SQL Server Magazine and is available online at the following URL:
http://www.sqlmag.com/articles/index.cfm?articleid=24425

HOT THREAD: LINKED SERVER PROBLEM

Tracy is trying to establish connections between one local server and three remote servers, but after she sets up the linked servers, she's unable to query the other servers through one of the remote servers. Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/Forums/messageview.cfm?catid=3&threadid=6040

TIP: UNDERSTANDING CODE PAGES

(contributed by the Microsoft SQL Server development team)

Q. I've noticed that the SQL Server 7.0 code page for a default installation is SQL_Latin1_General_CP1_CI_AS, but SQL Server 2000's default code page is Latin1_General_CI_AS. This change makes a significant difference when I need to restore SQL Server 7.0 databases into SQL Server 2000. How can I get around this change?

A. SQL Server is moving away from its legacy self-defined code page support and toward code pages that match the Windows code pages that you choose when you set Windows locale IDs. For more information about code pages, see "How Character Data Is Stored" in SQL Server 2000 Books Online (BOL). When you upgrade from SQL Server 7.0 to SQL Server 2000, by default you keep your existing sort order and code page. However, when you perform a clean installation of SQL Server 2000, you get the new sort order and code page. If you attach a database to a SQL Server 2000 instance, SQL Server keeps whatever the collation was on the database when you created it (i.e., you keep the SQL Server 7.0 code page if you attach a SQL Server 7.0 database to SQL Server 2000). Because SQL Server is moving toward the new default code page, we recommend that if you're upgrading by doing a clean installation, then attaching a database, you need to pick a collation at SQL Server 2000 installation time that matches your SQL Server 7.0 collation. You can determine this collation by performing an upgrade (e.g., an upgrade of the Pubs database) from your SQL Server 7.0 installation, then looking at the collation that results when you query the serverproperty ('collation') builtin. Microsoft has made this change to reduce the scope of differing results that incompatible SQL Server and Windows code pages cause.

Send your technical questions to [email protected].

7. NEW AND IMPROVED

(contributed by Carolyn Mascarenas, [email protected])

AUTOMATICALLY GENERATE VB CODE

MIDAR announced the Multi-Tier Code Generator, software that examines your database and automatically creates three complete Visual Basic (VB) projects for presentation, business logic, and database tiers. The software automatically generates SQL Server scripts to interface with the VB tier. The generated VB code uses COM interfaces. The software supports SQL Server 2000 and 7.0 and costs $31 per month. Contact MIDAR at [email protected].
http://www.midar.com.au

FACILITATE NETWORK MANAGEMENT

IS Decisions released WinReporter, software that retrieves network data to facilitate network management. You can use WinReporter to find all servers that don't have the latest service pack installed, find old computers in the network, count installed software, detect unregistered software, get information about available disk space, list groups and users by domain or computer, and list RAM sizes. The software supports SQL Server, Microsoft Access, and Oracle databases. For pricing, contact IS Decisions at [email protected].
http://www.isdecisions.com

8. CONTACT US

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

(please mention the newsletter name in the subject line)

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

Thank you for reading SQL Server Magazine UPDATE.