OperationalTasks - x-ian/dha-mis-migration GitHub Wiki

Operational Tasks

Runtime environment

MS SQL Server 2014

  • Install SqlExpress & SQL Server Management Studio (SSMS)
  • Install Service Pack 2 for SQL Server
  • Run Windows Update
  • Activate TCP/IP protocol via Config Manager
  • Verify that under Server properties SQL Server and Win Auth mode are both activated

Most configurational activities can be performed through SSMS. This doesn't need to run on the local server installation (e.g. via Remote Desktop), but can also be used to remotely connect to the server instance.

MSSQL (Enterprise) without a license installs as a trial version. Full features for 180 days; afterwards most likely falls back to 'emergency mode'. Current MSSQL Enterprise version expires around end of June. Check how many days remaining:

sp_configure 'show advanced options', 1;
RECONFIGURE
GO

sp_configure 'Agent XPs', 1;
RECONFIGURE
GO

DECLARE @RemainingTime INT
DECLARE @InstanceName SYSNAME
SELECT @InstanceName = CONVERT(SYSNAME, SERVERPROPERTY('InstanceName'))
EXEC @RemainingTime = xp_qv '2715127595', @InstanceName
SELECT @RemainingTime 'Remaining evaluation days:'

GO

MSSQL Editions and restrictions (https://docs.microsoft.com/en-us/sql/sql-server/editions-and-supported-features-for-sql-server-2016#Anchor_0). Express is mostly limited to 1 CPU socket or 4 cores, 1 GB RAM, and 10 GB database size (unclear if file size or aggregated data limit).

Enable sa account with remote access: http://superuser.com/questions/730994/default-sa-password-of-sql-server-automatically-installed-by-sharepoint-server-2

Windows

Office 2016 doesn't support Windows XP.

Windows 7 License: According to most sources besides the notifications there is no other negative impact of using a non-genuine Windows version. Nevertheless the Access Runtime doesn't install on these machines (tested with 2). Maybe the Access runtime is also seen as a Windows Optional component / update, which also isn't properly working with a non-licensed version.

MS Access & MS Access Runtime environment

The runtime environment is a license-free executable-only Access setup. It allows to run the frontend (including re-linking to other databases), but has no Design and manual database navigation features. At least for data entry activities this is sufficient.

However the biggest difference between the Runtime version and the Full version is the error handling. The full Access installation simply stops the execution in case of unhandled errors (e.g. when no On Error Goto ... is set up) and displays a generic dialog with the options to debug or end the execution. But it will keep the database file open so that the user can either retry or continue with another area of the application. The runtime version however stops everything and even exits the whole application. Therefore it is necessary to catch at least the most important errors. A minimal implementation of such error handling is added to the data entry frontend (e.g. form_art_clinic_obs_v8). Here a 'conditional error handler' is configured which on is active when the application run on a Access runtime environment. In case of a Full Access version is behaves as before.

The Runtime environment can be simulated by creating a dedicated shortcut (e.g. MS Access 2016 Runtime Simulation) with this target: "C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" /runtime (applicable for 32 Bit version). Afterwards .accdb files can be drag-and-dropped to this to open them from a machine with full Access installation.

ODBC Driver

"MS ODBC Driver 11 for SQL Server" for integration between Access and MSSQSL needed. 32 and 64 Bit version exists. More recently the version 13 was published but no extensive testing happened. The required ODBC driver is hardcoded in the RelinkTables VBA module and needs to change whenever the ODBC Driver is changed too.

ODBC Drivers can be configured in multiple ways through the Windows Control Panel applet 'ODBC Sources'. All of these ways use a datasource name (DSN) to set it up. A DSN can either be machine-wide, specific to a user, be taken from a file (.dsn), or hard-coded in VBA code while establishing the linked table (used for DHA-MIS). Machine- and Use-rspecific setups depend on outside/additional configurations, so they were not used. But a File-based DSN can be handy for development/testing purpose, e.g. to quickly establish a new linked table through the Linked Table Manager. The easiest way to set up this file-based DSN is via the ODBC Control panel.

Active Directory

The MSSQL ODBC driver can either use username/password to authenticate directly against MSSQL users, or delegate the authentication towards Active Directory. With Activate Directory no additional username/password is require beyond a valid AD domain user on the local workstation. Windows, AD and MSSQL negotiate the permissions in the background. As of now every valid Domain user is granted (admin) access to the DB. Breaking this further down into different AD groups is possible.

Operational Tasks

Debugging/profiling/tracing

Backup & Restore

Manual backup can be invoked through SSMS:

BACKUP DATABASE [HIVData-test] TO  DISK = N'd:\MSSQLBackup\HIVData-test.bak' 
WITH NOFORMAT, INIT,  NAME = N'HIVData-test Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

An Automated backup is set up with this SQL Server Agent Job:

DECLARE @BackupFileName varchar(100)
SET @BackupFileName = 'e:\art_db_bkp\MSSQL-HIVData-Backups\HIVData-live_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') + '.BAK'
BACKUP DATABASE [HIVData-live] TO DISK=@BackupFileName
WITH NOFORMAT, INIT, COMPRESSION,  NAME = N'HIVData-live Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

(requires proper permissions for MSSQL Service, so far simply added full access for Everyone)

Starting SQL Server Agent Create Job with above Step Schedule e.g. every 4 hours

SQL Server Agent needs to run automatically at startup. If not, then check http://stackoverflow.com/questions/25619950/sql-server-agent-not-starting-automatically-on-server-reboot, https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ae60b5c0-a5fe-4b03-920e-e43518ccd6d5/sqlserver-not-starting-automatically-on-system-reboot?forum=sqldatabaseengine

User admin

Adding a new user requires a few steps: 1 An AD user as part of the Domain Users group 1 A mapping for this user in the local DHA-MIS table map_users to map from the AD username (e.g. cneumann) to a DHA-MIS 'user' (e.g. chrneu) (used for the columns CreateUser and UpdateUser).

This mapping can also be used to map local MSSQL users to valid DHA-MIS users.

MSSQL Trusted Connection

1 Create AD user, with AD domain 1 Add new login to MSSQL (under database, security, right click) 1 Advanced search for either user or group for AD location (group search is deactivated as default) 1 Select element from search 1 User Mapping to DB HIVDatabase3 1 Create Server Role and add login as member 1 Set permissions for each table, assign role and tweak permissions

http://dba.stackexchange.com/questions/51311/using-ad-groups-vs-roles-in-sql-server

Deployment/releasing

As of now only the frontend are (manually) versioned by adding a new row into the local version table. Tagging/labeling old versions by specifying the versioon number in file name of the binary Access database is done manually.

Additionally there is also no versioning of the backend database.

Show open DB connections

SELECT DB_NAME(dbid) AS DBName,COUNT(dbid) AS NumberOfConnections,loginame
FROM    sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)

Version control

Potential solution: http://stackoverflow.com/questions/187506/how-do-you-use-version-control-with-access-development

Offline deployment

A (full) backup file can be restored on another machine. As long as the DB size is below 10 GB, it can also be restored on a MSSQL Express installation. However: Double-verify the destination target for the restore, because as a default the name of the source for the backup is taken. E.g. when creating a backup of the HIVData-live instance, a restore defaults back to HIVData-live. This could potentially overwrite the current live database.

So a lightweight MSSQL Express installation on a workstation could be used as a development environment as well as an offline scenario, e.g. for presentations.