Hardware Setup For A Database Server With Full-text Index
Oct 15, 2007
Hello Everyone,
I am developing a asp.net application using sql server 2005 with full-text searching.
Question
I am looking for a recommnded hardware setup for the databases server. I know there is no one answer but I am wondeirng if there is a microsoft guide that I could use.
If anyone has any experience, i would welcome your comments as well.
I am estiamting that the full text index will be about 2-4 gb in size, most of the activity will be full-text searches.
Also, any good benchmarks out there for full-text searching or sql server tps ?
Can anyone please explain the proper precedure for copying a SQL Express database between two instances?
I am accessing the database without problems from a local web application. And I want to copy the database to a SQL Express instance on another server, running the same web application.
I run into two problems every time I copy:
1) Orphaned users. I have to drop the database users and the re-map the server users to database users.
2) The full-text indexes are not available after copy, so I have to drop and re-create the indexes and the catalog.
Is there any way to move a db with full-text indexes from one physical machine to another without having to drop catalog/indexes and the re-create them? Cheers! /Eskil
I had my database in 6.5 which i upgraded to 7.0 using SQL Server upgrade wizard.Then I created full text catalog. When I say incremental population It gives me warning that You can create full text indexes but can not execute queries against it as the database is still in SQL server 6.5 mode.What is the reason behind this?
I am using-- exec sp_fulltext_database 'enable' to enable fulltext indexing on a database but query does not return any results, it just keeps on executing. I waited for about an hour and then cancelled. I dont know what is wrong. Can anybody please help me....
Hi, I have visual studio 2005 with the sql server express edition that is installed by default when installing vs2005. I am used to work from within vs when I want to create and work with databases for my web sites (I dont have visual manager installed). I would like to enable full text search on a table's column.
Is there an easy way to do that from within vs or is it necessary to install another copy of sql server express with advanced services as well as sql server managment studio express? How can I know if full text search is enabled?
I tried too much to create FULL TEXT INDEX by using SQL Server 2005 Management Studio Express, it returns this ERR MSG:
Informational: No full-text supported languages found. Informational: No full-text supported languages found. Msg 7680, Level 16, State 1, Line 1 Default full-text index language is not a language supported by full-text search
This problem dos not come when I use Microsoft SQL Server 2005 Management Studio, to create FULL TEXT INDEX!
My DB collation is: "Arabic_CI_AS" But I don€™t need this, I can use English Language.
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Quick question about the primary purpose of Full Text Index vs. Clustered Index.
The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance. Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself?
I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database.
Is this correct?
I am kind of confused on why you would use full text index as opposed to clustered index.
I have a database developed using SQL Server Management Studio Express with Full-Text searching. I can go into the Object Explorer and right click on my database and choose Properties, select File and the 'Use-full-ext indexing' is checked.
I'm not sure how I create an index for a table, can someone help? I want to create an index for table 'tblLogNotes' and have the following columns:
note_id = (PK, int, not null) note = (varchar(3000), not null) log_id = (FK, int, not null) operatorid = (int, not null) note_created = (datetime, not null)
I know I did something but not sure what I did, but on one of my tables, when I right click on table and choose 'log_note', I can see that Full-text Specification = yes, (is full-text indexed = yes), full-text type column = <none> and language = English (United States).
Table 'tblLog' has the following columns: log_id - (PK, int - not null) log_note (varchar(3000), not null) tag_number(varchar(50), not null)
However, when I try the following query, I get nothing:
SELECT * FROM tblLog WHERE CONTAINS(Log_note, '"pump*"')
I am trying to enable full text index on all of my databases but notices that it is grayed out. Also the service Full Text Index service msftesql.exe is not installed. I have tried running the install again but it says nothing has changed on the machine so it just stops the install... Hope someone can help me.
Could Full Index option only be configured during installation? When Itry sp_fulltext_table on a table, I get the message that full text isnot enabled for the system.--sharif
I am having an issue creating full indexes on both instances of an ActiveActive SQL Server 2000 cluster. I get the following error when trying to create the catalog:
Access is denied to $SQL PATH$, or path is invalid. Full-text search was not installed properly.
Does anyone have any suggestions that I may use to create the indexes?
First of all I’m new to MS SQL, I did work with mySQL
Table name db (real db has 12 columns) Id c1 c2 c3 1 tom john olga 2 tom john olga bleee
I enabled full text index on all columns
Problem when I do search like this: SELECT * FROM db WHERE CONTAINS(*,'�tom� AND “john�')
It will return only one row (id 2) – I understand that the full text search does look only at one column at a time because it did not return row #1
Anyway I thought that I can add extra column c4 and when user enters new data it will save data from columns c1, c2, c3 to c4 (varchar(750)) and then I will do search only on c4 – this way it will work the way I want.
1) Is there any better way to do this? 2) How do I sort results by “rank� with SQL
I have a table with 13,000,000 records. I want to generate a full-text index on one column (a varchar 2000). I am able to define the full-text index, but when I click on "Start Full population", there is virtually no activity (no disk activity, no CPU activity, very little to indicate anything is happening.
When I check the properties of the catalog, it shows 1 MB size and 0 records in the catalog. The status of the catalog is "idle" and the display in EM shows that the last full population occurred at (about) the time that I generated the population request. I have generated the request by using EM (right click on table) and through SQL Agent with the same result (no catalog generated).
I am running SQL 2000 (SP4) on Windows 2000 (SP4) with 4 GB RAM and sufficient disk space available. I have enabled the full-text service and verified that it is running (I have stopped and restarted it as well).
I have worked with Full Text indexes before and never had any kind of issue before. Any thoughts or suggestions would be welcome.
I am a developer, and I have a disagreement with my DBA. He has convinced management, that SQL 2005 FullText Index is so much overhead on production, that it should NEVER be used under any circumstances. We have a Cold Fusion site, and somehow he convinced management that a bunch of Cold Fusion developers can create a more efficient full text indexing method than by using SQL 2005 Full Text Index. So now we have to come up with a method for doing this in Cold Fusion.
Is there any statistical data that could possible support or refute his statements? Thanks
I build some t-sql code to check if full text is installed on the sql server. If not, some sql statements must be not executed. Here is my code:
if (select serverproperty('IsFullTextInstalled')) = 1
Begin
EXEC sp_fulltext_database 'enable'
CREATE FULLTEXT CATALOG [...] WITH ACCENT_SENSITIVITY = OFF AS DEFAULT
CREATE FULLTEXT INDEX ON dbo.Test (Name LANGUAGE 0, Description LANGUAGE 0) KEY INDEX IX_Test_1 ON [...] WITH CHANGE_TRACKING AUTO ALTER FULLTEXT INDEX ON dbo.Test ENABLE
End
Statement 1 and 2 is not executed, but for statement 3 the server throws the following error: Full-Text Search is not installed, or a full-text component cannot be loaded.
I don't know why the server tries to execute statement 3, because it is in an if statement.
I am tring to use full text indexing. I have created an index and catalog. I can search on stuff that was entered before I created the index using contains or freetext but if I search on anything afterwards the results come up blank. I have created the following database and tables. I am using sql express with advanced services. The primary key I went in after I created the tabled and modified the row to increment by 1
create database RSDB2
use rsdb2
create table support
(ftid int NOT NULL PRIMARY KEY,
problemId varchar(50) NOT NULL,
problemTitle varchar(50) NOT NULL,
problemBody varchar(max) NOT NULL,
lOne varchar(50),
lTwo varchar(50),
lThree varchar(50),
lFour varchar(50),)
create fulltext catalog RSCatalog AS DEFAULT
create unique index ui_Support on support(ftid)
create fulltext index on support(problemBody)
key index PK__support__7C8480AE on RSCatalog
insert into support(problemId, problemTitle, problemBody)
values('win1001','testing outt he database','testing out the databases full texting capabilities again.')
select * from support where freetext(problemBody, 'testing');
I have built a Full-Text Index on a indexed view. I'd like to replicate this indexed view from a control database to a live database. What values should I specify for @type and @schema_option for the sp_addarticle sproc to ensure the Full-Text Index is still functional after it's replicated?
For now, I have set @type="indexed view logbased" and @schema_option=0x90000F3. Are these values correct?
I've got a full text index working with a "CONTAINS" clause in the SQL. I'm looking for the character that I can place in CONTAINS(*,'WHATHERE') that will return everything. I've tried "*" and "%" but none of them will do it. Does anybody know?
if I want to use full text search in my program, can I include Full Text Search service in my program setup silently in the same way as including SQL Express and .Net Framework to the setup project, Or I have to Install MS Sql Server 2005 on my customer computer?
Are there any examples of maintenance(ReBuild FULL or Incremental) for Full-Text indexes? Are there any index integrity checks that can be done? What is the best way to backup a full-text index?
I've been trying to create a full-text index using Enterprise Manager. If I right-click on the table, "Full-Text Index Table" is grayed-out. If I right-click on Full-Text Catalogs, "New Full-Text Catalog" is grayed-out. If I try to start the Full-Text Indexing Wizard it tells me that the "Full-Text Server service needs to be running." The SQL database is on a remote server, and the host assures me that everything on their end is working properly. Does anybody know what I have to do??
hello, I'm looking for a way to populate my index on insertion but not on updates. I tried each possible value for CHANGE_TRACKING MANUAL|AUTO|OFF and it automatically takes every changes that have been made before in account. is there a way to "flag" the rows that I don't want the server to re-index (i.e. updated rows).
This files are generated durin full text search. now i have doubts regarding this, 1) Can we referrence this files directly 2) Where it will be located in our system? 3) is it loaded for each Full Text Index we created for the table. 4) How this file are used in Full Text Search.
I have recently upgraded our Database server from 2005 Standard to 2008 R2 Standard.I am having a problem while replicating Full Text Index in New Infrastructure.
Full text Index was working fine in old infrastructure. Replication scenario for Old infrastructure Publisher: SQL Server 2005 Standard Distributer: SQL Server 2005 Standard Subscriber: SQL Server 2005 Express with Advance Services Replication scenario for New Infrastructure Publisher: SQL Server 2008R2 Standard Distributor: SQL Server 2008R2 Standard Subscriber: SQL Server 2005 Express with Advance Services/ SQL Server 2008R2 Standard
Whenever I try to replicate Full text Index by selecting  "Copy Full Text Indexes"= "True" article property in Replication and create snapshot it will automatically set to "Copy Full Text Indexes"= "False" whenever I reopened publication properties or snapshot is created.Is SQL Server 2008 R2 Supports full text Index replication to SQL Server 2005.Do I missed some settings while setting up publication for Full Text Index.
hi there! how can i get the information represented in the table?
Keyword ColId DocId Occ
Crank
1
1
1
Arm
1
1
2
Tire
1
1
4
Maintenance
1
1
5
Front
1
2
1
Front
1
3
1
Reflector
1
2
2
Reflector
1
2
5
Reflector
1
3
2
Bracket
1
2
3
Bracket
1
3
3
Assembly
1
2
6
3
1
2
7
Installation
1
3
4 The Keyword column contains a representation of a single token extracted at indexing time. Word breakers determine what makes up a token. The ColId column contains a value that corresponds to a particular table and column that is full-text indexed. The DocId column contains values for a four-byte integer that maps to a particular full-text key value in a full-text indexed table. DocId values that satisfy a search condition are passed from the MSFTESQL service to the Database Engine, where they are mapped to full-text key values from the base table being queried.
The Occ column contains an integer value. For each DocId value, there is a list of occurrence values that correspond to the relative word offsets of the particular keyword within that DocId. Occurrence values are useful in determining phrase or proximity matches, for example, phrases have numerically adjacent occurrence values. They are also useful in computing relevance scores; for example, the number of occurrences of a keyword in a DocId may be used in scoring. http://technet.microsoft.com/en-us/library/ms142505.aspx
The following queries on a fulltext index is returning different results.
select CustomerNameLocal from dbo.Customers where contains (CustomerNameLocal,'A.C.E') -- returns 1388 records
select CustomerNameLocal from dbo.Customers where contains (CustomerNameLocal,'ACE') -- returns 1388 records
select CustomerNameLocal from dbo.Customers where contains (CustomerNameLocal,'ace') -- returns 1388 records
select CustomerNameLocal from dbo.Customers where contains (CustomerNameLocal,'a.c.e') -- returns 22 records
Can someone let me know why the last query is retuning only 22 records. Since search on ACE and ace returns the same number of records - I guess there shouldn't be any problem with case sensitivity.
