My experiments with Maintenance Plans in SQL Server Denali

For sometime now everyday I used to think “Today I will spend some time on Denali”. Due to various reasons it would somehow get missed in my To-do list. I had downloaded and installed SQL Server Denali CTP1 as soon as it was available for download.

When it comes to comparing features and reporting bugs in SQL Server as far I have seen no one can beat Aaron Bertrand (blog | twitter). He had already posted quite a number of articles on the new features and lot of other stuff about Denali here.

Maintenance Planned!

Today I decided that I will give it a shot. I finally fired up Windows Server 2008 on my VM and started SSMS. Connected to the instance and now what? Since in the SQL Server forums many of the questions revolve around Database Backups, Maintenance Plans was the first thing that came to my mind. Maintenance Plan is one of the features in SQL Server which has been constantly improving.

Finally I was there looking at the Maintenance Plans node. Most noticeable difference was that there was no New Maintenace Plan option in the context menu.

I was left with only the Maintenance Plan Wizard to create the Maintenance Plans. There was not much change in the screens, all looked same as the one in SQL Server 2008.

Now the Maintenance Plan is created, the next step is to check how it looks like while editing and what new features are added there. Right clicked on the Maintenance Plan and click Modify.

Modify? Where is Modify? Opened my small eyes wide open and searched, no Modify! The next destination was obviously SQL Server Denali CTP1 Release Notes. Here is the Note about Maintenance Plans.

This is the reason why the New Maintenance Plan and Modify options were missing in the Maintenance Plans. The only option to Edit the Maintenance Plans is to use the Business Intelligence Development Studio (BIDS). So, opened up a new Integration Services Project in BIDS and imported the Maintenance Plan by right clicking on SSIS Packages and Add existing package.

Double clicking on the Tasks again did not open the Task Designer due to the reasons mentioned earlier.

As of now the only option to edit the Maintenance Plan taks is to make use of the Properties tab for each task as in the below screenshot.

It was indeed a journey on a bumpy road. Before I could explore any further, my phone started ringing. It was a call from the office, “Pradeep, some issues while changing the service account….“. My day had just begun. Shut the VM quickly to hop on to the call.