SQL Server Maintenance Plans Jerome Espinoza Database Administrator - PowerPoint PPT Presentation

RMUG 2014 Laramie County Community College SQL Server Maintenance Plans Jerome Espinoza Database Administrator Laramie County Community College 1

Housekeeping Laramie County Community College  Please turn off cell phones  If you must leave the session early, please do so as discreetly as possible  Please avoid side conversation during the session Thank you for your cooperation! RMUG 2014 2

Overview or Agenda Laramie County Community College Introduction  The purpose of this presentation is to demonstrate how to implement many of the ‘Best Practices’ for SQL Server maintenance plans. The benefit to doing this is: – Managing data and transaction log files – Eliminating index fragmentation RMUG 2014 – Ensuring accurate, up-to-date statistics – Detecting corrupted database pages – Establishing an effective backup strategy 3

Overview or Agenda Laramie County Community College Introduction  Unfortunately, the material presented here cannot possibly be presented in great detail because of the time allotted for one session.  This material will be presented conceptually with as much detail as time permits. RMUG 2014 4

RMUG 2014 Laramie County Community College Agenda 5

Laramie County Community College Create SQL Server Administration Objects There are several custom database objects required by this maintenance plan that need to exist in each database. This section describes how to build those objects. RMUG 2014 6

Laramie County Community College 1. Create SQL Server Administration Objects A. Set each SQL Server instance to allow both SQL Server and Windows Authentication B. Create the following objects in each SQL Server instance: i. A login with SA privileges (I use the same name and password in each SQL Server instance) ii. A database named DBAdmin owned by the user created in step (i) iii. A stored procedure named spDBFileSpace in the DBAdmin database that will update a table with all of the database file sizes iv. A stored procedure named spOSDiskSpace that will check the free space to be > 10% of total disk space on each disk RMUG 2014 v. A table named DB_File_Space, located in the DBAdmin database, in which the stored procedure from step (iii) can record database file size data. 7

Laramie County Community College 1.A Database Authentication Open the server properties window and check the Security tab to be sure that both SQL Server and Windows Authentication are enabled for the server. RMUG 2014 8

RMUG 2014 Laramie County Community College 1.B.i Database Login. 9

RMUG 2014 Laramie County Community College 1.B.ii DB Admin Database 10

RMUG 2014 Laramie County Community College 1.B.iii spDBFile Space Stored Procedure 11

RMUG 2014 Laramie County Community College 1.B.iv spOSDiskSpace Stored Procedure 12

RMUG 2014 Laramie County Community College 1.B.v DB_File_Space Table 13

Laramie County Community College Maintenance Plans The following pages describe how to setup SQL Server maintenance plan jobs using the SQL Server Management Studio. RMUG 2014 14

RMUG 2014 Laramie County Community College SQL Server Maintenance Plans 15

RMUG 2014 Laramie County Community College SQL Server Maintenance Plans 16

Laramie County Community College SQL Server Maintenance Plans RMUG 2014 The screen shown Above will appear. Drag a task from the toolbox into the MP and configure as shown on the following pages. 17

Laramie County Community College Database Backups Database backups are the last line of defense against data loss or corruption and in my estimation the single most import duty performed by a DBA. RMUG 2014 18

RMUG 2014 Laramie County Community College 2. Database Backups 19

Laramie County Community College 2. Database Backups RMUG 2014 Your organization should decide what is best regarding backup storage. 20

Laramie County Community College 2. Database Backups • Back Up Database Task is used in this maintenance plan • Backup frequency depends on the type of recovery model/backup as per the table on the previous page. • Backups expire after 14 days • System Administrators are notified in case of job failure RMUG 2014 21

RMUG 2014 Laramie County Community College 2. Database Backups 22

RMUG 2014 Laramie County Community College 2. Database Backups 23

Update Statistics Laramie County Community College  Perform this function weekly to update query optimization statistics on a table or indexed view. RMUG 2014 24

3. Update Statistics Laramie County Community College  Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile when executed.  Do not update statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries.  You may want to consider updating your statistics weekly. This can be done over the weekend, or the middle of the night, on all user databases.  Also, leave the Auto Update Statistics and Auto Create Statistics properties set to true for your database.  The auto update will be invoked much less frequently if you manually RMUG 2014 force an update every weekend. This is a good thing because you don’t want queries recompiling at random times throughout the business week if you can help prevent it. 25

3. Update Statistics Laramie County Community College  Update Statistics Task is used in this maintenance plan – Small databases use a full scan – Large databases scan a percentage of the entire database  This task is performed on all user databases  This task is run weekly off-hours  System Administrators are notified in case of RMUG 2014 job failure 26

Verify Integrity Laramie County Community College  Perform this function weekly to check the allocation and structural integrity of user and system tables, and of indexes in the database. RMUG 2014 27

4. Verify Integrity Laramie County Community College  Use the Check Database Integrity Task to check the allocation and structural integrity of user and system tables, and indexes in the database  This task is run weekly off hours over the weekend  This is accomplished by running the DBCC CHECKDB Transact-SQL statement  Running DBCC ensures that any integrity problems within the database are reported, thereby allowing them to be addressed later by a system administrator  All User Databases can be checked in one maintenance RMUG 2014 plan  System Administrators are notified in case of job failure 28

Maintenance File Cleanup Laramie County Community College  This feature will remove files related to maintenance plans, including database backup files and reports created by maintenance plans. RMUG 2014 29

5. Maintenance File Cleanup Laramie County Community College  Two Maintenance Cleanup Tasks are used in this maintenance plan – First task deletes old database backups > two weeks old – Second task deletes old transaction log backups > two weeks old  This task is run weekly (after the weekly full backup jobs) RMUG 2014  System Administrators are notified in case of job failure 30

Perfmon/SQL Agent Job Log Cleanup Laramie County Community College  This feature will remove log files created by PerfMon and SQL Agent jobs that are not removed by the Maintenance File Cleanup task. RMUG 2014 31

6. PerfMon/SQL Agent Job Log Cleanup Laramie County Community College  This is a custom maintenance plan designed to cleanup old PerfMon files created by the Alert and Notification System* and SQL Agent log files not removed by the Maintenance File Cleanup task.  Execute SQL Server Agent Job Tasks are used in this plan  This maintenance plan has two subplans – Subplan 1: PerfMon log files will be deleted after 14 days – Subplan 2: SQL Agent job log files will be deleted after 90 days RMUG 2014  This maintenance plan is performed weekly  •DBA is notified in case of job failure 32

RMUG 2014 Laramie County Community College 6. PerfMon/SQL Agent Job Log Cleanup 33

6. PerfMon/SQL Agent Job Log Cleanup Laramie County Community College Create a SQL Agent job named Delete_Old_PerfMon_Logs with the properties set as follows: • The Type should be set to “Operating System (CmdExec )” • Be sure to run as “ AdminProxy ”. Will require AdminProxy creation. • The command should run RMUG 2014 the batch file created on the previous page. 34

RMUG 2014 Laramie County Community College 6. PerfMon/SQL Agent Job Log Cleanup 35

RMUG 2014 Laramie County Community College 6. PerfMon/SQL Agent Job Log Cleanup 36

6. PerfMon/SQL Agent Job Log Cleanup Laramie County Community College Create a SQL Agent job named Delete_Old_SQLAgent_Logs with the properties set as follows: • The Type should be set to “Operating system (CmdExec )” • Be sure to run as “ AdminProxy ”. • The command should run RMUG 2014 the batch file created on the previous page. 37

RMUG 2014 Laramie County Community College 6. PerfMon/SQL Agent Job Log Cleanup 38