SharePoint to Google AlloyDB

Last updated 26 October 2026

One way: SharePoint stays the system of record, and SPSync keeps a read-only copy of your lists current in Google AlloyDB.

Status: Shipped · product: SPSync for Relational Databases

Moving SharePoint list data into AlloyDB is usually about the questions SharePoint cannot answer at all: several years of inspection records grouped by site, condition and hazard, refreshed for Looker every few minutes. AlloyDB is Google’s PostgreSQL-compatible engine with a columnar cache for analytical reads. To SPSync it is a Postgres server: the same connection string, the same tables, the same SQL afterwards. What changes is the route to the instance and what you can ask of the data once it is there.

How do I connect SPSync to AlloyDB?

Most AlloyDB clusters have only a private IP. That leaves three ways in:

• Run SPSync inside the network. A VM or container in the same VPC, or a network joined to it through private services access or Private Service Connect, connects to the instance IP on port 5432. Add sslmode=require to the connection string.
• Use the AlloyDB Auth Proxy. Run the proxy beside SPSync and point the connection string at 127.0.0.1:5432. The proxy handles TLS and authorises itself with IAM, so the string carries no sslmode setting.
• Give the instance a public IP. Google recommends pairing it with the Auth Proxy; a direct connection needs the sync host’s address in the authorised networks.

If an administrator has turned on connector enforcement for the cluster, only the Auth Proxy and Google’s language connectors may connect, and the proxy route is the one left. Either way, SPSync authenticates with a database user and password held in an environment variable, never in the configuration file. IAM database authentication is not supported. Give that user rights to create tables in the schema SPSync writes to.

SPSync writes to AlloyDB through the same PostgreSQL destination it uses for a server you run yourself. Its automated tests run against stock PostgreSQL; the AlloyDB networking details here come from Google’s documentation, so test the route from the sync host with psql before the first run.

A role for the sync

AlloyDB’s postgres user is the obvious login and the wrong one to leave in a connection string on another machine. Create a role that can do the sync’s work and no more. The sync writes to the schema named by dataset_name and keeps a second schema, with _staging on the end, for the rows each run merges in, so it needs both:

create role ops_sync login password 'stored-in-secret-manager';
create schema ops authorization ops_sync;
create schema ops_staging authorization ops_sync;

Store the password in Secret Manager if that is your habit, but the sync reads it from an environment variable on its host, so something on that host has to put it there. A service start script that fetches the secret and exports the variable is the usual answer.

The tables you get

Say an asset register on a site replicated with the prefix ops has a Site lookup, a Condition choice column, an Inspector person column, a Last inspected date and a multi-choice Hazards column. SPSync creates:

A column added in SharePoint appears on the next run, a column removed in SharePoint stays in the table, and a column whose type changes gets a new _v2 column beside the original. Rows deleted in SharePoint stay with sp_deleted_at set, so a report on a past quarter still counts an asset that has since been scrapped.

A worked query: hazards by site and condition

This is the kind of read the columnar engine is for: a scan of every asset, grouped three ways, run every time someone opens the dashboard.

select s.title as site,
       a.condition,
       h.value as hazard,
       count(distinct a.sp_item_id) as assets,
       max(a.last_inspected) as latest_inspection
from ops_assets a
join ops_sites s on s.sp_item_id = a.site_id
left join ops_assets__hazards h on h.sp_item_id = a.sp_item_id
where a.sp_deleted_at is null
group by s.title, a.condition, h.value
order by assets desc;

With the columnar engine on, auto-columnarization watches queries like this and loads the columns they scan into the column store. Nothing in SPSync changes for it: the sync keeps writing rows to the primary, and AlloyDB keeps the column store fresh. A Looker explore built on the same join gives business users the drill-down without SQL.

Where AlloyDB pays off

The columnar engine helps when the replicated tables carry analytical load. A Looker model over several years of inspection records, or a KPI dashboard that aggregates every list on a site, runs against the same tables a Google Cloud app reads for single records. An application read replica on AlloyDB answers those lookups from SQL, with no Graph calls in the request path. Joins across lists work on the key columns, and several sites can share one database with a table prefix per site.

High availability, regions and test clusters

An AlloyDB primary instance with high availability keeps a standby node in another zone and fails over to it behind the same IP address, so the sync’s connection string does not change. A run that is writing at the moment of failover fails and the next run catches up. Cross-region replication adds a secondary cluster in another region, which is read-only until you promote it; the sync writes in the primary region, and a Looker instance near the secondary can read there.

To try the whole setup before paying for a production cluster, Google offers free trial clusters. Point a test replication at one with a single list, check the tables and the worked query above, then delete it. The settings carry over unchanged to the real cluster apart from the connection string.

Read pools, agents and MCP

A read pool instance gives the cluster read-only nodes behind one IP address. That is the natural place for Looker, a notebook, or an AI agent. Run a Postgres MCP server against the read pool with a role that holds SELECT on the replicated schema and nothing else. SPSync also has a read-only MCP server of its own, spsync mcp serve, and either way the agent needs no Google Cloud or SharePoint permission beyond that database login. spsync mcp serve reads the list definitions from SharePoint once when it starts, with SPSync’s own sign-in, then answers from the replica only.

create role asset_reader login password 'set-a-strong-one';
grant usage on schema ops to asset_reader;
grant select on all tables in schema ops to asset_reader;

SharePoint item permissions are not copied into AlloyDB. If inspectors in one region should not see another region’s assets, a row-level security policy on ops_assets does that job, since SharePoint’s own permissions stay in SharePoint.

Embeddings for semantic search

AlloyDB includes pgvector, so the asset register can be searched by meaning as well as by column. Enable it in the database with create extension vector and use the Postgres with pgvector destination with the same connection string. During each sync it embeds the text columns you list, with OpenAI, Azure OpenAI or a local model, writes one vector per row and keeps an HNSW index on it, re-embedding only rows whose text changed. AlloyDB’s own ScaNN index is an option you can add yourself afterwards; SPSync builds and maintains the HNSW one.

AlloyDB limits and costs to plan for

• Identifiers. AlloyDB follows Postgres and caps names at 63 bytes. SPSync shortens and disambiguates long SharePoint names to fit.
• Billing. Google bills the primary instance, any read pool nodes and the storage the cluster uses, separately from SPSync. Storage grows with the data, so soft-deleted rows you never purge cost a little each month.
• Maintenance. A maintenance window can restart the primary. A run in progress at that moment fails, and the next scheduled run picks up the changes.
• Pooling. AlloyDB’s managed connection pooling is aimed at apps that open many short connections. The sync connects once per run and does not need it.

Watching the sync from the AlloyDB side

AlloyDB’s query insights groups statements by the user that ran them, so the sync’s role shows up on its own line: a burst of inserts and merges every few minutes, and a longer one during the first load. If a dashboard slows at the same moments, move it to the read pool. For a quick look from SQL:

select usename, state, query_start, left(query, 60) as statement
from pg_stat_activity
where usename = 'ops_sync';

An empty result between runs is normal. The sync holds a connection only while a run is in progress, which is also why it needs no managed pooling.

When the first connection fails

• A timeout means no network path: the sync host is outside the VPC, private services access is not set up for its network, or a firewall rule blocks port 5432.
• The Auth Proxy refuses with a permission error when its service account lacks the AlloyDB Client role, or the AlloyDB API is not enabled in the project.
• password authentication failed usually means the variable on the sync host holds an old password, or the role was created on a different cluster.
• permission denied for database means the role cannot create a schema. Create the two schemas above as an administrator and make the sync’s role their owner.

AlloyDB or Cloud SQL for this?

Both run the same tables, and SPSync connects to both the same way, so the choice is about the reads. Cloud SQL for PostgreSQL costs less and suits an app or a report reading a handful of lists. AlloyDB earns its higher price when the same tables carry scans and aggregations across years of rows, where the columnar engine and read pools make a difference you can measure. Moving later is simple: point the replication at the new database and run a full load, since SharePoint holds the source of truth and the sync rebuilds every table from it.

Running it

1. Sign in with delegated access for a first look, or ask an admin to grant Sites.Selected on the sites you need so the sync can run unattended.
2. Put the connection string, direct or through the proxy, in an environment variable and name that variable in the destination settings.
3. Choose the lists or libraries, or a whole site so new lists join automatically.
4. The first run creates the schema and loads every item. Later runs read only the changes, and a reconcile, weekly by default, checks the table against SharePoint.
5. Point Looker, Power BI or a SQL client at the schema, or at a read pool.

Google bills the cluster separately from SPSync. Check the machine that runs the sync can reach the instance before the first run; on a private IP that is the most common reason a first connection fails.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name alloydb --prefix alloydb --destination-kind postgres --connection-string-env SPSYNC_DB_CONNECTION --dataset-name alloydb --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: alloydb
prefix: alloydb
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: postgres
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: alloydb
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist