Introduction to Databases and Microsoft Access 6/8

Planning SQL Server Databases:Demarcating the size of your database
Planning the SQL Server DatabaseTransaction Log

Create an SQL Server Database with Enterprise Manager

Create an SQL Server database using the Create Database wizard

Deleting (dropping) a database

* Planning SQL Server Databases:

The complexity of a database application can vary greatly. Having a robust design means allowing the database to grow as your users and data grows. You need to consider the number of users who will be logged on to the system at any one time and the number of transactions each user is performing.

* Demarcating the size of your database

·        With SQL Server 7.0, you don’t have to estimate the size of your database if you don’t want to.

·        You can tell the SQL Server to allow the database to grow automatically, and you can specify the maximum amount of space you want your database to occupy.

·        Note that if you don’t specify the maximum size, SQL Server decides that it is ok to use the whole hard drive if necessary.

* Planning the SQL Server Database Transaction Log

·        Transaction logs simply contain a series of log records that contain all the necessary information needed for the recovery of the database in the event of a system crash or a hardware failure.

·        A transaction log records all the data modifications done to your database; this includes INSERTs, UPDATEs and DELETE statements, as well as stored procedures. They are recorded in real time.

·        By default, when creating a log for SQL Server 7.0, the size of the transaction log is 25%. This is used primarily as a rough estimate, and you can change the size according to your needs

* Create an SQL Server Database with Enterprise Manager

1.    Open Enterprise Manager <Choose Start, Programs, SQL Server 7.0, Enterprise Manager>

2.    On the left pane, open Microsoft SQL Servers.

3.    Open SQL Server Group.

4.    Scroll down to databases and right click.

5.    Select New Database.

6.    Fill in the name for your database.

7.    Select the options you want. In case you’re unsure, go along with the defaults.


8.    Click OK.

* Create an SQL Server database using the Create Database wizard

1.    From inside the Enterprise Manager choose Tools, Wizards. All the objects that have wizards are listed in alphabetical order.

2.    After you select Databases, the tree should expand to show you a list of the available wizards for Databases.

3.    Click the Create Database Wizard and then click OK.

4.    After the splash screen, you are prompted for the database name and where it will reside on the hard drive.

5.    Edit the data filename and initial size. Here are some of the options you can alter:
Autogrow the file (y/n)? (default is yes)
How fast should the file grow? (default is by 10%)
How big should it grow? (default is unrestricted)


6.    Edit the log filename and initial size. You are presented with similar options and defaults:
Autogrow the file (y/n)? (default is yes)
How fast should the file grow? (default is by 10%)
How big should it grow? (default is unrestricted)

7.    The next screen indicates that everything is ready and you can click Back to review what you have done. Click on Cancel or Finish to complete the operation accordingly.

8.    After the operation has finished, you will be prompted for the Set Up A Maintenance Plan. Select No.

* Deleting (dropping) a database

·        The Enterprise Manager will only allow you to drop one database at a time.

·        After you drop a database, any Login ID that used the dropped database as its default will now use the master database.

·        You cannot drop databases that are
a) Currently opened for reading or writing by another user
b) Currently being restored
c) Publishing any of its tables (part of SQL replication)

·        In addition, you cannot drop any of the following system databases:
a) Master
b) Model
c) Tempdb

To drop/delete a database:

1.    Open Enterprise Manager

2.    Open the Database folder

3.    Right click the database you want to drop

4.    Select Delete

5.    You are prompted with a confirmation box. Select Yes.

Exercises:

(To be added later)