June 19, 2012 at 12:29 pm
I have setup my SQL2005 standard edition to mirror between my two sites.
Works fine.
But I didn't have another server (witness) at that time to use. Now I may have an extra server to use so I can set it up as asynchronos.
Can I use any SQL standard server? Can I piggy back off any other SQL server - if it's standard 2005?
June 19, 2012 at 12:31 pm
Any edition, even Express.
A witness is only needed with synchronous mirroring if you want auto failover. Async doesn't involve a witness since it can't automatically fail over.
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
June 19, 2012 at 12:37 pm
krypto69 (6/19/2012)
I have setup my SQL2005 standard edition to mirror between my two sites.Works fine.
But I didn't have another server (witness) at that time to use. Now I may have an extra server to use so I can set it up as asynchronos.
Can I use any SQL standard server? Can I piggy back off any other SQL server - if it's standard 2005?
If I read this correctly, asyncronous mirroring is only possible with Enterprise Edition. Using Standard Edition you must use syncronous mirroring (with or without a witness).
June 19, 2012 at 2:08 pm
If I read this correctly, asyncronous mirroring is only possible with Enterprise Edition. Using Standard Edition you must use syncronous mirroring (with or without a witness).
Okay..
I'm stuck with using standard edition ($). Would I be able to add a witness server to one of my exising SQL standard boxes?
And if so..
What do I gain by having a syncronos witness server?
With my current setup (mirroring syncronous)it is kinda of a huge pain if one side goes down...you gotta setup everything from scratch. Would the witness make it so everything would automagically re-sync?
June 19, 2012 at 2:12 pm
krypto69 (6/19/2012)
If I read this correctly, asyncronous mirroring is only possible with Enterprise Edition. Using Standard Edition you must use syncronous mirroring (with or without a witness).
Okay..
I'm stuck with using standard edition ($). Would I be able to add a witness server to one of my exising SQL standard boxes?
And if so..
What do I gain by having a syncronos witness server?
With my current setup (mirroring syncronous)it is kinda of a huge pain if one side goes down...you gotta setup everything from scratch. Would the witness make it so everything would automagically re-sync?
Adding a witness server (which can be any edition of the same version of SQL Server) allows for automatic failover to the mirror. Without the witness, you have to manually failover.
Care to expalin this in more detail?
With my current setup (mirroring syncronous)it is kinda of a huge pain if one side goes down...you gotta setup everything from scratch.
June 19, 2012 at 2:19 pm
krypto69 (6/19/2012)
If I read this correctly, asyncronous mirroring is only possible with Enterprise Edition. Using Standard Edition you must use syncronous mirroring (with or without a witness).
Okay..
I'm stuck with using standard edition ($). Would I be able to add a witness server to one of my exising SQL standard boxes?
Yes
What do I gain by having a syncronos witness server?
It's not a synchronous witness. It's synchronous mirroring with a witness, and it gets you automatic failover. Read up on database mirroring
With my current setup (mirroring syncronous)it is kinda of a huge pain if one side goes down...you gotta setup everything from scratch. Would the witness make it so everything would automagically re-sync?
That sounds like a misconfigured environment. Mirroring won't require setting up from scratch unless people break the mirroring instead of failing over.
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
June 19, 2012 at 2:28 pm
That sounds like a misconfigured environment. Mirroring won't require setting up from scratch unless people break the mirroring instead of failing over.
Maybe then I'm doing something incorrectly..
I have two sites mirrored. Power goes out at site A for four days (I'm in florida..I have to plan for hurricanes). During this time all my users are working on site B. Power comes back after four days, now they are not in sync. If I try to reconnect after this much time it will give an out of sync error and not re-sync. I doubt I would have the t logs to catch up site A.
In this senario the witness server would come into play and auto send the log to resync site A correct?
June 19, 2012 at 2:30 pm
krypto69 (6/19/2012)
That sounds like a misconfigured environment. Mirroring won't require setting up from scratch unless people break the mirroring instead of failing over.
Maybe then I'm doing something incorrectly..
I have two sites mirrored. Power goes out at site A for four days (I'm in florida..I have to plan for hurricanes). During this time all my users are working on site B. Power comes back after four days, now they are not in sync. If I try to reconnect after this much time it will give an out of sync error and not re-sync. I doubt I would have the t logs to catch up site A.
In this senario the witness server would come into play and auto send the log to resync site A correct?
Did you force a failover or did you break the mirror then do a restore database with recovery on the mirror database to bring it live?
June 19, 2012 at 2:31 pm
The witness, by the way, has nothing to do with transferring transactions. It sole purpose in life is to automate the failover from the principal to mirror database.
June 19, 2012 at 2:37 pm
Did you force a failover or did you break the mirror then do a restore database with recovery on the mirror database to bring it live?
Lets say the power goes out for days (unexpected)...So the mirror was broken, would I have to re-create the mirror from scratch?
does adding a witness server help me at all with not having to recreate the mirror?
June 19, 2012 at 2:49 pm
krypto69 (6/19/2012)
Did you force a failover or did you break the mirror then do a restore database with recovery on the mirror database to bring it live?
Lets say the power goes out for days (unexpected)...So the mirror was broken, would I have to re-create the mirror from scratch?
does adding a witness server help me at all with not having to recreate the mirror?
If you break the mirror, then yes, you have to reestablish it. This may mean from scratch or by apply t-log backups to the mirror (was principal) when the server comes back up.
If you didn't break the mirror, which is something you may do if you don't have the disk capacitiy to hold all transactions until the site that is down comes back up and is availalbe, the databases would begin to syncronize. If you were down for several days, it may actually make more sense to break the mirror then set things up again with the other site becomes available. It really depends on the size of the database and number of transactions processed during that time.
June 19, 2012 at 2:55 pm
So really in my worst case senario the adding the witness doesn't help me much.
Thanks for all the help Lynn.
-->sending you good karma<--- 🙂
June 19, 2012 at 3:16 pm
krypto69 (6/19/2012)
Did you force a failover or did you break the mirror then do a restore database with recovery on the mirror database to bring it live?
Lets say the power goes out for days (unexpected)...So the mirror was broken, would I have to re-create the mirror from scratch?
Power going out for 4 days does not break mirroring. It'll just suspend the mirroring (and the principal's log will grow). To break mirroring, someone will have had to explicitly break the mirroring partnership.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply