Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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
0
398669
Asked:
398669
  • 5
  • 3
  • 2
1 Solution
 
MikeWalshCommented:
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.

0
 
MikeWalshCommented:
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.
0
 
398669Author Commented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MikeWalshCommented:
Pat,

I can appreciate your concern for looking good but there are a lot of variables and side issues here. If you are managing a large database that has to be up all of the time there are a lot of things to learn (more than what can be gleaned from a response to a question here).

If you can -NOT- have the database unavailable for any length of time than you really want to setup clustering. A failure in clustering would require some slight downtime (10seconds to a few minutes) while the server comes back online on the other node. It would also require some downtime while you switch the db into the clustered environment, but not doing so means you will not have the best strategy moving forward.

You can look at log shipping but that still requires some knowledge and experience to set up properly.

Restoring a backup is going to be the safest method short of any high availability options. Attaching a database from a server that essentially just died is not the cleanest and does not fall under the best practice. You have invested the money for a SAN which tells me you are from an organization that has some money. I think you need to research all high availability options and pick the right one.

Do you do Transaction log backups? It sounds like your system has to be up all the time and I would imagine that means you need to be able recover to within the hour(or more frequently) for a failure. Without Transaction log backups you are shooting yourself in the foot as far as that goes.

I would suggest getting SQL SERVER 2000 HIGH AVAILABILITY.. go buy it today and read it this weekend. If you want to look good you need to understand your options and implications of each option. I have that book on my cluttered desk open right now. It is an important resource and what you are looking for can't be fixed quickly.

I would also say that part of looking good at your company is being honest and knowing when to raise a flag. If you don't know something or need downtime to make something more available in the future, you need to take the hit of pride and make the mention to the higher ups.
0
 
Scott PletcherSenior DBACommented:
>> 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.
0
 
398669Author Commented:
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.
0
 
Scott PletcherSenior DBACommented:
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.
0
 
MikeWalshCommented:
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.
0
 
MikeWalshCommented:
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.
0
 
398669Author Commented:
Mike,

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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now