Deployment Solution

SQL 2005 Express is available free from Microsoft. It is a replacement for MSDE which will perform much better than its predecessor. SQL 2005 has no workload throttling like MSDE does and it supports up to 2 CPUs and 2GB RAM, where MSDE only supports 1 CPU and 1GB RAM.

If you are using MSDE and are considering managing more clients than you currently are, or are having problems with your Deployment Server or Notification Server running slowly even after optimizing your configuration, you will want to install SQL Server 2005 instead of MSDE.

SQL 2005 Express supports servicing SQL over your network and so it can be used for Deployment Server and Notification Server as long as you don't have enough clients that you will need more than 2GB RAM. However, network is not enabled by default, so there is some configuration required to get SQL Server 2005 Express to work correctly.

This article will guide you through the process of installing and configuring SQL Server 2005 Express so that you can use it with Deployment Server or Notification Server.

Download SQL Server 2005 Express

First, you will need to download SQL Server 2005 Express. You have two options to download. You can either download SQL Server 2005 Express without any management tools (55 MB) from http://www.microsoft.com/downloads/details.aspx?FamilyId=31711d5d-725c-4afa-9d65-e4465cdff1e7&displaylang=en or you can download SQL Server 2005 Express with management tools (256 MB) from http://www.microsoft.com/downloads/details.aspx?FamilyId=5b5528b9-13e1-4db9-a3fc-82116d598c3d&displaylang=en

I recommend installing "SQL Server 2005 Express with Advanced Services", it is worth the extra 200 MB to be able to troubleshoot your SQL Server if anything ever goes wrong.

Install SQL Server 2005 Express

SQL Server 2005 Express requires .NET Framework 2.0, if your server does not have .NET Framework 2.0 you will need to download .NET Framework 2.0 and install it on your server. When you are ready to install SQL 2005 Express, follow these steps:

1. Run SQLEXPR_ADV.EXE to install SQL 2005 Express.
2. When you get to the Registration Information page, uncheck "Hide advanced configuration options" and click Next.
3. On the Feature Selection page, enable "Connectivity Components" and "Management Studio Express".

   Click Next.

4. On the Instance Name page you have two options. If you are installing to a server that does not have another install of SQL and will never have another install of SQL, choose "Default instance", otherwise leave it set to a Named instance of SQLExpress.

   Click Next.

5. On the Service Account page, make sure "SQL Server" and "SQL Browser" are both enabled (checked).

   Click Next.

6. On the Authentication Mode page, you can either leave it configured to use Windows Authentication Mode only or Mixed Mode (Windows Authentication and SQL Server Authentication). I usually enable Mixed Mode so that if for some reason my security gets messed up I always have a backdoor account (the SA account). If you choose Mixed Mode, you will need to enter and confirm a password for the SA account. Click Next and click Next again.
7. On the Configuration Options page, enable "Add user to the SQL Server Administrator role".

   Click Next, click Next again, and then click Install.

8. When the installer completes, click Next and then Finish.

Enable TCP/IP

Now that SQL 2005 Express is installed, we need to configure the Network options that are disabled by default.

1. In the Start Menu, open Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
2. In the Surface Area Configuration utility, click the link "Surface Area Configuration for Services and Connections"
3. Go to MSSQLSERVER > Database Engine > Remote Connections
4. Enable "Local and remote connections"
5. Select "Using TCP/IP only". If you need Named Pipes then choose "Using both TCP/IP and named pipes".
6. Click OK.
7. Close the Surface Area Configuration utility.

Add Firewall Exceptions

If you have a software firewall installed on your server, you will need to add exceptions for the SQL Server and SQL Browser services. If you are using Windows Firewall, do the following:

1. In your Control Panel, open the Windows Firewall applet.
2. Go to the Exceptions tab.
3. Click Add Program
4. Browse to and select "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe"
5. Click OK.
6. Click Add Program
7. Browse to and select "C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe"
8. Click OK and then click OK again.

If you are using a third-party software firewall, you will need to follow their instructions to add exceptions to sqlservr.exe and sqlbrowser.exe.

Restart the Services or Reboot the Server

When you have completed all of these steps, you will need to restart the SQL Server and SQL Browser services to complete the process. If you still have problems, you may need to restart your server.