What are the limitations of MSDE?

What are the limitations of MSDE?

All SQL Server 2000 versions (as well as several other Microsoft products; see KB #324998) ship with SQL Server 2000 Desktop Edition, often referred to as MSDE (Microsoft Data Engine) 2.0. Essentially, MSDE is a version of SQL Server that you can distribute with applications. As such, it is slightly lighter weight, and has some notable restrictions. Other than the following list, however, MSDE and the other editions of SQL Server have very similar behavior and performance characteristics (see Article #2345 for a technical comparison). 

• an MSDE database can be no larger than 2 GB (this includes MDF and NDF files only — log sizes are not included in the size limit); 
   
  
• performance throttling occurs when there are more than five (5) concurrent workload batches in progress (this does *not* mean 5 concurrent *users* — with a properly build application, you can certainly support more. For more detailed information about the query governor, see this MSDN topic); 
   
  
• MSDE cannot be a publisher in transaction replication, and when acting as publisher in all other types of replication, must also be the distributor; 
   
  
• MSDE does not ship with GUI administrative tools such as Enterprise Manager or Query Analyzer -- see Article #2442 for alternative administration tools; 
   
  
• MSDE does not support SQL Mail (see Article #2403 for alternatives); 
   
  
• MSDE does not offer OLAP / data warehousing capabilities; and, 
   
  
• MSDE does not come with Books Online, but you can download it from Microsoft downloads (updated January, 2004).

The licensing issues surrounding MSDE have long needed to be clarified—perhaps Microsoft SQL Server: SQL Server 2005 Ex... is a good start. 
 
There are also some other minor differences in MSDE that you should be aware of... this article goes into detail about them, and this product matrix might be useful as well. 
 
You can also look at this WebCast in KB #817788, which details issues surrounding installing, deploying, and maintaining MSDE. 
 
And for a verbose commentary on MSDE, see SoftwareEng's WiKi. 
 

---

SQL Server Express 2005 
 
As of July 2004, these are the planned limitations of the next generation of MSDE, now known as SQL Server Express (which will still be a free product): 

• 1 GB memory (for buffer, not total); 
   
  
• 1 CPU; 
   
  
• 50 named instances per machine; 
   
  
• 4 GB per database (not including log files); 
   
  
• transactional replication is limited to a subscriber role only; 
   
  
• data mirroring and clustering are not available; 
   
  
• full-text search is not available; 
   
  
• SQL Agent is not available (you can use Service Broker or Task Scheduler instead); 
   
  
• the DTS Runtime is not available (though you can use DTS in Express from other machines); 
   
  
• Reporting Services is not available; and, 
   
  
• Business Intelligence is not available (this includes Notification Services and Analysis Services).

There is no more workload governor, though if you are running Express on Windows XP Home or Windows 2000/XP Professional, and using named pipes to connect, you are limited to 5 and 10 simultaneous connections, respectively. This is enforced at the OS level, and is not a new limitation in SQL Server Express; it also exists in previous versions of MSDE and SQL Server Personal Edition. 
 
There will be a limited graphical management tool for SQL Express, however it will not ship until as late as mid-2006. In the meantime, you can access SQL Server Express through SQLCMD, the new command-line admin interface to SQL Server; with Management Studio from any other SQL Server edition; or, from a subset of the applications listed in Article #2442.