Microsoft SQL Server
When using SQL Server, the following initial database configuration is recommended. However, On this page we state the minimum requirements for a SQL Server installation to support PhixFlow, and present a suggested installation.
The suggested installation incorporates the minimum requirements for SQL Server. It is suitable as an initial configuration for many instances of PhixFlow, but note that some of the options below may need to change over time as the work carried out by PhixFlow increases.
Minimum requirements
The following the minimum requirements for a SQL Server database installation to support PhixFlow.
Server settings
Option | Setting |
SQL Server Edition | Standard or Enterprise |
Version |
Service Name
(Please provide to PhixFlow installation consultant)
Collation Character set | Latin1_General_100_CI_AI_SC_UTF8 The database that PhixFlow runs against must be created with the collation set to this character set (see Install the PhixFlow Database Schema), otherwise the performance of PhixFlow can be severely impacted. However, PhixFlow uses TEMP tables for efficient processing of ‘is in’ filter clauses with large numbers of parameters, and this uses the collation setting of the server itself. Therefore, it is strongly recommended that the server is set to use this collation. |
Database settings
Parameter | Setting |
Collation | Latin1_General_100_CI_ |
Max Server Memory
Server Authentication
AI_SC_UTF8 | |
READ_COMMITTED_SNAPSHOT | ON |
Login and user
A command to create the database, along with a local (SQL Server) login, and associated user and user mapping, are provided in Install the PhixFlow Database Schema. If you do not wish to use this command, please consult PhixFlow Support.
Suggested configuration
The following configuration is suggested as a guide. It incorporates the minimum requirements for SQL Server databases above.
The provision of database files fits in with the scheme presented in MS SQL Server planningPlanning for disk partitions on the database server.
Logins
Create a new login as follows:
...
Parameter
...
Setting
...
Login Name
...
phixflow
...
Authentication
...
SQL Server
...
Password
...
Please provide to the PhixFlow installation consultant
...
Enforce password policy
...
No
...
Default database
...
Set to phixflow once the phixflow database has been created (below)
...
Default language
...
As appropriate
Database
Create a new database as follows:
...
Parameter
...
Setting
...
Name
...
phixflow
...
Owner
...
phixflow
...
Collation
...
Latin1_General_CI_AS
...
READ_COMMITTED_SNAPSHOT
...
ON
Filegroups
...
Name
...
Default
...
PRIMARY
...
Yes
...
Server settings
Option | Setting |
SQL Server Edition | Standard or Enterprise |
Version | |
Collation Character set | Latin1_General_100_CI_AI_SC_UTF8 |
Max Server Memory | Do not set a fixed maximum memory; see MS SQL Server Planning |
Server Authentication | SQL Server and Windows Authentication Mode |
Database, login and user settings
Note that it is possible to use MS SQL Server Integrated Authentication to authenticate PhixFlow against the database. This is especially useful on production servers where the number of people who can have access to key passwords is strongly controlled. However, for most instances, a local SQL Server login as below can be used.
The command given in Install the PhixFlow Database Schema will create a database, login and user with the suggested settings.
Filegroups
Name | Default | Autogrow All Files |
PhixFlow database | ||
PRIMARY | Yes | No |
Temp database | ||
PRIMARY | Yes | Yes |
Database Files
Logical Name |
Setting
phixflow
File Type | Filegroup |
Initial Size: 2000 MB
Autogrowth: 10% Unrestricted (or as per management policy)
Path: (As per management policy)
phixflow_data_01
Type: Rows Data
Filegroup: ANALYSIS_DATA
Initial Size: 100,000 MB
Autogrowth: 10% Unrestricted (or as per management policy)
Path: (As per management policy)
phixflow_log
Type: Log
Initial Size: 1000 MB
Autogrowth: 10% Unrestricted (or as per management policy)
Path: (As per management policy)(Initial) size | Autogrowth/ Maxsize | Path (see MS SQL Server Planning) | Filename | |||
PhixFlow database | ||||||
phixflow | ROWS Data | PRIMARY | 10,000 MB | By 1000MB, Unlimited | On Disk 1 - data files | phixflow.mdf |
phixflow_log | LOG | - | 1000 MB | By 64 MB, Unlimited | Disk 2 - transaction logs | phixflow_log.ldf |
Temp database | ||||||
temp | ROWS Data | PRIMARY | 1000 MB | By 64 MB, Unlimited | On Disk 3 - temp DB | tempdb.mdf |
temp_log | LOG | - | 100 MB | By 64 MB, Unlimited | Disk 2 - transaction logs | tempdb.ldf |