SQL MythBusters – MSDE/SQL Express has a 5 concurrent user limit

Oh man, if I had a quid for every time this has been answered in the newsgroups or at conferences it would be time to retire for sure! While validating this myth does not require another history lesson it might be useful.

 

MSDE 1.0

The first version of MSDE was launched at TechEd in Dallas in 1999, the goal for MSDE at the time was to provide a replacement for JET (the publicly consumable version of which was being deprecated, there are 2 Jets at Microsoft, one that is used and confused with Access and the other is used by Exchange and others) and also to support a seamless strategy for ISVs where we could provide a single API and store for them that supported an application transition from the desktop to the high end of the enterprise(to this end it was possible to detach/attach, backup/restore MSDE databases to SQL Server Enterprise Edition).

 

In the initial version there were not a lot of MSDE specific features, limitations were put in around memory, processors etc, and the most widely known/misreported “feature” was the workload governor.

 

The governor was designed to kick in when more than 5 operations were occurring in the server. What’s an operation? Simply put it’s a unit of work, something happening for reads/writes etc to occur. Once more than 5 of these are occurring a wait is introduced in these operations that effectively slow down the engine. (There is a much better explanation linked below).

 

In theory MSDE supports the same number of connections as SQL Server – 32,767 HOWEVER, it’s almost certain the server would run out of memory (remember there is a memory limit) before allocating that many connections, and due to the governor they would not all be able to run at the same time. So how many can run? That’s much like asking how long a piece of string is, I have heard of badly behaved apps with one connection hitting the governor, I’ve also heard of 100 users connected. In reality it all depends on the app and how it uses the server, a good planning number for a well written app that gets in and out of the database quickly would be 10-15 connections, however mileage really does vary.

 

I will say that not everyone at Microsoft made this clear when explaining it, the “5 operation” explanation was a little complex to get over, so many people said it was 5 connections as that was easier to explain, and hence the myth started…

 

MSDE 2000

While MSDE 1.0 was introduced after SQL 7.0 was released, MSDE 2000 was released at the same time, with some shiny new/unique features.

 

We wrote a new setup that was MSI (Darwin) based as opposed to the Installshield based installer we used for the other SKUs of SQL Server 2000. This was done primarily at the request of the office team as they only supported component installs via MSI/MSM based technologies. This also offered a cleaner way for ISVs to embed MSDE in their install (more of this later).

 

In addition we introduced an easy way to discover if the governor had kicked in (via DBCC CONCURRENCYVIOLATION) to help you decide if it was time to upgrade to a more full featured SKU. The unique feature here is it shows historical values, where by the event log and error log entries are more real time in nature.

 

We also made a change to the governor that led to even more confusion…One of the problems with the 5 operation limit in MSDE 1.0 was replication. If you were using MSDE to replicate to a server from MSDE then replication was using (approx) 3 of the operations to maintain itself, leaving only 2 for other applications. Now this caused problems and was not really what we intended, so in MSDE 2000 we upped the operations limit to 8, providing the functionality that we originally intended, which was 5 USER operations. But we faced a problem of how to message that, in the end we choose not to mention it as we were delivering on our original promise, however technical correctness overrode marketing spin and the developer that implemented the feature changed the event log entry and the DBCC command to report based on 8. Hence people actually hitting the limit kept seeing references to 8 not 5 and were confused, this confusion added to the operations vs connections confusion and a lack of documentation/formal explanation from us led to the myth perpetuation. As a side effect of the move to 8 I’d guestimate the connection limit as more like 15-20 compared to the 10-15 for MSDE 1.0.

 

Despite this, MSDE2000 REALLY took off, and I believe part of the reason for this was the multi instance capability, and the isolation this achieves, provides a lot of confidence for folks, especially ISVs. Soon we had 100s/1000s of apps using MSDE. However there was another confusion factor in addition to the governor and that was who was actually entitled to redist MSDE and under what rules. Unfortunately this was far more complex than we intended, more restrictive than we intended and varied by how you got your copy of MSDE (basically through SQL Server, Office or Visual Studio the agreements were all different).

 

Hence part of the way through MSDE’s life we decided to try and simplify both of these confusion points.

 

1/ We wrote a White Paper on the governor, how it worked how to deal with it etc, you can still find that paper here.

 

2/ We changed the redist rights to allow anyone to download, use and redist MSDE at will, we do require you register with us if you want to redist, but if you want to use there is no registration required..

 

The other thing we changed partway through the life of MSDE 2000 was we stopped shipping it as a set of MSMs as well as an MSI. Now I know that this has caused problems for some people but it really is for the best. The reason is somewhat involved (almost as involved as an explanation of the governor) however here is a simple explanation;

 

MSI was never designed to be used for Multi Instance (in fact to the best of my knowledge there is no other app in Microsoft that supports the same notion of Multi Instance that we do) and as this was going to be one of the headline capabilities of MSDE 2000 we had to find a way to handle it. The mechanism is complex but it manifests itself as the 16 sqlrunxx.msi files that exist for MSDE2000. Each one of these maps to an instance (SQL Server 2000 has a limit of 16 instances) with a unique product code. Now if you choose not to use one of these MSIs to embed/install with your app and instead go with an MSI of your own with embedded MSMs and a product code of your own, then there is a problem. That problem is patching and servicing, this is somewhat by design due to the nature of the plumbing but not really desired and can be a real problem. Hence we no longer release the MSMs for MSDE and will not be doing so for SQL Server Express.

 

SQL Server Express Edition

When we started looking at what to do for “MSDE” in SQL Server 2005 we changed our approach and some of our fundamental assumptions. First of all we created a dedicated team derived mostly from the SQL Server Mobile team in Redmond (up until now MSDE had been a virtual/part time team). This team did an in-depth analysis of the overall space and also the desires of our partners in this space, mostly ISVs and Visual Studio.

 

One of the first decisions out of this group was that the Governor had to go. It was simply causing too much confusion, but we had to find a way to limit “MSDE 2005” hence the memory and proc limits came in. We upped the DB size limit from 2GB to 4 GB as that was just a reflection on how much data was being generated and was useful to customers.

 

Let me repeat for clarity THERE IS NO WORKLOAD GOVERNOR IN SQL SERVER EXPRESS EDITION.

 

Now some of the harder work started, first up was naming, MSDE had some brand recognition behind it, both good and bad, so we were torn on what to do here however in many ways this decision was made easy as we knew that the VS team was working on their low end solution and “MSDE 2005” was going to be a key component of this, once they decided on the name Express that seemed like a good plan to follow.

 

Next up was size/footprint, this presented a huge challenge, in case you had not noticed yet SQL Server 2005 is a vast product, there is more of everything, including disk space requirements. Based on user research the original goal for SQL Server Express was for it to be a 35MB download, well when we built the first version we had something of a shock coming, my memory is a little vague but I seem to remember the download being 150MB and it taking 400MB on disk! Thus started the SQL Express diet, led by Dave Nettleton we looked for everything that could be taken out of Express, we looked for optimizations, new compression algorithms, you name it we looked. It was not an easy task, in SQL Sever 2000 sqlserv.exe is a shade under 9MB, in SQL Server 2005 it’s a tad over 28MB. So we had to be very very aggressive, as part of this exercise SQL Agent and DTS were removed along with some other aspects of the product.

 

Over the course of a number of months we got very close to our goals for size and footprint and during that time expectations changed a little so we felt we succeeded.

 

The penultimate big change was in the GUI aspects, as MSDE was originally designed as ISV embedded store we did not provide a GUI. WOW did we get a lot of feedback on that topic…Hence we chose to reverse that decision and do something in the GUI space, but once again we looked at the problem from scratch so our options were basically;

 

1/ A Web based tool that would run locally under Cassini like Web Matrix and the sample Web Tool already did.

2/ Build a cut down Management Studio.

3/ Build a new tool from scratch (Express Manager).

 

We settled on 3 for a variety of reasons but the 2 key ones were size (feedback at the time was that this thing needed to be < 5MB download) and we could provide a more appropriate experience vs SSMS which is very much an Enterprise focused tool.

 

Eventually we shipped a CTP of XM, which for the most part was received ok, but I feel got a bad rap. That initial CTP did everything we wanted it to do; we showed the new concept dialog (compare the number of options on the SSMS Create DB Dialog with the 3 on the XM Create Database Dialog), we had prioritized a query editor so that folks could do “everything” using the fall back of script. However lots of folks thought this was all that XM was going to be and based on that and other factors, the team switched plans to deliver SSMS-E (SQL Server Management Studio – Express Edition) instead.

 

Having done all this only one really large piece of feedback remained unaddressed and that was the aspect of JET that kept deployment very simple, the single file that could be located anywhere. This presented quite a challenge as a file based DB and a service based DB are VERY different, however the team found a solution in what we call User Instances. These are worthy of several blogs all by themselves hence I’ll not repeat the content here.

 

All of the above brings us to SQL Server 2005 RTM/Today, but the team has not stopped listening and thinking about this space, watch out for Express Advanced Services in the coming weeks, more starter apps and more info on express.

 

Ok now back to the origin of this stream of consciousness…

 

Conclusion: Myth Busted! But we (Microsoft) take the blame for the confusion on this one.