Main Folder Security

Main Folder Security.sql

DECLARE @IsPrimary INT;
DECLARE @JobName NVARCHAR(128);

-- Check if the current node is the primary replica
SELECT @IsPrimary = CASE 
                      WHEN role_desc = 'PRIMARY' THEN 1
                      ELSE 0
                    END
FROM sys.dm_hadr_availability_replica_states AS ars
JOIN sys.availability_replicas AS ar
ON ars.replica_id = ar.replica_id
WHERE ars.is_local = 1;

-- If this node is primary, enable all jobs. If secondary, disable all jobs.
IF @IsPrimary = 1
BEGIN
    -- Enable all SQL Agent jobs
    DECLARE @EnableJobs TABLE (JobName NVARCHAR(128));

    -- Insert all jobs into the temporary table
    INSERT INTO @EnableJobs (JobName)
    SELECT name
    FROM msdb.dbo.sysjobs;

    -- Enable all jobs in the table
    WHILE EXISTS (SELECT * FROM @EnableJobs)
    BEGIN
        SELECT TOP 1 @JobName = JobName FROM @EnableJobs;
        
        EXEC msdb.dbo.sp_update_job @job_name = @JobName, @enabled = 1;
        PRINT 'Enabled ' + @JobName + ' on the primary replica';

        -- Remove the job from the table after processing
        DELETE FROM @EnableJobs WHERE JobName = @JobName;
    END
END
ELSE
BEGIN
    -- Disable all SQL Agent jobs
    DECLARE @DisableJobs TABLE (JobName NVARCHAR(128));

    -- Insert all jobs into the temporary table
    INSERT INTO @DisableJobs (JobName)
    SELECT name
    FROM msdb.dbo.sysjobs;

    -- Disable all jobs in the table
    WHILE EXISTS (SELECT * FROM @DisableJobs)
    BEGIN
        SELECT TOP 1 @JobName = JobName FROM @DisableJobs;
        
        EXEC msdb.dbo.sp_update_job @job_name = @JobName, @enabled = 0;
        PRINT 'Disabled ' + @JobName + ' on the secondary replica';

        -- Remove the job from the table after processing
        DELETE FROM @DisableJobs WHERE JobName = @JobName;
    END
END

Query02.sql

/* 
   This query retrieves information about employees along with their associated projects, if any. 
   The OUTER APPLY operator ensures that all employees are listed, even if they have no associated projects.
*/
SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    p.ProjectName,
    p.StartDate,
    p.EndDate,
    p.Budget
FROM 
    Employees e
OUTER APPLY 
    (SELECT ProjectName, StartDate, EndDate, Budget 
     FROM Projects 
     WHERE Projects.EmployeeID = e.EmployeeID) p;

Query03.sql

/* 
   This query retrieves unique locations from both the Departments table and the combination of 
   Employees and Departments, avoiding duplicates by using UNION.
*/
SELECT Location FROM Departments
UNION
SELECT d.Location
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Query04.sql

/* 
   This query retrieves all locations from both the Departments table and the combination of 
   Employees and Departments, including duplicates, using UNION ALL.
*/
SELECT Location FROM Departments
UNION ALL
SELECT d.Location
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Query05.sql

/* 
   This query retrieves locations that are common between the Departments table and 
   the combination of Employees and Departments using INTERSECT.
*/
SELECT Location FROM Departments
INTERSECT
SELECT d.Location
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Here's the revised version of your original code incorporating the recommendations for error handling, safe cursor usage, logging, and documentation. The updated script includes improved structure, error logging, and more robust logic to handle job enabling/disabling during failovers in an Always On Availability Group:

/*
    Script Purpose:
    This script enables or disables SQL Agent jobs based on the current server's role in an Always On Availability Group.
    - If the server is the primary replica, it enables all SQL Agent jobs.
    - If the server is the secondary replica, it disables all SQL Agent jobs.
    
    Key Sections:
    1. Determine if the server is primary or secondary.
    2. Enable or disable jobs accordingly.
    3. Error handling for both job processing and role detection.
    4. Exclude any critical jobs from being disabled.
    5. Enhanced logging for easier troubleshooting.

    Author: [Your Name]
    Date: [Current Date]
*/

BEGIN TRY
    DECLARE @IsPrimary INT;
    DECLARE @JobName NVARCHAR(128);
    
    -- 1. Check if the current node is the primary replica
    SELECT @IsPrimary = CASE 
                          WHEN role_desc = 'PRIMARY' THEN 1
                          ELSE 0
                        END
    FROM sys.dm_hadr_availability_replica_states AS ars
    JOIN sys.availability_replicas AS ar
    ON ars.replica_id = ar.replica_id
    WHERE ars.is_local = 1;
    
    -- Log the server role for troubleshooting
    IF @IsPrimary IS NULL
    BEGIN
        PRINT 'Error determining whether this node is primary or secondary. Check AG configuration.';
        RETURN; -- Optionally terminate script if the role cannot be determined
    END
    ELSE
    BEGIN
        PRINT 'Server is recognized as: ' + CASE WHEN @IsPrimary = 1 THEN 'Primary' ELSE 'Secondary' END;
    END

    -- 2. Enable jobs if this is the primary replica, disable jobs if this is the secondary replica
    IF @IsPrimary = 1
    BEGIN
        PRINT 'This is the primary replica. Enabling all jobs.';

        -- 3. Declare a cursor for all jobs in sysjobs, excluding critical jobs that should always be enabled
        DECLARE JobCursor CURSOR FOR
        SELECT name 
        FROM msdb.dbo.sysjobs
        WHERE name NOT IN ('CriticalJob1', 'CriticalJob2'); -- Modify to exclude any critical jobs
        
        OPEN JobCursor;
        
        FETCH NEXT FROM JobCursor INTO @JobName;
        
        -- Process each job: Enable on primary
        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                -- Enable the job
                EXEC msdb.dbo.sp_update_job @job_name = @JobName, @enabled = 1;
                PRINT 'Enabled job: ' + @JobName + ' on the primary replica.';
            END TRY
            BEGIN CATCH
                -- Error handling for each job
                PRINT 'Error enabling job: ' + @JobName;
                PRINT 'Error Message: ' + ERROR_MESSAGE();
            END CATCH;

            -- Fetch next job
            FETCH NEXT FROM JobCursor INTO @JobName;
        END
        
        -- Close and deallocate the cursor
        CLOSE JobCursor;
        DEALLOCATE JobCursor;

    END
    ELSE
    BEGIN
        PRINT 'This is the secondary replica. Disabling all jobs.';

        -- 4. Declare a cursor for all jobs in sysjobs, excluding critical jobs that should always be enabled
        DECLARE JobCursor CURSOR FOR
        SELECT name 
        FROM msdb.dbo.sysjobs
        WHERE name NOT IN ('CriticalJob1', 'CriticalJob2'); -- Modify to exclude any critical jobs
        
        OPEN JobCursor;
        
        FETCH NEXT FROM JobCursor INTO @JobName;
        
        -- Process each job: Disable on secondary
        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                -- Disable the job
                EXEC msdb.dbo.sp_update_job @job_name = @JobName, @enabled = 0;
                PRINT 'Disabled job: ' + @JobName + ' on the secondary replica.';
            END TRY
            BEGIN CATCH
                -- Error handling for each job
                PRINT 'Error disabling job: ' + @JobName;
                PRINT 'Error Message: ' + ERROR_MESSAGE();
            END CATCH;

            -- Fetch next job
            FETCH NEXT FROM JobCursor INTO @JobName;
        END
        
        -- Close and deallocate the cursor
        CLOSE JobCursor;
        DEALLOCATE JobCursor;

    END
END TRY
BEGIN CATCH
    -- Global error handling for the entire script
    PRINT 'An error occurred while processing jobs. Error details:';
    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10));
    PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(10));
    PRINT 'Error State: ' + CAST(ERROR_STATE() AS NVARCHAR(10));
    PRINT 'Error Procedure: ' + ISNULL(ERROR_PROCEDURE(), 'N/A');
    PRINT 'Error Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10));
    PRINT 'Error Message: ' + ERROR_MESSAGE();
    
    -- Optionally log the error to a table or alert system here
    -- Example:
    -- INSERT INTO dbo.JobExecutionLog (LogTime, JobName, Action, Message) VALUES (GETDATE(), @JobName, 'Error', ERROR_MESSAGE());
END CATCH;

Detailed Review of Changes:

1. Overall Comments:

   • Added detailed comments to the script header and key sections, explaining the purpose of the script, logic, and the flow of the process.
   • Each section (primary detection, job enable/disable, error handling) is clearly documented for easier understanding.

2. Global Error Handling:

   • Enhanced the global CATCH block with detailed error information (error number, severity, state, procedure, and line) to provide complete debugging information.
   • This helps in diagnosing any issues that may occur during script execution.

3. Primary/Secondary Detection:

   • Added logging to print the server’s role (Primary/Secondary) for easier troubleshooting and monitoring.
   • Added an early exit (RETURN) in case the role cannot be determined, which prevents further processing in an invalid state.

4. Cursors:

   • Safe usage of cursors with proper CLOSE and DEALLOCATE to ensure no resource leaks.
   • Improved error handling inside each cursor loop. If a job fails to be enabled or disabled, the error is caught and logged, but the rest of the jobs continue processing.

5. Exclusion of Critical Jobs:

   • Added logic to exclude specific jobs (e.g., 'CriticalJob1', 'CriticalJob2') that should not be disabled/enabled. You can modify this list as needed for your environment.
   • This prevents critical jobs (e.g., monitoring jobs) from being inadvertently disabled.

6. Logging:

   • PRINT statements are included for interactive debugging, but an example of logging to a table (JobExecutionLog) is provided in the comments. This could be useful for long-term tracking or integration with a monitoring system.

Testing and Deployment:

• Testing: It is crucial to test this script in a non-production environment where you can simulate failovers and ensure that jobs are enabled/disabled as expected.
• Performance: Ensure that the script runs efficiently with your number of jobs, especially if there are hundreds of jobs.
• Frequency: If this script will run frequently (e.g., every minute), monitor for any potential performance impact or conflicts with other scheduled tasks.

With these changes, the script is now robust, safe, and ready for a production environment, providing clear error handling, easy troubleshooting, and protection against resource leaks or unintended job disabling.