SQL Server 2005 Express

Installing & Getting Started with Microsoft’s Latest Free Database System

 

 

SQL Server 2005 Express represents Microsoft’s latest generation of database technology.  What was once a small database meant only for technical people to experiment with has developed into a full-fledged database system capable of supporting many standalone applications.  Improvements have been made in memory management, security and ease of use just to name a few.  In this tutorial we will walk you through the installation, configuration, maintenance and testing of SQL Server 2005 Express as well as walk you through Microsoft’s latest management system for the database, SQL Server Management Studio Express.

 

 

Did You Know?

For more information on SQL Server 2005 Express, visit the product information page located at http://msdn.microsoft.com/sql/express/

 

 

System Requirements

 

Before we get started, let’s take a moment and make sure that we have the right hardware to run SQL Server Express.

 

 

Software Requirements

 

In order to install SQL Server 2005 Express and SQL Server Management Studio Express we will need to download some software.  All of this software is free of charge courtesy of Microsoft. You will need the following packages downloaded:

 

 

 

 

Important Tip!

You should be logged on with an account that has administrator access to install SQL Server 2005 and other components.

 

 

 

 

 

Getting Started – Installing .Net Framework 2.0

Before we can begin installing SQL Server 2005 Express we must install the .Net Framework v2.0.  You may already have this installed on your computer.  To check, we will use our Control Panel applet to see which wizards are loaded.  To do this, go to your Start Menu -> Settings -> Control Panel -> Administrative Tools. 

 

You are looking for any mention of the .Net Framework 2.0.  In our example here, you can see this machine does not have .Net 2.0 installed as the only configuration options are for v1.1.

 

If you have 2.0 already installed, you can skip to the next section.  Otherwise, follow along as we install.

 

Locate the file you downloaded above (dotnetfx.exe) and double-click to begin installation.  The installation itself is straightforward.  Simply follow the prompts and let it install automatically. 

 

Be patient during the install.  It can take several minutes even on a fast machine to install the .Net 2.0 framework.

 

 

Important Tip!

After you install .Net 2.0 run Windows Update to download any service packs and fixes that may have been released for the product. 

 

 

Installing SQL Server 2005 Express Edition

Once we have the .Net Framework installed we are ready to proceed with installation of our SQL Server.  In our setup we are going to install the server in mixed-mode authentication.  This will allow us to not only login to the database with our Windows account but also let us specify specific logins to the database without having to have a Windows account associated with that login.

 

We’ll also install SQL Server 2005 with a different instance name than what is supplied.  To give you a brief overview, you can have multiple instances of SQL Server on a machine.  For example, you may want to have a test instance and a production instance.  Each instance has its own tables, security and access methods.  They simply share the same database engine. 

 

If you are ready, let’s get started!  Locate the SQL Server 2005 Express file you downloaded above.  It will have a filename of SQLEXPR.EXE.  Double-click on it to get started.

 

The first screen you get is asking you to install prerequisite components necessary to support the SQL Server 2005 install.  Depending on your computer setup you may have more or less components listed than show up in our image to the right.

 

Simply click Install to let the program install the necessary files.  Installation of these prerequisites will usually take less than one minute.

 

When finished with the prerequisite installation the Install button will change to a Next button.  Click Next to continue installation.

 

You will now be presented with a screen that checks your computer for minimum hardware and software requirements necessary to support SQL Server Express 2005.  If your computer fails to meet any of the necessary requirements you will be able to review the necessary steps you should take to fix the problem before installation can continue.

 

As you can see in our example, our sample system meets all the requirements for installation.  We can now click Next to continue installation.

 

You will be presented with several information screens next which you can simply click Next to continue past.  When you get to the screen that asks you to enter your name and company name pay close attention to the checkbox located at the bottom of that screen.

 

In order for us to setup SQL Server 2005 with mixed-mode authentication and change the instance name we will need to make sure this checkbox is unchecked. 

 

When you have verified this setting, click on Next to continue.

 

You can now continue until you get to the Feature Selection screen as shown on the right.  Here we take all of the default options for installation with one exception.  We must click on the dropdown box to the left of Connectivity Components and select the option to install on local hard drive.

 

We will need these connectivity components for setting up any TCP/IP connections to our database as well as ODBC connections.

 

You will notice that once you select the option to install to the local hard drive the red “X” disappears and is replaced by a picture of a hard drive.

 

Once you have verified your selections we are ready to begin installation.  Click Next to continue.

The next screen you will receive is the Instance Name selection screen.  We have two options here:  We can either keep the default instance name or create a named instance.  For our example, we want to create a named instance.

 

Select the Named Instance button and then type in “TEST” for your named instance.  This will create a database instance that is referred to as TEST on the local machine.

 

After typing in your named instance name click Next to continue.  You will then be presented with a screen that asks you about Service Account logins.  We can accept all of the default options on this screen and just click Next to continue.

The next screen you are presented with is the Authentication Mode screen.  Remember above we said that we wanted to install our database in mixed-mode authentication.  Simply select the radio button next to mixed-mode.  You will notice that now you are asked for a password for the sa account for SQL Server.  The sa account is the master database owner account.  It can do anything and everything to the database.  You should choose a strong password and keep it in a safe place.  This password will be necessary for doing some of the more advanced administrative tasks on your database.

 

 

In our example here, we will use the password of “test123”.  You would not use this password in a production database as it would be too easy to guess.

 

Once you have verified the password, click Next to continue with the installation.

 

The next screen you will be presented with is the Collations Screen.  Your database can see in data in many ways.  Sometimes we may want to have support for case sensitive data or binary data.  For our example though, we will just keep the default selections and hit Next to continue.

 

You are now at the User Instances screen.  This screen is asking how users who aren’t administrators should run the database itself.  You can take the default selection of “Enable User Instances”.  This is merely a security feature and will not change how your database performs.

 

We’re almost done!

 

The next screen is the Error and Usage Reporting Settings screen.  This is asking us if we want to send performance data and error reports to Microsoft.  This really isn’t necessary in our case, so let’s leave them unchecked as they are by default and click Next to continue.

 

You are now presented with the final confirmation screen.  It will give you a review of what will be installed.  When you are ready to proceed with the database being installed on your system click the Install button.

 

Installation may take a few minutes, so please be patient during this time.

 

When installation is finished you will receive an overview screen as shown on the right.  If there are any errors you will be see a red “X” next to the name of the component that failed.  You can click on the name and find out more about the success or failure of each installation.

 

When you are finished reviewing this screen, click Next to continue.

 

One more button to click, “Finish” on the final informational screen and you are done!

 

Congratulations, you have now installed SQL Server 2005 Express on your machine!

 

 

 

 

Installing SQL Server 2005 Management Studio Express

 

Now that we have our database installed and running, let’s install a tool that will help us manage it.  SQL Server Management Studio Express is an easy to use, free product from Microsoft that will let you manage and view your database in a graphical interface.

 

To do this, let’s find the file we downloaded from above.  The filename is SQLServer2005_SSMSEE.  Double-click on this file and let’s get started!

 

Installation of this program is simple.  The first few screens will ask you to accept the licensing agreement, enter your name and choose what to install.  Since there is only one product, we can just take the default installation list and continue along.  Installation will take less than a minute.

 

When complete you will need to reboot your system for changes made to take effect.  Be sure to save any work you may be doing and select Yes to allow the reboot.

 

 

 

 

Important Tip!

Do not attempt to use the management studio without rebooting first!

 

 

 

Enabling the TCP/IP Protocols for Web Applications

 

Congratulations, you now have both SQL Server 2005 Express and the SQL Management Studio Express installed!  You did remember to reboot, didn’t you?

 

Now that we have them installed and your SQL database is up and running we need to tweak your connection settings.  Since we will be using web applications to access the database we need to make sure that the TCP/IP protocol is enabled.  This protocol is how machines connected over the Internet and other networks talk to each other.  By default TCP/IP is disabled for SQL Server 2005, but it is an easy task to get it enabled.

 

To begin, we will need to run the SQL Server 2005 Configuration Management Tool.  This tool is located in your Start Menu -> Programs -> SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. 

 

You will now select SQL Server 2005 Network Configuration in the left-hand menu and expand the menu item so Protocols for TEST is displayed.  Remember, TEST was the name of our SQL Server instance.  If we had several instances on this box we would see a configuration item for each.

 

Once you have selected the Protocols section, notice your right-hand pane is now filled in with all the available connection protocols and their status.  Choose TCP/IP, right click on it and select Enable.

 

Once enabled, you will need to restart your SQL Server to pick up the changes.  This can also be done from within the Configuration Manager.  Select SQL Server 2005 Services from the left-hand window pane.  You will now see your SQL Server instance running.  Highlight this entry, right click and choose Restart.

 

Once it restarts TCP/IP connections are now enabled.  You are all done!

 

 

Testing Out Your New Setup Using Microsoft Access

 

Now that we have everything installed, let’s make sure that we’ve covered all our bases.  Using Microsoft Access we are going to make sure we can connect to our database and see the default tables that get installed. 

 

We are going to use Access only to test our connection and not make changes on any of the databases.  This is because SQL Server 2005 was released after all current versions of Access and some features are not compatible, especially when trying to enter data into SQL Server from Access.  This problem is expected to be corrected with the upcoming release of Microsoft Office 2007.

 

To test our connection, start Microsoft Access and select the File menu and then New.  From the pane that displays choose “Project using existing database…” which will be the third item down on the new file type list.


Access will ask you for a name of the file and where to save it.  You can call it whatever you like and you can save it wherever is most convenient for you.  We won’t be using this file outside of testing our setup, so it can be deleted when we are finished.

 

You are now asked to specify details about your database. 

 

In order to connect to your database you need to know two things:  Your database instance name (remember, we called ours TEST) and your computer name.  The easiest way to find out your computer name is go to a DOS prompt and type in “ping localhost”.  Your computer name will be displayed back to you. 

 

You can also use the dropdown box located next the server name field.  This will search your local network for any available SQL Servers and display them as a list for you to choose from.

 

Once you have established your computer name enter it in the server name field followed by the instance name in the following format:  SERVERNAME\INSTANCE-NAME

 

For example, since we are connecting to the TEST instance on my machine which is called M002405 I would enter:  M002405\TEST

 

Next, you will enter the sa user name and password by selecting the radio button for using a specific name and password. 

 

Finally, you can test out your connection two ways - either by clicking on the “Test Connection” button or by hitting the dropdown arrow by the select database field.  If you see a list of available databases you have successfully connected up to your SQL Server!

 

 

Quick Tip!

If you are accessing your SQL Server on the same machine you are working on you can substitute (local) in for the machine name.

 

 

 

Attaching & Detaching Databases

 

There may come a time when you need to move your database to a new instance of SQL Server or to a new disk drive because of space issues or upgrades.  The easiest way to accomplish this is by using the SQL Server Management Studio to help you attach and detach databases in a familiar GUI interface.

 

In this example, we’ll take you through the process of logging into SQL Server Management Studio Express, creating a database, then detaching it and re-attaching it.

 

First, let’s launch our Management Studio.  Go to your Start Menu -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio Express.

You will notice that the name of the server as well as your instance is pre-populated for you in the connection window.  We will want to use SQL Server Authentication so choose that from the drop-down box under Authentication.

 

Next, enter the sa username and password so that we can access SQL Server with full administrative rights.

 

When you have established a successful connection, you will see a tree-menu of various SQL Server categories appear in the left hand-pane.

 

If you receive an error upon trying to login, verify that you have entered the correct sa password and that your SQL Server service is running.

 

 

We are now ready to create a test database we can work with.  Right click on the Databases folder and select “New Database” from the drop-down list.

 

 

 

You will be presented with a screen with many choices and options.  Don’t let this overwhelm you.  We only need to worry about one field on that screen, “Database Name”.  Give your database a name such as “TEST2” or something easy to remember and click the OK button.

 

 

 

 

 

When you click “OK” you will have created your first database within SQL Server!  You will that your menu tree on the left hand side of the screen has changed to now show your new database, TEST2.

 

 

Now that we have created the database, let’s go as ahead and test out attaching and detaching the databases to see how that works.

 

 

 

 

 

To begin, locate your database in the left hand tree view.  Remember, our testing database was called TEST2.   Right click on this database to bring up the action menu for the database.  On that menu you will see a Tasks sub-menu.  Inside the Tasks sub-menu you will find the option to detach the database.

 

Select Detach to open up the database detachment tool.  The first screen you see is asking you how you would like to handle any active connections, statistics and catalogs associated with that database.  We don’t need to worry about changing any of the settings on this page and can just accept the defaults and click OK.

 

 

 

With that one click you have now detached your database.  Don’t worry; the data is still there it is just no longer associated with SQL Server.  Now let’s take a look at how to re-attach the database, it’s a simple process much like detaching it.

 

This time we want to go to our Databases folder on our left menu tree.  From here we can right click on the folder and the second item in our pop-up menu is to attach a database.  Go ahead and select it to get started.

The first window you will see is asking you which databases you want to attach.  Right now the window is blank, but simply hit the Add button to locate your database.   You could choose any database located on any available drive.  For our purposes those we can just select out database in the default DATA folder which comes up automatically.  Find the database named TEST2 and click to add it.

 

Once you have done that, go ahead and click OK.   You are now taken back to the window you just came from but now you will notice your TEST2 database is listed as a database to add.  You don’t need to change anything on this window so just go ahead and click OK to process the add request.

 

That’s all there is to it!  Your database is now back in your SQL Server and ready for use!