SQL Server System Databases

SQL Server System Databases:

Master

• Purpose - 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
  • Databases residing on the instance
  • Files for each database
  • Logins
  • Linked\Remote servers
  • Endpoints
• Additional Information
• The first database in the SQL Server startup process
• In SQL Server 2005, needs to reside in the same directory as the Resource database

• Resource
  • Purpose - The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
  • 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 does not have an entry in master.sys.databases
• TempDB
  • Purpose - Temporary database to store temporary tables (#temptable or ##temptale), 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
    • Properly Sizing the TempDB Database
    • Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state
• Model
  • Purpose - Template database for all user defined databases
  • Prominent Functionality
    • Objects
    • Columns
    • Users
  • Additional Information
    • User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
    • The database configurations such as the recovery model for the Model database are applied to future user defined databases
• MSDB
  • Purpose - Primary database to manage the SQL Server Agent configurations
  • Prominent Functionality
    • SQL Server Agent Jobs, Operators and Alerts
    • DTS Package storage in SQL Server 7.0 and 2000
    • SSIS Package storage in SQL Server 2005
  • Additional Information
    • Provides some of the configurations for the SQL Server Agent service
    • For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database
    • Missing SQL Server Agent History
• Distribution
  • Purpose - Primary data to support SQL Server replication
  • Prominent Functionality
    • Database responsible for the replication meta data
    • Supports the data for transaction replication between the publisher and subscriber(s)
• ReportServer
  • Purpose - Primary database for Reporting Services to store the meta data and object definitions
  • Prominent Functionality
    • Reports security
    • Job schedules and running jobs
    • Report notifications
    • Report execution history
• ReportServerTempDB
• Purpose - Temporary storage for Reporting Services
• Prominent Functionality
  • Session information
  • Cache

System Databases Do's and Don'ts

• Data Access - Based on the version of SQL Server query only the recommended objects. In general the system database objects are being deprecated to a set of views, so be sure all of your scripts are accessing the right objects. If not, you are going to have a big project in the future to convert all of your scripts.
• Changing Objects - Do not change system objects. In SQL Server 2005 all of the database objects have been moved to the Resource database which stores the definition for the system objects and can be updated via new SQL Server releases independent of the data.
• New Objects - Creating objects in the system databases is not recommended. If you have objects that are needed for the instance i.e. administrative items, just create a separate DBA database to store these objects.
• Sneaking a Peak - Up to this point, all of the T-SQL code for the tables, views, stored procedures, functions, etc. has been clear text. So you can review the objects and learn from the techniques used by Microsoft.
• Dropping Objects - The most prominent reason to drop system objects are for specific types of lock downs and auditing in particular industries. Although some of those practices are well documented, be sure you understand the ramifications related to administering and developing applications once those restrictions are in place.
• Security - Do not forget about the Public role and Guest user, they are the conduit for users to access the system objects. So that should answer the question of how people (logins\users) can access the objects based on the object owner or schema, depending on the SQL Server version.
• Backups - Be sure to have a consistent backup process for your system databases. Including the system databases with your user defined databases might be the best approach if a disaster occurs.
• Scope - Each SQL Server instance (including the Express Edition) has its own set of SQL Server system databases. As such, if a single Windows server has multiple SQL Server instances installed, a change to one system database only impacts the single instance, not all instances on the Windows server.

Next Steps

• Building your knowledge about the system databases is important to have a better understanding of how SQL Server works internally.
• Next time you see a script referencing a system object, dig a little deeper to learn about the object to expand your understanding of the script and how SQL Server works.
• Although system databases are databases, heed caution when working with them due to the potential impact the change could have across the instance.

When not to use the sa password in SQL Server applications

Where are the places that I should not use the sa login:

• Embedded in management tools or application development tools
• Hard coded in application connection strings
• DTS or SSIS connection objects
• UDL files
• Batch files
• SQLCMD, OSQL or ISQL T-SQL scripts
• In casual conversation
• On sticky notes on your desk or monitor

Alternatives to using the sa login

Depending on how the sa login and password is being used in your environment, dictates what steps can be taken to limit the sa usage. Once you have that information, then you can make a decision on how to migrate from using the sa credentials to other alternatives outlined below.

• Windows authentication to SQL Server with a Windows domain account
• Setup application logins that are assigned the needed rights in order for the application to operate properly
• Convert the code to execute in Jobs without having to authenticate externally to SQL Server
• Use the trusted connection switches for the connection strings or applications

Next Steps

• Assess the usage of the sa login in your environment to determine if the usage is acceptable or needs to be corrected to improve this portion of SQL Server security.
• Once you understand where the sa login is used, determine if the sa login is truly needed. See what alternatives can be put into place to limit the sa login usage and better secure the sa credentials.
• Be sure to properly test your applications to understand the needed rights and validate that the applications work properly.
• Secure the sa password to prevent this login from being used in future applications just because the password is known.
• Work with your team to understand the security needs as new applications are built.

When was the last time your SQL Server database was restored

DECLARE @dbname sysname, @days int

SET @dbname = NULL --substitute for whatever database name you want

SET @days = -30 --previous number of days, script will default to 30

SELECT

rsh.destination_database_name AS [Database],

rsh.user_name AS [Restored By],

CASE WHEN rsh.restore_type = 'D' THEN 'Database'

WHEN rsh.restore_type = 'F' THEN 'File'

WHEN rsh.restore_type = 'G' THEN 'Filegroup'

WHEN rsh.restore_type = 'I' THEN 'Differential'

WHEN rsh.restore_type = 'L' THEN 'Log'

WHEN rsh.restore_type = 'V' THEN 'Verifyonly'

WHEN rsh.restore_type = 'R' THEN 'Revert'

ELSE rsh.restore_type

END AS [Restore Type],

rsh.restore_date AS [Restore Started],

bmf.physical_device_name AS [Restored From],

rf.destination_phys_name AS [Restored To]

FROM msdb.dbo.restorehistory rsh

INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id

INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id

INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id

WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days

AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all

ORDER BY rsh.restore_history_id DESC

GO

Simple script to backup all SQL Server databases

DECLARE @Name VARCHAR(50)--database name

DECLARE @Path VARCHAR(256) -- path for backup files

DECLARE @FileName VARCHAR(256)--filename for backup

DECLARE @FileDate VARCHAR(20) -- Used for file name

SET @Path ='H:\backuptest\'

SELECT @FileDATE =CONVERT(VARCHAR(20), GETDATE(),112)

DECLARE db_Cursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_Cursor

FETCH NEXT FROM db_Cursor INTO @Name

WHILE @@FETCH_STATUS =0

BEGIN

SET @FileName =@Path + @Name + '_' + @FileDate +'.BAK'

BACKUP DATABASE @Name TO DISK =@FileName

FETCH NEXT FROM db_Cursor INTO @Name

END

CLOSE db_Cursor

DEALLOCATE db_Cursor

What is Identity Columns in Sql. What are its advantages & disadvantages?

In MSSQL there is a feature called a Identity which automatically generates a new sequential no for new record. This is a very good features because once it’s applied on any column developer needs not to be worry about maintaining numbers. SQL automatically fetches a new id & inserts into table.

This features is a panic only when some transaction is running & during execution some error occurs & transaction rolls back or if delete command is fired on system instead of Truncate command. In these scenarios identity sequence doesn’t rollback to its original count & starts from the very next value which system was trying to generate last time.