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.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join the TeamTutorials mailing list and get the latest tips, tricks, and special discounts for members only.

Thanks! Please click the link in the email we just sent you to confirm.

  • You have a really great site here with easy to follow and very useful tips. I think I will be coming back to visit you some more.

  • Good basic tutorial for all those wanting to start with SQL 2008 Express.
    Keep up the good work.

  • Paul

    This site has been very helpful.

    Thanks

  • John

    Great site! Huge Help!

    Thanks!

  • Maaz

    Really helpful. Keep up the good work.

  • making changes to tables after you placing the data it’s a little tricky

  • that was awesome……..ive been a php fanatic lately and decided to be a windows based fanatic and thot of vb.net.

    this tutorial just made my day………….i began installing yestaday and 2day im really familia w everything u said…..this is rilly awesome….keep up the good wek

  • sahih

    good work keep it up buddy. long time no see

  • Rashsul

    This helped a lot. Thanks.

  • Dmtmb

    Got this message when tried to create database. Is there a fix for this? CREATE DATABASE permission denied in database ‘master’. (.Net SqlClient Data Provider)

  • this has certanely helped me a lot .. thank you for this

  • Chris

    Awesome!

  • Ayan

    hi,

    this has helped me a lot to setup sql server 2008. if kindly inform me how to setup username and password for sql server authentication then that will will be great for me.

  • Job well, very excellent tutorial.

  • Thats was great help.

    thank you very much.
    🙂

  • azureskies

    This was very helpful. Thank you. Was the ‘next tutorial’ ever made?

  • bony

    Hi,

    I install sql server 2008, and i facing above error. your solution can’t help me coz in
    configuration manager->Network Configuration can’t display any submenu :(.

    so i can’t go next step. plz help you have any idea abut that.

    Thank you in advance

  • Shah

    GREAT SITE!
    Very helpful.

  • Nalina

    This is greate..

  • Ron

    I just wanted to say thanks. Very easy to follow and very helpful.

  • Jacques

    Excellent tutorial

  • Kristina

    Very good site.

  • Nice tut0rial.. that was great!

  • Jojo

    Very informative for novices….
    Keep up the good work..

  • adil

    thank u i look for more option

  • Manoj Shriyan

    Thanks Tons… have been struggling with this for the past 6 hrs… the scenario was spot-on… just following the steps worked like a charm.

    Thanks a million 🙂
    10 STARS

  • Roman

    i can not find the tutorial for creating tables. Can you please point to it?

  • Renzo

    great site..thanks it helps me…im a newbee for sql server..thanks

  • Le chi Hieu

    wonderful ! thank you very much, i got errors on connecting to server and creating database for almost 2 days, i felt really relieved when i found your article.

  • Mutua_brian

    yeah,the tutorial really helps.More of this will be highly appreciated.

  • jitendra

    thanks a lot.its really very nice…and helpful……….

  • dhiraj

    thank you u=very much

  • Stella

    Very helpful website thanks.