Choosing a SQL Server Edition for ASP.NET Web Application Development

  • Article

**

[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:

Recommendations for Visual Studio 2012

The following recommendations apply to your development environment when you're using Visual Studio 2012 or Visual Studio Express 2012 for Web.

Scenario Recommended SQL Server Edition and Web Server

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.

Recommendations for Visual Studio 2010

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.

Scenario Recommended SQL Server Edition and Web Server

The default development environment

SQL Server Express 2008 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 2008 with IIS.

The following recommendations apply when your production database is SQL Server 2012.

Scenario Recommended SQL Server Edition and Web Server

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.

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.

Recommendations for Round-tripping Between Visual Studio 2010 and Visual Studio 2012

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.

Working with SQL Server Databases in Visual Studio

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.

  • Server Explorer enables you to connect to a database server, connect to a database, create a database, and perform some basic data display and update functions. You can open the Server Explorer window by choosing it from the View menu.
    Server Explorer is included in all versions of Visual Studio.
    Server Explorer works with SQL Server 2008 and earlier versions, and it works with SQL Server Compact 4.0. Some of its features do not work with SQL Server 2012.

  • SQL Server Object Explorer provides a more comprehensive set of database manipulation tools than Server Explorer. It is also known as SQL Server Data Tools (SSDT) and has functionality that is similar to SQL Server Management Studio (SSMS). You can open the SQL Server Object Explorer window by choosing it from the View menu.

    SSDT is included by default in Visual Studio 2012 and Visual Studio Express 2012 for Web. It can be installed in Professional and higher versions of Visual Studio 2010. If you have Visual Web Developer 2010 Express, install Visual Studio Express 2012 for Web which has SSDT by default and is free. For information about how to install SSDT in Visual Studio 2010, see Installing and using SQL Server Data Tools (SSDT) on Visual Studio 2010 and VWD on Rick Anderson's blog.
    SSDT only works with SQL Server databases. It works with SQL Server 2012 and earlier versions, all editions including SQL Server Express and LocalDB. It does not work with SQL Server Compact. For more information about SSDT, see Get Started with Microsoft SQL Server Data Tools.

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 2012

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:

  • Features Supported by the Editions of SQL Server 2012 (Lists SQL Server features that are included in SQL Server Express.)
  • How to take ownership of your local SQL Server Express 2008 (SQL Server Express blog. A common problem is not being able to work with SQL Server Express databases because you're not an administrator on the SQL Server Express instance. By default, only the person who installed SQL Server Express is an administrator. This blog explains how to make yourself a SQL Server Express administrator if you're an administrator on the computer.)

LocalDB

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.

Note
The first time a user on a computer tries to connect to LocalDB, the LocalDB instance must be both created and started. The extra time required to create the instance can cause the connection to fail with a timeout message. When you try to connect to a LocalDB database in SSDT or SSMD and the timeout error happens, wait to let the instance creation process complete, and then try to connect again.

For more information about LocalDB, see the following resources:

SQL Server 2012 (full editions)

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.

Windows Azure SQL Database (formerly SQL Azure)

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

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:

  • Server Explorer in Visual Studio offers limited database manipulation functionality.
  • The Databases tab in WebMatrix has more features than Server Explorer.
  • Relatively full-featured third-party or open source tools are available, such as the SQL Server Compact Toolbox and SQL Compact data and schema script utility.
  • You can write and run your own DDL (data definition language) scripts to manipulate a database schema.

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:

  • There are differences in the database engines and the ADO.NET providers between SQL Server Compact and the other SQL Server editions. These differences can cause the same code to generate different results. Therefore, you should test with SQL Server Express, SQL Server, or SQL Database before deploying to SQL Server or SQL Database in production.
  • The Web Deploy provider that Visual Studio uses to automate both initial database deployment and schema updates for SQL Server databases does not work with SQL Server Compact. An older Web Deploy provider can be used to do initial deployment, but it does not handle incremental schema updates. (This limitation does not apply if you deploy the database by using Code First Migrations, because that method does not use a Web Deploy provider.) For more information, see Configuring Database Deployment in Visual Studio in Web Application Project Deployment Overview for Visual Studio and ASP.NET.

For more information about SQL Server Compact, see the following resources:

See Also

ASP.NET Data Access Content Map