SQL Server vs. Access

Chapter 2 - Relational Databases
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

For our final act in this chapter (and therefore just prior to putting some of the things we've looked at into practice in real ASP.NET applications), we're going to have a quick look at Microsoft's two RDBMS products, with a view to helping you decide which to use in your production applications. As you know, we're going to be using MSDE as we work through the examples in this book, but commercial applications tend to require commercial solutions.

Microsoft Access can be a good database choice when you need a relatively small-footprint, relatively inexpensive database that resides on the client. However, performance begins to breaks down for large databases, and for many simultaneous users. Using Access as a server-side database for even small databases will yield poor performance, since Access does not have a server component. That is, the client application always has to read the entire table from the file server (that is, the computer on which the Access database resides), even if the SELECT is requesting a single record.

Microsoft SQL Server is much more expensive, and may be subject to license fees per user. It has a larger footprint than Access, but it will virtually always reside on a server. It performs well for medium-sized databases, and it has a server component that is designed to operate very efficiently over a network. It also supports stored procedures (see Chapter 8) that can perform significantly better than Access's queries, since the stored procedure execution plan is optimized on the fly. SQL Server has a much richer and more robust security model than Access. Finally, SQL Server allows richer maintenance operations, since it keeps a transaction log that can be used to recover a corrupt database without loss of data.

MSDE

As we described in the previous chapter, to a large extent MSDE is SQL Server, but without client tools like the Query Analyzer and the Enterprise Manager. Without the aid of these tools, routine database maintenance becomes more difficult - but not prohibitively so, as you'll soon see.