Skip to main content

Phase 2: Set Up the SQL Server Database for Code Insight

Once the SQL Server instance has been installed and the SQL Server Agent started, the DBO performs the following steps to set up the database for Code Insight.

To set up the SQL Server database for Code Insight, do the following:

  1. Ensure that you have downloaded and extracted the required the Code Insight scripts, as described in Downloading the Scripts Needed to Set Up the SQL Server Database.

    The following is a brief description of the scripts:

    • codeinsight_serversettings.sql—This script configures the database server to enable the maximum performance for Code Insight. The script sets the following server parameters:

      • Cost of parallelism—15 (the threshold at which the optimizer chooses parallel processing)

      • Max degree of parallelism—Number of threads created specifically for this configuration.

      • Max memory configuration—The server’s maximum utilization (60 percent) of total memory.

      • TF—Trace flags 111, 1118, 2371.

      You are strongly recommended to review existing configurations in this script and note their values in case a rollback is needed. However, do not edit this script.

    • codeinsight_db_creation_with_maintainenceplan.sql—This script creates the database and schedules maintenance jobs. Specifically, it performs the following operations:

      • Creates a database with 4 data files and 1 log file.

      • Creates a new folder called MSSQLDATA on a non-OS disk. If only one drive exists, the database is created on the OS drive itself.

      • Creates a subfolder with the database name under the MSSQLDATA folder.

      • Creates a daily maintenance job to perform an Update Statistics every 6 hours (no downtime needed).

      • Creates maintenance job to perform an Update Statistics and Index Reorg every two weeks (no downtime needed). The default is to run at 10 pm per server time zone every two weeks.

      You can edit some settings in this script as described in Step 5.

  2. Ensure that the SQL Server Agent is running.

  3. Open the codeinsight_serversettings.sql script, and execute it. Do not edit this script.

  4. Open the codeinsight_db_creation_with_maintainenceplan.sql script, edit the @dbname setting if necessary, and then execute the script.

    The default value for @dbname is fnciv7. To edit this setting, simply overwrite the current value with the preferred database name. If you provide a database name that already exists, the script execution will fail.

  5. Create the user who will install Code Insight. At installation, this same user is automatically identified to Code Insight (in core.db.properties) as the user Code Insight will use internally to manage the database.

  6. Assign this user at least the minimally required permissions: ALTER, DROP, CREATE, DELETE, INDEX, INSERT, and UPDATE.

    note

    Code Insight uses this same user to migrate the database during a Code Insight upgrade. If you intend to upgrade Code Insight in the future, this user must have the db_ddladmin role to perform the migration. However, you can add this role at the time of the upgrade and then revoke it once the upgrade is complete.