3 Replies Latest reply on Feb 27, 2012 9:06 AM by dmease729

    Problems noted during backup configuration and test (Agent XPs and SQL error 22022)

    dmease729

      Hi All,

       

      Bear with me here - just looking for more information on the below :-)

       

      cheers!

       

      Background information
      ======================

      Appliance:3100
      OS: Win2k3 enterprise SP2
      MVM version: 7.0.4
      SQL Server version: 2005 (standard shipped with appliance)

      From MVM700 Product Guide, p92:  To back up the database using SQL Server Management Studio, go to the MSDN Library at http://msdn2.microsoft.com/en-us/library/ms187510.aspx

      As we want the backup process to be automated, there is a link on the above page entitled "How to: Start the Maintenance Plan Wizard (SQL Server Management Studio)", which pointed at using this wizard to set up the required scheduled tasks.

      The first problem was that when I tried to start the wizard, I received the following error:

      "cannot show requested dialog
      unable to execute requested command
      'agent XPs' component is turned off as part of the security configuration for this server.  A system administrator can enable the use of 'Agent XPs' by using sp_configure.  For more information about enabling 'Agent XPs', see 'surface area configuration' in SQL server books online.  Microsoft.sqlserver.management.maintenanceplanwizard"

      After digging around I came across http://technet.microsoft.com/en-us/library/ms178127.aspx, that advised on how to enable 'Agent XPs'.  This was followed and the wizard worked.

      After the maintenance plan was set up, I tried to execute it manually, and received the SQL error 22022.  Research pointed to the service SQL Server Agent not being started.  Startup type for this service set to manual, so started and run maintenance plan successfully.  I have now set this service startup type to automatic.


      Questions:
      ==========

      1) Has anybody else experience these or similar issues when trying to set up scheduled backups?
      2) Given the fact that automated backups are probably preferred by many, is there any other way of setting up this which would mean the above tweaks did not have to be made?
      3) If the above tweaks have to be made, would it not be helpful to note this in the MVM documentation, or at least in the Knowledgebase?
      4) Could there be any impact on MVM itself with the above tweaks?

        • 1. Re: Problems noted during backup configuration and test (Agent XPs and SQL error 22022)

          hi dmease,

           

          I just tested this out on one of my appliances, and had the same issues when I tried to follow the Maintenance Plan wizard

           

          I'm not sure of the issues you mentioned above, and would like to research it further.  Can you open an SR (PM me the SR#), so I can get the work into my queue to get done. 

           

          Thanks!
          Cathy

          • 2. Re: Problems noted during backup configuration and test (Agent XPs and SQL error 22022)

            Dmease,

             

               Just thought I would weigh in here.  Part of the appliance setup is hardening and disabling of certain items which could be compromised.  So to answer your questions:

             

            1. Yes, any appliance will be setup in this same manner

            2. The changes to the agent service would be the most preferred method, but it does alter the hardening of the appliance.  Other automation solutions would require installation of client software onto the appliance which is not recommended.

            3.  As this "breaks/changes" hardening I am not sure we would want to add it to the standard documentation, there could possibly be a KB article written for registered users.  (This is the question getting Cathy an SR for would be most beneficial)

            4.  Impact should be minimal, although if you have a number of transactions occuring at the same time as your scheduled backup this could cause deadlocks, slowness, or other unusual behavior.  My suggestion would be if you are scheduling automatic backups, to do so when you have the least amount of scans occuring.

             

            Submitting a SR and getting it further researched in support is never a bad idea, hope this helps.

             

            Thanks,

            Dave

            • 3. Re: Problems noted during backup configuration and test (Agent XPs and SQL error 22022)
              dmease729

              Quick update on this.  https://kc.mcafee.com/corporate/index?page=content&id=KB73630 (available to non-registered, I just checked)

               

              Note that the KB does say this is a supported configuration, but Daves comments above regarding scheduled backups should be followed!  Cheers for all the assistance with this Cathy!