Starting with SQL Server

We will be using Microsoft SQL Server 2008 running on IISQLSRV.cs.washington.edu. Accounts on it have been set up for each of you, and you have been given your user names and passwords. To get started,you will need to run SQL Server Management Studio. There are three options: (1) run it from any Windows machine in the undergrad labs. (2) remote desktop to a Windows machine following these instructions http://vdi.cs.washington.edu/vdi/ and run it from there. (3) Install a copy of SQL Server Management Studio on your own machine from MSDNAA then use SSH tunneling for SQL Server.

SQL Server Management Studio is a client application. This client application will establish a connection to the server where the IMDB database (and other databases) are located. The client application will send SQL statements to the server. The server will execute these SQL statements and will send results back to the client application.

Click here for more information about a client-server setup and an illustrative figure.

Before you can run queries on IISQLSRV, you thus need to connect to the server in Management Studio and change your password:

  1. Go to Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio.
  2. A box labeled "Connect to Server" will show up. Fill in the options to connect:
    1. In the box labeled SQL Server, enter iisqlsrv.cs.washington.edu .
    2. Select the Use SQL Server Authentication option.
    3. Enter your login (your UW account) and password (has been given in class).
    4. Click OK.
  3. You will be asked to change your password. Enter the new password; you only need to change it on your first connection.
  4. Now you are connected; on the left side of the screen there should be an expanding tree.

In order to use Management Studio to connect to the server and run SQL queries, you need to go through the following steps:

  1. Connect to the server using Management Studio.
  2. Start a new query by clicking New Query in the upper left.
  3. Type in USE imdb; , and click ! Exectue, or just press F5 to execute the statement. This will set your current database to be the imdb database, used in the first project.
  4. You can now enter and execute any SQL statements. Pressing F5 runs all the SQL code in the text window, displaying the results in the results view below. If you highlight a piece of SQL code and press F5, only that code is executed.

That should be enough to get you started with Management Studio and SQL Server, but both tools have many more capabilities. The variant of SQL supported by SQL Server is called Transact-SQL (T-SQL); in addition to executing all T-SQL statements, Management Studio can also be used to view query execution plans, statistics, and more. To learn more about these features, explore the Query, Tools, and View menus of Management Studio, or read the Management Studio manual.

Note that in addition to read-only privileges on the imdb database, you also have your own database on IISQLSRV. This database's name is the same as your SQL Server username, so it can be accessed using the SQL statement USE your_iisqlsrv_username; You have full privileges to this database; feel free to use it for any project or homework assignment.

WORKING FROM HOME : It is not possible to directly connect to SQL Server on IISQLSRV from outside the CSE network. This restriction is there for security reasons. If you plan to use Management Studio or connect to SQL Server from home, consider working on a CSE terminal server, see instructions here http://vdi.cs.washington.edu/vdi/ , or run Management Studio on your own Windows machine and set up SSH tunneling for SQL Server.