July 15, 2010 at 11:27 am
Your connection string would look something like this:
Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;
The application will connect to whichever one is available, if the primary fails, the application connections will automatically switch to the failover.
The failback is not automatic (and I wouldn't want it to be) it's not 100% seemless in that any open transactions will be rolled back so it's best to schedule a failback for an appropriate time.
That being said, other than licensing issues, there is no need to fail back. If your primary server failed, the failover server becomes the new primary. When the failed server comes back online, it automatically goes into the failover role.
MS Licensing states that if a failover server is used for 30 days then a license is required. For that reason you would want to be consistent in your server roles.
SQL guy and Houston Magician
July 15, 2010 at 11:45 am
Robert Cary (7/15/2010)
Your connection string would look something like this:
Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;
The application will connect to whichever one is available, if the primary fails, the application connections will automatically switch to the failover.
The failback is not automatic (and I wouldn't want it to be) it's not 100% seemless in that any open transactions will be rolled back so it's best to schedule a failback for an appropriate time.
That being said, other than licensing issues, there is no need to fail back. If your primary server failed, the failover server becomes the new primary. When the failed server comes back online, it automatically goes into the failover role.
MS Licensing states that if a failover server is used for 30 days then a license is required. For that reason you would want to be consistent in your server roles.
hmm..i'll have to ask the people that wrote the front end program if it has a connection string option.
July 15, 2010 at 11:48 am
This is not required for the CA solution.
Thanks for the information, I'll have to keep it in case someone else I know may use it. 🙂
What a great place to get your questions answered! 🙂
Rudy
July 16, 2010 at 4:11 am
_taz_ (7/15/2010)
Robert Cary (7/15/2010)
Your connection string would look something like this:
Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;
The application will connect to whichever one is available, if the primary fails, the application connections will automatically switch to the failover.
The failback is not automatic (and I wouldn't want it to be) it's not 100% seemless in that any open transactions will be rolled back so it's best to schedule a failback for an appropriate time.
That being said, other than licensing issues, there is no need to fail back. If your primary server failed, the failover server becomes the new primary. When the failed server comes back online, it automatically goes into the failover role.
MS Licensing states that if a failover server is used for 30 days then a license is required. For that reason you would want to be consistent in your server roles.
hmm..i'll have to ask the people that wrote the front end program if it has a connection string option.
Note:
You have to be using ADO.NET or the SQL Native client to gain this functionality!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 16, 2010 at 6:50 am
ok i guess that answers it. it uses an MS Access type front end right now, i think their net release is designed in .Net. don't know if it's ADO.Net or not
July 16, 2010 at 12:29 pm
If the Access front end is an ADP, then you're probably using the native SQL and could expand on the connection string.
If you're using an MDB and are thinking about High Availability, perhaps you have outgrown your front-end technology.
SQL guy and Houston Magician
July 17, 2010 at 8:33 am
Robert Cary (7/16/2010)
If you're using an MDB and are thinking about High Availability, perhaps you have outgrown your front-end technology.
It probably wouldnt take too much work to convert this into a nice little VB.NET or C# front end!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2010 at 2:05 pm
My 2 cents
I never yet saw an automatic failover solution that made a good determination when to failover
I prefer for a fast manual failover
All servers are configured to perform all roles
and a set of scripts that allow the key operations to be performed very fast
another trick is delayed log shipping
do not restore the log files to the target server immediately - keep a 24 hour delay
in an emergency it makes it very easy and quick to restore to a point in time / mark
whereas a mirror will copy the problem ...
July 20, 2010 at 12:47 pm
doobya (7/17/2010)
My 2 centsI never yet saw an automatic failover solution that made a good determination when to failover
I prefer for a fast manual failover
All servers are configured to perform all roles
and a set of scripts that allow the key operations to be performed very fast
another trick is delayed log shipping
do not restore the log files to the target server immediately - keep a 24 hour delay
in an emergency it makes it very easy and quick to restore to a point in time / mark
whereas a mirror will copy the problem ...
I think a better way to look at HA is that high-availability solutions do what they're designed to do well; to provide continuity in the event of hardware/os/etc failure. There is always a very clear, black and white determination of when to failover - when the primary node is not responding. In my experience, both mirroring and clustering do an excellent job.
HA is not generally intended to encompass disaster recovery and/or human error (accidentially dropping a table) but HA is intendend to be part of a broader operations strategy.
SQL guy and Houston Magician
July 20, 2010 at 12:53 pm
the CA product has the ability to correct for an accidentally dropped table ( not that my users have direct access to the tables). it also has a rollback feature where if someone fubar's it at 3:30 i can roll it back to 3:29 and be going again
anyway, still taking everyone's input thanks!
measure (product) twice, cut (check) once....
July 20, 2010 at 1:29 pm
I agree that HA products are not meant for correcting human errors like dropped tables. But the HA solutions can be used for hardware/electrical/AC failure and as a disaster recovery (DR) solution.
We are using the CA product and have configured our DR site to point to the fail over node which will allow us to work at the DR site with the real time replicated data. Access to data at the DR site is not available until you fail over the primary server(s).
If the building was to have a major problem and we had to use the DR site our systems would be using the secondary node. We would just turn off the replication and make the secondary server the primary (just one click on the CA console). Once we could go back to our building or another location we could use the same product to replicate back (again one click). No tape restores required.
Our latest test show that this works very well. CA's product requires a lot less work in setting up and managing the replication. No log shipping or mirroring required and the the best part is no application changes either.
I would recommend getting the demo license and try it for yourself after you tried log shipping and mirroring. I think you will agree that it works great and give you more time to do other DBA work.
One last point. It also provides alerts and status via reports and/or email.
Rudy
July 20, 2010 at 1:36 pm
I forgot to mention that you may also want to look at LiteSpeed from Quest as it can do object level restores like tables, views and stored procedures to help you recover a drop object.
If you need to restore a table, for example, I would be very,very careful. You better know the system inside and out as you may need to restore other tables or views too. Some applications have a problem when you just restore a table but this is for another discussion.
Hope this helps
Rudy
July 22, 2010 at 4:23 am
Rudy Panigas (7/20/2010)
look at LiteSpeed from Quest as it can do object level restores like tables, views and stored procedures to help you recover a drop object.
Redgate SQLBackup Pro also offers this functionality!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply