Moving ConnectWise to the Cloud Without Losing SQL Access

Overview

If you run ConnectWise PSA on-premise and you’re planning a move to the cloud, one question comes up almost every time: what happens to our SQL access?

For years, direct database access has been the quiet engine behind a lot of MSP operations: custom views, SQL Agent jobs, Power BI and SSRS reports, one-off data fixes. In the cloud, that changes. In this post I’ll go over what you lose, what you keep, and how to get most of the functionality back so the migration doesn’t become a reporting problem.

Moving to the cloud is a reasonable decision for a lot of teams, so this is mostly a checklist to help you plan for the parts that are easy to miss. There is one exception, for companies that grow by acquisition, which I cover near the end.

What Changes When You Leave On-Premise

When ConnectWise hosts your database, you no longer have direct SQL access to it. In practice that means a few things you may rely on today stop working the way they do now:

• Custom views. You can’t add your own views or tables to the ConnectWise database, so any report or integration built on a view of yours needs a new home.
• SQL jobs. Scheduled SQL Agent jobs, such as alerts, exports, nightly clean-ups and mass updates, don’t have a server to run on anymore.
• Data fixes and merges. Direct updates to the database, and the kind of comprehensive data merge or conversion that is done at the database level, are no longer something you or a consultant can run against your live system.
• Reports that connect straight to SQL. Power BI, SSRS and Excel connections need a different data path.

ConnectWise does offer a way to get at your data from the cloud: a read-only data access add-on that gives you a live copy of your database. It’s priced separately, and ConnectWise only sets it up when you ask for it. If you use Power BI, you’ll usually need an on-premise data gateway to reach it, and we’ve written about setting that connection up. It’s worth knowing what it is and what it isn’t. You can query the data and use the existing views, but you can’t see their definitions. It doesn’t give you back custom views, jobs or writes, and if you rely on a gateway, that’s a machine you have to keep running, patched and reachable.

What You Can Get Back

Most of what people actually use SQL access for can be rebuilt on the ConnectWise API plus a database of your own. Here’s how the pieces map over.

• Custom views and joins: the data your views pulled together can be loaded from the API into a hosted database, with the views rebuilt there as datasets. Reports run against those instead of against the ConnectWise database.
• Scheduled SQL jobs: alerts, exports and routine updates become scheduled automations that read and write through the API.
• Power BI and SSRS reports: they can stay where they are if the cloud data access add-on and gateway work for you, or they can be rebuilt as hosted dashboards that don’t depend on a gateway at all.
• Write-backs: updating fields in ConnectWise from a report or an automation is done through the API, with a review step before anything changes if you want one.

It isn’t identical to owning the database. The API has its own limits and your queries run against a copy of the data, not the live tables. But for reporting, alerts and routine updates, it covers the large majority of what most MSPs do with SQL day to day.

How NexNow Reports and Automations Fit

NexNow Reports is how we do this for clients. Your ConnectWise data is pulled through the API into a database hosted and monitored by us, and reports and dashboards are built on top of it. A few things that tend to matter to teams coming from SQL:

• We can rebuild the reports you already have. If you have a Power BI or SSRS report your team depends on, we can rebuild it on the hosted data, so you don’t need the gateway.
• Custom datasets instead of custom views. The joins and calculations you built into views move into datasets we build and maintain for you.
• Automations replace SQL jobs. Scheduled checks, alerts and updates run on a schedule, and they tell us if they fail.
• Write-back fields. A report can include fields that update ConnectWise or your other tools with open APIs.

If you want to write your own queries against the hosted data, direct query access can be set up as an add-on. That isn’t part of the base setup, and we’d talk through what you need first.

Plan This Before the Cutover

The most useful thing you can do before moving is take an inventory while you still have access. It’s much easier to document a view you can still open than to reverse-engineer it from a report later.

1. List your custom views, tables and stored procedures and what uses each one.
2. List your scheduled jobs: what they do, how often, and who notices if they stop.
3. List every report and integration connected to the database: Power BI, SSRS, Excel, and anything another vendor set up.
4. Note which of these write data back. Those take the most care to move.
5. Decide what you can retire. There’s usually a long tail of reports nobody has opened in a year.

In later posts, we’ll cover some scripts you can use to bring some of the above to light. 3rd party ConnectWise consultants are also a great resource for this and we can help connect you with one that fits your business. Even a rough list helps. It turns “we’re worried we’ll lose SQL” into a short set of specific items, and most of them have a straightforward replacement.

A Word of Caution for Frequent Acquisitions

There’s one thing we haven’t been able to replace, and if it applies to you, it should weigh heavily in the decision: comprehensive data merges.

When an MSP acquires another MSP and needs to bring that company’s ConnectWise data into its own, the work happens at the database level. Companies, contacts, agreements, tickets, projects, time, configurations and invoices all have to be mapped and brought over together, with their relationships and history intact. That takes direct SQL write access to the database, and it’s the one job where the API is not a substitute. The API can create and update records, but it can’t keep original dates, numbers and links between records the way a database merge does, and it’s far slower at that scale.

So if you expect to make more acquisitions, we strongly recommend staying on-premise. A cloud-hosted database severely limits what a merge can do, and it can turn a project that would otherwise be routine into a compromise on what history you bring across. For everything else in this post there’s a good replacement. For merges there isn’t, yet.

If you do have a merge or conversion on the horizon, do it before any move to the cloud. Our ConnectWise data conversion and database merger service covers it, and data cleansing and mass updates covers the smaller clean-ups that can still be done through the API afterward.

Wrapping Up

Losing direct SQL access is a real change, but it’s rarely the blocker it first sounds like. Make an inventory of what depends on your database, decide what to keep, and rebuild the rest on the API. If you’d like help with that, or want to see what hosted reports look like, book a reports demo and tell us what you run on SQL today.

You can also connect with us on the ConnectWise subreddit or the ConnectWise University Forums.