Configuration: Database configurations

By default, Cayosoft Guardian uses Microsoft SQL Server Express LocalDB as a database to store all configuration and collected data. This database has limitations, such as including a maximum database size of 10 GB. In the production environment, it is strongly recommended to use an external MS SQL database (such as Microsoft SQL Server Standard 2019 Standard or Enterprise) for storing history data. If you have configured Cayosoft Guardian with a local database, you can later copy your configuration data to the new database, so you don't need to configure your Guardian installation once again.

Scenario: Connecting to a database on the first run

1. Login to Cayosoft Guardian web portal and navigate to Settings > System databases.
2. Select Configuration database.
3. Select one of the options, for example, select Connect to an on-premises SQL database.
4. Provide connection options and click Next.
5. Check the settings and confirm.

Scenario: Changing local database to external server

By default, the Cayosoft Guardian service uses a Local System Account, so if you're planning to use Windows Authentication, a machine account should be created in Microsoft SQL. You can also create SQL account.

There are two ways to create a SQL account and database that can be used by Cayosoft Guardian. First, an account can be created manually using the Microsoft SQL Management Studio. Second, those familiar with running SQL Scripts can use the script provided below to create the account. In both cases, the database and SQL account with required permissions will be created.

NOTE: Choose either manual or script creation as you do not need to execute both.

Preparing SQL account and database manually

1. Open Microsoft SQL Server Management Studio, navigate to Security > Logins, right- click, and select the New Login... command.
2. Create a new SQL login account with the domainName\machineName$ name.
3. Navigate to Databases, right-click, and select the New Database... command.
4. On General page, specify Cayo.Guardian as Name and the created SQL login account as Owner.
5. On the Options page, change the value of Is Read Committed Snapshot On option to True.
6. Navigate to <Servername>, right-click Properties.
7. Open the Database properties and on Permissions page, click View server permissions.
8. Select the created Guardian SQL login account in the logins list.
9. Enable the View any definition and View server state permission checkboxes.

Prepare SQL account and database by script

1. Declare variables for file paths at the top of the script to simplify edits and avoid accidental changes in sensitive parts.

   TIP: Replace domainName\machineName$ with your actual domain and machine account, and adjust file paths as needed.
   

   Copy

   -- Set file paths as variables for easy adjustment
   DECLARE @DataFile NVARCHAR(260) = N'E:\Guardian\Cayo.Guardian.mdf';
   DECLARE @LogFile NVARCHAR(260) = N'E:\Guardian\Cayo.Guardian_log.ldf';
   
   -- Create the database if it does not already exist
   IF DB_ID(N'Cayo.Guardian') IS NULL
   BEGIN
       CREATE DATABASE [Cayo.Guardian]
       ON 
       (
           NAME = N'CayoServiceMgr',
           FILENAME = @DataFile
       )
       LOG ON 
       (
           NAME = N'CayoServiceMgr_log',
           FILENAME = @LogFile
       );
   END
   

3. Create a login for Guardian machine account and grant permissions. Replace domainName\machineName$ with your domain and machine names.
   

   Copy

   
   -- Create a login for Guardian machine account 
   CREATE LOGIN [domainName\machineName$]
    
   FROM WINDOWS WITH DEFAULT_DATABASE=[Cayo.Guardian], DEFAULT_LANGUAGE=[us_eng]
   
   -- Grant database owner permissions 
   USE [Cayo.Guardian]
   CREATE USER [domainName\machineName$] 
       FOR LOGIN [domainName\machineName$]    
   EXEC sp_addrolemember N'db_owner', N'domainName\machineName$'
           
   -- Grant permissions to read system objects
   USE [master]
    
   GRANT VIEW ANY DEFINITION TO [domainName\machineName$] 
   GRANT VIEW SERVER STATE TO [domainName\machineName$]

1. For Azure SQL PaaS, to create a SQL-authenticated user with db_owner permissions:

CREATE USER sqlAccess FOR LOGIN sqlAccess
ALTER ROLE db_owner ADD MEMBER [myAppName];

Changing current Guardian database

1. Login to Cayosoft Guardian web portal and navigate to Settings > System databases.
2. Select Configuration database.
3. In the opened dialog, specify parameters:
   • SQL Server name like Host\Instance
   • Database name (default is Cayo.Guardian)
   • Select SQL Server authentication if you need. If you created machine SQL account, leave the checkbox not selected.
4. The service needs to restart after changing the database settings.
5. Leave Restart service when the configuration is applied selected if you want service to perform restart automatically after the settings applied.
6. Select one of the following:
   • Copy mandatory configuration data – Copies essential settings like Domains, Credentials, and Alert Settings
   • Copy all configuration data – Creates a full database copy including Job History, Alert History, Message Log, etc.
   • Do not copy configuration data – Creates a blank database. The user will have to configure the database from scratch
7. Select I confirm this database is not used by another Guardian Service option if the target database was copied or moved from another installation of the Cayosoft Guardian. This Guardian Service would take the ownership over this copy of the database even if it contains information about previous owner.
8. Click Yes to complete the operation.
9. Wait until service restarts.

Scenario: Move local database to another drive

By default, Guardian stores databases in C:\ProgramData\Cayo Software\Guardian\database. If you have disk space limitations on your C: drive and want to move the database to another location, you can do it in the Web PortalStorage Settings.

1. Login to Cayosoft Guardian web portal and navigate to Settings > System databases.
2. Select Configuration database.
3. Click Create SQL database.
4. In the opened dialog, specify parameters:
   • Set SQL Server name to (LocalDb)\CayoInstance
   • Set Database name to preferred database name
   • Set Database path to preferred path, for example E:\Guardian
5. Select one of the following:
   • Copy mandatory configuration data – Copies essential settings like Domains, Credentials, and Alert Settings
   • Copy all configuration data – Creates a full database copy including Job History, Alert History, Message Log, etc.
   • Do not copy configuration data – Creates a blank database. The user will have to configure the database from scratch.
6. Enable the Create the specified database if they are not present on the SQL server option. In case this option is disabled, and the database is missing, the action will fail with "Database '{0}' does not exist. Create the DB manually or enable the automatic database creation option."
7. Click Yes.
8. Wait for the configuration migration to complete. Guardian will automatically switch to the new database and resume data collection. You may need to refresh the browser to get back to the web portal.

Scenario: Change Cayosoft Guardian database through appsettings.json

You can specify external database settings or revert service to use local database in application settings.

1. Stop Guardian service.

2. Open appsettings.json in the Cayosoft Guardian installation folder. By default, it is located here: C:\Program Files\Cayo Software\Guardian\appsettings.json.

3. Modify the ConnectionString parameter in the DataBaseSettings section: Default connection string example before editing:

   Copy

   "Data Source=(LocalDb)\\CayoInstance;Initial Catalog=Cayo.SvcMgr;AttachDbFil

   Customized connection string examples after editing:

   Copy

   "Data Source=dch1.hercules.milkyway.cayotest.net\\SQLEXPRESS;Initial Catalog

4. Start Cayosoft Guardian service.

Scenario: Moving data from a local database to an external SQL server

NOTE: Only configuration data will be moved automatically. To preserve Change History data, create a new History database on external SQL and attach your previous LocalDB as an archive.

To move a database to an external SQL server:

1. Login to Cayosoft Guardian web portal and navigate to Settings > System databases.
2. Select Configuration database.
3. Select a database to be moved.
4. Click Create SQL Server.
5. Specify the following information:
   • SQL Server name
   • Database name
   • Database path
6. Specify Additional parameters.
   • Copy mandatory configuration data – Copies essential settings like Domains, Credentials, and Alert Settings
   • Copy all configuration data – Creates a full database copy including Job History, Alert History, Message Log, etc.
   • Do not copy configuration data – Creates a blank database. The user will have to configure the database from scratch.
7. Click Yes.

Moving historical data to external SQL

When transitioning from a local database (LocalDB) to an external SQL Server instance, only configuration data is automatically moved.
If you need to retain historical Change History data:

1. Create a new History database on the external SQL Server. You can configure this in Settings > Service Settings > Database Configuration by selecting the external SQL instance and specifying a new database name for the History database.

2. Make the new History database active by saving the configuration. After saving, Guardian will begin using the new database for all subsequent change history collection.

3. Attach the previous LocalDB as an archive database. This lets you preserve access to historical data without merging it into the active history database. You can query or review this archived data manually when needed.

After you switch Guardian to the new external History database, connect the old LocalDB-based history database as a read-only archive so you can still access older records from the Archive.

1. Open the Cayosoft Guardian web portal.
2. Go to Settings > Archive Databases.
3. Click New.
4. In Connection settings, enter:
   • SQL Server name:
     • If the old database is still on the Guardian host as LocalDB: (LocalDb)\CayoInstance
     • If you attached the .mdf/.ldf to an external SQL instance: use that SQL host\instance name
   • Database name: select/enter the previous History database name (the one that contained your historical Change History records before you switched History collection to the new DB).
   • Authentication: use the same authentication method you use for your other Guardian databases (Windows or SQL authentication), based on how your SQL access is configured.
5. After adding the database, enable Show data on Archive pages if you want this archive included in Archive searches.

For more details about archive database behavior (default archive vs. read-only archive, searching across archives), see: Configuration: Managing archiving in Cayosoft Guardian .

To migrate your database manually:

1. Stop the Cayosoft Guardian service.

2. Detach the old LocalDB database using SQL Server Management Studio (SSMS).

3. Copy and attach the database file (.mdf and .ldf) to the target external SQL Server instance.

4. Update Guardian’s database configuration to point to the newly attached external database.

5. Restart the Cayosoft Guardian service.

Notes

• Manual detach/attach should be performed only if you understand SQL Server administration.

• Always back up both databases before performing this operation.

IMPORTANT: On the first data collection run after switching to a new external SQL database, Cayosoft Guardian will perform a full synchronization of object data. This process may take longer than usual due to the volume of data being collected.

Scenario: Moving data from a local database to Azure SQL Server

NOTE: Entra ID authentication for the Azure SQL server is not supported via Cayosoft Guardian web UI. Only SQL authentication is supported in the web UI. However, you can configure Entra ID authentication manually. Configuration of Azure SQL with Entra ID authentication is described in article:Passwordless connection for Azure SQL in Cayosoft Guardian.

To move a database to an Azure SQL server:

1. Open Cayosoft Guardianweb portal.
2. Navigate to Settings > System Databases.
3. Select a database to be moved.
4. Click Create Azure SQL Database.
5. Provide SQL Server name, login name, and password.
6. Specify Additional parameters.
7. Select one of the following:
   • Copy mandatory configuration data – Copies essential settings like Domains, Credentials, and Alert Settings
   • Copy all configuration data – Creates a full database copy including Job History, Alert History, Message Log, etc.
   • Do not copy configuration data – Creates a blank database. The user will have to configure the database from scratch.
8. Click Yes.