SQL Server Installation

1 Versions supported
2 Express edition
3 Install
4 Network
5 Firewall
6 Database
7 Logins / Users
8 Backup

Versions supported

SQL Server 2008 R2 onwards is supported.

Express edition

The SQL Server Express edition will be more that adequate for most installations. Multiple instances can be installed on the same server to avoid conflict with other applications that use SQL Server such as MYOB, Act!, Maximizer.

If you already have SQL Server installed then you can just create a new database in the current installation. To avoid conflicts with other applications using the server you may elect to install an Icare specific instance.

Install

The following editions are available for download:

SQL Server 2012 LocalDB - This is the simplest option for a single PC installation (no connections are allowed from other PCs),
SQL Server 2017 Express,
SQL Server 2012 Express,
SQL Server 2008 R2 Express.

Network

Unless you are installing Icare and the server on a stand alone PC/laptop you will need to enable networking.

In the SQL Server Configuration Manager:

Enable TCP/IP protocol for the ICARE instance,
Restart ICARE instance.

The SQL Server Browser makes client configuration easier by allowing you to refer to server instances in the form SERVER\INSTANCE rather than specific network address an port number.

In the SQL Server Configuration Manager:

Set Start Mode of SQL Server Browser service to Automatic
Start SQL Server Browser

Firewall

If the server has a local firewall you may need to allow inbound access to the following items:

SQL Server
SQL Server Browser

It is recommended to allow access via by the application (exe file) rather than ports since the ports are dynamic if you use anything other than the default instance name.

Database

Create an empty database on the server, give it the name "Icare".

Logins / Users

Add each of the users who will be accessing the Icare database.

Give each user the following role membership:

db_datareader
db_datawriter
db_ddladmin

Backup

SQL Server databases can be backed up into single files. These files are more suitable for restoring after failure and onto new machines.

Full versions of SQL Server can have backup schedules prepared to produce these backup files automatically. When using the Express edition of SQL Server this automation must be setup with other tools.

A batch script such as the following can be prepared:

@echo off
set SERVER=COMPUTER
set INSTANCE=SQLEXPRESS
set DATABASE=Icare
set LOCAL=C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Backup\Icare.bak
set SQLCMD="C:\Program Files\Microsoft SQL Server\100\Tools\binn\SQLCMD.exe"
set SCRIPT="BACKUP DATABASE [%DATABASE%] TO DISK = N'%LOCAL%' WITH FORMAT, INIT, NAME = N'Full Database Backup of %DATABASE%', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
%SQLCMD% -E -S %SERVER%\%INSTANCE% -Q %SCRIPT%
for /F "delims=/ tokens=1,2,3" %%a in ('date /t') do call :dated %%a %%b %%c
set BACKUP="C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Backup\Icare backup at %DATED%.bak"
copy "%LOCAL%" "%BACKUP%"
pause
goto :EOF
:dated
set DATED=%4-%3-%2
goto :EOF

Such a script can be saved in a *.cmd file and then scheduled to run nightly with the Windows Task Scheduler