SQL Server Magazine UPDATE, February 12, 2004--SQL Server Licensing

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

THIS ISSUE SPONSORED BY

Quest Software, Inc.

Amaze Your Co-workers with Your SQL Server Smarts
(Below COMMENTARY)

February 12, 2004—In this issue:

1. SQL SERVER PERSPECTIVES

2. SQL SERVER NEWS AND VIEWS

3. READER CHALLENGE

4. ANNOUNCEMENTS

5. RESOURCES

6. NEW AND IMPROVED

7. CONTACT US

Sponsor: Quest Software, Inc.

Is database contention affecting the performance of your SQL Server environment? Are you experiencing performance degradation in your SQL Server applications due to contention? With more concurrent users on the same system, you run the risk of being forced to wait for each other or reading inconsistent data due to in-progress changes. The trade-off between accuracy and throughput is critical; it can have a major impact on your SQL Server performance. Get a solid strategy to diagnose and correct SQL Server contention issues. Improve your overall system performance.
   Download the "SQL Server Contention: Diagnosing and Resolving Blocking Problems" white paper

1. SQL SERVER PERSPECTIVES

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

What does the number of clowns you can fit in a Volkswagen Beetle have to do with the SQL Server licensing model? Last week, I asserted that SQL Server 2000 Reporting Services will have a significant effect on all SQL Server shops, whether they deploy the product or not. I pointed out that someone might ask you to defend a decision to spend money on a third-party reporting solution that you could have gotten for free. In response, a SQL Server Magazine editor forwarded a reader's comment asking how Microsoft could call Reporting Services free when most customers can't install the product on current SQL Server systems because of performance restrictions. True, you must run Reporting Services on a machine that's already licensed for SQL Server, but we're all running SQL Server on dedicated machines, right? Here's where SQL Server's licensing model begins to resemble a VW Beetle full of circus clowns.

There's a Web site that claims 24 circus clowns can fit in a VW Beetle. If you assume that you can transport 24 circus clowns in one VW Beetle, then a circus with 12 clowns and a VW Beetle can hire 12 more clowns without increasing transportation costs, right? Maybe not. I'm trying to teach my little girl, Emily, that you don't always do something just because you can. The lesson applies to performance tuning and capacity planning as well. Many customers have no business installing Reporting Services on an existing SQL Server production server—even though Reporting Services would then be free—because it will degrade the server's performance. You can't always fit another clown in the car. For these customers, Reporting Services isn't free. However, many small and medium-sized businesses can efficiently run Reporting Services on an existing SQL Server box. The installation might slow performance some, but there's room to add one more clown to the car. For these customers, Reporting Services is free.

For those of you who believe it's naive to think that many customers have the hardware room to add Reporting Services to their existing SQL Server systems, let me tell you a story. I recently helped a customer drop average CPU utilization on its servers from more than 50 percent (with spikes up to 100 percent) down to an average of 5 percent by tweaking only three procedures. I've seen similar situations repeatedly. Many customers have plenty of room on their SQL Server boxes—provided their applications are running efficiently. And most customers can fit one more clown inside the Beetle—or Reporting Services on the box.

Amaze Your Co-workers with Your SQL Server Smarts

Subscribe to SQL Server Magazine and gain access to a treasury of SQL Server experts, content, tips, code listings, and more. Along with receiving the magazine each month, our online search capabilities allow for quick, easy access to locked-down, valued articles that feature columns by such experts as Brian Moran, Itzik Ben-Gan, and Kimberly L. Tripp. Subscribe today and receive 12 print issues, along with full access to the entire online article archive during your subscription term. BONUS—the newest System Table Map Poster and Subscriber Benefits Card. Click here

2. SQL SERVER NEWS AND VIEWS

The voting has closed in SQL Server Magazine's Instant Poll for the question, "What OS are you running SQL Server on?" Here are the results (+/- 1 percent) from the 514 votes (deviations from 100 percent are due to a rounding error):

The next Instant Poll question is "How do you prefer to get how-to or best-practices information from Microsoft?" Go to the SQL Server Magazine Web site and vote for 1) Information Web sites, 2) White papers, 3) Web seminars, 4) Live seminars, or 5) Consulting services.
   http://www.sqlmag.com

3. READER CHALLENGE

contributed by Umachandar Jayachandran, [email protected]

Congratulations to Narasimhan Jayachandran, a database management consultant for HTC Global Services in Troy, Michigan, and John Hanson, vice president of operations for MEDePass, Inc. in San Francisco. Narasimhan won first prize of $100 for the best solution to the February Reader Challenge, "Reporting Book Sales." John won second prize of $50. You can find a recap of the problem and the solution to the February Reader Challenge at
   http://www.sqlmag.com/articles/index.cfm?articleid=41747

Now, test your SQL Server savvy in the March Reader Challenge, "Restoring a Database" (below). Submit your solution in an email message to [email protected] by February 19. 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: Keith is the DBA for a company that runs several SQL Server 2000 data warehouses. He has a crucial database that contains the fact and dimension tables for the data warehouse. Keith performs full backups every week and periodic log backups after the database backup. The company's development team has requested the latest copy of the database along with any log backups for testing purposes. The team needs to run ad hoc queries against the database at different points in time to collect statistics. The sequence of steps that Keith uses to create the database and log backups is available online at
   http://www.sqlmag.com/articles/index.cfm?articleid=41747.

Keith also needs to provide the commands for restoring the database (in read-only format) up to and including the latest log backup, DW.trn.3. Help Keith write the script to restore a read-only copy of the database after different backups have been restored on a development server.

4. ANNOUNCEMENTS

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

SQL Server Magazine Connections will be held April 18-21 along with the concurrently running Microsoft ASP.NET Connections and Visual Studio Connections conferences. Register today and save $200, get access to all three conferences for one low price, and get a chance to win a Harley-Davidson. Register online or call 203-268-3204 or 800-438-6720.

Discover SQL Server solutions. Delve into real-world success stories. Drill down into building highly available database servers. Go to the SQL Server Magazine Special Reports section online. Valuable tools are only a click away.
   http://lists.sqlmag.com/cgi-bin3/DM/y/eebl0FgQMn0BRZ0BEkP0AF

5. RESOURCES

Microsoft left many SQL Server features undocumented so that the product developers could freely change those features in later releases or even service packs. This article is the first in a series exploring some undocumented features and back doors that T-SQL programmers can use. Beware, undocumented features can change without warning, and Microsoft won't help you if undocumented features don't work the way you expect them to. In his February T-SQL Black Belt column, "T-SQL Back Doors," Itzik Ben-Gan provides some keys that open hidden back doors to special T-SQL objects. Read this article today at
   http://www.sqlmag.com/articles/index.cfm?articleid=41044

Andy18 has a user with a Web site developed in Arabic and English. The search tool on his Web site uses approximated keywords for Arabic letters in nvarchar and ntext fields. The search tool works fine in the English version but not in the Arabic version. The user mentioned that the SQL collation on his local SQL Server is Arabic, though the SQL collation for Andy18's database is SQL_Latin1_General_CP1_CI_AS. Will changing the collation solve the problem? Or will changing the collation corrupt the data in the database? Offer your advice and see what other people have said on SQL Server Magazine's Development forum at
   http://www.sqlmag.com/forums/messageview.cfm?catid=9&threadid=22785

Q. My server has 3GB of physical memory. A previous Windows administrator set the page file size to 2GB, but I'd like to reduce the page file's size so I can use more memory for other needs. Will reducing the page file size decrease my application's performance?

A. In general, a well-tuned SQL Server running on adequately sized hardware will perform few physical reads or writes to the page file. SQL Server performance won't suffer if the page file is too large and not being fully used, but having a large page file wastes disk space. The page file's Percent Usage Peak counter in Performance Monitor will help you determine your page file's utilization. For the complete answer to this question, see
   http://www.sqlmag.com/articles/index.cfm?articleid=41324

Send your technical questions to Brian Moran at [email protected].

6. NEW AND IMPROVED

(contributed by Dawn Cyr, [email protected])

Guardium announced SQL Guard, a non-intrusive appliance for secure data access. SQL Guard delivers continuous visibility into all network-based access to distributed databases so that you can know who is accessing your data, what data they access, and when and how they access it. The patent-pending technology is content and context aware and is network based, so the product can offer a global network view from outside the database without degrading network, application, or database performance. SQL Guard automatically sends information about database access to security, auditing, and privacy staff in the form of customizable reports that those people can use to build better security and deter intrusions. SQL Guard works with SQL Server 2000, Oracle, and Sybase systems, and pricing for the product starts at $12,995. Contact Guardium at 877-487-9400 or [email protected].
   http://lists.sqlmag.com/cgi-bin3/DM/y/eebl0FgQMn0BRZ0BFVL0Ap

IPLocks announced its Database Security Audit System (IPLocks-DSAS) 3.2, a software platform that automates the process of assessing and monitoring transaction-level data integrity. IPLocks-DSAD alerts key personnel to malicious acts, security-policy violations, data corruption, changes to database structural integrity, hidden viruses, and suspicious access or usage patterns. Designed for systems with high transaction volume, the software provides a non-intrusive system for vulnerability assessment and monitoring that you can implement in your existing infrastructure. The software supports heterogeneous and multiple-database environments and works with various platforms including SQL Server, Oracle, IBM DB2, and Sybase. For pricing and more information, contact IPLocks at 408-383-7513.
   http://lists.sqlmag.com/cgi-bin3/DM/y/eebl0FgQMn0BRZ0BFVM0Aq

SPONSORED LINKS

Quest Software, Inc.
   Database contention affecting SQL Server performance? Download white paper

Innovartis Ltd.
   Auditable, repeatable, reversible team SQL code? Meet DB Ghost.

7. 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

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

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

Copyright 2004, Penton Media, Inc.