Home

Starting Microsoft SQL Server

 

The Service and the Server

 

The MSSQLSERVER Service

After installing Microsoft SQL Server, you can start using it.

Because Microsoft SQL Server works as a service to the operating system, in order to use it, you must make sure its service has started. Sometimes you will have to start the service and sometimes you will have to stop it. To check it, you can open the Control Panel and the Administrative Tools. In the Administrative Tools window, open the Services.

In the Services window, check the status of the SQL Server (MSSQLSERVER), the SQL Server Agent (MSSQLSERVER), and the SQL Server Browser:

Services

If the MSSQLSERVER service is stopped, you should start it. To do this, you can right-click it and click Start. If it fails to start, check the account with which you logged in:

  • If you are using Microsoft Windows XP-7 and you logged in as Administrator but did not provide a password, you should open Control Panel, access User Accounts, open the Administrator account, and create a password for it
  • If you are using a server (Microsoft Windows Server 2003 or Microsoft Windows Server 2008), make sure you logged in with an account that can start a service

Once the service has started, it should be labeled Started:

Services

Opening Microsoft SQL Server

To launch Microsoft SQL Server, you can click Start -> (All) Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio

Launching Microsoft SQL Server 2008

When it starts, it would present a dialog box that expects you to log in.

Practical LearningPractical Learning: Launching Microsoft SQL Server

  1. Start the computer
  2. Log in with the account you used to install Microsoft SQL Server
  3. To launch Microsoft SQL Server, click Start -> (All) Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio. A splash screen will appear:
     
    Splash Screen
  4. On the Connect to Server dialog box, click Cancel

The Microsoft SQL Server Management Studio

 

Introduction

There are many tools you will use in Microsoft SQL server. One of them is called Microsoft SQL Server Management Studio. To access it, you can click Start -> (All) Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio. A dialog box would come up but you can click Cancel on it:

Microsoft SQL Server Management Studio

The top section of the SQL Server Management Studio displays the classic title bar of a regular window, with an icon on the left, followed by the title of the application, and the system buttons on the right side.

The Main Menu

Under the title bar, the menu bar displays categories of menus that you will use to perform the various necessary operations.

The Standard Toolbar

The Standard toolbar displays under the main menu:

The Standard toolbar displays under the main menu

The Standard toolbar is just one of the available ones. Eventually, when you perform an action that would benefit from another toolbar, the Microsoft SQL Server Management Studio would display that toolbar. Still, if you want to show any toolbar, you can right-click any menu item on the main menu or any button on a toolbar. A menu would come up:

Context-Sensitve Menu

The Object Explorer

The left side of the interface displays, by default, the Object Explorer window, with its title bar labeled Object Explorer. If you don't see it, on the main menu, you can click View -> Object Explorer.

The Object Explorer is a dockable window, meaning you can move it from the left side to another side on the interface. To do this, you can click and drag its title bar to a location of your choice. When you start dragging, small boxes that represent the possible placeholders would come up:

Dragging

You can drag and drop to one of those placeholders.

The Object Explorer is also floatable, which means you can place it somewhere in the middle of the interface:

To place the window back to its previous position, you can double-click its title bar. The window can also be tabbed. This means that the window can be positioned either vertically or horizontally.

At any time, if you do not want the Object Explorer, you can close or hide it. To close the Object Explorer, click its close button.

On the right side of the Object Explorer title, there are three buttons. If you click the first button that points down, a menu would appear:

Object Explorer

The menu allows you to specify whether you want the window to be floated, docked, or tabbed.

The right side of the window is made of an empty window. This area will be used to display either the contents of what is selected in the Object Explorer, or to show a result of some operation. As you will see later on, many other windows will occupy the right section but they will share the same area. To make each known it will be represented with a tab and the tab shows the name (or caption) of a window.

Close Microsoft SQL Server

After using Microsoft SQL Server Management Studio, you can close it. To do this:

  • Click the icon on the left side of Microsoft SQL Server Management Studio and click Close
  • On the right side of the title bar, click the system Close button Close
  • On the main menu, click File -> Exit
  • Press Alt, F, X
 
 
 

Introduction to Connecting to Microsoft SQL Server

  

Overview

In order to do anything significant in Microsoft SQL Server, you will have to establish a connection to it. This depends on whether Microsoft SQL Server is installed on the computer you are using or you are connecting to an external server using a client/workstation. In fact, you can connect to more than one computers that have Microsoft SQL Server installed.

If you start Microsoft SQL Server Management Studio from the Start button, the Connect To Server dialog box would come up:

Connect To Server

If you had started from the Start button but clicked Cancel, to connect to a server:

  • On the main menu, click File -> Connect Object Explorer...
  • On the Standard toolbar, click the New Query button New Query
  • In the Object Explorer, click the arrow of the Connect button and click one of the options, such as Database Engine...

Any of these actions would display the Connect to Server dialog box.

Using a Stand-Alone Computer

In the previous lesson, we saw two types of installations: on a lone computer or with a client tools. We also saw that the simplest way to use Microsoft SQL Server was with one computer. In this case, after starting the computer and logging in, especially if you log in with the same account you used to install Microsoft SQL Server, on the Connect to Server dialog box, the Database Engine option would be selected in the Server Type combo box. The name of the computer should be selected in the Server Name combo box. In this case also, the easiest way to connect is to select Windows Authentication in the Authentication combo box. The User Name combo box should have the name of the computer, followed by a back slash, and followed by the user name of the account that is currently logged in. You don't have to enter a password (this is because you are using Windows Authentication and because you are logged in already):

Connect to Server

Connection in a Client/Server Environment

  
Connection in a Client/Server Environment  

Introduction

If you are working in a network environment, you may use a client that connects to a computer on which Microsoft SQL Server is installed. If you had installed either Microsoft SQL Server or the client tools on the current computer, you can use that workstation to connect to Microsoft SQL Server. In this case, you use a different type of authentication.

Before being able to connect, you must prepare the server. Once the server can receive external connections, you can either connect to it when you launch SQL Server Management Studio or change the connection after starting Microsoft SQL Server.

Preparing the Server for Remote Connectivity

In most cases, the default installation of Microsoft SQL Server doesn't allow clients to connect to it. Sometimes, if you try establishing a connection to the server (in this and the next lessons, we will see how that connection is established), you may receive an error as "A network-related or instance-specific error occurred while establishing a connection to SQL Server.":

Connect to Server

There are a few actions you must first perform, from the operating system's firewall to Microsoft SQL Server's own configuration.

One of the actions you should take care of for client connectivity is to dig/create a hole in the firewall. To do this, click Start -> Control Panel. Double-click Firewall. Using the Exceptions tab, create a firewall.

Besides taking care of the firewall, you should configure TCP/IP connectivity and create an alias.

Client Remote Connection

After configuring the server for client connectivity, you can establish connections to it. When you start Microsoft SQL Server, you are presented with the Connect to Server dialog box. If you are working with only one computer, you can use Windows Authentication, in which case the computer name, the user name, and the password would be selected for you. If you are using one computer to connect to Microsoft SQL Server on another computer, you have to make a few selections.

In the Connect to Server dialog box, click the arrow of the Server Name combo box and select <Browse for more...>:

Connect to Server

If you are using a computer on which the server is not installed, the Local Servers property page would be empty:

Browse for Server

If the server is installed in the local computer, the tab would show more options:

Browse for Server

Click Network Servers. For a few seconds, the dialog box would start looking for the computers that have Microsoft SQL Server in your network:

Browse for Server

The names of servers would appear in a tree list:

Browse for Server

Click the name of the computer you want to connect to and click OK. Back in the Connect to Server dialog box:

Browse for Server

You will then decide whether to use Windows Authentication or not, which we will cover in the next sections.

Other Microsoft SQL Server Connection Tools

 

The SQL Command Prompt

Besides the SQL Server Management Studio, you can also work on Microsoft SQL Server from the DOS Command Prompt. To access the command Prompt, click the Start button:

  • If you are using Microsoft Windows XP or 7, click the box that appears, type cmd and press Enter
  • If you are using Microsoft Windows Server, click Command Prompt

Once at the Command Prompt, type SQLCMD (case-insensitive) and press Enter.

The Command Prompt

To see the names of the computers that have Microsoft SQL Server in your network, type SQLCMD -L and press Enter:

The Command Prompt

Unlike the SQL Server Management, the SQLCMD utility allows you to connect to only one SQL Server instance at a time.

After using Microsoft SQL Server from the command prompt, to close it, type Quit (case-insensitive) and press Enter. To close the DOS window:

  • Type Exit (case-insensitive) and press Enter
  • Click the system Close button Close

The Windows PowerShell

When Microsoft SQL Server 2008 installs, it also installs an application named Windows PowerShell, which is a command-based application from Microsoft. Besides the SQL Server Management Studio and the Command Prompt, you can use PowerShell to create and manage databases. To access it:

  • Access the Command Prompt as seen above, type sqlps, and press Enter

    Windows PowerShell

    At the Command Prompt, type SQLCMD and press Enter
  • Click Start -> (All) Programs -> Windows PowerShell 1.0 -> Windows PowerShell
  • If you had already started Microsoft SQL Server, in the Object Explorer, right-click the name of the computer or the Databases node and click Start PowerShell

In both cases, a DOS window would display:

Windows PowerShell

Notice that the title bar displays Windows PowerShell. To access Microsoft SQL Server from PowerShell, type SQLCMD and press Enter:

Windows PowerShell

Notice that, this time, the title bar displays SQLCMD, which indicates that the application is ready to receive commands that relate to Microsoft SQL Server.

As mentioned for the SQL Server Management Studio, Windows PowerShell allows you to connect to more than one SQL Server instance at the same time. As a result, you can create a SQL script that is meant to target more than one database in different servers, store the script in a file, and then execute at the Windows PowerShell to perform many operations on different databases.

After using the PowerShell, to exit from Microsoft SQL Server, type Quit (case-insensitive) and press Enter:

Windows PowerShell

To close PowerShell and the DOS window, you can:

  • Type Exit (case-insensitive) and press Enter
  • Click the system Close button Close

Practical LearningPractical Learning: Closing Microsoft SQL Server

  • To close Microsoft SQL Server, on the main menu, click File Exit.
    If you are asked whether you want to save something, click No
 
 
   
 

Previous Copyright © 2008-2011 FunctionX.com Next