Steps to Install Microsoft SQL Server 2019

Note: Install the database according to the vendor's instructions. Make sure to note the database installation user name and password to use it later in the installation process.

The Microsoft SQL Server Management Studio is used to create the new databases and users, as well as restore the databases from the provided backups.

Create New Databases

Using Microsoft SQL Server Management Studio, create two new databases:

  1. OIPA database

  2. IVS database

  1. Execute the below command to set the compatibility level of the OIPA and IVS databases to 140.

    ALTER DATABASE <<database_name>> SET COMPATIBILITY_LEVEL = 140;

  2. Check the compatibility level to make sure it is set to 140.

    SELECT compatibility_level FROM sys.databases WHERE name = '<<database_name>>';

Note: Replace the actual database name in the above SQL.

Create Database Users

Three new database users will need to be created: two for the OIPA database and one for the IVS database.

  • OIPA database user with full privileges for the OIPA database
  • OIPA database user with read-only privileges for the OIPA database
  • IVS database user with full privileges for the IVS database

Restore the Databases

The OIPA Media Pack includes backups of both databases that can be restored using Microsoft SQL Server Management Studio. To restore the databases, right-click on the specific database in SQL Server Management Studio and select restore. Ensure that the correct backup file is used to restore each database.

  • OIPA database: oipa_pas.bak
  • IVS database: oipa_ivs.bak

Encrypting the Database

  1. Create the same users in Master database and grant the below privileges to the users, make sure the server has the same logins and have access to the databases.

    2. view definition
    view database state
    control
    select
    connect

  2. Create a master key

    3. USE MASTER;
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<<password>>';
    go

  3. Create or obtain a certificate protected by the master key

    4. USE MASTER;
    GO
    CREATE CERTIFICATE <<Certificate name>> WITH SUBJECT = 'OIPA TDE Certificate';
    GO

  4. Create a database encryption key and protect it by the certificate

    5. use <<Database Name>>;
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128
    ENCRYPTION BY SERVER CERTIFICATE <<Certificate name>>;
    GO

  5. Set the database to use encryption

    6. use <<Database Name>>;
    GO
    ALTER DATABASE <<Database Name>> SET ENCRYPTION ON;
    GO

  6. Verify the encryption status of the database

    7. select name,is_encrypted from sys.databases;
    GO