Full Text Catalog Organ Transplant: Moving SQL Server full text indexes

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 in Microsoft’s knowledge base article 240867 so I will not go into details on the procedure.  I hope this helps you if you are in the same situation.

SQL Server Issues

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.

SQL Server 2000

Microsoft SQL Server 2000

I am always trying to learn something. I really enjoyed learning about programming yesterday and now I want to shift my efforts onto Microsoft SQL Server. I have an install of SQL server 2005 at school and I might have a copy of SQL Server 2000 at home. I plan to study and work with it for the next few months. I have one book on it and some lab manuals. I might order a few more depending on my interest. It feels good to be learning. Ian and I are going to go to the library on Saturday to study. We will have lunch too. I sure hope Chris will join us. It would be fun for Ian to study for the MCSD, Chris to study for A+ and me to study SQL Server. Porter Library in Westlake as wireless internet and a nice atmosphere.
SQL Server

SQL Server Event

Richard Hale and I took my classes on a field trip to the Microsoft offices here in Cleveland for a SQL server event.  They gave us lunch at the event.  It was nice to see the Microsoft facilities but I was not overly impressed with the presentation itself.  The content was a little too technical for the students and some things were too complex for Richard and me to understand.  They showed a few charts without explaining the data which gave the impression that the data was not very supportive of their claims or that it was not very accurate.  The event did show the students the importance of knowing their acronyms and that there is a lot out there that they do not know.  It helped us all get a little more comfortable with SQL Server 2005 and that is certainly a good thing.  I am not sorry that I attended the event because it let us all get out of the classroom for a while which excited everyone.  Thanks Microsoft!I am looking into events for my night class to attend since this even took place during the morning class hours.  I am thinking of attending one of the local user groups since they usually meet around 6:00 PM.  I just need to find the right one and get approval from the college.

SQLserver2 SQLserver1