Oct 11 2016

SQL Server 2012 AlwaysOn Availability Groups Insallation and Configuration

Category: AdministrationFatih Acar @ 09:00

Always On Availability Group is a new feature in SQL Server Database with 2012 release. Before this feature, we was using database mirroring feature in SQL Server 2005. But, mirroring has some limitations and weaknesses. and mirroring feature will be disabled near future.

AlwaysOn Availability Groups Features

  • Multi or Single Database Failovers
  • Multiple Secondaries
  • Active (Readably) Secondaries
  • Integrated HA Management

sqlserveravailabilitygroup

System Information

OS: Windows 2012 Server
DB: SQL Server 2012
Side 1: TESTDB1
Side 2: TESTDB2
Failover Cluster Name: TESTDB
Availability Group Name: TESTDB_AG
Listener Name: TESTDBVIP
Listener Port: 1433

AlwaysOn Availability Groups Installation Steps

  • Take Domain all sides and connect with domain admin user.
  • Install Failover Clustering feature on all sides.
  • Create Failover Cluster with exclude shared disk (uncheck “Add all eligible storage to the cluster”) on first side with domain admin user.
  • Add windows user as Administrator from domain to Operating System like domain\facar on all sides.
  • Connect to operating system with domain\facar user for installation.
  • Install SQL Server Database with the same Instance name on all sides. (Note : Use SQL_Latin1_General_CP1_CI_AS collation due to you can get an error when creating AG at listener service.)
  • Check Enable AlwaysOn Availability Groups (Sql Server Configuration Manager > SQL Server Services > SQL Server (DBNAME) Properties > AlwaysOn High Availability > Check Enable AlwaysOn Availability Groups) on all sides and restart SQL Server service on all sides.
  • Create user on SQL Server Database with Operating System user domain\facar with sysadmin privilege on all sides.
  • Open services.msc and change SQL Server service Log On account domain\facar user and restart service on all sides.
  • Create folder like E:\Backup and share for backup and t-log transport. You have to reach with \\TESTDB1\Backup
  • Take full backup of database that you want to use for failover.
  • Create New Availability Group on first side.

sql_availability_group1

sql_availability_group2

sql_availability_group3

sql_availability_group4

sql_availability_group5

sql_availability_group6

sql_availability_group7

sql_availability_group8

sql_availability_group9

sql_availability_group10

After all steps, you can test your database to control whether your operations flow into second side or not.

You can find how to force manual failover Availabiliry Group here : https://www.fatihacar.com/blog/mssql-server-2012-availability-groups-ag-force-manual-failover/

5,125 total views, 2 views today

Tags: Database Administration, MsSQL Server, SQL Server Administration, SQL Server Availability Group

facebook comments:

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.