SQL Server 2012 deployment design for two different location
Can anyone please assist me in what is the best practice (Design practice) for me in terms about SQL Server 2012 high availability so that when I patch the OS where SQL Server 2012 resides, the database is always on and not interrupted at all.
I'm planning to install the SQL Server 2012 in two physical servers across two different data center so that it runs PRODUCTIOn instance, and the other run the DR instance, but then I'm not sure if this should be done on top of the Microsoft clustering service (MSCS) which requires shared storage, in my plan, I do not intend to include the shared storage as this is spread across tow different VLAN and geographical location.
I'm planning to install the SQL Server 2012 in two physical servers across two different data center so that it runs PRODUCTIOn instance, and the other run the DR instance, but then I'm not sure if this should be done on top of the Microsoft clustering service (MSCS) which requires shared storage, in my plan, I do not intend to include the shared storage as this is spread across tow different VLAN and geographical location.
ASKER
ok, so in this case with the always ON technology, I do not need two sets of the SQL Server 2012 nodes to become a cluster like I do in SQL Server 2008 R2 ?
Because in this year I can only granted 1 physical server in Production site and another one in DR site, so clustering is not possible.
Because in this year I can only granted 1 physical server in Production site and another one in DR site, so clustering is not possible.
With SQL Server 2008 r2 you can mirror without a cluster thus fulfilling to need for only 1 machine in each location. Always ON availability groups require clustering to operate although this also doesnt mean you require 2 machines in each location as you are able to create 2x single node clusters.
ASKER
OK, so does this means that I works the same way as Exchange Server CCR cluster or DAG.
Sorry, dont know exchange clusters.
Effectively its database mirroring and you have a listener configured so that if the primary fails it switches the server associated with the DNS record.
ASKER
So in this case when the OS is patched, the SQL server will then redirect all the DB request to the DR node ?
From the application point of view, the database is just single instance not mirrored or clustered (since they both requires additional setup in the app.)
From the application point of view, the database is just single instance not mirrored or clustered (since they both requires additional setup in the app.)
Yep to your first question.
There should be no additional setup in the application. Although please do bear in mind that this is not a zero downtime solution! When the active server goes down it will drop all the active connections and there will be a slight lag involved before the listener changes the active server.
Thinking about it again I think the servers at both sides need to be part of same windows cluster but no shared storage is required. The cluster takes care of the access point.
There should be no additional setup in the application. Although please do bear in mind that this is not a zero downtime solution! When the active server goes down it will drop all the active connections and there will be a slight lag involved before the listener changes the active server.
Thinking about it again I think the servers at both sides need to be part of same windows cluster but no shared storage is required. The cluster takes care of the access point.
ASKER
ok, so what is the solution for the zero downtime ?
because with the MSCS SQL Server 2008 R2, there is almost no downtime when I failover the active node to the passive node for OS patching.
because with the MSCS SQL Server 2008 R2, there is almost no downtime when I failover the active node to the passive node for OS patching.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, so does this means that the Always ON is almost the same like Asynch Replicaiton ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks !
ASKER
ok, so the best solution to avoid downtime is to use which technology ?
I think its more like which is the best at minimizing downtime. TBH with always ON you're talking about a few seconds of downtime... Is this not acceptable to your business?
ASKER
ah if it is just few seconds, then it is fine, so in this case I'll go ahead with the purchase of the 2x HP DL 580 with 1 TB of RAM to be used with Violin Memory 64 TB as the ultra fast datastore.
.... Yeah sure. Do i get commission ;)
Most people just take a few seconds of downtime as ok.