You might consider migrating the SQL Server database information into
metadata stored with the files in document properties. This could
probably be automated with a script if you have a large repository. Once
all of the metadata is with the files, MOSS search  will have easy access
to it. Are all of the resumes in the same format? If they're in
different formats, then you would likely need to search / organize on
differing document properties depending upon file  format (which would
not be ideal).

The same approach to migrating the documents to a SharePoint library
(and discontinuing the database) might also be appropriate.

The resumes are in different formats, such as MS Word, PDF, etc, so
the option of migrating the mata data to doc properties may not be ideal.

For the second option of migrating the resumes to doc library, can I
setup a search  scope pointing to that library? I tried to do so but
not successful. I created a search scope with the following rule:
Folder = http://crpspt03d/Unsolicited%20Resumes, (I also tried the
follwoing rule: Folder =
http://crpspt03d/Unsolicited%20Resumes/Forms/AllItems.aspx). I
uploaded 5 resumes to the library and the search scope shows 0 items
in the scope. Did I miss something here with regard to setting up the
search scope?

Another option is to develop custom protocol handler, but the
documentation on this is very thin...

Documents in a Document Library can be associated with common metadata
eliminating the tangential SQL database as Bradley suggested. SharePoint
will do the hard work. All you need do is upload the documents into a
library that includes the extended metadata attributes you want and then
populate those attributes with the information currently housed in the
SQL database. Shouldn't be a long or difficult process.

I like the document library idea and I have created some site columns
for metadata and created a content type named resume, and associated
the resume content type with my document library. Also my new scope is
working now since I need to crawl the default content souce. I was
able to add a new tab to the search  page too. Now I am trying to
figure out the how to modify the property picker dropdown on the
advanced search box webpart.

A slight change of topic, how much data (in terms of number of
items/physical size) can a document library hold? Should I use a
document center intead? For the situation, we have about 700,000
resumes already and growing. What is the impact on performance and
maintenance?

Here is a link that describes the limits of the old version of the product:
markharrison.co.uk/.../...point-scaling-limits.htm
which is pretty much what the product can do now. The gist is that a
document library can have millions of documents in it, but you will clearly
want to use lots of folders so that each folder doesn't have more than a few
thousand documents in it.

The capacity planning guidelines for WSSv3 and MOSS2007 are here:

WSS:
technet2.microsoft.com/.../0e13e879-0156-4e9f-b6
d5-abddcf326d211033.mspx

MOSS:
technet2.microsoft.com/.../eb2493e8-e498-462a-ab5d-1
b779529dc471033.mspx

I would strongly suggest you not place millions of documents in a single
library - not because it cannot be done, but because usability will
suffer greatly.

Definitely we need to have decent usability. There is only way to find
it out ...

Not sure anybody has any info on custom protocol handler
implementation for MOSS2007?

I don't think that this changed from v2 to v3.

Is the library part of an existing content source and crawl schedule? If
not, it needs to be included.

How often is the content crawled? Perhaps an incremental crawl hasn't
been conducted yet.

Let me know how you make out with that
property picker drop-down. We've contracted out a custom web part for
that.