Database Scripts
This page summarizes the database scripts and folders that are used in the Install and Update powershell scripts.
Different powershell scripts are used depending on (a) if this is a new installation or an upgrade, and (b) if we need to create the entities directly from a SQL script rather than the SQL client - we may have to do this in certain cloud environments (Azure, AWS) where we may not have access to the SQL client.
- Use install.ps1 where we have access to the SQL client, otherwise use SqlScriptInstall.ps1.
- Use update.ps1 where we have access to the SQL client, otherwise use SqlScriptUpdate.ps1.
- database.sql - This will create the initial database without entities.
- datamodel.sql - This will define the database entities, tables, indexes, constraints. It will also load some minimum data.
- login.sql - This creates a pxstat login for use by the C# application.
- msdb.sql - This assigns roles to the pxstat user.
- All scripts in the Drop folder.
- All scripts in the Scripts folder between the previous version and the current version inclusive. These will be run sequentially by version.
- All of the scripts for entities (see below).
- CloudScriptInstall.sql - this script is recreated by the powershell script each time the powershell script is run. You must then run the sql script against the database using e.g. SSMS.
Database scripts for use by SqlScriptUpdate.ps1 - this script is recreated by the powershell script each time the powershell script is run. You must then run the sql script against the database using e.g. SSMS.
- CloudScriptUpdate.sql.
The contents of all of the following will be installed by the install powershell script or deleted and re-installed by the update powershell script.
- Stored Procedures.
- Types.
- Views.
- Jobs.
- Schedules.
- Drop - This folder contains the drop scripts for the entities.
This is a scheduled task for data maintenance. It runs the DataMatrixDeleteEntities_$(DB_DATA) job which itself runs the following stored procedures:
- Data_Matrix_DeleteEntities - This will delete all data and metadata related to a matrix that is itself soft-deleted.
- Security_Database_UpdateIndexes - This will reorganise indexes with a fragmentation greater than 51%
- System_Navigation_Keyword_Release_EliminateDupes - From 3.8.3 onwards, keywords are not checked for duplicates during loading (for efficiency reasons). This procedure will eliminate any keyword duplicates. pxstat_live Note - $(DB_DATA) refers to the database name, e.g. pxstat_live.
The schedule is controlled by the sql script, e.g.:
EXEC sp_add_schedule
@schedule_name = N'DataMaintenance_$(DB_DATA)' ,
@freq_type = 4,
@freq_interval = 1,
@active_start_time = 012000 ;
GO
In this case:
- Frequency type is daily, i.e. @freq_type = 4.
- Frequency interval is every 1 day, i.e. @freq_interval = 1.
- The schedule runs at time 01:20:00, i.e. @active_start_time = 012000.
These parameters can be changed to suit the specific installation.
