Configuration

This section provides general configuration information about the MSSQL data source.

MSSQL general configuration options

  • Server Instance: Specify the name of the Microsoft SQL Server instance to connect to. This usually takes the form SERVER\INSTANCE_NAME.
  • Integrated Security: This option is available for only installations with a Windows or Windows domain user as the database access user account.

    To allow IPF to access SQL Server without a user ID and password, you can select this option. This allows IPF to use Windows authentication.

    If you are using the SQL Server FILESTREAM feature to facilitate large BLOBs, then you must select this option. Also, the IIS Web application must have permissions to access the location of the BLOB. See http://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx for more information. Also see "Blob handling for MSSQL" in MSSQL data source functionality.

  • User ID and Password: For installations with a Windows or Windows domain user for the database access user account, if integrated security is not being used, specify a SQL Server authentication user ID and password to use when connecting to the SQL Server database. This must be a user with dbo permissions in the database.

    For installations with a control DB user for the database access user account, you must specify a SQL Server authentication user ID and password to use when connecting to the SQL Server database.

  • Database: Create or specify the name of the database to connect to on that server. Also see “About Managed Schema in MSSQL Databases” on page 249.
  • Default SQL Schema: Specify the schema to use when accessing objects such as stored procedures, tables and views. This parameter is optional; however, SQL performs slightly better when it is specified. When paired with a Mongoose-based database, this option is disabled and "dbo" is always used.
  • Query Timeout: Specify the number of seconds the portal will wait for a request to be serviced by the Server Instance before giving up and returning an error. If set to zero, the portal will wait forever.
  • Paired Back Office Data Source: Optionally, you can configure this data source to be used as a stand-in for the specified Mongoose-based data source in certain scenarios. See Pairing.
  • Use Default Values as Initial Values: When this check box is selected, new records created in collections associated with this data source will have their properties pre-populated with their default values as configured for the table in the database. The values used are taken from the COLUMN_DEFAULT column of the INFORMATION_SCHEMA.columns view.

    Only column defaults that can be converted to the data type of the column are used. If the column default includes a SQL expression, the default will be considered null and the application will be responsible for setting the initial value.

Pooling-related configuration options

The MSSQL data source uses ADO.Net to connect to Microsoft SQL Server. ADO.Net supports a connection pooling feature to increase performance. For more information about ADO.Net connection pooling, see:

http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.90).aspx

Unless your servers are operating under high-load conditions, you can usually leave the defaults for these options unchanged. These configuration options are available:

  • Min Pool Size: Specify the minimum pool size. The default value is zero.
  • Max Pool Size: Specify the maximum pool size. The default value is 100.
  • Minimize Pools: When this check box is selected, this data source will attempt to avoid pool fragmentation (caused by many databases) by implementing the procedure described in "Pool Fragmentation Due to Many Databases" on the web page cited above. It will connect to the master database on the server first, and then switch to the configured data base.