1

I have the following scenario on SQL Server 2016 Standard Edition 13.0.4411.0 (SP1-CU1):

2 database servers (DBNode1 and DBNode2). I have a database in a Basic Availability Group, with DBNode1 being the Primary and DBNode2 being the Secondary. As expected, if I try connecting to the DB on DBNode2, it fails as expected with the "The target database, ..., is participating in an availability group and is currently not accessible for queries" error

I've created a SQL Server Agent Job on DBNode2 only (the Secondary) to perform a dummy UPDATE on the database every minute as a test and expected this to fail as that DB should not be accessible on that Secondary node.

UPDATE dbo.SomeTable SET Field1=GETDATE() WHERE Id = 1

The "Database" option for the job step is set to my DB. However, it succeeds and has actually performed the operation on the Primary node. (See images 1 and 2 below)

If I change the Job to target the master database, and use the following TSQL command structure, this does then error as I'd expect, being the Secondary node (See images 3 and 4 below).

UPDATE MyDB.dbo.SomeTable SET Field1=GETDATE() WHERE Id = 1

It seems like behind the scenes, despite targetting the local server, this is actually working out that the target DB is in an Availability Group and so connects to the Primary node and runs the command there. Is this normal / documented behaviour?

UPDATE:

Added screenshots to show the setup, to avoid confusion.

1 - original job setup showing the unexpected behaviour 1 - Original job - setup

2 - original job history output, showing the successful execution 2 - Original job - history

3 - revised job setup showing the expected behaviour 3 - Revised job - setup

4 - revised job history output, showing the failed execution as expected enter image description here

  • No it is not normal/documented behavior. I just tested and got this error message. Message Executed as user: domain\account. Failed to update database "AGTest" because the database is read-only. [SQLSTATE 25000] (Error 3906). The step failed. – SqlWorldWide Mar 28 '17 at 12:11
  • @Taiob - that was the outcome I was expecting, hence surprised it wasn't what happened! If anyone knows of how this is achievable, would be great to understand :/ – AdaTheDev Mar 28 '17 at 12:17
  • 1
    When you look at the job properties, what server is it targeting? (Job Properties --> Targets). But if you want to find out if the job is running on the primary node, a better method is here, querying the system tables. sqlworldwide.com/schedule-sql-agent-job-with-availability-group – Jonathan Fite Mar 28 '17 at 13:16
  • @JonathanFite The target is set to the only available option: "Target local server" - plus the SQL Agent History shows the local server (the Secondary node - DBNode2 in this example) as the Server the job was run on. I did also test tweaking the TSQL step to check things like @@SERVERNAME and "am I the primary" - it showed @@SERVERNAME was DBNode1 and Primary = true, despite the fact the job is targetting the local server on DBNode2. It's like, the connection being used, is connecting to the DB via the availability group listener and then running the TSQL – AdaTheDev Mar 28 '17 at 13:30
  • 1
    13.0.4411.0 (SP1-CU1) – AdaTheDev Mar 28 '17 at 13:37
-2

Sadly, I'm going to argue that you're just 'scratching the surface' in terms of some of the complexity associated with SQL Server Agent Jobs and some of the complexities you face with Availability Groups (AGs). And, I say "sadly" because Microsoft has actually done a VERY poor job of addressing these concerns to the point where there's virtually no guidance from them.

So, for example - assume you get this working as expected and then you've got a job set to run at the same time on both servers and it'll automatically run on the current/correct replica (depending upon which is the primary) - great/perfect. Until someone comes in and disables the job or changes the schedule - but only makes that change on ONE server (not both) - and then there's a failover. Now what? (Or, in other words, if it's important that a job run (or NOT run) then it's important that whatever changes are made to the job should be synchronized between one server and the next (in a 2-node AG) and, yet, it's incredibly easy to let jobs like this get out of sync - even when there's only 1 'DBA' managing the servers.

As such, I spewed out a set of blog posts on all of this a while back: http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-27-options-and-concerns-more-advanced-dep

The blog posts are VERY MUCH a 'stream of consciousness' sort of affair - and there are a couple of mis-steps in the posts and things are LESS insightful than I'd like. Still, by skimming through those posts, you can gain some great insights (i think) on some of the problems to expect and OPTIONS for working around those (with various pros/cons configured).

Eventually, I'll rewrite that set of posts into something more concise (and more 'wise' - i.e., that benefits from the number of AGs I've worked with since those posts - and all of the changes I've made). That said, here's a rough overview of what I do now to address these details:

  • I fully use the UDFs that determine whether the replica is the principal or not (either based on db-name or ag-name).
  • Rather than use the 'job state table' I outlined (as that's too clunky), jobs are either a) run as 'server-level' jobs (backups, server event logs/history maintenance/etc.) or b) assigned a 'disabled category' - meaning the job is/should be disabled (and then my code checks to make sure 'disabled' jobs are disabled and so on) or c) set to a SQL Server Agent Job category where the NAME of the category = the name of the AG they're in - so that when failover happens, code traps said failover and responds on the new primary by enabling all jobs of categoryname = 'ag-name' and doing the opposite on the secondary (if it's still up).
  • Still running synchronization checks (vs server-level details and jobs) every x minutes - to catch situations where something changes on one server - but not the other. (I don't bother letting CODE try and sync the differences - instead, it just reports that something is different.)
  • I skimmed the series and I believe you're making this a good bit harder than it needs to be. IMHO, check if you are the primary and run the job. That isn't that hard, again, in my opinion. If you want to make life easier, use an enterprise job scheduling engine and take agent out of it - which should be used when there are complex ancestor/children scenarios and complex job interactions, agent wasn't made for that. – Sean Gallardy Mar 28 '17 at 21:14
  • Additionally, there is guidance. Put the jobs on all of the replicas, use the DMFs. Done. It is spelled out in multiple places in BOL. – Sean Gallardy Mar 28 '17 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.