Blog

Setting up Always On Availability Group via PowerShell: A Step-by-Step Guide

Marcelo Fernandes

By fernandesdba

Introduction:

Always On Availability Groups in SQL Server provide high availability and disaster recovery solutions for mission-critical databases. Automating the setup process using PowerShell can streamline the configuration and ensure consistent deployment across environments. In this blog post, we will delve into the process of setting up an Always On Availability Group using PowerShell, empowering DBAs to efficiently establish robust high availability solutions.

Prerequisites:

Before we begin, ensure that the following prerequisites are met:

  1. SQL Server 2016 (or later) Enterprise Edition is installed on all participating nodes (Standard edition will allow you to have Basic Availability Group)
  2. The SQL Server instances are configured with Windows Server Failover Clustering (WSFC).
  3. All participating nodes are part of the same WSFC.

Step 1: Import the SQL Server PowerShell Module

To interact with SQL Server using PowerShell, import the SQL Server module by running the following command:

Import-Module SQLPS -DisableNameChecking

Step 2: Establish Connection to SQL Server Instances

Establish a connection to the SQL Server instances participating in the Always On Availability Group. Use the following command for each instance:

$serverInstance = 'ServerName' $connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($serverInstance) $connection.Connect()

Step 3: Create an Availability Group Listener

The Availability Group Listener provides a virtual network name to which clients connect, abstracting the actual node names. Create the listener using the following PowerShell script:

$availabilityGroupName = 'AGName' $availabilityGroupListener = 'AGListener' $availabilityGroup = New-SqlAvailabilityGroup -Name $availabilityGroupName -Path 'SQLSERVER:SQL$serverInstance' -AvailabilityMode 'SynchronousCommit' $availabilityGroup | Add-SqlAvailabilityGroupListener -ListenerName $availabilityGroupListener -StaticIp 'IP_Address' -Port Port_Number

Step 4: Create Availability Group Replicas

Create replicas for each participating SQL Server instance in the availability group. Run the following PowerShell script for each replica:

$replicaServerInstance = 'Replica_Server_Name' $replicaEndpointURL = 'TCP://Replica_Server_Name:Port_Number' $replica = Add-SqlAvailabilityReplica -AvailabilityGroup $availabilityGroupName -Path 'SQLSERVER:SQL$serverInstance' -ReplicaServerName $replicaServerInstance -EndpointURL $replicaEndpointURL

Step 5: Configure Database(s) for Availability Group

Add the desired database(s) to the availability group using the following PowerShell script:

$databaseName = 'DatabaseName' $availabilityGroup | Add-SqlAvailabilityDatabase -Database $databaseName

Step 6: Configure Endpoints

Configure the required endpoints for the availability group by running the following PowerShell script:

$availabilityGroup | New-SqlAvailabilityGroupEndpoint -Name 'EndpointName' -EndpointType 'TCP'

Step 7: Start the Availability Group

Start the availability group to initiate the synchronization process among replicas. Execute the following PowerShell command:

$availabilityGroup | Start-SqlAvailabilityGroup

Conclusion:

Setting up an Always On Availability Group in SQL Server via PowerShell streamlines the configuration process, allowing for consistent and efficient deployment. By following the step-by-step guide outlined in this blog post, DBAs can establish highly available and resilient database solutions, ensuring minimal downtime and enhanced disaster recovery capabilities.

Fonte: Marcelo Fernandes