На главную
ПРАВИЛА FAQ Помощь Участники Календарь Избранное DigiMania RSS
msm.ru
Модераторы: maxim84_, juice
  
> SQL Server Job Monitoring from an Asp.Net Application, How to monitor SQL Server Job. Fast problem solution. SQL Server Agent / MS SQL Server 2005-2008
    Здравствуйте, уважаемые читатели :)
    Столкнулся с проблемой, решение которой хочу выложить на всеобщее обозрение. Обсуждение, конечно-же приветствуется.

    Итак, задача:
    "Предоставить пользователю управлять SQL Server Job при помощи веб-странички. Пользователь должен иметь возможность остановить/запустить SQL Job а также, видеть текущий статус ее выполнения"

    Если вам знакома эта тема, то абзац можно смело пропустить, я здесь лишь приведу общие сведения об SQL Server Agent and SQL Server Jobs.
    Что такое SQL Server Agent и зачем он нужен.
    Цитата
    SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company servers every weekday after hours, you can automate this task. Schedule the backup to run after 22:00 Monday through Friday; if the backup encounters a problem, SQL Server Agent can record the event and notify you.

    Т.е. SQL Server предоставляет своего рода шедулер (реализован в виде Windows Service), который может выполнять какие-либо операции в "фоновом" режиме. Пример: автоматическое создание резервных копий базы.
    Эта функциональность особенно полезна для выполнения административных функций, но в некоторых случаях требуется взаимодействие пользователя приложения с SQL Server Jobs.

    Приведу пример из жизни:
    Есть довольно крупное веб-приложение, с помощью которого пользователи по всему Миру вводят определенную финансовую информацию. Раз в какое-то время эта информация должна быть
    предоставлена в виде отчетов биг боссам корпорации. За построение отчетов отвечает отдельный сервер (IMB Cognos), который получает данные из нашего приложения (не будем вдаваться
    в подробности, как именно эти данные приходят). для того что бы подготовить данные для отчетов - необходимо выполнить очень-очень накладные вычислительные операции + преобразовать их
    структуру. Здесь на помощь приходит SQL Server Agent, который автоматически запускает Job, код которой и выполняет столь дорогостоящую операцию. И все было отлично пока клиенту не
    припекло запускать Job в любое время при помощи веб-странички (ну логинься ты на сервак, открывай Management Studio и запускай SQL Job. Но нет, такой вариант по ряду причин не подошел)

    Т.е. понадобилось каким-то образом получить программный доступ к SQL Job - смотреть ее статус и уметь его изменять. Казалось-бы, задача простенькая, но не все так радужно.

    На сегодняшний момент мне известно 2 способа как это можно сделать:
    1. Восползовавшись SQL Server Management Objects (SMO) MSDN SMO Page
    2. При помощи специальных(системных) хранимых процедур, которые присутствуют в системной базе "msdb"

    С первым пунктом у меня не срослось. Даже не хочется описывать все этапы моих мучений, но SMO не решили моей проблемы (несмотря на потенциальную возможность управления службами)
    и пришлось прибегнуть ко второму варианту. Если интересно - что именно не получилось, пишите - обсудим в каментах.

    Второй метод с хранимыми поцедурами выглядит вообще простецким. Просто вызываешь хранимку и получаешь полную информацию о Job'e. Вызвал другую - запустил/остановил, и т.д.
    Но, не тут-то было. Возникло 2 проблемы:
    1. Необходимо коннектиться к базе "msdb" (а приложение, естесственно, работает с другой базой)
    2. Доступ и права на выполнение этих хранимок должны быть правильно настроены.


    Итак, проблема №1. Подход первый.
    В моем приложении используется Linq-To-SQL в качестве ORM. Архитектура приложения такова, что строка подключения впрыскивется один раз в специально отведенном месте.
    Затем, DataContext впрыскивается при помощи Unity в репозитории и там используется. Время жизни DataContext'a - один DataContext на один Web Request (это очень важно).

    Немного кода:
    ExpandedWrap disabled
      public class SqlJobRepository : ISqlJobRepository {
              private readonly DataContext _context;
              //Каким-то образом, получаем контекст
              public SqlJobRepository(DataContext context){
                  if (context == null) throw new ArgumentNullException("context");
                  _context = context;
              }
       
              //Метод по запуску Job'ы :)
              public void RunJob(string jobName){
                  //Берем текущую коннекшн-стрингу у существующего контекста
                  //(само подключение использовать не получится - нужно новое создавать) см. ниже.
                  using(SqlConnection connection = new SqlConnection(_context.Connection.ConnectionString)){
                      connection.Open();
                      //Ахтунг! Меняем базу!
                      connection.ChangeDatabase("msdb"); //именно в msdb живут наши хранимки
                      //Не обращайте пока внимания на эту проверку
                      if(JobIsStarted(jobName, connection)){
                          throw new InvalidOperationException(string.Format("The job {0} has already been started", jobName));
                      }
                      //Метод GetJobStartupCommand создает и инициализирует SQL Command для запуска Job
                      SqlCommand startupCommand = GetJobStartupCommand(jobName, connection);
                      startupCommand.ExecuteNonQuery();
                      //get result of execution 0 == success
                      if((int)startupCommand.Parameters["RETURN_VALUE"].Value != 0){
                          throw new Exception(string.Format("Error during {0} job startup", jobName));
                      }
                  }
              }
            
              //sp_start_job - имя хранимки которую нужно вызвать для запуска
              private static SqlCommand GetJobStartupCommand(string jobName, SqlConnection connection){
                  if (connection == null) throw new ArgumentNullException("connection");
                  SqlCommand cmd = new SqlCommand("sp_start_job", connection){CommandType = CommandType.StoredProcedure};
                  SqlParameter parameter = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
                  parameter.Direction = ParameterDirection.ReturnValue;
                  parameter = cmd.Parameters.Add("@job_name", SqlDbType.NVarChar, 50);
                  parameter.Value = jobName;
                  return cmd;
              }
      }

    Внимание! я не рекомендую использовать приведенный выше пример в качестве рабочего. В итоге я пришел к другому решению.

    По-идее, думаю, все должно работать. Подключился к базе с текущей строкой подключения, поменял базу, вызвал хранимку. Каково же было мое удивление
    когда приведенный выше код работал "через раз" и начала происходить магия при вызове connection.Open();
    Дело в том, что если вы храните пароль в строке подключения (в файле web.config, например) то вы не сможете получить пароль после того как подключение было
    хоть раз использовано. Пароль "где-то" кешируется (доказано при помощи рефлектора) и, пытаясь подключиться еще раз, SQL Server отказывает в доступе.

    Т.е. вариант с созданием нового подключения не канает - нужно использовать одну и ту-же базу данных что и все остально приложение. Плюс ко всему,
    использование Ado.Net выглядит нелепо если в приложении уже используется Linq-To-Sql. Т.е. Нужно каким-то образом добавить в свою базу вызов нужных системных
    хранимых процедур. Сделал я это вот как:
    ExpandedWrap disabled
      USE ["Имя вашей базы"] /* Имя вашей базы - ключевой момент */
      GO
       
      /****** Object:  StoredProcedure [dbo].[wc_sp_start_job]    Script Date: 10/28/2010 10:28:13 ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
       
      CREATE PROCEDURE [dbo].[wc_sp_start_job] /* Процедура старта */
        @job_name    sysname          = NULL,
        @job_id      UNIQUEIDENTIFIER = NULL,
        @error_flag  INT              = 1,    -- Set to 0 to suppress the error from sp_sqlagent_notify if SQLServerAgent is not running
        @server_name sysname          = NULL, -- The specific target server to start the [multi-server] job on
        @step_name   sysname          = NULL, -- The name of the job step to start execution with [for use with a local job only]
        @output_flag INT              = 1     -- Set to 0 to suppress the success message
       
      AS
      BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;
       
          DECLARE @return_value int
       
          EXEC    @return_value = msdb.dbo.sp_start_job
          @job_name, @job_id
          
          return (@return_value)
          
      END
       
      /****** Object:  StoredProcedure [dbo].[wc_sp_help_job]    Script Date: 10/28/2010 10:26:16 ******/
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
       
      CREATE PROCEDURE [dbo].[wc_sp_help_job] /* - собственно, называйте хранимку как хотите :) */
        @job_id                     UNIQUEIDENTIFIER = NULL,  -- If provided should NOT also provide job_name
        @job_name                   sysname          = NULL,  -- If provided should NOT also provide job_id
        @job_aspect                 VARCHAR(9)       = NULL,  -- JOB, STEPS, SCEDULES, TARGETS or ALL
        -- Job set parameters
        @job_type                   VARCHAR(12)      = NULL,  -- LOCAL or MULTI-SERVER
        @owner_login_name           sysname          = NULL,
        @subsystem                  NVARCHAR(40)     = NULL,
        @category_name              sysname          = NULL,
        @enabled                    TINYINT          = NULL,
        @execution_status           INT              = NULL,  -- 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions
        @date_comparator            CHAR(1)          = NULL,  -- >, < or =
        @date_created               DATETIME         = NULL,
        @date_last_modified         DATETIME         = NULL,
        @description                NVARCHAR(512)    = NULL   -- We do a LIKE on this so it can include wildcards
       
      AS
      BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;
       
              DECLARE @return_value int
          
              /*sp_help_job - Вот она, родимая, и монторит статус Job(s) :)*/
          EXECUTE  @return_value = msdb.dbo.sp_help_job @job_id, @job_name
          
          return (@return_value)
      END

    Т.е. по-сути дела, написал оболочки в которые инкапсулировал вызов системных хранимок. При этом "оболочки" живут в моей пользовательской базе.
    Что делаю дальше: добавляю эти хранимки-"оболочки" :) в дата контекст и спокойно их вызываю. При этом получается такойй вот красивый и лаконичный код:

    ExpandedWrap disabled
          public class SqlJobRepository : ISqlJobRepository {        
              private readonly IStoredProcedures _storedProcs; //Инкапсулирует вызов хранимок с помощью DataContext'a
       
              public SqlJobRepository(IStoredProcedures storedProcs){
                  if (storedProcs == null) throw new ArgumentNullException("storedProcs");
                  _storedProcs = storedProcs;
              }
       
              public void RunJob(string jobName){
                  if(JobIsStarted(jobName)){
                      throw new InvalidOperationException(string.Format("Job {0} has already been started.", jobName));
                  }
                  _storedProcs.StartJob(jobName);
              }
       
              public bool JobIsStarted(string jobName){
                  return CheckJobStatus(jobName) == ExecutionStatus.Executing;
              }
       
              public ExecutionStatus CheckJobStatus(string jobName){
                  return _storedProcs.CheckJobStatus(jobName);
              }
          }

    Красота: никаких тебе SqlCommands & SqlConnections - все просто и лаконично.
    Запускаю - не работает. Получаю следующую ошибку:
    Цитата
    EXECUTE permission denied on object 'sp_help_job', database 'msdb', schema 'dbo'. The 'wc_sp_help_job' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.


    Вызвать системную хранимку не так-то просто. Должно выполняться 2 условия:
    1. Пользователь с учетными данными которого выполняется хранимка-оболочка должен быть "владельцем" Job
    2. У этого пользователя должны быть права на запуск и мониторинг Job(s)

    Делаем юзера владельцем:
    ExpandedWrap disabled
          USE [msdb]
          GO
          EXEC msdb.dbo.sp_update_job @job_name=N'<Имя вашей Job>',
          @owner_login_name=N'Имя Пользователя'
          GO


    2. Даем право на выполнение следующих хранимок:
    ExpandedWrap disabled
          GO
          USE msdb
          GO
       
          GRANT EXECUTE ON msdb.dbo.sp_help_jobstep TO <User Name>
       
          GO
       
          GRANT EXECUTE ON msdb.dbo.sp_help_job TO <User Name>
       
          GO
       
          GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO <User Name>
       
          GO
       
          GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO <User Name>
       
          GO

    Этот набор минимальный для мониторинга и старта/стопа Job.
    Тема, конечно довольно специфичная, и это далеко не частый сценарй. Но, если вам нужно мониторить
    Job программно - готовый рецепт, который, надеюсь, сэкономит массу времени :)

    Спасибо за внимание.
    Было бы здорово услыашть и ваше мнение по этому вопросу.

    Миха @ CODE WW.
    Демки, фотки с концертов и др. инфа http://vkontakte.ru/club3969012
    0 пользователей читают эту тему (0 гостей и 0 скрытых пользователей)
    0 пользователей:


    Рейтинг@Mail.ru
    [ Script Execution time: 0,0648 ]   [ 17 queries used ]   [ Generated: 18.11.17, 03:00 GMT ]