Select an Edition of SQL Server for Application Development

This article attempts to provide guidance to developers who are considering using a SQL Server database in an application, but who are uncertain which edition of SQL Server best meets their needs.

SQL Server comes in a variety of editions, each of which build upon the features of the previous edition. This allows you to purchase an edition that provides only the features you need, and upgrade to another edition in the future if you require additional features. The exception to this rule are SQL Server Compact Edition and SQL Azure, which offer features such as embedding or integration with the Azure cloud platform that are not found in other editions.

The table below shows the relationship between the various editions of SQL Server.

For more in-depth information on the available editions of SQL Server, see http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx.

General Recommendations

Ultimately, the decision as to which edition of SQL Server to use will be based on the set of requirements of your application. The number of simultaneous users, whether you require a highly scalable database, support for specialized data types, all are critical to determining which edition is suitable for use with your application. However there are some easy questions that you can answer that will immediately narrow the list of SQL Server editions that are viable for your application.

• Will the application require the database to be on the same computer, or will it access the database over a network?
• Will SQL Server need to run inside the application process (embedded,) or as a separate process (a service)?

The following table lists the editions of SQL Server that are the best answers for the above questions:

Remote Database

A remote database is a database that resides on a different computer from the application(s) that use it. Editions of SQL Server that provide remote database functionality are implemented as a service, and are capable of handling a large number of simultaneous connections from multiple users and applications. A remote database that is accessed over the network is typically desirable if:

• The application runs on multiple computers, all of which need to access a central database
• The application runs in a web browser and cannot store all required data locally
• The application requires a large amount of database storage (editions other than Azure and Express support up to 524,272TB; SQL Server 2008 R2 Express supports 10GB, SQL Azure up to 150GB)

These editions of SQL Server run in their own process space, and allocate system resources separately from those used by your application. By default, these editions will dynamically allocate memory as needed by the SQL Server process. Minimum and maximum memory values can be configured if desired.

To support critical applications that must be highly available, these editions of SQL Server allow you to perform database backups while the database is in use. They also support transactional recovery, which guards against data corruption, and advanced multi-user concurrency.

Cloud Database (SQL Azure)

SQL Azure provides many of the same capabilities of SQL Server delivered as a cloud service on the Windows Azure Platform. Unlike other editions of SQL Server, you do not need to provision hardware for, install or patch SQL Azure; Microsoft maintains the platform for you. All you have to do is set up a subscription, provision your service, and start using SQL Azure database.  You also do not need to architect a database installation for scalability, high availability or disaster recovery as these features are provided automatically by the service. Any application that uses SQL Azure must have Internet access in order to connect to the database.

Ideally, applications that use SQL Azure Database are built for and deployed to the Windows Azure Platform.  This provides the highest performance and reliability for database connections because all of the communication between the application tier and the data tier happen inside Microsoft data centers and use private high speed networks.  You can configure SQL Azure to support connections from applications running on-premise, but the quality of the connection will be limited to the bandwidth and reliability of your internet connection.  

If you know how to develop applications with SQL Server, ASP.NET and the .NET Framework you already have many of the skills required to build cloud applications on the Windows Azure Platform.    Give it a try today!  Note that MSDN subscribers get great discounts on trial Windows Azure subscriptions (which include SQL Azure).

Local Database

A local database is a database that resides on the same computer as the application(s) that use it. A local database is most often used when:

• The application has no network connectivity, or intermittent connectivity and needs to cache data for use when there is no connectivity
• The application data is only accessed by processes running on the same computer

While both SQL Server Compact and SQL Server Express are suitable for providing a local database, there are many differences between the two products that will determine which one is more suitable for your application. SQL Server Express is a general purpose edition that is suitable for use as either a local or remote database (additional configuration is required to service remote connections,) and provides optional features such as full text indexing and reporting. SQL Server Compact is a special purpose edition that is designed specifically for use as a local database that is embedded as part of the application.

Specific differences in how these editions provide local database functionality are listed in the following table:

In-Process (Embedded)

All editions of SQL Server other than Compact run as a service.  They require a separate installation, consume their own resources, and usually are configured to run continuously, even when no applications are accessing the database.  While this is desirable for many scenarios, it may be unnecessary if the application requires only a local database.

A SQL Compact database is hosted by the application and runs in the application's process space. This can simplify the installation requirements of the application, as the Compact assemblies and database are part of the application itself instead of requiring a separate installation of SQL Server. It also removes inter-process calls that are normally involved with accessing other editions of SQL Server that run as a service.

Microsoft Access Database Engine

While not a member of the SQL Server family, the Microsoft Access Database Engine (also known as the Microsoft Jet Database Engine,) is also suitable for providing a local database for an application. The Microsoft Access Database Engine is similar to SQL Server Compact edition, in that it is designed primarily for local database scenarios and runs in-process within the host application. While databases created using the Microsoft Access Database Engine cannot be directly upgraded to an edition of SQL Server, they can be migrated to SQL Server by using a tool such as the SQL Server Migration Assistant (http://www.microsoft.com/sqlserver/2008/en/us/migration.aspx).

The Microsoft Access Database Engine is installed with Microsoft Access, part of the Microsoft Office family of products. While many associate the Microsoft Access Database Engine with Microsoft Access, the engine can also be used by non-Access applications, such as C# or VB.NET applications created using Visual Studio.

For information on using the Microsoft Access Database Engine with a C# application, see the Samples and Tutorials section. For information on Microsoft Access as a development environment, see the Development Platforms section.

Features By Edition

Use the following links to discover the features available in the editions of SQL Server 2005, 2008, 2008 R2 and SQL Server Compact 3.5 and 4 (CPT 1):

• Features supported by the editions of SQL Server 2005(http://msdn.microsoft.com/en-us/library/ms143761(SQL.90).aspx)

• Features supported by the editions of SQL Server 2008(http://msdn.microsoft.com/en-us/library/cc645993(SQL.100).aspx)

• Features supported by the editions of SQL Server 2008 R2(http://msdn.microsoft.com/en-us/library/cc645993.aspx)

• SQL Server Feature Limitations (SQL Azure)(http://msdn.microsoft.com/en-us/library/ff394115.aspx)

• SQL Server Compact Edition 3.5

  • Differences between SQL Server Compact and SQL Server(http://msdn.microsoft.com/en-us/library/bb896140(v=SQL.100).aspx)
  • Database Objects(http://msdn.microsoft.com/en-us/library/ms172451(v=SQL.100).aspx)
  • Data Types(http://msdn.microsoft.com/en-us/library/ms172424(v=SQL.100).aspx)

• SQL Server Compact Edition 4.0

  Note:

  SQL Server Compact Edition 4.0 is currently under development, and is available as a Customer Technical Preview (CTP.)

  • Introducing SQL Server Compact 4.0(http://blogs.msdn.com/b/sqlservercompact/archive/2010/07/07/introducing-sql-server-compact-4-0-the-next-gen-embedded-database-from-microsoft.aspx)

Development Platforms

Microsoft provides two development platforms that install one or more editions of SQL Server as part of the development platform installation.  This is provided as a convenience to the developer, and in no way limits the development environment to only using the edition of SQL Server that came with it. Other development platforms, such as Microsoft Office, can also use SQL Server but do not install it.  This section primarily focuses on the development platforms that install SQL Server as part of their setup.

WebMatrix

WebMatrix is an all-in-one package that streamlines the building and hosting Web sites using Windows. It includes IIS Developer Express, ASP.NET, and SQL Server Compact 4.0, as well as templates for Web sites and the ability to easily start Web sites based on popular open-source apps such as WordPress, mojoPortal, Drupal and Moodle.

Web sites created using WebMatrix can either be hosted directly using IIS Developer Express, or deployed to a hosting provider running IIS.  Likewise, applications created using WebMatrix that use SQL Server Compact can continue using Compact after deployment, or can be easily migrated to another version of SQL Server, such as Web edition.

For examples and tutorials using WebMatrix, see the Samples and Tutorials section.

Visual Studio

Where WebMatrix is tightly focused on one specific development scenario (Web sites,) Visual Studio is an integrated development environment that can be used to develop a variety of applications (Windows forms applications, Web applications, Web sites, Web services, console applications, etc.) for all platforms supported by Microsoft Windows, Windows Mobile, Windows CE, .NET Framework, .NET Compact Framework, Microsoft Silverlight, and Windows Phone 7. Visual Studio provides built-in support for  programming languages such as C#, VB.NET, C++, as well as allowing for the installation of additional languages such as Python and Ruby.

Visual Studio installs both SQL Server Express and SQL Server Compact, the current version (Visual Studio 2010,) installs SQL Server 2008 Express SP1 and SQL Server Compact 3.5 SP2. While Visual Studio installs SQL Server Express and SQL Server Compact, it does not limit you to only these editions; you can create applications that target any edition of SQL Server.

For examples and tutorials using Visual Studio, see the Samples and Tutorials section.

Microsoft Office (Access and Excel)

While Microsoft Office does not install any version of SQL Server, applications created using Office products such as Access or Excel can be used to develop applications that connect to SQL Server. Some Office products, such as Access, provide specific features for integrating with SQL Server, while others can make use of Visual Basic for Applications (VBA) to connect to SQL Server.

Microsoft Access provides an easy way to create and access a database by including templates, tools for designing forms, and queries and reports that can be used with little or no coding. Access also includes features for developers who want to build redistributable Access based applications.  By default, Access stores its data in the Microsoft Access Database Engine; however Access can connect to other data sources, including SQL Server.

For examples and tutorials using Access and Excel, see the Samples and Tutorials section.

Samples and Tutorials

WebMatrix

• WebMatrix Tutorial - Working With Data(http://www.asp.net/webmatrix/tutorials/5-working-with-data)
• How to Publish a Web Site Using WebMatrix Beta (http://learn.iis.net/page.aspx/871/publish-your-website/)
• How to Migrate a Database to SQL Server Using WebMatrix Beta(http://www.asp.net/webmatrix/tutorials/migrate-a-database-to-sql-server)
• Creating PHP Web Sites Using WebMatrix Beta(http://blogs.msdn.com/b/brian_swan/archive/2010/07/12/creating-php-websites-with-webmatrix.aspx)

Visual Studio

• How to Add a SQL Sever Compact 3.5 Database to a Project(http://msdn.microsoft.com/en-us/library/aa983322.aspx)
• Walkthrough: Adding a SQL Server Compact 3.5 Database to an Application and Deploying it(http://msdn.microsoft.com/en-us/library/aa983340.aspx)
• Data and ADO.NET Quickstarts(http://quickstarts.asp.net/quickstartv20/howto/doc/adoplus/overviewcontents.aspx)
• How to Connect to SQL Azure Using ADO.NET(http://msdn.microsoft.com/en-us/library/ee336243.aspx)
• How to Connect to SQL Azure Using ASP.NET(http://msdn.microsoft.com/en-us/library/ee621781.aspx)
• How to Connect to SQL Azure Using PHP(http://msdn.microsoft.com/en-us/library/ff394110.aspx)

Microsoft Office

• How to Import Data from Microsoft SQL Server into Excel(VBA) (http://support.microsoft.com/kb/306125)
• Create an Access Project (http://office.microsoft.com/en-us/access-help/create-an-access-project-HA010341589.aspx?queryid=10ee58aac71440ce8c4686de076c1100&respos=0&CTT=1)
• Import or Link Access to SQL Server Data (http://office.microsoft.com/en-us/access-help/import-or-link-access-to-sql-server-data-HA010341762.aspx?CTT=5&origin=HA010341589)
• How to retrieve and display records from an Access database by using ASP.NET, ADO.NET, and Visual C# .NET(http://support.microsoft.com/kb/308100)

References

• Maximum Capacity Specifications for SQL Server - http://msdn.microsoft.com/en-us/library/ms143432.aspx  
• General Guidelines and Limitations (SQL Azure) - http://msdn.microsoft.com/en-us/library/ee336245.aspx