[Note: This 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 -- t
he 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
When you're deploying a database to SQL Server or Windows Azure SQL Database (formerly SQL Azure) in production, you have several SQL Server editions and versions to choose from for your development environment. This topic provides guidelines for choosing the right SQL Server edition and version for your scenario. The topic also provides information about SQL Server Compact 4.0, which strictly speaking is not an edition of SQL Server but is an alternative you can use for both development and production in an ASP.NET web application.
The first three sections of the topic summarize the recommended choices for typical ASP.NET development scenarios:
The following sections explain features of Visual Studio and SQL Server that the recommendations are based on:
The following recommendations apply to your development environment when you're using Visual Studio 2012 or Visual Studio Express 2012 for Web.
The default development environment.
LocalDB with IIS Express.
First choice if you want a closer match to your production environment.
The same edition and version of SQL Server as in production, with IIS.
Second choice if you want a closer match to your production environment and the first choice is not feasible.
SQL Server Express 2012 with IIS.
Visual Studio 2012 and Visual Studio Express 2012 for Web install LocalDB but not SQL Server Express by default. If you choose to use SQL Server Express, you have to install it. For more information, see SQL Server Express 2012 later in this topic.
Visual Studio 2012 and Visual Studio Express 2012 for Web use IIS Express as the development web server by default. For information about how to select full IIS, see Web Servers in Visual Studio for ASP.NET Web Projects.
The recommended development environment for Visual Studio 2010 differs depending on whether your production SQL Server version is 2012 or an earlier version. (If you have Visual Web Developer 2010 Express, install Visual Studio Express 2012 for Web and see the previous section. Visual Studio Express 2012 for Web is free.)
The following recommendations apply when your production database is SQL Server 2008 or an earlier version, or Windows Azure SQL Database.
The default development environment
SQL Server Express 2008 with IIS Express.
SQL Server Express 2008 with IIS.
The following recommendations apply when your production database is SQL Server 2012.
By default, Visual Studio 2010 installs SQL Server Express 2008, and the Visual Studio Server Explorer window only works with SQL Server 2008 and earlier versions. Therefore, these recommendations advise you to continue to use SQL Server 2008 if possible. If you prefer to work with SQL Server 2012 in development and you don't mind losing Server Explorer functionality, adopt the recommendations for SQL Server 2012 even if you're deploying to an earlier version of SQL Server.
If you choose to use SQL Server Express 2012 or LocalDB, you have to install it. You also have to install SQL Server Data Tools (SSDT) in order to work with SQL Server 2012 databases. For more information, see SQL Server Express 2012, LocalDB, and Working with SQL Server Databases in Visual Studio later in this topic.
By default, Visual Studio 2010 and Visual Web Developer 2010 Express use the Visual Studio Development Server (Cassini) as the development web server. For information about how to select IIS Express or full IIS, see Web Servers in Visual Studio for ASP.NET Web Projects.
If you want to enable multiple developers to work on the same Visual Studio web project, with some of them using Visual Studio 2010 and some of them using Visual Studio 2012, follow the recommendations for Visual Studio 2010. If you do that and you decide to use SQL Server Express 2008, you have to install SQL Server Express 2008 on the computers that have Visual Studio 2012. The following link uses the Web Platform Installer (WebPI) to do that: SQL Server Express 2008 R2.
Visual Studio includes two windows that facilitate managing databases: Server Explorer (also known as Database Explorer in the Express versions of Visual Studio) and SQL Server Object Explorer.
Visual Studio also includes web deployment features that facilitate deploying databases along with web projects. For more information, see Configuring Database Deployment in Visual Studio in ASP.NET Web Site Project Deployment Overview.
SQL Server Express is the entry-level free edition of SQL Server. It shares the same database engine as the full editions of SQL Server but omits some advanced features and has some limitations, such as a maximum database size of 10 gigabytes. SQL Server Express is typically used during development for databases that will be deployed to SQL Server or SQL Database in production. SQL Server Express can also be used for a production web site if its limitations are acceptable for the production environment.
SQL Server Express runs as a Windows service except when you set User Instance to true in the connection string. When you use the user instance feature, SQL Server Express runs as a process. The user instance feature is intended to facilitate dynamically attaching .mdf files by using the AttachDBFileName connection string setting. This feature is deprecated in SQL Server Express 2012 and may not be included in future versions of SQL Server. Therefore, using the User Instance and AttachDbFileName options with SQL Server Express 2012 is not recommended. For file-based database access, use LocalDB instead.
There is one exceptional scenario in which you have to use the deprecated user instance feature: when you want to use file-based database access with full IIS. In this scenario, LocalDB is not recommended. SQL Server Express with the User Instance and AttachDbFileName options is the only alternative. For more information, see the User Instance section in SQL Server Connection Strings for ASP.NET Web Applications, and LocalDB later in this topic.
Visual Studio 2012 and Visual Studio Express 2012 for Web do not install SQL Server Express 2012 by default. They install LocalDB instead. If you have Visual Studio 2012 or Visual Studio Express 2012 for Web, and you decide to use SQL Server Express 2012, install it by clicking the following link: Download SQL Server Express 2012. If you want to use graphical design tools with SQL Server 2012 databases, select Express with Tools from the drop-down list; otherwise, select Express (Database Only). For more information, see Working with SQL Server Databases in Visual Studio earlier in this topic.
Visual Studio 2010 and Visual Web Developer 2010 Express install SQL Server Express 2008 by default. If you want to use SQL Server Express 2012, install it as directed earlier for Visual Studio 2012 and Visual Studio Express 2012 for Web. In order to manage SQL Server 2012 databases in Visual Studio, install SSDT also. For more information, see Working with SQL Server Databases in Visual Studio earlier in this topic.
For more information about SQL Server Express, see the following resources:
LocalDB was introduced with SQL Server 2012 in order to provide a relatively simple development environment that is easy to install and requires less configuration than service-based SQL Server Express.
A SQL Server instance normally runs as a Windows service in its own security context. LocalDB is a SQL Server Express instance that runs as a process in the security context of the logged-on user. This instance execution mode is similar to the user instance feature of SQL Server Express, but LocalDB does not require that a full SQL Server Express instance be started first as a Windows service.
A LocalDB database is stored in .mdf (database) and .ldf (log) files. A LocalDB instance can attach and open any LocalDB .mdf file that the logged-on user has file system access to. (The LocalDB instance does not run with administrator privileges even if the user is an administrator on the computer, however. The user must have file system access to the .mdf file under the user's own account.) Only one LocalDB instance can connect to a LocalDB .mdf file at a time, and LocalDB cannot connect to an .mdf file on a remote computer.
Storing the .mdf file in the App_Data folder of a project is often convenient during development because you can copy the project files from one location to another, and the database goes with the project. In SQL Server Express, file-based access requires the user instance option, but that feature is deprecated and may be removed in future versions of SQL Server. The recommended edition of SQL Server if you want to use the AttachDbFileName connection string option is LocalDB.
LocalDB works with Cassini and IIS Express but requires problematic workarounds to work with IIS. If you use LocalDB for development in Visual Studio, consider deploying to SQL Server Express, SQL Server, or SQL Database in a test environment that uses full IIS before deploying to production. For a tutorial that illustrates how to deploy to IIS on the development computer, see Deploying to IIS as a Test Environment on the ASP.NET site. If you want to use IIS while developing in Visual Studio, use SQL Server Express instead of LocalDB.
Visual Studio 2012 and Visual Studio Express 2012 for Web install LocalDB by default, and the default project templates use it for the ASP.NET membership database.
Visual Studio 2010 and Visual Web Developer 2010 Express install SQL Server Express 2008 by default. They do not install any edition of SQL Server 2012. If you have Visual Studio 2010 or Visual Web Developer 2010 Express, and you decide to use LocalDB, install it by clicking this link: Microsoft SQL Express LocalDB Edition 11.0. (This WebPI link also installs the .NET Framework 4.0.2 update, which is required for LocalDB.) In order to manage SQL Server 2012 databases, install SSDT also. For information about how to do that, see Working with SQL Server Databases in Visual Studio [#vstools] earlier in this topic. If you use ASP.NET membership functionality in the default project templates, you also have to change the connection strings. For information about how to do that, see SQL Server Connection Strings for ASP.NET Web Applications.
For more information about LocalDB, see the following resources:
If you're deploying an ASP.NET web application to your own servers or to a shared hosting environment at a hosting company, you typically use a full edition of SQL Server for production, but you can also choose SQL Database (formerly SQL Azure). This topic does not provide guidance on how to choose between SQL Server and SQL Database for your production database. For links to resources that help with that choice, see Windows Azure SQL Database later in this topic. For information about the differences between SQL Server Editions, see Editions and Components of SQL Server 2012.
Use a full edition of SQL Server for development if you're deploying to SQL Server and you want your development environment to match production as closely as possible, or if you need to use features in development that are not available in LocalDB. For example, you might want to use SQL Server Profiler to help resolve performance issues, but it does not work with SQL Server Express.
SQL Database is the cloud edition of SQL Server. If you're deploying an ASP.NET web application to Windows Azure, you typically use SQL Database for your production database. Use SQL Database for development if you're deploying to SQL Database and you want your development environment to match production as closely as possible.
This topic does not provide guidance on how to choose between SQL Server and SQL Database for your production database. For help in making that choice, see the following resources:
SQL Server Compact 4.0 is a free, lightweight database engine that you can use in ASP.NET web applications. It supports a maximum database size of 4 gigabytes. It does not support stored procedures, triggers, views, or replication. SQL Server Compact does not have to be installed on a computer: you can include it in a project by installing the Microsoft.SqlServer.Compact NuGet package. When you deploy the project, the database engine assemblies can be deployed with it. This means that you can deploy SQL Server Compact databases to any production environment, including a shared hosting environment in which SQL Server Compact is not installed on the servers.
A SQL Server Compact database resides in an .sdf file, and file-based access is used in production as well as development. Therefore, deploying a database can be as easy as copying the .sdf file. If your connection string uses the |DataDirectory| option to specify the path to the file, you don't even have to change the connection string when you deploy a project.
It's easy to share projects across multiple versions of Visual Studio when you use SQL Server Compact, because you can include the database engine in a NuGet package and the database itself in the App_Data folder. With other editions of SQL Server, you have to make sure that each computer that runs Visual Studio has the right version of SQL Server installed.
You can't use SQL Server Management Studio or SQL Server Data Tools to manage SQL Server Compact databases, but other options are available:
You can use SQL Server Compact in development only, and deploy to SQL Server or SQL Database. However, there are some disadvantages to this approach:
For more information about SQL Server Compact, see the following resources: