Steps to enable Alwayson in SQL Server 2012

Steps to configure Always on availability groups in SQL Server 2012

1. Always on availability groups can be configured in standalone or clustered SQL Server instance.

2. All SQL Server instance (principal and all replicas) must reside on servers which are part of same Windows Server Failover Cluster.

3. The server instance must be running an edition of SQL Server that supports AlwaysOn Availability Groups

To install and configure windows failover cluster follow the steps in HOW TO CREATE CLUSTER USING HYPER-V

Ensure all the systems which has SQL Server instances and will host principal and all replica databases are part of same windows cluster.  

Note: Windows failover cluster can be installed and configured without shared disk starting from windows2008 unlike windows2003.

Once the cluster is configured follow the below step by step guide to setup always on

In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server for which you want to enable AlwaysOn Availability Groups, and click Properties.

clip_image001[3]

Select the AlwaysOn High Availability tab.

Verify that Windows failover cluster name field contains the name of the failover cluster. If this field is blank, this server instance currently does not support AlwaysOn Availability Groups. Either the local computer is not a cluster node, the Windows Server Failover cluster has been shut down, or this edition of SQL Server 2012 that does not support AlwaysOn Availability Groups.

Select the Enable AlwaysOn Availability Groups check box, and click OK.

clip_image001[5]

 

Open SQL Server Management Studio.

Choose any one instance to become PRIMARY. Expand Alwayson High availability->Right-click Availability Groups and select New Availability Group wizard.

clip_image001[4]

New Availability group introduction window will appear,Click Next.

clip_image001[6]

Specify availability group name and click Next.

clip_image001[8]

Select the user databases for the availability group(Database Should have Full backup) and click Next.

clip_image001[16]

Specify an instance of SQL Server to host a secondary replica by selecting Add replica.

clip_image001[18]

Add NODE2 as secondary replica by connecting it.

clip_image001[20]

After selecting secondary replica,Optionally, you can specify endpoint details or leave them default.

clip_image001[22]

Set the backup preference.

clip_image001[24]

Specify your preference for an availability group listener that will provide a client connection point or else select do not create an availability group listener now. 

clip_image001[26]

Select your data synchronization preference as Full and specify a shared network location accessible by all replicas.

clip_image001[28]

Summary of availability group validation will be displayed,Click Next.

clip_image001[30]

Verify the choice made in this wizard.Click Finish.

clip_image001[32]

Wizard completed successfully,Click close and finish the availability group wizard.

clip_image001[34]

Successfully we have created a availability group called AGroup.

clip_image001[38]

You can suspend and resume data movement by expanding Availability groups->Group name->Availability databases->Choose the database –>resume or suspend database.

clip_image001[6]

You can view the synchronization status through the dashboard reports.

clip_image001

If you want to do a failover,right click the available group->group name->Failover.

clip_image001[4]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s