SQL Server

Reads: Your own tables only through the metric queries you write, and from those only the period, value, entity and sample_size columns, one row per month. Never a view's text: where the login can read one, only a digest computed on your server.

Keeps: Totals only, such as a month's count or spend. No record is kept. For each metric query you write, every version of its text and the rows each version returned: a month, a number, a record count, and a breakdown's value, which is whatever the query selects as its entity. These stay after the connection is deleted, because the figures computed from them point to them.

What Roiva keeps from each platform, field by field →

You connect SQL Server from inside Roiva, so you need a Roiva workspace first. Sign in to connect it now. New to Roiva? Start free with your work email, take the free assessment and create your workspace at the end, or request an invite.

1. In SQL Server: Run the setup script below in the database your metric views will live in, as its owner. It makes a user for Roiva, a roiva_metrics schema, and lets the user read that schema's views and nothing else. On SQL Server rather than Azure SQL, use the commented lines to make a login first
2. In SQL Server: Let Roiva's address, 3.231.16.92, through the server's firewall (on Azure SQL, Networking › Firewall rules) on the server's port: 1433, unless it listens on another you'll enter below. Roiva connects from that one IPv4 address only, encrypts every connection, and checks the server's certificate
3. Back in Roiva: Enter the Host (yourserver.database.windows.net), the Port if it isn't 1433, the Database, and the Username, paste the password into API Key / Secret, and click Create Connection
4. Back in Roiva: The connection page checks each step as that login and shows the SQL for anything still missing. Once it's all ticked, link this connection to an initiative on the Initiatives tab
5. In SQL Server: Create a view for each metric in roiva_metrics. Then in Roiva, set Metrics Schema to roiva_metrics in this connection's settings, and add a query for each metric on its Metric Queries tab

-- Run in the database your metric views will live in, as its owner
-- (on Azure SQL, the server admin). Choose a long, random password.

-- Azure SQL Database: a user of this database with its own password.
CREATE USER roiva_reader WITH PASSWORD = '<a long random password>';

-- SQL Server instead: a login on the server, then its user here.
-- USE master;
-- CREATE LOGIN roiva_reader WITH PASSWORD = '<a long random password>', CHECK_POLICY = ON;
-- USE <your database>;
-- CREATE USER roiva_reader FOR LOGIN roiva_reader;
GO

-- Where your metric views live, owned by dbo like the tables they read,
-- so a view reads them with dbo's rights and Roiva needs no grant on
-- them. Roiva may read this schema's views and nothing else.
CREATE SCHEMA roiva_metrics AUTHORIZATION dbo;
GO
GRANT SELECT ON SCHEMA::roiva_metrics TO roiva_reader;

Email hello@roiva.ai with what you're seeing, and you'll hear back within one business day.