Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nwalter
Starting Member
39 Posts |
 Posted - 2009-02-13 : 13:12:04
|
| This is more of a suggestion unless what I want to do is actually possible and not documented anywhere.I've been working on setting up database mirroring. And I really like the ease of failing over a database when you have a witness present. However, I do not necessarily like the fact that the database moves around so freely with automatic failover present. I've found that we have a number of services and other processes that are simply too dumb to be able to support database mirroring. Because of this, it is somewhat important that the database remain relatively static on one server unless there is a real failure of the primary server in which case the services can be manually re-configured or we can simply do without them as they are not that critical.So, I would like to be able to either use database mirroring with a witness but with a manual failover option. So that the database will stay put during a reboot or other maintenance of the server, and so that in the event of a failure i can simply connect to the backup server and tell it to failover in SSMS. This would also make it easier for non-dba's to deal with a failure, Open SSMS - Select Mirroring Properies, Click Failover. VS write SQL query to break mirror relationship and restore database with no recovery. It would also make it easier to fail back to the original server once it has been restored/repaired instead of having to go through the whole backup, copy backup, backup tran log, copy backup tran log, restore both, re-configure mirroring session etc.The alternative to that would be to have the option to set a preferred server in the mirroring session. Such that the database is always automatically moved back to the designated server when/if that server becomes available again. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-13 : 13:38:57
|
| Why don't you just use database mirroring without a witness? It is only 1 or 2 commands (depending upon if you are using sync or async) to failover to the mirror without a witness. We use asynchronous database mirroring and can failover to the mirror in just a few seconds. Here is how easy it is:-- Run this on PrincipleALTER DATABASE dbName SET SAFETY FULL -- only needed if using asyncgoALTER DATABASE dbName SET PARTNER FAILOVERgoTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-02-13 : 13:53:37
|
| Actually, my experience has been more like this when the primary server is offline.ALTER DATABASE dbname SET PARTNER OFFRESTORE DATABASE dbname WITH RECOVERYAnd then when you want to go back when the server comes back online, you have to run a full backup against the current server, copy it over the network, restore it, run a transaction log back up, restore that, then go back and reconfigure the mirroring session all over again. And then when you are done with that you can finally failover the database back to the original server.If you are dealing with a large database, or two servers over a WAN this could take you hours or even days to perform start to finish. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-02-13 : 14:06:52
|
| True, in our case we wouldn't even bother failing over the server during a maintenance window. We're strictly going for disaster recovery, so my tests so far have involed simply stopping the SQL server service on the primary server and recovering from there and hence the methods and pains i've found when not using a witness with automatic failover. I cringe at the thought of having to test it, or actually use it in production, because of the sheer amount of time it takes to set up the mirroring session after a failure.I'm also the only DBA we have so I cringe at the thought of the rest of our support staff having to go through this entire process if the system were to fail while I was say on a boat in the middle of the carribean. Was hoping DB mirroring would really be able to give me the whole one click failover type scenario, but unfortunately with some of our more dumb systems the automatic failover between systems just doesn't work out very well as I can't have the servers switching on me because we rebooted one for a patch. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-13 : 14:11:48
|
| For your situation then, I'd suggest not using a witness. You only have to go through the pain that you've seen when the principal database is unavailable completely due to an unplanned event. For your testing, I'd just use the command(s) that I posted so that you don't have to keep setting up database mirroring again. Those are the commands you'd run when the principal database is available and you want to failover to the mirror database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
DB Mirroring without automatic failover