Because clustering is too expensive for my company I'm looking for an
alternative disaster recovery solution. I am considering setting up a
secondary SQL Server as a standby server in case the production server
crashes. The production server and the standby server would each have their
own internal hard drives partitioned as the C: drive containing only the OS
and application files. An external storage array would contain a D:
partition hosting the SQL database files and an E: partition hosting the SQL
transaction log files. If the production server crashes could I attach the
storage array to the standby server, power up the standby server, and use
this server until the production server is fixed? My main concern is that
the standby server may need files on the C: drive of the production server
in order to maintain data consistency. In this example the C: drives of the
2 servers are separate. Is there a need to replicate any files between the
2 C: drives in order to maintain data consistency? Is anybody out there
setup like this?

Thanks,
Mike

Bad Idea. Very Bad Idea. Moving SQL databases to another server after a
crash has a fairly low probability of success. This is especially true if
the disk controllers use on-board caching and haven't flushed it to disk.
Then you are practically begging for total disk corruption. You are likely
better off measuring and calculating your system availability based on your
current configuration. Then if management finds it unacceptable, then
present the next option for higher availability. If they refuse, at least
you have done the best you can with your resources and have successfully
managed expectations. Trying strange recoverability scenarios is OK.
Relying on them is professional suicide.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"Mike Colella" <mcolella (AT) sswhiteburs (DOT) com> wrote

Another option would be to replicate the data to a standby SQL Server using
1) Transactional Replication
2) Log Shipping
3) 3rd Party data replicating tools like Double-Take:
http://www.nsisoftware.com/pro/doubletake/

Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.

This posting is provided "as is" with no warranties and confers no rights.

Geoff,


Thanks for the reply. I was planning on using an HP MSA500 for the storage
array. It is a RAID array with redundant controllers built in. It connects
to the server via SCSI. So the disk controllers reside in the storage array,
not in the server, and they are redundant. Considering this, do you still
think it's a bad idea? Thanks again. I aprreciate your input.

Mike

"Geoff N. Hiten" <SRDBA (AT) Careerbuilder (DOT) com> wrote

Looks like the issue of disk corruption is off the table. I still belive
you have a low probability of success with databases that are not properly
closed. sp_attach_db wants to start with cleanly closed databases, either
from a controlled shutdown or a sp_detach_db execution. Moving a disk array
after a hard shutdown is a desperation move, not something I would bet my
job on working every time. Again, your job as DBA is to document your
recoverability and availability procedures for management and offer them
alternatives. Let them decide if there is a business case for justifying
higher availability. It is good to make the most out of your resources, but
sometimes you have to invest to get more. Higher availability is one of
those things you simply have to cough up the cash to get.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"Mike Colella" <mcolella (AT) sswhiteburs (DOT) com> wrote

Hi there,

what mechanism uses cluster service to recover database/log files after a
failover
from a failed cluster node? Isn't this also a takeover of possible not
controlled
shutdowned disk ressources? Is the cluster service containg some sort of
additional
layer in disk access that avoids the corruption of the disk ressources in
case of
failover / failback?

regards,

Stefan

All disk resources are 'shared' in a cluster. That is, they are constantly
physically connected to all host nodes. The cluster software arbitrates
ownership so that only one node at a time gets control. The cluster also
groups resources together so that the same SQL server always runs with the
same disks. When a failover occurs, it is like the same SQL server stopped
and restarted. It is not like attaching a disk array in an unknown state to
a new SQL instance.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"Stefan Dambeck" <dc7ds_nospam_ (AT) gmx (DOT) de> wrote