Choosing the Right SQL Server Edition

SQL Server 2000 editions all have distinguishing features, but the lines between editions are blurred in SQL Server 2005 editions because of new capabilities in SQL Server Standard and Express editions.

ITPro Today

May 10, 2006

17 Min Read
ITPro Today logo in a gray background | ITPro Today

In This Issue:

-- Different SQL Server 2000 editions all have distinguishing features that make choosing the best product for your business straightforward. But the lines between editions are blurred in SQL Server 2005 editions because of new capabilities in SQL Server Standard and Express editions.

-- Microsoft Releases Hotfix for SQL Server 2005 SP1

Subscribe to SQL Server Magazine:
      http://lists.sqlmag.com/t?ctl=11522:7B3DA

=================================

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.

Quest Software
      http://ads.quest.com/SQLServerMagUpdateNLTopToadSQLServer051106

AppDev
      http://www.appdev.com/promo/RN00365

Patchlink
      http://a.gklmedia.com/pnsqlsm/nl/148

XOSoft
      http://www.windowsitpro.com/go/essential/xosoft/sqlserverha/?code=SQLHot0511

=================================

May 11, 2006

1. Perspectives
      - Choosing the Right SQL Server Edition: Not a
      Straightforward Proposition

2. SQL Server 2005 Watch
      - Microsoft Releases Hotfix For SQL Server 2005 SP1

3. News & Views
      - Microsoft Ships Vista Beta 2 Preview to Testers
      - Results of Previous Instant Poll: Streamlining UPDATE
      - New Instant Poll: Installing SQL Server 2005 SP1

4. Reader Challenge
      - May Reader Challenge Solution: Running SQL Server 2000
      Queries in SQL Server 2005
      - June Reader Challenge: Writing Efficient Queries to Return
      Inventory Items

5. Events and Resources
      - Get the Facts About Deploying SQL Server 2005!
      - Understanding the x64 Architecture
      - SQL Server High Availability Options
      - The Essential Guide to SQL Server 2005 High Availability

6. Featured White Paper
      - Your Guide to Exchange 2003 High Availability

7. Peer to Peer
      - Hot Tip: BOL Searches Beyond Online Help
      - Hot Article: Custom Data Processing Extensions for
      Reporting Services
      - In a Nutshell: Looking Back
      - Hot Threads

8. Announcements
      - SQL Server Performance Tips, Articles, and Forums
      - May Exclusive--Save $100 On the Windows IT Security
      Newsletter

9. New & Improved
      - Track SQL Server Database Dependencies
      - Manage Database Infrastructures with your Mobile Device
      - Tell Us About a Hot Product and Get a T-Shirt!

==== Sponsor: Qwest Software ====

Hit Your SQL Server 2005 Performance Target
      Aim for optimal SQL Server 2005 performance with solutions from Quest Software. Automate the time-intensive process of identifying, re-writing and benchmarking poor SQL with Toad for SQL Server Xpert. Isolate SQL Server stress factors with Benchmark Factory for Databases. Discover, diagnose, and resolve performance issues and database bottlenecks in real time with Spotlight on SQL Server. And forecast and trend storage requirements with Capacity Manager for SQL Server. Hit a performance bull's-eye. Quest Software has the tools to help you optimize your SQL Server 2005 platform. Download one or all of our free trial products now at:
      http://ads.quest.com/SQLServerMagUpdateNLTopToadSQLServer051106

1. ==== Perspectives ====

Choosing the Right SQL Server Edition: Not a Straightforward Proposition
      by Brian Moran, [email protected]

Are you "right-sizing" your choice of SQL Server 2005 editions? Lately, I’ve been fielding lots of questions from customers and readers who are asking, "Can I get by with SQL Server 2005 Standard Edition rather than paying many tens of thousands of dollars in extra license costs for SQL Server 2005 Enterprise Edition?" or "Can I get by with the free SQL Server 2005 Express Edition instead of paying for SQL Server 2005 Standard Edition?" These questions aren’t new. SQL Server 2000 customers ponder them too. However, the answer is usually more straightforward with SQL Server 2000.

When choosing a SQL Server 2005 edition, the first question many businesses face is whether to pay for SQL Server or try to get along with a free version. No one wants to pay more than they have to, but with SQL Server 2000, the answer was simple. MSDE, the free version of SQL Server 2000, didn’t include management tools and had a performance governor that made the product unsuitable for all but the most trivial of real-world needs or personal use. However, SQL Server 2005 Express Service Pack 1 (SP1) now offers a useful set of GUI administration tools. Running SQL Server 2005 Express on a fast dual-core server with 1GB of memory will give you a remarkably capable platform for lower-end production uses. SQL Server 2005 Express with Advanced Services even includes support for Reporting Services.

Likewise, choosing between Enterprise Edition and Standard Edition has become more complex with SQL server 2005. SQL Server 2005 and 2000 Enterprise Edition both include numerous features that the correlating Standard Edition doesn’t support. However, with SQL Server 2000, memory usage is a primary reason to upgrade to Enterprise Edition. SQL Server 2000 Standard Edition is capped at 4GB of memory. If you need more memory, Enterprise Edition is the only answer, regardless of whether you wanted or needed the other advanced features in Enterprise Edition.

But in SQL Server 2005, memory is no longer a primary decision-making factor, because the new Standard Edition of SQL Server supports as much memory as the OS can use. Standard Edition is still limited to four processors, whereas Enterprise Edition is limited only by the number of processors that the OS will support (Windows 2003 Server will support up to 64). Microsoft defines “processor” at the socket level, which means you can have a four-CPU dual-core box. That’s not quite as fast as a true eight-CPU machine, but it’s no performance slouch either. And heck, four-core boxes aren’t too far away. Does Enterprise Edition offer lots of nifty features that you might want, need, or otherwise be willing to pay for? Absolutely--Enterprise Edition offers numerous high-availability and scalability features not included in Standard Edition. However, the reality is that Standard Edition offers a tremendous amount of computing power and many customers who went to SQL Server 2000 Enterprise Edition for memory or processor power may no longer need to.

In case you’re wondering, no, I’m not going to tell what specific features in the various editions might influence your choice between Express and Standard or between Standard and Enterprise. I don’t have enough space left to even begin addressing that topic this week. This week is just about making you think. What version do you need? Can you get by with a less expensive version of SQL Server 2005? The answer might be yes.

I’m curious to know how many customers are debating these questions in their companies. I’d love to hear from you! Send me your thoughts on this topic at [email protected].

****Sponsor: AppDev ********** Receive a Free SQL Server 2005 Training CD-ROM!
      Start learning SQL Server 2005 today with cutting edge training from AppDev. Get a FREE SQL 2005 training CD (a $115 value!) taken directly from our new course. Click the link below for your FREE SQL Server 2005 training CD.
      http://www.appdev.com/promo/RN00365
******************************

2. ==== SQL Server 2005 Watch ====

Microsoft Releases Hotfix For SQL Server 2005 SP1
      Late last week, Microsoft posted a Hotfix for a problem that can occur when you install SQL Server 2005 Service Pack 1 (SP1) on a computer that's running SQL Server Integration Services (SSIS). If SISS was running on the computer before you installed the service pack, SSIS won't start and you'll see the error message "The service did not respond to the start or control request in a timely fashion" in the system event log. For more information about the hotfix, read the Microsoft article "The SQL Server Integration Services (SSIS) Service Does Not Start When you Install SQL Server 2005 SP1 on a Computer That is Already Running the SSIS Service" at
      http://support.microsoft.com/kb/918644

==== Sponsor: Patchlink ====

Automatically Analyze, Deploy and Track Security Patches
      Does your patch management solution automatically track and re-deploy to ensure network security? 20 percent of patches unknowingly become un-patched. Learn more about automating the analysis, distribution and tracking of security patches using PatchLink's security patch & vulnerability management solution--the world's largest repository of tested patches. Request a free trial disk.
      http://a.gklmedia.com/pnsqlsm/nl/148

3. ==== News & Views ====

Microsoft Ships Vista Beta 2 Preview to Testers
      by Paul Thurrott, [email protected]

Several beta testers reported to me early Saturday morning that Microsoft had issued 32-bit and 64-bit versions of Windows Vista build 5381.1, which is a preview of the Beta 2 version of Vista the company intends to ship later this month to millions of testers. I first reported that Microsoft would ship a variant of build 5381 as Beta 2 earlier this week when rumors surfaced that the software giant would delay Vista further into 2007.

Windows users who are eager to test Vista won't have much longer to wait: Currently, the company still plans to ship Beta 2 on May 22, and the company will eventually allow millions of consumers to obtain this release at little or no cost. You can find a screenshot gallery on the SuperSite for Windows. My next Vista review will coincide with the Beta 2 release.
      http://www.itprotoday.com/showcase/winvista_5381_gallery_01.asp

Results of Previous Instant Poll: Streamlining UPDATE
      "What part of SQL Server Magazine UPDATE would you cut if you had to?" Here are the results from the 50 votes:
     - 24% The table of contents
     - 2% The news/information stories
     - 33% The monthly puzzle
     - 2% Article summaries
     - 7% All of the above
     - 31% None of the above

New Instant Poll: Installing SQL Server 2005 SP1
      "Have you installed SQL Server 2005 Service Pack 1 (SP1) at your site?" Go to the SQL Server Magazine home page ( http://lists.sqlmag.com/t?ctl=3D1:7B3DA ) and submit your vote for
      - Yes, it's up and running
      - Yes, but I ran into a few glitches
      - No, but I intend to
      - No, I'm waiting for all the bugs to be worked out
      - No, I don't intend to

Editor's Note: As you can tell, we're looking at ways we can streamline SQL Server Magazine UPDATE to bring you the information you want and need. If you have specific suggestions, please feel free to email them to SQL Server Magazine senior editor Dawn Cyr at [email protected]. We respect your time and opinions, and we want to make SQL Server Magazine UPDATE valuable to you!

4. ==== Reader Challenge ====

May Reader Challenge Solution: Running SQL Server 2000 Queries in SQL Server 2005
      by Umachandar Jayachandran, [email protected]

Congratulations to Ahmad Bahr Mohamed and Len Binns. Ahmad Bahr Mohamed won first prize of $100 for the best solution to the May Reader Challenge, "Running SQL Server 2000 Queries in SQL Server 2005." Len Binns won second prize of $50. You can read a recap of the problem and the solution to the May Reader Challenge at
      http://www.sqlmag.com/Article/ArticleID/50276/sql_server_50276.html

June Reader Challenge: Writing Efficient Queries to Return Inventory Items
      Now, test your SQL Server savvy in the June Reader Challenge, "Writing Efficient Queries to Return Inventory Items" (below). Submit your solution in an email message to [email protected] by May 18. 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:
      Alex is a database architect who has designed an inventory application for his company. The application uses a SQL Server 2000 server to store the data, and the inventory database consists of two main tables: Suppliers and Items. The code below creates the tables containing the relevant columns and sample data.

CREATE TABLE Suppliers (SupplierId INT NOT NULL PRIMARY KEY,
      Weight INT
NOT NULL CHECK(Weight >= 0) UNIQUE)
CREATE TABLE Items (Item VARCHAR(50) NOT NULL,
      SupplierId INT NOT NULL
REFERENCES Suppliers(SupplierId), PRIMARY KEY(SupplierId, Item))

INSERT INTO Suppliers VALUES(1, 30) INSERT INTO Suppliers VALUES(2, 20)
INSERT INTO Suppliers VALUES(3, 10)

INSERT INTO Items VALUES ('item1', 1) INSERT INTO Items VALUES ('item1', 2)
INSERT INTO Items VALUES ('item2', 2)
INSERT INTO Items VALUES ('item2', 3)

The Suppliers table stores each supplier of a particular item in the inventory. The inventory application uses an algorithm to assign a weight to each supplier. If multiple suppliers provide a particular item, the supplier that has the lowest weight has higher priority. (You can set the priority according to a combination of factors--e.g., availability, customer feedback, price. In this challenge, the priority indicates the item's availability.)
      Help Alex write an efficient query that returns each item with the preferred supplier (i.e., the one with the lowest weight), and explain how he can use Query Analyzer tool to verify the various queries' efficiency.

5. ==== Events and Resources ====

Get the Facts About Deploying SQL Server 2005!
      Learn how to use SQL Server 2005's new capabilities to improve your database-computing environment. The SQL Server 2005 Roadshow is coming to Copenhagen. Register now!
      http://www.windowsitpro.com/roadshows/sqlservereurope/?code=0510emailannc

Understanding the x64 Architecture
      Learn the key differences in system architecture between the x64 and 32-bit platforms and the benefits of deploying 64-bit technology with Windows Server 2003 and SQL Server 2005. Industry expert Mike Otey presents his findings in this on-demand Web Seminar.
      http://www.windowsitpro.com/go/seminars/amd/understandingx64/?partnerref=0510emailannc

SQL Server High Availability Options
      Industry expert Mike Otey explains how to design high-availability options for your SQL Server 2005 environment. He'll also cover Windows clustering, database mirroring, and online operations. Live Event: Wednesday, May 31, 2006, 12:00 EDT
      http://www.windowsitpro.com/go/seminars/xosoft/sqlserver/?partnerref=

The Essential Guide to SQL Server 2005 High Availability
      Learn the essentials of high availability for SQL Server 2005, including cluster services, replication, and log shipping.
      http://www.windowsitpro.com/go/essential/xosoft/sqlserverha/?code=0510emailannc

====================

6. ==== Featured White Paper ====

Your Guide to Exchange 2003 High Availability
      Many things can affect your messaging system's availability, including component failure, power outages, operator errors, and natural disasters. This FREE white paper will help you plan and implement reliable strategies to maintain highly available Exchange Server 2003 messaging systems. Download this white paper today!
      http://www.windowsitpro.com/go/whitepapers/HP/exchange2003?code=0510featwp

7. ==== Peer to Peer ====

Hot Tip: BOL Searches Beyond Online Help
      Microsoft has changed the way that SQL Server 2005 "Books Online (BOL)" works. Now, after you search a topic in BOL, you can also search Internet sources if help wasn't available locally. Read this tip today and post your comments at
      http://www.sqlmag.com/Articles/ArticleID/49284/49284.html

Hot Article: Custom Data Processing Extensions for Reporting Services
      by Andrew Potter


      Are you spending time rewriting the same logic in multiple places? Have you wondered whether it's possible to share a data set across multiple SQL Server Reporting Services reports? Read this article today at
      http://www.sqlmag.com/Articles/ArticleID/49645/49645.html

In a Nutshell: Looking Back
      In this week's blog, Kevin Kline reminds us about an important SQL Server resource called the SQL Server Customer Advisory Team (SQLCAT) blog. SQLCAT members post entries pertaining to best practices and lessons learned from SQL Server implementations. There are 7 post categories that contain lots of helpful information. Access the SQLCAT blog at http://lists.sqlmag.com/t?ctl=19E6C:7B5CC and tell Kevin how helpful this information is today at
      http://lists.sqlmag.com/t?ctl=29211:7B3DA

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

SQL Server 2005 General Discussion: SQL 2005 Excel Import
      http://forums.sqlmag.com/web/forum/messageview.aspx?catid=82&threadid=47436&enterthread=y

SQL Server 2005 Administration: Moving Logins in SQL Server 2005
      http://forums.sqlmag.com/web/forum/messageview.aspx?catid=83&threadid=45245&enterthread=y

SQL Server 2005 Integration Services: Problem Using OLE DB Command to Update my Oracle Database
      http://forums.sqlmag.com/web/forum/messageview.aspx?catid=86&threadid=47387&enterthread=y

SQL Server 2000 Development: Comparing Tables
      http://forums.sqlmag.com/web/forum/messageview.aspx?catid=66&threadid=47449&enterthread=y

**** Hot Spot: XOSoft ****
Download this Essential Guide to learn the essentials of high availability for SQL Server 2005, including cluster services, replication, and log shipping.
      http://www.windowsitpro.com/go/essential/xosoft/sqlserverha/?code=SQLHot0511

8. ==== Announcements ====

SQL Server Performance Tips, Articles, and Forums
      Hundreds of free tips and articles on SQL Server performance tuning and clustering. And get quick and accurate answers to your performance- and cluster-related questions in our forum. All from the SQL Server performance and clustering authority: SQL-Server-Performance.com.
      http://www.sql-server-performance.com

May Exclusive--Save $100 On the Windows IT Security Newsletter
      For a limited time, order the Windows IT Security newsletter and SAVE up to $100! In addition to 12 helpful issues loaded with solutions you won't find anywhere else, you'll get FREE access to the entire Windows IT Security online article database. Subscribe now:
      https://store.pentontech.com/index.cfm?s=1&promocode=eu2565uy

9. ==== New & Improved ====
      by Blake Eno, [email protected]

Track SQL Server Database Dependencies
      Red Gate Software announced SQL Dependency Tracker, a graphical and interactive tracker for SQL Server 2005 and 2000 database dependencies. The product lets you view, analyze, and document all of your SQL Server database dependencies in real-time with six different layout modes. You can choose to view whole databases or selected objects. Additionally, you can save and export diagrams as pictures or as XML reports. Pricing for SQL Dependency Tracker starts at $295 for one user. For more information, contact Red Gate at 866-733-4283, [email protected], or [email protected].
      http://www.red-gate.com

Manage Database Infrastructures with your Mobile Device
      Idera announced SQL mobile manager 2.3, a remote-management and administration solution for SQL Server environments. Using a Windows Mobile Device, you can securely monitor and manage your SQL Server infrastructure. The product provides you with real-time and historical performance metrics such as users, processes, batches, transactions, network traffic, and disk I/O. Configurable alerts are available for CPU utilization, database growth, network traffic, failed database jobs, and blocked processes. In addition, you can execute T-SQL commands, start and stop a job, or reboot a server. Pricing for SQL mobile manager starts at $795 per SQL Server instance. For more information, contact Idera at 713-533-5144, 877-464-3372, or [email protected]
      http://www.idera.com

Tell Us About a Hot Product and Get a T-Shirt!
      Have you used a product that changed your IT experience by saving you time or easing your daily burden? Tell us about the product, and we'll send you a T-shirt if we write about the product in a future Windows IT Pro Magazine What's Hot column. Send your product suggestions with information about how the product has helped you to [email protected].

==== Contact Us ====

About the [email protected] About SQL Server [email protected]
About technical questions--http://www.sqlmag.com/forums
About product [email protected]
About your [email protected]
About sponsoring SQL Server Magazine UPDATE--Richard Resnick, [email protected]

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.windowsitpro.com/rd.cfm?s=9&code=eu215xsL

Manage Your Account You are subscribed as %%$email%%. To unsubscribe from this email newsletter, click here
      http://lists.sqlmag.com/u?id=%%SUBSCRIBER_ID_TAG%%

To manage your email account, simply log on to our Email Preference Center.
      http://www.sqlmag.com/email

View the SQL Server Magazine Privacy Policy.
      http://www.sqlmag.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 2006, Penton Media, Inc. All Rights Reserved.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like