MSDE or SQL Server?

from the Artful SQL Server & Access Tips List

Microsoft-centric developers now have three database choices: Access, SQL Server, and MSDE. Even if you've checked out the Microsoft Data Engine specs, you might conclude that it's little more than a testbed for developers on a budget. The Microsoft documentation suggests that MSDE will be useful only for a small number of users, yet many developers are pushing it far beyond the advertised limits. I know of a couple of installations where 100 users are hitting a single database on a single server. MSDE is a genuine database. Now let's examine the performance differences between the three databases. Given a query that pulls 100 rows from a table, Access sends the entire table (or tables where joins are involved) to the client, where the front end filters out the irrelevant rows; whereas, in SQL Server and MSDE, the filtering is performed on the server. The client receives only the relevant rows and not the whole table(s). How I see the database landscape At the low end of the spectrum are stand-alone databases. These could be something as simple as a database in which to catalog your CDs, or an order-entry system whose maximum size will never exceed a couple of gigabytes. At the opposite end of the spectrum, a large enterprise may stake its future on your database. In that case, you want all the bells and whistles that SQL Server offers: stored procedures, views, user functions, logins, roles, DTS, Query Analyzer, and so on. In the middle, let's say you have a small enterprise that could be bigger. Currently, the enterprise doesn't have the budget for SQL Server licenses for all its users. But since there is serious potential for growth, you want to design for success so you don't have to re-invent the database a year from now. In this case, MSDE is the best choice. MSDE is also a good choice for the application developer who sells to both small and large enterprises. When selling to a large company, she can assume that the client has already licensed SQL Server. Then, when selling to a small business, she can include a copy of MSDE, and thus pave the way for future growth without rewriting the application or redesigning the database or revising a single stored procedure.

Last updated 22 May 2009

Return to the Artful SQL Server & Access Tips page