Four quick tips for using a microsoft SQL server database as a common datastore

Day in and day out, we at Resolute Software solve complex engineering challenges. No matter the field, we conduct extensive research and apply best in class engineering principles. Throughout this series of posts, we'll be sharing real-life challenges we've come across during our work.

Enjoy the read!

Bilger Yahov

08 Dec 2020

2 min read

Bilger Yahov

08 Dec 2020

2 min read

Data’s been the new big thing for years. Businesses have started working smarter rather than harder, and are looking for potential new ways to grow their footprint. Building large datastores across different business areas is undoubtedly no longer an extra, but rather a need. The real challenge hides behind aggregating the data, and building insightful information sources.

Imagine that you’ve been assigned the task to gather information from several external databases and to store that information on a SQL Server database. While it might seem quite straightforward at first glance, you’ll quickly realize there’s a lot to be careful with. Have a look at the topics below to make sure that you are aware of some of the possible pitfalls to avoid.

Using linked servers (database engine)

Linked Servers will let you connect and execute commands to external (remote) data sources. For example, if any of your services makes use of a MySQL database, creating the link between them will ease the transfer of data.

Don’t forget to check out the official Microsoft docs for Linked Servers: Linked Servers (Database Engine)

Using OPENQUERY (Transact-SQL)

OPENQUERY fulfills the role of executing pass-through queries on the specified linked server. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name.

OPENQUERY(linked_server, 'query')

Official documentation from Microsoft can be found here: OPENQUERY (Transact-SQL)

Parameterized OPENQUERY

You will have a great time struggling with SQL Server if you want to attach parameters to your OPENQUERY requests. Reading through the official docs, it’s stated:

“OPENQUERY does not accept variables for its arguments.”

Luckily enough, SQL Server allows you to build dynamic queies for this type of challenges.

You can find more info on building dynamic queries and passing through an OPENQUERY, on the official support page: Pass a variable to a linked server query

Procedures vs functions with OPENQUERY

Both Stored Procedures and Functions work well with OPENQUERY requests. There’s one major difference, which comes handy to know while building your data architecture. Functions in SQL Server do not allow the usage of dynamic queries with EXEC statements. Thus, if your use case includes triggering an OPENQUERY with parameters, then using procedures is the way to go. On the other hand, if the use case dictates usage of a non-parameterized, straightforward OPENQUERY, then a function will suffice for the job.

If there’s anything else that comes to your mind, or you need help building the right architecture for your data transfers, please get in touch! We’ll help you identify the critical points and follow the right path through solving the challenge.

Data Management

Business Intelligence

Data Stores

Data Transfer

SQL Server

Microsoft