MSSQL Server AlwaysON: Get alerted for inconsistencies in logins, jobs and databases

  • Good stuff!

    I use the dbatools to do the same things.  https://dbatools.io/commands/

    There are a few different commands that copy logins and permissions between servers.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John wrote:

    Good stuff!

    I use the dbatools to do the same things.  https://dbatools.io/commands/

    There are a few different commands that copy logins and permissions between servers.

    I've not delved into DBATools.io much.  Which commands are you using that copy logins and permissions between servers?  And are they actually better than the old "sp_HelpRevLogin" tool?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Michael L John wrote:

    Good stuff!

    I use the dbatools to do the same things.  https://dbatools.io/commands/

    There are a few different commands that copy logins and permissions between servers.

    Great, thanks for liking and sharing Michael. My attempt was to monitor the missing pieces. Once alerted, dbatools indeed has a lot in the collection and can certainly prove to be useful. Much appreciated!

  • Hey Arun,

    very nicely written article to address this specific issue and very well summarized.

    Keep up the good work buddy.

    Cheers have a great day!

    Rohit

  • Jeff Moden wrote:

    Michael L John wrote:

    Good stuff!

    I use the dbatools to do the same things.  https://dbatools.io/commands/

    There are a few different commands that copy logins and permissions between servers.

    I've not delved into DBATools.io much.  Which commands are you using that copy logins and permissions between servers?  And are they actually better than the old "sp_HelpRevLogin" tool?

    I stopped using "good old sp_HelpRevLogin" when I centralized all of the processes of this type.  I have central server file server that I run all of these things from.

    Weekly, I execute the Copy-DBALogin command from the primary to all of the secondary's in the various AG's that keeps the logins in sync.  It kills any logins that may have been inadvertently created on the secondary's,  and adds a new login that may have been created on the primary from a deployment.

    Nightly I use the Get-DBALogin to export the logins from all of the servers into a file.  The bad thing with this is creating SQL scripts to re-create them in the event of a disaster.  It also does not do SQL logins, but we only have 3 of those and they are only on old legacy servers.

    I use the Export-DBAConfigure to export the server configurations in this process.

    There's way more work to make this foolproof, it's a work in progress.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Hello,

    just to mention that the last two scripts have the same name in the initial description as well as in the scripts itself (there the description is also the same).

  • Hi Arun,

    Thanks for sharing this.

    One of the worst nightmares for DBAs is to avoid any unwanted inconsistencies across all nodes in Always ON.

    There can be scenarios by newbies that they create/modify any object on one node and miss to do so on all other nodes.

    And then when there is a Failover, a bridge call is opened. 😉

    But Arun, with this one DBAs can get rid of this nightmare and be proactive for maintaining exact same copies of replicas…

    Thanks a lot for sharing this worthy script and your knowledge…

    Waiting for more to be shared here…

    🙂 (y)

  • hans-juergen.markart wrote:

    Hello,

    just to mention that the last two scripts have the same name in the initial description as well as in the scripts itself (there the description is also the same).

    Thanks Hans,

    The second last script is to create a stored procedure "usp_Check_async_SQLAgent_jobs_and_alert" to check SQL Agent jobs that do not match between replicas.

    The last script is to create a multi-step SQL Agent job named "AlwaysON-Async-Login-Job-DB-check" to perform the following actions:

    Step 1 will check and e-mail if there are any databases which are not a part of Availability group.

    Step 2 will check and e-mail if there are any logins which are present on a given availability replica and are missing from any other replica(s).

    Step 3 will check and e-mail if there are any SQL Agent jobs which are present on a given availability replica and are missing from any other replica(s).

  • Hello Arun,

    thanks for the answer. However, what I meant were the equal names  of the solution description:

    Check_async_SQLAgent_jobs_and_alert.sql: Use this script to create another stored procedure which will check and e-mail if there are any sql agent jobs which are present on a given availability replica and are missing from any other replica(s).

    Check_async_SQLAgent_jobs_and_alert.sql: Use this script to create a SQL Agent job which can either be executed on demand or,  a required schedule can be attached to get notified whenever required.

    Same in the script text itself.

     

  • hans-juergen.markart wrote:

    Hello Arun,

    thanks for the answer. However, what I meant were the equal names  of the solution description:

    Check_async_SQLAgent_jobs_and_alert.sql: Use this script to create another stored procedure which will check and e-mail if there are any sql agent jobs which are present on a given availability replica and are missing from any other replica(s). Check_async_SQLAgent_jobs_and_alert.sql: Use this script to create a SQL Agent job which can either be executed on demand or,  a required schedule can be attached to get notified whenever required.

    Same in the script text itself.

    Thanks Hans, a revision has been submitted for approval.

    AlwaysON-Async-Login-Job-DB-check-SQLagentjob.sql: Use this script to create a SQL Agent job that can either be executed on-demand or,  a required schedule can be attached to get notified whenever required.

  • Comments posted to this topic are about the item MSSQL Server AlwaysON: Get alerted for inconsistencies in logins, jobs and databases

  • Wonderful script, working like charm. Liked the approach and lucid output information .

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply