Hiding View All Site Content menu item in SharePoint Online

This entry assumes you are using the new SharePoint Online interface that has the Site Actions menu on the top LEFT of the screen and there is a View All Site Content menu item on that menu. The goal is to hide the View All Site Content menu item from non-admin users. Please note, this does not stop people that have a link to the page already. Keep in mind that mobile users have access to the page also, so you won’t be stopping them either. This change simply hides the menu item in the Site Action menu. You should be able to use this same technique to hide the link on the Quick Launch as well. The easiest way is to click on it in the Design view in SharePoint Designer. If you do a search in the master page for viewlsts.aspx you will that there are five references to the url for the All Content Page, so you may want to consider changing those as well if you want to get rid of those entry points as well.

1. You will need to edit (which will get you a copy) the v4.master master page that your site uses. The easiest way is to use SharePoint Designer 2010 (Free from Microsoft).
2. Do a search in your master page for MenuItem_ViewAllSiteContents. There should only be one match.
3. Locate the PermissionString=”ViewFormPages” attribute and change it to PermissionsString="ManagePermissions". The ManagePermissions should be sufficient to only include admin access to this menu item, but you may also want to consider other permission levels as well. Since this isn’t really a security measure, and really just trying to shield the average user from getting confused my limited testing of who sees the menu item was sufficient. You may want to do more testing.
4. Don’t forget to check-in and publish your changes (assuming it is enabled) so that others can see the changes. Otherwise, you will be the only one that can see the changes.

Best tool for CAML query generation

After lots of looking around, I finally found a CAML query generation tool that supports SharePoint Online authentication and Client Side Object Model api. It also works with SharePoint 2010. It is well designed, easy to use, and very powerful. At the click of a button it gives you the applicable Server Object Model code or Client Side Object Model code or even Web Service code. Unfortunately, the generated code does not work with SharePoint Online, but that is because it uses different authentication.However take a look at my post on using authenticating against SharePoint Online when using the Client Side Object Model.

Thank you to Karine Bosch for writing the CAML Designer and making it freely available AND giving good documentation on it.

Click here to learn more and download.

LINQ to SharePoint

Why use LINQ instead of CAML

• Intellisense
• Compile-time type checking
• You don’t have to know CAML which is not as well documented.
• If you know LINQ to ??? you already have a good feel for the syntax.

Supported Environments

From what I can tell (I found conflicting information on this and I could not get it to work) you can’t use spmetal (which is required for LINQ to SharePoint) on SharePoint Online. However, if you are using SharePoint 2010 (on premise) it does generate ok, so I expect the LINQ to SharePoint would work as expected.

Learn more about LINQ to SharePoint

1. Introducing Linq to SharePoint Video – Good place to start.
2. LINQ Syntax Video – Learn the basics of the syntax for general LINQ (if you don’t know it already)
3. Accessing SharePoint Data using LINQ to SharePoint

Basics

1. Create Entity Classes using SPMetal
2. Add reference to Microsoft.SharePoint.Linq.dll to your project
3. Create DataContext
4. Write Queries

Getting Started with SharePoint Online Development

Authenticating against SharePoint Online

The first place I would start is here to learn how to authenticate against SharePoint Online (O365). This will give you a good understand of what libraries are available to do the claims based authentication SharePoint Online uses. It also explains it in an easy to understand manner. Remote Authentication in SharePoint Online Using Claims-Based Authentication – This is a article, but really what you want is the code / solution you can use to connect to your SharePoint Online site and get its title. Here is the direct link to download the solution for Visual Studio. You can also check out this library instead. They both do similar authentication. These libraries are great because they make authenticating against SharePoint Online which is a MAJOR pain in the rear, much easier.

If you are doing your development on a machine that does not have SharePoint 2010 installed you will need the SharePoint Foundation 2010 Client Object Model Redistributable. This gives you among other things the Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll assemblies which you will need. If you have an installation of SharePoint 2010 on another machine you can copy these files from the c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI directory. If you have trouble, you may want to look here first for a code tweak that brings up the login screen. I did not find it necessary to do the tweak, but some people do.

As it turns out the SharePoint Online requires a bit more work to get the authentication to work than the standard example for SharePoint 2010 show. This is because SharePoint Online uses Claims-Based Authentication. For whatever reason it is not implemented and certainly is not transparent in the SharePoint Client Object Model. What I recommend is downloading the solution from above if you have not already done so since this has the Claims-Based Authentication implemented for you. You should still be able to use most samples for SharePoint 2010 regarding the CSOM (SharePoint Client Object Model), but you will need to do two things to make them work (which is related to authentication).

1. Add a reference to the ClaimsAuth (that is from the solution above).
2. In the code replace any calls to new ClientContext(url) with ClaimClientContext.GetAuthenticatedContext(url);

Once you are authenticated, the api should be the same.

Technology Overview

The SharePoint Foundation provides an api to interact with SharePoint (both SharePoint 2010 and SharePoint Online) data remotely from script that executes in the browser, Silverlight, or .NET applications. The api includes a subset of the features available in the SharePoint Object Model (Microsoft.SharePoint.dll) that you would use if you were running your code on the SharePoint Server itself (which is not an option if you are using Microsoft SharePoint Online). Each language uses the same api to make switching between them easier. Obviously, the syntax is different for each language.

Microsoft decided to make the client object model instead of constantly trying to extend the web services. The reason is that with the client object model it is very similar to the server object model that was available on the SharePoint server itself. This makes providing new features much easier and also easier for developers since there is one object model needed for both client and server (in many cases).

You can do most things you can do with the server object model. The following areas are supported by the client object model:

• Site Collections and Sites
• Lists, List Items, Views, and List Schemas
• Files and Folders
• Web, List, and List Item Property Bags
• Web Parts
• Security
• Content Types
• Site Templates and Site Collection Operations

This has some advantages over the web services that Microsoft used to require developers to use. The biggest advantage may be that it does not have the overhead associated with web services (xml bloat).

A good resource is the Client Object Model Resource Center.

A great video to get more details.

IMPORTANT: It only works with .NET 3.5 right now. It does NOT work with a .NET 4.0 project.

Do your first project.

1. Create a new .NET Command Line application (or other type if you prefer) using the 3.5 (4.0 is the default and will not work as of 2012-07-26)
2. Make sure you compiled the ClaimsAuth project that is located in the Remote Authentication in SharePoint Online Using the Client Object Model solution and reference the ClaimsAuth.dll, or just add that project to your project if you prefer to have the source code.
3. Add a reference to the Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll to your project as well. Remember, you can get that from SharePoint Foundation 2010 Client Object Model Redistributable installation or from c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI on an existing SharePoint 2010 installation.
4. Add [STAThread] attribute to the Main method.
5. Make your Main method look like this (you’ll need to change the url)
   [STAThread]
   static void Main(string[] args)
   {
       using (ClientContext clientContext = ClaimClientContext.GetAuthenticatedContext(https://yoursite.sharepoint.com/teams/site1/site2/etc))
       {
           Web site = clientContext.Web;
           clientContext.Load(site); // tell model we want that data
           clientContext.ExecuteQuery(); // actually go to SharePoint and get data
           Console.WriteLine("Title: {0}", site.Title);
       }
   }
6. I have highlighted the most important pieces that are different from what you would do in SharePoint 2010. When you run it you will see a browser object popup and then continue. That is all there is to it.

Where to go from here

Using the SharePoint Foundation 2010 Managed Client Object Model – good tutorial that covers lots of the CRUD type operations you may need to do.

You may prefer to use LINQ instead of CAML to do your queries. This may be a good place to start.

Creating a simplified Master–Detail functionality using SharePoint Designer 2010

This assumes you have two Lists. One list is the lookup table (we’ll called it FAQTopics) that you want to group by. The other list is a table (I called it FAQ List) that has the rest of the data you want to display. In this scenario I want my lookup table to show on the left side of the screen and when I click one of the items on this table I want the list on the right to update and only show the rows that are in that match. In this case, the master is the FAQTopics list and the detail is the Topics list.

If you need to be able to change the item that is selected by default or have a link to the page with a certain topic selected, I suggest you try an enhanced version of this.

• Create a new web part page
  1. Open SharePoint Designer 2010
  2. Go to Site Pages
  3. Click Web Part Page from the Pages ribbon and select the desired web part layout. Pick on that has at least a left and right web part area for our purposes since we want the list of topics on the left and the FAQs on the right.
  4. Rename the file as a desired. I called it FAQ.aspx
  5. Right-click new page and select Edit File in Advanced Mode.
• Add the FAQTopics to the page
  1. Click on one of the left web part zones
  2. Click the Insert ribbon and choose Empty Data View from the Data View button. This will create a DataFormWebPart. This will work best since it gives us access to the html directly and allows us to easily customize the selected row styles.
  3. Click the Click here to select a data source link in the middle of the DataFormWebPart and select FAQTopics (you may have called it something different). The screen won’t change much, but open the Data Sources Details panel (if it is not all ready visible). Here you will see a list of columns from the FAQTopics.
  4. Drag over the column you want to display in the FAQTopics list. In my case, I used Title. Please, note, this field should uniquely identify the row.
  5. On the Options ribbon, set any filters or sorting you may need by clicking the Filter and Sort & Group buttons.
  6. You can now test what we have done. Click the Home ribbon and then click the Preview in Browser button. This will open your page up in the browser. It should show a list of rows from your FAQTopics table.
• Add the FAQ List to the page
  1. Click on one of the right web part zones
  2. Click the Insert ribbon and choose FAQ List from the Data View button. This will create a XsltListViewWebPart. Note, we didn’t create an Empty Data View (like we did for the FAQTopics) and instead selected the FAQ List. This causes SharePoint Designer to use the default XsltListViewWebPart. You could use a DataFormWebPart like we did for the FAQTopics, but you may lose some functionality. For example, if you want to use the Rating stars to allow users to rate the FAQs, they don’t really work when you use the DataFormWebPart and instead you just get the number stored in the database. So, depending on your needs, pick the appropriate web part. The rest of the steps are the same regardless.
  3. You can now test what we have done. Click the Home ribbon and then click the Preview in Browser button. This will open your page up in the browser. It should show a list of rows from your FAQTopics table and also have the list of FAQs on the right side. They are independent of each other right now, but we’ll connect them next.
• Connect the two lists
  1. Right-click anywhere in the FAQTopics web part on the left and choose Add Connection.
  2. Choose Send Row of Data To (the default).
  3. Click the Next button.
  4. Click Connect to Web Part on this page (the default).
  5. Click the Next button.
  6. Choose FAQ List from the Target Web Part drop down list.
  7. Choose Get Filter Values From from the Target action drop down list.
  8. On the right set of columns (called Inputs to FAQ List) in the list of columns scroll down to the column you want to group by and will match the text in the FAQTopics. In my case the column is called Topic.
  9. Click the column (it shows as <none> initially) to the left of the Topic column. You will notice that <none> turns into a drop down list. Select the matching column from the list. In my case, I selected Title.
  10. Click the Next button.
  11. Select Title from the Create a hyperlink on drop down list.
  12. Check the Indicate current selection using checkbox.
  13. Click the Modify button and select the column that uniquely identifies the row. In my case, this is Title.
  14. Click the Next button.
  15. Click the Finish button.
  16. You can now test what we have done. Click the Home ribbon and then click the Preview in Browser button. This will open your page up in the browser. It should the two lists like before, except this time the first item in the FAQTopics (the one on the left) should be bold (the default style for the currently selected row). You can each row and the list on the right side (FAQ List) should update to show only the rows that match the selected FAQTopic.
• Tweak the UI (optional)
  1. This step is totally optional if you are happy with the default styles, etc of the topics on the left.
  2. Do a search for bold in FAQ4.aspx. You will find a tag like this:
     
     <xsl:attribute name="style">
          <xsl:if test="$CurrentRowKey = $dvt_curselkey">font-weight: bold;</xsl:if>
     </xsl:attribute>
     
     This adds the style=”font-weight:bold:” to the link tag. You can modify this or if you prefer, you can add a css class reference by adding the following lines after the lines for the style attribute.
     
     <xsl:attribute name="class">
          <xsl:if test="$CurrentRowKey = $dvt_curselkey">selected</xsl:if>
     </xsl:attribute>
     
     In this case if the row is selected we the link tag to have class=”selected” to it. Then in your stylesheet you create a .selected style. The name (selected) is not important, but it does need to match what you have in your stylesheet.

Hiding column headers on SharePoint lists

I found that sometimes I don’t want to see the column headers on a SharePoint list. I ran into such an instance when I was putting together a FAQ which was implemented as a Custom List in SharePoint 2010. I create a view that grouped my FAQs by the Topic field. SharePoint give a fairly nice view, but in this case I think the column headers are a bit confusing to the end users.

It makes even more sense, if you don’t have tabular data for each row or two up in my case.

The the goal here, is to remove the column header (denoted by the red arrows) so that the page looks something more like this:

To do this you will need SharePoint Designer 2010 (Freely available from Microsoft). Once you have SharePoint Designer 2010 installed (you may need to restart before it will open the page properly) follow the steps below.

1. Add the list to a page as a web part. In this case, my list is called FAQ List, so I added there was a web part of type FAQ List.
2. Go to Site Actions | Edit In SharePoint Designer. The page will load in SharePoint Designer 2010.
3. Click one of column headers. You will notice that the Table ribbon is now available.
4. Click the Select button | Select Table button.
5. On the same ribbon, there is a Shading button. Click it and choose a color (for example red). This will change the background of the headers to red. This works because the headers are actually in nested table. By changing the background of the table, we force the html for that table to be generated so that it can be extended.
6. Find the style="background-color: #FF0000" (assuming you chose red).
7. Change the style attribute to be style=”display:none;”. This will cause it to still be sent to the browser, but it will ignore it. Notice, the background-color is not set anymore, but it doesn’t hurt anything because the table is not displayed and does not take up the height and width it did on the page either. It is effectively gone from the users view now.
8. Save your changes in SharePoint Designer 2010 and refresh the page in the browser to see verify that your goal was accomplished.

What not to do

The first time I tried to do this I went into SharePoint Designer 2010 and clicked the column headers and just started hitting the delete key. As expected, the column headers disappeared because I deleted them. The problem I saw with this is that my Ratings control no longer would allow the user to click the stars to rate each of the FAQs. It would no error or give a javascript error depending on if error are turned off in the browser. I assume this has something to do with a missing javascript file that didn’t get included, but I wasn’t going to track it down. Instead, I thought to myself, why not just trick SharePoint into loading the proper files and then on the browser side just hide the column headers since it really is just a cosmetic change that I need. As far as I can tell, the solution outlined here works much better and is actually quite easy to undo if you want to also.

A backup strategy for MS SQL Server

In this post I want to talk about the basics of a backup plan for SQL Server databases.The question is often, "What do I need to keep and for how long when I backup my SQL Server databases?"

The long answer is it depends on how large the database is, what type of database, how large are the maintenance windows, etc. However, for most databases you will want to minimize your risk for losing data. The bottom line is that you need to know how the different types of backups in SQL Server work. I'll try to explain this.

First some general thoughts. You should always backup to a disk that is different from the one the thing (database, log file, etc) that you are backing up. A backup to tape or another backup of the file system is advisable as well. This way in the event of a fire, natural disaster, etc you will have at least something to work with. Please note that you cannot backup the actual database file in the system, you have to use SQL Server 

The most important thing you can do is a periodic full backup of your database. It is best to do your system databases nightly since they are very quick. This will give you your jobs, etc. You should do your user databases during a time of low usage because this can take a while. Typically you would do a full backup once a week.  If that is the only thing you do you will lose a week of data if your database becomes corrupted, accidentally deleted, hardware failure, etc. In most cases this is not good enough, so we need to reduce the window of risk.

To reduce the window of risk, we could do a full backup everyday, but typically there is not a maintenance window that allows the time or resources to do this. So, this means we need a faster way to backup. This can be done by doing a differential backup in SQL Server. It is typical to do a differential backup each day of the week. This means that each time you do a differential backup you will be backing all the changes to the database since the last FULL backup. This also means that if you are doing your full backs weekly that the day before your full backup your differential backup will potentially be quite large because each day after the full backup the differential backup keeps getting larger and larger each day. The size grows because each differential includes the previous differentials data as well.

With the weekly full backup and the daily differential backs running we still have at most a day of data we could lose. We also have the problem that our log file itself will grow indefinitely (assuming full or bulk-logged recovery mode for the database is selected). Once solution is to periodically backup and truncate the log file manually after a full backup. This is not an optimal solution if you have many databases. The highly recommended method for solving that problem AND decreasing your window of risk is to do hourly log file backups. This action will save only the changes since the last log file backup which is one hour of changes in this case. The log file backup is extremely fast in most cases. The more often you run it the faster it is (for the most part). So at the end of the day you will have 24 log file backs and your log file will be truncated as well (pending a bunch of conditions that are outside the scope of this entry).

Now that we have a weekly full backup, a daily differential backup, and an hourly log backup our window of risk has been reduced to 1 hour max. This is great, but we still don't know what we need to keep and for how long in order to make a restore of our database. The answer to this pretty simple if you remember the following:

1. FULL BACKUPS: You need to keep a full backup until you have made another full backup AND verified it.
2. DIFFERENTIAL BACKUPS: You only need to keep the latest differential backup and it can be deleted when you do a full backup. You don't need differential backup files if you have log backups from the time of the full backup to the time the differential backup was last done. This means that you don't really need to ever do a differential backup if you keep all your log backups between each full backup. This actually recommended by some DBAs because it really simplifies the backup strategy.
3. LOG FILE BACKUPS: You need to keep log files for the time period that you don't have a differential or full backup. For instance, if you have a full backup and your last backup, you only need the log backups that occurred since the last differential or full backup. This would mean that you don't need to at least 24 hours of log backups if you do a differential backup every 24 hours.

So, as you can see there is no definite answer, but it is easy enough to figure out given your backup strategy.

One thing that was not obvious the first time I scheduled backups is that it is probably best to do a differential backup EVERYDAY including the day you do the full backup or keep more than 24 hours of log backups. Why? Consider, you do a differential backup on everyday except Sunday because that is when you do you full backup. Full backups take longer and thus I usually schedule them earlier in the day than I do for the differential. This difference in time creates a period where we are not protected and unable to restore using our log backups. The additional risk is that let's say your full backup on Sunday starts at 10am and your next differential backup is on Monday at 6pm. There are more than 24 hours between the two backups, but we are only keeping 24 hours of log backups. This means the log files between 10am and 6pm will be deleted. Since we are missing those log backups , it makes the rest of the log backups not very useful either. That means that between Sunday 10am and Monday 6pm we are not longer getting the benefit of the log backups and instead have opened our window of risk back to 1 day and on a day that we are doing extra maintenance most likely. This is not a good scenario. The simplest solution seems to do the differential backup on Sunday as well (as the full backup we did that morning). The backup will be fast and small and we will be safe. The other option would be to keep 32 hours of log backups to cover that period. The downside is that we will have to keep an extra 8 hours of log files throughout the week or make a special change just for that day of the week, and that is just messy.

I hope this helps. If you are looking for a backup implementation that is both quick and easy to use, I recommend reading my other entry on Implementing a backup strategy in MS SQL Server.

Implementing a backup strategy on MS SQL Server

I have implemented a new backup strategy on a server using the SQL Server Maintenance Solution by Ola Hallengren. It is very flexible and very robust. There are lots of advantages to using it instead of the built-in maintenance plans.

Some of the reasons are:

• Easy Deployment to multiple servers using sql scripts.
• Have control of the name of the sql jobs.
•  Wild card inclusion/exclusion of databases
• Handles a differential or log back up if full has not been executed as in when a database is created.
• Totally customizable
• Very robust options
• Smart index maintenance by only re-indexing or organizing indexes that need it.
• Better clean up of old files
• All details of every command executed is stored in a SQL table and text file system. Job history also has job level details.
• Easy to diagnose issues due to detailed logging of commands.
• If one thing in job fails, the other commands in the job are still executed. This means if one database can’t be backed up the rest still will be.
• Used by tons of corporations around the world.
• Works with multiple version of SQL Server
•  Has received Gold Community Choice award and Silver Editor’s Best Choice award (both in 2011).
• Handle Large databases well.

The one that I like most is ease of deployment. Since it is based on stored procs and sql jobs all that needs to be done is run a sql file. The default configuration is nearly perfect. The jobs just need to be scheduled for the most part.

There is a good video (about an hour) that reviews it in detail.

It is an open source project that has a open ended license. The source and documentation can be found at: http://ola.hallengren.com

Delete all alerts in SharePoint

Whenever I setup a development copy of SharePoint and want to do some testing on a content database I copied from production, I don’t want have the alerts still enabled. I have experimented with enabling and disabling via script, but it never seemed to work quite how I expected. Then I got the idea to just delete them since this is development copy of the content database anyway.

The PowerShell script below MUST be ran as ADMINISTRATOR on one of the front end servers for SharePoint 2010. If you don’t Run As Administrator, then you will get an error that you don’t have permissions to access the farm.

The script is theoretically simple. It goes to the SharePoint url you give it, opens up the web application, gets all the site collections. For each site collection it gets all the webs in that site collection (this is the same as recursively traversing the SharePoint tree of webs, but much wrapped up nicely in the AllWebs property). Then we loop through each web and get all the Webs. We then get all the ids of the alerts we want to delete. Next we delete each alert. If one fails because of a user not existing anymore, we put a valid user in its place and try again. This script should only take a short time to run (like seconds or few minutes, not hours).

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

$numWebs = 0
$numDeleted = 0;

$sp = Get-SPWebApplication ("http://mySharePoint”)
foreach ($site in $sp.Sites)
{
    foreach ($web in $site.AllWebs)
    {
        $numWebs++;
       
        $alertIds = @();
        foreach ($alert in $web.Alerts)
        {
            $alertIds += $alert.ID;
            $numDeleted++;   
        }
       
        foreach ($alertId in $alertIds)
        {
            try
            {
                $web.Alerts.Delete($alertId);
            }
            catch [system.exception]
            {
           
                # user no longer exists. Perhaps not migrated properly when changed domains, etc.
                # In any case, we just need to update the alert so that it points to a valid user,
                # then we can delete it.
                if ($_.Exception.Message.Equals("User cannot be found."))
                {
                    $alertToUpdate = $web.Alerts[$alertId];
                    $alertToUpdate.User = $web.SiteUsers[0];
                    $alertToUpdate.Update();
                   
                    #try to delete again, but this time we have a valid user
                    $web.Alerts.Delete($alertId);
                   
                }
            }
        }
    }
}

Write-Host "Num of Webs checked: " + $numWebs;
Write-Host "Num of Alerts deleted: " + $numDeleted;

To delete all alerts for a particular person, you may want to try this script. It was also the starting point for my script here.

SQL Server Maintenance Plan Best Practices

Let me start by saying most of this content is copied directly from Brad's Sure Guide to SQL Server Maintenance Plans. It is an excellent book, I highly recommend reading it if you have the time or referring to it if you have a situation that is not covered here. It is a good reference for particular topics as well.  The purpose of this entry is just to cover the basics that I tend to need every time I setup a Maintenance Plan in MS SQL Server. I hope you find it useful as well. Keep in mind, these suggests assume you have a maintenance window or off peak period when maintenance can be performed. These recommendations may not be good enough for business critical applications that must be available 24/7, but should be good for most other situations. If you don’t know the which options to pick for each task, the Brad’s eBook covers each task in great detail. I have no connection to Brad, but I did find his book very useful.

Maintenance Plan Tasks Overview

Check Database Integrity

The Check Database Integrity task runs DBCC CHECKDB against selected databases and performs an internal consistency check on them to see if there are any problems with their integrity. While this task is very resource intensive, it is critical that you perform it on a regular basis, to ensure that your databases aren't damaged.

Shrink Database

Never use the Shrink Database task. Is that clear enough advice? While we will discuss why it is not a good idea to automatically shrink a database in Chapter 6, the point to keep in mind is that, if you ever need to shrink a database, it should be done manually.

Rebuild Index

The Rebuild Index task runs the ALTER INDEX statement with the REBUILD option on indexes in the selected databases, by physically rebuilding indexes from scratch. This removes index fragmentation and updates statistics at the same time. If you use this option, you do not want to run the Reorganize Index or the Update Statistics task, as doing so would be redundant.

Reorganize Index

The Reorganize Index task runs the ALTER INDEX statement with the REORGANIZE option on the indexes in the selected databases. This task helps to remove index fragmentation, but does not update index and column statistics. If you use this option to remove index fragmentation, then you will also need to run the Update Statistics task as part of the same Maintenance Plan. In addition, you won't need to run the Rebuild Index task, as the use of Reorganize Index task (followed by the Update Statistics task) renders redundant the Rebuild Index task.

Update Statistics

The Update Statistics task runs the sp_updatestats system stored procedure against the tables of the selected databases, updating index and column statistics. It is normally run after the Reorganize Index task is run. Don't run it after running the Rebuild Index task, as the Rebuild Index task performs this same task automatically.

Execute SQL Server Agent Job

The Execute SQL Server Agent Job task allows you to select SQL Server Agent jobs (ones you have previously created), and to execute them as part of a Maintenance Plan. This feature offers you additional flexibility when performing database maintenance using the Maintenance Plan Wizard.

History Cleanup

The History Cleanup task deletes historical data from the msdb database, including historical data regarding backup and restore, SQL Server Agent and Maintenance Plans.
If you don’t perform this task periodically then, over time, the msdb database can grow very large.

Back Up Database (Full)

The Back Up Database (Full) task executes the BACKUP DATABASE statement and creates a full backup of the database. You will probably want to run this task daily against your system and production databases. In most cases, the databases you will be backing up with this task use the Full Recovery model, and you will also want to run the Backup Database (Transaction Log) task as part of your Maintenance Plan.

Back Up Database (Differential)

The Back Up Database (Differential) task executes the BACKUP DATABASE statement using the DIFFERENTIAL option. This task should only be used if you need to create differential backups.

Backup Database (Transaction Log)

The Backup Database (Transaction Log) task executes the BACKUP LOG statement, and, in most cases, should be part of any Maintenance Plan that uses the Back Up Database (Full) task. It is a common practice to run this task every hour or so, depending upon your needs.

Maintenance Cleanup Task

The Maintenance Cleanup task is problematic as it does not really do what it is supposed to do. In theory, it is designed to delete older backup files (BAK and TRN), along with older Maintenance Plan text file reports (TXT) files that you no longer need. The problem is that it can only delete one type of file at a time within a single Maintenance Plan. For example, if you choose to delete older BAK files, it won't delete older TRN or TXT files; if you choose to delete older TRN files, it won't delete older BAK or TXT files.
What we really need is a task that performs all three inside the same Maintenance Plan, but we don't have it. So, what is the best way to delete old BAK, TRN, and TXT files? One way is to use the Maintenance Plan Designer, which allows you to create three separate subplans that will take care of deleting each of these three kinds of files within a single Maintenance Plan (see Chapter 17). However, if you want to use the Maintenance Plan Wizard exclusively to delete all three file types, you must create three different plans to accomplish your goal.

Order to execute tasks

Here are the general guidelines or thoughts as to what should be done in what order.

• Logical Task Ordering. A task such as Clean Up History can be performed at any point in the plan but, for other tasks, there is a certain logical order in which they should be performed.
• It makes sense to start the Maintenance Plan with the Check Database Integrity task, because there is no point in running the rest of the maintenance tasks if the integrity of your database is in question.
• The Back Up Database (Full) should come before the Backup Database (Transaction Log) task as we can't perform a transaction log backup before we perform a full database backup. If we were to try, we would get an error.
• If a Rebuild Index task (or the Reorganize Index and Update Statistics tasks) is performed during the same maintenance window as the Back Up Database (Full) task, then I always perform the Rebuild Index task first. Why? This way, should I need to perform a restore of the backup, it will have its indexes defragmented and will be ready for production.
• The Maintenance Cleanup task, if selected (see previous discussion), should be performed only after the Back Up Database (Full) has been completed. This way, you can ensure that a good backup has been made before deleting any older backups.
• Task Scheduling. If you choose Separate schedules for each task, the scheduling of these tasks (covered later) will determine the actual order in which they occur. For example, it is possible to schedule the tasks in such a way that the Backup Database (Transaction Log) task runs before the first ever Back Up Database (Full) task for a given database, although this would cause the Maintenance Plan to fail. The key thing to remember is that the logical task order you select in Figure 3.7 is not absolute, and that it can be overridden by the schedules you set for each one.
• Some would argue that you should optimize your indexes THEN check the database integrity because sometimes optimizing indexes will correct corruption issues.If you want to go this route, then in the example below you would move the Rebuild Index… line to be first. After doing this, the second step would be check database integrity

Now that we have a basic idea of what the tasks are, we now need to decide what order to execute them. If you were to write this as code this is what it could look like

if (Check Database Integrity succeeds)
{
    Rebuild Index OR (Reorganize Index and Update Statistics)
   
    Back Up Database (Full) or Back Up Database (Differential)
    if (Back up Database (Full) succeeds)
    {
        Backup Database (Transaction Log)
        Maintenance Cleanup Task
    }

    Shrink Database (never)
}

Run these anytime:
    Execute SQL Server Agent Job
    History Cleanup

Controlling order of the tasks

When you start the Maintenance Plan Wizard it will ask you if you want separate schedules for each task or single schedule for the entire plan. This is a big decision because if you choose separate schedules for each task you will get a different job under SQL Agent | Jobs and if you choose a single schedule for all the tasks then you get one job in SQL Agent | Jobs. If you choose single schedule option then the wizard will ask you what order you want them to execute. If you choose separate schedules then you control the order based on the date you schedule the tasks.

Scheduling Consideration

No matter if you choose one or multiple schedules for your tasks you will want to do these operations during off peak hours if at all possible. You can run some tasks in parallel or overlap, while others should not be. For example, the Check Database Integrity task and Rebuild Index task should not be executed at the same time since they are both resource intensive. If you do, you may find your server becoming to slow.

If you choose to have separate schedules for each task then you will need to take an educated guess as to how long each task will take and set the schedule accordingly. The tricky part is that the same task doesn’t always take the same amount of time to run each time. So, you will want to watch it over time to see if it needs to be adjusted to accommodate the longer durations. You can get these durations by going to the View History on the jobs.

How often to run jobs

This will vary depending on your needs, but here are some common guidelines for how often to schedule tasks.

User Databases

• Check Database Integrity – DAILY (or at least WEEKLY)
• Rebuild Index  WEEKLY (DAILY is better). Choose this instead Reorganize Index and Update Statistics if you have enough time
• Reorganize Index and Update Statistics WEEKLY (DAILY is better) Only do this if no time for Rebuild Index
• Back Up Database (Full) WEEKLY
• Back Up Database (Differential) EVERYDAY EXCEPT FULL BACKUP DAY
• Backup Database (Transaction Log) – HOURLY
• Maintenance Cleanup Task – WEEKLY?
• Shrink Database DO NOT SCHEDULE – ON DEMAND ONLY
• Execute SQL Server Agent Job – DEPENDS
• History Cleanup – WEEKLY (or other)

References: Brad's Sure Guid to SQL Server Maintenance Plans (Free eBook)