SQL Server Magazine UPDATE, February 24, 2005--Open-Source Databases

Our domain name has changed! To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.

This Issue Sponsored By
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.

Fast Access to SQL Server with DataDirect JDBC
    http://lists.sqlmag.com/t?ctl=368B:7B3DB

Free .NET or SQL Server Training CD!
    http://lists.sqlmag.com/t?ctl=3689:7B3DB

DB Ghost For SQL Server
    http://lists.sqlmag.com/t?ctl=3688:7B3DB

February 24, 2005

1. Perspectives

2. News and Views

3. Events and Resources

4. Peer to Peer

5. Announcements

6. New and Improved

Sponsor: 5.25" floppies * 8-Tracks * SQL Server 2000 JDBC
Sure, they were great technologies once, but try using them today! Microsoft's JDBC driver for SQL Server 2000 is built on technology that's more than 3 years old. You wouldn't try to run your CD on an 8-track player. Why would you run your business application with an old JDBC driver? DataDirect presents today's JDBC—the SpecjAppServer/ECPerf leader. Featuring Windows Authentication support, J2EE certification, and advanced 3.0 specification compliance for SQL Server 2000 and SQL Server 7. The difference? Like comparing DVD to Betamax.

Try DataDirect Connect for JDBC. You'll never go back. Read more:
    http://lists.sqlmag.com/t?ctl=368B:7B3DB

1. Perspectives

    by Brian Moran, [email protected]

Does it make sense to consider using an open-source database? More to the point, does it make sense to choose an open-source database instead of SQL Server when cost is your primary concern? Interesting questions—I want to share some insights. I touched on this topic in my previous commentaries "In the Express Lane with SQL Server" and "MSDE: Get More than You Pay For". I want to revisit the topic because I've been reading some articles in the mainstream press about open-source databases such as MySQL or Ingres and how they compare favorably to database solutions that you have to pay for.

Let me set the stage for this discussion. Imagine that you're planning a new application rollout. You use Microsoft technology regularly (but not exclusively), and SQL Server is on the short list of database platforms you're considering, but price is a significant factor. Let's assume that up-front cost to purchase is the only thing that matters—of course, that's not true, but I want to keep the discussion simple. MySQL, Ingres, or any other free, open-source database can look attractive if up-front license costs are your primary concern. Can Microsoft play in that game? The short answer is probably. (Don't you love a journalist who's willing to go out on a limb with a definitive opinion? ) Microsoft SQL Server Desktop Engine (MSDE) is a viable free option in many scenarios when you can roll out a free, open-source database. However, there are some caveats to that statement. First, MSDE isn't completely free, but buying a single license for any product that MSDE comes with (e.g., Microsoft Access) gets you free redistribution rights for MSDE's core pieces. You can build an application and distribute it to an infinite number of users for free. To read more about how to get and distribute MSDE, see "Register for MSDE 2000 Redistribution Rights". Second, the Query Governor prevents MSDE from scaling to meet your high-end database needs. However, if you're worrying about high-end databases, it's likely that a completely free open-source database probably won't meet your needs either.

Now, let's talk about the options that will exist in a few months when SQL Server 2005 ships and SQL Server 2005 Express Edition becomes commercially available. SQL Server Express is the new version of MSDE that's part of the SQL Server 2005 product family. Yes, it's cheating to compare current open-source products to software from Microsoft that's not commercially available yet, but I'm doing it anyway. SQL Server Express will be totally free—no caveats. The product's only limitation will be that it won't use more than one physical processor—though the OS it's running on can certainly use more. However, you'll get more CPU juice on a multi-processor box with hyper-threading because SQL Server Express will use the additional logical processor that hyper-threading provides. Maximum database size is 4GB, and the buffer pool for SQL Server memory is limited to 1GB.

Are there open-source databases that will provide you with more theoretical scalability? Yes, but if you're running an application that requires more processing capabilities than a free version of SQL Server Express there probably isn't a free lunch for you. You'll pay whatever vendor you end up working with for that database processing one way or another. But SQL Server Express will present an honest-to-goodness free option for a huge number of small departmental database application needs.

You can put together staged benchmarks that showcase circumstances in which a free, open-source database can do things that MSDE or SQL Server Express can't. However, I suspect that most users who can benefit from a free, open-source database can also benefit from a free version of MSDE or SQL Server Express. I sense that some people look to open-source databases because they don't consider a free version of a Microsoft database as a viable candidate. I'm not saying that it's the best solution in all cases—there are cases in which an open-source database provides more bang for your buck, but it's unfortunate when customers think they need a free solution and wrongly assume that Microsoft doesn't have any story to tell in the database arena.

Free .NET or SQL Server Training CD!
See why AppDev was voted THE BEST by readers of SQL Server Magazine...get a FREE .NET or SQL Server training CD-ROM--a $95 value! Click the link below for your free CD-ROM.
    http://lists.sqlmag.com/t?ctl=3689:7B3DB

2. News & Views

Paul Thurrott reported today that the oft-delayed SQL Server 2005 product family will finally ship midyear but it will introduce a new product lineup and higher prices. For starters, Microsoft is adding a SQL Server Workgroup Edition. But the product won't wait for SQL Server 2005. Instead, Microsoft is launching SQL Server 2000 Workgroup Edition this spring, ahead of the SQL Server 2005 launch. The SQL Server 2005 product family will comprise four editions: Express Edition, Workgroup Edition, Standard Edition, and Enterprise Edition. Read Paul Thurrott's full article today at
    http://lists.sqlmag.com/t?ctl=367D:7B3DB

DBAs, developers, and business intelligence (BI) professionals can learn first-hand how to upgrade to SQL Server 2005 in a one-day intensive course coming to seven cities across the U.S. in April and May. The "Get Ready for SQL Server 2005 Roadshow," produced by SQL Server Magazine and the Professional Association for SQL Server (PASS) and sponsored by Microsoft, will include three content tracks. Scalability Experts trainers will lead the administration track, DevelopMentor trainers will lead the development track, and Hitachi Consulting trainers will lead the BI track. The roadshow is coming to Boston on April 19, New York on April 21, Anaheim on April 26, San Francisco on April 28, Chicago on May 12, Houston on May 17, and Dallas on May 19.

The roadshow will provide an in-depth look at the new SQL Server release from experts who have worked with the technology for months, according to Kathy Blomstrom, editor in chief of SQL Server Magazine. "Whether your main responsibility is administration, application development, or business intelligence, you'll get in-the-trenches instruction about every aspect of the upgrade process," says Blomstrom. "The roadshow will cover best-practices upgrade planning, building Reporting Services applications, new development technologies in SQL Server 2005, and many other topics that will prepare database professionals to hit the ground running when SQL Server 2005 is released."

The full-day course includes technical presentations by premier sponsors BMC Software, HP, and Imceda, as well as keynote presentations about SQL Server 2005 innovations by Microsoft technical representatives. The day will end with a user group party hosted by PASS that will give roadshow attendees an opportunity to learn about local user group resources. The registration fee for the roadshow is $99, which includes a full day of technical content, breakfast, lunch, the user group party, a one-year subscription to SQL Server Magazine, a one-year membership to PASS, an attendee bag, roadshow T-shirt, and the opportunity to win prizes from various sponsors.

For more information, including the complete agenda and registration information, visit the following URL:
    http://lists.sqlmag.com/t?ctl=3681:7B3DB

Microsoft has released a hotfix for a problem that significantly reduces SQL Server 2000 performance. When you use trace flag 9134 to prevent error message 601 in SQL Server 2000, SQL Server performance is significantly reduced. Error 601 is logged to the SQL Server 2000 error log and occurs if the page at the current position of the scan is deleted when you scan with the NOLOCK locking hint or with the transaction isolation level set to READ Uncommitted. The performance problem occurs because setting trace flag 9134 to TRUE causes additional reads to test whether the page is allocated and linked correctly. This check traverses Index Allocation Map (IAM) chains and Page Free Space (PFS) pages. This behavior causes the additional reads and slowdown. To find out more about this problem and the supported hotfix, read the Microsoft article "FIX: Performance is significantly reduced when you set trace flag 9134 to prevent error message 601 in SQL Server 2000" at
    http://lists.sqlmag.com/t?ctl=3682:7B3DB

"What's your favorite technique of accessing data from several tables?" Here are the results from the 232 votes:

"What open-source or free database products do you use?" Go to the SQL Server Magazine home page and submit your vote for

3. Events and Resources

Join Microsoft and our partners in Houston for an in-depth review of SQL Server 2005 and SQL Server 2000 technical presentations and customer success stories for database professionals at the Westin Oaks Hotel in Houston on March 17 from 7:30am-4:30pm. Register with Event Code 1032267447 at the following URL:
    http://lists.sqlmag.com/t?ctl=368D:7B3DB

Don't miss Microsoft's Tom Rizzo in his keynote presentation, "Getting Business Done with SQL Server 2005." Get your training directly from the source and be among the first to go inside SQL Server 2005. All attendees receive the latest betas of Visual Studio 2005 and SQL Server 2005.
    http://lists.sqlmag.com/t?ctl=368E:7B3DB

Get the facts about migrating to SQL Server 2005. SQL Server experts will present real-world information about administration, development, and business intelligence to help you implement a best-practices migration to SQL Server 2005 and improve your database-computing environment. Receive a 1-year membership to PASS and 1-year subscription to SQL Server Magazine. Register now!
    http://lists.sqlmag.com/t?ctl=3679:7B3DB

Sign up now for this free, 1-hour Web seminar and get a quick start in mapping Oracle database-management skills, knowledge, and experience to SQL Server database management. Learn about the varying similarities and differences between Oracle and SQL Server and get a preview of real-world tips and techniques for managing these associated technologies. Register now!
     http://lists.sqlmag.com/t?ctl=3670:7B3DB

See the complete Windows IT Pro Network guide to Web and live events.
    http://lists.sqlmag.com/t?ctl=368C:7B3DB

4. Peer to Peer

    by Brian Moran, [email protected]

Q. What's the difference between the following SELECT statements?

SELECT * FROM tableA, tableB WHERE tableA.a1 = tableB.b1

and

SELECT * FROM tableA JOIN tableB ON tableA.a1 = tableB.b1

Someone told me that first statement isn't ANSI standard, but a lot of my existing code uses that syntax. Do I need to change the non-ANSI standard SELECT statements? Does one format perform better than the other?

Read the answer today at
    http://lists.sqlmag.com/t?ctl=3677:7B3DB

Change control isn't just for application development. Database schemas need change control as much as any other component in your IT organization. Database schemas—tables, columns, data types, functions, stored procedures, views, and so on—aren't static and will naturally change to accommodate the varying needs of the organization. As a company targets new markets, expands its mission, or modifies a line of business, its database has to be able to support these endeavors, which sometimes means changes to the database. The trick is to manage those changes carefully. In her March article, "7 Steps to Database Change Control," Michelle A. Poolet gives readers 7 steps to get a handle on their database changes. Read this article today at
http://lists.sqlmag.com/t?ctl=3678:7B3DB

In this week's blog, "The Perfect Training Tidbit—Nuggets," Kevin Kline points to several very useful sources of training nuggets. A training idea that Kevin has long held in high esteem, but seen little efforts to create is the short training vignette or "nugget." Kevin likes this idea so much that he looks for opportunities to put them in place wherever they can be of benefit. He's proposed them as an idea for the PASS community and at Quest Software. See what else Kevin has to say and let him know what you think today at
http://lists.sqlmag.com/t?ctl=367A:7B3DB

Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.
http://lists.sqlmag.com/t?ctl=3683:7B3DB

DTS: DTS Size Limitations in SQL Server 2000
http://lists.sqlmag.com/t?ctl=3673:7B3DB
General Discussion: Specifying Row Terminator in DOS-level BCP Command
http://lists.sqlmag.com/t?ctl=3675:7B3DB
Administration: Procedure Cache Hit Ratio Low
http://lists.sqlmag.com/t?ctl=3674:7B3DB
T-SQL: Incorrect Results When Numeric Literals are Replaced
http://lists.sqlmag.com/t?ctl=3672:7B3DB
Security: Does a Job Owner Have to be an Administrator?
http://lists.sqlmag.com/t?ctl=3671:7B3DB
Development: Error Creating 3041 Alert
http://lists.sqlmag.com/t?ctl=3676:7B3DB

Hot Spot

DB Ghost For SQL Server DB Ghost builds directly from your source control system, validating your code as it builds, so the hassles of working with broken databases become a distant memory. DB Ghost can then compare and synchronize a target database giving you a perfect release. Every time. See for yourself:
    http://lists.sqlmag.com/t?ctl=3688:7B3DB

5. Announcements

Celebrate 2005 with the newest version of the SQL Server Magazine Master CD! You'll receive portable access to every SQL Server Magazine article ever written since 1999--online and in-hand on CD-ROM! As an added bonus, you'll also receive exclusive SQL Server 2005 content. Sign up now:
    http://lists.sqlmag.com/t?ctl=3685:7B3DB

If you haven't seen Exchange & Outlook Administrator, you're missing out on key information to help you migrate, optimize, administer, backup, recover, and secure Exchange and Outlook. Plus, paid subscribers receive exclusive online library access to every article we've ever published. Order now!
     http://lists.sqlmag.com/t?ctl=3680:7B3DB

6. New & Improved

    by Dawn Cyr, [email protected]

Have you used a product that saved you time or made your job easier? Tell us your product-success story, and if we print your submission in the Hands On department, we'll send you a SQL Server Magazine t-shirt. Send your product suggestion with information about how the product helped you to [email protected].

DTM Soft announced DTM DB Stress, a stress-testing tool for databases, database servers, and related applications. Database developers and administrators use the tool to create a nonstop flow of server queries in OLAP or OLTP. You can alter the quantity and priority of queries to see how applications and hardware handle heavy data traffic, multiple simultaneous connections, and competing queries and transactions. The tool comes with a test package that has an extensive set of database tasks (in the form of SQL statements). The tool lets you launch multiple copies of a task running in parallel on different threads, and you can see each task's priorities. Then, you can use the information you collect to estimate your system's maximum threshold or plan for system upgrades. DTM DB Stress supports various database formats, including SQL Server, Microsoft Access, Oracle, IBM DB2, and Interbase/Firebird. Pricing starts at $125 for a single copy, and a free demonstration version is available online. For more information, contact DTM Soft at [email protected].
http://lists.sqlmag.com/t?ctl=3687:7B3DB

RippleTech announced LogCaster for Sarbanes-Oxley, a pre-configured Sarbanes-Oxley Section 404 compliance solution. Nicknamed "SOX in a Box," the product is an intelligent event-log-consolidation solution that lets you quickly implement the internal IT audit and reporting controls necessary for compliance. An easy-to-use interface lets you select the reports you need about the control of financial data, ensuring compliance while identifying areas of potential risk. In addition, the product provides instant access to audit data and gives you the ability to report on controls to meet Sarbanes-Oxley requirements. You can get the reports on demand or automatically in various formats. The product provides security features including security-event-log management; filtering, alerting, and security-policy validation; instant access to security audit data for system and file access; and security-event-log consolidation. A free demonstration copy of the product is available for download. For pricing and other information, contact RippleTech at 866-739-8587, 610-862-4000, or [email protected].
http://lists.sqlmag.com/t?ctl=3690:7B3DB

AquaFold announced Aqua Data Studio 4.0, a database query and administration tool that lets developers easily create, edit, and execute SQL scripts as well as browse and visually modify database structures. Aqua Data Studio provides an integrated database environment with one consistent interface to all major relational databases, which lets DBAs and developers perform multiple tasks simultaneously from one application. New features in the latest release include Visual Editing for Security, which lets you use simple forms to visually create, alter, and drop security objects such as SQL Server logins, roles, users, server roles, object permissions, and column permissions. An enhanced, customizable Query Analyzer lets you view the SQL editor and results in a split pane. Query Analyzer scripts now support four new client-side commands that let you execute a stored procedure with local variables bound to the parameters. And a new Export Tool lets you export the contents of any table or the results of any SELECT statement and save the data in a delimited text file or a file with INSERT statements. The latest release supports the newest versions of all major database platforms, including SQL Server 2005, IBM DB2 8.2, PostgreSQL 8.0, Sybase Anywhere 9.0, and MySQL 4.1. Pricing for Aqua Data Studio 4.0 starts at $149 per license. For more information, contact AquaFold at [email protected].
http://lists.sqlmag.com/t?ctl=3691:7B3DB

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.
http://www.sqlmag.com/rd.cfm?code=00ep214xeb

Manage Your AccountYou are subscribed as #EmailAddr#. To unsubscribe from this email newsletter, click here /#Mailing:UnsubEmail

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 2005, Penton Media