SQL Server 2012 makes your database maintenance jobs easy by proving an effective Maintenance Plan Wizard. This wizard does a fair job of creating core maintenance plans for your database. However, you may choose to define a variety of additional tasks and checks in order for providing extra stability to your database. This objective is achieved by creating plans manually, which could cater to your specific requirements.

Benefits of Using Maintenance Plans

The maintenance plans created using SQL Server 2012 database engine are executed by a SQL Server Agent job. These plans may be either automated or scheduled to run at some specific time. Maintenance plans in SQL Server offer great benefits. Some of the best characteristics of these plans are listed below:

• You can create a workflow or a course of action comprising typical maintenance tasks. It is easy to generate custom Transact-SQL scripts.

• You can sort essential tasks into a logical order and create conceptual hierarchies. Specific tasks can be combined together and made to run at any desired point in time.

• With the support for multi-server plans, you can run maintenance jobs in master server/target server environments.

• You can keep track of the plan history by recording all maintenance activity to remote servers.

• You get the benefit of both Windows Authentication and SQL Server Authentication. It is recommended to use Windows Authentication (if possible).

Tasks Performed by Maintenance Plans

Maintenance plans provide you with a great deal of functionality that can be utilized to optimize a SQL Server database. With the help of such plans, you can perform a variety of tasks as follows:

• You may redesign indexes by specifying a new fill factor and contribute to rearranging the data stored on both the data pages and index pages. By providing a new fill factor for indexes, you can ensure that the database has proper organization of data and free space. This facilitates smooth and faster database growth.

• You can shrug off empty database pages to compress your data files.

• You can update the statistics information for all indexes in your database in order to improve the performance of query optimizer. The query optimizer uses this information to speed up access to data. The index statistics information is updated on a routine basis automatically by the SQL Server, but this option is a must-have if you want the updates to occur immediately.

• Maintenance plans provide you an easy tool to run various types of integrity checks against your database. These checks are mandatory to make sure no data damage has occurred within the database.

• You can perform database and transaction log backups as well as retain them for a desired period of time. This helps you maintain a history of backups and perform point-in-time recovery of your database when a disaster strikes. You may also choose to create differential database backups.

• You can execute SQL Server Agent jobs. This will help you automatically create jobs that perform different actions and create maintenance plans to smoothly carry out these jobs.

Maintenance Plan Wizard

With the help of the Maintenance Plan Wizard in SQL Server 2012, you can easily generate a single server or multi-server maintenance plan. The tasks included in a maintenance plan can be run periodically by Microsoft SQL Server Agent. Maintenance plans makes your database maintenance experience a breeze by simplifying or automating database backups, database statistics updates, database integrity checks, and more.

Limitations and Restrictions

• For creating and running multi-server maintenance plans, you need to have a multi-server environment comprising a master server and one or more master servers. The master server takes full control of maintaining a multi-server maintenance plan. These plans cannot be maintained on the target servers.

• The members of the ‘db_ssisadmin’ and ‘dc_admin’ roles may gain exclusive rights of the ‘sysadmin’ role. This may occur when executing maintenance plans, data collection sets, or other Integration Services packages because these roles make changes to the Integration Services packages allowing the SQL Server Agent jobs to use an account with ‘sysadmin’ privileges. In order to avoid this, you should preset the SQL Server Agent jobs to use an account with restricted privileges or only add the members of ‘sysadmin’ role to the ‘db_ssisadmin’ and ‘dc_admin’ roles when running these packages.

Using the Maintenance Plan Wizard to Create Maintenance Plans

Mentioned below is the detailed procedure to create a maintenance plan for your database using the Maintenance Plan Wizard:

• Launch Microsoft SQL Server Management Studio (SSMS), navigate to the Object Explorer, and expand the server where you need to create a maintenance plan. Expand the ‘Management’ folder below the server. Right-click the ‘Maintenance Plans’ folder and choose ‘Maintenance Plan Wizard’. This will launch the SQL Server Maintenance Plan Wizard as shown below. Click ‘Next’ to proceed.

• The next screen presents you with the name and description of your database maintenance plan. This page enables you to specify a range of plan properties. On this page:

• • Provide the name of your maintenance plan in the ‘Name’ box.

  • Type some details of your plan in the ‘Description’ box to help other administrators easily address the objectives of your plan.

  • Provide credential to the Microsoft SQL Server Agent in the ‘Run as’ list.

  • Choose from ‘Separate schedules for each task’ or ‘Single schedule for the entire plan or no schedule’ options for specifying the intended recurring schedule of each task in the plan. If you choose ‘Single schedule for the entire plan or no schedule’, go to ‘Schedule’ and click ‘Change’:

    • The ‘New Job Schedule’ dialog box will be displayed.

    • Choose any of the following types of schedules in the ‘Schedule type’ list. If you choose ‘Separate schedules for each task’, you have to repeat these steps for each task in your maintenance plan.

      • Start automatically when SQL Server Agent starts

      • Start whenever the CPUs become idle

      • Recurring (This option is selected by default)

      • One time

• • • Select or deselect the ‘Enabled’ checkbox for enabling or disabling the schedule.

    • If you choose ‘Recurring’:

      • You can choose the desired frequency of occurrence for the schedule under ‘Frequency’ in the ‘Occurs’ list.

        • For ‘Daily’, provide a value in the ‘Recurs every’ box to specify the number of times the job schedule will repeat in days.

        • For ‘Weekly’, provide a value in the ‘Recurs every’ box to specify the number of times the job schedule will repeat in weeks. Choose the specific days on which the job schedule will run.

        • For ‘Monthly’, choose from ‘Day’ or ‘The’.

          • If you choose ‘Day’, select a date of the month for running the schedule and the number of times the schedule will repeat in months.

          • If you choose ‘The’, select a day of the week in the month to run the schedule and the number of times you want the schedule to repeat in months.

      • In the ‘Daily Frequency’ section, you can specify the frequency of occurrence of the job schedule on the day it runs:

        • Enter a time of the day you want the schedule to run in the ‘Occurs once at’ box.

        • If you choose ‘Occurs every’, specify the number of times the schedule will run on the day selected under ‘Frequency’.

    • If you choose ‘One Time’, specify a specific date to run the schedule under ‘One-time occurrence’ in the ‘Date’ box.

    • Verify the ‘Description’ provided under ‘Summary’ to check if the job schedule has been set correctly. Click ‘OK’.

  • Click ‘Next’.

• On the Select Target Servers page, choose the servers on which you need to run the maintenance plan. This page will be only displayed to the SQL Server instances that are set as master servers.

• On the Select Maintenance Tasks page, select and add various maintenance tasks to your plan. Once you select all the essential tasks to be included in the plan, click ‘Next’.

• On the Select Maintenance Task Order page, you can specify the order of execution of tasks in your database maintenance plan. Choose ‘Move Up’ or ‘Move Down’ to change the order. If you chose ‘Separate schedules for each task’ earlier, you cannot change the order of execution of tasks on this page.

• Once you are finished with this, you have the option to configure each task included in your maintenance plan. You may configure database check integrity tasks, database shrink tasks, index tasks, update statistics tasks, history cleanup tasks, backup tasks, and more.

• Next, you may specify the reporting options to allow SQL Server generate reports each time the plan executes. You can choose to save the report to a text file on the server or send an email to a user providing details of the actions performed by the plan.

Finally, you will be presented with the ‘Complete the Wizard’ page for verifying the details of the plan. Once you verify the details, click ‘Finish’. The ‘Maintenance Wizard Progress’ page will help you check the status of each action performed by the Maintenance Plan Wizard.