Manual Creation of new database

Owned by John Paliwoda
May 13, 2022
1 min read

  1. Create a new database

    1. Using Microsoft SQL Server Management Studio (SSMS), create a new database. Typically “studytrax” is used as the database name

      1. It is recommended that the database's "Initial Size" is set to least 1000 MB (greater
        if a lot of data is expected in the database)

  2. Create the tables

    1. Load the "Ddl.sql" file, load it into SSMS, and click the "Execute" button

      1. In production, the "Ddl.sql" file is in _content\sql\

      2. In development, run ConsolidateSqlFiles.

        1. This places the “Ddl.sql” file in C:\dev\studytrax\app\Database\Output

  3. Load stored procedures

    1. Load the "StoredProcedures.sql" file, load it into SSMS, and click the "Execute" button

      1. In production, the "StoredProcedures.sql" file is in _content\sql\

      2. In development, the “StoredProcedures” file in C:\dev\studytrax\app\Database\Output

  4. Initialize the database

    1. Open a new window in SSMS, enter "EXEC dbo.InitializeDatabase" and click the Execute button.

  5. Assign the database role to the login (user) the StudyTRAX system will use to access the database.

    1. Open a new window in SSMS, enter “EXEC sp_addrolemember @RoleName='studytrax_executor', @MemberName='StudyTraxUser'“ and click the Execute button.

      1. Replace 'StudyTraxUser' with the user StudyTRAX will use to connect to the database. The value for StudyTraxUser will be one of the following:

        1. If the Connection String contains a SQL Server username, use that value.

        2. If the Connection String uses integrated security, then use the windows login associated with the "Identity" set in the IIS Application Pool under which the StudyTrax application
          is running. For example: 'MyDomain\StudyTrax' or 'NT AUTHORITY\SYSTEM'