Monday 14 April 2014

Step by Step Always On Availability Groups in SQL Server 2012

This step-by-step has been created to help you get started in creating a SQL Server 2012 AlwaysOn Availability Group for your databases.
In order to follow this post, you need the following :
Three (3) VMware Host with following details:
1)      One Domain Controller (DC), Windows Server 2008 R2 Failover Cluster with 2 Nodes. (WIN1 & Win2). You can follow my post on setup Windows Server Failover cluster 
2)      Microsoft SQL Server 2012  (Standalone) Installed on Both the nodes. (WIN1 & WIN2)
3)      SQL Instance on WIN1 :          WIN1\WIN1SQL12
4)      SQL Instance on WIN2 :          WIN2\WIN2SQL12
5)      Assign Service accounts with domain permissions to SQL Service.
6)      Off the firewall Settings on both the node.
7)      Shared folder on the Primary Node.
8)      Make sure that you are able to connect both SQL Server Instances from both Node.


Enable Availability Groups on SQL Server
This needs to be done on all of the SQL Server instances that you will configure as replicas in your Availability Group. To enable the SQL Server 2012 AlwaysOn Availability Groups feature
1.      Open SQL Server Configuration Manager
2.      Select SQL Server Services
3.      Right-click on your SQL Server (in our example it is named WIN1SQL12) and select Properties
4.      Select the “AlwaysOn High Availability” tab and check "Enable AlwaysOn Availability Groups"
5.      Restart the SQL Server service.


Follow the same steps on Second Instance (WIN2SQL12)




Sample DB for Always On Configuration

Create a sample Database on WIN1SQL12 Instance (which we are going to set as a Primary Instance)


Here we are giving DB name as “Test_AlwaysOn”


  
Make sure that Recovery Model for the database should be Full.


Take Backup of Test_AlwaysOn DB in a shared folder, so we can copy the backup file on secondary Server and restore there.







 Open the shared folder on Secondary Node (WIN2) via Network Share.


Copy the backup file on Secondary (WIN2) Server.


Restore Test_AlwaysOn DB on Secondary Node (WIN2) in No recovery Mode.




Make sure you select RESTORE WITH RECOVERY Option.



So Now Database State will be in Restoring Mode.



Create and Configure SQL Server 2012 AlwaysOn Availability Groups
To create and configure a SQL Server 2012 AlwaysOn Availability Group,
1.      Choose any one instance to become the PRIMARY (say WIN1\WIN1SQL12)
2.      Open SQL Server Management Studio on WIN1\WIN1SQL12
3.      Expand the Management folder
4.      Right-click “Availability Groups” and select “New Availability Group Wizard…”



 In the Introduction page, click Next.




In the Specify Availability Group Name page, enter the name of the Availability Group in theAvailability group name: field. Click Next.




In the Select Databases page, select the checkbox beside the database that you want to include in your Availability Group.. Click Next.




In the Specify Replicas page, under the Replicas tab, click the Add Replicas button and connect to the other SQL Server instances that you joined as nodes in your Windows Server Failover Cluster.




 Configure the following options 
·         Automatic Failover (Up to 2) :          Checked
·         Synchronous Commit (Up to 3) :      Checked
·         Readable Secondary:                          Yes




In the Listener tab, select the Create an availability group listener option. Enter the following details.
 
·         Listener DNS name: Name that you will use in your application connection string
·         Port: 1433

Click the Add… button to provide an IP address. In the Add IP Address dialog box, enter your preferred virtual IP address in the IPv4 Address field. Click OK. Click Next.





In the Select Initial Data Synchronization page, select the Join option.



  
In the Validation page, verify that all validation checks return successful results. Click Next.


In the Summary page, verify all configuration settings and click Finish. This will create and configure the AlwaysOn Availability Group and join the databases.




In the Results page, verify that all tasks have been completed successfully.




  
View the Availability Group in SSMS

In SSMS, drill down to Management and Availability Groups. Here you will see your Availability Group. Drill down one farther and you'll see Availability Replicas, Databases, and Group Listeners.



To add a database, replica or view the dashboard right click on the Availability Group Name.




The dashboard will help you determine if your databases are Synchronized and Healthy

You can also check the Availability Group from Failover Cluster Manager.




  
Failover Testing

There are 2 ways via you can test your Always On Availability Failover.

    1)  Via Failover Cluster Manager.
    2)   Via SSMS.

      1)      Failover Always On Availability Group via Windows Failover Cluster Manger:

  • Open Failover Cluster Manager
  • Right Click on Always on Group. Currently Primary Node is WIN1.
  • Move the Service or Application on Secondary Node
  • Click on your Secondary Node (WIN2)



    
  • Confirm Movement of Always on Group on Secondary Node. (WIN2)





  
  • Confirm Successful Failover of Cluster on Secondary Node (WIN2)





  
  • Confirm the Failover from SSMS.
  • Check Availability Group on Primary and Secondary SQL Instance.
  • Current Secondary Replica (Instance) : WIN1\WIN1SQL12



  • The dashboard will help you determine if your databases are Synchronized and Healthy



  • Current Primary Replica (Instance) : WIN2\WIN2SQL12




  • The dashboard will help you determine if your databases are Synchronized and Healthy


     2) Failover from SSMS:

  • Go to the primary Instance
  • Right click on your Availability Group
  • Click on Failover



  •  Click on next on the Introduction Window.


  

  • Select the primary Replica (Instance) on Which you want to failover your Always On Database.




   
  • Provide the Credential and connect to the Primary Instance.



  •   Click on Finish.


   
  • Check the result for Successful Failover and click on Close.



  • Check the Availability Group configuration after successful failover on both primary and secondary Replica (Instance)
  • The dashboard will help you determine if your databases are Synchronized and Healthy
  • Current Secondary Replica (Instance):           WIN2\WIN2SQL12



  • The dashboard will help you determine if your databases are Synchronized and Healthy
  • Current Primary Replica (Instance) :  WIN1\WIN1SQL12





If you get stuck with the setup somewhere, please leave a comment here and I will be glad to help. 

Have fun!

23 comments:

  1. Excellent and thx for sharing such knowledge. Really help us out how to create always on step by step. .

    ReplyDelete
  2. Excellent post its really helps us to implement Always On feature.

    ReplyDelete
  3. Excellent Share... Please keep on share... Thank you so much

    ReplyDelete
  4. I like your post very much. It is very much useful for my research. I hope you to share more info about this. Keep posting SQL server DBA training

    ReplyDelete
  5. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing

    Digital Marketing In Telugu
    Digital Marketing In Hyderabad
    internet marketing
    Digital marketing
    Digital Marketing Strategy

    ReplyDelete
  6. I feel there is a need to introduce Postgre SQL and find out about many other aspects in order to make them work fully and executed.

    SSIS Postgresql Write

    ReplyDelete
  7. Thanks so much with this fantastic new web site. I’m very fired up to show it to anyone. It makes me so satisfied your vast understanding and wisdom have a new channel for trying into the world.
    If you want Digital Marketing Serives :-
    Digital marketing Service in Delhi
    SMM Services
    PPC Services in Delhi
    Website Design & Development Packages
    SEO Services PackagesLocal SEO services
    E-mail marketing services
    YouTube plans

    ReplyDelete
  8. Microsoft SQL Server 2019 Standard provides all database features with analytics and reporting capabilities. It also includes basic availability features and disaster recovery. Microsoft SQL Server Standard can build rich content management applications.

    ReplyDelete
  9. This blog is very knowledgeable .Thankyou for writing this blog .Dreamsoft Consultancy is one of the Leading Company in India who Provide Genuine Experience Certificate in Pune. So Contact here and Get all Details. Contact Us with Get all Details- 9599119376 or check Our Website- https://experiencecertificates.com/experience-certificate-provider-in-Gurgaon.html

    ReplyDelete
  10. Thank you for sharing! We appreciate how much of an impact the data in this post has made on our life and that you choose to visit our blog.
    Power BI Training in Hyderabad
    Power BI Course in Hyderabad

    ReplyDelete
  11. Синоптичные катаклизмы или ритуальные убийства животных по прошествии длительного времени создали целенаправленное трактование увиденного. Гадание онлайн Таро что он думает значится наиболее правдивым вариантом предсказать грядущее человека. Ведущие средства гадания родились тысячелетия тому назад до нашей эры.

    ReplyDelete
  12. Your blog post is very informative and very useful to me. Thank you for sharing this blog post.
    SQL Training Course in Hyderabad

    ReplyDelete