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.
LVL 9
Senior IT System EngineerIT ProfessionalAsked:
Who is Participating?
 
EvilPostItCommented:
No, its almost the same as mirroring, although it allows multiple replica's and the connection to the database is managed at the server side rather than at the client side.
0
 
EvilPostItCommented:
You could use Always ON availability groups, although technically speaking there would still be a minimal downtime but this would also be the case with clustering and mirroring. There is no true high availability in SQL Server unless you load balance the SQL Server and use replication between all nodes to sync the databases.

Most people just take a few seconds of downtime as ok.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
EvilPostItCommented:
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.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
OK, so does this means that I works the same way as Exchange Server CCR cluster or DAG.
0
 
EvilPostItCommented:
Sorry, dont know exchange clusters.
0
 
EvilPostItCommented:
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.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
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.)
0
 
EvilPostItCommented:
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.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
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.
0
 
EvilPostItCommented:
The only way to do zero downtime is to have multiple database servers load balanced utilizing transactional replication. This would involved a probable re-write of the database.

Although this will still drop the sessions which are active on the failure node.

All other methods still involve an element of downtime.

Clustering (Downtime while shared storage is moved and instance brought online)
Mirroring (Downtime while mirrored database changes state and clients re-connected)
AlwaysON (Downtime while replica database is brought online and Virtual name is updated)
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
ok, so does this means that the Always ON is almost the same like Asynch Replicaiton ?
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Thanks !
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
ok, so the best solution to avoid downtime is to use which technology ?
0
 
EvilPostItCommented:
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?
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
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.
0
 
EvilPostItCommented:
.... Yeah sure. Do i get commission ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.