Subscribe to our insights, updates, and tech news

Thank you!
Coming to your inbox soon.

Four Quick Tips For Using A Microsoft SQL Server Database As A Common Datastore

08 December 2020

2 minute read


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!


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 fullfills 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.


Tags:

Data management

Business intelligence

Data stores

Data transfer

Sql server

Microsoft

Bilger Yahov
ABOUT THE AUTHOR
Bilger Yahov

Bilger Yahov is a Certified Solutions Architect. With an eye for the smallest detail, he currently designs software solutions for enterprise mobile, web and cloud applications.

We forge open, long-lasting partnerships with our customers.

Let us do great work together

post stamp
Please enter your first name
Please enter your last name
Please enter your company
Please enter a valid email
The phone number is invalid
Optional
Optional
We kindly ask you to accept our Privacy Notice when reaching out
Secured with ReCAPTCHA. Privacy Policy and Terms of Service.
OFFICES —
SAVANNAH
1305 Barnard St # 839,
Savannah, GA 31401, USA
+1-770-901-9870
AMSTERDAM
Dusartstraat 46hs, 1072HT,
Amsterdam, Netherlands
+31-65-514-2911
SOFIA
141 Tsarigradsko shose Blvd
1784 Sofia, Bulgaria
+1-770-901-9870

We forge open, long-lasting partnerships with our customers.

Subscribe to our insights

Subscribe to receive insights, updates and tech news from the Resolute team.

You are now subscribed to Resolute's insights!

Thank you!