locked
SQL failover - mirroring, log shipping, clustering, load balancing RRS feed

  • Question

  •  

    Hello.

     

    I am confuse and cant decide on how to setup high availability on our SQL 2005. Here's what on my mind and on resources list:

     

    I plan to have mirroring on my SQL1 to SQL2 with the help of SQL3 as witness. So this would be automatic failover. My idea on mirroring is when SQL1 goes down, SQL3 would tell SQL2 to run and be the primary. It will automatically failover to SQL2. Right? My questions are:

    1) How can I revert back to SQL1 once it is ready?

    2) I read in one of the post that it is impossible to write in a mirrored DB, is this true? I mean, what's the use of failing over to the next node when it's not possible to write and update data/records?

     3) If number 2 is false (i hope so), how would the data be synchronize from SQL2 back to SQL1. Those transaction that were made while SQL1 is down.

    4) How about the connection string from the web applications? Would it be automatically point to SQL2? We have load balancing setup in place, would this help web application connection to automatically point to SQL2?

     

    Another setup:

     

    We have SAN in place (not yet used, but is planning to use for this SQL thing), EMC to be specific. My question would be:

    1)  For SAN setup, the data storage would be centralize. So would that mean that SQL1 and SQL2 services will use the same data and log file from the SAN storage?

    2) How would you call this setup then? Can this be clustering type of high availability? Will clustering work under load balancing setup? I believe mirroring is not possible here? Right?

    3) How can I setup my 3 SQL servers with the same theory in mind: when SQL1 goes down, SQL2 will take over. Data will be synchronize when SQL1 is up and running again. With automatic failover and reverting back to primary.

     

    I read so much topics about this, but the more I research, the more I get confuse.

     

    Any suggestions, comments, advice is greatly appreciated!

     

    Thursday, June 14, 2007 2:45 AM

Answers

  • It all depends on what you are trying to achieve with your high availability.

     

    The scope of mirroring is at the database level, so if you have more than one database on the server you would have to set up mirroring for each database.

     

    1. You can force a fail over back to SQL1, but that shouldn't be necessary if the the servers are physically the same.

    2. Unfortunately it is true, you cannot access a database in the mirrored state. 

    3. Data synchronisation occurs after the SQL1 is back because the SQL2 will have a send queue to update SQL1.

    4. You need to specify both the principal and the mirror instances in the connection string.  Your web application will have to managet he reconnection to the new principal server.  Here is some (unfortunately) more reading http://msdn2.microsoft.com/en-us/library/ms175484.aspx.

     

    The other setup you mentioned is server clustering, this is where the entire sql instance will failover to the other machine.  This is different to mirroring.

    1. Yes

    2. Failover Clustering. Yes. Depends on whether you are setting up active/active (licensing considerations) or active/passive. Wrong, you can mirror a database in a cluster to another server or another cluster even (depending on your situation this may be overkill).

    3. With an EMC SAN you are practically assured that the data on it will not be lost.  So you only have to worry about the server failing or network issues.  In the scenario you have provided you will have an active/passive setup.  The web application will be pointing to a virtual ip address/server so it doesn't care which server is in use.  Any way, you can find more inforamtion about this here http://msdn2.microsoft.com/en-us/library/ms189134.aspx.  You should definitely go to the link Before Installing Failover Clustering at the bottom of the provided link.

     

    Hope this helps, i'm going through trying to understand mirroring myself.

     

    Good luck.

     

     Seems my information on active/active clustering is a little skewed, have a look at the following

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1332959&SiteID=1http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1332959&SiteID=1

    This configuration may not be relevant to you anyway.

    Monday, July 2, 2007 6:32 AM

All replies

  • It all depends on what you are trying to achieve with your high availability.

     

    The scope of mirroring is at the database level, so if you have more than one database on the server you would have to set up mirroring for each database.

     

    1. You can force a fail over back to SQL1, but that shouldn't be necessary if the the servers are physically the same.

    2. Unfortunately it is true, you cannot access a database in the mirrored state. 

    3. Data synchronisation occurs after the SQL1 is back because the SQL2 will have a send queue to update SQL1.

    4. You need to specify both the principal and the mirror instances in the connection string.  Your web application will have to managet he reconnection to the new principal server.  Here is some (unfortunately) more reading http://msdn2.microsoft.com/en-us/library/ms175484.aspx.

     

    The other setup you mentioned is server clustering, this is where the entire sql instance will failover to the other machine.  This is different to mirroring.

    1. Yes

    2. Failover Clustering. Yes. Depends on whether you are setting up active/active (licensing considerations) or active/passive. Wrong, you can mirror a database in a cluster to another server or another cluster even (depending on your situation this may be overkill).

    3. With an EMC SAN you are practically assured that the data on it will not be lost.  So you only have to worry about the server failing or network issues.  In the scenario you have provided you will have an active/passive setup.  The web application will be pointing to a virtual ip address/server so it doesn't care which server is in use.  Any way, you can find more inforamtion about this here http://msdn2.microsoft.com/en-us/library/ms189134.aspx.  You should definitely go to the link Before Installing Failover Clustering at the bottom of the provided link.

     

    Hope this helps, i'm going through trying to understand mirroring myself.

     

    Good luck.

     

     Seems my information on active/active clustering is a little skewed, have a look at the following

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1332959&SiteID=1http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1332959&SiteID=1

    This configuration may not be relevant to you anyway.

    Monday, July 2, 2007 6:32 AM
  • thanks! thanks! thanks! Smile
    Tuesday, July 3, 2007 7:04 PM
  •  

    I was just reviewing my post. Unfortunately I wasnt able to setup Mirroring that time but was able to setup clustering. Thanks for the reply Newbie77. However, I might have not clear one of my question:

     

    "2) I read in one of the post that it is impossible to write in a mirrored DB, is this true? I mean, what's the use of failing over to the next node when it's not possible to write and update data/records?"

     

    -->> Unfortunately it is true, you cannot access a database in the mirrored state. 

     

    What I probably mean by this is when SQL1 failover to SQL2, application will be directed to SQL2 (with supported connection string), right? And at that time, they can read, write to SQL2. I hope I am getting the theory correct here Smile)  So if this is true, does it mean that my SQL2 is no loger on a mirrored state and users can read and update the database? And when SQL1 is up again, a synchronization will be automatic and SQL1 will be the principal node again, and in turn the SQL2 will  be set as mirrored node again?

     

    Thanks in advance for any inputs here... It is always appreciated.

     

     

     

    Friday, February 8, 2008 3:45 PM
    1. Yes the mirrored db will be in restoring state and you cannot access it or even read it. However you can create a database snapshot and read the datas.
    2. Yes after failover the mirrored db will be in read-write mode and can be modified as well. If you have configured availability mode in mirroring then you can perform automatic failover and thus once the Sql 1 is up then it will become the principal now and Sql 2 will be the mirror.

    - Deepak

     

     

    Friday, February 8, 2008 3:59 PM
  • Thanks for clarification. It will be a matter of trial and testing for me to do now.

     

    Tuesday, February 12, 2008 6:39 PM