SQL Server Installation and Configuration
aPriori requires a SQL Server, MySQL, or Oracle database. This section describes the requirements for SQL Server database configuration. If you are using Oracle or MySQL instead, please go to either:
SQL Server configuration should be performed by a certified SQL Server Database Administrator.
Installing SQL Server
Before beginning SQL Server installation and configuration, you should note the following information that is required by the aPriori client:
Names of users, database instance names, port numbers, etc., are not assumed by the aPriori client. This means that you are free to specify values that make sense for your location. However, make note of the following information as you define it in the following steps, because it is required during the installation of the client software:
• SQL Server host name
• SQL Server port number (typically 1433)
• SQL Server database instance name
• SQL Server authentication style (either "SQL Server" or "Windows")
• SQL Server username for the aPriori database
• SQL Server password
If you use an existing installation of SQL Server for aPriori, you or a certified SQL Server administrator must identify the correct values to specify to the aPriori installation utility.
If you need to install SQL Server for aPriori, the installation defaults provided during a SQL Server installation are generally compatible with aPriori EXCEPT for the details specified below.
To install and configure SQL Server
Proceed to install SQL Server using the default procedures and values except:
• Specify the 'Mixed mode' authentication option. This setting allows you to use both SQL Server and Integrated (Windows) authentication:
• Once you have installed SQL Server, to set the TCP port and to define a user account, run the SQL Server Configuration Manager:
3 Bring up the Windows Start menu and in the Search programs and files field, start typing:
SQL Server 2014 Configuration Manager
10 When the SQL Server 2014 Configuration Manager link appears, click it.
11 In the resulting window, click the Protocols line under SQL Server Network Configuration, then right-click the TCP/IP line in the right column and Enable it.
12 Right-click TCP/IP again and click Properties.
13 Click the IP Addresses tab.
14 Scroll down to the IPAll section and enter a value for TCP Port. The typical port is 1433.
15 Restart SQL Server.
16 Create an "apriori" login and specify a secure password. You can do so either with SQL Server Management Studio (SSMS), or from the command line using the following syntax:
sqlcmd -S "localhost,1433" -d master -Q "CREATE LOGIN apriori WITH
PASSWORD='< apriori_db_password >'
PASSWORD='< apriori_db_password >'
Notes:
• This command assumes that your Windows user account was included as a SQL Server administrator during the install. If the account is not an SQL Server admin, add: -U sa -P <password>
• SQL Server distinguishes between Logins and Users. Logins are associated with the Server; User are associated with individual Databases. Logins get mapped to Users. When aPriori requires a database username in order to establish a connection to the database it needs the Login name.
To create a SQL Server database instance
You should create an SQL Server database instance for the aPriori Client before you install aPriori Professional. This section shows how to create a database instance. If you plan to use more than one version of aPriori, each should use a separate database instance. If the client is intended to connect to an existing database instance, skip this step.
17 Create a database, ensuring the Is Read Committed Snapshot On option is enabled.
Note: You might experience hangs or deadlocks if this option is not enabled.
You can enable this option from the command line as shown below. You can also use SQL Server Management Studio (SSMS) if you prefer.
sqlcmd -S "localhost,1433" -d master -Q "CREATE DATABASE [apriori_db]; ALTER DATABASE [apriori_db] SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE [apriori_db] SET AUTO_CLOSE OFF; ALTER AUTHORIZATION ON DATABASE::[apriori_db] TO apriori"
Notes:
• This example creates a database named apriori_db. You can specify whatever name you like, but you must ensure that it is used consistently wherever the database is referenced.
• This configures the 'apriori' user (created earlier) as the database's owner. If you plan to do everything using your Windows user account, you can omit the 'ALTER AUTHORIZATION' clause.
• The SQL shown also disables the auto-close feature. This is typically disabled by default but could be enabled in some versions of SQL Server. (The auto-close feature closes the database and releases some resources when there are no users connected. In a production environment, closing the database has little value and typically degrades performance for the next connection.)
18 Ensure that the Collation setting under Options is set to SQL_Latin1_General_CP1_CI_AS
Notes:
– Collations that have either “_CS_” or _BIN in their name prevents aPriori from working correctly. aPriori has table names such as “fbc_ComponentState”) that have uppercase letters in their name, but which get called from within aPriori as all lowercase (e.g., “fbc_componentstate”). A database with a CI (case insensitive) collation ignores the difference, while a database with CS (case sensitive) or BIN (binary comparison) fails to open the table in request.
– Non-USA installations may be able to substitute other suitable collation settings as documented on this Microsoft page: https://msdn.microsoft.com/en-us/library/ms143508(v=sql.105).aspx
– Collations that have either “_CS_” or _BIN in their name prevents aPriori from working correctly. aPriori has table names such as “fbc_ComponentState”) that have uppercase letters in their name, but which get called from within aPriori as all lowercase (e.g., “fbc_componentstate”). A database with a CI (case insensitive) collation ignores the difference, while a database with CS (case sensitive) or BIN (binary comparison) fails to open the table in request.
– Non-USA installations may be able to substitute other suitable collation settings as documented on this Microsoft page: https://msdn.microsoft.com/en-us/library/ms143508(v=sql.105).aspx
19 Import the aPriori standarddata<version>.sql file into the newly created database, using the following command line (replacing <version> with the actual platform and version suffix to the file name):
sqlcmd -S "localhost,1433" -U apriori -P <apriori_db_password> -d apriori_db
-i standarddata<version>.sql
-i standarddata<version>.sql
Notes:
• This assumes that you are using the apriori user created earlier. The -U and -P parameters can be omitted if you instead use your Windows account.
• Several warning messages of the form appear:
"Warning! The maximum key length is 900 bytes."
These can be ignored. The client includes runtime checks to avoid exceeding SQL Server's maximum key length.
• However, there is a limit of 404 characters on the combined length of the component and scenario names. Any attempt to save or migrate a component that exceeds this limit fails with an error messages similar to:
"The scenario name is too long. The combined length of the component and scenario names must not exceed 404 characters..."
If you encounter such a message when using aPriori, you must rename the problematic scenario.
aPriori Client Installation Adjustments
When you install the aPriori Client (see Installing the aPriori Professional Client) after installing SQL Server, make the following adjustments:
At the Database Schema Configuration panel, select SQL Server and fill in the fields reflecting the values used in the database configuration above.
Note the option to choose either 'SQL Server' or Windows Authentication ("Integrated/Native Authentication"). In the latter case, the username and password fields are redundant and are therefore disabled.
Notes:
• In the SQL Server environment, the Database Schema Name field changes to Database Instance Name. Schemas are distinct from database instances in SQL Server. aPriori uses the database user’s default schema, typically ‘dbo’. Distinct database instances are used in cases where distinct schemas would be used with MySQL or Oracle.
• If you select SQL Server Authentication, the username field requires the login name created earlier (for example, enter apriori if the earlier command line example was used exactly as shown).
20 Complete the install as directed in the installation chapter.
A Note About Deleting SQL Server Databases
You may find that attempts to delete SQL Server databases fail or time-out due to connections having been left open. This is easily solved:
• When deleting a database from SQL Server Management Studio, check the Close existing connections' option:
• When deleting a database from the command line, set the database into 'single user' mode to kill off any other connections:
sqlcmd -S "localhost,1433" -d master -Q "ALTER DATABASE [apriori_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [apriori_db]"