Press "Enter" to skip to content

15 – MiniSqlAgent – Implementare il servizio per eseguire Job Schedulati su SQLServer

Alla distanza di 42 post dall’inizio della serie di quest’anno, arriviamo finalmente al post più importante per concludere lo sviluppo della versione 1.0 di MiniSqlAgent, un Servizio Windows in grado di eseguire in modo schedulato degli script SQL su qualsiasi SqlServer simulando in versione minimalista il Sql Server Agent. In questo post implementeremo quanto necessario ad effettuare le seguenti operazioni:

  • Allo startup del servizio leggere tutti i Job memorizzati sulla cartella dati del servizio ed inserirli in una collection.
  • Impostare un file system watcher che controlli se vengono aggiunti, cancellati, modificati dei Job sulla cartella dati riportando gli aggiornamenti sulla collection.
  • Impostare il Loop di servizio in modo tale che controlli se vi sono Job da eseguire e li esegua.

Le modifiche a MiniSqlAgentJob

Per gestire l’esecuzione dei job ed evitare che lo stesso Job possa essere messo in esecuzione per due volte di seguito, introduciamo nella classe del Job una property.

[XmlIgnore]
public bool IsExecuting
{
    get
    {
        return mIsExecuting;
    }
    set
    {
        mIsExecuting = value;
        OnPropertyChanged(FLD_IsExecuting);
    }
}

Questa property serve ai soli fini funzionali del servizio, pertanto non verrà serializzata su file (a questo serve l’attributo XmlIgnore).

public static MiniSqlAgentJob ReadXml(string xmlFile)
{
    MiniSqlAgentJob ret = null;
    try
    {
        ret = (MiniSqlAgentJob)XmlHelper.DeserializeFromFile(typeof(MiniSqlAgentJob), xmlFile);
        ret.JobFileName = xmlFile;
    }
    catch (Exception ex)
    {
        EventLogger.SendMsg(ex);
        throw;
    }
    return (ret);
}

Modifichiamo anche la funzione di deserializzazione in modo che una volta letto il file aggiorni la property JobFileName, altrimenti sarebbe vuota e non ci permetterebbe di salvare il job dopo aver reimpostato la data della prossima esecuzione.

La collection MiniSqlAgentJobsCollection

Implementiamo la collection che ci servirà per gestire l’esecuzione dei Job.

public class MiniSqlAgentJobsCollection: List<MiniSqlAgentJob>
{

    [XmlIgnore]
    public MiniSqlAgentJob this[string pJobFileName]
    {
        get
        {
            return (this.FirstOrDefault(item => item.JobFileName == pJobFileName));

        }
    }

    public new void Add(MiniSqlAgentJob item)
    {
        if (this[item.JobFileName] == null)
        {
            base.Add(item);
        }
        else
        {
            throw new DuplicateJobException(string.Format("The Job {0} has been already loaded", item.JobFileName));
        }
    }

    public void RemoveJob(string jobFileName)
    {
        MiniSqlAgentJob current = this[jobFileName];
        if (current != null)
        {
            this.Remove(current);
        }
    }

    public void ReplaceJob(string oldJobFileName, MiniSqlAgentJob newJob)
    {
        MiniSqlAgentJob current = this[oldJobFileName];
        if (current != null)
        {
            this.Remove(current);
        }
        this.Add(newJob);
    }

    public IEnumerable<MiniSqlAgentJob> GetExpiredJobs()
    {
        return this.Where(item => item.NextExecutionTime <= DateTime.Now);
    }
    
}

Si tratta di una collection molto semplice, abbiamo utilizzato una List, non una Observable collection perché questa collection non viene usata sulla user interface, non ha quindi scopo usare una collection dotata di eventi relativi alla UI. Abbiamo implementato le seguenti cose:

  • Un indexer che ci permette di trovare un Job dato il nome del suo file.
  • Un overload del metodo Add (Forzato dalla keyword new) per controllare che non vengano generati due Job uguali.
  • Un metodo RemoveJob, in grado di rimuovere un Job dato il nome del file, unico dato che ci viene fornito dal FileSystemWatcher dopo una Delete.
  • Un metodo ReplaceJob, in grado di sostituire un Job modificato o rinominato.
  • Un metodo GetExpiredJobs, che è in grado di restituire una IEnumerable di tutti i Job che risultano scaduti in un determinato momento.

Le modifiche al JobManager

La classe JobManager è quella che fa tutto il lavoro all’interno del servizio, finora si occupava solo di scrivere dei messaggi sulla console o sul log dell’applicazione, ora invece diverrà l’oggetto che effettua tutto il lavoro di processing ed esecuzione dei Job. Vediamo quindi tutte le modifiche apportate alla classe che abbiamo costruito nel post Creare un Servizio Windows installabile con InnoSetup per gestire l’esecuzione dei Job.

private MiniSqlAgentJobsCollection mJobs;

private FileSystemWatcher mJobWatcher;

private SemaphoreSlim mJobExecutionSemaphore;

private object syncRoot = new object();

Prima modifica, abbiamo aggiunto la collection dei Job da eseguire, un file system watcher per controllare le modifiche eseguite sulla cartella dati, ed un Semaforo di esecuzione che ci permetterà di accodare tutti i thread da eseguire ed eseguirli uno alla volta. Abbiamo inoltre creato l’oggetto syncRoot al fine di poter controllare ed evitare che thread concorrenti possano modificare la collezione dei Job provocando inconsistenza nei dati.

public void StartThreading()
{
    try
    {

        string message = "Mini Sql Agent Start Main Thread";
        EventLogger.SendMsg(message, MessageType.Info);

        GetJobs();

        StartFileSystemWatcher();

        mJobExecutionSemaphore = new SemaphoreSlim(1);

        mProcessJobsMainThread = new Thread(new ThreadStart(this.ThreadingLoop));
        mProcessJobsMainThread.Name = "MSAMainThread";
        mProcessJobsMainThread.Start();
    }
    catch (Exception ex)
    {
        EventLogger.SendMsg(ex);
    }
}

Modifichiamo il metodo di startup del manager inserendo il necessario per acquisire tutti i Job presenti su disco, far partire il File System Watcher e attivare il semaforo di esecuzione dei Thread, in questo momento, lo abbiamo regolato in modo che permetta di eseguire non più di un thread per volta, prossimamente metteremo questo parametro fra quelli di configurazione del servizio.

private void GetJobs()
{
    mJobs = new MiniSqlAgentJobsCollection();
    string[] jobFiles = Directory.GetFiles(
        ServiceContext.Instance.SettingsManager.DataFolder,
        "*.xml", SearchOption.AllDirectories);
    for (int i = 0; i < jobFiles.Length; i++)
    {
        MiniSqlAgentJob job = GetJob(jobFiles[i]);
        if (job != null)
        {
            lock (syncRoot)
            {
                mJobs.Add(job);
            }
        }
    }
}

Il metodo che acquisisce i Jobs dalla cartella dati indicata nei parametri di configurazione.

private MiniSqlAgentJob GetJob(string fileName)
{
    MiniSqlAgentJob job = null;
    try
    {
        //Wait until the file has been written and closed
        //made to avoid errors in reading if it is a new job
        while (true)
        {
            try
            {
                FileStream file = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.None);
                file.Close();
                break;
            }
            catch
            {
                //file is in use wait
                Thread.Sleep(1000);
            }
        }

        job = MiniSqlAgentJob.ReadXml(fileName);
        
    }
    catch (Exception ex)
    {
        EventLogger.SendMsg(ex);
        //Ignore errors in files but log them
        job = null;
    }
    return (job);
}

L’acquisizione del singolo file, effettua un test per verificare che il file non sia ancora aperto in scrittura dal programma di modifica, nel caso attende il completamento dell’operazione di scrittura del file, poi va a leggere il Job e genera l’oggetto. In caso di errore questo viene loggato, ma non viene bloccato nulla per evitare che il servizio si blocchi a causa di un file corrotto o scritto male.

private void StartFileSystemWatcher()
{
    mJobWatcher = new FileSystemWatcher(ServiceContext.Instance.SettingsManager.DataFolder, "*.xml");
    mJobWatcher.Changed += mJobWatcher_Changed;
    mJobWatcher.Created += mJobWatcher_Created;
    mJobWatcher.Deleted += mJobWatcher_Deleted;
    mJobWatcher.Renamed += mJobWatcher_Renamed;
    mJobWatcher.EnableRaisingEvents = true;
}

Il metodo di startup del file system watcher, che sottoscrive gli eventi di creazione, modifica, cancellazione e rinomina dei file della cartella dati ed attiva il watcher.

void mJobWatcher_Changed(object sender, FileSystemEventArgs e)
{
    try
    {
        MiniSqlAgentJob job = GetJob(e.FullPath);
        EventLogger.SendMsg(string.Format("Job {0} changed", job.Description), MessageType.Info);
        if (job != null)
        {
            lock (syncRoot)
            {
                mJobs.ReplaceJob(e.FullPath, job);
            }
        }
    }
    catch (Exception ex)
    {
        EventLogger.SendMsg(ex);
    }
}

Il metodo eseguito quando un Job viene modificato su disco, rilegge il job e lo sostituisce al precedente nella collection.

void mJobWatcher_Created(object sender, FileSystemEventArgs e)
{
    MiniSqlAgentJob job = GetJob(e.FullPath);
    if (job != null)
    {
        try
        {
            lock (syncRoot)
            {
                mJobs.Add(job);
                EventLogger.SendMsg(string.Format("Job {0} added from queue", job.Description), MessageType.Info);
            }
        }
        catch (DuplicateJobException ex)
        {
            EventLogger.SendMsg(ex);
        }
    }

}

Il metodo eseguito quando un Job viene generato su disco, legge il job e lo aggiunge alla collection.

void mJobWatcher_Deleted(object sender, FileSystemEventArgs e)
{
    try
    {
        lock (syncRoot)
        {
            EventLogger.SendMsg(string.Format("Job {0} removed from queue", e.FullPath), MessageType.Info);
            mJobs.RemoveJob(e.FullPath);

        }
    }
    catch (Exception ex)
    {
        EventLogger.SendMsg(ex);
    }
}

Il metodo eseguito quando un Job viene cancellato dal disco, usa il nome fornito dall’evento per andare a rimuovere il Job dalla collection.

void mJobWatcher_Renamed(object sender, RenamedEventArgs e)
{
    try
    {

        EventLogger.SendMsg(string.Format("Job {0} renamed old job removed from queue", e.OldFullPath), MessageType.Info);
        MiniSqlAgentJob job = GetJob(e.FullPath);
        if (job != null)
        {
            lock (syncRoot)
            {
                mJobs.ReplaceJob(e.OldFullPath,job);
                EventLogger.SendMsg(string.Format("New renamed Job {0} added to queue", job.Description), MessageType.Info);
            }
        }
    }
    catch (Exception ex)
    {
        EventLogger.SendMsg(ex);
    }
}

Il metodo eseguito quando un Job viene rinominato su disco, viene rimosso il vecchio file e generato il nuovo Job.

public void ExecJob()
{
    try
    {
        EventLogger.SendMsg("Mini Sql Agent Execute expired jobs", MessageType.Info);
        List<MiniSqlAgentJob> jobsToExecute = mJobs.GetExpiredJobs().ToList();
        foreach (MiniSqlAgentJob job in jobsToExecute)
        {
            if (job.IsExecuting) continue;
            
            
            Thread t = new Thread(()=>JobExecute(job));
            t.Name = job.JobFileName;
            t.Start();

            
        }
    }
    catch (Exception ex)
    {
        EventLogger.SendMsg(ex);
    }
}

Il metodo che esegue i Jobs, allo scadere del timer di controllo impostato in fase di configurazione, richiede alla collection la lista di tutti i Job da eseguire e lancia per ciascuno di essi un Thread di esecuzione.

private void JobExecute(MiniSqlAgentJob job)
{
    try
    {

        EventLogger.SendMsg(string.Format("Queueing job {0}", job.Description), MessageType.Info);
        mJobExecutionSemaphore.Wait();
        job.IsExecuting = true;
        EventLogger.SendMsg(string.Format("Executing job {0}", job.Description), MessageType.Info);
        Dnw.Base.Data.SqlServer.Entities.SqlConnectionInfo cnInfo = ServiceContext.Instance.SqlConnections[job.ConnectionID];
        if (cnInfo != null)
        {
            try
            {

                SqlHelper.ExecuteNonQuery(cnInfo.ConnectionString, job.SqlScript);
                job.NextExecutionTime = DateTime.Now.AddMinutes(job.ExecutionInterval);
                lock (syncRoot)
                {
                    job.WriteXml(job.JobFileName);
                }
                EventLogger.SendMsg(string.Format("Executed job {0}", job.Description), MessageType.Info);

            }
            catch (Exception ex)
            {
                EventLogger.SendMsg(ex);
            }
            finally
            {
                job.IsExecuting = false;
            }
        }

    }
    catch (Exception ex)
    {
        EventLogger.SendMsg(ex);
    }
    finally
    {
        mJobExecutionSemaphore.Release();
    }
}

Il metodo che si occupa di eseguire fisicamente un Job, è costruito per effettuare le seguenti operazioni:

  • Richiedere al Semaforo di esecuzione il permesso di eseguire quanto richiesto (Wait attende se vi è un altro thread in esecuzione, quando questo esce va avanti, se nessun thread è in esecuzione, va avanti).
  • Recupera le informazioni di connessione relative al Job.
  • Se trova i dati di connessione
    • Prova ad eseguire lo script su SQL Server.
    • Aggiorna la data ed ora dell’esecuzione successiva.
    • Aggiorna il file del job su disco.
    • Aggiorna il flag di esecuzione
  • In caso di errore logga il problema
  • Informa il Semaforo che è terminato (indipendentemente dal fatto che sia andato a buon fine).
public void StopThreading()
{
    try
    {
        //Do something to stop the job processing
        mJobWatcher.EnableRaisingEvents = false;

        //Disable the threading timer to stop the loop
        mEvQuit.Set();

        if (mProcessJobsMainThread != null)
        {
            mAbortTimer = DateTime.Now.AddMinutes(1);
            //Aspetto che abbia finito il thread in esecuzione
            while (mProcessJobsMainThread.ThreadState != System.Threading.ThreadState.Stopped)
            {
                Thread.Sleep(10000);
                if (DateTime.Now > mAbortTimer)
                {
                    mProcessJobsMainThread.Abort();
                }
            }
        }

    }
    catch (Exception ex)
    {
        EventLogger.SendMsg(ex);
        ExitApp();
    }
}

Se richiesto di fermare il servizio, oltre a fare il necessario per chiudere il thread primario, viene disattivato il file system watcher.

Inseriamo il video che mostra come generare tre Job di esempio sul nostro database Recipes, e come farli eseguire al servizio.

E’ un video noioso, ma i tempi d’attesa devono esserci, se siete attenti a sufficienza noterete un Exception loggata sulla console di servizio, si tratta della prima esecuzione del Backup del Log, che non avendo noi gestito alcuna dipendenza fra i Jobs viene eseguita prima che sia effettuato il Backup Full e da errore per questo motivo. Una volta eseguito il Backup Full del database, tutto funziona.

Conclusioni

Abbiamo finalmente implementato un servizio che fa qualcosa di utile all’interno del nostro PC, possiamo quindi utilizzarlo per programmare il backup dei database sul nostro SQL Express o installarlo su una macchina server con SqlServer o SqlExpress ed utilizzarlo per i backup o per altre funzioni schedulate. Per fare questo cosa abbiamo visto di nuovo in questo post?

  • L’uso del multithreading all’interno di un servizio.
  • L’uso di un semaforo per regolare l’esecuzione dei thread.
  • L’uso di un file system watcher per verificare la modifica dei files su una cartella.

Si tratta di un progetto molto grezzo, che affineremo nei prossimi post almeno un pochino, listiamo alcune delle cose che gli mancano:

  • Qualsiasi controllo sulla correttezza del codice SQL dei Jobs.
  • La possibilità di controllare il timeout delle connessioni.
  • La possibilità di eseguire script formati da più step.
  • La possibilità di schedulare i job in maniera articolata (giornaliero, settimanale, mensile).
  • La possibilità di decidere di fare eseguire più job contemporaneamente al sistema.

Cosa invece è già implementato

  • La possibilità di connettersi a qualsiasi edizione di SqlServer
  • La possibilità di eseguire Jobs su più di un server (ricordate che le connessioni sono una collezione).
  • La possibilità di monitorare cosa fa il servizio tramite la console.
  • La possibilità di configurare l’utente che esegue il servizio in base alle esigenze dell’amministratore di sistema.
  • La possibilità di eseguire qualsiasi tipo di script.

Le ultime due cose indicate qui sopra sono importanti perché servono a gestire problemi di sicurezza, se vogliamo evitare che il servizio che fa i backup possa essere utilizzato per eseguire script malevoli, possiamo limitare i privilegi con cui l’utente che si collega a SQL Server esegue i Jobs, e possiamo limitare anche i privilegi dell’utente del servizio windows in modo tale che ad esempio possa scrivere solo sulle cartelle relative ai dati del servizio e al deposito dei Backup.

Il codice del progetto Minisqlagent con le modifiche relative a questo articolo è disponibile al link seguente:

Il codice delle librerie di uso comune collegate a questo articolo è disponibile al link seguente:

Per qualsiasi domanda, approfondimento o curiosità , usate il link al modulo di contatto oppure il link al forum Microsoft dove troverete me ed altri esperti in grado di rispondervi.