SQL Server 2012 – Configuring AlwaysOn

February 2, 2013

This is a follow on to my initial post which was a high-level overview of the SQL Server 2012 Always On feature.

Since then a project has come up which requires SQL Server Always On feature and SQL Server Master Data Services. In preparation for this task I started compiling high level steps on what would be involved when installing/configuring AlwaysOn.

Hopefully you’ll find it useful too.

Windows Failover Cluster

  • Install/configure Windows Failover Cluster feature on all nodes that will participate in AlwaysOn using Failover Cluster Manager
  • Add the nodes that will participate in the cluster
  • Provide Cluster name (windows name) and VIP – this creates DNS and AD objects.

Enable AlwaysOn in SQL Server Configuration Manager

  • SQL Server Configuration Manager – SQL Server properties – Notice the extra tab for AlwaysOn. The value here displays the Windows Cluster name. If the Windows cluster is not configured, it will mention this in the field. Check the AlwaysOn text box.

AlwaysOn

  • Repeat this on each node individually, or use SQL Powershell cmdlets from SSM.
[Enable -SqlAlwaysOn -ServerInstance xxx -Force]

Configure AlwaysOn

  • SSMS, AlwaysOn section, use the AlwaysOn wizard.
    • Create AlwaysOn Name
    • Select the databases to be part of AlwaysOn
    • Select Replicas, Automatic Failover, Synchronous commit, Read only on Secondary
    • Add other nodes, with same settings as above.
    • There are other tabs here, Endpoints, backup preferences and listener.
    • Select initial data synchronization – http://msdn.microsoft.com/en-us/library/hh231021.aspx
    • Allows you to script out process or let wizard do the work.

Simulating Failover

  • SSMS, Right click the AlwaysOn Availability group and select failover
  • Select instance to use as failover node.
  • Connect to that instance
  • A script can be generated at this point for this process or let the wizard action:
ALTER Availability group [xx] FAILOVER
  • Applications will fail / Clients disconnect – if specifying the instance name.
  • Challenge is to make applications failover seamlessly.
  • This is where we use the listener tab mentioned above.
  • Add the listener name.
  • This creates the AlwaysOn listener name which is the virtual name for the AlwaysOn group.
  • If the application or client is specifying this listener name then the failover will be seamless to the client, redirecting to the correct instance that’s hosting the primary replica.

Backups

 

Thanks for reading.

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

%d bloggers like this: