Page 15 - DCAP508_DATABASE_ADMINISTRATION

Unit 1: SQL Server RDBMS

1.5 SQL Server Databases Notes

A database in SQL Server is made up of a collection of tables. These tables contain data and other
objects, such as views, indexes, stored procedures, user-defined functions, and triggers that are
defined to support activities performed with the data. The data stored in a database is typically
related to a particular subject or process, such as inventory information for a manufacturing
warehouse. You will learn about SQL server databases in detail in Unit 5.

1.6 Database Storage

SQL Server stores data and the log in disk files. In a basic installation, and as a default, data and
log files are created in the default location specified in the server configuration. However, to
maximize performance and manageability, you can apply a few basic principles:

Spread data over as many disks, channels, and controllers as possible.
In general, the more disks (spindles) you have (regardless of their individual size) and the
faster your access to them (controllers and channels), the faster the storage engine can read
and write data. The larger your system usage becomes, the more important it is to separate
the data files from log files by storing them on different sets of physical drives. Also,
because the use of tempdb has changed, you should now store tempdb on a large set of
disks, for example, with the data files or on a set of disks.

Use filegroups to make your enterprise database more manageable.
Every database begins with one default filegroup. Because SQL Server 2000 can work
effectively without additional filegroups, many systems will not need to add user-defined
filegroups. However, as a system grows, the use of additional filegroups can provide
more manageability, when implemented and maintained by a qualified DBA.

In SQL Server 2000, if you set a particular filegroup within a database to read-only, the data on
that filegroup cannot be altered, but catalog information such as permissions can still be managed.

Notes In SQL Server 2000, the number of asynchronous I/Os is now managed dynamically
inside the database engine, and is not influenced by the number of files or filegroups used,
as was the case in SQL Server 7.0.
When implementing or optimizing a database design, the Database Administrator (Database System
Engineer) needs to consider the configuration of the database storage components, particularly the
layout of physical and logical disks and the arrangement of the database files across disks.

1.7 Security: Windows and SQL Server Authentication

Microsoft SQL Server can operate in one of two security (authentication) modes:
1. Windows Authentication Mode (Windows Authentication): Windows Authentication mode
allows a user to connect through a Microsoft Windows NT® 4.0 or Windows® 2000 user
account.
2. Mixed Mode (Windows Authentication and SQL Server Authentication): Mixed Mode
allows users to connect to an instance of SQL Server using either Windows Authentication
or SQL Server Authentication. Users who connect through a Windows NT 4.0 or Windows
2000 user account can make use of trusted connections in either Windows Authentication
Mode or Mixed Mode.

LOVELY PROFESSIONAL UNIVERSITY 9