How to install Microsoft SQL Server 2005 Express Edition

Last updated 2008-04-30

How to install Microsoft SQL Server 2005 Express Edition for use as the MonTel Master Client database system. The most recent version of MSSQL Server 2005 Express Edition was SP2 as the time this article was constructed.

This is the recommended version from MonTel 2.12 onwards, though MonTel with work with with SQL Server 7, MSDE, SQL Server 2000 and MSDE 2000.

The installation is different from the default installation because we need to configure remote computers to be able to access the database.

Download

On the Microsoft SQL Server Express Web site ( http://www.microsoft.com/sql/editions/express/default.mspx or even http://msdn.microsoft.com/en-au/sqlserver/default.aspx) and follow the link Get SQL Server 2005 Express. After optionally registering, download "Install Microsoft SQL Server 2005 Express Edition" from the left hand column. (Microsoft may change this layout in the future, so specifically download SQLEXPR32.exe (http://go.microsoft.com/fwlink/?LinkId=65212).

If you want to administer the SQL Server, and it is a good idea to have this just in case, download the Microsoft SQL Server Management Studio Express Service Pack 2: "SQL Server Management Studio Express", and SQLServer2005_SSMEE.msi (http://go.microsoft.com/fwlink/?LinkId=65110) as well.

If you have not installed the Microsoft .NET Framework 2.0 you will need to download and install it ( http://msdn2.microsoft.com/netframework/).

You may also need the Windows Installer 3.1 v2 ( http://www.microsoft.com/downloads/details.aspx?FamilyId=889482FC-5F56-4A38-B838-DE776FD4138C).

Copy the downloaded files to a temporary folder on the computer which will be the SQL Server. This may or may not be the MonTel Server. Regardless of where it is make sure that it is part of the the organisations backup regime.

Down load the netwiz sql express install batch file zip archive and extract SQLEXPR32_inst.bat. Or create a batch file in your temporary folder, (and name it SQLEXPR32_inst.bat for ease of reference) with the following command line (which should be all on one line even if not displayed in the browser as such).

SQLEXPR32.EXE /qb addlocal=all InstanceName=SQLEXPRESS DisableNetworkProtocols=2 SECURITYMODE=SQL SAPWD=MonTelSys

To change the SAPWD to another value if you don't want the password to be MonTelSys, change MonTelSys to something else and remember or record it somewhere secure. This command line will install SQL with the Mixed mode authentication on, with the default SQLEXPRESS instance, and TCP/IP networking as the default network protocol. Without using this command line you will need to set up the networking yourself.

Preparation

Make sure that you remove any existing MS SQL server systems from the computer, such as MSDE and SQL 6, SQL 2000 and so on. It is possible to upgrade from SQL 2000 if you have installed Service Pack 3, though it is probably best to go the whole way and upgrade first to SP4. At the same time it would be useful to clean up any other unused programs, temporary data and perhaps defrag the hard drive to maximise performance from the system. Having said that MonTel is pretty light on SQL Server resources, so even on a reasonably large site performance is unlikely to be an issue.

And make sure you are not going to be installing it to a compressed volume (or more importantly, that the data files are not going to be on a compress volume).

For more information see Installing SQL Server Express at http://msdn2.microsoft.com/en-us/library/ms143441.aspx.

Install

To install simply run the batch file (SQLEXPR32_inst.bat) we created. It will automatically run the installation installing both the server and the browser and exit when complete.

Getting past the firewall

In some situations including a default installation of Windows XP service Pack two a firewall may block TCP/IP connection to the SQL server. For third party local or personal firewall software refer to the vendors documentation. You will need to make sure that any firewall software allows Port 1433, and/or allows sqlserver.exe and sqlbrowser.exe to act as a network server. Obviously you would want to block access to this port on your network from the internet, which would be a default of most internet firewall software. We are only talking about software firewalls on the local server PC.

For Windows XP SP 2 Click Start, click Control Panel, and then click Security Center. Scroll down to Manager security settings and double click on the settings for Windows Firewall.

On the Exceptions tab, in the Programs and Services box, add you may see that SQL Server is listed, but not selected as an exception. In this case may sure the check box is ticked (selected) so that Windows will open the 1433 port to let in TCP requests. Alternatively, if you do not see SQL Server listed, do the following: Click Add Program. You may sqlserver.exe listed, though this is not SQL Server Error and Usage Reporting or SQL Server Surface Area Configurations.

Click Browse. Navigate to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BINN, (assume it was installed to C: drive) and add the file sqlserver.exe to the list of exceptions. You will now need to add the SQLBrowser. This can be found in a slightly different location: C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe and add it in the same way.

Depending on your network set up it might also be a good idea to change the scope to the local network.

In the following example, there is only one sub net that needs access, so for each of the two exceptions you have just added, click the Edit button and then the Change Scope button.

In change scope select the My network (subnet) only radio button.

Testing

Now test that the server can be reached from other computers on the network. Probably the best way to do this is to use SQL Server Management Studio Express (download links shown above), by installing it on a remote computer on the same network and attempting to get a connection.

The server type is: Database Engine. You will to know the name of the computer (in the example below it is VC136) and the instance name - which by default and in the batch file example is SQLEXPRESS. Login with SQL Server Authentication and use the Login of 'sa' and the password of 'MonTelSys' or the new password if it has been changed.

You should have a successful connection. If not, check that the password was entered exactly, for example note the capital letters in "MonTelSys". Other causes of failure include, MDAC issues, network failures or configuration problems, firewall problems or SQL server surface area configuration or installation problems. Make sure that there is no other installation of SQL Server on the same computer.

For assistance with SQL Server see: Getting SQL Server 2005 Assistance (http://msdn2.microsoft.com/en-us/library/ms166016.aspx).

Change the password

Once MonTel has been installed (or even better, now, if you remember to record the password), you must change the default password in the batch file to a more sensible password for the 'sa' account. You can change the password from inside MS SQL Server Manager Studio by drilling down through SERVER\SQPEXORESS->Security->logins.