SQL Server¶
Kensium POS uses the Microsoft SQL Server database engine as its data store. As of version 7.0, POS supports deployment of SQL Server using a Docker container, or on an external server.
Docker vs External¶
There are several considerations when determining whether to deploy SQL Server as a Docker container or an externally-hosted instance.
Existing Clients¶
Existing clients that currently use older versions of POS have already deployed SQL Server to their Corporate and Store locations. It is recommended to keep using these SQL Server instances rather than use the SQL Server Docker container, unless the instance version is less than SQL Server 2022 and an upgrade is impractical / not cost-effective.
Large Volume Clients¶
There can be performance implications when running SQL Server in a Docker container. For clients that require large volumes of inventory, customer, pricing or sale transaction data the Docker container approach might not be suitable.
Running SQL Server in a Docker container can lead to performance implications due to resource constraints and the way SQL Server interacts with the host system. Here are some key points to consider:
Resource Constraints: Docker allows you to set resource constraints like CPU and Memory limits when creating containers. These limits can affect the performance of SQL Server by limiting the resources available to the container, potentially leading to bottlenecks and reduced performance.
Performance Degradation: Some users have reported a degradation in performance when running SQL Server in Docker containers, particularly in database-heavy integration tests. This can be attributed to overhead related to the host operating system and the Windows file system.
Memory Configuration: Proper memory configuration is crucial for SQL Server on Linux. You can adjust memory limits using the mssql-conf tool or the MSSQL_MEMORY_LIMIT_MB environment variable. It's important to set this limit lower than the container memory limit to leave room for the operating system and auxiliary processes.
Cgroup Constraints: SQL Server detects and honors control group (cgroup) v2 constraints, which provide fine-grained control over CPU and memory resources in Docker. This can improve resource isolation and performance.
Best Practices: Best practices for SQL Server memory on Linux include using the default memory limit of 80% of physical RAM and adjusting it as necessary. It's also recommended to use data volumes for persistent storage and to ensure SQL Server is configured to use the correct processor affinity.
See the following reference material for more information:
Clients with larger volume requirements may require externally-hosted SQL Server instances for the Corporate Server and possibly Store Servers.
Docker at Stores¶
Depending on backup strategy employed (see next), a hybrid approach may be warranted - where an external SQL Server instance is deployed at the Corporate location, and Docker containers are used for SQL Server in store environments.
This approach can combine the reliability and performance characteristics of an externally-hosted SQL Server instance at Corporate, with the ease-of-maintenance of Docker SQL Server containers at each store.
Backup Strategy¶
Similarly, both Docker and an externally-hosted SQL Server instance require different approaches to data backups.
Note
Data backups must be performed at the Corporate location. Backups are also recommended at Store locations, so that store history is preserved (there is currently no down-sync of store transaction history from Corporate to a Store server.)
Docker Container¶
TODO
External Instance¶
TODO
Network Configuration¶
Depending on the approach used to host SQL Server, there are several considerations when deploying the database.
Docker Container¶
TODO
- What type of configuration needed so that external components (e.g. ASI, Comms, SQL Server Enterprise Manager) can access the database?
- Credentials approach and guidelines
External Instance¶
TODO
- Open ports
- Credentials approach and guidelines