Solved

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

Posted on 2006-06-16
10
365 Views
Last Modified: 2013-11-15
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
Comment
Question by:398669
  • 5
  • 3
  • 2
10 Comments
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16923164
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16923171
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
 

Author Comment

by:398669
ID: 16923265
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
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 500 total points
ID: 16923375
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 16923502
>> 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:398669
ID: 16923516
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 16923542
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16923562
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16923592
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
 

Author Comment

by:398669
ID: 16923729
Mike,

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

By default, Carbonite Server Backup manages your encryption key for you using Advanced Encryption Standard (AES) 128-bit encryption. If you choose to manage your private encryption key, your backups will be encrypted using AES 256-bit encryption.
Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now