Databases and Database Files

1
Databases and Database Files
2
What are databases

• A Microsoft SQL Server database is a collection
  of objects that hold and manipulate data
• It maps the logical objects of a database back to
  the physical objects of the computer
• Files
• Memory
• Etc.

3
What are these Objects?

• Tables
• Views
• Indexes
• Stored Procedures
• Constraints

4
What are other characteristics

• It is owned by a single user or account
• It has its own system tables (or catalog)
• It has its own security
• It maintains its own files or logs for backup,
  restore and recovery
• It has a theoretical limit size of 1TB (larger in
  SQL Server 2000)

5
What is not a SQL Server DB

• A SQL Server installation
• An installation may have multiple databases (up
  to 32k)
• A single SQL Server table
• A table is part of a database. A database will
  have multiple tables
• A specific operating system file.
• A database will use multiple files

6
Special System Databases

• These are built at install time
• They include
• Master
• Model
• MSDB
• Tempdb
• Pubs
• Northwind

7
Master Database

• This is part of the system catalog
• Tracks information about all databases that are
  defined to the server
• Is critical to correct operation
• Includes information about
• Disk space and file allocation
• Configuration settings
• Account information
• Information about other SQL Servers

8
Model Database

• This is a template database
• Every time you create a database, the Model is
  used as a basis for creating it
• Characteristics you would like all databases to
  have can be put in the Model DB
• Is required to create new DBs

9
MSDB

• Is used by SQL Agent for performing scheduled
  activities against the database
• These activities might include backup,
  reorganization, or replication tasks.
• Is required for SQL Agent
• Should only be modified thru Enterprise Manager

10
TempDB

• Is used to create temporary tables in that are
  created by a user at run time
• No backup or recovery is possible (only rollback)
• Is recreated each time the system is started
• Is required

11
Pubs and Northwind

• Application databases
• Pubs was the original database included with SQL
  Server
• Northwind was originally created for MS Access
• Are not required for SQL Server
• Will be used in this class (dont delete them)

12
Create DB Statement

• CREATE DATABASE database_name ON      lt
  filespec gt ,...n      , lt filegroup gt
  ,...n LOG ON lt filespec gt ,...n
  COLLATE collation_name FOR LOAD FOR
  ATTACH
• lt filespec gt
• PRIMARY ( NAME logical_file_name ,
      FILENAME 'os_file_name'      , SIZE
  size      , MAXSIZE max_size UNLIMITED
       , FILEGROWTH growth_increment )
  ,...n
• lt filegroup gt
• FILEGROUP filegroup_name lt filespec gt ,...n

13
Database Name

• Is the name of the new database. Database names
  must be unique within a server and conform to the
  rules for identifiers. database_name can be a
  maximum of 128 characters, unless no logical name
  is specified for the log. If no logical log file
  name is specified, Microsoft SQL Server
  generates a logical name by appending a suffix to
  database_name. This limits database_name to 123
  characters so that the generated logical log file
  name is less than 128 characters.

14
Primary File Group

• Specifies that the associated file list defines
  the primary file. The primary filegroup contains
  all of the database system tables. It also
  contains all objects not assigned to user
  filegroups. The first file entry in the primary
  filegroup becomes the primary file, which is the
  file containing the logical start of the database
  and its system tables. A database can have only
  one primary file. If PRIMARY is not specified,
  the first file listed in the CREATE DATABASE
  statement becomes the primary file.

15
Secondary Filegroups

• Additional filegroups can be defined for use by
  non system tables (user defined tables)
• A common practice is to define at least one
  secondary filegroup and make it the default

16
Default Filegroups

• Any tables defined that do not specify the
  filegroup they are to be put in, go to the
  default group
• At database creation time, the Primary filegroup
  is considered the default
• The default can be changed with the alter
  database command

17
Log On

• Specifies that the disk files used to store the
  database log (log files) are explicitly defined.
  The keyword is followed by a comma-separated list
  of ltfilespecgt items defining the log files. If
  LOG ON is not specified, a single log file is
  automatically created with a system-generated
  name and a size that is 25 percent of the sum of
  the sizes of all the data files for the database.

18
Other Parameters - Size

• Is the initial size of the file defined in the
  ltfilespecgt. The kilobyte (KB), megabyte (MB),
  gigabyte (GB), or terabyte (TB) suffixes can be
  used. The default is MB. Specify a whole number
  do not include a decimal. The minimum value for
  size is 512 KB. If size is not specified, the
  default is 1 MB. The size specified for the
  primary file must be at least as large as the
  primary file of the model database.

19
Other Parameters Max Size

• Is the maximum size to which the file defined in
  the ltfilespecgt can grow. The kilobyte (KB),
  megabyte (MB), gigabyte (GB), or terabyte (TB)
  suffixes can be used. The default is MB. Specify
  a whole number do not include a decimal. If
  max_size is not specified, the file grows until
  the disk is full.

20
Other Parameters - Filegrowth

• Is the amount of space added to the file each
  time new space is needed. Specify a whole number
  do not include a decimal. A value of 0 indicates
  no growth. The value can be specified in MB, KB,
  GB, TB, or percent (). If a number is specified
  without an MB, KB, or suffix, the default is
  MB. When is specified, the growth increment
  size is the specified percentage of the size of
  the file at the time the increment occurs. If
  FILEGROWTH is not specified, the default value is
  10 percent and the minimum value is 64 KB. The
  size specified is rounded to the nearest 64 KB.

21
Create Database

• Create database archive
• On primary
• (namearch1,
• Filename'c\mssql7\data\archdat1.mdf',
• Size 100MB, maxsize 200, filegrowth 20),
• (namearch2,
• Filename 'c\mssql7\data\archdat2.mdf',
• Size100MB, maxsize200,filegrowth20)
• Log On
• (namearchlog1,
• Filename'c\mssql7\data\archlog1.ldf',
• Size100MB,maxsize200,filegrowth20)

22
Filegroups

• Files can be grouped together into file groups
  for administrative and usage purposes
• Performance can be improved by controlling the
  placement of data and indexes on specific file
  groups
• If a filegroup has multiple files, data will be
  evenly spread across all files

23
Types of Filegroups

• Primary filegroup
• Used for system tables and any table not
  specifically placed in another file group
• Secondary filegroup
• Used in explicit user defined table definitions
• Can be used to spread data across multiple disks
• Default filegroup
• If defined is used as the implicit filegroup for
  user defined tables

24
Create database archive On primary
(namearch1a, Filename'c\mssql7\data\archdat1a.
mdf', Size 100MB,maxsize 200, filegrowth
20), (namearch1b, Filename'd\mssql7\data\archda
t1b.mdf', Size 100MB,maxsize 200, filegrowth
20), Filegroup S_fg (namearch2a, Filename
'c\mssql7\data\archdat2a.mdf', Size100MB,
maxsize200,filegrowth20), (namearch2b,
Filename 'd\mssql7\data\archdat2b.mdf', Size10
0MB, maxsize200,filegrowth20) Log
On (namearchlog1,Filename'c\mssql7\data\archlog
1.ldf', Size100MB,maxsize200,filegrowth20)
25
Changing the default filegroup

• When a database is defined the primary group is
  the default group
• Use the alter database to change the default
• Alter database Archive
• modify filegroup s_fg default

26
Databases
Master DB
Filegroup
Maps to
File
File
Is Defined In
Database
Filegroup
File
File
27
Databases

• The Master DB contains the basic definitions for
  all databases in the system
• A databases system tables are defined in the
  primary file (or filegroup) at database creation
  time
• Secondary filegroups can be defined to the
  database
• A table can be specifically defined to a
  filegroup or it can default

28
Database Recommendations

• Create databases with filegroups.
• Immediately after db creation, alter the db to
  make a secondary filegroup the default
• Place files in the filegroup across multiple
  disks
• Try and locate tables that are used in complex
  joins in separate filegroups and separate disks
• Place transaction logs on non-data disks for
  performance and reliability