Configuring and Creating a Database in MS SQL 2008

This tutorial will walk you through what you need to do to start using the installation of Microsoft SQL 2008 Express Edition that we did in the last tutorial. We will need to configure the services to allow incoming connections and we will also go through creating the database as well as some tables to insert information. Let’s get started.

First, it is important to note, that for security reasons, MS SQL comes with all connections via TCP/IP and Named Pipes disabled. Named pipes is a way for the connection to distinguish which instance of SQL you are trying to connect to (sort of like a port would act for TCP/IP). Named Pipes is not a network protocol though. It is a type of transmission that occurs over TCP/IP. We will be working with TCP/IP connections in the tutorials on this site, so we will not be enabling named pipes connections, however, when the point comes that you would do that, I will make note of it for you.

The first thing we need to configure are the connection properties that tell our server how people will be connecting to it. To do this we need to use the SQL Server Configuration Manager.

Goto Start ->All Programs -> Microsoft SQL Server 2008->Configuration Tools->SQL Server Configuration Manager.

This window will come up. This is the tool that allows you to configure how application will “talk” with your server. You will see the SQL Server Network Configuration category with a plus next to it. Expand this field.

In here you will see a category called Protocols for “your instance name here”. Whatever you called the instance you created during the installation will show there. By default it is called SQLExpress. Select this category and the pane to the right will populate with the connection setting for that instance. (Note – if you have more than once instance installed, each instance will have it’s own category under SQL Server Network Configuration)

All you need to do to enable the connections to the server is right-click on TCP/IP and click on enable.

This window will pop up to alert you that your changes will not take effect until we restart the service for this particular instance that we are modifying. Next, select the option in the left pane that says SQL Server Service.

This pane show you the services for all instances that are currently on your machine. Right now, we only have the three since we only have on instance installed. The SQL Server Browser is used across all instances but each instance will have an SQL Server and SQL Server Agent. All we need to do to make our TCP/IP changes take effect is to restart the SQL Server.

To restart the service simply right-click on it and select restart; a screen will appear that show you the progress of stopping the service and then starting it back up. After it does that, you can close out of that window as we are done with it. Next, we need to create the database that we will be using. To do this we are going to use one of the included tools that came with the software: SQL Server Management Studio.

Start->All Programs->Microsoft SQL Server 2008->SQL Server Management Studio

This screen will come up asking for authentication. You will need to be logged in as the user you added during installation in order for windows authentication to go through. The first thing we need to do in here, is create out database that we will be using.

This is the main window of the management studio.

To create our database, right-click on the Databases folder and click on New Database.

This screen let’s you configure and name your database. All we need to do is select a name for our database and type it in. I called mine ‘TeamTutorials’. Click on OK to create the database.

Now in our Management Studio if we expand the Databases folder, we will see our database that we created in there. Next we are going to change a setting that will be helpful down the road.

At the top of the window, click on Tools and select Options from the menu.

This window will appear, click on the designers category (you do not need to expand the field). Once you are there un-check the box next to “Prevent saving changes that require table re-creation.” This will allow you to make changes to your tables after you have built them and put data in them. This is more of a protective measure than anything. Just to be safe, I wouldn’t recommend turning this off if you plan on keep very private or important information in the database that you can’t afford to lose. Next we need to create the tables we will be using. Our tool here will allow us to create them using a graphical interface so you don’t have to write any code, this is acceptable but I might recommend at least keeping the scripts it generates for reference. This way if you need to do it again sometime, you can just run the script to create the database and make changes as necessary. We will cover creating the tables and setting them up in a way that suites our needs in the next tutorial. I hope this tutorial was easy to follow and helps you. Thanks for reading.