SQL Server Magazine UPDATE—brought to you by SQL Server Magazine http://www.sqlmag.com and SQL Server Magazine LIVE! http://www.sqlmag.com
THIS ISSUE SPONSORED BY
Need to Configure a Server for Microsoft SQL Server?
http://www.hp.com/solutions/mssql
Increase Server Reliability and Uptime
http://www.execsoft.com/downloads/menu.asp?ad=sqlnl4
(below COMMENTARY)
How to Geo-Cluster DB Servers: Free Unisys Paper
http://www.app1.unisys.com/simplify/sql/newsletter/
(below NEWS AND VIEWS)
SPONSOR: NEED TO CONFIGURE A SERVER FOR MICROSOFT SQL SERVER?
Check out the online ProLiant Transaction Processing Sizer for Microsoft SQL Server 2000 from HP. This automated tool will help you determine an optimum hardware configuration for your database server — based on YOUR requirements. Through an interview process, a set of hardware configurations is developed using patented system sizing and configuration technology. Specific configuration information and performance recommendations are provided for each configuration. Developed by HP in our software integration lab, this tool supports the ProLiant server family and appropriate options. For more information, visit
http://www.hp.com/solutions/mssql
October 31, 2002—In this issue:
1. COMMENTARY
- MSDE Demystified
2. SQL SERVER NEWS AND VIEWS
- 4 Innovators Win Top Awards at SQL Server Magazine LIVE!
- Microsoft and EMC Build Largest SQL Server SAN Ever
- Microsoft and CA Set Backup/Restore Benchmark
- Results of Previous Instant Poll: XML Knowledge
- New Instant Poll: Data Access
3. ANNOUNCEMENTS
- Register Today! SQL Server Security for .NET
- Did You Miss SQL Server Magazine's Web Seminars?
4. RESOURCES
- What's New in SQL Server Magazine: Cross-Join Performance
- Hot Thread: Delayed Data Retrieval
- Tip: Choosing the Right Performance Monitor Counters
5. NEW AND IMPROVED
- Learn to Maximize SQL Server Performance
- Perform Data Replication from IBM Servers to SQL Server
6. CONTACT US
- See this section for a list of ways to contact us.
1. COMMENTARY
(contributed by Brian Moran, news editor, [email protected])
Sometimes the best things come in small packages, and MSDE is one of them. MSDE is Microsoft’s mini data-storage engine and query processor that you can freely distribute and embed in other applications, according to the terms of its license agreement. Microsoft supports MSDE under two labels. Confusing? Some customers think so. When the company introduced MSDE with SQL Server 7.0, the acronym stood for Microsoft SQL Server Data Engine. Microsoft rebranded MSDE as Microsoft SQL Server 2000 Desktop Engine when SQL Server 2000 shipped, but Microsoft also calls this version SQL Server 2000 Desktop Edition. (SQL Server 2000 Books Online consistently uses the Desktop Engine taxonomy for the product.) To add to the naming complexity, some users confuse MSDE with the SQL Server 2000 Personal Edition. This week I show you how MSDE differs from the full SQL Server and compare MSDE 2000 with the Personal Edition.
MSDE shares SQL Server 2000 Standard Edition's engine features. However, three traits distinguish MSDE from its parent: A query governor limits database performance when SQL Server executes more than 5 batches concurrently, database size is limited to 2GB, and the engine conspicuously lacks GUI administration tools. MSDE doesn't ship with any administration tools because it's designed to be a hidden data store for another product, and that product needs to provide the necessary maintenance tools. Technically, you can use a standard version of Query Analyzer or Enterprise Manager to interact with MSDE, but legally you can't use the tools that way unless you have a valid client access license (CAL) to use them with another version of SQL Server.
MSDE 2000 has some features in common with SQL Server 2000 Personal Edition but differs in others. The Personal Edition shares the 5-user query limit but isn't restricted to a 2GB database and comes with standard SQL Server administrative tools. You can use Personal Edition on any machine that has a valid license for connecting to SQL Server 2000 Standard or Enterprise Edition. In some cases, you could use either MSDE or Personal Edition. The biggest difference between the two products is that you can distribute MSDE 2000 in applications that you build, but you can't distribute Personal Edition.
Some novice users might wonder whether you could use either MSDE 2000 or the Personal Edition as a low-cost alternative to SQL Server Standard Edition for a small work group. Don't do it! This practice is illegal. You'd also find that performance would suffer because the query governor kicks in when more than 5 users try to run concurrent T-SQL batches.
I hope this brief discussion has cleared up a few questions you might have had about MSDE in its various incarnations. You'll find more detailed answers to license and usage questions for MSDE at
http://www.microsoft.com/sql/howtobuy/msdeuse.asp
SPONSOR: Increase Server Reliability and Uptime — FREE
What do server slowdown, system crashes and freezes, slow boot times, file corruption and hard drive failures all have in common? Fragmentation. That's right — these problems are all most often the result of a fragmented disk, which greatly impairs system stability and reliability. Diskeeper(R) Lite, the manual introduction to automatic disk defragmenter Diskeeper 7.0, eliminates fragmentation and the problems that result from it, so your system is more reliable and you experience more uptime. Diskeeper Lite has been updated, and is now as fast as full-version Diskeeper. Diskeeper Lite also runs on all Windows(R) operating systems and automatically detects the fragmentation levels on your machine. Get maximum uptime for your system today. Download Diskeeper Lite — free!
http://www.execsoft.com/downloads/menu.asp?ad=sqlnl4
2. SQL SERVER NEWS AND VIEWS
Four SQL Server professionals walked away with top honors in the first annual SQL Server Innovator Awards, presented at the SQL Server Magazine LIVE! conference in Orlando, Florida, this week. The Innovator Awards, sponsored by Microsoft, is an editorial-driven awards program designed to highlight outstanding business solutions that use SQL Server. The four winners were selected from a pool of highly qualified entries collected over a 3-month period.
"The field included more than 100 entries, and the selection process was rigorous," said Brian Moran, contributing editor for SQL Server Magazine and one of the five judges for the awards program. "We chose the winners based on their innovations' efficiencies, improved processes, and return on investments."
The award winners were Herts Chen, Dave Fackler, Paul Munkenbeck, and Zareer Siganporia. Chen, who works for the City of Portland, developed a solution that involved Analysis Services, traditional reporting techniques, and ESRI-based mapping objects, all of which produced a powerful traffic and accident data warehouse that publishes its data to the Web.
Fackler, with Intellinet Corporation, and his team received an Innovator Award for a telephony solution that integrates Microsoft relational and analytical tools, Data Transformation Services (DTS), XML-based recordsets, MSMQ, and custom COM objects.
Munkenbeck, with Maritz, Ltd., and his team created a solution based on modifying core replication stored procedures, which lets them update the master schema without requiring a full resync.
Siganporia, previously with SchlumbergerSema, was the fourth winner, and his entry was also selected as the best overall, capturing the Innovator Awards traveling trophy. His solution employed the creative use of metadata and custom code to detect when and how large data sets should be partitioned for maximum efficiency.
Brenda Roode, circulation manager for SQL Server Magazine, noted, "With over 100 entries, we feel that the enthusiasm the awards generated indicates the overall growth and strength of the SQL Server community."
Microsoft and partner EMC announced this week that they've developed a 45TB storage area network (SAN)—the largest SQL Server SAN built to date, according to the companies. The joint engineering project, tested with a 10TB simulated human genomics database, showcases SQL Server's capacity for handling large-scale, automated, networked storage environments. All new development and testing programs at the SQL Server labs now take place on the EMC networked storage platform. For more information about the project, see
http://www.microsoft.com/presspass/press/2002/Oct02/10-29msemcsqlsanPR.asp
Microsoft, Computer Associates International (CA), and other collaborators recorded a record-setting 2.6TB-per-hour archival backup of a 2.5TB SQL Server 2000 Enterprise Edition database running on Windows 2000 Datacenter Server, the companies said this week. CA's BrightStor Enterprise Backup sustained a restore rate of 2.2TB per hour on 32 drives. You can find a white paper that documents the benchmark process, the lab architecture, the backup-and-restore procedures tested, and more at
http://www3.ca.com/solutions/collateral.asp?id=2696
The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "How familiar are you with XML?" Here are the results (+/- 1 percent) from the 531 votes:
- 25% I've used XML in a project
- 11% I understand XML, including the data model, parsers, and technologies
- 32% I'm just starting to learn about XML
- 20% I've heard the name and know that XML is similar to HTML
- 11% I don't know anything about it
The next Instant Poll question is "What is the primary data API you use for accessing SQL Server?" Go to the SQL Server Magazine Web site and submit your vote for 1) ADO.NET, 2) ADO, 3) OLE DB, 4) ODBC, or 5) DBLIB.
http://www.sqlmag.com
SPONSOR: Free Geo-clusters paper from Unisys
FREE EXPERT ADVICE: Don't neglect your SQL database servers when it comes to availability and business continuity. Especially for applications that need data around the clock or around the world — especially your Internet based ones. And with security such a vital issue today, dispersing clustered Windows-based servers across distances is the optimal solution. Experienced designer/engineers at Unisys show you how to do it successfully in this detailed, comprehensive white paper on Geographically Dispersed Clusters.
http://www.app1.unisys.com/simplify/sql/advice/
3. ANNOUNCEMENTS
(brought to you by SQL Server Magazine and its partners)
Join Morris Lewis on Tuesday, November 12, for a special 1-hour presentation, "Designing SQL Server Security for .NET," brought to you by SQL Server Magazine. Learn how to adapt SQL Server security to support truly distributed applications. Tap into Morris Lewis's programming, network administration, database administration, and training expertise. Click here!
http://www.sqlmag.com/sub.cfm?code=mlei322jul
No worries! They're still accessible right at your desktop! Kalen Delaney discusses SQL Server internals, Brian Moran identifies performance problems, Rich Rollman teaches about XML for database professionals, and Morris Lewis instructs on high availability. Valuable training at a fraction of the cost of traveling to further your education. Go to
http://www.sqlmag.com/sub.cfm?code=wnei322juw
4. RESOURCES
When you're analyzing terabytes of data in an OLAP cube, performance bottlenecks quickly become obvious. One of the most common sources of diminished performance is the MDX CROSSJOIN() function. Russ Whitney explores options for optimizing the use of this function in his column "Cross-Join Performance," which appears in the October 2002 issue of SQL Server Magazine and is available online at
http://www.sqlmag.com/articles/index.cfm?articleid=26100
X-Man and his team have begun building cubes from a newly installed instance of SQL Server 2000 Analysis Services. The first time users try to access a cube's data, retrieval takes a few seconds. However, response to subsequent queries is immediate. X-Man wants to know what the OLAP server is doing during the long interval of the first access attempt—trying to allocate memory? Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/forums/rd.cfm?cid=10&tid=9870
(contributed by Brian Moran, [email protected])
Q. I need to create a performance baseline for my company's SQL Server based on Performance Monitor counters. What counters should I measure, and which ones are most important?
A. We could devote an entire issue of SQL Server Magazine to a discussion of which SQL Server counters are important under what circumstances. Instead, let's look at a few counters that I think are most important and, sometimes, least understood.
When you talk about performance, you're usually concerned with the amount of business-related work a server can process—in other words, the numbers of queries and transactions you're processing. After all, who cares if CPU utilization is consistently near 100 percent if response time is always great and the application handles all your users' requests? Of course, a server at 100-percent CPU utilization has no headroom to handle processing peaks, and you wouldn't likely get adequate response time for queries if the server was consistently running at 100 percent. However, the amount of real work the database performs is ultimately the only measure that matters to end users, administrators, and their bosses.
How can you use Performance Monitor to track the amount of work that SQL Server performs? Two Performance Monitor counters specifically let you track SQL Server's work: the Transaction/sec counter in the SQL Server Databases object and the Batch Requests/sec counter in the SQL Server General Statistics object. Both counters are useful, but many people incorrectly interpret the values that they show. For an example of the kind of information these counters provide, perform the following tests.
Open an instance of Performance Monitor, select the System Monitor pane, and add the Transactions/sec and Batch Requests/sec counters I mentioned above. The Transactions/sec counter tracks information at a database level, so you need to select a database—I use tempdb for this example. Create a test table in tempdb by running the following script:
USE tempdb CREATE TABLE WorkTest (Col1 int) INSERT INTO WorkTest VALUES (1) GO
Now execute the following simple batch repeatedly as fast as possible:
SELECT * FROM worktest GO
The easiest way to execute the batch repeatedly is to select the batch in Query Analyzer, press Ctrl+E to execute the batch, then keep Ctrl+E depressed. The query will execute over and over again as long as you keep the keys depressed. Run the batch in this manner continuously for at least 5 seconds, then toggle back to Performance Monitor.
What do you see in Performance Monitor? You should see the Batch Requests/sec counter spike (it went to about 15 on my laptop) while the Transactions/sec counter remains flat at 0. When you have multiple counters, sometimes picking out the line in the Performance Monitor chart that contains the counter information you want is hard. To make your counters easier to read, use the highlighting option: Select the icon that looks like a light bulb to highlight the currently selected counter in a wide bold line.
Now run the following batch repeatedly, using the technique I described above, and observe the activity in Performance Monitor:
BEGIN TRAN SELECT * FROM worktest COMMIT TRAN
In Performance Monitor, you should see identical values for both Batch Requests/sec and Transactions/sec. What does this result explain about the nature of each counter?
Transaction/sec doesn't measure activity unless it's inside a transaction. Batch Requests/sec measures all batches you send to the server even if they don't participate in a transaction. SQL Server Books Online specifically says that Batch Requests/sec is a good measure of throughput (i.e., work). However, Transactions/sec is the counter that many people use in the field. Unfortunately, this counter gives you a number skewed to the low side if your application workload includes a reasonably large number of batches that don't participate in a transaction—which is common. What's a large number? If 20 percent of your workload includes simple SELECT statements that don't participate in a transaction, the Transactions/sec counter will be off by about 20 percent—a significant amount in my mind. Numbers as low as 5 percent could be significant to you, depending on how detailed your analysis needs to be. Batch Requests/sec is a better indicator of throughput in such cases.
To more fully understand Transactions/sec, run the following final test:
UPDATE Worktest set Col1 = 1 GO
You should see the same behavior that you saw in the previous test: Transactions/sec and Batch Requests/sec are identical. Thus, you don't need to issue an explicit pair of BEGIN/COMMIT statements to make SQL Server report the batch as having started a transaction. Of course, all UPDATE, INSERT, and DELETE statements are transactionally consistent and operate within the scope of a transaction.
Send your technical questions to [email protected].
5. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
Prentice Hall announced a book by Carl H. Speshock, "Microsoft SQL Server 2000 Database Administrator's Guidebook," a guide for DBAs who want to maximize SQL Server performance, security, and scalability. The book covers planning, design, installation, optimization, automation, clustering, technical writing, and data modeling. The book also includes help for DBAs migrating from Oracle or Sybase. The CD-ROM includes a library of support documents and scripts. The 474-page book costs $35.99. Contact Prentice Hall at 800-282-0693.
http://www.phptr.com/ptrbooks/ptr_0130614300.html
HiT Software announced DB2Motion, software that performs realtime data replication from IBM iSeries or AS/400 servers to SQL Server databases. The software provides a graphical administration interface, VBScript support, and a Windows service engine with log support. You can install DB2Motion on Windows XP, Windows 2000, and Windows NT servers. The software supports OS/400 and SQL Server 2000 and 7.0. Pricing is $2995 per server. Contact HiT Software at 408-345-4001
http://www.hitsw.com
6. 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?
More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at [email protected] or 800-719-8718.
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
The SQL Server Magazine LIVE! conference is chock-full of best-practices information from magazine authors and Microsoft product architects designed to provide you with the latest SQL Server tools, tips, and real-life examples you need to do your job.
http://www.sqlmagLIVE.com
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.