Solved

SQL Server 2012 deployment design for two different location

Posted on 2013-06-27
17
374 Views
Last Modified: 2013-07-01
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.
0
Comment
  • 9
  • 8
17 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39283642
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39288007
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39289197
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39289337
OK, so does this means that I works the same way as Exchange Server CCR cluster or DAG.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39289365
Sorry, dont know exchange clusters.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39289509
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39289543
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39289569
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39289827
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
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 500 total points
ID: 39289849
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39289863
ok, so does this means that the Always ON is almost the same like Asynch Replicaiton ?
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 39289877
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
 
LVL 7

Author Closing Comment

by:Senior IT System Engineer
ID: 39289882
Thanks !
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39289886
ok, so the best solution to avoid downtime is to use which technology ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39289946
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 39290161
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39290168
.... Yeah sure. Do i get commission ;)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Resolve DNS query failed errors for Exchange
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now