Alternative database configurations

The Centara POS uses LocalDB by default and the instance is called centara. It can also run on SQL Server Express or more powerful versions.

If the SQL server instance is called centara or hbi then the POS will automatically adjust server settings and create databases as needed. Under other instance names more manual configuration may be needed.

Under the default settings no additional work is needed for the database except making sure that LocalDB is installed.

LocalDB database for a single Windows user (default configuration)

By default the POS connects to LocalDB, centara instance. To get back to this state once configuration has been changed just make sure there's no database or dbserver properties in the centara.ini configuration file.

LocalDB configuration for multiple windows users

When using a shared LocalDB instance, we must make sure that all users have access to the POS database. The directions below use an SQL user to achieve that.

Create the following SQL script, replacing the <password> with something meaningful.

prepare-db.sql

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO  

create database CentaraWorkstation

create login centarapos with password = '<password>'
go

sp_addsrvrolemember 'centarapos', 'dbcreator'

use CentaraWorkstation
create user centarapos for login centarapos
go

sp_addrolemember 'db_owner', 'centarapos'

Run the following

1. sqllocaldb create centara

2. sqllocalb share centara centara (as Administrator)

3. sqlcmd -S (localdb)\.\centara -i prepare-db.sql

Modify the connection string in the centara.ini file, replacing the <password> with the one you just created.

centara.ini

[CentaraWkst]
Database=Database=CentaraWorkstation;Server=(localdb)\.\centara;User Id=centarapos;Password=<password>

Verifying the configuration

Start the POS. For a new setup, the POS should create all database contents and do the initial synchronization. If this is not a new setup then the POS should start normally and bring up a login window or a day-close dialog if a day close hasn't been performed yet.

Revert to default configuration

To revert to the default configuration just remove the Database= keyword from centara.ini.

Optionally the SQL user and login may be removed for full cleanup.

cleaanup.sql

use CentaraWorkstation
drop user centarapos
go

drop login centarapos
go

The above SQL can be run with the command

1. sqlcmd -S (localdb)\.\centara -i cleanup.sql

SQL Server Express

Install an SQL Server Express 2014 or later, and name the instance centara.

Multiple users

If multiple Windows users are intended to have access to the database then an SQL user needs to be created.

prepare-db.sql

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE; 

create login centarapos with password = '<password>'
go

sp_addsrvrolemember 'centarapos', 'dbcreator'

Run prepare-db.sql, and then modify the centara.ini file:

centara.ini

[CentaraWkst]
Database=Database=CentaraWorkstation;Server=(localdb)\.\centara;User Id=centarapos;Password=<password>

Database connection properties reference

Centara.ini database properties in the [CentaraWkst] section.

Keyword	Purpose	Default value
database	Fully specified connection string. Any of the properties below will override the appropriate keyword in the connection string if defined.
dbserver	The database server/instance to use.	(localdb)\centara
dbname	The database name to use.	CentaraWorkstation
dbuser	The SQL user to use. If this is not specified then Windows authentication is used.
dbpassword	The SQL user password for authentication. Omit if using Windows authentication.