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.

Comments

• Anonymous
  March 09, 2006
  Great article!!!  If only I'd known then what I know now...
  
  Thanks

• Anonymous
  March 09, 2006
  And what are the limits with SQL Server 2005 Developers Edition? We could not find that documented. If it was in the comparison of editions page, it has been removed.
  -- Craig Bolon

• Anonymous
  March 09, 2006
  There are no limits in terms of memory, db size or procs for DE, it is essentially Enterprise Edition.
  
  There is however a licensing restriction that prevents it from being used in production.

• Anonymous
  March 10, 2006
  Is there any chance that MSDE 2000 will be revisited and the governor tossed out? 8 is better than 5 yes. But it's still a major limitation. Can't see any good reason to keep the old limit in place with Express available (except that MS may not want to spend the resources on a MSDE rev). There are reasons to use MSDE 2000 over SQL Server Express, one being that Access 2003 and back support MSDE and as far as I'm aware will never support SQL Server Express. I am a full time Access developer and I've not interfaced with a single other Access developer that likes what Microsoft is doing with the new version of Access. It may mean that Access 2000-2003 remain the most viable platforms for Access devs. Loosening MSDE restrictions would help.
  
  I know that would not seem to earn any $ for Microsoft. But have to ask. It's not about being willing to spend $ on developer tools, I'd be willing to pay much more than I have been (I buy Office dev and VS Pro) for dev tools (not licensing for deploys, different issue). But unfortunately Microsoft does not build the dev tools that I really want. The product I really want (enterprise level db tool based on the ideas present in Access since early 90s), you never build. No one does...pretty strange, but true.

• Anonymous
  March 10, 2006
  
  Some cool white papers about the architecture of Microsoft.com
  More Blog Skins for CS 2.0
  SQL Express/MSDE...

• Anonymous
  March 10, 2006
  Michael S,
  Unfortunately it is EXTREMELY unlikely that we will remove the governor from MSDE2000. However thanks for the feedback and I have sent it on to folks in the Access and Visual Studio Teams.
  

• Anonymous
  March 13, 2006
  If don't frequent Euan Garden's BLOG and you're interested in a history lesson on the Workload Governor...

• Anonymous
  March 13, 2006
  Euan,
  Great read!
  
  I was one of those who also felt that XM felt too limited. I remember the first thought when I fired up XM was: is that all.
  
  No complaints about SSMS-E. It serves all my needs, and more. I use it on my development machine to connect to our SQL Server 2000 instances, purely because I prefer the integration, organization and the look & feel of the UI over EM/QA.
  
  Waiting for the final version of SSMS-E, though :)
  
  Regards,
  Phylyp

• Anonymous
  March 16, 2006
  We have a huge visual basic 6 application with about 200 classes and reports. We plan to port this to .net but cannot do it now.  
  
  Today, however we need a good client server database. This application is currently using access 2000 on the backend.
  
  We want to use sql express 05 but we want to utilize it in a network environment (2-10 systems) via Visual Basic 6 technology ADO 2.8 using sql native client provider.
  
  It seems nobody will tell us if we can use this from Visual basic 6 using ADO 2.8.
  
  Sooo .. can we run  a small network (10 systems) using ADO 2.8 from VB6?
  
  
  

• Anonymous
  March 16, 2006
  Fergus,
  This should work fine, but I suggest a prototype first to make sure you are not going to be constrained by memory or CPU.
  
  You could use profiler to capture your normal query load and then use replay, potentially from multiple clients, to simulate the load.

• Anonymous
  March 16, 2006
  
  Some cool white papers about the architecture of Microsoft.com
  More Blog Skins for CS 2.0
  SQL Express/MSDE...

• Anonymous
  March 17, 2006
  Euanga,
  
  thank you for the comment.
  
  We are going to give it a shot and see how it works.
  130 companies nationwide will be receiving the new sql express 05 so i hope it works out well for them.
  

• Anonymous
  March 18, 2006
  Hello Euanga,
  
  I am using MSSql Express 2005 (uninstalled MSDE and install MSSql express 2005) and MSAccess 2005.
  
  I have an ADP that I have pointing to a SQL Express 2005 db  (a db made with MSDE and now held by SQL Express 2005) and when I open a view I get a warning :
  
  "this version of ADP doesn't support design changes with the version of MSSQL Server your Access project is connected to. See the Microsoft Office Update web site for the latest info and downloads. Your design changes will not be saved."
  
  I check Update site and no update for Access 2003 for support to MSSql 2005?

• Anonymous
  March 19, 2006
  Luke,
  At this point there will not be an update to suppoirt ADP/Visual Designers in Access 2003 working against SQL Server 2005(any SKU its not unique to Express)

• Anonymous
  March 21, 2006
  Everyone seems to be glancing over the fact that SQLExpress 2005 no longer has DTS.  My question is how do you easily import and export data if there is no DTS.  I use DTS regularly to convert data from existing databases into SQL.

• Anonymous
  March 22, 2006
  Interesting article.
  
  I'd be interested to read something about how instancing will be implemented in SQL 2005 Express. You mentioned "user instances". Will they be limited in the same way MSDE was to 16 per machine?
  

• Anonymous
  March 28, 2006
  Great article. I've never been a heavy user or anything, but I did know about the limit in msde so I was always put off a little by that. I thought it had carried over into SQLE but now that I know it hasn't I think I'm going to give it the ole installeroni :P

• Anonymous
  March 28, 2006
  Steve B,
  Its a good point about DTS being missing, our answer would be to use open rowset technologies.
  
  You are not the first person to bring this up and we have been reviewing the decision but at this point no change

• Anonymous
  March 28, 2006
  Paul,
  I'll try and dig out more info on the User Instances, I don't believe they inherit the limitation of 16 but I would not try and run that many User Instances on a machine.

• Anonymous
  March 28, 2006
  Robbie,
  Hope you enjoy the experience, remember to check out the express blog; http://blogs.msdn.com/sqlexpress

• Anonymous
  March 30, 2006
  Euan,
  
  You have mentioned in one of the posts above that "There is however a licensing restriction that prevents it from being used in production ".
  
  Can you comment further on that?
  
  What if I write and sell an application that uses SQL Express and my customer deploys that in their production environment. Does the licensing restriction apply? If yes then it pretty much makes SQL Express usless for any serious development. I don't rememeber a similar restriction on MSDE... or was there ?

• Anonymous
  March 31, 2006
  He was talking about SQL Server 2005 Developer Edition, not SQLExpress...

• Anonymous
  March 31, 2006
  HSD,
  Dario is correct I was referring to Developer Edition not Express Edition.

• Anonymous
  April 01, 2006
  Yes. We are getting  ready to roll out sql express in production to about 150 businesses nationwide.  

• Anonymous
  April 10, 2006
  The comment has been removed

• Anonymous
  April 11, 2006
  Joe,
  I think this can help;
  
  http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
  

• Anonymous
  April 23, 2006
  
  Some cool white papers about the architecture of Microsoft.com
  More Blog Skins for CS 2.0
  SQL Express/MSDE...

• Anonymous
  May 01, 2006
  Nice to learn that it's 8 and not 5 operations that are managed.    Seeing that MSDE allows 16 instances, and the governer is activated per Instance, not database, does this mean using a distributed query, you could concievable use all 16 instances, in turn if well planned gain a total of 128 operations collectively before the governer starts it's horrid reign ? Wow if so, that beats my current use of a loophole I discovered to get me past the 2 GB limit per DB.
  
  For those that are not aware, for example: I have been using MSDE 2000 with Distributed Queries (e.g. Homogeneous Queries), I have discovered burried in the EULA for MSDE that you can indeed use distributed queries on the same machine, even across multiple instances and through a good bit of research I found you can include a total of 255 UNIQUE database references inside a distributed query.  That's 255 x 2 GB folks, keep in mind you'll have to implement and abstraction layer to keep your 'virtual' DB ERD 'as if' nothing has changed.  
  
  What does this mean ? At 2 GB limit per DB PER Instance (Just talking one instance here) I can now query across 510 GB (255 unique DB's x 2GB) of data in one single query using MSDE,  FAR beyond the 2 GB limit and all legal and legit.  
  
  Was this loophole intentional ? I've been considering releasing an ADO interface clone to allow for transparant operatbility to shops that otherwise would need to move to the Standard licensing after they hit 2 GB limit.
  
  I've worked for shops that didn't know about Distributed Queries and in turn levvied a SQL Standard licensing price to client when MSDE + distributed/homogeneous queries would have done the trick for free, sheesh, 1/2 Terabyte of data available in one query  ?  for free ?  I ask ? What 2 GB limit !
  
  My current solution maintains several metadata dictionaries to construct the 'real' SQL behind the scenes to reach across what currently uses AAA, AAB etc for DB's.  The entire management of the real underlying DB's is transparant through an infrastructure I implemented to make sure I stay sane.  My top level queries look like there exists one atomic DB, when in reality, it's a slew of 2 GB AAA, AAB, AAC behind the scenes.
  
  Is MSDE 2000 going to be supported in the future ?
  
  I have invested too much time for solutions that work to migrate to .net at this point, MSDE 2000 is just perfect for my work, as SQL Express mandates .net I don't want anything to do with it, can't afford another surprise storm of abandonment of working technologies to take the risk again.  
  
  
  
  

• Anonymous
  May 10, 2006
  I am in the process of converting a SQL 2000 server to SQL 2005. However there are roughly 20 Access adp's running against the server.
  
  What do I need to do to convert the adp to the SQL 2005 server. Will they run correctly
  
  Thanks
  
  Dave

• Anonymous
  May 16, 2006
  i use MSDe in my .net application as a backend and from my experience only 5 connestion are avalilable but if unlimited connections also applied then why i use SQL Server 2000 why not MSDE 2000 bcaz MSDE 2000 is freely avaliable if unlimited connections are apllied then how is it possible explain it porperly

• Anonymous
  May 17, 2006
  SQL 2000's MSDE has a 5 worker thread limit.  That is NOT 5 concurrent users.   Many people mistake "5 worker threads" with "5 users" and they are not the same at all.  The limit is designed to throttle down to t threads working at the same time.. in other words, you can't have 20 threads running at once.  You could have 20 users connected and most of them sleeping.  
  
  Note that the majority of the time, your connection is idle.. when you want data, it 'works', gets you data, and 'sleeps' again. A Sleeping thread doesn't count agaisnt your "5 worker thread" maximum.
  
  SQL2005's SQL Express has no such limitation.  The only limitations are memory and cpu limitations, as well as db size limitations.
  
  Of course the db size limit is a worthless limit in that you can easily code such that you partition your db objects in various DBs.
  
  HTH
  Eric

• Anonymous
  May 25, 2006
  Hi all,
  sorry if I sound dumb, but here is how i see it:
  If I purchase MS SQL Server Developer edition 2005, I get, in its EULA, the right to redistribute, deploy the following:
  
  (a) MSDE (sql2ksp3.exe) - which has limits - but it'll be running on a single PC with a single user (or may be 2-3), per customer that buys my contacts/billing/inventory system and they dont need a license and I dont need a deployment license.
  
  (b) SQL Server 2005 Express - which has lesser limitations.
  Again, my customers and I both dont need licenses.
  
  So i'll be able to do all that with the one single purchased developer edition license.
  
  Am i right?
  
  TIA,
  Martin
  

• Anonymous
  May 25, 2006
  Martin,
  You do not need to purchase Developer Edition to get redist rights, all you have to do is download MSDE/SQL Express.
  
  I do however recomend buying DE so that it is easier to develop for.

• Anonymous
  May 25, 2006
  Dave,
  ADPs will run against a 2005 server but the visual design tools will not work in read/write mode, only read mode.

• Anonymous
  May 25, 2006
  Tim,
  SQL Express does require .Net 2.0 to be installed but you can use it from native (ie non .net) apps just like MSDE, I recomend using express over MSDE.

• Anonymous
  January 01, 2007
  Welcome to 2007! As seems to be the trend here is the list of most popular posts for the last year, I

• Anonymous
  August 01, 2008
  PingBack from http://lacie.freepicsinfo.info/msde10.html