How to make AG a proper DR solution (single IP and sync logins/jobs)?



  • I am analysing a HA DR solution.

    There are 2 sql servers (A and B) on-site and one sql server (C) in the remote location.

    Synchronous AG is configured between A and B. Asyncronous AG is configured between A and C.

    1. If there was a clustering setup then 1 IP address could have been used that will auto connect to the active sql server. In absence of clustering, and in case of the above AG setup, is it possible for application to use 1 sql server ip address?

    2. This isn't a true DR solution because unlike clustering (which uses 1 SAN), the logins, jobs are not syncronized between the sql servers. How can I sync this data with AG?



  • If there was a clustering setup then 1 IP address could have been used that will auto connect to the active sql server. In absence of clustering, and in case of the above AG setup, is it possible for application to use 1 sql server ip address?

    When you referent to the "absence of clustering", I get the impression you're referring to a Failover Cluster Instance. It's still relatively common for an Availability Group to run on a Windows Failover Cluster, though no longer a requirement since SQL Server 2017.

    You cannot use the same IP address when you're dealing with Availability Groups that span subnets. In your case, when talking about a SQL Server in a remote site, so it's pretty safe to assume they're on different subnets. If they're not, the you're going to have much large networking issues to deal with.

    You need to configure an Availability Listener with an IP from each subnet that your Availability Group spans. By default, SQL Server will register both IP addresses in DNS, and it'll be up to the clients to figure what which IP is currently accepting connections.

    You can alter that behavior by changing the RegisterAllProvidersIP setting at the cluster level. Making sure to also lower the TTL. More details available https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-ver15 .

    This isn't a true DR solution because unlike clustering (which uses 1 SAN), the logins, jobs are not syncronized between the sql servers. How can I sync this data with AG?

    It's still a true DR solution, it just requires some additional effort on the front end when setting it up. If you create a SQL Agent job on one node in the Availability Group, you'll have to remember to add it to all nodes in the Availability Group.

    Additionally, you'll want to put a check at the start of each job to check if that node is the primary replica before the job continues, so that you're not having to enabled/disable SQL jobs every time there is a failover. If you're using SQL Server 2014 or later, you can use something like

    IF sys.fn_hadr_is_primary_replica('dbname') = 1
    BEGIN...
    

    Making sure to specify the name of the database the job is going to run against.

    SQLShack has a great article to help you https://www.sqlshack.com/synchronize-logins-between-availability-replicas-in-sql-server-always-on-availability-group/ .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2