Only users with SysAdmin privileges on the Personify database can execute these scripts.Manually installing the necessary database objects for Unified Search involves executing three (3) SQL scripts available in the “1_MANUAL_DATABASE_INSTALL” sub-folder of your Unified Search file package. The included scripts perform the following actions:
1. ADD_FILEGROUP_AND_FILE_TO_PERSONIFY_DB_FOR_UNIFIED_SEARCH.SQL
Adds the Database File Group and sets up the database Data File on your SQL Server database.
2. ADD_SQL_CLR_TO_PERSONIFY_DB_FOR_UNIFIED_SEARCH.SQL
Adds a SQL CLR Assembly to your Personify database.
3. USEARCH_CREATE_SEARCH_FILES_SQL_JOB.SQL
Creates a SQL Job that generates data for the Google Mini to crawl and index.
Execution of these scripts is necessary to perform a fresh installation of Unified Search on your Personify Application. More information on these scripts and steps for executing them appear in the subsections below.
Only users with SysAdmin privileges on the Personify database can execute these scripts.
All database tables containing data for Unified Search will be hosted in a separate data file within the Personify database on your SQL Server. This ensures the processing of records for Unified Search does not increase I/O contention on the remainder of the Personify data.
In order to accomplish this, you need to run a script as outlined below to perform the following functions:
· Create a new File Group with the name "USEARCH."
· Add a new data file to host data for thr Unified Search related tables.
To add the database file group and set up the database data file:
1. Navigate to the following file path in your release package:
...\UnifiedSearch\Personify.UnifiedSearch.Database\1_MANUAL_DATABASE_INSTALL
2. Open the following script in a text editor such as Notepad:
ADD_FILEGROUP_AND_FILE_TO_PERSONIFY_DB_FOR_UNIFIED_SEARCH.SQL
3. Within the file, locate the code below and replace the keyword ‘Database’ (as highlighted in red) with the name of your local Personify database.
DECLARE
@DATABASE_NAME VARCHAR(100) = 'DatabaseName'
4. Save and close the file.
5. Execute the script.
Setup for Unified Search includes adding a SQL CLR Assembly to your Personify database.
In order to accomplish this, you need to run a script as outlined below to create the following objects in your Personify database:
· Assembly Personify.UnifiedSearchCLR
· Stored Procedure USEARCH_ExportDataToUNC that references the SQL CLR stored procedure
To add the SQL CLR Assembly to your Personify database:
1. Navigate to the following file path in your release package:
...\UnifiedSearch\Personify.UnifiedSearch.Database\1_MANUAL_DATABASE_INSTALL
2. Open the following script in a text editor such as Notepad:
ADD_SQL_CLR_TO_PERSONIFY_DB_FOR_UNIFIED_SEARCH.SQL
3. Within the file, locate the code below and replace the keyword ‘DatabaseName’ (as highlighted in red) with the name of your local Personify database.
*/
USE [DatabaseName] -----<<<<<----- UPDATE DATABASE NAME HERE! GO
-----Step 1: SET TRUSTWORTHY ON
ALTER DATABASE [DatabaseName] -----<<<<<----- UPDATE DATABASE NAME HERE! SET TRUSTWORTHY ON
GO
4. Save and close the file.
5. Execute the script.
The last step in manually configuring your database for Unified Search is to create a SQL Job that generates data for the Google Mini to crawl and index. The SQL script associated with this SQL job is "USEARCH_CREATE_SEARCH_FILES_SQL_JOB.SQL".
By default, this SQL Job is currently set to run every 4 hours starting 3 AM. To configure this script based on your association, please refer to Changing the Schedule and Frequency of SQL Job.
To install the SQL job that generates data for the Google Mini crawl:
1. Navigate to the following file path in your release package:
...\UnifiedSearch\Personify.UnifiedSearch.Database\1_MANUAL_DATABASE_INSTALL
2. Open the following script in a text editor such as Notepad: USEARCH_CREATE_SEARCH_FILES_SQL_JOB.SQL
3. Within the file, locate the code below and replace the keyword ‘sa’ (as highlighted in red) with the logged in SQL User Name of your local Personify database.
EXEC @ReturnCode = msdb.dbo.sp_add_job
@job_name = N'USEARCH_CREATE_SEARCH_FILES'
, @enabled = 1
, @notify_level_eventlog = 0
, @notify_level_email = 0
, @notify_level_netsend = 0
, @notify_level_page = 0
, @delete_level = 0
, @description = N'SQL Job that will run procedures to create files for
Unified Search.'
, @category_name = N'[Uncategorized (Local)]'
, @owner_login_name = N'sa' -----<<<<<----- UPDATE DOMAIN ACCOUNT NAME HERE!
, @job_id = @jobId OUTPUT
4. Locate this next bit of code below and replace the keyword ‘DatabaseName’ (as highlighted in red) with the name of your local Personify database.
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @jobId, @step_name = N'EXEC_STORED_PROCEDURE'
, @step_id = 1
, @cmdexec_success_code = 0
, @on_success_action = 1
, @on_success_step_id = 0
, @on_fail_action = 2
, @on_fail_step_id = 0
, @retry_attempts = 0
, @retry_interval = 0
, @os_run_priority = 0
, @subsystem = N'TSQL'
, @command = N'EXEC dbo.USEARCH_JOB_RUNNER GO'
, @database_name = N'DatabaseName' -----<<<<<----- UPDATE DATABASE
NAME HERE!
, @flags = 0
5. Save and close the file.
6. Execute the script.