Data synchronization overview

Introduction

SchoolMessenger Communicate synchronizes data with thousands of customers daily, and we are familiar with most of the common data sources found in the market. Your project manager may recommend a specific option for data synchronization based on prior customer experience, but regardless of the specific synchronization strategy employed, this guide will help you understand the basic process flow by which data is synchronized with your Communicate account.

This document is intended to familiarize you with the steps required for typical data synchronization and to give you a general overview of the additional options available at each. There are more situations than those mentioned here, and if you need guidance, reach out to your project manager or support for assistance.

The data synchronization process can be divided into three fundamental steps necessary for synchronizing data:

1. Extract the necessary data elements from your database.

2. Format the data for the system as needed.

3. Encrypt and transfer the data into your account.

Our support and project management has a wide range of experience integrating with various Student Information System (SIS) packages. For this reason, we strongly recommend that your database administrator work directly with Communicate support to determine the best strategy for data synchronization prior to any actual configuration taking place.

Step 1: data extraction

Most likely, the data you want to synchronize with your Communicate account resides in a database, although some contact data could simply be in the form of a spreadsheet that you maintain. This section is primarily concerned with extracting data from your database, but if you do have data you maintain in a spreadsheet, uploading it to the system will be very similar to uploading a file from a database export, described in Option 1: Export Data to a File.

Note: For a list of fields to include in the extract, see Appendix A: Data Fields to Export.

Common methods for providing data

This section contains some of the most common methods used to make data available to Communicate.

Option 1: Export data to a file

Using this method, you will create a file containing the data that will then be securely uploaded to the system. Most common database applications include a feature for exporting data. Often, these exports can also be scheduled, allowing data exports and synchronization to be automated. This can make the job of data synchronization much easier to maintain, potentially.

If you choose this option, please carefully review Step 2: Format the Data for a description of the ideal exported data file format.

Please note that in the data transfer step, the location of the CSV file will need to be accessible by the

Communicate Upload Utility. Also, the name of the extracted file needs to be the same name each day in order for the Upload Utility to locate the correct file. Most customers simply overwrite the old exports each day with a new export file.

Note: The Upload Utility can be installed on the same computer where the exported files reside or can be on a different server that has access to a shared network directory containing the export files. In certain situations, the data could be sent to a Secure FTP server on the Communicate side, where the Upload Utility will also be located. See the Option 3: Secure FTP under the Step 3: Transfer the Data section for more details.

Scheduled or automated data file exports require synchronization between your export schedule, the Upload Utility’s schedule, and your Repeating Broadcast start times, such as Attendance notifications. You should contact support before changing the data export schedule to determine if corresponding changes need to be applied to the Communicate import configurations, as is often the case.

Option 2: Direct database connectivity (ODBC)

If your database does not come with built-in export features, it may be possible to export the data using a query if the database is ODBC compliant. This technique requires knowledge of SQL as well as knowledge of the underlying schema of your database.

One technique to simplify using this method is to create a data view from your query. We have tools for safely reading the data view and converting it into a properly formatted data file that will be securely transferred into your account.

For this solution to be successful, it is important to verify that an ODBC driver or other connection option is available for the host operating system prior to implementing this type of solution. Additionally, your Database Administrator must provide the connection information and any queries necessary to extract the required data. Furthermore, the Database Administrator must also be available for any testing and troubleshooting necessary to ensure the data synchronization is fully implemented and working properly.

Step 2: format the data

The final format of the data file to be imported into your Communicate account must be an ASCII encoded Comma Separated Values (CSV) file with a carriage return (CR) and line feed (LF) at the end of each record. For best results, each field value should be encapsulated in double quotes to avoid any problems due to stray commas in the data field values themselves. There is no required field order, but the column order of the final, formatted data file should typically stay the same each time to avoid issues with the mapping of the imported data to Communicate fields. If new columns need to be added or the order rearranged, make sure to contact support so that the appropriate corresponding Import Field Mapping changes can be made on the Communicate side as well.

If the format of your original source file does not exactly match this description, don’t worry. We have tools for handling almost any data file format imaginable.

There are also certain types of data that may require specific field formatting in order to be of use. Those types of specialized requirements are handled by Communicate using specially designed formatting tools.

Step 3: transfer the data

Once the data is in the proper format, the file must be securely transferred into your Communicate account. To keep the system up to date so that it may deliver notifications to the proper recipients and destinations, Communicate must regularly import contact data. Typically, these uploads are scheduled to occur at the same time each day and can potentially be automated for ease of maintenance.

Common methods for transferring data

There are multiple methods by which this transfer can occur, but by far the most common method that we strongly encourage you to use is our Communicate Upload Utility.

Option 1: The upload utility

The Communicate Upload Utility is a proprietary Java application designed exclusively to facilitate the secure transfer of data between the customer’s site and their hosted Communicate account. The application encrypts the data using 256-bit encryption and communicates using standard SSL/Https on port 443.

This software may be installed on the computer where the extracted data files are stored or, if the extracted data files reside in a shared network directory, it can be installed on a different machine that has access to that directory, or on a server. Once the application is able to access the export data file, it can be configured to perform an upload automatically each day.

Note: The names of the extracted data files need to be the same each day for the Upload Utility to locate them. Most customers simply overwrite the old exports each day with a new export file.

Before the Upload Utility performs an upload, it compresses the data file to reduce the size of the transmission, resulting in dramatically faster uploads, particularly with large data files. If there are any network interruptions during transmission, it will retry in an effort to ensure the data is transferred. After the upload is complete, the Upload Utility verifies that the data was transferred successfully. Finally, the application creates detailed log files of each upload attempt, which can be useful in troubleshooting.

The Communicate support team will work with you to configure and test the Upload Utility once you have created your source data files.

Scheduled or automated data file exports require synchronization between your export schedule, the Communicate Upload Utility’s schedule, and your Repeating Broadcast start times, such as Attendance notifications. You should contact Support before changing the data export schedule to determine if corresponding changes will be required to the Communicate import configurations.

Option 2: Manual user upload

For customers who cannot automate the creation of their source data files or wish to upload data from a spreadsheet that they manage themselves instead, using the application’s web interface to simply browse for their CSV data files and upload them manually is another popular means of secure data transfer. Individual fields from the source file can be mapped to corresponding Communicate fields in an account. The settings are retained so that each subsequent upload of the same file simply requires the user to browse for the new file and upload it into their account.

Note: Using the same column order in the file each time is best practice to prevent mapping issues.

Option 3: Secure FTP

A small number of customers require that all data transfers to third-party vendors be handled via FTP. If this is a required policy, FTP can be used to transfer data to Communicate; however, there are certain security requirements that must be met.

Only Secure FTP is supported. No unencrypted data transferred via plain text FTP will be accepted.

Authentication for Secure FTP transmission must be achieved using Key Pair Authentication. Username and Password authentication is not supported, as it is a less secure method of user account authentication.

FTP transfers do not actually import data directly into your account. Once the data is transferred to Communicate, an additional processing step is required to import it into your account. There are no particular benefits to using FTP file transfers. FTP should only be used where policy dictates its use.

Appendix A: data fields to export

This appendix contains the required and optional data fields which should be included in an export or data view for student, attendance, and staff data. Each table represents a separate data extraction. Additional or alternative fields can also be imported; however, you should confirm with Communicate before doing so. In addition to basic contact data, certain Communicate features may also require that other specific types of data be available.

Student contact/demographic data fields

Student attendance data fields

Staff contact/demographic data fields (optional)