MS SQL 2005 Server Hosting

Now that you have Microsoft SQL Server 2005 Express Edition installed, if you didn’t before, you can now move on to configuring it so that it works they way you want it to and so that it is able to function with any web applications that you might want to use with it.

To begin with, you will need to install Microsoft SQL Server Management Studio Express so that you are able to add and modify databases on your new SQL Server; without this program it won’t be possible. Microsoft SQL Management Studio Express can be downloaded from the Microsoft website free of charge: http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en.

Once you have installed Microsoft SQL Server Management Studio Express, you can proceed to configuring your installation of Microsoft SQL Server 2005 Express to enable you to manage it.

The first thing you will need to enable is remote connections and the ability for your instance of Microsoft SQL Server to appear in the SQL Server browser when somebody is trying to use SQL Server Management Studio Express to connect to your database server. Remote connections allow you to connect to your private server using remote desktop console server window. You need to ensure that you have additional terminal server license installed on the windows server if you wish to allow multiple database managers to access your SQL database Server.

In order to do this, you need to open the SQL Server 2005 Surface Area Manager, by navigating to ‘Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Database Server Surface Area Configuration’. The Surface Area Configuration Manager should then open.

You then need to select ‘Surface Area Configuration for Services and Connections’ so that you can configure the necessary connection settings and enable the SQL Server browser service on your instance of Microsoft SQL Server 2005.

The first option you will need to set is the remote desktop connections one, so that you are able to connect to your SQL Server from any remote location where you are able to use a ‘computer that is connected to the internet. To do this you will need to select ‘Remote Connections’ from under ‘Database Engine’ on the left hand pane.

You will then need to choose ‘Local and Remote Connections’, and the ‘Using both TCP/IP and Named Pipes’; by choosing these options you are ensuring that any dynamic web applications that might need access to hosted databases are able to, and also ensuring that you are able to access your SQL Server for administration from any remote internet connected location.

If you then proceed to clicking the ‘Apply’ button near the bottom of the window, an message should popup telling you to restart the SQL service so that any changes that you have made are applied.

To restart the SQL service, you will need to select ‘Service’ from under ‘Database Engine’ on the left hand menu. You will then need to press the ‘Stop’ button, and wait for the SQL service to stop. Once it has stopped, you can then press the ‘Start’ button to get it running again.

Next, you will need to enable the MS SQL Browser service so that you can find your instance of Microsoft SQL Server when you are using the SQL Server Management Studio locally or on a machine that is connected to the same network as your SQL Server. To do this you will need to select ‘Service’ from under ‘SQL Server Browser’ which is located on the left hand menu.

Since this service is normally disabled on a standard installation of Microsoft SQL Server 2005 Express Edition, you will need to select ‘Automatic’ from the ‘Startup Type’ drop down menu. If you then click the ‘Apply’ button near the bottom of the screen, you should be able to start the service by clicking the ‘Start’ button.

The next thing to do is to configure your Microsoft SQL Server 2005 instance to allow login using both SQL Server and Windows authentication methods, so any scripts that you create for use with a Microsoft SQL Server database are able to login to your instance.

You will first need to open Microsoft SQL Management Studio Express, which you should already have installed, by navigating to ‘Start > All Programs > Microsoft SQL Server 2005 > Microsoft SQL Server Management Studio Express’. If this is your first time using the application, then a message may appear saying that the environment is being configured for first time use. After that, you should be presented with the login screen.

By default, the server name field should be field in with the exact address to the location of your instance. If this is your first run of the program, and the field hasn’t been pre-filled, then all you need to do is enter ‘localhost\INSTANCE’, replacing ‘INSTANCE’ with the name of your Microsoft SQL Server 2005 instance.

In the case that you are running the SQL Server instance on the same machine, all you need to do is select ‘Windows Authentication’ from the authentication drop down box; in the case you are using Windows Authentication, you shouldn’t need to provide a password since you are connecting with your current user account. One thing to note is that SQL Server Authentication can’t be used on the first run, unless specified otherwise during the setup.

The first thing you will need to configure, if not already done is to allow SQL Server authentication login to your Microsoft SQL Server 2005 instance, otherwise scripts and web pages that you create for use with SQL Server 2005 databases won’t be able to access what they need.

To do this, you will need to right-click on your server in the left hand pane in SQL Server Management Studio Express (this is also known as the ‘object explorer’) and click properties. You will need to select ‘Security’ from the left hand menu, and ensure that ‘SQL Server and Windows Authentication mode’ option is selected.

If you press the ‘OK’ button at the bottom of the screen, and then restart your SQL instance to reflect the new changes.

That’s it! You have now configured SQL Server 2005 Express Edition to ensure that it is accessible by both yourself and any dynamic web pages or scripts that you write that need access to SQL Server 2005 databases that you have created.