Solved

SQL Server 2012 deployment design for two different location

Posted on 2013-06-27
17
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 8

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 8

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 8

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
 
LVL 8

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 8

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 8

Author Closing Comment

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

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 8

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

724 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