Jobs and Maintenance Plans

Jobs are good. Especially mundane, repetitive, unnoticed-when-they're-done-well jobs that you don't have time or the will to do yourself. There are certain tasks that need to be done on a regular basis, tasks which you can automate to be executed by SQL Server Agent, consistently and reliably.

A Job can have many steps, running tasks sequentially.
A Maintenance Plan is much the same.

Both Jobs and Maintenance Plans can send you emails on success, failure or completion. You'll probably find you favour one, but use both. Personally, I favour Maintenance Plans when I want to do a bunch of things, and Jobs when I want to do just one.

To create a Maintenance Plan

• Expand Management, right-click Maintenance Plans and select New Maintenance Plan, giving it a name
• From the toolbox on the left, drag whatever tasks you want to include on to the blank designer surface on the right
• Right-click and Edit to customise each Task.
• You can attach as many instance of Notify Operator Task as you like
• You'll notice each task you have selected has an arrow hanging off it. Drag these to other tasks to create a flow
• If you have 2 tasks connected, you can right-click an arrow and select Success, Failure or Completion
• Above the designer surface you can define a Schedule, and add Subplans
• Once completed, it is good practice to right-click the Maintenance Plan and Execute to test the Plan

To create a Job

• Expand SQL Server Agent, right-click Jobs and select New Job
• Name and Categorise (optional) your Job
• Click Steps and add the Steps you want to execute, defining what happens On Success or On Failure
• Click Schedules and define when and how often your Job should run
• Alerts can be defined
• Notifications can be setup, assuming you have setup Database Mail
• Once completed, it is good practice to right-click the Job and Start Job at Step... to test the job functions as expected

Alerts and Errors

It's always better to know if something goes wrong when it goes wrong, not when someone complains to you about it. SQL Server can be configured to send alert and notifications by email, once you've setup Database Mail.

Alerts can be used to notify an operator or execute a job. SQL Server generates events which enter the Windows Application Log. On startup, SQL Server Agent registers itself as a callback service with the Windows Application Event log. When SQL Server Agent receives a logged event, it compares it to the alerts defined. When a match is found, it fires an alert.

It is important to understand:

• The nature of errors
• The locations errors can occur when T-SQL code is executed
• The data returned by errors
• The severities errors can exhibit
• How to configure the SQL Server error log

Errors raised by the database have the following attributes:

• Error number
• Error message
• Severity - (0-0=information, 11-16=user can correct, 17-19=software errors, 20-24=system errors, 25=SQL Server terminating)
• State – value used to determine the location in code at which the error occurred
• Procedure name – name of stored procedure or trigger in which the error occurred (if applicable)
• Line number – Which line of a batch, stored procedure, trigger or function the error fired

You can create alerts to respond to individual error numbers or to respond to all errors of a specific severity level. It is good practice to fire alerts when errors have a severity greater than 19.

The list of system supplied error messages can be seen:

SELECT * FROM sys.messages
WHERE language_id=1033
ORDER BY message_id

The SQL Server Error Log

A new SQL Server Error Log is created with each restart. Six log files are kept by default. The sys.messages view in msdb shows which system error messages are logged by default. You can control the logging behaviour of individual errors by using the sp_altermessage system stored procedure.

Create an Alert

Alerts are created using the GUI in SSMS or by calling sp_add_alert. When defining an alert, you can specify a SQL Server Agent job that should be started when the alert occurs. Here is an Alert example:

EXEC msdb.dbo.sp_add_alert
@name=’AdventureWorks Transaction Log Full’
@message_id=9002,
@delay_between_responses=0,
@database_name=’AdventureWorks’,
@job_id=’255060-5606-6540-0bbf6fgb50’;
GO

Customised error messages can be created using sp_addmessage, and viewed using RAISERROR.
When an alert fires, two things can be configured to happen:
1) A job can start (only one, though this can start other jobs)
2) An Operator can be notified, using the sp_add_notification procedure:

EXEC msdb.dbo.sp_add_notification
@alert_name=’AdventureWorks Transaction Log Full’,
@operator_name=’SQL Admins’,
@notification_method=1;
GO

How to restore system databases

System databases are less likely to be affected by system failures however, you may need to restore them at some point so you must back them up. Not all system databases can be recovered in the same way.

master

The master database holds all system level configurations. SQL Server requires the master database before it can even run. If the master database becomes corrupt, you must first create a temporary master database, then replace it with a backup.

To obtain a temporary master database you can:
a) Use SQL Server setup program to rebuild the system databases (after which you will need to restore all as all will be rebuilt
b) Use a file-level backup of the master database files to restore the master database. This file-level backup must have been taken when the master database was not in use (ie when SQL Server was not running)

If the GUI interface isn’t working, you must then restore master database through sqlcmd using:

RESTORE DATABASE master
FROM ‘D:\AWsys.bak’
WITH REPLACE

This should be done in single-user mode (-m flag in command-line)
Check Books Online for more info on this subject

model

The model database is the template for all databases created on the SQL Server instance. When this is corrupt, the instance of SQL Server cannot start.

In the case of a corrupt model database, the instance must be started with the –T3608 trace flag as a command-line parameter. This only starts the master database. After this, the model database can be restored using RESTORE DATABASE.

msdb

The msdb is used to schedule job and alerts, and recording details of operators. It records history, including backup and restore operations. If msdb become corrupt, SQL Server Agent will not start. msdb can be restored like user databases, then the SQL Server Agent can be restarted.

resource

This is a read-only, hidden database. It can however, become corrupt by failures in areas such as I/O subsystems or memory. If so, it can be restored by a file-level restore in Windows or by running the setup program for SQL Server.

tempdb

This is a temporary database. It cannot be backed up, nor does it ever need to be as it is recreated each time SQL Server is started.

How to restore a user database

1. Right click on the Server, select Tasks and Restore
2. In General pane, select From device and locate the .bak file to restore from.
3. Select all but the final element from within the .bak file to restore
4. Select database name from To database
5. In Options select RESTORE WITH NORECOVERY
6. Run through options 1-4 again, selecting the transaction log to restore.
7. In Options select RESTORE WITH RECOVERY
8. The database has now been restored, recovered and ready for use

In the options above, it is possible to select a restore to a point in time.

If a database is restored onto another server, the backup information is not restored with the database, as it is held in the msdb database of the original system.

There may be a time when a damaged page messes up your database. First of all, you need to determine which page is damaged. This can be done by looking in System databases > msdb > Tables > dbo.suspect_pages. Then restore the page like so:

RESTORE DATABASE AdventureWorks
PAGE = '1:160' -- '1'=File_ID, '610'=page_ID
FROM AWBackup -- this is your backup device
WITH NORECOVERY

SQL Server Architecture

SQL Server is constructed from a series of many small components that work together, which fall into 3 categories

1. Query Execution Layer
   This has three sub categories:
• The Parser – checks you’ve followed the T-SQL rules
• The Algebrizer – which lists what you want to achieve and converts it to a series of logical operations
• The Query Optimizer – which considers the different ways your query can be executed. It finds an acceptable plan, which is not necessarily the most efficient.
2. Storage Engine Layer
   This manages how data is stored on disk and in memory. The main sub-categories:
• The Access Methods component – works with how you access data
• The Page Cache – stores cached copies of data pages to minimise data retrieval time.
• The Locking and Transaction Management components – maintain consisteny, integrity of data, with help of database log file.
3. SQL OS Layer
   This provides operating system functionality (ie interfaces) to SQL Server components.
   The most important functions provided by this layer are memory management and scheduling.

The most important resources SQL Server utilizes from the server platform are CPU, memory and I/O.

CPU and I/O

When a SQL Server component needs to execute code, it creates a task which represents the time it’ll take to run. These tasks are scheduled by Windows. SQL Server tasks spend most of their time waiting for something external to happen, usually I/O. When a task needs to wait for a resource, it is placed in a waiting list until the resource is available. When it is told the resource is available, it then waits for a share of CPU time, allocated by SQL OS.
SQL Server keeps records of how long tasks spend waiting and the types of resources they wait for. Query the following:
sys.dm_os_waiting_tasks
sys.dm_os_wait_stats

Memory

The Buffer Pool is the main memory object of the SQL Server, and is divided into 8KB pages – the same size as database data pages. It has three sections:

1. Free Pages – not yet used
2. Stolen Pages – used pages
3. Data Cache – used for caching database data pages. Data modification happens here, then the pages are considered dirty and so the changes are written to the database by a process called CheckPoint.
   The Data Cache uses a least recently used (LRU) algorithm to determine which pages to drop from memory when cache space is needed.

SQL Server memory and Windows memory

The memory manager within SQL OS manages the SQL Server memory. It does this by checking the Windows memory, and calculating a value it thinks appropriate to use. Once this has been exceeded, it answers requests for memory from components by freeing up memory used by other components. Basically, it impacts on component performance.

Physical and Logical I/O

1. Logical I/O – when a requested page is available in the Buffer Cache
2. Physical I/O – when a requested page is not available in the Buffer Cache and must be read from the data page into the Buffer Cache

You can minimise the number of Physical I/O operations by:

• Providing enough memory for the data cache
• Optimising the physical/logical layout (including indexes)
• Optimise queries to request as few I/O operations as possible

You can monitor logical and physical I/O operation counts by:

SET STATISTICS IO ON;

You can monitor the overall physical I/O operations by querying the sys.dm_io_virtual_file_stats system function. The values are cumulative from the last system restart.

Parallelism

The Query Optimiser decides if tasks should be synchronised and monitored (which happens if they’re expensive) and only does so if it’s worth it. Basically, Parallelism involves spreading tasks across CPUs, rather than running them sequentially.
Further reading : SQL Server threads, Affinity Mask, MAXDOP, Lazy Writer,

Planning Server Resource Requirements

There is no formula for this difficult task. You should consider the following:

• If there are any case studies, perhaps you have similar systems in your own organisation
• Consider things at a business level, rather than a server level (ie ‘How long does an order take to save’ rather than ‘How long does an I/O operation take?’)
• Simulate a database of the same size as the final, operational one
• Monitor things after the system goes live.

Planning CPU Requirements

• You should have as many CPUs as possible.
• Anything over 30% will lead to slow systems. Peaks above this are only a problem if they are sustained.
• Any server running production based SQL Server should not have any other major services running

Planning Memory Requirements

SQL Server uses memory while queries are being executed. Each user executing a query needs a separate memory space known as an execution context. A lack of memory can impact a system in such a way that it appears to be a lack of CPU or I/O.

Planning Network Requirements

• Consider the use of multiple network adapters
• Most low-level network protocols (ie Ethernet) become unusable quite quickly
• Remember things like backups cause high volumes of network I/O, which can slow down connections
• Database mirroring and replication might need dedicated connections to perform correctly

Planning storage and I/O Requirements

In larger organisations, the following will be determined by a team of people, each with their own skillset.

• Determine requirements of the application
• Determine the style of storage used (SAN vs DAS).
• DAS (direct attached storage) generates better, more predictable results. For DAS storage, RAID levels 1,5 and 10 are the most common in use on SQL Server systems.
• For most systems, the number of drives/spindles will matter more than the size of the disk. Often, a single large disk will not provide sufficient I/O operations per second or enough data throughput (MB/sec) to be workable.
• Write caches can substantially improve SQL Server I/O performance, but make sure hardware caches guarantee a write, even after a system failure. Many drive write caches cannot survive failures and can lead to database corruptions.

Stress testing (SQLIOSIM)

A common problem occurring in database deployments is when the server platform is delivered and installed and immediately commissioned once SQL Server is installed on it. There are many subtle I/O problems that can occur and can be hard to evaluate.
Pre-installation stress-testing can help using tools such as SQLIOSIM


Further reading: NUMA, SAN vs DAS storage, SSD based systems, SQLIOSIM

How to setup Notifications

A Notification, Alert or Error is sent to an Operator, which must be setup first.

Configure SQL Server Agent Operators

1. Under SQL Server Agent, right-click ‘Operators’ and add a New operator
2. Right-click ‘SQL Server Agent’, select ‘Properties’, ‘Alert System’ then Enable mail profile and select a Mail Profile.
3. Now right-click a job from your list, select ‘Properties’ and select ‘Notifications’. Now check ‘Email’, selecting the Operator and when to send the email (on job completion, success or failure).
4. You will need to restart the SQL Server Agent for the effects to take place.

To add a Notification within a Maintenance Plan

1. Right-click and ‘Modify’ maintenance plan
2. Drag an instance of Notify Operator Task from the ‘Toolbox’ on the left
3. Right click and ‘Edit’ the task. You will need to set up a connection
4. Select an Operator and define your own email content
5. Now highlight the task you want to be notified about. Drag the arrow that hangs from it onto the Notify Operator Task. To define if the notification should be sent on success, failure or completion, right-click the arrow.