Walkthrough: Installing and Configuring SQL Server 2008 R2 Express with Advanced Services

[ This document was written for WCF Services Version 1 Service Pack 2 and might not be up to date Please see Release Notes or Changelog for a list of changes since WCF RIA Services ]

The Open Ria Services walkthroughs assume that you have SQL Server 2008 and the AdventureWorks database installed. SQL Server 2008 R2 Express with Advanced Services is the minimum required version for the installation and use of the required AdventureWorks databases. The normal SQL Server 2008 R2 Express version does not have the required services. This walkthrough outlines the steps needed to create an instance of SQL Server 2008 R2 Express with Advanced Services.

The procedure for installing the AdventureWorks database is outlined in the Walkthrough: Installing the AdventureWorks OLTP and LT sample databases topic.

Warning: Visual Studio, the Silverlight Runtime and SDK, and Open Ria Services and the Open Ria Services Toolkit must also be installed to use the Open Ria Services walkthroughs. The installation points for these programs are provided in the Prerequisites for Open Ria Services topic.

Warning: The AdventureWorks database installer only functions with an instance of SQL Server that was newly created with SQL Server 2008 R2 Express with Advanced Services or any retail version of SQL Server 2008 R2. It does not work properly on upgraded instances.

Installing and Configuring SQL Server 2008 R2 Express with Advanced Services

Tip: It is recommended that for these walkthroughs you install a completely new instance of SQL Server 2008 R2 Express with Advanced Services.

To install a new instance of SQL Server 2008 R2 Express

1. Download the correct version of SQL Server 2008 R2 Express with Advanced Services for your OS architecture (x86 or x64).

2. When the download completes, click on Run

   After the Installer extracts the required files, the SQL Server Installation Center appears.

3. In the left panel, select Installation, then click New installation or add features to an existing installation.

   The SQL Server 2008 R2 Setup dialog appears, initializes the setup files, and Runs a rule check.

4. On the Installation Type screen, select New installation or add shared features and click Next.

5. Check I accept the license terms, and click Next.

[!NOTE] A restart might be required at this point if files affected have operations pending.

1. Click Select All, and then click Next.

2. If this is the first installation of SQL Server on this machine, you can select Default instance, otherwise select Named instance.

   Type in an instance name (that you can remember), and click Next.

[!TIP] SQL Server instance names are generally written in all caps, as a single word.

1. Leave the default settings for Server Configuration and click Next.

2. On the Account Provisioning tab, select (the default) Windows authentication mode, and click Add Current User.

3. On the FILESTREAM tab, check Enable FILESTREAM for Transact-SQL access, check Enable FILESTREAM for file I/O streaming access, and click Next.

4. Leave the default settings for Reporting Services Configuration and click Next.

5. Leave the default settings for Error Reporting and click Next.

6. When the installation completes, click Next and then click Close.

Now that the installation of the new instance is complete, it needs to be configured. These configuration settings enable the connections needed to access the SQL Server Instance.

Configure Your New Instance of SQL Server for the Installation of the AdventureWorks Databases

1. Click on the Start Menu, select All Programs, Microsoft SQL Server 2008 R2, Configuration Tools, and click SQL Server Configuration Manager.

   The SQL Server Configuration Manager window will open.

2. In the left pane select SQL Server Services, and right click on your SQL Server (\) instance. In the pop up menu select Properties.

[!IMPORTANT] "<InstanceName>" is a placeholder and needs to be replaced with the actual name of the instance you are working with.

The **SQL Server (\<InstanceName\>) Properties** dialog is now open.

1. On the FILESTREAM tab ensure that Enable FILESTREAM for Transact-SQL access and Enable FILESTREAM for file I/O streaming access are both checked.

2. Click OK.

3. Back in the SQL Server Configuration Manager, ensure that SQL Full-test Filter Daemon Launcher (\) is running. If it is not running, right click on it, and select Start.

4. In the left pane of the SQL Server Configuration Manager, expand SQL Server Network Configuration, and click Protocols for \.

5. Double-click on Named Pipes, and in the Named Pipes Properties dialog that appears, select Yes in the dropdown next to Enabled. Click OK.

6. Repeat the previous step for TCP/IP protocol as well to enable it. Accept the default values for the Keep Alive and Listen All properties.

7. In the left pane of the SQL Server Configuration Manager, select SQL Server Services again, right click on SQL Server (\) in the right pane, and select Restart.

[!TIP] It is best practice, after modifying the configuration of an SQL Server instance to restart the instance, so that the changes which were made are available.

The installation and configuration of your SQL Server 2008 R2 Express with Advanced Services instance is now complete. You are now ready to install the AdventureWorks OLTP and LT sample databases. The step for installing the database is provided in the Walkthrough: Installing the AdventureWorks OLTP and LT sample databases topic.