Prerequisites

• SQL Server installed and running.

• Access to SQL Server Management Studio (SSMS) or a similar database client.

• Git installed.

• Linux shell available (e.g., WSL on Windows).

Step-by-Step Instructions

1. Clone openIMIS Database Project

Clone the openIMIS Database repository from GitHub.

git clone <https://github.com/openimis/openimis-db_dkr.git>

2. Create a New Database

Open SQL Server Management Studio (SSMS) or your preferred SQL Server client and create a new database, for example, test_imis.

3. Create a Database User

Create a new database user named IMISuser with read, write, and adminddl access only to the test_imis database.

Create User (SSMS)

1. In SSMS, navigate to your SQL Server instance.

2. Expand Security > Logins.

3. Right-click Logins and select New Login.

4. Enter IMISuser as the Login name.

5. Select SQL Server authentication and set a password.

6. Uncheck Enforce password policy if desired.

7. In the User Mapping page, map IMISuser to the test_imis database.

8. Assign db_datareader, db_datawriter, and db_ddladmin roles to IMISuser.

9. Click OK to create the user.

4. Log in to the Database as IMISuser

Log in to the test_imis database using the newly created IMISuser.

sqlcmd -S <Your_Server_Name> -U IMISuser -P <Your_Password> -d test_imis

Replace <Your_Server_Name>, <Your_Password>, and test_imis with your server name, password, and database name, respectively.

5. Navigate to the Cloned Folder

Navigate to the database_ms_sqlserver folder within the cloned openIMIS Database Project.

cd openimis-db_dkr/database_ms_sqlserver

6. Execute the Script File in a Linux Shell

Open a Linux shell (e.g., WSL on Windows) and execute the script file concatenate_files.sh located in the database_ms_sqlserver folder.

./concatenate_files.sh

This script will generate a SQL script file in the output folder, e.g., fullDemoDatabase.sql.

7. Run the Generated SQL Script

Run the generated SQL script (fullDemoDatabase.sql) in SQL Server to set up the database schema and initial data.

Execute Script (SSMS)

1. Open SSMS and connect to your SQL Server instance.

2. Open the fullDemoDatabase.sql file generated in the output folder.

3. Ensure that the test_imis database is selected.

4. Execute the script by pressing F5 or clicking the Execute button.

Summary

You have now successfully set up the openIMIS Database in SQL Server with the necessary user and permissions. The user IMISuser has the appropriate rights to interact with the test_imis database. Additionally, you have cloned the openIMIS database repository and executed the required SQL scripts.