What happens if you have a SQL Agent job, but the logic is not aware (or cannot be aware) that you may have an Availability Group in place. There is nothing built-in to Agent to account for this, but there is a way can you configure an Agent job to check for the existence of an AG before proceeding. Here is the scenario. First, we have a 2-node cluster running a synchronous Availability Group with automatic failover and readable secondaries turned off. Next, we have a requirement to run a SQL Agent job every hour to collect data from a...
Continue reading...Availability Groups
How to Tell If Your Users are Connecting to the Availability Group Listener
You’ve spent a lot of time planning and building out a new SQL Server 2012 environment complete with Availability Group Listeners, but how can you be sure the end users are connecting to the listener and not directly to the SQL Server instance? So why would we care about this? To begin with, if the users are not connecting to the listener, then upon a failover to another replica, those users would have to connect to a different SQL Server instance name. Having a single point of connection is crucial for the high availability process to work correctly. In a...
Continue reading...Are You the Primary Replica?
UPDATED — Jul 3, 2015 — To verify database exists, per comments by Konstantinos Katsoridis. Thanks for finding the bug! In my recent adventures with AlwaysOn Availability Groups, I noticed a gap in identifying whether or not a database on the current server is the primary or secondary replica. The gap being Microsoft did not provide a DMO to return this information. The good news is the documentation for the upcoming release of SQL Server 2014 looks to include a DMO, but that doesn’t help those of us who are running SQL Server 2012. I’ve developed a function, dbo.fn_hadr_is_primary_replica, to provide you...
Continue reading...Setup an Availability Group with Multiple Subnets in VMware Workstation
Before we get started, I want to make it clear this is NOT how you would normally configure all these items in a production environment. This is meant for a lab or demo area to play with Availability Groups over multiple subnets. I use VMware a lot for demos at work as well as tooling around with various Windows and SQL Server related stuff. In working with Availability Groups, one of the things I would like to do for my demos is have multiple subnets in VMware Workstation, so I can simulate a site failover. Just to test Availability Groups...
Continue reading...
