Transferring XYZs

The following migration process is to be completed by a database admin (DBA).

The Move_LocationData_sp makes it easy to move XYZ data from one database to another while maintaining the integrity of the data. This functionality allows the user to configure the XYZs for new facilities or make changes to the configuration of existing facilities on a test environment separate from the production environment. This practice provides the security that any unintentional changes will not have a negative effect on the production standard calculations. Additionally, the XYZ data transfer ensures that ProTrack Warehouse does not process incorrectly any of the production data resulting from importing XYZ data while simultaneously importing WMS data.

XYZ Data Transfer – Overview

There are two separate stored procedures that may be run to transfer data from a Test Environment to a Production system. The first (Move_LocationData_sp) is used to move the actual XYZ component data. The second only moves the two caches. Depending on the maintenance scenario, it may only be necessary to move the cache.

The Move_LocationData_sp copies all XYZ component data from one database to another. The stored procedure moves data from the following tables:

1. facility
2. floor
3. aisle_path
4. segment
5. travel_path
6. location
7. level
8. node
9. aisle_path_node
10. travel_path_node

The Move_Cache_sp is used to move just the cache tables from one database to another:

1. NodeCache
2. TravelPathHistory

3. TravelPathHistoryDetail

   Info

   • The only XYZ-related data that is NOT moved is that which defines which nodes are interconnected. Since ConnectedNodes is NOT moved, the PopulateConnectedNodes_sp must be run after transferring XYZ data.

All modifications are done in one direction reading from a database that is designated as “Source” and updating the database designated “Destination”. Any errors while executing this request will result in a full rollback of the data.

XYZ Data Transfer – Instructions

1. Disable the scheduled task operations in both the Source and Destination environments.
   1. Log in to ProTrack Warehouse.
   2. Click System under the Configuration menu.

   3. Click on the Scheduled Task sub-tab.
      
      
      ^{Fig.1 System Configuration}

       

   4. Select “N” for ALL of the drop down boxes.

   5. Select all of the check boxes.
      
      
      ^{Fig.2 System Configuration - Disable System}

       

   6. Click  to save the changes.
2. Ensure that all processing in both environments is complete.
   1. Scheduled Request Records.
      1. Click on the Monitor tab.
      2. Click on the Scheduled Request tab.
      3. Click .
      4. Search for all records where the status is INP.
      5. If there are any records in INP wait for these records to leave INP status. All WMS processing is complete when there are no records in the Scheduled Request that are in INP status.
   2. Batch Process Records.
      1. XYZ data is imported based on a batch process that runs based on a configurable time interval.
      2. Check that there are no files in the import directories on the application server for the following import types:
         1. Facility
         2. Floor
         3. Aisles Path
         4. Segment
         5. Location
         6. Travel Path
   3. During the XYZ transfer process you will also want to ensure that nodes are not generated from the facility layout screen in either environment. This process cannot be disabled and will have to be independently monitored by the users.
3. Access SQL for the database that is going to be updated. This database will be referred to as the Destination database. The database that contains the updated XYZ information will be referred to as the Source database.
4. Ensure that the following stored procedures have been installed in the destination database:
   1. Move_Aisle_Path_Node_sp.sql
   2. Move_Aisle_Path_sp.sql
   3. Move_Cache_sp.sql
   4. Move_Facility_sp.sql
   5. Move_Floor_sp.sql
   6. Move_Level_sp.sql
   7. Move_Location_sp.sql
   8. Move_LocationData_sp.sql
   9. Move_Node_sp.sql
   10. Move_Segment_sp.sql
   11. Move_Travel_Path_Node_sp.sql
   12. Move_Travel_Path_sp.sql
   13. LinkedServerConnection_sp.sql
5. Backup both the Source and Destination databases.
6. It is assumed that when Move_LocationData_sp is being run, Move_Cache_sp will also be run afterward.

7. Temporarily disable constraints by running the following statement.

   EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

8. Delete all XYZ data in the destination database by opening a new query (connected to the destination database) and run the following statements, one-by-one from top to bottom.
   

   1. Delete from level
      Delete from location
      Delete from segment
      Delete from aisle_path_node
      Delete from travel_path_node
      Delete from node
      Delete from aisle_path
      Delete from travel_path
      Delete from TravelPathHistoryDetail
      Delete from TravelPathHistory
      Delete from NodeCache
      

   1. Open a new Query connected to the destination database.

   2. Run the following query:

      exec dbo.Move_LocationData_sp
      @SourceDB = 'Insert Source DB Name Here',
      @DestDB = 'Insert Destination DB Name Here',
      @SourceServer = 'Insert Source Server Name or IP address Here',
      @DestServer = 'Insert Destination Server Name or IP address Here',
      @LinkedUserName = 'protrack',
      @LinkedUserPwd = 'protrack',
      @Debug = 0
      

   3. Wait for the query to result as complete.
       

9. Next, open a new Query connected to the destination database and run the stored procedure that will connect the nodes of a floor.

   exec PopulateConnectedNodes_sp

10. Enable the constraints by running the following statement.

    EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

11. To move the travel path history, travel path details, and node cache:

    1. Delete all cache data in the destination database by opening a new query (pointed at the destination database) and run the following statements, one-by-one from top to bottom:

       Delete from TravelPathHistoryDetail
       Delete from TravelPathHistory
       Delete from NodeCache
       

    2. Open a new Query connected to the source database.

    3. Run the following query:

       DECLARE     @return_value int
       EXEC  @return_value = [dbo].[Move_Cache_sp]
                   @SourceDB = N'Insert Source DB Name Here',
                   @DestDB = N'Insert Destination DB Name Here',
                   @SourceServer = NULL,
                   @DestServer = N'Insert Destination Server Name or IP address Here',
                   @Action = N'INSERT'
       SELECT      'Return Value' = @return_value
       GO

12. Validate that all known differences between the two databases have been synchronized and that all of the effected tables have the same number of records.
    1. Run any additional comparisons as seen fit until confident that the data was transferred successfully
    2. If there are any discrepancies restore the databases from their backups and double check that steps 1 and 2 had been completed successfully.
    3. If problems still exist contact TZA.
13. Enable the scheduled task operations in both the Source and Destination environments.
    1. Log in to ProTrack Warehouse.
    2. Click the System tab under the Configuration menu.
    3. Click on the Scheduled Task sub-tab.
    4. Select “Y” for all of the drop down boxes.
    5. Select all of the check boxes.
    6. Click  to save the changes.