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 your Connection Dashboard, where you can see your current server status:

From here, you may change, edit or create your servers. Let’s explore these in a little more details…

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 two server types - SQL Server, and it’s cloud-hosted doppelganger, SQL Azure. We’re considering other data stores too, so get in touch if you need something else.

Automation

If you’re using SQL Azure, then you can take advantage of Azure’s automation options and instruct us to create your data warehouses automatically for you. This is a fantastic solution for Consultants that want to host all their clients on the same server.

Once configured, SyncHub will run multiple times a day and automatically queue up new databases ahead-of-time, making your client on-boarding quick and seamless.

Registering a warehouse

Most of our regular BYOD customers only need one warehouse to hold all their SyncHub connections. However, we have built our system with flexibility in mind, and technically you can register as many as you like.

The real strength of this however, is for our Consultants. In the screenshot above, you can see that warehouses are adopted by new clients automatically as they come onboard. If you are using our automation feature (above), then there is literally nothing else you have to do from here - we will create your new warehouses for you, and automatically assign them to new clients.

However, for single-use warehouses, or for specific assignments, we offer even more granular control by allowing you to manually register a database:

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):

If you are using this warehouse solely for your own purposes, you don’t even need to submit the reporting credentials. However, if you are scaffolding databases for use by your own clients, then you’ll need to tell us what they are.

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

As discussed above, if you are a Consultant, or are otherwise on-boarding a variety of clients to use your warehouses, then we will automatically assign warehouses to clients as they sign up.

However in many cases, you may wish to reserve a specific database for a specific client ahead-of-time. For example, perhaps you have a particularly large client coming on board and you want to give them a warehouse with extra capacity and power. It would be frustrating to create this database and then have another client register and “snatch” the new database before its intended recipient signed up.

For these cases, we also offer an ability to Reserve a data store. It’s pretty self-explanatory - just hit the Reserve button in the screenshot above, then follow the instructions. The next time your new client creates a connection, they will be automatically assigned this warehouse instead of us popping one off the queue dynamically.

Pretty cool, huh?

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.