How to Bulk Update Tenant Database Information in On-Premises Deployments

This article explains how to update the database information for multiple tenants using a command-line tool. To update the connection string for the sites, retrieve the active site’s database details and update them with the new database details using the following commands:

• getsitedbdetails – Retrieves the site details.
• updatesitesdbdetails – Updates the database with the new server details.

Retrieving Sites Database Information

The getsitedbdetails command retrieves the site’s database details as a site_db_details.csv file in the <deployment_location>\app_data\site_details path. This file helps to verify the existing database information for the sites.

Additionally, a template file named update_site_db_details.csv is generated in the same folder for updating the site’s database details.

The site_db_details.csv file contains the site details for all active tenants with the following columns:

1. Tenant ID
2. Site URL
3. Server connection string
   
   

The update_site_db_details.csv file serves as a template for providing updated database details. It contains the following columns:

1. Tenant ID: This field is required if you wish to update the database information for a specific site or if the site’s database name has changed.

2. Site URL: This field is required if you want to update the database information for a specific site or if the site’s database name has changed.

3. Server Type: This is a required field. Set the value to either mssql, mysql, or postgresql based on your database type.

   The current migration process only supports migrations within the same SQL server type. Migration between different SQL server types, such as MySQL to MSSQL or vice versa, is not supported.

4. Old Server Name: This is a required field. You must provide the old server name, which can be obtained from the site_db_details.csv file.

5. Server Name: This is a required field. You must provide the new server name to which the database has been migrated.

6. Port: This field is required only for PostgreSQL and MySQL databases. Leave this field empty for MSSQL.

7. Username: This is a required field. You must provide the new database server username. Ensure that this user has the necessary permission sets as defined here.

8. Password: This is a required field, and you must provide the new database server password.

9. Database Name: This field is required only if you need to update the database name for individual sites. If a database name is provided in this field, it will update the database for that specific site. If the database name has not changed, you can leave this field empty or enter “*”.

10. Maintenance Database: This field is optional and is required only for PostgreSQL.

11. Additional Parameters: This field is optional.

12. IsWindowsAuthentication: This field is required only for MSSQL. Set to true for Windows Authentication; otherwise, set to false.

Updating the Database to a New Server

The updatesitesdbdetails command updates all sites that match the old server name to the new server. This allows updating multiple sites in bulk.

Updating Database Details Individually for Sites

To update a different database name and database server for an individual site, fill in all the columns mentioned in the update_site_db_details.csv template file.

Updating DB Server Name and Credentials for All Sites

To update the database server name and credentials for all sites, provide a single row with the old and new server details and enter ‘*’ for the database name.

After executing the updatesitesdbdetails command, a result file is generated in the following location:

<deployment_location>\app_data\site_details\db_update_results

You can verify the site update results from this folder.

Refer to the sections below to update the database information for multiple sites based on your Bold Reports^® hosting environment. This includes guidance for the following environments:

• Windows
• Linux
• Docker
• Kubernetes

Windows

Step 1: Open the Command Prompt and change the directory to the Admin Utility location by running the following command:

cd C:\BoldServices\utilities\adminutils  

Step 2: Run the command Syncfusion.Server.Commands.Utility.exe getsitedbdetails to retrieve the site details.

Step 3: The files site_db_details.csv and update_site_db_details.csv will be generated in the C:\BoldServices\app_data\site_details path.

Step 4: Before executing the update command, ensure that the update_site_db_details.csv file is correctly filled with the required columns.

Step 5: Run the command Syncfusion.Server.Commands.Utility.exe updatesitesdbdetails.

Step 6: Once the update is complete, a file named updated_site_details_timestamp.csv will be generated in the following location, containing success or failure messages: C:\BoldServices\app_data\sites_details\db_update_results\updated_site_details_timestamp.csv.

Step 7: After updating the sites, restart the application by following this link.

Linux

Step 1: Open the command prompt and navigate to the admin utility directory by running the following command:

cd /var/www/bold-services/application/utilities/adminutils/

Step 2: Run the following command to retrieve the site details.

…/…/…/dotnet/dotnet Syncfusion.Server.Commands.Utility.dll getsitedbdetails

Step 3: The files site_db_details.csv and update_site_db_details.csv will be generated in the following path.

/var/www/bold-services/application/app_data/site_details.

Step 4: Navigate to this directory by running the command cd /var/www/bold-services/application/app_data/site_details and view or edit the file using the nano command.

Step 5: Before executing the update command, ensure that the update_site_db_details.csv file is correctly filled with the required columns.
Then, change the directory to the admin utility location by running the command cd /var/www/bold-services/application/utilities/adminutils/.

Step 6: Run the command …/…/…/dotnet/dotnet Syncfusion.Server.Commands.Utility.dll updatesitesdbdetails.

Step 7: The sites will be updated, and the file updated_site_details_timestamp.csv will be generated in the following directory with success or failure messages.

/var/www/bold-services/application/app_data/sites_details/db_update_results/

Step 8: Navigate to this directory by running the command cd /var/www/bold-services/application/app_data/sites_details/db_update_results and view the updated site details using the nano command.

Step 9: After updating the sites, restart the application by following this link.

Docker

Step 1: Open the command prompt, enter the container, and navigate to the admin utility directory by running the following commands:

docker exec -it <Container ID or Container name> bash
cd /application/utilities/adminutils

Step 2: Run the command dotnet Syncfusion.Server.Commands.Utility.dll getsitedbdetails to retrieve the site details.

Step 3: The files site_db_details.csv and update_site_db_details.csv will be generated in the /application/app_data/site_details directory.

Step 4: Navigate to this directory by running the command cd /application/app_data/site_details, and then view or edit the file using the nano command.

Step 5: Before executing the update command, ensure that the update_site_db_details.csv file is correctly filled with the required columns.
Then, change the directory to the admin utility location by running the command cd /application/utilities/adminutils/.

Step 6: Run the following command to update the site database details:

dotnet Syncfusion.Server.Commands.Utility.dll updatesitesdbdetails

Step 7: The sites will be updated, and the file updated_site_details_timestamp.csv will be generated in the /application/app_data/sites_details/db_update_results/ directory with success or failure messages.

Step 8: Navigate to this directory by running the command:

cd /application/app_data/site_details/db_update_results

Then, view the updated site details using the nano command.

Step 9: After updating the sites, restart the container using the following command:

docker restart <container ID or container name>

Kubernetes

Step 1: Open the command prompt and Bash the id_web_deployment pod, and navigate to the admin utility location by running the following commands:

kubectl exec -it <id-web-deployment-pod> -n namespace -- bash
cd /application/utilities/adminutils/

Step 2: Run the command dotnet Syncfusion.Server.Commands.Utility.dll getsitedbdetails to retrieve the site details.

Step 3: The files site_db_details.csv and update_site_db_details.csv will be generated in the /application/app_data/site_details directory.

Step 4: Before executing the update command, ensure that the update_site_db_details.csv file is correctly filled with the required columns.

Step 5: Run the following command:

dotnet Syncfusion.Server.Commands.Utility.dll updatesitesdbdetails

Step 6: The sites will be updated, and the file updated_site_details_timestamp.csv will be generated in the /application/app_data/sites_details/db_update_results/ directory, containing success or failure messages.

Step 7: After updating the sites, restart the application using the following command:

kubectl rollout restart deploy -n <namespace>