SQL Server System Databases
*********************************************************************************
Master
Core system database to manage the SQL Server instance.
In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema.
In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.
* Prominent Functionality
+ Per instance configurations/parameters
+ Databases residing on the instance
+ Files for each database/METADATA
+ Logins
* Additional Information
+ The first database in the SQL Server start up process
+ In SQL Server 2005, needs to reside in the same directory as the Resource database
+ SQL Server cant operate without the Master database
Resource
The Resource database is responsible for physically storing all of the SQL Server 2005 system objects and Service Pack information(CU/Hot-fix/Service Pack).
This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
Hidden and read-only database that is not accessible via the SQL Server tool set.
* Prominent Functionality
+ System object definition
* Additional Information
+ Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
+ Prior to SQL Server 2005 the system related data was stored in the master database
+ Read-only database that is not accessible via the SQL Server 2005 tool set
+ The database ID for the Resource database is 32767
+ The resource database doesn't have entry in master.sys.databases
+ In SQL server 2005, it is recommended to place Resource database along with Master database. If possible in the same location.
+ In SQL Server 2008, the location of the Resource database is
Why Resource Database is placed in Binn folder in SQL Server 2008?
All binary files were stored under the Binn folder [ex: sqlservr.exe], In 2008 resource database is treated just as if it were a dll, a library file for SQL Server. Resource database contains only system objects, but no data.
TempDB
Temporary database to store temporary tables (#temptable or ##temptable), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
* Prominent Functionality
+ Manage temporary objects listed in the purpose above
* Additional Information
+ Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state
Model
Template database for all user defined databases
MSDB
Primary database to manage the SQL Server Agent configurations
* Prominent Functionality
+ SQL Server Agent Jobs, Operators and Alerts
Distribution
Primary data to support SQL Server replication
*******************************************************************************
Temporary table
*******************************************************************************
SQL Server provides the concept of temporary table.
These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.
Different Types of Temporary Tables
SQL Server provides two types of temp tables based on the behavior and scope of the table.
* Local Temp Table
* Global Temp Table
Local Temp Table
Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.
* the scope of Local Temporary table is only bounded with the current connection of current user.
Global Temp Table
Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.
* Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.
Storage Location of Temporary Table
Temporary tables are stored inside the Temporary Folder of tempdb. Whenever we create a temporary table, it goes to Temporary folder of tempdb database.
When to Use Temporary Tables?
* When we are doing large number of row manipulation in stored procedures.
* This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
* When we are having a complex join operation.
Points to Remember/Delimits:
* Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
* Number of rows and columns need to be as minimum as needed.
* Tables need to be deleted when they are done with their work.
*********************************************************************************
Files and Filegroups
*********************************************************************************
- Every database in SQL Server associated with three types of files - Primary data files, Secondary data files and Log files
Primary data files: The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.
Secondary data files: Secondary data files make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several secondary data files. The recommended file name extension for secondary data files is .ndf.
Log files: Log files hold all the log information that is used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.
- Every database comprise minimum of 2 files, one mdf and one ldf
- A database can have only one mdf and additional ndf's
- A mdf can grow up to 16TB
- A log file grow up to 2 TB
- File group is a group of files logically grouped together
- mdf file should be present ONLY in primary file group
- Log files don't have file groups
- Default work group is PRIMARY
- Primary file group can't be read-only
- If file groups are removed, the associated data files will also be removed
- SQL Server does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions help you identify the different kinds of files and their use.
Why File Groups?
- Logical grouping for ease of identification/administration and maintenance
- performance
- ease of backups
*********************************************************************************
Database States/Status
*********************************************************************************
A database is always in one specific state.
ONLINE: Database is available for access.
OFFLINE: Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
RESTORING: One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
RECOVERING: Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.
RECOVERY PENDING: SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
SUSPECT: At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.
EMERGENCY: User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.
To verify the current state of a database, select the state_desc column in the sys.databases catalog view or the Status property in the DATABASEPROPERTYEX function.
(Or)
Using SQL Server Management Studio, Right click on Database and choose properties option. Under General page - Database tab, you can view the Status field for Database status.
*********************************************************************************
Pages and Extents
*********************************************************************************
More to be blogged soon !!!!
