Link to home
Start Free TrialLog in
Avatar of 398669
398669

asked on

Attaching an SQL data base that has not been previously detached.

I have two 2003 servers with SQL 2000. One is a standby.  I have one HUGE database on SAN storage that can only be seen by one server at a time. This is my question.
If the ACTIVE server gets hit by a sledge hammer and I switch to the standby can I
Attach the SAN storage  database to the standby SQL knowing that it was NOT Detached from the Server that got hit by the sledge hammer.

Thank You

Pat
Avatar of MikeWalsh
MikeWalsh
Flag of United States of America image

Hi Pat...

Couple things...

First and foremost, lock up your server room, and if necessary post a guard there to check for any hidden sledge hammers. Try not to leave any sledge hammers lying around in the server room in case someone sneaks in. Just be nicer to your employees and this should be less of an impact.

Secondly are we talking about a 3lb,6lb or 12lb sledge?

Seriously though.. It sounds like you want Failover Custering which you don't currently have. If that sledgehammer hits your server you can try and attach the storage to the standby SQL Server and then try and attach the data and log files to your SQL Server installation, but it is quite possible that you will have corrupt data and risk a potential of data loss.

What I suggest is use the Microsoft Clustering service included with the Enterprise edition of Wind Server 2003.. Setup SQL Server enterprise edition with clustering. Now you will have two servers already aware of the disks and when that sledgehammer comes a swingin' SQL will automatically failover to the still intact server. SQL will do this in a friendly way and you will have a far better chance of success.

So the quick answer is maybe, but why risk it? Setup clustering and implement a proper backup strategy. If that happens, you can restore the backups to the good server and apply transaction log backups.
Avatar of 398669
398669

ASKER

Thanks for the quick comeback people,  I do do a nightly backup so I could do a restore (and I may have to) but this is a very large data base and I'm looking for a quick recovery (so I'll look good).  As for the clustering,  I can't have the database unavailable for any length of time also I have never set up a cluster.

Pat
ASKER CERTIFIED SOLUTION
Avatar of MikeWalsh
MikeWalsh
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scott Pletcher
>> A failure in clustering would require some slight downtime (10seconds to a few minutes) while the server comes back online on the other node. <<

I've never seen it take a "few minutes" to fail from one node of the cluster to the other; if it takes more than 30 secs, I think there is something wrong.


You really do need a clustered environment.  However, that begins before SQL Server.  Windows itself must be set up as a clustered environment.  In fact, once that's done properly [which can be tricky, especially if the Win person hasn't done it before], the SQL install is no big deal, being sure to install to the virtual name, not the physical node name.
Avatar of 398669

ASKER

Mike,

You are right and I do have the book.  I will bring this up to the VIP's Mon.
Maybe they can get me two more servers or something.

Thanks. Pat
PS the looking good bit was just a joke.
A total of two servers is enough.  Two *additional* servers would be nice, so that you don't have to take the current one done to configure the cluster.
Scott -

I always have been under the impression that the "sla" for failover time from MSFT is up to a number of minutes (single digit). I have personally never seen a failover take more than about 20 - 30 seconds at the longest. It depends on starting SQL up, starting any other clustered services up, etc.
I agree with Scott. It would be ideal to get two new servers. Setup clustering on those,then migrate your database from the existing server to the new ones. Your entire setup has to be approved as a whole unit in order to be supported from MSFT. Work with your vendors on this.

You can then "retask" the existing server for another purpose if possible.

Of course that is in an ideal world. If you have to configure it in place, you have to configure it in place. Read that book again :) there is a nice section on Windows clustering and it all holds true but work closely with your systems people. You may even want to have a consultant come in and configure and train on the environment..

Benefit to the company is: an expert is setting this up. They are sinking a lot of software and hardware money into the solution, a week of a consultant isn't the end of the world if it means a better chance of not wasting that money should something get missed.

Benefit to you is: you get to learn from an expert and add to your comfort level. You will be able to better support and understand the environment. You will also get a nice education from it for your resume.
Avatar of 398669

ASKER

Mike,

     I have been asking for on site SQL support on an "as needed" basis for over a year.
Pat