For more information on enabling or disabling change data cache for SQL Server, please see: http://technet.microsoft.com/en-us/library/cc627369.aspx.
Before you can install and configure Online Store and App Data Cache on the system, please ensure the following steps have been completed:
1. Configure SQL Server service to run using a Domain User Account
2. Start SQL Server Agent Service on SQL Server
A critical step in normal processing of Online Store and App Data Cache SQL job is generating HTML files that are consumed by the Google Mini search appliance. This step involves selecting data from the database and exporting it as an external HTML file. In order to do this, the user that runs the SQL Job needs to have read and wrote permission on the file destination location. This can be done with a domain user that has access to the database and has above mentioned permissions on file server. This account can be any user account on the domain or can be the same account that runs SQL Server service. The recommended approach is to set SQL Server service to run under a Windows service account and assign Read and Write permission for this account on file server location that will host Online Store and App Data Cache related files.
The SQL Server Agent Service needs to be started on SQL Server instance where the SQL job will be located. Not having the SQL Server Agent service running will return errors when creating the job. You may receive an error like this if you attempt to create the SQL Job on a server that has SQL Server Agent Service turned off.
In order for the Online Store and App Data Cache feature to work in Personify e-Business application, a System Administrator must complete the following steps:
1. Get the latest version of packaged script
2. Execute database upgrade script
3. Install/configure Database Objects Manually
· Install and Configure SQL Job
· Verify CDC SQL Jobs (if applicable)
Before we can move forward with adding new SQL Database related objects for Online Store and App Data Cache, we need to get the latest version of the package from Base. This can be located on the corporate “P” drive.
The next step is to upgrade the database by executing bat file UpgradeDatabase.CMD
This step involves executing SQL scripts available in sub-folder-...\Version.XX\Databases\SQL_EXECUTIVE_SCHEDULED_JOBS\OnlineStore.
The scripts in this folder must be executed when a new installation of Online Store is set up for Personify.
There are two scripts in this folder listed in the order of execution and one ReadMe file in this folder-
Read me file - ReadMe_FIRST.txt
SQL Job 1 - ADD_ONLINE_STORE_DATA_SQL_JOB.SQL
SQL Job 2 - APP_DATA_CACHE_CLEAR_DATA_SQL_JOB.SQL
It is important that you read the instructions in the ReadMe file before creating SQL Jobs.
SQL Job 1 - ADD_ONLINE_STORE_DATA – This SQL Job is to be used to insert data to tables related to Online Store.
Script Execution Instructions:
· This script must be executed by user that has sysAdmin privileges on SQL Server.
· Please specify SQL User that will be used to run the SQL Job. Replace value ‘sa’ with SQL User name in line 110.
· Please specify Database Name by doing a global replace of keyword "DATABASENAME" with name of Personify database here. This global replace should update keyword at 5 instances.
SQL Job 2 - APP_DATA_CACHE_CLEAR_DATA – This SQL Job deletes old data from table APP_DATA_CACHE_INFO. By default, this stored procedure will delete data older than 10 days.
Script Execution Instructions:
· This script must be executed by user that has sysAdmin privileges on SQL Server.
· Please specify SQL User that will be used to run the SQL Job. Replace value ‘sa’ with SQL User name in line 110.
· Please specify Database Name by doing a global replace of keyword "DATABASENAME" with name of Personify database here. This global replace should update keyword at 5 instances.
This option is applicable for Personify installations on Enterprise edition of SQL server. For clients using the Enterprise Edition of SQL Server, the following SQL jobs are created once script CHANGE_DATA_CAPTURE_ENABLE_ON_ONLINE_STORE_TABLES.SQL is executed as part of database upgrade process:
· cdc .<DatabaseName>_capture – This SQL job runs continuously and adds change data to system tables mentioned above
· cdc .<DatabaseName>_cleanup – This SQL Job runs daily at 2:00 AM and deletes data from change data capture related system tables that is more than 3 days old
Please verify the existence of these SQL Jobs on Personify database server.
When restoring a backup of Personify database after the database has been upgraded to 7.4.0, the above Change Data Capture related SQL jobs are not automatically created on the database server. In this case, SQL script mentioned below must be executed on Personify database.
...\ Change Data Capture\CHANGE_DATA_CAPTURE_ENABLE_ON_ONLINE_STORE_TABLES.SQL
For Personify implementations where Personify database is installed on the Enterprise version of SQL server, we recommend maintaining the same edition on other environments as well. These environments may include Development/QA/Testing, Performance Testing, Bug Fix, etc. Maintaining the same edition enables easy backup and restoration of the Personify database from Production onto other environments. The Developer edition of SQL server can be used in lieu of the Enterprise edition. This edition is free and can be used in all non-production environments without incurring licensing fees from Microsoft for SQL server installation. It is vital that you check your Microsoft Software License Agreement for pricing details related to SQL Server editions. More information related to this can be found by navigating to the following URL: http://www.microsoft.com/sqlserver/en/us/get-sql-server/how-to-buy.aspx
In the unlikely event of making a backup of the Personify database that will be restored on an instance of SQL Server that is not Enterprise, the Change Data Capture must be disabled on the Personify database prior to performing the database backup. This can be done by executing the following script:
DISABLE_CHANGE_DATA_CAPTURE_ON_DATABASE.SQL that can be found at the following location:
…\Release.XX\Package\Databases\Post_Deployment_Scripts\DISABLE_CHANGE_DATA_CAPTURE_ON_DATABASE.SQL
Failure to do so will throw errors similar to the message displayed here during the restore process:
Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 231
Could not update the metadata that indicates database DATABASE_NAME is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_MScdc_ddl_database triggers] 'drop''. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.
Msg 266, Level 16, State 2, Procedure sp_cdc_disable_db_internal, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_cdc_restoredb_internal, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_cdc_restoredb, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Line 1
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Location: execsql.cpp:344
Expression: exception != se_OK
SPID: 54
Process ID: 3204
Msg 3165, Level 16, State 1, Line 1
Database 'DATABASE_NAME' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database 'DATABASE_NAME'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Change Data Capture must be enabled again once the database backup process has been completed. This can be enabled by executing the SQL script below:
…\Release.XX\Package\Databases\Post_Deployment_Scripts \CHANGE_DATA_CAPTURE_ENABLE_ON_ONLINE_STORE_TABLES.SQL
All
data stored in CDC related tables will be lost when it is disabled on
Personify database and there will be no change tracking for the duration
of the database backup when until CDC is enabled again.
The
above approach is NOT recommended; it is strongly suggested that you maintain
similar editions of SQL server (mix of Enterprise and Developer) across
the entire organization as previously mentioned. It is important that
you check your Microsoft Software License Agreement for pricing details
related to SQL Server editions. More information can be found at the following
website: http://www.microsoft.com/sqlserver/en/us/get-sql-server/how-to-buy.aspx