Data Update Job changes

Data Update Job is the daily-recurring SQL Server Agent scheduled job that connects Snow Inventory with Snow License Manager, ensuring that the data collected in Snow Inventory is correctly replicated in Snow License Manager for use in license compliance calculations, managing computers, reports, and visualization.

note

In Microsoft SQL Server Agent, the name of this job is License Manager Data Update—the job content has been entirely revised.

Data Update Job for Snow License Manager 9 contains approximately 100,000 lines of rewritten code designed to yield greater efficiency of the data update process.

The sections below communicate key Data Update Job changes introduced with the release of Snow License Manager 9.

note

Data Update Job for Snow License Manager 9 requires the use of Snow Inventory 6.

In addition, see DB changes due to Data Update Job modifications for a reference of SnowInventory and SnowLicenseManager database objects that have been added, changed, and deleted as a result of the modifications introduced in Data Update Job for Snow License Manager 9.

A new model

Data Update Job in Snow License Manager 9 uses a new dependency model that defines how data flows between the SnowInventory and SnowLicenseManager databases. New database schemas and aligned table names are indications of this change.

This new model provides the foundation for Data Update Job incremental data handling and parallel SQL job execution (which in turn affects SQL database user permissions compared to Snow License Manager 8).

Database schemas and aligned table names

With the release of Snow License Manager 9, two new schemas contain Data Update Job-specific database objects:

• SnowInventory database: The slm schema has been added.

• SnowLicenseManager database: The inv schema has been added.

Furthermore, table names in these two databases are now aligned, to make for a more uniform and more traceable experience. Here is an example:

• SnowInventory source database table: inv.DataNetworkAdapter

• SnowInventory SYNONYM: slm.inv_tblComputerNetworkAdapter

• SnowLicenseManager staging database table: inv.tblComputerNetworkAdapter

• SnowLicenseManager target database table: dbo.tblComputerNetworkAdapter

Dependencies in the flow of data

In Data Update Job for Snow License Manager 9, the following three concepts have been introduced:

• DataFlow, a non-scheduled SQL job that makes a simple call to a Get* or Update* procedure. Defined in the SnowLicenseManager database table inv.tblDataFlow.

• DataFlowDependency, a table (inv.tblDataFlowDependency) in the SnowLicenseManager database that defines the parent-child relation between two dataflows and also to which DataStream (see below) this relation belongs. The parent DataFlow must have finished running before the child DataFlow can start.

• DataStream, which is simply all dependent DataFlows joined together to form the full Data Update Job. Defined in table inv.tblDataStream.

Parallel-running jobs

Instead of one SQL job comprised of many serial steps, Data Update Job for Snow License Manager 9 uses non-scheduled jobs that run in parallel through a new job handler procedure.

note

These jobs are triggered by License Manager Data Update, which can be considered the "master job."

From a Microsoft SQL Server perspective, the power of parallelism is evident at the start of Data Update Job, at which point all available SQL Server processors work at maximum performance to handle multiple jobs simultaneously. The CPU load decreases as soon as the parallel load of data from SnowInventory is complete, which typically should be finished within a couple of minutes. Moreover, Data Update Job for Snow License Manager 9 processes all customers (CIDs) for a Service Provider Edition of Snow License Manager in parallel.

Incremental data transfer

Data Update Job now handles data incrementally, transferring to SnowLicenseManger only the SnowInventory data that has been added or changed.

This incremental data transfer occurs on all levels. For example, if the only change in a computer between two runs of Data Update Job is a newly installed hard drive, only data that involves the hard drive change will be transferred during the latter run of Data Update Job.

Here is an overview of how this happens:

1. First, data from the SnowInventory database is moved into an intermediate table (or staging area) in the SnowLicenseManager database.

2. Then, Data Update Job continues to process data in the staging area, which is saved in the final tables of the SnowLicenseManager database.

note

Although these processes run sequentially, each process loads the delta data in parallel, making the overall data transfer very time efficient.

To do this, Data Update Job uses new objects in both databases, to correspond more to the database design in SnowInventory. For each table in SnowInventory that moves all or parts of its data to SnowLicenseManager, there is:

• A Get* procedure that takes the delta data from the source SnowInventory table and places it in the correct SnowLicenseManager staging area (a table in the inv schema) by using the SQL Server synonym function:

  

• An Update* procedure that moves the staging data into final target tables in the dbo schema

Initial Data Update Job run

When Data Update Job for Snow License Manager 9 is run for the very first time, the last-known state for every computer and user object in the SnowInventory database is transferred to the SnowLicenseManager database's staging area.

In other words, this is not all available data in the SnowInventory database. Data Update Job only loads the latest logged version (SequenceNumber) of data for each object in the SnowInventory database (examples: ClientId, UserId).

At the same time, this initial Data Update Job also loads the entire dataset from two potentially very large database tables (inv.DataMetering and inv.DataMeteringConcurrency). This is the reason why this one-time initial run requires considerable time to complete, as compared to subsequent incremental runs.

Understanding the data transfer picture

The following illustrates how Data Update Job's incremental data transfer works (using a simple computer printer example), starting from the perspective of the SnowInventory database. This will display to the Snow License Manager 9 web interface user all of the printers connected to a specific computer.

Diagram explanation:

• SnowInventory has no knowledge of SnowLicenseManager, so there is no database at the direct receiving end of the Get* data transfer (large black arrow).

• However, the synonym slm.inv_tblComputerPrinter acts as a file explorer shortcut, pointing to the SnowLicenseManager database staging area table inv.tblComputerPrinter, to which the following is delivered by the slm.GetComputerPrinter procedure:

  • The slm.tblInventoryDatabase unique integer value InventoryDBID, which identifies this specific SnowInventory database instance

  • All new printer data from inv.DataPrinter per ClientId (printer data connected to computers from SnowInventory) since the last successful Data Update Job job run:

    • The Get* procedure here filters the ClientId by using the following values stored in the specific SnowInventory slm.tblInventoryDatabase table (identified by its unique InventoryDBID):

      • OldSequenceNumber/NewSequenceNumber

      • OldRowVersion/NewRowVersion

Completing the picture: Initial vs. incremental run

To complete your understanding of the data transfer picture, the diagrams below compare the day one initial Data Update Job run to the day two incremental run. We can look at this as taking place in six distinct steps, three for each day.

1. Day 1: initial run (steps 1-3)—using the simple computer-printer example, you can follow the numbered steps in the diagram below to take a closer look at this process (the numbers in green represent new information in each diagram):

   

   Here in step one, Data Update Job reads the largest available SequenceNumber and RowVersion in the SnowInventory database, storing these values in the table slm.tblInventoryDatabase as NewSequenceNumber and NewRowVersion.

2. All data between Old and New values are loaded into the SnowLicenseManager staging table.

3. The staging table data is merged into the final target table.

4. Day 2: incremental run (steps 4-6):

   

   Day two starts with the fourth Data Update Job step—incremental load preparation—which consists of the following sub-processes:

   1. Data Update Job truncates the staging table.

   2. Old value is set to New + 1.

   3. New value is set to maximum available SequenceNumber value in the database (same as initial run).

5. Data Update Job then loads the delta values (all values between Old and New) into the staging table.

   

6. Finally, these new values are added to the target table.

note

Data Update Job then repeats steps 4, 5, and 6 from day three onward.

SQL role changes

The new parallel job-running Data Update Job affects SQL database user permissions compared to Snow License Manager 8.

As with version 8, the Snow License Manager 9 installation process creates a user in the SQL database called LicenseManagerUser with the following roles:

• SQLAgentOperatorRole

• SQLAgentReaderRole

• SQLAgentUserRole

note

Requirement for a Snow License Manager 8 to 9 upgrade: If the roles above have been changed in a customer's Snow License Manager 8 installation, these must be reset back to the version 8 defaults before the version 9 upgrade can be successfully completed.

In addition to the three LicenseManagerUser roles above, the Snow License Manager 9 installation process includes new permissions with select access to the following database tables:

• msdb.dbo.sysjobhistory

• msdb.dbo.sysjobs

• msdb.dbo.sysjobsteps