none
Always on - Active/passive sql server 2012

    Question

  • Hello everyone

    I am going to install SQL Server 2012 on windows server 2012. I want to create two different windows server 2012 and install sql server 2012 on both servers. I want one server to be primary(active) and other server to be secondary(passive). So that where active server goes down, passive server becomes active.

    I have gone through details about Always on functionality in SQL Server 2012. So can i do active/passive server with always on functionality?

    If i can, so when primary server goes down, users can still write data to specific database on secondary server?

    Thanks in advance

    Wednesday, April 03, 2013 10:07 PM

Answers

  • You can assign the SAN cluster disks to cluster nodes with iSCSI or fiber optics. 

    http://www.petri.co.il/windows-failover-cluster-iscsi-technology.htm

    http://blogs.msdn.com/b/clustering/archive/2012/06/02/10314262.aspx

    http://www.vkernel.ro/blog/creating-a-windows-server-2012-failover-cluster

    Thursday, April 04, 2013 8:47 PM

All replies

  • Yes,please read this article,

    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 Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, April 04, 2013 5:46 AM
  • In your case, create a windows cluster between the two servers and install the one SQL Server 2012 instance on one node add that instance to secondary node then your instance will can act as Active\Passive as you requested.

    If you talking about Always on, it is giving high availability to database level and maintain the secondary copies of primary on passive nodes.

    That means, when your first node is goes down then only that grouped database fail over to passive node in Always on other info like jobs and system databases are will not available on passive node.

      

    Sriram 

     
    Thursday, April 04, 2013 1:12 PM
  • Hello Sriram

    Thanks for the response. So when we do windows cluster, i have to provide shared storage and virtual IP address, so do i have to create one more server for virtual IP Address? Where should i save disk for shared storage so both active and passive can access to it?

    Thanks

    Thursday, April 04, 2013 2:48 PM
  • Hi Swetha,

    For build windows cluster refer this link http://blogs.msdn.com/b/clustering/archive/2012/05/01/10299698.aspx

    No need to create one more server for Virtual IP address while creating windows cluster, it will ask Virtual IP and Cluster Name for that cluster

    You can use that IP or cluster name for managing cluster servers without logging into that actual nodes.

    Sriram

    Thursday, April 04, 2013 4:30 PM
  • Hello Sriram

    What about the disk shared storage for both nodes?

    Thanks again for your response

    Thursday, April 04, 2013 5:07 PM
  • You can assign the SAN cluster disks to cluster nodes with iSCSI or fiber optics. 

    http://www.petri.co.il/windows-failover-cluster-iscsi-technology.htm

    http://blogs.msdn.com/b/clustering/archive/2012/06/02/10314262.aspx

    http://www.vkernel.ro/blog/creating-a-windows-server-2012-failover-cluster

    Thursday, April 04, 2013 8:47 PM
  • Hello Sriram

    Thank you for your helpful responses. I have last question - When i install SQL 2012 on server 1(Active),i select New SQL server failover cluster installation.

    And on server 2(Passive), i select add node to a SQL server failover cluster.

    Am i correct in above scenario?

    Thanks

    Friday, April 05, 2013 1:56 PM
  • Yes You are correct
    Friday, April 05, 2013 6:46 PM
  • Thanks Sriram
    Friday, April 05, 2013 8:08 PM