-
Storage Units
-
-
Devices - Database devices are operating system
files. They can store multiple databases and parts of databases.
-
Databases - Databases are created on one or more
devices. When more than one device is used, the devices can exist
on separate hard drives. Minimum of 1 MB in size.
-
Allocation Unit - When a database is created, space
is allocated in 1/2 MB increments (256 pages or 32 extents) known
as an allocation unit.
-
Extents - An extent is 16K (8 contiguous 2K pages).
When a table or extent is created, SQL Server allocates an extent.
Each object is allocated its own extent. Extents are never shared.
Because there are 32 extents per allocation unit, there can be up
to 32 objects per allocation unit.
-
Page - The basic unit of storage is a 2K (2048 bytes)
page.
-
SYSTEM Tables
-
When SQL Server is installed, there are four system
databases created:
Master |
Controls the user databases and the operation of SQL Server
as a whole (default size 25 MB). No user is allowed direct
access to it. |
Model |
Provides a template or prototype for new databases. Contains
the system tables which belong in every database. Items that
are to appear in all new databases should be placed here. |
Tempdb |
Provides storage for temporary tables and other temporary
storage needs such as intermediate results of GROUP BY, ORDER
BY, DISTINCT and cursors. |
Msdb |
Supports the SQL Executive Service. This service is a scheduler
that enables such activities as replication, task scheduling,
and alert management. |
Master DB:
sysdatabases |
Databases on SQL Server |
sysdevices |
Available database and disk devices |
syslogins |
User accounts |
sysmessages |
System error messages |
sysservers |
Remote servers |
sysusages |
Disk space allocated to each database |
MSDB:
sysoperators |
Administrative personnel information, including email addresses
and pager numbers |
sysalerts |
User-defined alerts |
systasks |
User-defined tasks |
syshistory |
Tracks whether or not a task was executed |
sysnotifications |
All currently active alerts and their notification operator
information |
-
Database Objects
-
-
Table - Collection of columns and rows
-
View - An alternate way to look at data from one
or more tables
-
Default - Value that SQL Server inserts into a column
if the user does not enter a value
-
Rule - Specifies and controls what data can be entered
into a table
-
Stored Procedure - Precompiled collection of SQL
statements
-
Trigger - Special form of a stored procedure that
is executed automatically when a user modifies data in a table
-
SYSTEM Stored procedures
-
sp_addserver |
Register remote servers |
sp_adduser |
Adds a user to a DB. (params: login_id, username, group) |
sp_addlogin |
Creates a login_id for SQL server |
sp_addextendedproc |
Add an Extended Procedure to the MASTER database |
sp_changegroup |
Insert an existing user in a group |
sp_helpuser |
Verify current users |
sp_configure |
Can configure TempDB into RAM, worker threads, etc. |
sp_spaceused |
Show amount of spaced used for a database |
sp_dbremove |
Remove a damaged database (also DBCC DBREPAIR -- old) |
sp_who |
Provide information about connected users (including domain
info) |
sp_monitor |
Shows info on number of pages read |
sp_dropuser & sp_changedbowner will change a user to
a dbo.
To send your manager the results of a sql server query
as an attached mail message:
- xp_sendmail @recipients = 'manager', @query = 'select * from
Yourname',@attach_results = 'TRUE'
-
xp_logevent - logs user-defined error messages.
-
xp_cmdshell - Can call an MS-DOS batch file or NET
commands.
You can only use extended procedure when it's
typed with small caps --XP_senDMAIL is not valid.
XP.DLL provides a framework for extended stored procedures.
-
SQL Server Commands
-
CREATE DATABASE database_name
[ON {DEFAULT | database_device} [= size]
[, database_device [= size]] ...]
[LOG ON database_device [= size]
[, database_device [= size]] ...]
[FOR LOAD] (prevents users from updating DB. Used
to restore from backup)
ALTER DATABASE database_name
[ON {DEFAULT | database_device} [= size]
[, database_device [= size]] ...]
[FOR LOAD]
DISK INIT
NAME = ' '
PHYSNAME = ' '
VDEVNO = ' '
SIZE = (size in 2k pages)
DISK RESIZE
NAME = logical_device_name
SIZE = final_size (in 2K pages)
DISK REINIT
NAME = dev_name,
PHYSNAME = 'path',
VDEVNO = vitual_device_number,
SIZE = size (in 2K pages)
-
DISK REINIT - used to restore information about
the physical and logical device to the sysdevices table.
-
DISK REFIT - reconciles information to rebuild the
sysusages and sysdatabases tables.
If you expand or shrink a database after the last backup
of master, and then lose master, the DB is unrecoverable.
After using an ALTER DATABASE, you should issue a DUMP
DATABASE master.
Cannot use the DUMP TRANSACTION command when 'trunc
log on chkpt' is set to TRUE.
DUMP DATABASE is normally performed after DUMP
TRANSACTION.
Only DBO or object owner has rights to run UPDATE STATISTICS
on a table.
REFIT might be able to restore schema changes in a DB
after media failure even when not in last backup.
makepipe and readpipe are used to test the inegrity
of a pipe connection.
ALTER DATABASE can be used to change the size of a transaction
log.
You can reduce the size of a database, but not a device.
A dump device can contain dumps from multiple databases.
A database can be dumped to multiple devices.
DUMP TRANSACTION
- WITH TRUNCATE_ONLY - Clears, but does not back up the log.
- NO_LOG - Used when transaction log is completely full.
- NO_TRUNCATE - Used during recovery of a failed device.
Permissions that only SA can use:
- Add/Drop extended stored procedures
- Disk (Init, Mirror, Refit, Reinit, Remirror, Unmirror)
- Kill
- Reconfigure
- Shutdown
- DBCC commands (although some can be granted to DBO)
DBCC Commands:
CHECKALLOC |
Checks current or specified Database that all PAGES are correctly
allocated and used. |
CHECKCATALOG |
Checks for consistency in and between SYSTEM tables. |
CHECKTABLE |
Checks table to see that INDEX and DATA pages are correctly
linked. |
CHECKDB |
Runs same checks as CHECKTABLE on every table in the database. |
NEWALLOC |
Checks DATA and INDEX pages against corresponding extent structures.
Does not stop on error. |
SHOWCONTIG |
Shows how many data pages are used by a table (and contiguous
pages). |
MEMUSAGE |
Provides detailed reports on memory use (i.e. Server's memory
at startup, current memory usage of 20 largest objects in the
data cache & 12 largest objects in the procedure cache). |
SHRINKDB |
Used to shrink a DATABASE. |
SQLPERF (LOGSPACE) |
Reports on the currently used amount of Transaction log space,
expressed as a percentage. Log must be separate from data. |
SQLPERF (LRUSTATS) |
Generates statistics about CACHE used since server was last
started. |
SQLPERF (IOSTATS) |
Read and Write activity. |
SQLPERF (NETSTATS) |
Current and max number of worker threads. |
SQLPERF (RASTATS) |
Provides statistics about Read Ahead. |
-
Locks
-
Lock Types
Item |
Description |
Page |
A 2K data page or index page. This is the most common type
of lock. |
Extent |
A contiguous group of eight data pages or index pages. This
lock is only used for allocation. |
Table |
An entire table, including all data and indexes. |
Intent |
A special type of table lock to indicate the type of page
locks currently placed on the table. |
-
Replication
-
sysarticles |
Publications Server |
Information for each article created for replication. |
syspublications |
Publications Server |
One row for each publication created. |
syssubscriptions |
Publications Server |
One row per subscription from a subscribing server. |
MSlast_job_info |
Subscription Server |
Tracks the last successful transaction replicated. |
MSjob_commands |
Distribution Server |
Maintains a record of commands associated with a job. |
MSjobs |
Distribution Server |
Information about jobs, including date. |
MSsubscriber_jobs |
Distribution Server |
Information for each subscriber about the commands it needs
to receive. |
In central publisher replication, subscription servers
will generally treat replicated data as read-only.
The publication process is not controlled automatically
by the SQL Executive Service.
There should be one cleanup process for each publisher/subscriber
relationship.
The distribution process operates on both transactions
and synchronization jobs.
Transactions are read from the transaction log of the
source database and posted to the destination db.
The log reader process moves transactions to the distribution
database.
On a heavily used server, use Central Publisher with
Remote Distributor.
Replication over an expensive communications link should
use manual synchronization.
The repl_subscriber account is installed into the msdb,
master (and distribution) databases.
Replication datafiles are kept in \MSSQL\REPLDATA or
the working directory of the distribution DB.
A distribution server requires a minimum of 32MB.
A Scheduled Table Refresh replaces the entire table.
If the Synchronization process is failing, try to run
the SYNC task manually.
To start replication after completing manual synchronization,
choose 'Sync Completed' in Sub Server.
-
Application Development
-
SQL Enterprise Manager |
A graphical administration tool that simplifies managing
a muliple-server environment. Uses SQL-DMO automation objects. |
SQL Service Manager |
Used only to start and stop SQL Server. |
SQL Executive Service |
Uses Transact-SQL to run scheduled tasks through the command
prompt. |
SQL DMO |
Provides 32-bit OLE Automation objects. Exposes interfaces
for all SQL Server management functions to OLE-compliant applications. |
SQL Server Distributed Mangement Framework (SQL-DMF) |
Encompasses all of the functionality provided by SQL-DMO.
Includes a set of distributed management tools. Provides services
for replication. Has three levels:
- Lowest - Direct access to SQL Server via Transact SQL.
- Middle - DMO.
- Highest - SQL Server Enterprise Manager.
|
SQL Distributed Management Objects |
This includes alerting and notification and all other features
accesible through Transact-SQL. Applies the Component Object
Model to SQL Server functions. Used for managing collections
of properties, methods, objects, and events. |
SQLOLE |
Allows multiple SQL Servers to be managed from a single
user application. OLE Automation is used most often with SQL-OLE. |
Open Data Services (ODS) |
Can be used to develop applications that will act as a Windows
NT service, a remote stored procedure application, or a gateway
application to other database management systems. Used most
often to create extended stored procedures. |
DB-Library |
Functions often use the DBPROCESS parameter as their first
argument. The dbopen function initializes the DBPROCESS structure.
Can act as a connection between a front-end application and
SQL Server. Only DB-Library can be used from MS-DOS (ODBC
cannot). |
-
Performance
-
Maximum number of user connections for SQL Server
is 32,767.
After SQL memory ALLOC, Procedure and Data Cache take
remaining memory.
A low cache hit ratio is one indication that you might
benefit from putting tempdb in RAM.
Make sure the number of user connections is less then
maximum number of worker threads to avoid thread pooling.
UPDATE STATISTICs when you add memory or key values
change in an index, or after truncating table.
Setting a database to Read-Only disables all locking.
The setting for SQL Server memory is always allocated
in 2K units.
By default SQL Server uses 8MB if there is less than
32MB in server, and 16MB if there is greater than 32MB in server.
The Current Activity tool of SQL Enterprise manager
can graphically show locking information.
Only users connection threads can be limited by 'max
worker threads'.
Default value for the locks configuration option is
5000 locks.
SET SHOWPLAN ON reports join order and indexes used.
SET STATISTICS IO ON shows actual number of table scans
and pages read from cache and disk.
-
Alerts
-
Performance Monitor can kick off a backup of the
transaction log at 75% full and send an alert to the operator.
For alerts, Performance Monitor & SQLALRTR.EXE go hand
in hand.
An alert cannot be triggered for a SQL Server error
which is not written to the NT event log.
You can forward events and alerts to other servers (alert
severity to be sent is configurable).
Alerts can be configured to execute for a SQL Server
error number or a string contained in a SQL Server error.
SQL Server will examine sysalerts every time a SQL Server
event appear in the NT event log.
-
Clients
-
Windows workstation on Novell network with Integrated
security - use MULTIPROTOCOL.
When configuring a windows client with TCP/IP to talk
to SQLserver, entering the Server address of the Primary Domain Controller
will not work.
You can schedule .BAT, .CMD, TSQL, and replication commands
using Tasks.
To configure 16-bit clients to use standard security,
modify the WIN.INI file on the client.
You will be prompted for the port number for TCP/IP
sockets clients during setup.
The default service name for the Novell bindery is the
name of the SQL Server computer.
Use ADSP for Appletalk networks that aren't running
TCPIP (otherwise use TCP/IP sockets)
The SQL Client Configuration utility allows viewing
and changing of DB-Library and Net-Library.
It is recommended that Named Pipes not be removed even
if other libraries are installed.
Both Named Pipes and Multiprotocol allow integrated,
standard, or mixed security.
Named pipes will always work regardless of which other
protocols are installed.
Integrated security and encryption require Multiprotocol
on the client and server side.
When connecting to SQL Server from local client apps,
make foreground and background equal.
-
Logs
-
Transaction log should be 10-25% of the size of the
database.
Explicit allocation of log space is performed either
by the LOG ON clause of the CREATE DATABASE command or by using sp_logdevice
with the ALTER DATABASE command.
DISK INIT allows you to create a new device to be used
exclusively for a transaction log.
CREATE DATABASE cannot create a transaction log without
a database.
You can only disable Windows NT event logging by SQL
Server from SQL Server Setup.
-
Backups
-
Use 'skip' to ignore the ANSI headers on backup tapes.
Only two backup tasks are needed for backing-up your
database once on Monday, Wednsday, and Friday AND backing-up your
transaction log four times each day starting at 7:00am.
Backup Management cannot be performed using stored procedures.
Dumps to a tape device include a header, expiration
date, and stripe set information.
-
Accounts
-
There is always a one-to-one relationship between
sysusers and syslogins.
Each login has only one user per database, but a login
can use different names in each databases.
A single username in a database can map to multiple
logins using sysalternates.
In order to use SQLMain under NT 3.5.1, The SQL Server
service should use the LocalSystem account.
When adding a user to the database, you must provide
a valid SQL Login ID.
Create a Windows NT account for SQL Executive service
before running SQL Server Setup.
Users can read & write to the database while you are
backing-up using dynamic backups.
The Local System account is always available for use
by SQL Executive if its primary login is not available.
When using integrated security, SQL Server login ids
can only be assigned to users in trusted domains.
You can change the SA password from SQL Enterprise Manager
or with sp_password.
You can create a new password for a user with SQL Enterprise
Manager or sp_addlogin.
Users who do not have accounts in the master database
are given rights as Guest.
You cannot drop a login that is designated as an object
or database owner.
A DBO or SA can access tables for a particular user
without being GRANTED access by using SETUSER.
Only the owner and sa can execute an existing task.
With mixed, SQL Server uses integrated securiry if login
name is blank or matches NT username.
-
Miscellaneous
-
Default master Device is \MSSQL\DATA\MASTER.DAT.
SQL Server registry info is in: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
A minimum of 50meg of disk space is required to upgrade
from version 4.2 to 6.5.
The default value for the recovery interval is five
minutes.
SQL Server can send, receive, and respond to received
messages via email.
A checkpoint always occurs after a database option is
changed.
Server Group Management can be performed using drag
and drop.
The only database option which can be set for master
is 'trunc log on chkpt'.
You must have a data source name in order to establish
an ODBC connection to SQL Server.
A broken chain is when there is a view based on a view
based on a view, etc. and owner of a 'child' view loses permissions
to one of the 'parent' objects.
Forwarding events to a central server can reduce configuration
time.
The 'status' column in sysdatabase will tell you if
a particular database is suspect.
SQL Transfer Manager is used to copy data between SQL
Servers.
Transfer Manager cannot be driven using the ISQL command
line utility.
When using the SQL Transfer Manager, the source server
can be a Windows NT, OS/2, or non-Microsoft SQL Server, but the destination
server can only be Microsoft SQL Server.
It is not possible to dump a database from one processor
achitecture and load the dump into another architecture.
When a server is PAUSEd, no new conection to
any database is allowed.
SQL Enterprise Manager 'Edit Database Devices' dialog
only allows you to increase device sizes.
A database can be dumped while in use, but not loaded
while in use.
You would use 'On-Demand Schedule' with an alert or
when running a task manually.
ODBC calls cannot be included in an outgoing SQLMail
message.
You can register a server group by choosing the Server
Groups item from the Server menu in Enterprise Manager.
You must have SELECT on table and some SYSTEM objects
for BCP to copy from a table to a text file.
You must have the INSERT privilege to use BCP to add
ASCII data to a table.
Use Control Panel-Services-Setup to make the MSSQLServer
service start automatically.
Special Thanks to Matthew
Morris, MCSE+Internet, CNA, for writing the original Cramsession
for this exam! |
|