Quick Start Guide to SQL Server 7 -- Part 1, Page 3
Databases are a holding place for information.
Within SQL Server, a database may hold numerous tables, views or stored procedures, among other items.
We'll cover all of these later, just remember that SQL Server has features that Access doesn't have (such as stored procedures), and at the same time, Access sports certain qualities not found in SQL Server (such as Forms).
Another core difference is that Access is essentially just an .MDB file, whereas SQL Server is 'live' and running on the server.
In this section, we'll learn how to create an empty database the holding point for tables and so on plus port any existing Access databases across to SQL Server via something called the Upsizing Wizard.
Launch your Server Taskpad:
- Start Enterprise Manager, the front-end for SQL Server
- Programs/Microsoft SQL Server 7.0/Enterprise Manager
- Expand the SQL Server Group item to view all connected systems running SQL Server
- Dip into the computer you want by double-clicking on its icon
To create a Database:
- Launch your Server Taskpad (as before)
- Click the 'Set Up your Database Solution' icon
- Click the 'Create a Database' icon to run the Wizard
- On the first introductory screen, click Next
- On the next screen, give your database a name and location, then click Next
- It's best to give your database a name without spaces, to avoid confusion and errors later
- The database file location will be the place your actual tables, data within them, and so on, are stored
- Every action in your database is recorded in a transaction log. So, if you have a hardware failure or major database corruption, you can restore data from that transaction log. For greatest peace of mind, change the transaction location to an external drive one different from the location of the main database files
- This screen simply asks you to set the initial starting size of your database. Change if required, then click Next
- With Access, database sizes grow as more information is put into them. With SQL Server, you set a certain amount of disk space to one side, ready to hold future data
- When you need to store more information, previously you had to manually 'expand' the database. However SQL Server 7 can now automatically do that expanding for you, as we'll see on the next screen
- On this screen, select your database growth options. Click Next when finished.
- The default options dictate that when the current database is 'full', it allocates another block of hard disk space for the database to use.
- It's recommended you stick with the defaults, unless you have specific requirements
- The next two screens ask you for the initial size and growth rates of your transaction logs. Click Next when finished
- Once again, it's recommended you accept the defaults unless you have specific requirements
- On the final Wizard screen, review the description of your new database, move back and forth to implement any changes, then click Finish when complete
- After the database is complete, you may be prompted to create a maintenance plan for the database
- The maintenance wizard allows you to schedule database backups and so on. You may follow this through if you wish
- We will not be covering maintenance in this quick start guide
To view your database in Enterprise Manager:
- Launch your Server Taskpad (as above)
- Double-click on your server name, to the left of your screen
- Double-click on the 'Databases' folder
- You should see a list of databases on the screen, including your own. Many of these are samples that ship with SQL Server.
- Double-click on your database name
- If you're new to this, don't worry about some of the mysterious items listed under your database such as Rules and Stored Procedures. We'll cover the most important of these later
It's also worth noting that Microsoft Access 2000 ships with an Upsizing Wizard that can automatically port your .MDB database over to SQL Server.
To move your Access database to SQL Server:
- Launch Microsoft Access 2000
- Click 'Tools', 'Database Utilities', 'Upsizing Wizard'
- If you're using Access 97, you probably won't have the Upsizing Wizard we're looking for. To download it, visit www.microsoft.com/accessdev/prodinfo/AUT97dat.htm
- For all other versions, it's advisable to upgrade your database to Access 97/2000 and then use the Upsizing Wizard from there
- Unlike Access, SQL Server doesn't use 'forms'. Only your tables, queries and relationships will be ported across
- Follow the on-screen prompts