SQL Server Express WebLog

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 J. 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 SQ 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! J

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

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

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:

1. Reduced the number of clicks by 50% when performing an install.
2. 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)
3. Skip (auto advance) through the UI, meaning non-essential dialogs will still be skipped.
4. 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.

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.

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.

*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.

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

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

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

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

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.

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.

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.

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

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.

I have put together steps for you to build an Express Management Studio with Service Pack 1.  See these steps at http://blogs.msdn.com/petersad/archive/2009/05/29/creating-a-sql-server-2008-management-studio-express-slipstream-drop.aspx

When installing SQL Server 2005 RTM/SP1/SP2 and SQL Server 2008 RTM on Windows 7 RC it presents you with a warning message. The message says that this SQL Server version has a known compatibility issues with Windows 7 RC. This warning is genuine and applies to all SQL Server editions, including all Express editions. The incompatibility was fixed in SQL Server 2008 Service Pack1 and SQL Server 2005 Service Pack 3.

You can safely continue the installation by clicking "Run program" button and apply the latest service pack afterwards. In some cases you may be presented with this warning message multiple times during the installation process.

Once the installation is complete please apply the latest SQL Server service pack. You can use Windows Update to do so. Just be sure to click "Get updates for more Microsoft products" to start obtaining updates for SQL Server. SQL Server service packs should show up on the list of optional updates. You may need to kick-off the scan for updates manually if not - you can do it by clicking "Check for updates" link on the left pane of main Windows Update window.

If for some reason Windows Update doesn't work for you, please download and install latest service pack manually.

Download SQL Server 2005 Service Pack 3

Download SQL Server 2008 Service Pack 1

If you're installing SQL Server 2005 Express (any Express edition) or SQL Server 2008 Express (Runtime Only) you can just download the SQL Server Express package containing the latest service pack. This may seem counter-intuitive if you already have some Express package, but installing it and then downloading and applying service pack is going to take longer than downloading a package already containing the latest service pack and installing it. Plus, in case of SQL Server 2008 Express the service pack package is actually bigger than the SQL Server Express package, since the service pack is shared with all SQL Server 2008 editions.

Download SQL Server 2005 Express Edition SP3

Download SQL Server 2005 Express Advanced Edition SP3

Download SQL Server 2005 Express Toolkit Edition SP3

Download SQL Server 2008 Express SP1

If you need to install SQL Server 2008 Express edition other than Runtime Only on Windows 7 RC and need to avoid the warning dialogs, you can create SQL Server 2008 with Service Pack 1 package yourself. Please follow steps described by Peter Saddow here.

Creating a merged (slipstreamed) drop containing SQL Server 2008 RTM + Service Pack 1

-  Krzysztof Kozielczyk