Database Setup
The Docker images automatically keep the database schema up-to-date using Liquibase. Whenever you install the application or update it to a new version, the container updates the DB schema. On application downgrade, the changes are rolled back automatically (losing information specific for the new versions). Therefore, you only need to follow this documentation when you are installing and setting up the database from scratch.
Install Microsoft SQL Server
To get started, download and install the current version of MSSQL:
Start the database and connect to it using your preferred database tool.
Tip: We use DataGrip by JetBrains for any database related tasks. It allows easy data editing for all popular database engines and contains a powerful SQL console for even the most complex database tasks.
Create User
Start by creating the powerauth user in the database and by setting the user a strong password.
Tip: You can generate a strong password locally on your computer using openssl rand -base64 12.
CREATE LOGIN powerauth WITH PASSWORD = '$PASSWORD$';
CREATE USER powerauth FOR LOGIN powerauth;
Create Database
Now, let’s create the powerauth database to which we will store the data.
CREATE DATABASE powerauth;
USE powerauth;
EXEC sp_addrolemember 'db_owner', 'powerauth';
You can assign more granular privileges instead of using the db_owner role, if required for security reasons.
MSSQL Server Snapshot Isolation
Enable the SNAPSHOT isolation to avoid deadlocks. You can enable the SNAPSHOT isolation mode using following query:
ALTER DATABASE [powerauth_database] SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE [powerauth_database] SET READ_COMMITTED_SNAPSHOT ON;
The SNAPSHOT transaction isolation level is enforced automatically by PowerAuth server when database sessions are started using following query:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
Read Next
This is everything you need at this moment. Once the database is up and running with the right user and database, you can launch the Docker container. The Docker container uses Liquibase to create the schema automatically.