Create Databases and Database Users on SQL Server

This section contains sample scripts (for SQL Server and Oracle) to create the databases and database users before starting the 3DSpace installation.

You can also create them using the appropriate GUI tools supplied with each supported database product.

Note: If you are running SQL Server, you can create the database directly within the installation procedure.

SQL Server on Windows requires a native client on the server running the3DEXPERIENCE platform. This native client can be the one bundled with SQL Server. If the 3DSpace server is deployed on Linux with SQL Server 2017 or 2019, the required driver for connecting to SQL Server is the Microsoft ODBC Driver 17 for SQL Server.

Download it and install it according to Microsoft documentation. A datasource will be defined in either /etc/ odbcinst.ini(system-wide) or in a user $HOME/odbcinst.ini file. An example of a datasource would be:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.0.so.1.1

You can enable the encryption if you want the database connection to use SSL. Refer to Microsoft documentation for connecting with encryption to SQL Server.

Make sure the SQL Server computer has a certificate provisioned and imported.

  1. Access the database management tools.

    For SQL Server, the database collation should be set to determine case sensitivity and sort order. The recommendation is to choose a case-insensitive collation. For instance: 

    CREATE DATABASE database_name COLLATE Latin1_General_100_CI_AS;
    Note:

    If you choose a CS collation, a ‘BIN2’ is recommended for a slightly better performance.

    The ‘UTF8’ collations are not supported.

  2. Create a directory for data files, customize the database and user names as required, then execute the following script: 

     USE master;
-- Create database and assosiated file groups
CREATE DATABASE [spacedb] ON PRIMARY (NAME=[spacedb],       
FILENAME='C:\DassaultSystemes\3DEXP_DB\spacedb.mdf',SIZE=500MB, FILEGROWTH=10MB) , 
FILEGROUP [I1_DATA] (NAME=[I1_DATA] , FILENAME='C:\DassaultSystemes\3DEXP_DB\I1_DATA.mdf',SIZE=500MB,FILEGROWTH=10MB) , 
FILEGROUP [I1_INDEX] (NAME=[I1_INDEX] , FILENAME='C:\DassaultSystemes\3DEXP_DB\I1_INDEX.mdf',SIZE=500MB,FILEGROWTH=10MB), 
FILEGROUP [M1_DATA] (NAME=[M1_DATA] , FILENAME='C:\DassaultSystemes\3DEXP_DB\M1_DATA.mdf',SIZE=500MB,FILEGROWTH=10MB), 
FILEGROUP [M1_INDEX] (NAME=[M1_INDEX] , FILENAME='C:\DassaultSystemes\3DEXP_DB\M1_INDEX.mdf',SIZE=500MB,FILEGROWTH=10MB), 
FILEGROUP [V1_DATA] (NAME=[V1_DATA] , FILENAME='C:\DassaultSystemes\3DEXP_DB\V1_DATA.mdf',SIZE=500MB,FILEGROWTH=10MB), 
FILEGROUP [V1_INDEX] (NAME=[V1_INDEX] , FILENAME='C:\DassaultSystemes\3DEXP_DB\V1_INDEX.mdf',SIZE=500MB,FILEGROWTH=10MB)
LOG ON (NAME=[spacedb_log] ,FILENAME='C:\DassaultSystemes\3DEXP_DB\spacedb.ldf',SIZE=100MB) 
COLLATE Latin1_General_100_CI_AS;        
ALTER DATABASE [spacedb] SET AUTO_CLOSE OFF ; 
ALTER DATABASE [spacedb] SET READ_COMMITTED_SNAPSHOT ON; 
ALTER DATABASE [spacedb] SET AUTO_CREATE_STATISTICS ON; 
ALTER DATABASE [spacedb] SET AUTO_UPDATE_STATISTICS ON; 
ALTER DATABASE [spacedb] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
-- Create logins
CREATE LOGIN m1 WITH PASSWORD = 'Passport#1';
USE spacedb;
-- Create user on correct DATABASE (after use) and with correct schema
CREATE USER m1 FOR LOGIN m1 WITH DEFAULT_SCHEMA = spacedb;
-- Create schema
CREATE SCHEMA spacedb AUTHORIZATION m1;
-- Grant access
GRANT CREATE TABLE, ALTER, SELECT, INSERT, UPDATE, DELETE ON DATABASE::spacedb TO m1;