Versions Compared

Old Version 6

changes.mady.by.user Dragos Dobre

Saved on

compared with

New Version 7

changes.mady.by.user Dragos Dobre

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Note: This page is under construction. 

Note: The instructions described here applies to openIMIS version 1.3.0 and later. Some steps provide guidance for previous versions. 


Install SQL Server

Note: this installation guide has been developed and tested using SQL Server 2017. Newer or old versions might require adaptations.

  1. Download the desired version of SQL Server (for example, the Express version from https://www.microsoft.com/en-us/sql-server/sql-server-downloads).
  2. When the installation wizard opens, select the manual configuration option in order to fine-tune the installation process.
  3. On the Features selection:
    1. In Shared Feature section, select SQL Client Connectivity SDK
    2. For installations with Data Warehouse, select Analysis Services, Reporting Services and Integration Services (not available for the Express edition)
  4. On the Instance configuration, the default name (SQLEXPRESS) can be used, unless it is already used by another instance.
  5. On Database engine configuration, select Mixed Mode (SQL Server authentication and Windows authentication) in Authentication Mode.
  6. Continue the setup process until the installation is complete.


Configure SQL Server

  1. Open the SQL Server Configuration Manager
    1. On the left panel, select SQL Server Network Configuration → Protocols for SQLEXPRESS (or the name of your SQL Server instance) → Enable Named Pipes and TCP/IP

    2. Select SQL Server Services → right-click on SQL Server instance name and select Restart


Initialise openIMIS database

Note: to facilitate the setting up of the openIMIS database, it is suggested to install we suggest to install SQL Server Management Studio (SSMS). The following procedure is based on SSMS, but you can use the standard SQL Server prompt to proceed with the setup.

  1. First, download the openIMIS database SQL files and migration scripts from Github repository (the source code ZIP file).
  2. If you wish to initialise the database using SSMS, follow the steps: 
    1. Create a new database for the openIMIS instance (
    i
    1. e.
    e
    1. g. openIMIS.
    X
    1. 1.
    Y
    1. 3.
    Z
    1. 0 where
    X
    1. 1.
    Y
    1. 3.
    Z
    1. 0 is the openIMIS database version).
    Execute the initial database creation script provided in the release sources (from the Empty databases folder).
    1. Choose the database to restore based on your context. There are 3 types of databases (structurally identical, but they are pre-configured differently):
      1. Online: this is the default choice when deploying a central online server
      2. Offline: this mode is sometimes used for remote insurance offices without connectivity. Note: the synchronisation of data with the central server is manual.
      3. Offline HF: this database can be used in remote health facilities without connectivity. Note: the synchronisation of data with the central server is manual.
      Restauration can be done in SQL Server Management Studio or the below command line can be used in a shell. Be careful to adapt the querries to your setup, in the command lines example those assumptions were made:
    2. Online database version 1.2.0 is to be restored
    3. The database is called IMIS_DATABASENAME

      4. The SQL server is called SQL_Server_Name

      Code Block
      languagepowershell
      themeEclipse
      firstline1
      SqlCmd -E -S SQL_Server_Name –Q "RESTORE DATABASE [IMIS_DATABASENAME] FROM DISK='X:\PathToBackupFile\openIMIS_ONLINE_v1.2.0.bak'"

      In case the database needs to be restored on a server that doesn’t have the same file structure as the initial server (e.g. from Windows to Linux), the new location of the mlf/mlf files can be specified

      Code Block
      languagepowershell
      themeEclipse
      firstline1
      SqlCmd -E -S SQL_Server_Name -Q "RESTORE DATABASE [IMIS_DATABASENAME] FROM DISK=N'/tmp/openIMIS_ONLINE_v1.2.0.bak' WITH MOVE N'CH_CENTRAL' TO '/var/opt/mssql/data/IMIS.mdf', MOVE N'CH_CENTRAL_log' TO '/var/opt/mssql/data/IMIS_log.ldf'"
    Execute the SETUP-IMIS stored procedureIn SQL Server Management StudioIn the Object Explorer: openIMIS (the openIMIS db name) → Programmability → Stored Procedures → dbo.SETUP-IMISRight click on the dbo.SETUP-IMIS and execute the procedure.or run this command in a shell1SqlCmd -E -S SQL_Server_Name -d IMIS_DATABASENAME -Q "EXECUTE [SETUP-IMIS]" The result returned from the procedure should be 0.
    1. Execute the initial database creation script provided in the release sources (from the Empty databases folder): open the .sql file and execute the script (make sure the selected database is the one created in step a.)
  3. If you prefer to initialize the database using the shell:
    1. Be careful to adapt the queries to your setup, in the command lines example those assumptions were made:
      Online 
          1. The ONLINE database is to be initialized
          2. The database is called
       
          1. IMIS_DATABASENAME
          2. The SQL server is called
       
          1. SQL_Server_Name

        2. Run the following commands:

          Code Block
          languagepowershell
          themeRDark
      1 2
        1. firstline1
          linenumberstrue
          SqlCmd -E –Q
      “CREATE
        1. "CREATE DATABASE IMIS_
      DATABASENAME”
        1. DATABASENAME"
SqlCmd -E -S SQL_Server_Name -d IMIS_DATABASENAME –i X:\PathToSQLFile\openIMIS_ONLINE.sql

      2. Create a dedicated user with full privilege on the openIMIS database only:
        1. In the Security → Logins → right-click and select 
       “New Login…”
        1. “New Login…”
        2. In General page:
          1. Give a login name (
      i
          1. e.
      e
          1. g. ImisUser)
          2. Select SQL Server authentication and provide a password
          3. Unselect Enforce password expiration
          4. Change the default database to openIMIS
        2. In User Mapping page:
          1. Map 
       IMIS db 
          1. openIMIS database to ImisUser user
          2. Give the role of db_owner
    Upgrade the openIMIS database
    Note: Before updating the database make sure the database is not reachable (off lineoffline) for the applications (webWeb Application, mobileWeb Services, …).
    If you want to update a production instance:
    Please duplicate the database (create a full backup of the database)
    Execute the steps below on the copy of the database
    If the migration script succeeded on the copy, then you can apply the migration script to the production instance.
    This approach will etc.). The upgrade can be with SQL Server Management Studio(SSMS) or from the shell (be careful to adapt the querries to your setup).
    1. To update a production instance and to prevent impacting the production if the migration script failed because of customizations in your openIMIS instance:
      1. Duplicate the database using SSMS or shell (create a full backup of the database and 
     it will give you an idea of the time required to update the database.
    If an existing openIMIS database exists already, follow the next steps to upgrade it to the desired version:
    Do a backup of the database
    Use the backup tools available in SQL Server Management Studio
    or run this command in a shell
    Becareful to addapt the querries to your setup, in the command lines example those assumptions were made:
    The database is called IMIS_DATABASENAME
    • The SQL server is called SQL_Server_Name
    1
      1. restore it with another database name, e.g. openIMIS.1.3.0)
        1. Shell commands (e.g. duplicate 'openIMIS.1.2.0' database to 'openIMIS.1.3.0' database; please adapt to your situation):
           Code Block
          languagepowershell
          themeRDark
          firstline1
          linenumberstrue
          SqlCmd -E -S SQL_Server_Name –Q "BACKUP DATABASE [
    IMIS_DATABASENAME
        1. openIMIS.1.2.0] TO DISK='X:
    PathToBackupLocation\[Name_of_Database].bak'"

        1. \PathToBackupLocation\openIMIS.1.2.0.bak'" 
SqlCmd -E -S SQL_Server_Name –Q "RESTORE DATABASE [openIMIS.1.3.0] FROM DISK='X:\PathToBackupLocation\openIMIS.1.2.0.bak' WITH MOVE 'openIMIS.1.2.0' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\openIMIS.1.3.0.mdf', MOVE 'openIMIS.1.2.0_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\openIMIS.1.3.0_log.ldf'"

      2. Download the openIMIS database SQL files and migration scripts 
     from 
      1. from Github repository (the source code ZIP file).
    In SQL Server Management Studio, run 
      1. Execute the migration script on the 
     openIMIS database
    or using the shell:
    1
      1. copy of the database in SSMS or shell
        1. Shell command (e.g. from the current version 1.2.0 to the new version 1.3.0)
           Code Block
          languagepowershell
          themeRDark
          firstline1
          linenumberstrue
          SqlCmd -E -S SQL_Server_Name -d 
    IMIS_DATABASENAME
        1. openIMIS.1.3.0 –i "X:\PathToMigrationScript\openIMIS migration v1.2.0 - v1.3.0.sql" 
    ...

      2. If the migration script succeeded on the copy, then you can apply the migration script to the production instance or configure the openIMIS application to use the new database (e.g. openIMIS.1.3.0)

    Previous step | Go to parent | Table of content | Next step