How to Create a SQL Server Database for Visure?

To have a SQL Server database for Visure, the following steps are required.

1 Database Creation

In the SSMS (SQL Server Management Studio), right-click on the databases folder and click on new database:

After the new windows open, give it a name for the database (it could be VisureDB), then press the Add button at the end of the window:

Set the name to <database_name>_idx, as shown in the example below:

Notice that the database name was VisureDB, meaning that for this new database file, the name is VisureDB_idx (_idx is a suffix that represents indexes for this purpose).

In the same database file, click on the third column and change its filegroup from PRIMARY to <new filegroup>.

The system will ask you for a name. Use INDEXES as the name. No other option needs to be checked on the prompt.

After that, you should have three database files, with one being called <db_name>_idx with the filegroup as INDEXES.

Now, in the left section, go to Options and change the Recovery model from Full to Simple:

Optional, but highly recommended:

• The Snapshot Isolation is useful when multiple users will be using Visure at the same time.
• If this option is enabled, the user experience for concurrency will improve.
  To do this, in the options below, go to a category called "Miscellaneous" and set the Snapshot Isolation to True.
  
  If you don't see the option available under the "Miscellaneous" category, it might be in a different category called "Transactions and Recovery", here you should see the option.
  

Then, if you go to the Filegroups option in the left panel, you should see something like this:

If everything was done correctly, click on OK to finish.

2 Database User Creation

In the Security folder, right-click and then select New ⇾ Login:

After the new window prompts, give it a name for the user (preferable all in lower case), then do the following:

• Change from Windows authentication to SQL Server authentication.
• Set a new password for the login.
• Disable the Enforce password policy.
• At the bottom, change the Default database from Master to the recently created database (in this example, it is called VisureDB).

After all of this, in the left panel, select Server Roles, and add the dbcreator role (see example below).

Then, click on User Mapping. A list of databases will be displayed. Scroll until you can see the database that was recently created. Check that database and enable the following permissions in the section below:

The permissions must be the same as the previous image (db_datareader, db_datawriter, db_ddladmin, db_owner, public).

If you are wondering why those permissions need to be enabled, the reason is explained below:

• db_datareader allows Visure to retrieve information from the database to show in the application.
• db_datawriter allows Visure to create and modify information in the database through the application.
• db_ddladmin allows Visure to execute triggers on the tables that are in the database, those triggers are used on features from Visure (baseline creation, reusability, etc.).
• db_owner allows Visure to perform all schema migrations correctly (normally when switching from major versions, like 8.0 to 8.1, or 8.1 to 8.2).

If everything until this point was set up correctly, click on OK to finish.

3. Establish the Connection with Visure

Open the Visure Requirements ALM 8 Server app, and go to the Connections Tab (located in the left panel), then click on the icon of a database with a plus symbol (this option allows adding new connections):

A new window will prompt with different options/drivers to use.
Select ODBC Driver 17 for SQL Server (if you have 18 or newer, please download and install SQL Server ODBC Driver v17), then click on Next.

In this part, the information from the SQL Server is required to be filled with the following:

• SQL Server Machine (do not use the dropdown): This is the server name where the database is located.
  • If SQL Server Express is installed, append \SQLSERVEREXPRESS at the end of the server name.
  • If SQL Server Standard is installed, nothing needs to be appended at the end of the server name.
• Login ID: This is the user that was created before (in this example, visuredb_user).
• Password: This is the password for the previous user login.
• Database (use the dropdown only): To ensure that all the configuration was correct until this point, please consider using the dropdown and select the database from the list (only one database should be listed).
  
• Filegroup for Tables: This is the default filegroup called PRIMARY on the database.
• Filegroup for Indexes: This is the additional INDEXES filegroup created for the database.

You should have a result similar to the image below (if something was not working, see the Troubleshooting Section at the end). If that is the case, click on Next.

The connection will start populating the database (tables, views, etc.):

After it finishes (around 5 ~ 30 seconds), click on Finish:

In the connection details (after clicking on Finish), set a name for the connection (it is recommended to use the same name as the database, like VisureDB for this example):

Then, click on Test (), and if the result says Connection successful, click on Save:

You should be able to see your new connection listed and with the checkbox enabled:

All Visure Connections are only read at the service start. Meaning that in order to start this connection, the following needs to be done:

• First, click on OK and save the actual connection created.
• Second, restart the Visure Service, which can be done in the same application or through the services in Windows.
• After the service starts, the connection should be available and ready to use.

You may now log in to the Administration Center using the admin user to create your first project. By default, the password is empty.

4 Troubleshooting

4.1 Database is not showing in the dropdown

If you run into problems while trying to set up the connection, please ensure the following:

1. In the SQL Server, the security option is changed to allow Windows and SQL authentication. To do this, please follow the steps below:
   • To do so, go to SSMS, right-click on the Server Name, and click on Properties:
     
   • Then, go to Security and switch to SQL Server and Windows Authentication mode.
     
     This change requires restarting the SQL Server service (called SQL Server MSSQLSERVER).
2. Reset the password for the user login by doing the following:
   • In the SSMS, expand Security, then expand Logins.
   • Search for the exact login that was created for the database.
   • After it's found, right-click it and click on properties.
   • Set a new password and ensure that all the permissions are correct as described in step 2 of this article.
   • Please attempt to establish the connection once more.

4.2 Connection created, but not listed in Visure Connections

If you made some mistakes or have problems, you will not be able to see the connection in the list from the Visure Server.

• Use the icon of a database with an arrow (). This option allows adding existing connections.
• Follow the guide from step 3 in this article until finished (you might notice that this time the file groups are not required).

If you experience any issue configuring this, please reach out to our support team by emailing [e-mail].