Connect to external MSSQL database
- Overview
- Database and user requirements
- MSSQL Database configuration and performance tuning
- Configuring Zoho Creator to use MSSQL Server's database
Overview
Zoho Creator On-premise allows you to configure an external MSSQL database to manage your data. This setup requires certain configurations on both the SQL Server and the Zoho Creator server to ensure proper connectivity and optimal operation.
Database and user requirements
You need to create an MSSQL database and add users with appropriate privileges by following the steps below in your MSSQL console:
Database name: CreatorDB
User: creator_user
CREATE DATABASE "CreatorDB" COLLATE Latin1_General_100_CS_AI;
USE CreatorDB;
CREATE USER creator_user FOR LOGIN creator_user;
EXEC sp_addrolemember 'db_owner', creator_user;
MSSQL Database configuration and performance tuning
You must configure the MSSQL database before the initial (cold) start of Zoho Creator On-premise on your local machine. Once the service has started, connecting to an external database is no longer supported.
General system requirements
The following requirements apply specifically to the MSSQL instance hosting the Zoho Creator database.
| Component | Requirement |
|---|---|
| Processor | 2.4 GHz to 3.0 GHz, 30MB cache, 16 cores |
| RAM | 16 GB* |
| Hard Disk Type | Preferably SSD for better I/O performance |
Memory allocation
It is recommended that the local machine where you are configuring the database and Zoho Creator On-premise have max server memory to approximately 70% of the database host server's total physical memory, reserving the remaining 30% for the operating system and other processes. This helps ensure stable memory usage and prevents resource pressure on the host machine.
Example for 32 GB RAM:
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 22937;
RECONFIGURE;
Parallelism (MAXDOP)
Maximum Degree of Parallelism (MAXDOP) controls how many CPU cores SQL Server can use for a single query. Setting MAXDOP to 2 limits excessive CPU usage by individual queries and helps maintain stable performance. This setting offers a balanced level of parallelism suitable for most workloads..
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;
Isolation level (RCSI)
Read Committed Snapshot Isolation (RCSI) reduces locking by allowing queries to read versioned rows stored in tempdb. A versioned row is an older copy of a data row that SQL Server keeps so readers can access stable data while the original row is being updated. This prevents readers from being blocked by writers and improves overall concurrency. The result is more consistent performance when many users are active simultaneously.
ALTER DATABASE CreatorDB SET READ_COMMITTED_SNAPSHOT ON;
File auto-growth settings
The default SQL Server auto-growth settings (1 MB for data files and 10% for log files) can lead to fragmentation and negatively impact performance. Configuring fixed-size growth helps maintain predictable file sizes and reduces the number of expansion events.
Recommended values:
Data file: 256 MB
Log file: 128 MB
These values provide smoother, more stable performance, though optimal settings may vary depending on the workload.
MODIFY FILE (NAME = [YourDataFileLogicalName], FILEGROWTH = 256MB);
ALTER DATABASE CreatorDB
MODIFY FILE (NAME = [YourLogFileLogicalName], FILEGROWTH = 128MB);
Replace [YourDataFileLogicalName] and [YourLogFileLogicalName] with the actual logical file names of the MDF and LDF.
Configuring Zoho Creator to use MSSQL Server's database
Once MSSQL is properly configured on your local machine, you can proceed to configure Zoho Creator On-premise.
Server prerequisites
Before running the Zoho Creator On-premise configuration script, verify that your MSSQL server instance is set to allow the following requirements from the application server. This ensures Zoho Creator can communicate with the external database during setup.
| Requirement | Details | Action on DB Server |
|---|---|---|
| Remote Connections | Database server must accept connections from the application server's IP address. | Enable Named Pipes and TCP/IP in SQL Server Configuration Manager under SQL Server Network Configuration. Refer this |
| Port & Firewall | The default TCP port is 1433(for MSSQL). Ensure this port is open in the Windows Firewall (or other network firewalls) on the MSSQL host. | Verify port 1433 is listening and open to the application server's IP. |
| SQL Browser Service | If you are using a Named Instance (e.g., SERVER\INSTANCENAME), the SQL Server Browser service must be running. | Ensure the SQL Server Browser service is running and accessible. |
| User Permissions | The specified user must have sufficient rights (e.g., db_owner or equivalent permissions to create and manage tables) on the target database. | Grant necessary permissions to the database user (e.g., creator_user). |
Steps to configure Zoho Creator On-premise
If you must reconfigure the database after a cold start, ensure you have a full database backup before continuing.
Open your terminal or Command Prompt, navigate to: <installation_directory>/bin
and run: sh InstallationSettings.sh
The script will prompt you to switch the installation type to Multiple App Server Mode.
- Prompt: Switch to Multiple App Server Mode? (Y/N)
- Action: Enter N
When the Creator Configuration menu appears, select the option to update the database and enter:
- Prompt: Enter your choice's index
- Action: Enter 7 (Update DB Configuration).
Confirm that you are using a separately installed database::
- Prompt: Use a Database which you have already installed separately? (Y/N)
- Action: Enter Y
- Select database type:
- Prompt: Choose DB Type
- Action: Enter 3 (MSSQL)
Provide the connection details for your external MSSQL server:
Prompt Action Examples Enter DB Hostname/IP
(it should be reachable from all servers)Enter MSSQL server IP 192.168.90.2 Enter DB port Enter 1433 1433 Enter DB username Enter creator_user creatoruser Enter DB user's password Enter password CreatorUser@135 After the database update completes, the main menu will reappear:
Prompt: Enter your choice's index
Action: Enter 9 (Exit).
The script will confirm the exit with Exiting....
Copy the jar files from the installation_dir/tools/mssql/lib/ to the installation_dir/lib/ directory.
Start your Zoho Creator service for the changes to take effect.
Glossary
- Cold start - A cold start refers to the very first time the Zoho Creator service is launched after installation, before any configuration or initialization has taken place.
- Parallelism - In SQL server, parallelism is the process of dividing a query into multiple threads that run across several CPU cores to reduce execution time.
- CPU core - A CPU core is an individual processing unit inside a computer’s central processing unit (CPU).
- Locking - Locking is a SQL Server mechanism that controls how multiple users or processes access the same data simultaneously. When one operation reads or modifies data, SQL Server places locks on that data to prevent conflicting action.
- Data row - A data row is a single record within a SQL Server table. It represents one complete set of field values for an entity.
- Fixed-size growth - Fixed-size growth is a SQL Server file-growth setting where the database or log file increases by a specific, predefined amount (for example, 256 MB for data files or 128 MB for log files) whenever more space is needed.
- Expansion event - An expansion event occurs when SQL Server automatically increases the size of a data or log file because it has run out of available space.