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 more powerful database-driven web sites.

Login as System Administrator

From now until the end of the semester, you will be using the same machine for your system work and will have administrator access rights to (only) that machine. Take this opportunity to login as administrator and change the administrator password (by typing ctrl-alt-del and clicking on "Change Password"). Be sure to remember this password; if you forget it, we'll have to re-stage your machine and you'll have to reconfigure everything.

If you have trouble getting SQL Server running again when you log back in, you may have to change the SQL Server administrator password to this new password. Do this by starting the "Services" tool in the system administrative tools folder, double-clicking on "MSSQLSERVER", and then changing the administrator password accordingly.

You should also label your machine so you can remember which one is yours.

Importing the Movies Database into SQL Server

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 (movies.mdb). 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 (from the Windows 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 you 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 on, 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.
• Can you find anything about the movies database that was changed when you ported it from Access to SQL Server?

The choice of which of these access methods to use is largely up to you. I tend to use the Access front-end approach when I can (e.g., data entry, simple queries), but you'll find that there are some things that you must do within SQL Server itself (e.g., backups and other administrative tasks).

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. Tell the backup facility specify to put the backup files in its "BACKUP" directory, the default.
3. Tell SQL Server to make the backup.

It's a good idea to schedule backups of your important data on a periodic basis. On my grading server, I've configured the Windows backup utility to back up the grading database backup files to my remote Novell network drive. You don't have access to your Novell account in this lab, but you can sftp your backup files to other machines.

Here are some questions to consider:

• The backup utility distinguished between "complete" and "differential" backups. What's the difference between these two backup types? Which would you want for your important data?
• 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 now by:

• Create a new login on your local SQL server that grants guest access to your movies database.
• Register a new SQL Server on your machine that refers to one of your classmate's new guest account. Do this 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.
• You should be able to access that database in all the ways your classmate has specified. The key difference is that now you, and everyone else in the lab who knows the server name and user information, can access this same database remotely. This wouldn't be possible with Access.

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.