Archive for July, 2016

Configuration Manager 1602 introduced the support of SQL Server AlwaysOn Availability Groups
AlwaysOn Availability Groups provide high availability for multiple databases, and they can make use of multiple secondary replicas. Each secondary SQL Server replica has its own copy of the protected databases. AlwaysOn Availability Groups continuously synchronize transactions from the primary replica to each of the secondary replicas. This replication can be configured as synchronous or asynchronous to support local high availability or remote disaster recovery.

In this guide I used lab consisting of 2 SQL servers 2012 (SQL and SQL1) which will be member nodes of windows cluster named SQLCluster,1 machine where SCCM server 1602 will be installed,SCCM will be using Always on availability group instance named HA.Because this is lab environment,sccm server will be iSCSI target for SQL and SQL1,but in production environment iSCSI target should be dedicated machine

Installing windows iSCSI target
Install iSCSI Target Server role:

Untitled.png

Add 2 or more HDD’s,initialize it and format as NTFS partitions then create iSCSI disk

Untitled

Select disk,click next,choose Size and click Next again

Untitled.png

New-iSCSI target:

Untitled.png

Click Add to specify which hosts can access this iSCSi target:

Untitled.png

I added sql and sql1 (192.168.0.12/13)

Untitled.png

Untitled.png

Repeat same procedure for other disk(s)
Now on future cluster members (SQL and SQL1) from control panel click iSCSi initiator

Untitled.png

In targets type IP of iSCSI target server-Quick connect

Untitled

Initialize new disks and format it as NTFS on SQL and SQL1
Now,on both servers install Failover cluster features

Untitled.png

On either SQL and SQL1 open Cluster manager-Validate cluster

Untitled.png

Add both servers:

Untitled.png

Run full tests

Untitled.png

Untitled.png

Click Finish,create cluster wizard opens automatically,enter unused cluster name and unused IP.
If all went well,cluster is created and cluster computer name and IP address will be added automatically

Untitled.png

Now on both nodes install SQL server (standalone installation)

Untitled.png

Note!!,on another node repeat the same procedure but DON’T install reporting server feature
(because Reporting Server/Reporting SeverTemp database will be configured for replication)
Now,when SQL server is installed on both nodes (SQL and SQL1),let’s create Always on Availability group)
First,we need to enable this feature,on both node do the same procedure:
add SCCM computer account to local Administrator group:

Untitled.png

In SQL server configuration manager,right click on SQL Server (MSSQLSERVER)-properties

Untitled.png

Check Enable AlwaysOn Availability Groups and the restart MSSQLSER service)

Untitled

Now on node when Reporting services node is installed,open SQL server management studio,right click
Reporting Server database-Properties-Options-Recovery Model-Full

Untitled.png

Untitled.png

Again,right click ReportingsServer database-Task-BackUp

Untitled.png

Choose path and select OK

Untitled.png

Untitled.png

Repeat same procedure for ReportServerTemp database,these are conditions for database to be added to replication group

Now right click AlwaysOn High Availability-New Availability Group Wizard

Untitled

Set a name and click next,choose databases and click next again

Untitled.png

Click Add replica

Untitled.png

Type name of second node (where these 2 databases will be replicated) and click OK

Untitled

Enable automatic failover and set readability of database copy on secondary site to Yes

Untitled.png

Choose shared folder where wizard will copy databases chosen for replication and then restore them on
Secondary site.Note!!,on this shared folder give computer cluster account (SQLCluster- created by new cluster wizard) full NTFS permission

Untitled.png

Untitled.png

Now we need to create Availability Group Listener,it’s virtual DNS name and IP address which will be mapped to this Availability group.When install SCCM server this name will be submitted to SCCM wizard when we are prompted for SQL server where SCCM database will be installed.Right click on Availability Group listener-Add listener

Untitled.png

Type unused DNS name and IP address (the same procedure as when you created windows cluster at the beginning)

Untitled.png

Installing SCCM
When prompted for SQL server name type in name of Availability group listener

Untitled.png

And network location for database backup

Untitled.png

After installation SCCM database will be automatically added  to availability group

Untitled.png

In SCCM console,listener,and both nodes are shown

Untitled.png