Wednesday, May 15, 2013

70-462 Setup SQL 2012 Availability Group

The below setup for Testing on AG setup :
Server configuration
DC 10.10.10.10
SQL-A 10.10.10.20
SQL-B 10.10.10.30
alwaysonAGL - 10.10.10.70
SQL-AlwaysonCL 10.10.10.90

SQLServer service account =SQL cluster

from Failover Cluster->create a cluster

From DC server, sql-alwaysonCL is automatically appear

Enable AG in SQL Server at both node (SQL-A, SQL-B) and restart

Create a database at sql-A
create database Testdatabase1

Criteria for database participant in AG
- auto-close = off

ALTER DATABASE testdatabase1 SET AUTO_CLOSE OFF WITH NO_WAIT

select is_auto_close_on, * from sys.databases where name='testdatabase1'

alter database testdatabase1 set recovery full

SELECT name, recovery_model_desc  FROM sys.databases WHERE name = 'testdatabase1



Backup database Testdatabase1

Backup database testdatabase1 to disk='c:\backup\testdatabase1.bak'

Setup AG

AG setup fail!!Are we missing anything ??
Fail to create Windows Server Failover Clustering (WSFC) resource with name 'alwaysonAG' and
type SQL Server Availability Group'. The resource type is not registered in the WSFC
cluster. The WSFC cluster many have been destroyed and created again. To register the
resource type in the WSFC cluster, disable and then enable alwaysOn in the SQL Server Configuration Manager.
Failed to create availability group 'alwaysonAG'. The operation encountered SQL Server error
41105 and has been rolled back. Check the SQL Server error log for more details. When the
cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41105);


How to solve
From DC server, active directory user and computer
Right click on computer,


Restart SQL Server SQL-A and SQL-B and back to sql-a resetup AG


Add AG Listener

From DC, add sql cluster (SQL service account) into alwaysonCL with full permission

No comments: