Transcript Access Data

Week 4 Agenda
Chapter 4:
• Access data
• Create Databases
• Set Database Options
• ODBC
• The Web Assistant
Access Data
• A DBA is most concerned with administering
SQL Server (monitor, optimize, …)
• Users and developers are more concerned
about accessing data from their databases
• Today we'll discuss the process of creating
databases and methods used to access data
from a database
Create Database
Topics:
• Create a database
• Restore the database from backup
• Attach the database from file
Database Physical Storage
SQL Server stores data physically using 2
files:
1. The database file(.mdf extension)
2. The transaction log file(.ldf)
The relationship between these two files is
described by the checkpoint process
Checkpoint
• When
data for a database is modified the data
is first sent to memory
• The modified data is then written sequentially
to the transaction log file for that database
• The data, when committed, is then written to
the database file. When the data is verified by
the logging process to be in the database file a
mark is made on the line of data in the log file
indicating that the data has been recorded. The
marking process is known as a checkpoint.
Database Recovery Settings
• Database recovery settings define how
transaction log files are to be managed.
• SQL Server supports three different database
recovery settings:
1. Simple
2. Bulk-log
3. Full
Simple DB Recovery Setting
• Data is removed from the log once it has been
successfully written to the database file
• Appropriate recovery setting for non-critical
databases
• Keeps the transaction log files small
•However if database file becomes corrupted
any changes made to the database since the
last backup was made will be lost!
Full DB Recovery Setting
• Safest option but has most overhead because
of 3 step process for every transaction
• Committed data is marked but not erased
after writing to database file and remains on
log file.
• Log files may grow until the disk runs out of
space
• Successful backup of whole database or log
file truncates (removes committed
transactions) transaction log files
• Full option is exactly like bulk-log except it
doesn’t allow bypassing of transaction log
Bulk-log DB Recovery Setting
• Bulk-log exactly like full recovery except it
allows bypassing of transaction log by using
special commands.
• Logging is normally bypassed for bulk inserts
of large amounts of stored data stored (eg
batch processing) or actions like SELECT
INTO based on another large table.
• Bypassing logging is a recovery compromise
but it greatly improves performance. However
there is no transaction log so a backup should
be made after the actions performed with
bypass of logging are complete!
Database Growth Settings
SQL Server stores data in physical database
and log files. The growth of these files can be
managed in two different ways:
1. Specify a size for the files
2. Allow them to grow automatically
Database Growth Settings
Specify a size for the files
• Limit the size the files can attain
• Physical files and page files should not be
on same partition.
• If files attain the maximum size you can
add another file
Automatic Growth DB Setting
1. Specify growth by percentage or by MB
2. Percentage growth – increase grows as
database size grows; e.g. for 10% auto
growth when database is 10 MB , growth is 1
MB, when database is 100 MB , growth is 10
MB, …
3. Must monitor growth regularly otherwise
database can take up entire hard drive space
Tools to Access Data
• SQL Server Tools: Enterprise Manager and
Query Analyzer (not end user tools though!)
• ODBC (Open Database Connectivity): older
method
• OLE-DB (sometimes refers to as DSN-Less)
ODBC
1. ODBC is software that is configured to
connect to a specific server and a database
2. Has two parts:
- A program designed to use ODBC
- A driver located in each user system
3. Each configuration within the ODBC driver is
called Data Source Name(DSN)
4. MDAC Setup (a version of ODBC) comes with
SQL Server 2000
DSN
DSN is a collection of settings that access the
database from a client. There are three types of
DSN:
1. User DSN: allows access only to the
current user
2. System DSN: allows access to any one
logged onto the system
3. File DSN: is a file that contains the
connection information. It requires the
connection to make a trip to the file system
each time it’s called.
OLE-DB
• Uses APIs
• DSN-less method
• Used on server applications like webhosted databases
• The developer of the program may include
a configuration utility
Programs and DB Access
Applications that connect to SQL Server include:
•Microsoft Office: Almost all of Microsoft Office can
access data from SQL Server 2000 database using
ODBC or OLE-DB methods
•Visual Basic: You can access SQL Server 2000 using
many programming languages using ODBC or OLE-DB
•Web Pages: More and more SQL Server data is
accessed through web pages
•Command Line: Command line tools are good when
you access your server remotely and the connection is
slow. Examples of command line tools are Query
Analyzer and OSQL.
Database Options
Database Options in Enterprise Manager
controls database settings. To see settings:
Right click on database name, click Properties
Database Options
General : Displays general information about
your database
Data Files:
1. Add or delete data files(.mdf)
2. Change file growth settings:
- by MB (fixed chunk added)
- by percentage to be increased
3. Set maximum file size
Database Options (ctd)
Transaction Log:
1. Add or delete transaction log files(.ldf)
2. Change file growth settings:
- in MB
- by percentage
3. Set maximum file size
Database Options (ctd)
File Groups :
Logically arrange files using options:
1. Restrict access
2. Read only
3. Recovery model
4. Compatibility
The Web Assistant
SQL Server's Web Assistant tool creates
read-only pages for web browsers
Exercises
1. Set up an ODBC Connection
2. Create Database by using Enterprise
Manager, and by using Query Analyzer
3. Set Database Options
4. Use SQL Tools Query Analyzer to access data
5. Create a script
6. Create a web page using The Web Assistant