T-ODS Snapshot Utility

Introduction

The Ed-Fi Temporal ODS Snapshot Utility is a command-line tool provided to licensees to facilitate the management of T-ODS snapshot metadata, snapshot configuration, and snapshot loading. The Snapshot Utility can be run in interactive mode or via a command-line mode, which supports automated execution.

Overview

The Snapshot Utility is built in C# and works together with stored procedures and functions in the T-ODS to perform its tasks. For example, the snapshot load process’s default setting calls database features to take a snapshot, and orchestrates other key tasks such as referential integrity checks and data quality checks.

The Ed-Fi T-ODS Snapshot Utility supports the following actions:

• Create and update Snapshot Metadata
• Load Snapshot
• Lookup Snapshot Metadata
• Delete Snapshot and Snapshot Metadata for “inactive” snapshots (e.g., Snapshots that failed to complete)
• Interactive, manual, and automated execution
• Create initial partitions and addition of partitions

In addition, the T-ODS Snapshot Utility is able to:

• Display Snapshot Metadata including a flag that indicates whether a snapshot is active/inactive and available for query
• Capture and surface processing messages, post-processing messages, warnings, and errors
• Execute, activate, and deactivate UDM Audit configurations and Count Audit configurations
• Perform external referential integrity checks
  

A Note on the Microsoft SQL Server Snapshot Feature

Microsoft SQL Server includes a Snapshot feature. A database Snapshot in the Microsoft stack context is a static, read-only view of a SQL Server database.

The Ed-Fi Temporal Data Snapshot Utility leverages the SQL Server Snapshot feature by default to ensure that the data stored is consistent with the state of the information at the moment the T-ODS snapshot was initiated. However, certain editions of SQL Server do not support the Snapshot feature, elevated permissions are required, and ample storage must be available on the server – so the SQL Snapshot feature is not always desirable. 

Accordingly, the Snapshot Utility provides a bypass switch to disable the SQL Snapshot feature during loads.

Command-Line Options

Command-Line options are provided for unattended operation of the T-ODS Snapshot Utility tool. 

The T-ODS Snapshot Utility contains comprehensive help information for every unattended mode option, and is the most authoritative source of usage information. To view the help information, provide a single command line parameter "help". To view detailed help information for an operation type "help <operation name>".

The following functions are provided by the T-ODS Snapshot Utility Tool:

Set-up and Configuration Options

Database Settings

Users running the snapshot process will need the following permissions on the ODS:

• db_datareader (ODS and Master database)

• db_datawriter

• db_dlladmin

• Execute on t_edfi and tods schemas

To use the default Microsoft SQL Server snapshot setting, the user running the snapshot process will need the following:

• SQL Server Enterprise Edition
• dbcreator server role and db_datareader access to the Master database

Snapshot Utility Settings

The Snapshot Utility is included in the Ed-Fi ODS repo in the Utilities folder:

\Ed-Fi-ODS\Utilities\EdFiTemporalOds\EdFi.Ods.Tods.ConsoleApp\

Open the project solution and Build in Visual Studio 2015 or later.

Running the Snapshot Utility

The Snapshot Utility can be run in interactive mode for initiating actions manually or reviewing log entries on screen. Interactive mode is a great way to learn the system, and can be useful when performing actions like reviewing logs.

The Snapshot Utility can also be run in command-line mode, which allows for unattended operation. Command-line mode is useful for running scheduled jobs.

Worth noting is that all Snapshot Utility actions are available in both modes, and the parameters required are the same – so you can use whichever mode is appropriate for your situation.

Running the Snapshot Utility in Interactive Mode

Open a command-line window and navigate to the console app executable:

C:\Ed-Fi-ODS\Utilities\EdFiTemporalOds\EdFi.Ods.Tods.ConsoleApp\bin\Debug\

To run the Snapshot Utility in interactive mode, simply run:

.\EdFi.Ods.Tods.ConsoleApp.exe

The T-ODS Snapshot Utility provides a menu of prompts for creating, loading, and managing snapshots and the snapshot configurations. 

When the interactive mode is used, an equivalent unattended mode command-line options string is displayed as a scripting aid.

Running the Snapshot Utility in Unattended Mode

To run via an automated execution (i.e., unattended mode), run:

.\EdFi.Ods.Tods.ConsoleApp.exe [params]

And pass in appropriate parameters.

Command Line Parameters

Command line arguments without spaces may be quoted or unquoted. Arguments containing spaces should be quoted.

Connection Strings

Most operations require a connection string (-s or --connection) which may be a valid SQL connection string or the name of a connection defined in the .config file as follows:

--connection "Data Source=(local);Initial Catalog=sampleOdsDb;Integrated Security=True;"

--connection predefinedConnectionName

Operation and Command Line Parameters

Parameters vary depending upon the operation being invoked. The order of the parameters is not important.

create-metadata

-n, --name Required. The snapshot name

--description A description for the snapshot

-d, --date Required. The date of the snapshot

-b, --beginDate Required. The first covered by the snapshot data (inclusive)

-e, --endDate The last day covered by the snapshot data (exclusive)

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

update-metadata

-n, --name The snapshot name

--description A description for the snapshot. Pass 'null' to force the description to be null.

-b, --beginDate The first covered by the snapshot data (inclusive)

-e, --endDate The last day covered by the snapshot data (exclusive). Pass 'null' to force the end date to be null.

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

master-load

--bypass (Default: false) Bypass creating SQL server snapshot

-n, --name Required. The snapshot name

--description A description for the snapshot

-d, --date Required. The date of the snapshot

-b, --beginDate Required. The first covered by the snapshot data (inclusive)

-e, --endDate The last day covered by the snapshot data (exclusive)

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

delete-metadata

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

delete-code

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

load-data

--bypass (Default: false) Bypass creating SQL server snapshot

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

delete-data

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

count

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

audit-count

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

audit-udm

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

audit-ex-ref

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

lock

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

unlock

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

list-count-log

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

list-audit-log

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

create-code

-c, --code Required. The existing snapshot code

-n, --newCode Required. The new snapshot code

-s, --connection Required. The SQL connection string

update-code

-c, --code Required. The existing snapshot code

-n, --newCode Required. The new snapshot code

-s, --connection Required. The SQL connection string

get-metadata

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

get-metadata-byrange

-b, --beginDate Required. The first covered by the snapshot data (inclusive)

-e, --endDate The last day covered by the snapshot data (exclusive)

-s, --connection Required. The SQL connection string

list-count-config

-c, --code The count config code

-s, --connection Required. The SQL connection string

inactivate-count-config

-c, --code The count config code

-s, --connection Required. The SQL connection string

activate-count-config

-c, --code The count config code

-s, --connection Required. The SQL connection string

list-udm-config

-c, --code The count config code

-s, --connection Required. The SQL connection string

inactivate-udm-config

-c, --code The count config code

-s, --connection Required. The SQL connection string

activate-udm-config

-c, --code The count config code

-s, --connection Required. The SQL connection string

list-partition-config

-y, --schoolYear The school year for the partition (yyyy-yyyy)

-s, --connection Required. The SQL connection string

create-initial-partition-scheme

-s, --connection Required. The SQL connection string

create-next-partition

-s, --connection Required. The SQL connection string

update-partition-config

-y, --schoolYear Required. The existing value for school year (yyyy-yyyy)

-n, --newSchoolYear Required. The replacement value for school year

-b, --beginDate Required. The first covered by the snapshot data (inclusive)

-e, --endDate The last day covered by the snapshot data (exclusive)

-s, --connection Required. The SQL connection string

get-auditlog-byrange

--min (Default: -2147483648) The minimum error number to include

--max (Default: 2147483647) The maximum error number to include

-c, --code Required. The snapshot code

-s, --connection Required. The SQL connection string

delete-orphans

-s, --connection Required. The SQL connection string

Troubleshooting

In general, when an error is encountered, ensure that all required parameters are included in the command. Also ensure that all required parameters are being passed in a valid format. See the help menu for a list of parameters and formats.

Also see the T-ODS error guidelines here for additional information on T-ODS business rules, validations and error processing.

Errors when trying to run master-load or load-data

Error: The SELECT permission was denied on the object 'sysaltfiles', database 'mssqlsystemresource', schema 'sys'

Possible causes:

• The user account does not have adequate permissions to utilize the SQL Snapshot feature.
• This can be resolved by adding the user to the dbcreator server role and granting db_datareader access to the Master database.
• The other option will be bypassing the SQL Snapshot feature by setting the bypass parameter to true (-bp 1)

Errors when Loading data for Snapshot of SnapshotCode [snapshotcode]

Error: Database Snapshot is not supported on Standard Edition (64-bit).

Possible cause:

• The SQL Server version does not support snapshotting.
• To resolve this, run the snapshot utility on Enterprise Edition or set the bypass SQL Snapshot feature parameter to true.

Error when trying to run create-initial-partition-scheme

Possible remedy:

• Ensure that you are trying to create the partition on SQL Server version that supports partitioning.