Database Reference
In-Depth Information
Microsoft SQL Server Analysis Services:
Microsoft SQL Server Analysis Services
comprise a set of technologies to extend data warehousing into SQL Server. The Server
Analysis Services help us build OLAP (On Line Analytical Processing) data to perform
detailed trend analysis in many ways, as well as support data mining. The services
provide the capability to build and control these cubes, and a user interface to build,
administer, and query these cubes is also installed. The server side installs only on
Windows 2003 (or later). The client components and user interface are also available on
Windows clients.
Microsoft English Query:
Microsoft English Query allows an administrator to
configure a database schema and allows end users to run their database queries in English
instead of Transact-SQL. This capability is particularly beneficial for Internet-based
applications that don't want to force users to run SQL statements. For example, we can
say, “Show me the number of books sold for each author this year,” rather than use a
complicated SQL statement.
18.5 MS SQL Server Default Databases
When you install SQL Server, the following databases are installed. You can add
additional databases later, but these databases are guaranteed to be there. Some of them
(
master
,
model
,
tempdb
, and
MSDB
) are system databases — they cannot be dropped
without causing serious harm to SQL Server. The other two,
pubs
and
Northwind
,
are simply samples to help we learn SQL Server. These can be safely dropped from
production SQL Servers.
The master Database:
The
master
database is the key database for running SQL
Server. It contains a pointer to the primary data file for every other database installed on
system, as well as key server wide information. This server wide information includes
such items as system wide error messages, login information, system stored procedures,
and connected or linked servers. The master database can be recovered only in the event
of a disaster with special techniques.
The model Database:
The
model
database is best thought of as a template database.
Each time a new database is created, the
model
database is actually copied, and then the
size and other changes requested for new database are applied. Therefore, any object that
exists in the model database is copied to the new database at the time of creation. For
example, you can place a table, a username, and other essential objects in this database
right after installation of SQL Server. Each time a new database is created after that, these
essential objects are automatically included. The
model
database is about 768KB after
installation. Because the model is copied to create each new database, no database can
be smaller than the
model
.
The tempdb Database:
The
tempdb
database is the place where sorts, joins, and
other activities that require temporary space are performed. It's approximately 2MB after
installation, but as is the case with all databases in SQL Server by default, it can grow as
more space is required. The
tempdb
database is reinitialized each time SQL Server (the
SQL Server service) is started.
The MSDB Database:
The MSDB database supports the SQL Server Agent service,
including storing information about jobs, alerts, events, and replication. A history of all
backup and restore activity is also kept in this database. The MSDB database is about
12MB by default.
