Executive Summary: Microsoft SQL Server is a complicated product that has features that can seem confusing or annoying. When you encounter SQL Server database behavior that could be a bug, you should report the problem to Microsoft by using the Microsoft SQL Server Connect website. SQL Server 2008 comes in eight editions, but only the Enterprise edition has the complete SQL Server 2008 feature set. SQL Server 2008 has three basic licensing options: Server plus device CAL, Server plus user CAL, or Processor license.

It’s an understatement to say that Microsoft SQL Server is a complicated product. It can take months, if not years, to fully master it. Until you reach a certain level of understanding about the methodology of SQL Server, there will be aspects that can seem confusing, if not annoying. DBAs who stumble across something that annoys them typically find a workaround to accomplish the same goal. Eventually such workarounds become second nature, and the annoyance is forgotten.

As Pete Kavanagh, one of the forum pros on the SQL Server Magazine forums (sqlforums.windowsitpro.com/web/forum/default.aspx), mentioned to me, “I developed alternative habits to get around them (annoyances), so it is difficult to remember what they were.” Russell Bloom, another SQL Server Magazine forum pro, wondered if changes in the query editor’s tab settings between SQL Server 2000 and SQL Server 2005 counted as an annoyance; after all, he easily rectified that problem by altering a couple of SQL Server Management Studio settings. Nonetheless, we can all agree that some things about SQL Server 2008 are definitely annoying.

Learning Path

For more articles and commentary about SQL Server 2008 from SQL Server Magazine:
“What Readers Want SQL Server to Include,” Instant- Doc ID 98866
“SQL Server 2008: Which Edition’s Right for You?” InstantDoc ID 100066
“Microsoft Launches the SQL Server 2008 Experience,” InstantDoc ID 100438

For more articles about IT annoyances:
“Deploying Office 2007 and Managing VPNs with Group Policy,” InstantDoc ID 100667
“How to Solve 6 Aggravating Office 2007 Behaviors,”
“Should You Use Windows Server Backup?” InstantDoc ID 100683
“Fixing Network Problems,” InstantDoc ID 100660
“Ease Security Headaches,” InstantDoc ID 100724
“Bridging SharePoint’s Faults,” InstantDoc ID 100740
“Exchange 2007 Shortcomings,” InstantDoc ID 100620
“Windows Vista Annoyances,” InstantDoc ID 97490
“Avoid Active Directory Pain,” InstantDoc ID 97611
“Problems with Permissions,” InstantDoc ID 97619
“Manage Those Pesky Patches,” InstantDoc ID 97551
“File and Print Annoyances,” InstantDoc ID 94675
“Group Policy Annoyances,” InstantDoc ID 94618
“DNS Annoyances,” InstantDoc ID 94456
“Security Annoyances,” InstantDoc ID 94414

SQL Server Bugs
There are things about SQL Server that some users see as unusual and others see as expected behavior. If you read some SQL Server bug reports, you’ll find that the reported behavior is often “by design” rather than by accident. These behaviors might seem maddeningly illogical to some people and perfectly straightforward to everyone else. For example, when you query a view that uses the TOP (100) PERCENT and ORDER BY clauses in SQL Server 2000, the results are returned in the order specified by the ORDER BY clause. But when you queried the same view in SQL Server 2008 or SQL Server 2005, the results were returned in random order. People have coherent arguments about whether a random or ordered output should result. Some argue that the SQL Server 2000 behavior was the problem, others that it made no sense to change this behavior in later releases. The debate was ended only when Microsoft released a hotfix to bring the behavior of SQL Server 2008 and SQL Server 2005 in line with that of SQL Server 2000 (see support.microsoft.com/kb/926292).

That said, bugs do exist, and they are annoying. When you encounter database behavior that doesn’t conform to your expectations and hence might be a bug, you have an effective method of reporting this information to Microsoft by using the Microsoft SQL Server Connect website at connect.microsoft.com/SQLServer. You can check whether anyone else has reported the bug and, if so, if Microsoft has released a solution or patch for it yet. If no one has reported it, you can report it yourself. Bugs that impact many users attract a larger number of user ratings and are assigned a higher priority for resolution by the SQL Server team.

Too Many SQL Server 2008 Editions
Choice is good. Too much choice can lead to confusion. Although the abundance of SQL Server 2008 versions is understandable, it can also be frustrating. Ignoring the variations for processor architecture, SQL Server 2008 comes in eight editions:

  • Enterprise
  • Standard
  • Developer
  • Workgroup
  • Web
  • Express
  • Express with Advanced Services
  • Compact

Although each of these editions is targeted at a different market, it can take you a while to work out which edition best meets your organization’s needs. When you have only a few versions to choose between, your decision should be relatively straightforward. When you have to weigh up the pros and cons of eight different editions, the decision will take you more time.

Many DBAs take the approach that it’s probably safest to go with the Enterprise edition—that way, they won’t be blamed if they later find they need a feature or licensing option that isn’t included in one of the other seven editions. Just because your organization doesn’t need a particular feature today doesn’t mean that it might not need that feature tomorrow, the Enterprise edition is the only one that includes the complete SQL Server 2008 feature set.

Furthermore, any savings gained by purchasing the most appropriate edition of SQL Server for an organization might be lost in paying the DBA to work out precise needs in advance and then matching those needs to the edition of SQL Server 2008 that best meets them. It isn’t necessarily beneficial to spend a stack of billable hours only to save a couple hundred bucks in selecting the perfect edition. (Of course, this sort of task might be a good money spinner for those on the consulting side of things.)

Although there are reasonable arguments about why each edition is available, reducing the number of editions would make the DBA’s task of deciding which edition to deploy a lot easier. If that task is easier, the DBA is a lot less likely to make a mistake when attempting to complete it. The more choices you give people, the more likely it is that some of them are going to make the wrong one.

Complicated SQL Server Licensing
Ensuring that SQL Server is properly licensed can be quite challenging—especially now that hosting SQL Server instances in virtual environments is far more common. Searching Microsoft SQL Server Books Online (msdn.microsoft.com/en-us/sqlserver/cc514207.aspx) for the term licensing reveals little that’s illuminating. You have to dig around Microsoft’s website to find any details about how you license SQL Server 2008, and even then you might not come up with a solution that licenses the product in the most economical way.

There are three basic ways to license SQL Server 2008: Server plus device CAL, Server plus user CAL, or Processor license. The bird’s-eye view is that you choose Server plus device CAL if multiple people use a specific set of devices to access your SQL Server instance. Choose Server plus user CAL if specific users use more than one device to access your SQL Server instance, and choose a Processor license if you want to license an unlimited number of users and devices to connect from inside or outside the firewall. Although you can find the costs of each license type on Microsoft’s website at www.microsoft.com/sqlserver/2008/en/us/pricing.aspx, the prices listed are estimates because they vary depending on the number of licenses you buy. Your organization can negotiate volume license agreements with Microsoft.

Adding to the licensing complexity, the Workgroup edition has its own CALs. You can use Workgroup CALs only with Workgroup edition servers, but SQL Server 2008 CALs can be used with any SQL Server 2008 server. You also have to ensure that all devices that indirectly access SQL Server on behalf of a client are licensed. Microsoft says that a CAL is required no matter how many levels of hardware or software exist between SQL Server and the client device. When you reach a certain level of abstraction, however, it can be difficult to figure out how many devices are actually indirectly interacting with the server.

Continue to page 2

And then we arrive at the trickiest of licensing situations: What do you do about virtualized instances of SQL Server 2008? The Workgroup and Standard editions require at least one license per virtual operating environment. The SQL Server 2008 Enterprise edition license works differently from other editions when run in a virtualized environment. If you’ve licensed SQL Server 2008 Enterprise edition with one of the Server plus CAL options, you can run any number of SQL Server instances on separate virtual OSs as long as they’re on the same physical server. If you have the Enterprise edition with the Processor license, you can run unlimited instances only if all physical processors have been licensed. If that gives you a bit of a headache, don’t even bother looking into what goes on when you start using products such as System Center Virtual Machine Manager 2007 to transfer virtual machines (VMs) between hosts.

The further you look into it, the more complicated licensing becomes—which is probably why a lot of DBAs decide that the cost of tracking compliance will be significantly reduced by going with a Processor license. To reduce confusion about licensing, Microsoft should offer a licensing advisor application to accompany the Upgrade Advisor and other planning tools you can run before installing SQL Server 2008. An application that monitors license compliance might be useful as well, though it would probably be difficult to track licenses for clients that indirectly access SQL Server. More useful—and easy-to-find—documentation on licensing from Microsoft wouldn’t hurt either.

A Complex Application
Although the problems I touched on are in no way deal breakers, the complexity around licensing SQL Server 2008 and the variety of edition choices are things that can be annoying. DBAs have to worry about a whole host of other problems when upgrading to SQL Server 2008, so Microsoft should make deciding which edition to select and how to license it as simple as possible