OK since my company really would not prefer to spend the money for Windows 2003 Enterprise and MS SQL 2005 Enterprise I was thinking about this idea...
Server1 - W2K3 Std - SQL2005 Std - Primary
Server2 - W2K3 Std - SQL2005 Std - Mirror
Server3 - W2K3 Std - SQL2005 Std - Witness
Server4 - W2K3 Std - SQL2005 Std - Processing
OK So we have Server1 & Server2 doing the Database Mirroring. Then I have Server3 as the Witness to say who is the Primary and so forth. Now Server4 I thinking to us to create jobs that will run on a Schedule to pull data from our Ticketing system and import it into Server1 & Server2 (Yes this is a Datawarehouse).. So what I am wondering... We really wish to not have to change our connections to the DB with the Failover connection string and just use a common name. Since we are not usng Enterprise I can not do the Failover Clustering... So what I was wondering is if we could use the MS NLB. So for example with Server1 being primary and Server2 being Mirror... Server1 dies or goes down for maintance or whatever... Then Server3 will notice that and say OK Server2 you are now primary but also have Server3 go into the MS NLB and say Server3 Online and take all connections. Then when Server1 comes back on-line it is set as the Mirror system and is Offline in the NLB... Is this possible? If yes can someone either A... Give me directions on how to do that... or B shot me some URL's where I can read up on this stuff?
Thanks for all your help anyone is able to provide. It is greatly appricated.
Billy S.
1. You can build a 2 node cluster with the Standard edition
2. You don't need to deploy Standard edition for the witness, it can be Express Edition if you choose
3. Your imports would go to Server1. You can not write to a mirror.
4. Yes, you can combine this with NLB if you really want to.
|||1. How do you setup a Cluster in Windows 2003 Server Standard? To my understanding it wont allow you to create a cluster on the Standard version... Can you give me a URL for that?
2. Yeah I understand that... but was noticing you cant really schedule any jobs via Express edition... Unless I am confused about that also.
3. Wouldn't my imports go to the Virtual name being which ever SQL Server is the Primary? Cause say Server1 dies and Server2 becomes primary... Wouldn't I want the updates to then to go Server2?
4. Do you happen to know where some documentation is on this concept?
Thanks again for all the help.
Billy S.
|||What I believe Michael was referring to is SQL Server Standard Edition will allow you to create a 2 node cluster. I believe that you will still need to purchase Windows 2003 Server Enterprise Edition to cluster the two servers together at the OS level.
There are 3rd party products that allow you to do failover clustering similar to Microsoft Clustering that allow Windows 2003 Server Standard edition as the OS.
Hope this helps clarify things.
Drew
|||What about on the Witness when it goes to Set a server to be the primary... can it also execute another command that will send a command to either a HW NLB or the MS NLB to set which ever server Online and the other server offline?
I would try and do it now but I need to wait some time first and get our VM server in before I can test anything... But manager is wanting me to say yes or no on this method and exactly what versions we will need to purchase for both the OS & SQL parts... So... Is there some place I can read about what the Witness can do when it does do a failover and set say Server2 to be primary and Server1 as mirror... like say run server2prim.bat... Just trying to get this figured out the best I can while they still have the purse open :)
|||Actually, if you're operating in full sync mode (full safety), mirroring should do everything you need.
DBM can handle the redirection when there is a principal transition. You use a little different connection string which names both principal and partner. On connection, the app will automatically try the partner if the principal does not respond.
|||Yes... We can use the Failover Partner in the ODBC type connections... However some of the apps I design/manage/run use Perl and I do a direct connection not looking at ODBC to a single server... So... How would I do that without using the Failover peice in the connection string and not use the Microsoft Clustering Service? That is why I was looking at NLB...You have 2 machines in a NLB setup.. 1 system is set "Online" and the Secondary (Mirror Server) is set "Offline" now is there anyways on the Witness server to tell it when Primary server dies and it not available after you tell the Mirror server to become the primary run this command or batch job which will send a request to the NLB device or app saying Server1 is "Offline" and Server2 is "Online"... Does this make any sense? Or is what I am wanting to do just not doable period?|||1. Windows Server 2003 STANDARD edition will do a 2 node cluster. That is very clearly documented in the feature set and there are tens of thousands of clusters out there running under Windows Server 2003 Standard edition. SQL Server 2005 Standard edition will also support a 2 node cluster.
2. If you are putting a load balancer in front of this, there is NOTHING that you need to do. The load balancer gives you a virtual IP for everything you put behind the LB. You hit the virtual IP exposed by the load balancer. The load balancer takes care of routing the traffic to the proper machine on the back end. Your applications are oblivious to any of this since they connect to the VIP and NOT the actual machines behind the load balancer. You don't point anything at all at the witness. The sole purpose of the witness is to enable automatic failover. How does traffic get re-routed by the load balancer? Exactly the same way that a load balancer routes all traffic. It detects that a resource is unavailable and then it automatically routes traffic to the resources that are available. By putting a witness in here, YOU are not telling the mirroring session to failover, the software does this automatically. If you don't put a witness in there to have automatic failover, then putting all of this behind a load balancer would be completely pointless in the first place.
3. You can NOT combine Windows Load Balancing and Clustering. They are incompatible.
|||1) Michael, I know you as an extremely knowledgeable person, so I'm finding a hard time pointing out, that you seems to be off track. You cannot cluster on Windows Standard edition: http://www.microsoft.com/technet/windowsserver/evaluate/features/compare.mspx#64Clustering
Or at least, can you point me to a place in which Microsoft states that you can cluster on Windows 2003 Standard Edition?
Yours
Kim
|||Michael,
I like the Idea of using NLB with 2005 Database mirroring since our DB's guy's will not have to rewrite all of their existing code, however I'm concerned about what would happen in the event of the SQL service stopping. I understand the witness will auto-magically failover the SQL resources but how do you instruct NLB to look for that same condition and redirect traffic accordingly?
Rob
|||That is done in the NLB configuration. You setup NLB with health checks that handle the traffic direction. Simply put, when the code in the NLB configuration can not connect to the SQL Server, it will direct connections to the alternate IP address that it is configured with. It behaves no differently than if you had a web server farm or app server farm behind NLB.
No comments:
Post a Comment