none
ALWAYS ON set up and creating new Sql server on Windows Failover Clustering for DR RRS feed

  • Question

  • We have currently SQL Server 2016 (SP2-CU6)  (X64)   Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64>  (Hypervisor) and we are planning to create another sql server so we can setup ALWAYS ON.

    Could you please guide me what's the requirements should consider for Windows server configuration (existing and new, Sql Server installation and Set up for Always ON?

    Windows Server Failover Clustering need to be installed and setup before SQL is installed or SQL Always-On setup? 

    What's the best way i can configure and set up Always ON?

    Thank for your help!

    Thursday, November 28, 2019 2:42 AM

All replies

  • First of all WSFC , then SQL Server instances and last one set AAG

    https://www.microsoftvirtualacademy.com/tracks/mission-critical-confidence-using-microsoft-sql-server-2012

    http://www.sqlservercentral.com/articles/FCI/92196/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 28, 2019 5:41 AM
  • Hi pdsqsql,

    >>Could you please guide me what's the requirements should consider for Windows server configuration (existing and new, Sql Server installation and Set up for Always ON?

    According to your description, it is recommended that you install the same version of Windows operating system and SQL Server on another server. Note that each server instance must be running the same version of SQL server to participate in the always on group.         

    >>Windows Server Failover Clustering need to be installed and setup before SQL is installed or SQL Always-On setup?    

    Before you deploy always on availability groups, you should make sure that the server you want to join Always On availability groups does not have a domain controller, and then configure every server is a node in a Windows Server Failover Clustering. It is recommended to create a stand-alone instance for each server(you can also configure them as a cluster instance), and finally create Always On availability groups.

    >>What's the best way i can configure and set up Always ON?
    Here are some links that you may need:
    Prerequisites, Restrictions, and Recommendations for Always On availability groups

    In addition,because your version of SQL server is standard, it only supports "basic availability groups", which is an AlwaysOn feature with limited functions. For example, an AlwaysOn group can only support two availability replicas, only one availability database, and the secondary replica cannot be read or backed up. So if it is used as a production environment, it is recommended to use SQL Server 2016 enterprise, which will provide more powerful functions.     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 29, 2019 5:14 AM
  • Cris please do not propose your own answer.

    Pdsqsql,

    The important thing is you CANNOT have "full fledged"AG in Standard edition. What you have in standard edition is Basic Availability Groups (BAG).

    You can only have one database per BAG.

    If you have multiple databases and want to put in BAG you have to create one BAG for each database.

    You can have listener but it would be only for one BAG.

    The Secondary replica DB would not be read_only

    There can be only 2 nodes.

    The configuration process is same as that of AG


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, December 5, 2019 4:23 AM
    Moderator
  • Thank you Shashank.

    I am aware that BAG has limitation. but we already have existing Standard edition sql server and that we need configure for Always ON so we configured another sql server.

    Listener, we have to configure or it has MS default and just skip it?

    Secondary DB is only READ_ONLY right?

    Is it .Net 4.6 is ok or i have to add .Net 3.5 too?

    Sql server installation i have to make sure exactly at SP/CU both or just Sql Server 2016 standard any SP will work?

    When i do the Cluster enabled on both servers after that Cluster validation steps should be on only one server, right? Is it on Primary Server or Secondary server?
    Do i need any quorum witness or Lister configuration or i can just go with default like MS sql can pick automatically?

    Appreciate your help and guidance!

    Sunday, December 15, 2019 7:34 PM
  • Thanks Cris, Still working on it but i will make sure it.
    Sunday, December 15, 2019 7:35 PM
  • Hi pdsqsql,

    >>Listener, we have to configure or it has MS default and just skip it?
    Listener is not default,you can configure it or skip it.

    >>Secondary DB is only READ_ONLY right?
    No read access on secondary replica.The secondary replica remains inactive unless there is a need to failover.

    >>Is it .Net 4.6 is ok or i have to add .Net 3.5 too?
    .Net 4.6 is ok.you needn't to add .Net 3.5.

    >>Sql server installation i have to make sure exactly at SP/CU both or just Sql Server 2016 standard any SP will work?
    An Always On basic availability group can be created on any two SQL Server 2016 Standard Edition servers.But you'd better keep them consistent.

    >>When i do the Cluster enabled on both servers after that Cluster validation steps should be on only one server, right? Is it on Primary Server or Secondary server?
    Either of two.

    >>Do i need any quorum witness or Lister configuration or i can just go with default like MS sql can pick automatically?
    For clusters with even nodes,you need to configure an quorum disk or share file.More details refer to this blog.

    Best regards,

    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 16, 2019 1:51 AM
  • Thank you very much Cris for your detailed answer, it's very very helpful when someone doing first time.

    Really appreciate your help and guidance.

    If Sql Server 2016 AlwaysON BAG is NO READ or NO Backup then what's the main purpose people using?

    So if my Primary failed somehow then secondary will become Active automatically and we can point to our application and test it that we can read the data, right?

    Thank you!

    Sunday, December 22, 2019 2:31 AM
  • Hi pdsqsql,

    >>If Sql Server 2016 AlwaysON BAG is NO READ or NO Backup then what's the main purpose people using?

    "NO READ" means the SQL Server BAG cannot achive the function that transfer client's read-only request to secondary replica.
    "NO Backup" means no backups on secondary replica.But the secondary replica can use either synchronous-commit mode or asynchronous-commit mode to keeep the consistent with the primary replica.

    >>So if my Primary failed somehow then secondary will become Active automatically and we can point to our application and test it that we can read the data, right?

    When a failover occurs,it causes  the secondary replica to become the primary active database.
    If the failover happens, the client can transparently point to the new active replica(primary replica), you need the "Listener".

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 23, 2019 2:04 AM
  • Thank you so much Cris.

    When  i'm dreating BAG Availabilty Group, during Specify Replicas step,
    Whatever option i am selecting either Automatic Faliover or Sychronous commit, I have Primary Replica and Secondary Replica listed in Initial Role so i will be check marking selection into both check box?
    Ex.(Sholuld my check mark goes to both or only for Secondary?
    Server Instance Initial Role Automatic Failover
    Sql2016A Primary X
    Sql2016B Secondary X

    Thank You!

    Monday, December 23, 2019 3:34 PM
  • I mean
    Server Instance	Initial Role	Automatic Failover	
    Sql2016A	Primary		X
    Sql2016B	Secondary	X
    

    Monday, December 23, 2019 3:35 PM
  • Hi,

    I'm Having couple issue:

    1) I can't see Basic Availability Group option in Create New Availability Wizard, I can only see the drop down for Cluster Type is Windows Server Failover Cluster and Check box for Database LevelHealth Detection and Per Database DTC Support
    2)Another issue is I can't select the Primary DB as status shows Password Required and when i am trying to click on, getting this message:
    This DB is Encrypted by Master Key, You need to provide valid password when adding it to the availability group.

    Thanks for the help!

    Monday, December 23, 2019 6:29 PM
  • Hi pdsqsql,

    >>1) I can't see Basic Availability Group option in Create New Availability Wizard...

    At the moment, there is no option to explicitly create a Basic Availability Group using SQL Server Management Studio. But if you are creating an Availability Group with a Standard Edition instance using SQL Server Management Studio, the wizard will automatically detect it and create it as a Basic Availability Group.

    Please make sure  that your SQL Server version is SQL Server 2016(2017,2019) standard edition.

    More useful informations please refer to this  article.

    >>2)Another issue is I can't select the Primary DB as status shows Password Required...

    you could use T-SQL to add the database into AG or enter the password with few extra steps. 

    More details refer to these: post  or  blog.

    Best regards,

    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 24, 2019 2:56 AM
  • Hi pdsqsql,

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.

    Thanks for your contribution.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, December 30, 2019 2:26 AM
  • Thanks Cris.

    For the T-Sql,  In need to build the so i will try to google to find the proper ones.

    Monday, January 6, 2020 7:01 PM
  • Hello friend,

    If some of answers are helpful to you, please mark the useful reply as answer.

    Thanks for your contribution very much.

    Best regards,

    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 7, 2020 12:51 AM