Maintenance Plans

– Predefined set of maintenance tasks on SQL Server instances
– Can be created using

• Maintenance plan wizard – straigh
• Maintenance Plan Design tab – gives few extra options and control over maintenace tasks.

– These are basically SQL Server agent jobs which are executed by SSIS packages.

– Following is a list of maintenance tasks that can be performed using maintenance plans

• Backup database task
• Check Database integrity task
• Execute SQL server agent job task
• execute tsql statement task
• history cleanup task
• maintenance cleanup task
• notify operator task
• rebuild index task
• reorganizes index task
• shrink database task
• update statistics task

Every maintenance task can be reproduced using TSQL – so creating your own maintenance jobs gives you more control and flexibility.

– Maintenance plans can be exported and imported to other severs.

 

Policy-Based Management

• Policy based system for managing one or more instances of SQL Server
• Create conditions that contain condition expressions. Then, create policies that apply the conditions to database target objects

Facets are used to create conditions, which can then be used to apply to policies

Facet  – group of logical propertie

Condition – A check that is evaluated by a policy

Policy – a conditon that is applied to a given set of target object

Category – A group of policies that help you manage policy enforcement

Target – Object that is being managed by the policy

Administering Servers by Using Policy-Based Management

Policy-Based Management is a system for managing one or more instances of SQL Server 2008. When SQL Server policy administrators use Policy-Based Management, they use SQL Server Management Studio to create policies to manage entities on the server, such as the instance of SQL Server, databases, or other SQL Server objects.

Note: The msdb database contains all the policy definitions, as well as the jobs that execute the policy checks.

Policy-Based Management has three components:

• Policy management –  Policy administrators create policies.
• Explicit administration –  Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.
• Evaluation modes – There are four evaluation modes, three of which can be automated:
  • On demand. This mode evaluates the policy when directly specified by the user.
  • On change: prevent. This automated mode uses DDL triggers to prevent policy violations.

    Important

    If the nested triggers server configuration option is disabled, On change: prevent will not work correctly. Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.

  • On change: log only. This automated mode uses event notification to evaluate a policy when a relevant change is made.
  • On schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy.

  When automated policies are not enabled, Policy-Based Management will not affect system performance.

Source:
http://technet.microsoft.com/en-us/library/bb510667.aspx
Mastering SQL Server 2008 –  By Michael Lee, Gentry Bieker (policy category explaination)

 

Resource Governor

Resource Governor can be used to limit CPU and memory allocation for inbound requests. Cannot place limitations on internal SQL Server operations.

• Manage and limit the resource consumption  of inbound requests
• Prioritize workload

Consists of 3 main components

• Resource pool – allocates resources within a SQL Server instance – 2 types of resource pooI
• Internal – Used for all internal database engine processing. Cant be modified. CPU and memory are never limited here. It uses what it needs, and rest is available for others
• Default – Processes that have not been assigned to any specific resource pool (internal or user defined), gets assigned here.
• Workload Group – Used to group similar queries, so that they can be treated as a single unit. This is assigned to a resource pool.
• Classier – Used to assign an incoming request to the appropriate workload group. This function is created in the master database – only one function can be active at a given time for  resource governor.

Important The (Dedicated Administrator Connection) DAC is not subject to Resource Governor classification and can be used to monitor and troubleshoot a classifier function.

Example
–Create resource Pools
CREATE RESOURCE POOL ReportingPool
GO

CREATE RESOURCE POOL InteractivePool
Go

–Create Groups
CREATE WORKLOAD GROUP ReportingGroup
USING ReportingPool
GO

CREATE WORKLOAD GROUP InteractiveGroup
USING InteractivePool
GO

–Create classifier function
CREATE FUNCTION classifier()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
DECLARE @retVal SYSNAME
IF SUSER_SNAME() = ‘SCOTT’
SET @retval = ‘InteractiveGroup’
ELSE IF SUSER_SNAME() = ‘REPORTS’
SET @retval = ‘ReportingGroup’
RETURN @retVal
END
GO

–SET THE classifying function
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.classifier)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Sources:
1. http://www.sqlserver-training.com/video-how-to-implement-resource-governor/-
2. Pro SQL Server 2008 Aministration (Managing Query performance) – Ken Simmons, Sylvester Carstarphen

 

Log Shipping

Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances.

Log Shipping Operations

• Back up the transaction log at the primary server instance
• Copy the transaction log file to the secondary server instance.
• Restore the log backup on the secondary server instance.

Important: A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

Configuration consists of

• Primary server and database – must use the full or bulk-logged recovery model;
• Secondary server and databases (can have more than one)
• Optional monitor server

The optional monitor server tracks all of the details of log shipping, including:

• When the transaction log on the primary database was last backed up
• When the secondary servers last copied and restored the backup files.
• Information about any backup failure alerts.

Log Shipping Jobs – handled by dedicated SQL Server Agent jobs

• Backup job – created on the primary server instance for each primary database. It performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information
• Copy job – created on each secondary server instance in a log shipping configuration. This job copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server
• Restore Job – created on the secondary server instance for each log shipping configuration. This job restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information
• Alert job – created on the monitor server instance (if used). This alert job is shared by the primary and secondary databases of all log shipping configurations using this monitor server instance. Any change to the alert job (such as rescheduling, disabling, or enabling the job) affects all databases using that monitor server. If a monitor server is not used, alert jobs are created locally on the primary server instance and each secondary server instance.

Enabling and configuring Log Shipping

Source http://msdn.microsoft.com/en-us/library/ms190640.aspx

 

Database Mirroring

• Works only if the database is in full recovery model
• master, msdb, tempdb, or model databases cannot be mirrored
• Two copies of a single database that must reside on different server instances of SQL Server Database Engine.
• One server instance serves the database to clients (the principal server).
• The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session.
• When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions.
• When the session is not synchronized (asynchronized), the mirror server is typically available as a warm standby server (with possible data loss).
• Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance – high performance mode. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.- high safety mode

Transaction safety is controlled by the SAFETY property of the ALTER DATABASE statement. On a database that is being mirrored, SAFETY is either FULL or OFF.

• If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase. If a witness is set in high-safety mode, the session supports automatic failover.
• If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode, and the WITNESS option should also be OFF.

Role Switching

Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly.

The following three forms of role switching exist.

• Automatic failoverThis requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server.

  The role of the witness is to verify whether a given partner server is up and functioning. If the mirror server loses its connection to the principal server but the witness is still connected to the principal server, the mirror server does not initiate a failover.

• Manual failoverThis requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized.
• Forced service (with possible data loss)Under high-performance mode and high-safety mode without automatic failover, forcing service is possible if the principal server has failed and the mirror server is available.

  Important
  High-performance mode is intended to run without a witness. But if a witness exists, forcing service requires that the witness is connected to the mirror server.

In any role-switching scenario, as soon as the new principal database comes online, the client applications can recover quickly by reconnecting to the database.

 

Split File Backup

SQL Server backup command can take backups in two or more split parts. By simply adding an additional DISK option we can split the files backup files.

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

The backup is split into three equal parts of the original backup file size.

Restoring it is very similar – just add an additional DISK option.

RESTORE DATABASE [AdventureWorks]
FROM DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

Source: http://blog.sqlauthority.com/2009/09/02/sql-server-mirrored-backup-and-restore-and-split-file-backup-2/

 

Restore Verifyonly

This operation

• checks to make sure that a backup is intact physically
• ensures that all the files in the backup set is readable and can be restored
• Does not verify the structure of the data on the backup. However, if the backup was created using WITH CHECKSUMS, verifying the backup using WITH CHECKSUMS can provide a good indication of the reliability of the data on the backup

Checks performed by RESTORE VERIFYONLY include:

• That the backup set is complete and all volumes are readable.
• Some header fields of database pages, such as the page ID (as if it were about to write the data).
• Checksum (if present on the media).
• Checking for sufficient space on destination devices.

RESTORE VERIFYONLY FROMDISK=‘c:\sqlskills\broken2005.bck’;

GO

Source:http://msdn.microsoft.com/en-us/library/ms188902.aspx

 

File and filegroup backups

A file backup contains all the data in one or more files (filegroup). This backup can be performed when the database size and performance requirements make a full database backup impractical.

Minimally, this statement must specify the following:

• The database name.
• A FILE or FILEGROUP clause for each file or filegroup, respectively.
• The backup device on which the full backup will be written.

So long as you do not need to restore the priary filegroup and you are running sql server 2008 enterprise, the database can remain online and accessible to applications during the resotre operation. Only theportion of the database being restored is off line.

Partial Backup
To reduce the size of a backup to only the filegroups that can change, you can perform a partial backup. Patrital backps are performed by specifying the READ_WRITE_FILEGROUPS option as follows

BACKUP DATABASE DBNAME READ_WRITE_FILEGROUPS [filegrouplist] TO DEVICE

When a partial backup is executed, SQL SErver backs up the primary filegroup, all read/write filegroups, and any explicitly specified read only filegroups

Examples of filegroup backups

A. Creating a file backup of two files

The following example creates a differential file backup of only the SGrp1Fi2 file of the SalesGroup1 and the SGrp2Fi2 file of the SalesGroup2 filegroup.

--Backup the files in the SalesGroup1 secondary filegroup.
BACKUP DATABASE Sales
   FILE = 'SGrp1Fi2',
   FILE = 'SGrp2Fi2'
   TO DISK = 'G:\SQL Server Backups\Sales\SalesGroup1.bck'
GO

B. Creating a full file backup of the secondary filegroups

The following example creates a full file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.
BACKUP DATABASE Sales
   FILEGROUP = 'SalesGroup1',
   FILEGROUP = 'SalesGroup2'
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'
GO

C. Creating a differential file backup of the secondary filegroups

The following example creates a differential file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.
BACKUP DATABASE Sales
   FILEGROUP = 'SalesGroup1',
   FILEGROUP = 'SalesGroup2'
   TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'
GO
   WITH
      DIFFERENTIAL,
GO

Source: http://msdn.microsoft.com/en-us/library/ms179401.aspx