Upgrading a PSQL installation to AGRIS 17.3.0 or higher - Migrating PSQL data to MSSQL

TABLE OF CONTENTS:

Background Notes and Assumptions

1. These instructions are intended for configuration by people familiar and experienced with MSSQL environments.  If you are not comfortable or familiar with MSSQL or the following steps, please consult your hardware / I.T. provider for assistance in the MSSQL configuration. 
   
   
2. Assume an existing PSQL implementation of AGRIS (clients and servers) is running successfully AND: 
3. • you are NOT running oneWeigh's live on your AGRIS server
   • your SJI work files have been updated to use Multi work file configuration instead of the classic approach.  (known issue of SJI work files disappear upon conversion if using single work file approach)
     • See AGS-3437 for more details. 

Validate and Convert Datasets to AGRIS 17.1.0 or higher

Perform the following steps before upgrading to AGRIS 17.1.0 or higher.

• Use the Validate Database feature within AGRIS under Utilities > Diagnostics on all datasets (using the current installation) that will be migrated into SQL Server
  • If key length errors are displayed in the validation log, they can be ignored since the SQL migrations do not use indexes when migrating data from psql to mssql.  Other critical errors may not be ignored and may need further investigation before continuing.
    
    
• Convert all datasets to the current version of AGRIS - 17.1.0 or higher (using the current installation) that will be migrated into SQL Server.    
  • This step will help ensure a successful migration.  Example: AGRIS 9.6.0 SP14 is installed.  There are 10 datasets.  2 of the 10 are on 9.6.0 SP5.  
  • Please ensure:
    • All datasets convert successfully to the same version of AGRIS that you intend to install to MSSQL with. (17.1.0 or higher)
    • If conversion issues are encountered, prior to migration, contact the support team for assistance correcting the conversion issues before a migration is attempted. 
    • Backups are complete: please make sure all datasets have been backed up successfully

????  What if our company is on an older version of PSQL that is no longer supported?   Example PSQL10.    

A= There are low cost alternatives to moving your data with a supported version of PSQL.  Please speak with your Account Manager to learn more. 

Please do not continue until the steps above have been completed!

Install AGRIS 17.1.0 or higher on MSSQL Server and Migrate PSQL Data

1. Install MSSQL 2016 SP1 Standard or MSSQL 2014 Enterprise (Standard if you do not require auditing)  (you can use SQL express for a very small amount of data otherwise install Enterprise)

   • SQL Server 2014 Express  or SQL Server 2014 Enterprise
     

   • When installing MSSQL choose SQL Security Mode (this is not required just easiest)

   • Configure to use TCP/IP and Named Pipes = Enabled in SQL Configuration Manager

   • Ensure that SQL Server Management Studio can be launched and the user can login using the sa account or some other administrative account

     
     

2. Install AGRIS 17.1.0 or higher, then apply latest Service Packs and/or Software updates

   
   

3. Configure the AGRIS.INI

   • If you are using a separate standalone SQL Server then the Component Server name should be a common AGRIS instance or it should be the machinename of the machine you are installing.  Example:AGRISPROD

   • Add     Database Type=SQL      to the AGRIS.ini   (syntax and spacing are very important)

     
     

4. Configure AGRIS\bin\dbconnections.config

   • copy DBConnections.config.template to dbconnections.config

   • modify dbconnections.config to use the appropriate login credentials for SQL server user and password.  There are 3 of these to set in the dbconnections.config.

     
     

5. Confirm the "Dataset neutral" files and datasets are successfully copied / available for the conversion / migration. 

• • • Dataset neutral files are located in the (Datasets) folder directly above the individual dataset folder and typically contain files that end in *.AGR

6. Launch AGRIS

1. • When AGRIS is launched there will be a short delay while the PSQL global data is migrated into SQL Server

   • A grid with a list of datasets should now appear and you should notice a column on the right side of the grid labeled "Db Type".  Initially, this column will be blank indicating that each specific dataset is still in PSQL

   • If the AGRIS and Accelerator databases have been preconfigured, AGRIS will use the existing databases.  This allows the customer to configure the database(s) based on SQL Server 2014 Enterprise installation best practices while conforming to Corporate Policies and Guidelines.

   • Optimizations to consider for the Accelerator Database

     • Set Recovery Model to Bulk-Simple

     • Set the DataFile Paths to c:\databases\agris (not required just suggested)

   • Optimizations to consider for the AGRIS Database
     

     • Set Recovery Model to Full (Production) or Bulk-Simple (Testing)

       
       

 7. Convert existing PSQL datasets

1. • If there are datasets that are not of the currently installed version, they will need to be converted up to the current version.

   • Confirm that all PSQL datasets have been converted up to the current version

     
     

8. Migrate PSQL datasets to SQL Server

1. 1. Login to AGRIS and select a dataset to migrate

   2. Go to Maintenance→Dataset Maintenance

   3. Confirm that only the currently selected dataset is highlighted/selected

   4. Click on Migrate to start the migration.  This could take a long time depending on the amount of data to be migrated.  The time estimates on the migration form are just estimates and may vary.

   5. When the migration completes, you will see a message indicating the migration completed.

   6. If you want to migrate another dataset, start back at step a: selecting the dataset you want to migrate and repeat b-e.

   7. When a dataset is migrated there is a Migrated folder created under the dataset.  This migrated folder holds all the original PSQL files prior to the migration.