[Note: This blog post is a preliminary version of a document that has been published on MSDN. The published version with changes resulting from the comments received is now available here. Thanks to everyone who sent comments about this version.]
This is one of a series of posts that present preliminary versions of pages that will eventually be published on MSDN. These pages are a work in progress and suggestions for improvements or corrections are welcome. The intended audience is newcomers to ASP.NET and the purpose is to provide basic guidance for making data access choices for ASP.NET application development. Please forgive the less than ideal formatting -- the HTML for this post was generated by tools that weren't designed for blog publishing. I corrected some of the deficiencies but did not fix all of them.
The series includes:
-- Tom Dykstra ASP.NET User Education
This topic answers frequently asked questions about how to access data in ASP.NET web applications. It contains the following sections:
Microsoft strongly recommends against using Access in web applications. The AccessDataSource control has been removed from the Visual Studio Toolbox for projects that target ASP.NET 4.5, and Microsoft customer support will not help resolve problems resulting from the use of Microsoft Access in web applications. Such use might be successful for a while if you use the native Jet OLE DB Provider in place of the Microsoft Access ODBC driver, if you access an .mdb or .accdb file that is located within your web application's folder structure, and if web site traffic is very low. But a system failure with possible database corruption is likely to develop eventually, especially as traffic to the application increases. In addition, bugs in the database provider are no longer being fixed; the Jet provider has been superseded by the ACE provider, which specifically disallows use in web applications. Even if no errors are encountered, database security is especially important in a public web site, and Access databases have fewer security features than SQL Server databases.
Microsoft recommends SQL Server for web applications. For more information, see Choosing a Data Access Approach for ASP.NET Web Forms Applications and Choosing a SQL Server Edition for ASP.NET Web Application Development.
If you want to accept the risks and use Microsoft Access in a web site, see the Microsoft Support page on Using Microsoft Jet with IIS.
You can use SQL Server Express in a production web hosting environment if its limitations are acceptable for your web site. SQL Server Express is not designed to be highly scalable and may not be appropriate for a high-traffic web site. For example, a SQL Server Express instance is limited to 1 gig of memory and no more than 4 processors, and a SQL Server Express database is limited to 10 gigabytes. For more information about the differences between the full and Express versions of SQL Server, see Features Supported by the Editions of SQL Server 2012.
You can combine the membership tables and your application tables in a single database. The membership tables are clearly prefixed, so name collisions are unlikely. And if you keep all of the tables in the same database you can use referential integrity against your own tables that have the membership ID in them. For an example that shows one way to do this, see Deployment to a Hosting Provider: Migrating to SQL Server on the ASP.NET site.