January 17, 2018 at 4:29 am
Hi,
I have a custom maintenance and monitor database, which keeps history on database sizes, agent jobs, logon information and things like that.
Before AlwaysOn I didn't have any issues on how the setup should be, but ever since AO I am questioning whether to have the database inside the availability group or outside.
If it is inside an availability group, then I have a seperate step in my jobs that checks for primary. If I keep it outside the AO, then I have 2 seperate databases, which each keep their own split information, which I dont really want.
Is there anyone wondering about the same thing ?
January 17, 2018 at 7:24 am
Does your maintenance & monitoring database do anything on the system databases? That might inform your decision.
January 17, 2018 at 7:51 am
If this db is tightly bound to the user databases, maybe so. If not, it might warrant it's own AG. Either way, as Beatrix noted, if this depends on reading master/msdb/etc, you might need to rework things before putting this in an AG.
January 17, 2018 at 8:15 am
I do read msdb for the agent jobs, I keep a copy of jobs, to send alerts to nagios. The reason of keeping a copy of the jobs is the ability to acknowledge jobs that have failed.
I also read i guess the master database for info about database and datafile sizes. I keep track of it over time for statistical reasons.
And I get info about logins from SQLServer Error Log.
January 17, 2018 at 8:37 am
The things I'd worry about with failover is strange behavior, or sending dup alerts. Maybe you read some data from master/msdb and load this into a table in your db, then process it?
January 18, 2018 at 5:32 pm
One solution is to use a single central maintenance and monitor database that is then added to an Availability Group for high availability.
The collected data just needs the full SQL Server instance name added, so it can be identified.
I have also seen a combined solution where the data is first stored locally and then copied into a central database.
When the central database cannot be reached, the maintenance and monitor information is not lost, just resent later.
Perhaps you can use this if it means less modification to your current maintenance and monitor system.
Use the local database for urgent alerts and the central database for less urgent reporting.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply