Moving Data to an On-Prem Database with Skyvia
Many modern businesses choose cloud warehouse services like Google BigQuery or Amazon Redshift to consolidate their data storage and analysis. These services offer the ability to store huge data volumes and process them quickly with massive parallel processing.
However, some companies still prefer on-premise databases, not wanting to share their data to more cloud services than necessary. Others already have on-premise legacy databases and don't want to move them to the cloud for analysis. Many popular cloud analysis tools, including Periscope Data, support both on-premise database servers and cloud data warehouses.
When companies use cloud applications (for example, Salesforce) they need to get that data from the cloud and into their databases for analysis. While there are many solutions offering easy data replication to cloud warehouses on the market, there are fewer tools that support on-premise databases, like SQL Server, as a replication target. One of these solutions is Skyvia.
In this post, we’ll demonstrate how to quickly load data from Salesforce to an on-premise SQL Server with Skyvia and then connect to Periscope Data for analysis.
Getting Started with Skyvia
Skyvia is a cloud data integration solution, which can easily replicate data from Salesforce and other cloud applications to SQL Server as well as other database servers and cloud warehouses. Skyvia does not require installing any software locally.
Replicating data from a cloud application to a database or cloud warehouse is easy in Skyvia, requires no coding or deep IT knowledge, and can be configured in just a few minutes. Additionally, after creating a copy of cloud data in a database, Skyvia can update that data automatically by periodically importing new data from Salesforce and applying those changes to the target database.
To replicate your data this way, the first step is to register on Skyvia. Creating an account is free, and the free pricing plan allows you to load up to 5000 records per month between cloud applications and databases.
SQL Server Requirements
To connect to an on-premise SQL Server, the server must be available from the internet. It must be available from Skyvia's IP (220.127.116.11) for loading data and from Periscope's IPs (18.104.22.168 and 22.214.171.124) for data analysis. You may need to allow these IP addresses in your firewall.
Besides, SQL Server must allow access via the TCP/IP protocol and SQL Server authentication. For more detailed information on how SQL Server should be configured, please see Periscope Data's documentation. For Periscope Data, it's enough to have a SELECT privilege in SQL Server and only this privilege is mentioned in the documentation. Skyvia's replication creates the necessary tables automatically and writes the necessary data to them. Thus, additional privileges are needed for creating tables and writing data.
Connections to Data Sources
After you create a Skyvia account, click “Create Now” under “Replication.” A replication package editor page will open. The first step is to create connections to the corresponding data sources.
To do this, select connection types under the “Source” and “Target” headers. For this exercise, select “Salesforce” as the source, and “SQL Server” for the target. The next step is to create the corresponding connections (by clicking the corresponding “New” buttons).
For Salesforce, Skyvia supports both username/password and OAuth connections, so it is not necessary to store Salesforce credentials on Skyvia. Simply enter a connection name, log in to Salesforce and allow Skyvia access to your data.
For SQL Server, specify the corresponding connection parameters. The description of the necessary connection parameters can be found in Skyvia documentation.
Selecting Objects and Running Replication
After connecting to Salesforce and your database, everything is simple. Skyvia displays a list of Salesforce objects and you just need to select checkboxes for the objects that you want to copy.
You can also perform complete or partial replication. For each object, you can also use the “edit task” option to exclude some fields or set up filters to copy only certain parts of the data.
That’s all you need to do to copy your data. Click “Save” and then run your replication.
By default, Skyvia automatically creates tables with the corresponding structure in your database. In just minutes (depending on the volume of your Salesforce data), you will have exact copies of your Salesforce objects in your database.
You can also schedule a replication to run automatically, keeping your database always synced with Salesforce. To configure a schedule, open the created replication package for editing and under “Schedule” (at bottom left of the package editor) click “Set schedule.”
Skyvia allows flexible schedule configuration. You can configure your replication to run monthly, weekly, daily, on specific days, etc. In more robust plans, you can run replication every hour or even every few minutes.
After you configure your replication, you can easily keep your database up-to-date with Skyvia, continually importing fresh data for data analysis.
Other Integration Scenarios
Skyvia's replication is a perfect solution if you simply want to copy cloud data to a database and keep that data current. But Skyvia is not limited to replication, it supports other operations such as importing, exporting and synchronization.
For example, if you want to load data from other databases or if you want to load data to already existing tables, you will need Skyvia's import function.
Import has a more complex configuration process than replication, but it is much more flexible. It allows importing data from CSV files or from cloud applications and databases directly to other cloud applications and databases. Unlike replication, it doesn’t create target tables automatically. Instead you configure mapping between source and target tables and columns.
Powerful mapping settings allow you to load data between the data sources with the different data structure and formats while preserving relations between data. You can provide constant values, use powerful expressions, lookups, etc.
Connecting Periscope Data to Database
After the Salesforce data is loaded to SQL Server, you can connect Periscope Data to the database to analyze it. To do this, navigate to Settings > Database, then click “Add Database” and select SQL Server as the Database Type. Once you’ve provided the connection parameters for your SQL Server database, click “Add” and you’re ready to go.
After this you will be able to create charts for the newly connected database in Periscope Data and get insights on your data.