Bring your own database

One of the best things about SyncHub is that we provide customers with their own managed data warehouse. This makes signing up and testing our service a breeze - not only is it quicker, but our users don’t need to be server/database admins in order to test our service.

For most of our users, this managed database is all they need. But others - particularly enterprise - want more, and this is where our BYOD service comes in.

Why would you BYOD?

There are a few compelling reasons why BYOD may suit your organization…

Governance and compliance

Many companies (or even countries) mandate that their data is stored on their own servers (or country). Even if our managed warehouse suits your requirements on a technical level, there may be other forces compelling you not to use it.

However, with BYOD, these reservations are moot. Your data is now hosted in a database and country of your own choice.

Alignment with other data

For larger organizations, it is likely that your SyncHub data is only a part of your warehoused data. For reporting performance and maintenance reasons, it is logical to try to keep all this data in the same physical set of servers.

Performance

Our managed warehouses serve tens of millions of records to our users with ease. However, by necessity, it is still a one-size-fits-all solution. For example, we can index only your primary- & foreign-key columns - without knowing your reporting requirements, we wouldn’t presume to index anything else.

However, with BYOD, you suddenly have all these options at your fingertips. For example:

• add stored procedures to automatically move data to other tables/warehouses

• create views to aggregate data across different schemas

• create indexes to improve the performance of a specific report

• create triggers to fire off emails when certain thresholds are met

Reduced latency

Finally, moving your data warehouse physically closer to your BI tools will improve performance simply by reducing latency.

Getting started with BYOD

SyncHub makes it really easy to manage multiple databases and assign them to your connections. The process starts with our New Connection Wizard, which you access from your main Connections page. Within this wizard, we let you choose to provide your own database details:

Selecting the second option walks you through the registration wizard, which I’ll outline below in more detail…

Registering a server

We tend to think of data warehouses in terms of the warehouse itself - but actually it is a server that holds one or more warehouses. So, before you get your warehouses registered, you need to tell us about your server.

Server types

At time of writing, we currently support the following servers:

• SQL Azure

• SQL Server

• Redshift

• Postgres

• BigQuery

• Snowflake

Selecting your server type is the first step, as the wizard deviates somewhat depending on your selection. In this post, I’ll demonstrate with a SQL Server database.

Side note: automation

You’ll also note an automation option in the screenshot above. If you are using our White Labelled solution, you can take advantage of SQL Azure’s automation options. Using this, SyncHub can create databases within your server automatically, letting you onboard new clients without having to manually scaffold one each time.

If you are using our automation feature, then you can stop reading this article now, as the warehouse registration is taken care of for you. If not, read on…

Registering a warehouse

After you’ve registered your server, you need to register a data warehouse within that server. For most customers, you need only one database, however there are scenarios such as GDPR which might necessitate a client creating multiple warehouses.

Let’s talk through the various options quickly…

Setting your credentials

For security reasons, there are actually two sets of credentials that we require…

Admin credentials

Your admin credentials are used when SyncHub is creating the data structure of your warehouse. For example, if we are onboarding a new Xero connector, we SyncHub might need to:

1. create a new schema (e.g. xero_45)

2. create a new Invoice table and a new Customer table

For these reasons, your admin user must have full DDL permissions. To ensure that you have created a user with the appropriate permissions, we actually run a quick test on your warehouse before letting you save these credentials.

Reporting credentials

The reporting credentials are not actually used by SyncHub. Instead, these are provided to the consumer of the warehouse, via our connection Dashboard (which you are already familiar with):

Aside: while SyncHub doesn’t use the reporting credentials itself, it does adjust their permissions when we onboard new connections. Specifically it provides full read permissions on the new schema that is created for your connection(s).

Reserving a connection

Finally, once the database is ready, simply hit the Reserve button to lock the details in place and queue it up for use with your next connection.

To conclude

Our BYOD option provides a great deal more flexibility to our customers by giving them full control over where and how their data is stored. This in turn mitigates compliance and governance obstacles, as well as opening up the full power of database tools to our clients.