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:

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
• 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.

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):

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.

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.":

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.

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...>:

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

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

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

The names of servers would appear in a tree list:

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

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

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.

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

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

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

  

  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:

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

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:

To close PowerShell and the DOS window, you can:

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

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