IT Professional

Full Text Catalog Organ Transplant

Eric Vanderburg

Full text searching allows more complex searches such as word or phrase searching, inflection matching, proximity searching, and relevance matching to be performed on many different fields within tables.  In order for these searches to run SQL server builds and maintains a catalog.

We have such a large amount of data that rebuilding a full text catalog can take as long as 14 days.  We needed a way to be able to bring the full text catalog back without rebuilding it, essentially, I needed a backup.  Microsoft does not backup the full text catalog in normal backup operations and you cannot access it with software while the server is running because it is always in use.

You cannot use standard backup commands to back them up and Microsoft does not have any utilities available for the task either.  They do, however, have a guide for how to migrate it from one server to another.  We needed to do just that.  I call the process full text catalog organ transplant because organ transplants require the patient receiving the organ to have the same characteristics as the donor in order for the body to accept the new organ.

Full text catalogs need to have a destination system with many of the same characteristics as their previous system or they will not work at all.  The characteristics that must be similar are the SQL server version, full-text catalog folders and file locations, full-text catalog folder and file names and contents, and the same database id and table ids on both servers and databases.

The first few similarities are easy to recreate on the destination system.  The database id is a little more difficult as some DBAs do not even know what the id is for their databases.  You can obtain the database id of a SQL server database by issuing the following command:

Select db_id()

You have to be in the database you want to query first.  Use databasename will accomplish this.

This database number is generated when the database is attached to the instance.  Since the numbers have to match between source and destination, you need to create a situation where the database will receive the same database id when attached to the destination as it did at the source.

We found that you can reuse numbers from databases that have been detached so the process involves knowing the database ids of the databases you will import and then the ones in use at the destination.

If there is a database using the needed id, simply detach it, attach the imported database, and then attach the database you just detached.┬á The imported database will assume the detached databaseÔÇÖs id as long as it is the lowest available number.

If you have a gap in numbers such as in this example where there is no database with an id of 6 you will need to fill that gap first.  NOTE: master, tempdb, model, and msdb always have the numbers you see below.  You can do that easily by attaching the database with the id you want to use first so that it consumes that spot.

Database ID
master 1
tempdb 2
model 3
msdb 4
Database1 5
Database2 7
Database3 8
Database4 9

If you have a number of gaps and not enough databases to fill them you can create new databases with no data in them to consume database ids.

The rest of the process can follow the guide outlined here so I will not go into details on the procedure.  I hope this helps you if you are in the same situation.  http://support.microsoft.com/kb/240867/

Upcoming Articles and Presentations

Eric Vanderburg
There are quite a few upcoming things on the agenda. My article
"Implementation to Instruction ÔÇô Is teaching next in your career" will
be published in the June 2007 issue of Certification Magazine. This
article is on how IT professionals can move into a teaching career. I
am currently working on the next publication titled "Up to Date –
Earning the Certs the Market Demands" for the September issue. I will
be interviewing Lisa Spieth, president of LSRCS, on the certifications
that are most valuable in the market today.

Shinbokucon will be held on April 29. I will be presenting on "The
Culture and Impact of Groups in Japanese Society". The discussion
will center around how groups are such an essential and often
misinterpreted part of Japanese culture. I will also talk about how
groups have shaped the culture I see today and give some insight on
how to understand the Japanese group perspective.

I appreciate any feedback you may have on my articles once they are
published. Also, if you are able to make it to Shinbokucon, please
look up my presentation. I am available to chat in person afterwards
and the event should be fun.

ISO Recorder, MagicDisk and CD Burner XP Pro

Eric Vanderburg
I am always on the lookout for new programs that can solve my problems for free.  My copy of Nero is old and I want to upgrade but I do not want to buy a new copy.  I do not want to rip it off.  I want to find a legal solution.  I turned to freeware/open source programs for my solution.  I was also in need of a new ISO image making program because Nero did that for me as well.  Here are the programs I found.
1.┬á ISO Recorder V2 – This program is wonderful.┬á Once installed, you can right click on a CD drive in My Computer and select “Create Image from CD” and it will create an ISO for you.
http://isorecorder.alexfeinman.com/isorecorder.htm
2.┬á MagicDisk – This program works well with ISO Recorder.┬á This program creates virtual drives that you can load images into.┬á I used to use Alcohol 120 for this but why pay for a program if you can get it free.┬á MagicDisk sits in the system tray.┬á Simply right click on it and select Virtual CD-Rom and then mount.┬á Once you select an ISO, it will appear as the CD-Rom on your machine.┬á You will never have to hunt for that CD again with this program and you will not have to wear down commonly used CDs either.
http://www.magiciso.com/tutorials/miso-magicdisc-overview.htm
3.┬á CD Burner XP Pro – This program is a free burner to replace Nero.
http://www.cdburnerxp.se/

Odd form of Communication

Eric Vanderburg
I wanted to comment on an odd form of communication.┬á I looked at the writing inside the bathroom stall today and thought it funny how someone could write something and then another would comment.┬á The first person will most likely never read the comments written to their message and the same applies to anyone who replies.┬á Still, a one-way imprint of the thoughts of various bathroom users gets scrawled during the pursuit of bowel cleansing.┬á It is not a dialog because that involves two parties communicating back and forth but it is not simply a monologue either because that would involve one party communicating the entire time to others.┬á It is a collection of monologues or statements that meander around a central topic and quite interesting to think about (at least while residing in the stall)…

Domain Schema Sequence Numbers

Eric Vanderburg
I have not posted in a while because things have been so busy.  I think I will change the direction of the blog a little and start posting about the things I do at work without going into specifics, of course, because of my confidentiality agreement.
Lately I have been working on a problem with outlook web access.  My firefox clients can connect in without problems but IE clients stall at the loading screen.  The problems happened after I installed a new domain controller.  I tracked down problems with replication between the domain controllers on the schema partition and I have a problem with the unique sequence numbers not being the same on all DCs because of another backup process that restores active directory on the first domain controller.  This will disable inbound and outbound replication so that the USNs are not replicated.  I have not identified the backup process yet.  I may change permissions because it seems to run on an account that usually does not have access to the systemroot.  I found the successful backup entries in the logs for ntds being backed up.  I think that I will be able to move forward on the OWA problem once the replication problem is fixed.

Microsoft Database Administrator

Eric Vanderburg
Another certification.  I took the MCDBA (Microsoft Certified Database Administrator) tests and passed them all.  I finished the last one today.  I will take a break from certifications for a while and then pick up on them after a few months.  I may try for CEH (Certified Ethical Hacker) next.  We will see.  For now, I will enjoy the rest of my vacation. 

SQL Server Issues

Eric Vanderburg
I put SQL Server on a Windows Server 2003 virtual machine on my new 64 bit laptop but Enterprise Manager would not run.  After some searching, I found a guide that helped me fix the issue.  I had to change permissions on various registry entries in the HKEY_CLASSES_ROOT and then I had to manually register a few dll files.  Quite a number of services and processes had to be stopped during this procedure. 

New technology works well with new technology but new technology does not work well with old technology.  My situation here is SQL Server 2000 on Windows Server 2003.  If I was running SQL Server 2005, things would be different.  However, I want to learn more about SQL Server 2000 before I learn about 2005.  I like to know the history of technology and what changes were made in different versions. 

Everything seems to be working now.  Problem solved.

750 GB Drives

Eric Vanderburg
Seagate just announced the release of their 750 GB hard drive.  They also state that I should see 1 TB drives by the end of the year.  There is a SATA and an ATA (PATA) version for sale.  The drive is 7200 RPM and it has a 16 MB cache.  I am a hard drive fanatic so I am excited about the announcement and I may pick up one of those terabyte drives in December.