In this lab, we'll make the jump from personal database management systems (e.g., Microsoft Access) to client/server database management systems (e.g., Microsoft SQL Server). This is an important step toward supporting database-driven web sites.

Getting the Database

In this lab exercise, we'll work with the movies database, so start by grabbing a copy of the Access version of the movies database (H:/local/cpsc/341/movies.mdb or movies.mdb).

Importing the Database into SQL Server

There are several ways to port a database from MS Access to SQL Server. Here's one way:

1. Start the SQL Server Enterprise Manager (in the Windows 2000 Program menu).
2. Select the local SQL Server instance in the Console Tree view window (on the left).
3. Under the database folder, create a new database called "movies".
4. Click mouse-right on the new database and select AllTasks-ImportData.
5. This will start the import wizard - Giving it the information it asks for should allow it to import all the data.

If you have any trouble with this process, ask questions and we'll get it sorted out.

Working with SQL Server

The movies data is now stored inside of SQL Server, so you'll no longer need to find the ".mdb" file. Instead, you need to connect to your local machine's SQL Server and work with your data that way. We'll look at two ways of doing this.

First, you can work with the new database using the SQL Server Enterprise manager itself. You probably still have this system running. See if you can figure out how to do the following:

• Select the data from each of the 3 main tables (click mouse-right on the table and select "OpenTable-ReturnAllRows").
• Create a database schema diagram (as we did in Access). (Go to the diagrams sections and click mouse right "New Database Diagram". To establish the relational integrity constraints, you'll first need to define the primary keys for the tables).
• To run more sophisticated SQL queries against the database, start up the SQL Server Query Analyzer (by choosing Tools-QueryAnalyzer). You can then type your SQL commands in the main window and then click the green start arrow. Before moving one, try out a couple of queries, including a multiple-table query.
• You'll find the actual database files that SQL Server is using in C:\Program Files\Microsoft SQL Server\MSSQL\Data\movies.*. You should never mess with these files directly, always let the server work with them for you.

A second way to work with a SQL Server database is to use MS Access as a front-end. To do this, start up Access and create a new "project" (from existing data). This will allow you to specify which SQL server to connect to. (local) will designate your local machine's instance of SQL Server. In this configuration, you can use Access's interface building features, leaving the actual data on SQL Server (rather than in the Jet database format used by Access). Try the following:

• Look at the data in the three tables.
• Create a default Access Report for one of the tables using the report wizard - you'll find that it works pretty much like a normal report.
• To see the real database that Access is working with, choose File-Connection. This will show you the SQL server connection that Access is using. You can confirm this by looking at the file size of the movies.adp that Access created for you - note how small it is in comparison to the original movies.mdb file.

Backing Up and Restoring a Database

One important database administration task is that of creating backup copies of the database. This is important in the event that "live" version of the database is compromised in some way (e.g., it is accidentally/maliciously changed/deleted). To try this out, create a backup of the movies database using the SQL Server Enterprise Manager as follows:

1. Click mouse right on the database you want to back up and choose AllTasks-Backup.
2. Let the backup facility specify the default "destination" location in which to save the backup.
3. Tell SQL Server to make the backup.

Can you think of any reason why SQL Server wouldn't let you do your backup "remotely", i.e., dump data from SQL Server directly to your network drive?

Working with a Remote SQL Server

Because SQL Server is designed for client/server access, it is capable of supporting multiple users across a network. To try this out, register a new SQL Server on your machine by going to the current SQL Server Group, clicking mouse-right and selecting "New SQL Server Registration". Follow the wizard, designating the SQL Server instance on one of the machines in the lab. When you've got this server registered, you should be able to do all the things with it that you did with the local SQL Server above. The only difference is that now everyone in the lab is using the same SQL Server Instance and they are all using it remotely.

Checking In

Before leaving, use KV to submit a text version of one of your multiple table queries from above on the remote instance of the SQL Server. If you have questions on any of the queries, be sure to ask them.