Microsoft SQLServer

This connector materializes Flow collections into tables in a Microsoft SQLServer database.

It is available for use in the Flow web application. For local development or open-source workflows, ghcr.io/estuary/materialize-sqlserver:dev provides the latest version of the connector as a Docker image. You can also follow the link in your browser to see past image versions.

Prerequisites

To use this connector, you'll need:

• A SQLServer database to which to materialize, and user credentials.
  • SQLServer 2017 and later are supported
  • The connector will create new tables in the database per your specification, so user credentials must have access to create new tables.
• At least one Flow collection

Setup

To meet these requirements, follow the steps for your hosting type.

• Self-hosted SQL Server
• Azure SQL Database
• Amazon RDS for SQL Server
• Google Cloud SQL for SQL Server

Self-hosted SQL Server

1. Connect to the server and issue the following commands:

USE <database>;
-- Create user and password for use with the connector.
CREATE LOGIN flow_materialize WITH PASSWORD = 'secret';
CREATE USER flow_materialize FOR LOGIN flow_materialize;
-- Grant control on the database to flow_materialize
GRANT CONTROL ON DATABASE::<database> TO flow_materialize;

2. Allow secure connection to Estuary Flow from your hosting environment. Either:

   • Set up an SSH server for tunneling.

     When you fill out the endpoint configuration, include the additional networkTunnel configuration to enable the SSH tunnel. See Connecting to endpoints on secure networks for additional details and a sample.

   • Allowlist the Estuary IP addresses in your firewall rules.

Azure SQL Database

1. Allow connections between the database and Estuary Flow. There are two ways to do this: by granting direct access to Flow's IP or by creating an SSH tunnel.

   1. To allow direct access:

      • Create a new firewall rule that grants access to the Estuary Flow IP addresses.

   2. To allow secure connections via SSH tunneling:

      • Follow the guide to configure an SSH server for tunneling
      • When you configure your connector as described in the configuration section above, including the additional networkTunnel configuration to enable the SSH tunnel. See Connecting to endpoints on secure networks for additional details and a sample.

2. In your SQL client, connect to your instance as the default sqlserver user and issue the following commands.

USE <database>;
-- Create user and password for use with the connector.
CREATE LOGIN flow_materialize WITH PASSWORD = 'secret';
CREATE USER flow_materialize FOR LOGIN flow_materialize;
-- Grant control on the database to flow_materialize
GRANT CONTROL ON DATABASE::<database> TO flow_materialize;

3. Note the following important items for configuration:

   • Find the instance's host under Server Name. The port is always 1433. Together, you'll use the host:port as the address property when you configure the connector.

Configuration

To use this connector, begin with data in one or more Flow collections. Use the below properties to configure a SQLServer materialization, which will direct one or more of your Flow collections to your desired tables, or views, in the database.

Properties

Endpoint

Property	Title	Description	Type	Required/Default
/database	Database	Name of the logical database to materialize to.	string	Required
/address	Address	Host and port of the database. If only the host is specified, port will default to 3306.	string	Required
/password	Password	Password for the specified database user.	string	Required
/user	User	Database user to connect as.	string	Required

Bindings

Property	Title	Description	Type	Required/Default
/table	Table	Table name to materialize to. It will be created by the connector, unless the connector has previously created it.	string	Required
/delta_updates	Delta Update	Should updates to this table be done via delta updates.	boolean	false

Sample

materializations:
  ${PREFIX}/${mat_name}:
    endpoint:
      connector:
        image: ghcr.io/estuary/materialize-sqlserver:dev
        config:
          database: flow
          address: localhost:5432
          password: flow
          user: flow
    bindings:
      - resource:
          table: ${TABLE_NAME}
        source: ${PREFIX}/${COLLECTION_NAME}

Delta updates

This connector supports both standard (merge) and delta updates. The default is to use standard updates.

Reserved words

SQLServer has a list of reserved words that must be quoted in order to be used as an identifier. Flow considers all the reserved words in the official SQLServer documentation.

These reserved words are listed in the table below. Flow automatically quotes fields that are in this list.

Reserved words
absolute	connect	else	intersect	on
action	connection	end	intersection	only
ada	constraint	end-exec	interval	open
add	constraints	equals	into	opendatasourc
admin	constructor	errlvl	is	openquery
after	contains	escape	isolation	openrowset
aggregate	containstable	every	iterate	openxml
alias	continue	except	join	operation
all	convert	exception	key	option
allocate	corr	exec	kill	or
alter	corresponding	execute	language	order
and	count	exists	large	ordinality
any	covar_pop	exit	last	out
are	covar_samp	external	lateral	outer
array	create	extract	leading	output
as	cross	false	left	over
asc	cube	fetch	less	overlaps
asensitive	cume_dist	file	level	overlay
assertion	current	fillfactor	like	pad
asymmetric	current_catal	filter	like_regex	parameter
at	current_date	first	limit	parameters
atomic	current_defau	float	lineno	partial
authorization	current_path	for	ln	partition
avg	current_role	foreign	load	pascal
backup	current_schem	fortran	local	path
before	current_time	found	localtime	percent
begin	current_times	free	localtimestam	percent_rank
between	current_trans	freetext	locator	percentile_co
binary	current_user	freetexttable	lower	percentile_di
bit	cursor	from	map	pivot
bit_length	cycle	full	match	plan
blob	data	fulltexttable	max	position
boolean	database	function	member	position_rege
both	date	fusion	merge	postfix
breadth	day	general	method	precision
break	dbcc	get	min	prefix
browse	deallocate	global	minute	preorder
bulk	dec	go	mod	prepare
by	decimal	goto	modifies	preserve
call	declare	grant	modify	primary
called	default	group	module	print
cardinality	deferrable	grouping	month	prior
cascade	deferred	having	multiset	privileges
cascaded	delete	hold	names	proc
case	deny	holdlock	national	procedure
cast	depth	host	natural	public
catalog	deref	hour	nchar	raiserror
char	desc	identity	nclob	range
char_length	describe	identity_inse	new	read
character	descriptor	identitycol	next	reads
character_len	destroy	if	no	readtext
check	destructor	ignore	nocheck	real
checkpoint	deterministic	immediate	nonclustered	reconfigure
class	diagnostics	in	none	recursive
clob	dictionary	include	normalize	ref
close	disconnect	index	not	references
clustered	disk	indicator	null	referencing
coalesce	distinct	initialize	nullif	regr_avgx
collate	distributed	initially	numeric	regr_avgy
collation	domain	inner	object	regr_count
collect	double	inout	occurrences_r	regr_intercep
column	drop	input	octet_length	regr_r2
commit	dump	insensitive	of	regr_slope
completion	dynamic	insert	off	regr_sxx
compute	each	int	offsets	regr_sxy
condition	element	integer	old	regr_syy
relative	semanticsimil	structure	truncate	window
release	semanticsimil	submultiset	try_convert	with
replication	sensitive	substring	tsequal	within
restore	sequence	substring_reg	uescape	within
restrict	session	sum	under	without
result	session_user	symmetric	union	work
return	set	system	unique	write
returns	sets	system_user	unknown	writetext
revert	setuser	table	unnest	xmlagg
revoke	shutdown	tablesample	unpivot	xmlattributes
right	similar	temporary	update	xmlbinary
role	size	terminate	updatetext	xmlcast
rollback	smallint	textsize	upper	xmlcomment
rollup	some	than	usage	xmlconcat
routine	space	then	use	xmldocument
row	specific	time	user	xmlelement
rowcount	specifictype	timestamp	using	xmlexists
rowguidcol	sql	timezone_hour	value	xmlforest
rows	sqlca	timezone_minu	values	xmliterate
rule	sqlcode	to	var_pop	xmlnamespaces
save	sqlerror	top	var_samp	xmlparse
savepoint	sqlexception	trailing	varchar	xmlpi
schema	sqlstate	tran	variable	xmlquery
scope	sqlwarning	transaction	varying	xmlserialize
scroll	start	translate	view	xmltable
search	state	translate_reg	waitfor	xmltext
second	statement	translation	when	xmlvalidate
section	static	treat	whenever	year
securityaudit	statistics	trigger	where	zone
select	stddev_pop	trim	while
semantickeyph	stddev_samp	true	width_bucket

Changelog

The changelog includes a list of breaking changes made to this connector. Backwards-compatible changes are not listed.

Proceed with caution when editing materializations created with previous versions of this connector; editing always upgrades your materialization to the latest connector version.

V1: 2023-09-01

• First version