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

Question

Post reply

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

Arun Yadav

Ten Centuries

Points: 1289
More actions
February 22, 2022 at 1:23 am

#3977056

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

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

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

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

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/

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

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)

Arun Yadav Ten Centuries Points: 1289 More actions · Answer 3

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!

Ramji29 Hall of Fame Points: 3832 More actions · Answer 4

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

Michael L John One Orange Chip Points: 27038 More actions · Answer 5

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/

hans-juergen.markart Newbie Points: 7 More actions · Answer 6

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).

DJTushR_DBA SSC Enthusiast Points: 101 More actions · Answer 7

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)

https://t.me/pump_upp

Arun Yadav Ten Centuries Points: 1289 More actions · Answer 8

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).

hans-juergen.markart Newbie Points: 7 More actions · Answer 9

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.

Arun Yadav Ten Centuries Points: 1289 More actions · Answer 10

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.

iambitta Newbie Points: 3 More actions · Answer 11

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