SQL Server Express WebLog

Using LocalDB with Full IIS, Part 2: Instance Ownership

sqlexpress — Fri, 09 Dec 2011 07:17:00 GMT

This is the second post in a two-post mini-series on using LocalDB with Full IIS. Don't miss the first post.

Quick Recap

In the first post of this mini-series we said there are two properties of LocalDB causing problems when running under full IIS:

LocalDB needs user profile to be loaded
LocalDB instance is owned by a single user and private (by default)

We saw how to properly load the user profile, now it's time to tackle the problem of LocalDB instance ownership.

The Problem of the Private Instance

At the end of the previous post we left our Web Application in this state:

As we can see we are facing the following error:

System.Data.SqlClient.SqlException: Cannot open database "OldFashionedDB" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\ASP.NET v4.0'.

This time the error is quite clear. LocalDB was started and the Web Application was able to connect to it, but the connection was then terminated due to login failure. The ApplicationPoolIdentity account for the IIS application pool (in this case IIS APPPOOL\ASP.NET v4.0) couldn't login to LocalDB instance because the database specified in the connection string (OldFashionedDB) wasn't found. How odd, since connecting from Visual Studio with the same connection string succeeds!

(In this screenshot I am using SQL Server Object Explorer, which is introduced by SQL Server Data Tools. As you can see it adds SQL Server tools right inside Visual Studio 2010.)

How is it possible that Visual Studio connects to LocalDB just fine, while the connection from Web Application fails? In both cases the connection string is the following:

Data Source=(localdb)\v11.0;Initial Catalog=OldFashionedDB;Integrated Security=True

The answer is that there are two different LocalDB instances here. Unlike SQL Server Express instances, which are running as Windows services, LocalDB instances are running as user processes. When different Windows users are connecting to LocalDB, they will end up with different LocalDB processes started for each of them. When we connect to (localdb)\v11.0 from Visual Studio, a LocalDB instance is started for us and runs as our Windows account. But when Web Application, running in IIS as ApplicationPoolIdentity, is connecting to LocalDB, another LocalDB instance is started for it and is running as ApplicationPoolIdentity! In effect, even though both Visual Studio and Web Application are using the same LocalDB connection string, they are connecting to different LocalDB instances. Obviously the database created from Visual Studio on our LocalDB instance will not be available in Web Application's LocalDB instance.

A good analogy to this is My Documents folder in Windows. Say we open Visual Studio and create a file in our My Documents folder. Then we login to the same machine as a different user and go to My Documents folder again. We won't find the file there as My Documents of the second user and our My Documents are two different folders. Similarly LocalDB instances (localdb)\v11.0 owned by two different users are two different processes with two different sets of databases.

This is also the reason the Web Application was able to connect to LocalDB from IIS Express. Just like LocalDB, IIS Express is a user process. It is started by Visual Studio and runs as the same Windows account as the Visual Studio process. Two different processes running as the same Windows account (Visual Studio and IIS Express, both running as our Windows account) connecting to (localdb)\v11.0 are connecting to the same LocalDB process, also started as the same Windows account.

Possible Solutions

Understanding the nature of the problem brings multiple approaches to solving it. As different approaches have different tradeoffs, instead of prescribing one solution, below I presented three approaches that seem most viable to me. My hope is to hear from you about the one that worked best for you! Here is the list:

Approach 1: Run IIS as our Windows user
Approach 2: Use LocalDB Shared Instance
Approach 3: Use full SQL Server Express

Let's take a closer look at each of them.

Approach 1: Run IIS as our Windows user

If different user accounts are the problem, why not try to run our Web Application under our Windows account? Web Application would connect to the same LocalDB as Visual Studio and everything should just work.

Making the configuration change is relatively easy, just start IIS Manager and find the right Application Pool:

Open Advanced Settings screen (available in the context menu):

Click the little button in the Identity property to bring up the Application Pool Identity screen:

Starting the Web Application again will confirm that the problem is solved:

What are the drawbacks of this approach? Of course running Web Application under our account brings certain security risks. If someone hijacks our Web Application they will be able to access all system resources our account can. Running the Web Application as ApplicationPoolIdentity provides additional protection since ApplicationPoolIdentity accounts have very limited access to local system resources. Therefore I cannot recommend this approach in general, but when used with care it is a viable option in some cases.

Approach 2: Use LocalDB Shared Instance

We could also use an instance sharing feature of LocalDB. It allows us to share a LocalDB instance with other users on the same machine. The shared instance will be accessible under a public name.

The easiest way of sharing an instance is to use SqlLocalDB.exe utility. Just start an administrative command line prompt, and type the following command:

sqllocaldb share v11.0 IIS_DB

It will share the private LocalDB instance v11.0 under the public name IIS_DB. All users on the machine will be able to connect to this instance, using (localdb)\.\IIS_DB as a server address. Note the \. before the instance name, indicating this is a shared instance name. We should replace the connection string in our Web Application with an updated one:

Data Source=(localdb)\.\IIS_DB;Initial Catalog=OldFashionedDB;Integrated Security=True

Before the shared instance can be used by the Web Application we need to start it and create logins for the ApplicationPoolIdentity. Starting the instance is easy, simply connecting to it from SQL Server Object Explorer will start it and keep it alive. Once we are in the SQL Server Object Explorer we can also create the login for ApplicationPoolIdentity. We could use the following query:

create login [IIS APPPOOL\ASP.NET v4.0] from windows;
exec sp_addsrvrolemember N'IIS APPPOOL\ASP.NET v4.0', sysadmin

This script gives full administrative access to our LocalDB instance to the ApplicationPoolIdentity account. Whenever possible, I would recommend using more limited, database-level or even table-level permissions.

Now we can run our Web Application again. This time it should work just fine:

What are the drawbacks of this approach? The main one is that, before Web Application can connect to the shared instance, we need to make sure the instance is started. For that to happen the Windows account that owns the instance must connect to it and the connection must be kept open, or the LocalDB instance will shut down.

Approach 3: Use full SQL Server Express

Since full IIS runs as a service, maybe using traditional, service-based SQL Server Express is the right approach? We could just install SQL Server 2012 Express RC0 and create the OldFashionedDB database in it. We can even use our brand new SQL Server Data Tools to do it, as it works with any SQL Server version and edition. Our connection string would have to change to:

Data Source=.\SQLEXPRESS;Initial Catalog=OldFashionedDB;Integrated Security=True

Of course, just as in the previous case, we would need to make sure the ApplicationPoolIdentity account has access to our SQL Server Express instance. We can use the same script as previously:

create login [IIS APPPOOL\ASP.NET v4.0] from windows;
exec sp_addsrvrolemember N'IIS APPPOOL\ASP.NET v4.0', sysadmin

After that, running our Web Application brings the happy picture again:

What are the drawbacks of this approach? Obviously we lose the benefits of using LocalDB. Installing SQL Server Express may take more time than LocalDB, and there may be some machine cleanup necessary for it to succeed. SQL Server Express Setup can be blocked by problems like corrupt WMI database, polluted registry or components left by SQL Server or Visual Studio CTPs and Betas. And SQL Server Express will continue running in the background even when not needed, as services do.

Other options

There are other approaches of using LocalDB under full IIS that are not covered here. We could embrace the Web Application's private LocalDB instance and communicate with it through the Web Application by executing T-SQL scripts from ASP.NET code. We could also use AttachDbFileName option of ADO.NET connection strings and use a database file (.mdf) that would be attached to both our LocalDB during development and Web Application's LocalDB for debugging. I tried both I found them too cumbersome to discuss further.

Try It Now!

We are eager to hear your feedback on LocalDB. Please share your thoughts with us. Did any of the approaches presented in this post work for you? Did you figure out a better one? Let us know!

You may also want to see other posts and materials about LocalDB:

- Krzysztof Kozielczyk

Share Your Feedback

Please share your feedback with us! Just start a thread on SQL Express Forum, hit the "Email Author" button on this post, or file a Connect item!

Using LocalDB with Full IIS, Part 1: User Profile

sqlexpress — Fri, 09 Dec 2011 07:14:00 GMT

This is the first post in two-post mini-series on using LocalDB with Full IIS. If you found it useful make sure to continue to the second post.

LocalDB and Full IIS

In this recent post I described how to use SQL Server Express LocalDB with Visual Studio 2010. The approach I proposed works for all kind of projects, including Web Applications. However, when it comes to Web Application, there is a catch. An application hosted in Cassini or IIS Express will work as expected, but as soon as we try running it in full IIS (the regular IIS that comes with Windows and runs as Windows service) we are facing the following error:

What Went Wrong?

There are two properties of LocalDB that cause problems when running under full IIS:

LocalDB needs user profile to be loaded
LocalDB instance is owned by a single user and private (by default)

We will focus the rest of this post on the user profile, leaving the issue of LocalDB instance ownership for the next post.

Loading User Profile

Let's take another look at the error:

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 0 - [x89C50120])

The error message is not very helpful but LocalDB stores additional information in Windows Event Log. Looking in the Application section under Windows Logs we find the following message:

Windows API call SHGetKnownFolderPath returned error code: 5. Windows system error message is: Access is denied.
Reported at line: 400.

Followed by this one:

Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user.

Most likely there will be several copies of these two errors, as ADO.NET connection logic attempts to connect multiple times at different intervals:

The second message is clear, the problem we're facing is that the user profile needs to be loaded. That shouldn't be hard since each IIS Application Pool has an option called Load User Profile that can be found in Advanced Settings section. Unfortunately things got slightly more complicated in Service Pack 1 for Windows 7. As described in KB 2547655 enabling loadUserProfile is not enough to fully load user profile, we also need to enable setProfileEnvironment. This requires editing applicationHost.config file which is usually located in C:\Windows\System32\inetsrv\config. Following the instructions from KB 2547655 we should enable both flags for Application Pool ASP.NET v4.0, like this:

Having completed that we restart the Application Pool to make sure the new settings are applied and run our Web Application again. If everything went as expected we should be ... faced by a new error:

There's no reason to panic, as this error is fully expected. After all we haven't dealt with the second problem with running LocalDB under full IIS. We still need to address the fact that by default LocalDB instances are private, which means that IIS, running as ApplicationPoolIdentity, will not have access to our LocalDB instance, running as our Windows account. We will explore different ways to address this problem in the second post from this mini-series on using LocalDB under full IIS.

- Krzysztof Kozielczyk

Share Your Feedback

Please share your feedback with us! Just start a thread on SQL Express Forum, hit the "Email Author" button on this post, or file a Connect item!

Announcing SQL Server 2012 Express LocalDB RC0

sqlexpress — Mon, 28 Nov 2011 09:29:00 GMT

LocalDB RC0 is Here, with a Brand New MSI!

Release Candidate 0 for SQL Server 2012 has just shipped, bringing a new update to SQL Server Express LocalDB!

As announced here LocalDB is a version of SQL Server Express, introduced in the CTP3 of SQL Server 2012 and created specifically for developers. It is very easy to install and requires no management, yet it offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express. For more details please see the original announcement and related posts listed in the Try It Now section below.

With this introduction done I am very happy to present a small, streamlined installer for LocalDB. This dedicated, 33MB MSI package can install LocalDB in 30 seconds and completes the first version of SQL Server Express LocalDB.

Installation

You can download LocalDB from this page. Just pick your CPU architecture (32-bit or 64-bit) and select LocalDB from the drop-down list. Just double-click the SqlLocalDB.msi file is downloaded that will be downloaded. We kept LocalDB simple so there is not a single configuration option in the entire installer. All you need to do is to accept the EULA and click Next a couple times. The installation of LocalDB requires administrative privileges, so if your machine has UAC enabled you will also need to agree to elevate the installation process.

Silent and Embedded Installation

As any decent installer, LocalDB supports silent installation. Here is an example a command that installs LocalDB silently:

msiexec /i SqlLocalDB.msi /qn IACCEPTSQLLOCALDBLICENSETERMS=YES

As you can see these are all standard Windows Installer options, except for the IACCEPTSQLLOCALDBLICENSETERMS variable. Setting it to YES is telling us that the user read and accepted LocalDB EULA. As expected the installer will not continue unless this variable is set to YES.

If you are considering distributing LocalDB with your application, be sure to put 32-bit and 64-bit versions into separate folders. LocalDB doesn't support installing 32-bit version on 64-bit Windows, so you will need to distribute both versions of LocalDB with your application and they both have the same file name (SqlLocalDB.msi). Renaming LocalDB packages is not an option, as Windows Installer requires that MSI file names don't change (see Rule 14 in this post for the explanation).

Multiple Versions of LocalDB and Upgrades

To keep the LocalDB installation simple we decided that major versions of LocalDB will be installed side-by-side. This way an application using LocalDB version 11 will never be broken by some other application upgrading LocalDB to version 12. Both applications will be able to install the versions of LocalDB they need without interfering with each other.

Service Packs for LocalDB will also work in the simplest possible way. In each Service Pack we will release new MSI packages containing all the LocalDB binaries. The updated MSI package can be used to either apply the Service Pack to an existing LocalDB installation or to install a fresh LocalDB installation. In both cases the command line for the silent installation will be the same.

Try It Now!

As you can tell we are fairly satisfied with the final shape of LocalDB, at least for SQL Server 2012. But the final call belongs to you! Please give it a try and share your feedback with us.

You may also want to see other posts and materials about LocalDB:

- Krzysztof Kozielczyk

Share Your Feedback

Please share your feedback with us! Just start a thread on SQL Express Forum, hit the "Email Author" button on this post or file a Connect item!

Using LocalDB in Visual Studio 2010

sqlexpress — Mon, 28 Nov 2011 07:41:00 GMT

SSMS is a Success, But What About the VS?

In my recent post I was using .NET Framework 4.0.2 to enable SQL Server Management Studio to connect to LocalDB. It all worked rather well and I declared success and moved on to another topic. I conveniently kept silent about connecting to LocalDB from Visual Studio 2010. For a good reason -- currently Visual Studio 2010 doesn't really work with LocalDB. There are two reasons for that:

The SQL Server database experiences in Visual Studio 2010 are built on top of User Instances of SQL Server Express. Using LocalDB to power them is more complex than just changing a connection string to (localdb)\v11.0. It would require releasing some kind of a wide patch for Visual Studio 2010.
LocalDB was introduced in SQL Server 2012 CTP3. Visual Studio 2010 only supports SQL Server 2008 R2 and earlier.

In effect, even though adding the connection to LocalDB to Data Connections node of Server Explorer will succeed, trying to open any database designer from this connection will result in this sad message:

Is There At Least a Hack?

At this point the situation might seem pretty grim. Without Visual Studio 2010 support for LocalDB, developers that want to try it would need to wait for the next Visual Studio release. How can we get your feedback on LocalDB if there's no way to try it for months?

Fortunately, many Visual Studio 2010 features actually work with LocalDB and we should be able to use SQL Server Management Studio for the missing parts. We could also take the opportunity to try the new SQL Server Data Tools -- I will post about working with LocalDB from SSDT soon, as it integrates into Visual Studio, providing developers with a nice alternative for usig SSMS.

I already covered how to connect to LocalDB from SSMS and create databases, tables, input data and so on. Let's look at how we can use them from the Visual Studio 2010.

As I mentioned earlier creating a connection to LocalDB in the Data Connections node of Server Explorer will just work. Let's give it a try! One way to do it is to right-click the Data Connections node and select Add Connection...

We should use the standard LocalDB connection string: (localdb)\v11.0

Clicking OK and done, the connection is created!

Don't worry about the strange format of the connection -- we will soon see that the connection indeed points to our usual LocalDB instance.

The next step is to use the connection. Let's create a DataGridView control by dragging it from the Toolbox panel.

As we can see the DataGridView is not connected to any data source. Let's bind it to our LocalDB connection. Click Add Project Data Source... link on the Tasks quickview of the DataGridView control to bring up the data connection wizard and select Database and Dataset on the next two screens.

When asked about the connection, pick the LocalDB connection from the list. Once it's selected we can confirm that it points to LocalDB by looking at the Connection string property.

The last step is selecting a table to be presented in the DataGridView. After finishing the wizard the DataGridView should have the same columns as the underlying table, like this:

We're done. Hold your breath and hit F5 to run the application. With any luck it should start and the DataGridView should present the data from the table, like on this screenshot:

That was easy, wasn't it? :-)

Try It Now!

This walkthrough covers just one scenario, but the same approach should work in other scenarios as well. Just use SSMS or SSDT to create your database and Visual Studio to connect to it. Please try out your favorite scenario and share your feedback!

You may also want to see other posts and materials about LocalDB:

- Krzysztof Kozielczyk

Share Your Feedback

Please share your feedback with us! Just start a thread on SQL Express Forum, hit the "Email Author" button on this post or file a Connect item!

LocalDB: Where is My Database?

sqlexpress — Fri, 28 Oct 2011 21:11:00 GMT

As I mentioned in the previous post LocalDB handles database files differently than regular SQL Server.

The regular SQL Server assumes that it is actively managed by a DBA. The DBA carefully configures SQL Server during the installation and afterwards. The DBA decides where SQL Server binaries are installed. The DBA also decides where the system database files are located. The location of the system databases also becomes the default location for all user databases.

LocalDB is different. It's built to be used by developers, not DBAs. And we have heard developers saying loud and clear that they just want to focus on their databases and database code. Therefore our primary design goal was to eliminate all the server configuration and management that is getting in their way. As a result LocalDB stores all the system databases deep inside the "hidden" AppData folder in the user profile. For example, after playing with the Automatic Instance in the previous post, I find this folder in my user profile:

This folder constitutes my Automatic LocalDB Instance. Any good DBA will explain in great and painful details what these files are, but if you are a developer this is the first and the last time you will look at them. Which is great, but left us with an interesting question. If the location of system databases is hidden from the developer, what should the default location for the databases created by the developer be? Creating user databases in a hidden folder didn't seem like the right design. We considered My Documents too, but that could give the impression that SQL Server databases are portable documents. Plus half of our team was using some sort of document synchronization solution, like Live Mesh. Those would quickly destroy the database files and we thought they will only get more popular over time, thus more likely to wreck havoc in the future.

In the end we decided to create the database files in the root of the user profile. On most machines it is located in C:\Users\user-name folder

In the spirit of a scientific approach, let's try it out. Open SSMS and connect to your Automatic Instance:

Then create database foo without specifying the location for its files:

create database foo

Open Windows Explorer and navigate to your profile (typing %USERPROFILE% in the address bar is a nice shortcut). If the database was created successfully there will be new files in this folder, foo.mdf and foo_log.ldf that represent your database. QED.

Given that user profile folder is likely not the best location to store database files, we advise developers creating databases to always specify the location for the database files, like in this T-SQL example:

create database foo on (name='foo', filename='c:\DBs\foo.mdf')

- Krzysztof Kozielczyk

Send us Your Feedback

Please share your feedback with us! Just start a thread on SQL Express Forum, hit the "Email Author" button on this post or file a Connect item!

.NET Framework 4 now supports LocalDB!

sqlexpress — Fri, 28 Oct 2011 02:35:00 GMT

Updated 2011-11-11: Added a link to the post on how LocalDB handles database file locations.

Updated 2012-01-03: Clarified that only SSMS from SQL Server 2012 (or newer) will work with the .NET 4 Update 4.0.2, since earlier SSMS versions are using .NET 3.5.

.NET Framework 4 Update 4.0.2

.NET team has just released Update 4.0.2 for .NET Framework 4. This update enables connecting to SQL Server Express LocalDB via System.Data.SqlClient provider.

.NET Framework 4 Update 4.0.2 can be downloaded from http://support.microsoft.com/kb/2544514

Does this work?

Well, let's give it a try!

We start by installing SQL Server 2012 Management Studio RC0 (SSMS) and LocalDB together with .NET 4. The version of SSMS is important as earlier versions are using .NET 3.5 and currently only .NET 4 can be updated to understand LocalDB connection strings. Next we update .NET 4 to 4.0.2 (hey, no reboot in my case!). Then fire up SSMS. When prompted for the server address we specify "(localdb)\v11.0", like this:

Clicking Connect and … (drum roll please!) …

Success!

Does it really work?

OK, so we confirmed SSMS connects to LocalDB. Which is great, but how about doing something more useful for a developer? The next logical step would be to create a database. We know the drill -- right-click the Databases node in Object Explorer and select New Database. So far, so good. After typing in the name of the new database (in my case LocalDBTest) we are ready to hit OK...

Clicking OK and ... we hit an error!

This is obviously a bug, but there are at least two reasons we shouldn't panic. First, the bug has an easy workaround that we're going to use very, very soon. Second, it points to an important characteristic of the way LocalDB handles database files. I will explain it in more detail in the next post, but for now let's get back to LocalDB and creating our database.

To work around this problem we need to explicitly specify the folder for the database files. For the lack of a better location let's just put them into My Documents, like this:

This time clicking OK creates the database without any problems.

We are now ready for some serious database development, but this too will have to wait for another post.

- Krzysztof Kozielczyk

Send us Your Feedback

Please share your feedback with us! Feel free to start a thread on SQL Express Forum, hit the "Email Author" button on this post or file a Connect item!

Introducing LocalDB, an improved SQL Express

sqlexpress — Tue, 12 Jul 2011 12:30:00 GMT

Updated 2011-11-28: Added reference to the walkthrough of using LocalDB in Visual Studio 2010 and to the new LocalDB Installer.

Updated 2011-11-02: Added reference to .NET Framework 4 support for LocalDB in the Q&A section.

Introduction

It gives me great pleasure to introduce a new version of SQL Express called SQL Express LocalDB.

LocalDB is created specifically for developers. It is very easy to install and requires no management, yet it offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express. In effect the developers that target SQL Server no longer have to install and manage a full instance of SQL Server Express on their laptops and other development machines. Moreover, if the simplicity (and limitations) of LocalDB fit the needs of the target application environment, developers can continue using it in production, as LocalDB makes a pretty good embedded database too.

Background

Before focusing on technical description of LocalDB, I'd like to provide some background on the direction we took building it.

Today SQL Server Express serves two distinct needs. On one hand it is a free edition of SQL Server. The installation, management and programming of SQL Express in this role is expected to be 100% compatible with other editions. It can be used for learning, training and to run relatively small production database (with less than 10GB of data). Upgrade from SQL Express to paid SQL Server editions is a matter of typing in a license key and no installation is required.

But SQL Express is also SQL Server edition for developers writing applications targeting SQL Server. In this role the programming of SQL Express is still expected to be 100% compatible with other SQL Server editions, but SQL Express is supposed to be small, simple, low-footprint, require no configuration or administration, run as non-admin user, etc.

Our approach so far was to try to make SQL Express perform well in both roles. But as SQL Server product matured, and in effect added more complexity, it became harder and harder for SQL Express to be both compatible with other SQL Server editions and small/simple. The challenge is most visible in installation and configuration of SQL Express. In SQL Server "Denali" we decided to change the approach it and introduce a dedicated version of SQL Express for developers - LocalDB that delivers the simplicity and yet is compatible with other editions of SQL Server at the API level.

Also, by making LocalDB a better SQL Express for developers, we hope to be able to improve the regular SQL Express to be a better free SQL Server. We'd be very happy to hear your feedback in this area, especially if you're using SQL Express as a database server and find any issues caused by the new features that were introduced to fit the needs of developers and desktop environment.

High-Level Overview

After the lengthy introduction it's time to take a look at LocalDB from the technical side. At a very high level, LocalDB has the following key properties:

LocalDB uses the same sqlservr.exe as the regular SQL Express and other editions of SQL Server. The application is using the same client-side providers (ADO.NET, ODBC, PDO and others) to connect to it and operates on data using the same T-SQL language as provided by SQL Express.
LocalDB is installed once on a machine (per major SQL Server version). Multiple applications can start multiple LocalDB processes, but they are all started from the same sqlservr.exe executable file from the same disk location.
LocalDB doesn't create any database services; LocalDB processes are started and stopped automatically when needed. The application is just connecting to "Data Source=(localdb)\v11.0" and LocalDB process is started as a child process of the application. A few minutes after the last connection to this process is closed the process shuts down.
LocalDB connections support AttachDbFileName property, which allows developers to specify a database file location. LocalDB will attach the specified database file and the connection will be made to it.

LocalDB Instances

As we said before, at the heart of LocalDB is the same sqlservr.exe as in the regular SQL Express and other editions of SQL Server. In case of LocalDB, it is installed into one central location together with all necessary DLLs. By default it is located at "C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn".

When an application uses any of the client-side providers (like ADO.NET, ODBC or PDO) to connect to "Data Source=(localdb)\v11.0", the provider will first check if LocalDB instance for the current user is started. If it's already started the application will connect to it. Otherwise the LocalDB instance for the current user will be started and then the provider proceeds to connect to it. Note that each user (Windows login) may have their own LocalDB instance that is isolated from instances of other users.

To try LocalDB just use this connection string: "Data Source=(localdb)\v11.0;Integrated Security=true". Note that because the activation logic lives in the client-side provider, you need to install the latest one - SQL Server Native Client "Denali" (for ODBC and OLE DB) or the next .NET Framework (for ADO.NET). We are also shipping a QFE for .NET Framework 4 that adds the support for LocalDB to it (the updated .NET Framework 4 is already available, see this post for details).

Database as a File

LocalDB connection strings support AttachDbFileName property that allows attaching a database file during the connection process. This lets developers work directly with databases instead of the database server. Assuming a database file (*.MDF file with the corresponding *.LDF file) is stored at "C:\MyData\Database1.mdf" the developer can start working with it by simply using the following connection string: "Data Source=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=C:\MyData\Database1.mdf".

New Installer

In this CTP LocalDB is a shared feature of SQL Server Express available to try as an option during installation of any other version of SQL Express. Our ultimate goal is to offer LocalDB in a separate, simplified and smaller installer. We will keep you posted on our progress here.

LocalDB and User Instances of SQL Express

Users familiar with today's User Instances of SQL Server Express should feel right at home with LocalDB. In essence LocalDB offers the ability to create and start a "user instance" without the hassle of installing and maintaining the parent SQL Express instance. In that respect LocalDB could be seen as an upgrade of the User Instances feature of SQL Server Express.

Let me also take this opportunity to remind everyone that User Instances are on a deprecation path since SQL Server 2008.

LocalDB and SQL Express

LocalDB is not a replacement for SQL Server Express -- it is an addition to SQL Server Express lineup. While LocalDB is meant for developers, the regular SQL Server Express will continue to exist as a free SQL Server edition, fully compatible with and easily upgradeable to higher SQL Server editions.

LocalDB and SQL Server Compact?

Small and simple database, lightweight installation, connecting to a database file -- this will sound familiar to any developer using SQL Server Compact. The similarities are not accidental, as our goal for LocalDB was to be as easy to use as SQL Server Compact (while being as powerful and compatible with full SQL Server as SQL Express).

There are significant differences between LocalDB and SQL Server Compact:

Execution mode: SQL Server Compact is an in-proc DLL, while LocalDB runs as a separate process.
Disk usage: all SQL Server Compact binaries amount to some 4MBs, while LocalDB installation takes 140MBs.
Features: SQL Server Compact offers core RDBMS functionality like querying, while LocalDB provides a much richer set of features, including Stored Procedures, Geometry and Geography data types, etc.

Send us Your Feedback

Please share your feedback with us! Feel free to start a thread on SQL Express Forum, hit the "Email Author" button on this post or file a Connect item!

Q&A

Thank you all for your feedback. Please read below for answers to your questions!

Q: You say that LocalDB runs as a separate process. However, the main SQL Server site states "LocalDB has all of the same programmability features as SQL Express, but runs in-process with applications". So which is it?

A: LocalDB runs as a separate process - it does not run in-process. It does however run in the same security context as the calling application.

Q: Does SQL Express Denali still have the same memory limitations as that of SQL Server 2005 Express.

A: The memory limitation has not changed for Denali. At Denali RTM, LocalDB will have the same memory limitations as Express.

Q: What is the difference between LocalDB and SQL Server Compact Edition?

A: LocalDB is the SQL Server database engine. As such it offers functionality such as online backup, transactions and data integrity. LocalDB is designed for PCs but unlike Compact does not support small mobile devices like smart phones because of its hardware and disk requirements. Compact Edition is a shared file system database. It is not fully API compatible with SQL Server and does not support the same level of data integrity. SQL Compact is also not supported by the PHP Driver and PDO.

Q: Where can I find the option to install LocalDB?

A: You must install the SQL Server Express Edition and select LocalDB as a feature. You can unselect the Database Engine if you do not require SQL Server Express as a service.

A: Starting from SQL Server 2012 RC0 LocalDB can be downloaded and installed separately, as described in this post.

Q: How do you actually create a LocalDB database?

A: Connect to LocalDB and then use the usual T-SQL “CREATE DATABASE …” command.

Q: Can LocalDB be a subscriber for merge replication?

A: Merge replication is not supported by LocalDB.

Q: Can LocalDB be launched from a service?

A: LocalDB can be launched from a service, as long as the profile is loaded for the service account.

Q: Installed OK, but how about a nice little VS2010 C# (working) sample for creating a LocalDB database?

A: We are currently working on a sample application to illustrate the usage of LocalDB in C#.

A: Please see Using LocalDB in Visual Studio 2010 for a sample walkthrough.

Q: When will System.Data.SqlClient support LocalDB?

A: LocalDB is planned to be supported with .NET 4.0 with a QFE (not yet shipped) and future versions of .NET

A: LocalDB is supported by System.Data.SqlClient in .NET Framework 4 Update 4.0.2, as announced in this post.

ClickOnce Bootstrapper for SQL Server 2008 R2 Express

sqlexpress — Wed, 30 Mar 2011 20:52:00 GMT

I'd like to share this post about ClickOnce Bootstrapper for SQL Server 2008 R2 Express delivered for the community by Robin Shahan (RobinDotNet, MVP). It is great to see members of our community to step up and deliver where we failed to deliver. Thank you Robin!

http://robindotnet.wordpress.com/2011/02/06/how-about-a-bootstrapper-package-for-sqlserver-express-2008-r2/

Disclaimer – this ClickOnce bootstrapper package is not supported by Microsoft. You are using it at your own risk. Any comments, complaints or suggestions should be directed to the author.

- Krzysztof Kozielczyk

SQL Server 2008 R2 Express has been released

sqlexpress — Mon, 26 Apr 2010 04:25:29 GMT

The final release of SQL Server 2008 R2 was made publically available on April 21, 2010! SQL Server Express continues to ship in 4 different packages. In this release of Express, we simplified the installation wizard. For the November CTP release, I documented the Express installation process at http://blogs.msdn.com/petersad/archive/2009/11/13/how-to-install-sql-server-2008-r2-express-edition-november-ctp.aspx. The installation process has not changed since the November CTP, except some messages have been changed to indicate the final release.

I posted more information about this release to http://blogs.msdn.com/petersad/archive/2010/04/26/installing-sql-server-2008-r2-express.aspx.

SQL Server 2008 R2 Express Database Size Limit Increased to 10GB

sqlexpress — Wed, 21 Apr 2010 20:43:00 GMT

Great news for SQL Server Express users today! We increased the maximum database size in SQL Server 2008 R2 Express from 4GB to 10GB.

You can try it out today – just go to SQL Server 2008 R2 Download Page and select “SQL Server 2008 R2 Express” from the menu on the left side of the page:

http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx

FAQ

Is the database size limit increased in all editions of SQL Server 2008 R2 Express?

Yes, SQL Server 2008 R2 Express with Tools and SQL Server 2008 R2 Express with Advanced Services will allow 10GB databases as well.

What about CPU and memory limits? Are any other limits changed in SQL Server 2008 R2 Express?

No, the database size limit is the only limit we updated in SQL Server 2008 R2 Express. SQL Server 2008 R2 Express is still limited to 1 CPU and 1 GB or RAM.

Was the database size limit changed for previous versions of SQL Server Express?

No, the database size limit in previous versions of SQL Server Express (including SQL Server 2005 Express and SQL Server 2008 Express) stays unchanged at 4GB.

- Krzysztof Kozielczyk, Program Manager for SQL Server Express

How to take ownership of your local SQL Server 2008 Express

sqlexpress — Wed, 24 Feb 2010 01:13:00 GMT

Ward Beattie, one of our developers, has published a nice script that allows you to get sysadmin privilege to your local SQL Server Express instance. Obviously, you need to have administrative rights on your machine or access to an account that does.

The script can be very useful in the following situation:

When Visual Studio 2010 RC is installed by one user and being used by another user, the second user is unable to create databases, tables and other objects in SQL Server 2008 Express instance that is installed by Visual Studio 2010 RC. This is because only the user who installed SQL Server 2008 Express is granted sysadmin role in the SQL Server 2008 Express. So even if the second user has administrative privileges to her machine, she will not be able to manage the local SQL Server 2008 Express instance.

If this is your problem, just run Ward's script and become the master of your SQL Server 2008 Express!

Some Background

As surprising as it may seem at first, it all makes sense, when we think about it a little bit deeper. The user who installs SQL Server 2008 Express is granted sysadmin role during the installation. There is no way SQL Server 2008 Express could guess who else may be developing database applications and we certainly wouldn't like to give administrative access to the SQL Server instance to everyone J.

In the past (SQL Server 2005 Express) we were also granting sysadmin role to local Administrators group. But this doesn't help on Windows Vista and above, because User Account Control (UAC) is hiding the Administrators group from the security token assigned to the Visual Studio process. In order to be able to create database objects every Windows Vista or above user would have to run Visual Studio in elevated mode ("Run as Administrator"), which is not recommended (and inconvenient).

So the second user in our example has basically two options. She can ask the first user - the one who installed SQL Server 2008 Express - to grant sysadmin rights to her. Note that unless the first user is familiar with managing SQL Server security via T-SQL scripts, the task is not straightforward and may require installing SQL Server Management Studio Express on the target machine.

If the second user is a local administrator (or can get help from one), she has one more option available. She could follow steps for SQL Server administrator password recovery described by Raul Garcia here. She could then automate those steps in a form of a command script that could be reused next time she finds herself without access to her local SQL Server 2008 Express.

Or even better, she could just use Ward's script that does the automation of administrator account recovery steps for her

- Krzysztof Kozielczyk

Visual Studio 2010 Release Candidate is shipped!

sqlexpress — Thu, 11 Feb 2010 23:54:00 GMT

Visual Studio 2010 Release Candidate is available for download. Congratulations to the Visual Studio team! You can get it here:

http://www.microsoft.com/visualstudio/en-us/products/2010/default.mspx

From SQL Server Express’ point of view, Visual Studio 2010 brings two changes:

First, .NET Framework 4 support I described in the previous post, which allows you to build .NET Framework 4 applications that use and install SQL Server Express without forcing the end user to install two .NET Framework versions.

Second, ClickOnce bootstrapper now installs SQL Server 2008 Express with SP1. ClickOnce applications using SQL Server Express will no longer result in a compatibility warning.

- Krzysztof Kozielczyk

SQL Server Express and .NET Framework 4

sqlexpress — Wed, 09 Dec 2009 19:43:00 GMT

[update 1/17/2012: Fixed formatting and clarified all comments apply to all future Service Packs of SQL Server 2008 Express and SQL Server 2008 R2 Express]

[update 4/28/2010: All comments in this post apply to .NET Framework 4 RTM and to SQL Server 2008 R2 Express]

We have just shipped a new package of SQL Server 2008 Express SP1 that supports .NET Framework 4 Beta 2, extending the range of .NET Framework versions supported by SQL Server 2008 Express SP1 to .NET 2.0 SP2, 3.5 SP1 and 4 Beta 2.

The reason for this change is that Visual Studio 2010 Beta 2 ships with .NET Framework 4 Beta 2. It also distributes SQL Server 2008 Express, which originally required .Net 2.0 SP2 or 3.5 SP1. As a result Visual Studio team had to include two versions of .NET Framework in Visual Studio 2010 Beta 2 because of SQL Server 2008 Express – not the best user experience :-). Obviously that will also apply to any applications that will want to use the new .NET 4 Beta 2 and SQL Server 2008 Express; with the new package they will avoid having a dependency on two .NET versions!

We decided to find a way to add .NET Framework 4 Beta 2 support in SP1 for SQL Server 2008 Express. The biggest challenge was that .NET 4 Beta 2 was still under development when we were shipping the SP1 for SQL Server 2008. So we added the code to support .NET Framework 4 in SP1, but blocked the code in the initial release. We waited for .NET Framework 4 Beta 2 bits to stabilize and then verified it again that SQL Server 2008 Express SP1 works well with .NET Framework 4 Beta 2. Only now do we feel we’re ready to release the package of SQL Server 2008 Express SP1 that has the .NET Framework 4 Beta 2 support unblocked.

Another problem we realized was that SQL Server Engine can host only one version of .NET Framework (I mean SQL Server .NET Integration aka SQLCLR). So hosting .NET Framework 4 Beta 2 in SQL Server Engine means it cannot host .NET Framework 3.5 SP1. That would be breaking change, something we never do, especially in Service Packs. We want to make sure that our Service Packs never break any users’ applications. So we looked at the SQL Server 2008 Express and concluded that even without SQLCLR-based features it provides tons of valuable functionality. Therefore, we decided that SQL Server 2008 Express SP1 will install with .NET Framework 4 Beta 2, but will only host .NET Framework versions based on CLR 2.0 – .NET 2.0 SP2 and 3.5 SP1. The good part is that when SQL Server 2008 Express is installed with .NET Framework 4 Beta 2 only, nothing is lost. The user can just install .NET Framework 2.0 SP2 or 3.5 SP1 and all SQLCLR-based functionality will just start working.

Q & A

Here’s more information about this package, in the form of a Q&A list.

Does new SQL Server Express package install with old .NET Framework (2.0 SP2 or 3.5 SP1)?

Yes, the new package installs with .NET Framework 2.0 SP2 or 3.5 SP1 or 4 Beta 2. We added .NET Framework 4 Beta 2 as a new option without removing any of the existing options.

Will it work with .NET Framework 4 RTM?

Yes, the new package will work with .NET Framework 4 RTM once it ships.

Does all SQL Server Express functionality work with .NET Framework 4 Beta 2?

No, there are few features that still require .NET Framework 2.0 SP2 or 3.5 SP1. These features will be disabled if .NET Framework 4 Beta 2 is the only .NET Framework on the machine, but will start working the moment .NET Framework 2.0 SP2 or 3.5 SP1 is installed.

The list of features that don’t work with .NET Framework 4 Beta 2:

The CLR data types geometry, geography, and hierarchyid.
The dynamic management views for assemblies and spatial objects.
The CLR user-defined types, functions, aggregates, procedures, and triggers.

Why some features don’t work with .NET Framework 4?

SQL Server Engine can only host one version of .NET Framework. In SQL Server 2008 it is .NET Framework 3.5 SP1. So hosting .NET Framework 4 Beta 2 in SP1 would mean that we no longer host .NET Framework 3.5 SP1. We simply don’t do this kind of breaking changes, since we try very hard to make sure that our Service Packs do not break any applications.

Do all SQL Server editions support .NET Framework 4 Beta 2?

No, the .NET Framework 4 Beta 2 support is only available in the Express edition (in Runtime Only package).

Do all SQL Server Express editions support .NET Framework 4 Beta 2?

No, the .NET Framework 4 Beta 2 support is only available in the Express (Runtime Only) edition. SQL Server 2008 Express with Tools and SQL Server 2008 Express with Advanced Services still require .NET Framework 3.5 SP1.

What are the functional differences between original SP1 and the new one with .NET Framework 4 support?

There are no differences. We put all the code necessary to support .NET Framework 4 Beta 2 in the original release of SQL Server 2008 Express SP1, the code was just blocked. In this new package we just unblocked the code allowing for working with .NET Framework 4 Beta 2.

Are the any other changes to the prerequisites of this package?

No, all other prerequisites of this SQL Express 2008 SP1 package are the same as the original one. The only change in the prerequisites of this package is that it supports .NET 4 Framework Beta 2, in addition to .NET Framework 2.0 SP2 and 3.5 SP1 supported by the original and the new ones.

Is the original SQL Server 2008 Express SP1 replaced by the new one?

No, both SP1 packages are available and valid. Feel free to use any of them, as I said, apart from .NET Framework 4 Beta 2 support, they are identical.

Why didn’t we ship .NET Framework 4 support in the original SQL Server 2008 Express SP1?

When we shipped the SP1 for SQL Server 2008, .NET Framework 4 Beta 2 was still under development. We had to wait for the final version of .NET Framework 4 Beta 2 to test the Express SP1 with it and make sure they work well together.

Example: Imagine we just shipped SP1 with .NET Framework 4 Beta 2 support. Then imagine a week later we discovered a security issue in .NET Framework 4 Beta 2. Say the fix to this bug breaks SQL Server Express Setup. Our policy is that security issues take precedence before any other problems, so we would have fixed the security bug and break the SQL Server Setup. That would put many people (including myself) in a really bad place! :-)

Is there something wrong with the original SQL Server 2008 Express SP1?

No, as I said, both packages are identical (apart from .NET Framework 4 support). And we’re keeping both packages available on Microsoft Download Center, so if some application already depends on the original package

Will future Service Packs of SQL Server Express have two versions as well (one with .NET Framework 4 support and one without it)?

No, going forward all Service Packs for SQL Server 2008 Express and SQL Server 2008 R2 Express wil support .NET Framework 4.

Is there a new package for the general Service pack 1 for SQL Server 2008 (the one that can patch SQL Server Standard edition and above)?

No, there will be no special package for the general SQL Server 2008 Service Pack 1. .NET Framework 4 Beta 2 support is only available in the Express edition of SQL Server 2008 SP1, so updating the general SP1 package wouldn’t make any sense.

How can I get the new SQL Server 2008 SP1 with .NET Framework 4 Beta 2 support package?

The most natural place to get SQL Server 2008 Express SP1 with .NET Framework 4 support is … Visual Studio 2010 Beta 2. Just go here and install Visual Studio 2010 Beta 2 with SQL Server 2008 Express SP1.

Obviously, we’re also planning to include it in the final release of Visual Studio 2010.

You can also download it directly from Microsoft Download Center here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=01af61e6-2f63-4291-bcad-fd500f6027ff&displaylang=en

- Krzysztof Kozielczyk

SQL Server 2008 R2 November CTp Is released

sqlexpress — Fri, 13 Nov 2009 05:28:47 GMT

With the release of the SQL Server 2008 R2 November CTP, we have simplified the SQL Server Express (all editions and package) installation process. In this release, we have completed the following:

Reduced the number of clicks by 50% when performing an install.
All inputs are defaulted on Vista and above operating systems. (many inputs are defaults on Windows XP and Windows 2003, except for the service accounts)
Skip (auto advance) through the UI, meaning non-essential dialogs will still be skipped.
Reduced the size of the Express basic package

See http://blogs.msdn.com/petersad/archive/2009/11/13/how-to-install-sql-server-2008-r2-express-edition-november-ctp.aspx for step-by-step instructions on how to install.

Embedding SQL Server Express Survey

sqlexpress — Tue, 08 Sep 2009 22:03:21 GMT

Microsoft is committed to making the SQL Server the best database in the industry for embedding with your application. For us to achieve this goal, we need your candid feedback on your experience with embedding SQL Server Express. If you have used several different versions of SQL Server Express, focus on the latest version that you have used. When SQL Server 2008 Customer Technical Preview 3 is released, feel free to complete this survey again since several installation improvements have been made. This information will be used to improve our future releases including SQL Server 2008 R2 and SQL Server 11. This survey will be available until the end of November.

Instructions:

To register for the ISVs embedding SQL Express Survey, follow this link.* Once your registration is complete, click here and then click on “Respond to this Survey” to complete the profile.

*If you are already registered on Microsoft Connect, visiting the link will prompt you to login with the Windows Live ID e-mail address associated with your account (note that the email address we used to contact you regarding this invitation is not necessarily your account e-mail address). If you are not already registered on Microsoft Connect, visiting the link above will prompt you to register.

General SQL Server Express Survey

sqlexpress — Tue, 08 Sep 2009 21:58:19 GMT

Microsoft is committed to making the SQL Server Express installation experience the best in industry. For us to achieve this goal, we need your candid feedback on your experience with installing SQL Server Express. If you have used several different versions of SQL Server Express, focus on the latest version that you have used. When SQL Server 2008 Customer Technical Preview 3 is released, feel free to complete this survey again since several installation improvements have been made. This information will be used to improve our future releases including SQL Server 2008 R2 and SQL Server 11. This survey will be available until the end of November.

Instructions:

To register for the General Express Usage Survey, follow this link.* Once your registration is complete, click here and then click on “Respond to this Survey” to complete the profile.

ClickOnce bootstrapper for SQL Server 2008 Express with Tools

sqlexpress — Thu, 03 Sep 2009 22:02:00 GMT

I’d like to share the link to a blog post by Brock Weaver, where he explains how to add SQL Server 2008 Express with Tools to the list of prerequisites for ClickOnce packages:

http://youarenotexpectedtounderstandthis.blogspot.com/2009/08/vsnet-2008-prerequisite-for-sql-server.html

It is great to see people from the community extend our platform! Thanks Brock! That’s what we always hope for and that’s why we always try to design our software in an open and extensible fashion.

Disclaimer – this ClickOnce bootstrapper package is not supported by Microsoft. You are using it at your own risk. Any comments, complaints or suggestions should be directed to the author.

- Krzysztof Kozielczyk

Microsoft SQL Server 2008 R2 August CTP

sqlexpress — Fri, 14 Aug 2009 07:42:28 GMT

The August CTP of Microsoft SQL Server 2008 R2 is now published: http://technet.microsoft.com/en-us/evalcenter/ee315247.aspx. Give it a try and give us feedback!!!

This release does not have many changes around the Express when compared to SQL Server 2008. But the next CTP will contains several improvements as a result of your feedback. Here are some of the changes that will be in the SQL Server 2008 R2 Express product:

The Express runtime package is smaller
Setup UI Changes
- All user input defaulted (Vista and above): Therefore you can just click Next through the Installation Wizard. Less choices means better chance for success.
- All features selected by default:
- Number of clicks and screens: The number of steps or clicks to install have been reduced by 50%.
- Pre-reqs: Clearer messaging whether .NET or MSI needs to be installed before proceeding with the installation and a direct link to the download

Troubleshooting .NET 2.0/3.5 Installation errors

sqlexpress — Tue, 11 Aug 2009 01:36:16 GMT

The installation of .NET 3.5 can fail for several reasons. Sometimes the .NET 3.5 install fails and sometime SQL Server Setup fails to detect .NET is already on the machine.

If you view the %temp%\sqlsetup.log you can determine whether the .NET 3.5 installation failed or SQL Server Setup incorrectly detected .NET was already installed.

Here is an example were .NET 3.5 SP1 failed to install.

05/21/2008 07:32:55.750 ======================================================================
05/21/2008 07:32:55.765 Setup launched
05/21/2008 07:32:55.765 Attempting to determine media source
05/21/2008 07:32:55.765 Media source value not specified on command line argument.
05/21/2008 07:32:55.765 Setup is launched from media directly so default the value to the current folder.
05/21/2008 07:32:55.765 Media source: \\sqlbuilds\Improvements\clusterRefresh\10.00.1441.12\retail\SQLFULL_ENU\
05/21/2008 07:32:55.765 Attempt to determine media layout based on file '\\sqlbuilds\Improvements\clusterRefresh\10.00.1441.12\retail\SQLFULL_ENU\mediainfo.xml'.
05/21/2008 07:32:55.796 Media layout is detected as: Full
05/21/2008 07:32:55.796 Local setup.exe not found, so continuing to run setup.exe from media.
05/21/2008 07:32:55.796 /? or /HELP or /ACTION=HELP specified: false
05/21/2008 07:32:55.796 Help display: false
05/21/2008 07:32:55.796 Checking to see if we need to install .Net version 3.5
05/21/2008 07:32:55.796 .Net version 3.5 needs to be installed from media: \\sqlbuilds\Improvements\clusterRefresh\10.00.1441.12\retail\SQLFULL_ENU\
05/21/2008 07:32:55.796 Attempting to find media for .Net version 3.5
05/21/2008 07:32:55.796 Attempting to install .Net version 3.5
05/21/2008 07:35:36.725 Received exit code -1073741818 from installing .Net version 3.5
05/21/2008 07:35:36.725 .Net version 3.5 installation failed, so setup will close. Error code: -1073741818
05/21/2008 07:35:36.725 Setup closed with exit code: 0x84C40013
05/21/2008 07:35:36.725 ======================================================================

When .NET failed to install, your next step is to look at the .NET log files. The logs produced by the .NET Framework 3.5 setup wrapper:

%temp%\dd_dotnetfx35install.txt
%temp%\dd_dotnetfx35error.txt
%temp%\dd_depcheck_netfx*.txt

You should review these log to determine the cause for failure. If you still need help look at these resources:

http://blogs.msdn.com/astebner/archive/2007/07/31/4156781.aspx
Post the error to http://social.msdn.microsoft.com/Forums/en-US/netfxsetup/threads
How to: View SQL Server Setup Log Files: http://msdn.microsoft.com/en-us/library/ms143702.aspx
Troubleshooting an Installation of SQL Server: http://msdn.microsoft.com/en-us/library/ms143698.aspx

Installing SQL Server Express through the Web Platform Installer

sqlexpress — Tue, 04 Aug 2009 01:07:19 GMT

This topic will be expanded in the near future, but currently is a placeholder.

SQL Server 2008 Express Edition will be updated through Automatic Update

sqlexpress — Fri, 31 Jul 2009 01:20:35 GMT

Starting in September, we are considering automatically updating SQL Server 2008 including all Express Edition to Service Pack 1 (SP1). See http://blogs.msdn.com/petersad/archive/2009/07/30/sql-server-2008-service-pack-sp-1-on-microsoft-update-as-a-required-automatic-update.aspx for details.

Provide us feedback on this change.

“Embedding SQL Server 2008 Express in an Application” technical Article

sqlexpress — Thu, 09 Jul 2009 06:40:40 GMT

We recently published a technical article on how to embed and run SQL Server 2008 Express in unattended mode. The article is at http://msdn.microsoft.com/en-us/library/dd981032.aspx. We expect to revise this article at a later point in time. To ensure we continually improve the Express embedding experience, please provide us feedback. You can provide your feedback through this blog site or through the Microsoft connect site at https://connect.microsoft.com/SQLServer/Feedback. It is preferable to submit through https://connect.microsoft.com/SQLServer/Feedback to ensure we capture and track the feedback.

How should I Install SQL Server 2008

sqlexpress — Mon, 15 Jun 2009 22:54:38 GMT

There are two options for installing SQL Server 2008 Express.

1. Basic: Choose this option to install SQL Server Express with minimal effort by using the Web Platform Installer and a pre-defined defaults. Visit SQL Server Express download page to perform a basic install.

The Microsoft Web Platform Installer will only perform an installation will all features with an Instance Name called “SQLExpress”.

2. Customize: If you need to customize the installation or install on a Domain Controller, you can obtain SQL Server Express from the Microsoft Download Center.

Here are the Express editions available for manual download:

Express Package
If your system is? If your system is? If your system is?

Microsoft® SQL Server® 2008 Express Edition with Service Pack 1

X86

X64

WOW64

Microsoft® SQL Server® 2008 Express with Tools

X86

X64

.

Microsoft® SQL Server® 2008 Express with Advanced Services

X86

X64

.

Microsoft® SQL Server® 2008 Management Studio Express
X86

X64

.

More details on when I need to perform a customized installation

It is only necessary to perform a customize install under the following scenarios:

What if I want to customize the installation:

If you need to install differently than the defaults provided in option #1, you will need to perform a custom install. Here are the defaults that are used in option #1.

Option Description

Features Installs the Database Engine, Reporting Services, and Management Tools if available

INSTANCE NAME Installs a named instance called “SQLExpress”

Database Engine Service Account Configures the Database engine Service account to run under Network Service. This account is not supported on a Domain Controller, therefore it is necessary to perform an custom install on a Domain Controller.

Reporting Services Service Account Configures the Reporting Services Service account to run under Network Service, if available. This account is not supported on a Domain Controller, therefore it is necessary to perform an custom install on a Domain Controller.

SQL Server Database Admin Account Gives the user that installed SQL Server the ability to connect to the database engine.

ENABLE RANU Enabled User Instances

Upgrading

If you are upgrading from SQL Server 2000 or SQL Server 2005 to SQL Server 2008 a custom/advanced installation is required.

For more information, see “How to identify your SQL Server version and edition”.

Specifying an Instance Name other than SQLEXPRESS

SQL Server 2008 Express uses the default instance name of “SQLEXPRESS”. Changing the instance name or installing additional instances requires a custom installation. You cannot change instance names once the installation is complete. There are several scenarios of when you would to provide your own instance name.

The default instance name has already been used (installing multiple instances)
Your environment (application or standards) requires the use of an alternate instance name. For example, you may use instance names to segment groups or organizations within the company, i.e. Sales, Operation, Finance, etc.
You want to provide a higher level of abstraction from the underlying database software.

Embedding

If you develop software applications that embed SQL Server 2008 Express, custom installations are typically required. For more information, see Embedding SQL Server 2008 Express in an Application.

Installation on a Domain Controller

If you would like to install SQL Server Express on a Domain Controller, you need to perform an advanced installation since the default account (Network Service) specified by the Web Platform Installer is not supported on a Domain Controller. See SQL Server support on a Domain Controller for more information.

Further Setup Customization

Some SQL Server configuration settings can only be set during install. Once the installation is complete, you cannot change these settings. Any change to the configuration settings would require a new installation of the product. Some settings directly impact how data is stored and organized so changing the configuration setting would require significant data manipulation work in order to preserve the data.

Setting the non-Default Collation

Collations effect how characters (data) for a language or alphabet are recognized and sorted. A majority of SQL Server installations use the default collation. There are some situations where the use of a collation other than the default collation setting would be required.

Select a BINARY2 collation if binary code point based ordering is acceptable.
Select a Windows® collation for consistent comparison across data types.
Use new 100 level collation for better linguistic sorting support. For more information, see Collation and Unicode Support.
If you plan to migrate a database to the upgraded instance of ssNoVersion, select the collation that matches your existing collation of the database.

For more information, see Collation Settings in Setup.

Enabling Filestream

The Filestream configuration setting specifies where to store unstructured data such as text documents, images or videos. You can store this unstructured data inside the database or outside the database in the NTFS files system. The default setting is to store the data inside the database. Storing outside the database can cause data management complexities. You should consider enabling FILESTREAM if:

Objects being stored are, on average, larger than 1 MB.
Fast read access is important.
You are developing applications that use a middle tier for application logic.

For smaller objects, storing unstructured data in the database often provides better streaming performance.

For more information, see FILESTREAM Overview and FILESTREAM Storage in SQL Server 2008.

Enabling Error Reporting

Enabling the Error Reporting setting allows you to report Windows and SQL Server errors to Microsoft via the internet. Microsoft uses these error reports to improve future releases of SQL Server. Error reports contain only technical data. All error reports are confidential and anonymous.

Customizing Installation Directories

Installation directories are unique folder locations used for placement of:

Program software
System database
User databases and logs
Temp DB and log
Backups

During installation, you have the option to changing the default directories for some or all of the folders. Business requirements or environmental factors may dictate specifying directories other than the default.

Configuring SQL Server Service Accounts

SQL Server includes many services ranging from tools such as SQL Server Browser service to major product components such as Analysis services and Reporting services. During installation, you can specify which services to install and the account in which they run under. By default, most SQL Server services run under the Network Service account during initial setup. For better manageability and a more secure environment, it is recommended you:

Use separate accounts for each SQL Server service.
Configure service accounts to use the lowest possible permissions. This can reduce surface area for attacks.
Associate SQL Server services with low privileged Windows local user accounts, or domain user accounts.

For more information, see Setting Up Windows Service Accounts.

We need your Express Setup FeedBack

sqlexpress — Wed, 10 Jun 2009 09:32:31 GMT

In the next release of SQL Server, called SQL Server 2008 R2 we are working on addressing issues that complicated the installation of SQL Server Express. As the timeframe to address these issues is tight for this release we won’t be able to address all issues. However, still take the time to file your issue at http://connect.microsoft.com/. If we can’t address in SQL Server 2008 R2, we will consider addressing in the next major release.

Thanks,

Peter Saddow

Program Manager

SQL Server Setup Team

SQL Server 2005 Express edition, SP3 update for Windows 7

sqlexpress — Thu, 04 Jun 2009 22:04:50 GMT

On June 23rd, we will publish the Express SQL Server 2005 SP3 updates for Windows 7 through Microsoft Update. This is just a change in the detection logic to offer to a Windows 7 system. The SQL Server 2005 SP3 packages that have already been released to Microsoft Update and the Microsoft download center are unchanged.

These Express updates are affected:

Microsoft SQL Server 2005 Express Edition Service Pack 3 (KB955706)
Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 3 (KB955706)
Microsoft SQL Server 2005 Express Edition Toolkit Service Pack 3 (KB955706)

As usual, these updates will be available through Windows Automatic Update.

Express Package	If your system is?	If your system is?	If your system is?
Microsoft® SQL Server® 2008 Express Edition with Service Pack 1	X86	X64	WOW64
Microsoft® SQL Server® 2008 Express with Tools	X86	X64	.
Microsoft® SQL Server® 2008 Express with Advanced Services	X86	X64	.
Microsoft® SQL Server® 2008 Management Studio Express	X86	X64	.

Option	Description
Features	Installs the Database Engine, Reporting Services, and Management Tools if available
INSTANCE NAME	Installs a named instance called “SQLExpress”
Database Engine Service Account	Configures the Database engine Service account to run under Network Service. This account is not supported on a Domain Controller, therefore it is necessary to perform an custom install on a Domain Controller.
Reporting Services Service Account	Configures the Reporting Services Service account to run under Network Service, if available. This account is not supported on a Domain Controller, therefore it is necessary to perform an custom install on a Domain Controller.
SQL Server Database Admin Account	Gives the user that installed SQL Server the ability to connect to the database engine.
ENABLE RANU	Enabled User Instances