Knowledge Center

Contact

K2 4.6 Database Considerations

KB Number: KB001281
Date Published: 4/10/2012 12:54:01 PM

Introduction

A new installation of K2 4.6 provides a single consolidated K2 Database (K2) that contains the same Schema definitions as an upgraded K2 4.6 installation. There are five FileGroups defined as part of the database schema:

FILEGROUP

DATABASE

FG_Server

Server

FG_HostServer

Category
Designer
Environment
Eventbus
HostServer
MessageBus
Relationship
Workspace
CustomUM
Dependency
EnvironmentServices
Utility

FG_Identity

Identity
Authorization

FG_SmartBroker

SmartBox
SmartBroker
SmartForm

FG_ServerLog

ServerLog

See the following link for more details:

http://msdn.microsoft.com/en-us/library/ms189126.aspx

 All the FileGroups are created in the DefaultDataPath, as shown in the following example:

Image

If the server you are hosting the database on has multiple disks, you may gain a performance advantage by moving each filegroup to a separate disk spindle. This operation should be done by an experienced Database Administrator. See the following articles on MSDN for more information:

http://msdn.microsoft.com/en-us/library/ms187087.aspx
http://msdn.microsoft.com/en-us/library/ms345483(v=sql.105).aspx

For more information regarding the K2 4.6 Database consolidation please see Database Consolidation Overview

Backing Up FileGroups

Warning: This is an advanced Microsoft SQL Administrator's task. There may be situations where backing up a single FileGroup is required, however we recommend that full backups be made to prevent data synchronization issues.

The following screenshot demonstrates backing up the K2HostServer database FileGroups:

Image

Rebuilding Indexes
Database indexes are utilized by K2 to improve performance.  On upgrades, the indexes for K2’s largest databases - K2Server, K2ServerLog and K2SmartBroker – may take a long time to rebuild.  K2 utilizes SQL Server jobs to execute stored procedures that rebuild the indexes.  These jobs can be run during upgrades by clicking Repair on the K2 Server > Rebuild Indexes node in Configuration Analysis.  To successfully create and execute the jobs, the SQL Server Agent service must be started and the Installer Account must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role.

For more information, please see Create Jobs: http://msdn.microsoft.com/en-us/library/ms186273.aspx

In environments that either cannot allow SQL Server jobs to be created or that have large databases that may require additional planning for downtime, indexes should be rebuilt manually by executing the following stored procedures as either part of a job (recommended) or directly (not recommended).

  • [K2Server].[DatabaseRebuildIndexes]
  • [K2ServerLog].[DatabaseRebuildIndexes]
  • [K2SmartBroker].[DatabaseRebuildIndexes]

Warning: The K2 Server should be stopped during the rebuilding of indexes. Depending on the size of your databases, the load on your SQL server, and the server’s processing power, these stored procedures may take a long time to complete.

Note: All other database indexes will continue to use the existing inline .sql logic and be upgraded automatically by the installer.

To check the status of the indexes rebuild, the Configuration Analysis should be used to run Analyze on the K2 Server > Database Rebuild Indexes node.  The Analyze node will have three  potential statuses. 

  • Passed –the indexes have been determined to have been rebuilt successfully.
  • Information – the indexes have started rebuilding but have not finished.
  • Warning – either the indexing jobs could not be created or the indexes have not started rebuilding.

Additionally, the following stored procedures can be executed and the results analyzed.  Any returned rows indicate the indexes that have not yet been built.  Zero returned rows indicate that all indexes have been rebuilt successfully. 

  • [K2Server].[DatabaseCheckIndexes]
  • [K2ServerLog].[DatabaseCheckIndexes]
  • [K2SmartBroker].[DatabaseCheckIndexes] 

Rebuild indexes Task

  1. The analysis tool does this indexing by creating jobs using the SQL SERVER AGENT
  2. The analysis tool checks for
    • Permissions to be able to do this
    • SQL Server Agent running status
    • SQL Server Agent installed or not
    • Job Status ( by re analyzing the task updates the progress ) 
  3. Reporting
    • Re-analysing  the task updates the progress
    • Errors will be thrown when any is received
    • Analysis will Pass only when all indexes are completed
PRODUCT
K2 blackpearl
K2 blackpoint
APPLIES TO
K2 blackpearl 4.6
K2 blackpoint
K2 blackpearl