Bigger Topics - x-ian/dha-mis-migration GitHub Wiki

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).

MS Access 2016 used for development. 2016 runtime environment should work. On Full Access 2016 installation create shortcut to "C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" /runtime and then drag and drop an ACCDB file to this shortcut to simulate the runtime environment. Note that the runtime environment behaves differently in case of unhandeld runtime errors. Instead of showing the End, Debug, Cancel dialog it exits the whole application. "Execution of this application has stopped due an runtime error . The application can't contniue and will be shut down."

Manual backup

Automated backup 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

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

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

Potentially moving 'heavy queries' from Frontends to MSSQL views. But custom VBA code isn't supported in MSSQL. So if the logic can not be rewritten in T-SQL, then the query can't be migrated to MSSQL. (https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx)

Bound vs. Unbound Access apps

Differentiate between backend calls and frontend/report processing time

Traces every interaction with MSSQL, e.g. also all ODBC calls from Access. Access uses a variety of different access approaches, so it might not always be obvious how a request from Access is mapped to SQL. Either through parameterized queries, direct SQL statements or stored procedures (e.g. for paging). The duration of each interaction is included in the trace, so

• Via ODBC Control Panel Applet
• Requires restart of Access?

Useful Stats:

• CPU usage
• Network Interface bandwidth
• Network Interface bytes/sec send
• Network Interface bytes/sec receive

Create new table

• Columns
• Constraints
• Index

Define view