The Rapid Inquiry Facility

The RIF helps epidemiologists and public health researchers in environmental health activities.

SQL Server Development Database Installation

  1. Install SQL Server
  2. Create Databases and Users
    1. Network connection errors
    2. Power User Issues
  3. Create Additional Users
    1. Logon errors
      1. Wrong server authentication mode
  4. Installing the RIF Schema
    1. BULK INSERT Permission
    2. Re-running scripts
      1. Geospatial script: rif40_sahsuland_tiles.bat
      2. Re-load sahsuland example data
    3. SQL Server Backup and Restore
    4. SQL Server BULK INSERT Issues
      1. Line Termination
  5. Script Notes
    1. Script and documentation TODO

Install SQL Server

Install SQL Server 2012 SP2 or 2016(Express for a test system/full version for production). DO NOT INSTALL SQL Server 2008 or before:

SQL Server 2012 developer/evaluation edition databases are limited to 5G in size. This is a series limitation for the RIF. The SQL Server 2016 developer edition does not haves this limitation and is therefore recommended for RIF development. Note:

See: https://www.matrix42.com/blog/2016/09/23/how-free-is-microsoft-sql-server-developer-edition-really/

If you install SQL Server 2014+; make sure SQL Server Management Studio has been installed; SQL Server Management Studio

Check the version of your database:

sqlcmd -E
1> SELECT @@version AS version, compatibility_level FROM sys.databases Where DB_NAME() = name ;
2> go
version                                                                                               compatibility_level
----------------------------------------------------------------------------------------------------- -------------------
Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64)
        Sep 23 2016 16:56:29
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)                 110

or for SQL Server 2016

1> SELECT @@version AS version, compatibility_level FROM sys.databases Where DB_NAME() = name ;
2> go
version                                                                                              compatibility_level
---------------------------------------------------------------------------------------------------- -------------------
Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64)
        Jul  6 2017 07:55:03
        Copyright (c) Microsoft Corporation
        Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 16299: )

Create Databases and Users

This section details the original method using the script rif40_development_creation.sql. This section is useful to sort out connection problems. The easier way to rebuild the RIF is to skip to section 4, and run rebuild_all.bat which runs all the required scriipts in sequence and prompts for the RIF user and user password.

Run the following command as Administrator in this directory (…rapidInquiryFacility\rifDatabase\SQLserver\installation):

sqlcmd -E -b -m-1 -e -i rif40_development_creation.sql

Note:

Network connection errors

This is when the above command will not run.

C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation>sqlcmd -E -b -m-1 -e -r1 -i rif40_database_creation.sql
Result 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

Power User Issues

This is caused by rebuild_all.bat failing complaining the user is not an Administrator when run as a power user.

sqlcmd -E
1> SELECT user_name();
2> GO

--------------------------------------------------------------------------------------------------------------------------------
guest

(1 rows affected)
1> quit

The solution to this is to:

Create Additional Users

Run the optional script rif40_development_user.sql. This creates a default user %newuser% from the command environment. This is set from the command line using the -v newuser= and -v newpw= parameters. Run as Administrator:

sqlcmd -E -b -m-1 -e -i rif40_development_user.sql -v newuser=peter -v newpw=XXXXXXXXXXXXXXXX

Now test your can connect to the database and check your object creation privileges:

C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\Postgres\psql_scripts>sqlcmd -U peter -P peter -d test
1> SELECT db_name() AS db_name INTO test_table;
2> SELECT * FROM test_table;
3> go

(1 rows affected)
db_name
--------------------------------------------------------------------------------------------------------------------------------
test

(1 rows affected)
1> quit

C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\Postgres\psql_scripts>

Logon errors

Test for logon errors as using the command: sqlcmd -U peter -P XXXXXXXXXXXXXXXX -d sahsuland_dev

Test all combinations of sahsuland/sahsuland_dev/test databases.

Wrong server authentication mode

The server will need to be changed from Windows Authentication mode to SQL Server and Windows Authentication mode. Then restart SQL Server. See: https://msdn.microsoft.com/en-GB/library/ms188670.aspx

C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\Postgres\psql_scripts>sqlcmd -U peter -P XXXXXXXXXXXXXXXX -d sahsuland_dev
Msg 18456, Level 14, State 1, Server PETER-PC\SQLEXPRESS, Line 1
Login failed for user 'peter'.

In the Window “applications” event log:

Login failed for user 'peter'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

The node also show how to enable the sa (system administrator) account. As with all relational database adminstration accounts as strong (12+ chacracter) password is recommended to defeat attacks by dictionary or all possible passwords.

This is what a successful login looks like: sqlcmd -U peter -P XXXXXXXXXXXXXXXX

C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\Postgres\psql_scripts>sqlcmd -U peter -P XXXXXXXXXXXXXXXX
1> SELECT db_name();
2> GO

--------------------------------------------------------------------------------------------------------------------------------
sahsuland_dev

(1 rows affected)
1>

Installing the RIF Schema

A script (rapidInquiryFacility\rifDatabase\SQLserver\installation\rebuild_all.bat) is provided to build everything and create an example study. Note that this user’s password will be the username, so change it on a networked system:

Before you run rebuild_all.bat check the BULK INSERT permission

These scripts do NOT drop existing tables, the database must be rebuilt from scratch. You must build sahusland_dev before sahsuland; as sahsuland_dev is exported as the basis for sahsuland.

The batch tests for Administrator or power user privilege; gets the settings as detailed above and then runs the following scripts as Administrator:

The individual scripts can be run by batch files for sahsuland_dev only, but they must be run in this order:

An additional script in the installation directory can be used to create an example study, for example the ** user created by the script *rif40_test_user.sql*:

sqlcmd -U <my new user> -P <my new user> -d sahsuland_dev -b -m-1 -e -r1 -i rif40_run_study.sql

BULK INSERT Permission

SQL Server needs access permission granted to the directories used to BULK INSERT files, the files are not copied from the client to the server as in the Postgres psql ```\copy` command and the Oracle sqlldr command.

SQL Server needs access to the relative directories: ....\GeospatialData\tileMaker and ....\DataLoaderData\SAHSULAND. The simplest way is to allow read/execute access to the local users group (e.g. PH-LAPTOP\Users or USERS depending on your Windows version).

The following directories need to have read and execute permission granted to local users (and subdirectories):

The following directories need to have read, write, modify and execute permission granted to local users (and subdirectories):

DO NOT TRY TO RUN BULK INSERT FROM NETWORK DRIVES or CLOUD DRIVES (e.g. Google Drive). Use a local directory which SQL Server has access to; e.g. somewhere on the C: drive. Note that SQL Server BULK LOAD behaves deterrently if you logon using Windows authentication (where it will use your credentials to access the files) to using a username and password (where it will use the Server’s credentials to access the file).

BULK INSERT rif_data.lookup_sahsu_grd_level1
FROM 'C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation\..\..\GeospatialData\tileMaker/mssql_lookup_sahsu_grd_level1.csv'     -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
        FORMATFILE = 'C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation\..\..\GeospatialData\tileMaker/mssql_lookup_sahsu_grd_level1.fmt',            -- Use a format file
        TABLOCK                                 -- Table lock
);

Msg 4861, Level 16, State 1, Server PH-LAPTOP\SQLEXPRESS, Line 7
Cannot bulk load because the file "C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation\..\..\GeospatialData\tileMaker/mssql_lookup_sahsu_grd_level1.csv" could not be opened. Operating system error code 5(Access is denied.).

Re-running scripts

Geospatial script: rif40_sahsuland_tiles.bat

Re-running the geospatial script: rif40_sahsuland_tiles.bat will produce the following error:

-- SQL statement 75: Remove old geolevels meta data table >>>
DELETE FROM t_rif40_geolevels WHERE geography = 'SAHSULAND';

Msg 547, Level 16, State 1, Server PH-LAPTOP\SQLEXPRESS, Line 5
The DELETE statement conflicted with the REFERENCE constraint "rif40_covariates_geolevel_fk". The conflict occurred in database "sah
suland_dev", table "rif40.rif40_covariates".
Msg 3621, Level 0, State 1, Server PH-LAPTOP\SQLEXPRESS, Line 5
The statement has been terminated.

To resolve: delete the covariates. You must re-run rif40_sahsuland_data.bat afterwards.

DELETE FROM rif40.rif40_covariates WHERE geography = 'SAHSULAND';

Re-load sahsuland example data

Re-load sahsuland example data with: rif40_sahsuland_data.bat

SQL Server Backup and Restore

SQL Server needs access granted to the directories used to BACKUP and to RESTORE files.

SQL Server needs access to the relative directory: ..\production. The simplest way is to allow full control to the local users group (e.g. PH-LAPTOP\Users).

DO NOT TRY TO BACKUP or RESTORE FROM NETWORK DRIVES or CLOUD DRIVES (e.g. Google Drive). Use a local directory which SQL Server has access to; e.g. somewhere on the C: drive. Note that SQL Server BACKUP and RESTORE behaves differently if you logon using Windows authentication (where it will use your credentials to access the files) to using a username and password (where it will use the Server’s credentials to access the file).

--
-- Export database to ../production/sahsuland_dev.bak
-- Grant local users full control to this directory
--
BACKUP DATABASE [sahsuland_dev] TO DISK='C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation\..\
production\sahsuland_dev.bak';

Msg 3201, Level 16, State 1, Server PH-LAPTOP\SQLEXPRESS, Line 6
Cannot open backup device 'C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation\..\production\sah
suland_dev.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Server PH-LAPTOP\SQLEXPRESS, Line 6
BACKUP DATABASE is terminating abnormally.
rif40_sahsuland_dev_install.sql exiting with 1
rif40_sahsuland_dev_install.bat exiting with 1

SQL Server BULK INSERT Issues

Line Termination

BULK INSERT rif_data.pop_sahsuland_pop FROM 'C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation\..\..\DataLoaderData\SAHSULAND/pop_sahsuland_pop.csv'
WITH
(
   FORMATFILE = 'C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation\..\..\DataLoaderData\SAHSULAND/pop_sahsuland_pop.fmt',
   TABLOCK,
   FIRSTROW=2
);

Msg 245, Level 16, State 1, Server PETER-PC\SAHSU, Line 3
' to data type int.hen converting the varchar value '0

Script Notes

Msg 3729, Level 16, State 1, Server PH-LAPTOP\SQLEXPRESS, Line 6
Cannot DROP FUNCTION 'rif40.rif40_sequence_current_value' because it is being referenced by object 'DF__t_rif40_r__inv_i__12A9974E'.

Fix by running manually:

Script and documentation TODO

Still to do (low priority):

Peter Hambly 2nd March 2017