User Sync - SQL Server Agent Job Setup

Ensure all pre-requisites for SQL Server Configuration have been met and all applications are deployed.
The following steps are to be done directly on the SQL Server Environment Operating System

  1. In SQL Server Management Studio (SSMS), navigate and expand SQL Server Agent in the Object Explorer
  2. Right click on Jobs and select New Job
  3. Enter a Job Name
  4. Click on Steps from the left navigation pane
  5. Click the New button to add a new Step
  6. Enter Load Facilities for the Step Name

    Ensure Transact-SQL script (T-SQL) is selected for Type

    Ensure Run As is blank

    Select ACCMDRWEB for the Database

    Enter the command below
    EXEC DB.sp_VCUserLoadFacilitiesToTemp
  7. Click on Advanced from the left navigation pane
  8. Select Go to the next step for the On Success Action

    Click OK
  9. Click the New button to add a new Step
  10. Enter Load Users for the Step Name

    Ensure Transact-SQL script (T-SQL) is selected for Type

    Ensure Run As is blank

    Select ACCMDRWEB for the Database

    Enter the command below
    EXEC DB.sp_VCUserLoadEmployeesToTemp
  11. Click on Advanced from the left navigation pane
  12. Select Go to the next step for the On Success Action

    Click OK
  13. Click the New button to add a new Step
  14. Enter Sync Facilities and Users for the Step Name

    Ensure Transact-SQL script (T-SQL) is selected for Type

    Ensure Run As is blank

    Select ACCMDRWEB for the Database

    Enter the command below
    EXEC DB.sp_VCUserSyncMaster
  15. Click on Advanced from the left navigation pane
  16. Select Go to the next step for the On Success Action

    Click OK
  17. Click the New button to add a new Step
  18. Enter Purge Sync Logs for the Step Name

    Ensure Transact-SQL script (T-SQL) is selected for Type

    Ensure Run As is blank

    Select ACCMDRWEB for the Database

    Enter the command below
    DECLARE @return_value INT, @p_err_code INT, @p_err_mesg VARCHAR(100)
    EXEC @return_value = DB.sp_ArchiveLogs @p_archive_days = 3, @p_err_code = @p_err_code OUTPUT, @p_err_mesg = @p_err_mesg OUTPUT
  19. Click on Advanced from the left navigation pane
  20. Select Quit the job reporting success for the On Success Action

    Click OK
  21. Click on Schedules from the left navigation pane
  22. Click the New button to add a new Schedule
  23. Enter a Schedule Name
  24. Schedule it to execute Daily after hours (Recommended to execute around 10pm local time or 2am local time)