SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
Can You Recover a Dead Server in 15 Minutes?
SSMU Course 2073 Registration Deadline Is Monday
(below COMMENTARY)
Imperial Technology MegaRam(TM) Solid State Disks
(below NEWS AND VIEWS)
SPONSOR: CAN YOU RECOVER A DEAD SERVER IN 15 MINUTES?
You can with UBDR (UltraBac Disaster Recovery)! Ever calculated your cost for an hour of unplanned downtime? Corporate surveys estimate costs from $5000 per hour to over $15,000 PER MINUTE! Why cost your business by being down longer than necessary? UBDR reduces downtime to the absolute minimum. UBDR's image technology makes restoring a failed OS partition a snap, whether due to registry or file corruption or accidental file deletions. Just boot from a single floppy, no other operator intervention is required. Best of all, UBDR co-exists with ALL backup products! Download now!
http://lists.sqlmag.com/cgi-bin3/flo/y/eQRg0FgQMn0BRZ08j20Ai
April 10, 2003—In this issue:
1. COMMENTARY
- DeWitt Clauses: Serving the Wrong Master
2. SQL SERVER NEWS AND VIEWS
- Upgrade MSDE to SQL Server 2000 SP3
- Unisys Ships New ES7000 500 Series
- Results of Previous Instant Poll: DeWitt Clauses
- New Instant Poll: SAN Usage
3. READER CHALLENGE
- April Reader Challenge Winners and May Challenge
4. ANNOUNCEMENTS
- SQL Server Magazine Connections—Win a Harley-Davidson!
- Have You Checked Out SSMU's Newly Designed Site?
5. RESOURCES
- What's New in SQL Server Magazine: Partitioning Tips
- Hot Thread: Consolidating Databases
- Tip: Replicating Data in a Franchise Company
6. HOT RELEASES (ADVERTISEMENTS)
- Join the SQL Server Magazine Research Panel
- Find Answers to Your SQL Server Questions
7. NEW AND IMPROVED
- Speed the Creation of Dynamic Web Pages
- Get Answers to Your Common SQL Server Questions
8. CONTACT US
- See this section for a list of ways to contact us.
1. COMMENTARY
(contributed by Brian Moran, news editor, [email protected])
Vendors have some good reasons for including DeWitt clauses in their End User License Agreements (EULAs) to prevent people from publishing benchmarks without their approval. Last week ("The Devil's in the DeWitt Clause,"), I presented the vendors' side of the argument:
- Creating a benchmark that accurately describes a database's performance characteristics is hard.
- People can too easily accidentally or maliciously use benchmarks to misrepresent the true performance characteristics of a database.
- Customers might make poor database purchasing and deployment choices if they base their decisions on these inaccurate results.
Although I agree wholeheartedly with the first two points, I don't believe that DeWitt clauses serve customers' best interests. Here are a few arguments to support my position.
1. Extreme Control Issues
Yes, people will accidentally or perhaps purposely publish inaccurate database benchmarks if vendors remove DeWitt clauses from EULAs and allow free testing of their products. Taken to an extreme, however, this line of reasoning can lead vendors of all kinds of products to believe and demand that they must approve any review of their product—to protect the customer from making poorly informed choices, of course. That's clearly extreme. If vendors eliminated DeWitt clauses, I believe the market would quickly become savvy about evaluating the merits of database benchmarks. DBAs would learn how to sift through the useless and misleading information to find the benchmarks that are genuinely valuable. And they would learn how to better run performance tests of their own.
2. Where in the (Real) World?
Today's database-benchmarking offerings don't come remotely close to meeting the needs of real-world database users. Transaction Processing Performance Council (TPC) scores are useful for exploring the theoretical high end of a database's performance. But few of us run applications on 16-, 32-, or 64-CPU systems that cost tens of millions of dollars. TPC and vendor-sponsored benchmarks rarely give us the information we need to tune the 2- or 8-CPU systems that most of us use.
3. Serving the Marketing Master
For better or worse, vendor-sponsored benchmarks typically serve the higher master of marketing goals rather than help customers understand how to best use their systems. Most database users are concerned with making their system run faster—not with comparing the relative performance of one database platform with another, which inevitably is the goal of a vendor-sponsored benchmark such as a TPC-C score.
4. The Advantages of Public Debate and Review
Have you ever conducted a scientific study of what happens to SQL Server performance when you make one tuning or configuration change at a time? Have you fully experimented with changing the number of files in a filegroup to find the optimal setting or bounced back and forth between RAID 0, 1, 5, and 10 to find the optimal I/O settings for your system? Taking the time to run these types of tests in the real world is essentially impossible. Most of us don't even have time to document the systems we're already running. Wouldn't it be wonderful if independent third parties were free to fully explore the ins and outs of database performance tuning by using a public benchmark? Wouldn't the benchmark improve over time, and wouldn't we root out inaccuracies if the benchmark methodology were placed in the public domain for peer review?
5. What Does the Law Say?
Discussions about DeWitt clauses, which are facing legal challenges, might soon be moot. A reader alerted me to a case that the State of New York brought against Network Associates concerning a firewall review that Network World performed. Network Associates claimed the review violated its DeWitt clause and threatened legal action. You can find a summary of the case at http://antivirus.about.com/library/weekly/aa012003b.htm . The short version is that the New York Supreme Court, which said Network Associates' DeWitt clause stifles free speech, prohibited the benchmark disclosure language in the company's existing EULA. Eliot Spitzer, New York Attorney General, had this to say about the case:
"Whether the subject is political debate, debate in the arts and sciences, or debate over what software to buy, we must protect free and open speech from intimidation. The public has a right to information about products, and technologists have a right to create and innovate, so long as they adhere to the law when doing so. After all, we will lose the benefits of life in the Information Age if language contained on Web sites or product packaging prevents citizens from participating in vital exchanges of information."
High-end TPC-C scores serve a necessary and useful purpose, giving us information about how far a database can scale. However, benchmarking of real configurations is sorely underrepresented, with vendors playing a never-ending game of "TPC-C King of the Mountain." I hope vendors will recognize the shortcomings of the current database-benchmarking situation and find ways to meet their customers' needs in the real world. Power to the people! Down with DeWitt!
On a personal note:
INSERT INTO
BeautifulBabyBoy
SELECT
HugsAndKissesForever
FROM
TheWorldHasChanged
WHERE
DOB = '2003-04-01'
and ProudDaddy = 'Me'
and MyFirstSon = 'Joseph Connor'
He can't talk yet, but he's working on SQL this week and plans to start on MDX in a few weeks. <g>
SPONSOR: SSMU COURSE 2073 REGISTRATION DEADLINE IS MONDAY
Monday, April 14, is the registration deadline for SQL Server Magazine University (SSMU) e-Learning Center's Microsoft Certified Training Course #2073, Programming a Microsoft SQL Server 2000 Database. This LIVE online SSMU course provides students with the technical skills required to program a database solution by using SQL Server 2000. Daily 60- to 90-minute online classes will meet beginning on April 21. Sessions are archived for review by students 24/7. Hands-on training in our Virtual Lab is also included. Don't miss this valuable training designed to help you do your job better and prepare for Microsoft exams! Go now to:
http://lists.sqlmag.com/cgi-bin3/flo/y/eQRg0FgQMn0BRZ08ge0AW
2. SQL SERVER NEWS AND VIEWS
A new step-by-step article from Microsoft describes how to upgrade an instance of Microsoft SQL Server 2000 Desktop Engine (MSDE) to SQL Server 2000 Service Pack 3 (SP3). Note that if your computer has multiple instances of MSDE 2000 running, each instance might have a different set of upgrade requirements, so you must upgrade each instance of MSDE 2000 separately. To read "HOWTO: Upgrade an Instance of Microsoft SQL Server 2000 Desktop Engine to SQL Server 2000 Service Pack 3," see
http://support.microsoft.com/default.aspx?scid=kb;en-us;815013#2
Unisys has begun shipping the new 500 series of the company's established Unisys ES7000 server line. In addition to upgraded features for customers who run versions of Windows Datacenter Server 2002, the extended line includes entry-level servers for customers who run Windows Server 2003, Enterprise Edition. The servers all use the Unisys Cellular MultiProcessing (CMP) architecture. Kathy Ivens of Windows & .NET Magazine examined the new servers at the Unisys Engineering Evaluation Lab in Pennsylvania and discovered several changes since she last wrote about the server series in June 2001. Visit the following URL to read about the new server series:
http://www.winnetmag.com/windowsserver2003/index.cfm?articleid=38610
The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Are you for or against DeWitt clauses, which forbid publication of database benchmarks that the database vendor hasn't sanctioned?" Here are the results (+/- 1 percent) from the 238 votes (deviations from 100 percent are due to rounding errors):
- 16% I'm for the clauses
- 34% I'm against the clauses
- 25% I can see both points of view
- 8% I need more information to decide
- 16% I don't care about the clauses
The next Instant Poll question is "Do you use a Storage Area Network (SAN) in your environment?" Go to the SQL Server Magazine Web site and submit your vote for 1) Yes, 2) No, but we're considering it, or 3) No, and we're not considering it.
http://www.sqlmag.com
SPONSOR: IMPERIAL TECHNOLOGY MEGARAM(TM) SOLID STATE DISKS
MegaRam SSDs are storage products that perform at memory speeds, delivering thousands of I/Os per second at speeds of up to 20 times faster than traditional rotating disk drives. SSDs are commonly used for high transaction rate files like TEMPDB, Log, Indices, and frequently accessed Tables. For more information, see a 6-page white paper written by acclaimed guru Jeff Garbus of Soaring Eagle Consultants, Ltd., on using SSDs with Microsoft SQL Server at:
http://lists.sqlmag.com/cgi-bin3/flo/y/eQRg0FgQMn0BRZ08j30Aj
3. READER CHALLENGE
(contributed by SQL Server MVP Umachandar Jayachandran, [email protected])
Congratulations to Anith Sen, an independent database consultant in Cordova, Tennessee, and Ken Haley, manager of development for SPECTRUM Human Resource Systems Corporation in Denver. Anith won first prize of $100 for the best solution to the April Reader Challenge, "Data Export." Ken won second prize of $50. You can find a recap of the problem and the solution to the April Reader Challenge at
http://www.sqlmag.com/articles/index.cfm?articleid=38463.
Now, test your SQL Server savvy in the May Reader Challenge, "Query Strings" (below). Submit your solution in an email message to [email protected] by April 17. 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: Maria is the database developer for a company that analyzes Web data and provides reports based on Web traffic. One such report provides a list of the most popular URLs, based on number of hits. The company's databases run on SQL Server 2000. Maria has a QueryStrings table that contains query strings from various URLs and a Urls table that maps the query strings to specific URLs. The schema for the tables and some sample data follows:
CREATE TABLE QueryStrings ( QSID int NOT NULL IDENTITY PRIMARY KEY, QSVal varchar(8000) NOT NULL ) INSERT INTO QueryStrings VALUES( 'a=1&b=2&c=3' ) INSERT INTO QueryStrings VALUES( 'a=1&b=4' ) INSERT INTO QueryStrings VALUES( 'b=2&a=1&c=3' ) INSERT INTO QueryStrings VALUES( 'a=1&b=24' ) INSERT INTO QueryStrings VALUES( 'a=11&b=24' )
CREATE TABLE Urls ( UrlID int NOT NULL IDENTITY PRIMARY KEY, QSID int NOT NULL REFERENCES QueryStrings( QSID ) ) INSERT INTO Urls VALUES( 1 ) INSERT INTO Urls VALUES( 1 ) INSERT INTO Urls VALUES( 2 ) INSERT INTO Urls VALUES( 2 ) INSERT INTO Urls VALUES( 3 ) INSERT INTO Urls VALUES( 4 ) INSERT INTO Urls VALUES( 5 ) INSERT INTO Urls VALUES( 5 )
Maria uses the QueryStrings table in several queries and JOIN conditions to filter the URLs based on the search string specified by the user requesting the report. The following sample query joins the QueryStrings table with the Urls table:
DECLARE @SearchString varchar(8000) SET @SearchString = 'a=1&b=2' SELECT u.UrlID, u.QSID, q.QSVal FROM Urls AS u JOIN QueryStrings AS q ON q.QSID = u.QSID WHERE '&' + q.QSVal + '&' LIKE '%&' + @SearchString + '&%'
In the sample query, Maria searches for URLs that contain only certain query-string parameters and values, such as a=1&b=2. Given the sample data, the query will return two query strings: QSID=1 and QSID=3. Now, she wants to modify the query so that it returns all query strings that contain the parameters being searched for irrespective of their order. Help Maria find a set-based solution for her new requirement.
4. ANNOUNCEMENTS
(brought to you by SQL Server Magazine and its partners)
Register today for SQL Server Magazine Connections, and you'll get free access to concurrently run Microsoft ASP.NET Connections and Visual Studio Connections. Attendees will get a chance to win a Harley-Davidson motorcycle. Don't miss this exclusive opportunity to learn from and interact with your favorite SQL Server Magazine writers!
http://lists.sqlmag.com/cgi-bin3/flo/y/eQRg0FgQMn0BRZ0ggP0Aw
SQL Server Magazine University e-Learning Center has launched a new Web site for you to check out! SSMU's online learning opportunities include live, 1-hour Web seminars and Microsoft Certified Training courses taught by the best instructors in the industry. We're here to help you do your job better! Click now:
http://lists.sqlmag.com/cgi-bin3/flo/y/eQRg0FgQMn0BRZ067v0Ax
5. RESOURCES
SQL Server 2000's partitioned views let organizations tune their systems to handle a lot of data. This feature lets you partition data across several tables, and logically uniting the tables via a view makes the partitioning as transparent as possible to users and developers. In his April 2003 SQL Server Magazine article "Partitioning Tips," Itzik Ben-Gan shares a few tips that you might not be aware of, including the negative effect of using Enterprise Manager to make schema changes to partitions, how to circumvent the limitation on how you can insert data into the tables through the partitioned view, and problems caused by using constants in the partitioning criteria. You can read this article online at
http://www.sqlmag.com/articles/index.cfm?articleid=37889
Lee poses the following scenario: Suppose you have several online transaction processing (OLTP) databases running on different SQL Servers and they all relate to a certain extent (they have some shared domain tables and so on). What would happen, given that the databases aren't very large, if you combined all of them into one database? Assuming that the server has plenty of resources to handle your connections and their corresponding requests, what disadvantages might come from such consolidation? 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=14513
(contributed by Microsoft's SQL Server Development Team, [email protected])
Q. Our company has 100 franchises organized into 10 zones, and each zone manages 10 branches. Each branch runs a booking system on SQL Server 2000, and every week, each branch must send data to its zone office and the company's head office. What's the best way to ensure that the data is synchronized and includes modifications from each branch?
A. If you make updates only at the branch level, you can set up a hierarchical topology using transactional replication. Set up each zone database as a central (rollup) subscriber to publications that you create at each branch. Then, republish data from the zone databases into a central subscriber at the head office.
The "Designing a Replication Topology" section in SQL Server Books Online (BOL) provides more information about using transactional replication for this kind of data transfer. If updates go from the head office to the branches as well as from the branches to the head office and could conflict, you might want to use merge replication. Merge replication provides a rich, customizable conflict-management framework.
Send your technical questions to [email protected]
6. HOT RELEASES (ADVERTISEMENTS)
You can participate in this industry research panel by providing market input and commenting on trends in the industry. Express your views and make your voice heard in the SQL Server community
http://lists.sqlmag.com/cgi-bin3/flo/y/eQRg0FgQMn0BRZ08j40Ak
Inside the latest release of the SQL Server Magazine Master CD, you'll find a gold mine of SQL Server code, tips, and information! Search by keyword, subject, author, or issue. Order your one-year subscription today!
http://lists.sqlmag.com/cgi-bin3/flo/y/eQRg0FgQMn0BRZ0KrA0AQ
7. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
Advanced Extensions announced Advanced Query Wizard, a SQL query-building tool that speeds and simplifies the creation of dynamic Web pages in Macromedia Dreamweaver MX. Advanced Query Wizard helps you build record sets, which contain information from tables in ODBC data sources. You don't need SQL knowledge to insert dynamic data such as information about sales, pricing, and availability in your Web applications. The software automatically generates the correct SQL code to perform extracts, joins, filters, groups, and summaries. Advanced Query Wizard supports any ODBC-compliant database, including SQL Server 2000, Microsoft Access, MySQL, and Oracle.
http://www.advancedextensions.com
Curlingstone announced "SQL Server 2000 Fast Answer for DBAs and Developers," a book by Joseph Sack that covers a wide range of SQL Server topics for DBAs and developers. Topics include installation and upgrades, configuring SQL Server, creating databases, SQL Server Agent and SQL Server logs, security, replication, failover clustering, log shipping, Transact-SQL (T-SQL) techniques, and XML. The book provides "how to" answers to common SQL Server DBA and development questions. The 980-page book costs $41.99 at Amazon.
http://www.curlingstone.com
8. 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 Connections conference—loaded with best-practices information from magazine authors and Microsoft product architects—is designed to provide you with the latest SQL Server tools, tips, and real-life examples you need to do your job.
http://lists.sqlmag.com/cgi-bin3/flo?y=ePF50FgQMn0BRZ0ggP0At
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.
