March 8, 2012 at 12:58 am
Dear all,
I'm planning implementation of a new SQL infrastructure for my customer in the project department. Currently, I have really few information’s, which will come later from production department. I only know they currently have two 2 nodes clusters (one with SQL 2000 and one with SQL 2005). I also know that they are many databases on each server. They are low critical and medium critical databases. High critical production database are hosted on other DB servers.
They want to migrate to a new architecture with 4 SQL 2008 R2 servers. They are 2 sites (Main and Data Recovery), VmWare VSphere 5 and SAN on each site will be used. SRM, HA and DRS will be implemented. I don’t know if FT will be implemented.
I already work on VmWare ESX and with SQL Clusters, but never with both technologies in the same time. I also read that SQL Clustering in VSphere Cluster is not supported by DRS, and sometimes not recommended by experienced users, choosing SQL Mirroring.
The goal is an architecture consolidation, upgrade, high availability and DRP solution. I currently have no diagram tools (only a pen and paper) but here is my architecture design:
1 * 2 Nodes Cluster (Windows 2008 R2 Enterprise and SQL 2008 R2) on Main Site
Shared SAN for cluster
1 * 2 Nodes Cluster (Windows 2008 R2 Enterprise and SQL 2008 R2) on DR Site
Shared SAN for cluster
A High-Safety mode Session with Automatic Failover mirroring set from Main Site to DR Site.
A Witness server ideally placed on a third site.
Could you please advise me about this solution? Is it good? Bad? Is there a better way to complete the objectives?
Thank you !
March 8, 2012 at 4:12 am
Be very careful mixing mirroring with auto failover with clustering. The mirroring timeout is, by default, low enough that should the cluster fail over, the mirroring will likely declare a failure and move the principal to the DR site. That's probably not what you want.
If you go that route, change the mirroring timeout so that it allows enough time for the cluster's worst possible failover time (can be a few minutes easily) without declaring a failure
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2012 at 4:45 am
Thank you for your reply.
I saw that information on Microsoft Technet. (http://technet.microsoft.com/en-us/library/ms191309.aspx)
Interresant information for the mirroring timeout.
Thank you for advises
March 12, 2012 at 4:48 am
How can you work out a new architecture until you know exactly what they currently have? Until you know this, your new design can only be provisional.
It is very likely to be over or under specified on hardware power, and could easily miss vital aspects that you will only find out when you have the fiull details.
You should also include in your planning a consideration of moving to the cloud (eg EC2) and if you choose against this a reasoned justification for doing so.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 12, 2012 at 5:31 am
Since you have a SAN in both sites would it be more desireable to have a single 4 node geo cluster with SAN replication?
You can have the advantages of a multisite cluster without the need to change code and connections strings or potential headaches of mixed technologies.
March 15, 2012 at 3:37 am
Dear EdVassie,
You are right, nothing is yet decided by project management.
But having no job currently, I look for all options for HA and DR to propose the best solution when needed.
Dear MysteryJimbo,
Thank you for reply. I read many pro and counters advises for mirroring db, and also for geographically dispersed clustering.
But after some days of searching and reading, I'm know consider geo cluster as the best solution (Full automatic HA and DR) and all conditions are met (high speed fiber chanel connection between sites, same subnet, ...).
Considering the numbers of databases, management want to increase the number of SQL servers + Virtualization (for reliability during maintenance). 30 databases for the actual SQL2005 cluster that i divide in 9 possible instance.
Then two nodes geocluster with san replication seems to be a good solution for most databases, and maybe 4 nodes for medium critical databases. We consider also a third side for disk share quorum witness.
I'm searching for whitepapers showing geo cluster implementation with EMC Mirrorview.
March 15, 2012 at 5:42 am
I'd be cautious combining this clustering solution with virtualisation. It could work, but you need to present a lot of volumes to the server to make it workable.
March 19, 2012 at 4:13 am
You should also consider P2P replication.
P2P replication as advertised has a number of restrictions, but these can be overcome by having operational standards.
The main problem highlighted with P2P is managing updates from multiple sites, and managing schema changes.
Our testing has shown that if you only ever update from a single site, then these issues simply do not exist. But in order to achieve this you need some standards to ensure that all your updates are only ever done at a single site.
One of the best ways to do this is to set up a DNS vanity name that you point to one of your P2P servers, and insist that all updater activity is only ever done while connecting using this vanity name. This allows you to switch the vanity name to any other of your P2P servers if the original one fails, and you should have a script that automates all the work needed for this change.
Likewise if all your schema updates are only ever issued from a single site, then there is no need to quiesce the P2P network while doing this work.
The end result is multiple copies of your data that are fully readable, and quick redesignation of your master write server in the event of a failure. P2P does add complexity over having no replication, but IMHO the complexity and risks of well managed P2P are less than the complexity and risks of well managed clustering.
The major road block to adopting P2P is that your tables cannot have the Rowversion (formerly Timestamp) data type. Also, management of P2P is much easier on SQL 2008 and above than on SQL 2005.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 19, 2012 at 8:27 am
EdVassie (3/19/2012)
You should also consider P2P replication....
Our testing has shown that if you only ever update from a single site, then these issues simply do not exist. But in order to achieve this you need some standards to ensure that all your updates are only ever done at a single site.
...
How difficult is detection and correction after the standards aren't followed [new employee/contractor/manager/clever person outside the department/vendor/DNS update causes problems/etc]?
March 19, 2012 at 8:50 am
I cannot remember which of these is SQL 2005 and which is SQL 2008...
You can get a record of P2P conflicts to be directed to a table. You can then do your own processing with this table to decide what data to keep.
I think with SQL 2008 only, you can get automatic conflict resolution, but it is very simplistic. The server name with the lowest colation sequence wins.
If you are worried that your operational procedures may not be able to cope with P2P then you have got a worse problem if you use clustering. People messing about without knowing what they are doing can kill clustering quicker than replication, as there are more components involved.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 19, 2012 at 9:39 am
rabensql (3/8/2012)
Dear all,I'm planning implementation of a new SQL infrastructure for my customer in the project department. Currently, I have really few information’s, which will come later from production department. I only know they currently have two 2 nodes clusters (one with SQL 2000 and one with SQL 2005). I also know that they are many databases on each server. They are low critical and medium critical databases. High critical production database are hosted on other DB servers.
They want to migrate to a new architecture with 4 SQL 2008 R2 servers. They are 2 sites (Main and Data Recovery), VmWare VSphere 5 and SAN on each site will be used. SRM, HA and DRS will be implemented. I don’t know if FT will be implemented.
I already work on VmWare ESX and with SQL Clusters, but never with both technologies in the same time. I also read that SQL Clustering in VSphere Cluster is not supported by DRS, and sometimes not recommended by experienced users, choosing SQL Mirroring.
The goal is an architecture consolidation, upgrade, high availability and DRP solution. I currently have no diagram tools (only a pen and paper) but here is my architecture design:
1 * 2 Nodes Cluster (Windows 2008 R2 Enterprise and SQL 2008 R2) on Main Site
Shared SAN for cluster
1 * 2 Nodes Cluster (Windows 2008 R2 Enterprise and SQL 2008 R2) on DR Site
Shared SAN for cluster
A High-Safety mode Session with Automatic Failover mirroring set from Main Site to DR Site.
A Witness server ideally placed on a third site.
Could you please advise me about this solution? Is it good? Bad? Is there a better way to complete the objectives?
Thank you !
see my response to your other post on this topic at this link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply