Server and Database Setup
This page is to help install an instance of the SQL Server database and configure it for use.
Contents
Installing and configuring SQL Server
A Microsoft SQL Server database is used to hold and manage all information associated with PRAMSX2. The first decision you must make is which version of SQL Server you will use. PRAMSX2 is verified to work with all SQL Server versions from SQL Server 2008 to SQL Server 2016, including the Express versions. Your PRAMS support representative will discuss this with you and recommend the best version for your situation.
Regardless of the version you choose to install there are a few items to be aware of when installing or using an existing SQL Server.
Instance Databases
The decision on whether to use instances may be made for you if the SQL Server already exists. If you are installing a new SQL Server you have the option of using them or not. Your PRAMS support representative will discuss whether you should specify instances or not. If instances are used the SQL Server Browser service must be enabled and the users must specify both the host and the instance when connecting to the SQL Server.
Mixed Mode Authentication and TCP/IP
All client processes use a direct TCP/IP connection to the SQL Server. To allow this you must enable mixed mode authentication during the installation process (or enable it later using the SQL Server Management Studio). If you are using an Express version of SQL Server the default setting do not enable TCP/IP connections. Once the installation of SQL Server is complete you will need to use the server configuration manager to enable this. You will be reminded that you need to stop and start the SQL Server service to enable these changes. If you are using an existing SQL Server that has already been installed you can skip the next section and continue on to ‘Creating the PRAMSX2 database user’.
Performing an new installation of SQL Server
Once you have determined which version of SQL Server you will be using you’ll need to acquire the installation package for that version. Whichever version is to be used you should make sure that it includes the SQL Server Management Studio. The installation .exe will normally have ‘WT’ in the filename, which stands for ‘With Tools’.
Run the installation process in normal fashion and accept the default setting provided with the following exceptions:
- Specify that you will use ‘Mixed Mode’ authentication. This mode allows both Windows Authentication and direct SQL authentication. You will be required to specify and ‘sa’ password. This password should be chosen by you and should be a secure password as it is the super user password for the SQL Server.
- Specify whether you will be using instances or not. If you choose to use instances you can either take the default name provided or enter one of your own. We recommend renaming the instance to ‘PRAMS’ so that it is easy to identify once the installation is complete.
The installation of Microsoft SQL Server can take anywhere from 5-30 minutes. Once it is complete close the installation program.
If you have installed an Express version you’ll need to access the SQL Server Configuration Manager and enable TCP/IP as discussed above.
Custom options for database installation
In certain cases you may want to change other items such as the recovery model to use and the location of the mdf/ldf files created for the database. If you think you need to alter these please discuss it with your PRAMS support representative.
Once the database has been created continue on to ‘Defining a database user’.
Creating the PRAMSX2 database
To create the base database open SQL Server Management Studio. Specify the server name and log in using either Windows Authentication or the sa username and password specified during the installation.
Once you have logged in, right click the ‘Databases’ node under the server and select ‘New Database..’.
In the new window that opens specify the name of the database you are creating. We suggest ‘pramsx2’ but you are free to name it anything you want. For most installations this is all you need to do. Click ‘OK’ to create the database.
Creating the PRAMSX2 database user
All processes that communicate with the SQL Server use a login created by you on the SQL Server. If you are not logged in to the SQL Server Management Studio do so as instructed in ‘Creating the base database’.
The user account must have a minimum of dbo type access to the database so the how you define the level of access you assign to this account should be considered within the current security requirements of your organization.
If the new database exists in the context of other databases on an existing SQL Server please refer to your database administrators requirements before creating the database user account. Navigate to the ‘Logins’ item which is under the Security node under the server. Right click the ‘Logins’ item and select ‘New Login…’. In the ‘Login – New’ dialog box that opens specify the login name for the account (we suggest ppadmin, but you can use any name you choose) and then change the login type from ‘Windows authentication’ to ‘SQL Server authentication’. Because you specified ‘SQL Server authentication’ you will need to specify a password for this login. Choose a secure password and enter it in the ‘Password’ and ‘Confirm password’ fields. Change the ‘Default database’ to be the database you created in the previous step. If this is a standalone installation we suggest setting the Server Roles for this login account to sysadmin and User mapping to the database you previously created to db_owner. Click ‘OK’ to create the database user.