The architecture when using an Oracle or SQL Server database is shown below. In an Oracle or Microsoft SQL Server configuration, the WGC Data Store contains the connection information required for the Oracle or SQL Server database. It also provides storage for photographs and TSDB data, as in the Standard Workgroup Database. All other data is stored in the Oracle/SQL Server database.
The Oracle or SQL Server database schema are created using scripts which can be downloaded from the Autodesk website or obtained by contacting support. Note that the scripts should be updated before use, to provide a unique value for the “database_guid” field in the “global” table. ALL Oracle or SQL Server databases must have a unique value for this field. It is recommended that a GUID (Globally Unique IDentifier) is generated to guarantee uniqueness.
The connection information for an Oracle or Microsoft SQL Server database must be available to the Workgroup Server before it can be used. The connection information is provided by entering the details in a configuration file which exists at the root of the WGC Data Store directory for that database.
In the Data Store directory create a subdirectory with the name you wish to call the database and the extension sndb. E.g. My database.sndb. Inside the subdirectory created above create a text file called master.ini. Inside this file add the required keywords to specify the connection to the Oracle or SQL Server database.
The keywords that follow depend of the type of database in use:
Oracle Database Keyword Options | |
---|---|
DatabaseType | Always set to Oracle. |
OracleDatabase | The connect string to use to connect to the Oracle database server. This could a local net name or a URL (e.g. //machine:/instance). |
TableOwner | The owner of the Oracle tables (e.g. WGCDB) |
TableOwner | The database username to connect with. If this keyword is missing or blank then external credentials are used. On Windows this will use the windows user account under which the Workgroup Server service is running. |
Password | The password to use. Not required if using external credentials. |
Example master.ini configuration file for an Oracle database
DatabaseType=Oracle OracleDatabase=//power:/orcl TableOwner=ICM Username=ICMUSER Password=password
Microsoft SQL Server Keyword Options | |
---|---|
DatabaseType | Always set to SQLServer |
OLEDBProvider |
The name of the OLEDB provider to use. E.g. MSOLEDBSQL We recommend the OLE DB Driver for SQL Server (x64) MSOLEDBSQL (first released April 2018). Currently version 18.3 supports SQL Server 2012, 2014, 2016, 2017 and 2019. Older version can also be used. SQLNCLI11 for SQL Server 2012, 2014 and 2016 native client. Previously SQLNCLI10 was used for the SQL Server 2008 native client, however, note that SQL Server 2008 is no longer supported by Microsoft and therefore not recommended. |
Server |
Name of the database server including the instance name if required (e.g. myserver\sqlexpress) The value corresponds to the OLEDB connection property DBPROP_INIT_DATASOURCE. Therefore, the Microsoft SQL Server documentation for connection string applies. For example, the syntax for using a tcp/ip connection is: tcp:<servernameorIPaddress>[\instancename][,<port>] |
Catalog | Database name |
Username | Optional. Missing or blank for Windows Authentication (Integrated security). Used to specify username if using SQL Server Authentication. |
Password | Optional. Missing or blank for Windows Authentication (Integrated security). Used to specify password if using SQL Server Authentication. |
FileStreamBlobThreshold |
Optional. If using the FILESTREAM blob storage option for large blobs then this optional parameter can be used to specify the minimum blob size stored in the FILESTREAM table. The size is specified in MB and default value is 10. Blobs of size >= FileStreamBlobThreshold are stored in the FILESTREAM table, smaller blobs are stored in an IMAGE column, very small blobs in a BINARY column. |
When SQL Server uses Windows Authentication (Integrated security) it is important to run the Workgroup Server service under a user account that has access to the required SQL Server database. Alternately, the machine account can be granted access to your database.
Example master.ini configuration file for a SQL Server database
DatabaseType=SQLServer OLEDBProvider=SQLNCLI11 Server=(local)\sqlexpress Catalog=ICM
Snapshot Isolation
The SQL Server database must have Snapshot Isolation enabled. The following SQL command can be used to turn it on. Replace “MyDatabase” with the name of the SQL Server database.
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
FILESTREAM Blob Support
By default, blob data is stored in an IMAGE column, this limits the maximum size of any individual blob to 2GB when compressed. The Workgroup Server also supports storing large blobs in a FILESTREAM column, this removes the 2GB size restriction. This column type is not used by default as support for FILESTREAM must be enabled in the database by the Database Administrator.
SQL Server 2008 R2 is the earliest version on which FILESTREAM columns can be used.
It is assumed the Database Administrator is familiar with details and best practice of FILESTREAM support in SQL Server and the following is a summary of the steps involved in enabling it.
The Workgroup Server requires Transact-SQL access (Level 1).
- The FILESTREAM feature must be enabled in SQL Server installation, it is not enabled by default. This may be done during installation or afterwards using the ‘SQL Server configuration Manager’ and changing the propertied of the ‘SQL Server Service’.
- If not done during installation the FILESTREAM level must be set in the server instance. This can be done using ‘SQL Server Management Studio’ or by running the following statements:
EXEC sp_configure filestream_access_level, 1 GO RECONFIGURE GO
- When creating the database it must be setup with a FILESTREAM Data Container. This can be done using ‘SQL Server Management Studio’ or by running statements similar to the following example:
CREATE DATABASE WSPro ON PRIMARY ( NAME = WSPro_data, FILENAME = 'C:\DB\WSPro.mdf'), FILEGROUP FileStreamGroup CONTAINS FILESTREAM (NAME = WSPro_blobs, FILENAME = 'C:\DB\WSProBlobs') LOG ON (NAME = WSPro_log, FILENAME = 'C:\DB\WSPro_log.ldf') ALTER DATABASE WSPro SET ALLOW_SNAPSHOT_ISOLATION ON;
- In addition to running the normal Autodesk product specific database creation script it is necessary to run an additional script to create the table containing the FILESTREAM column. The script is the same for all products and is called SQL_Create_Filestream_Blob_Table.sql.