How to decouple cross databases query?

Question

Hi

I've been trying to find a solution for this company, where they several databases and applications cross referencing sql queries to pull data for different application.

Here's the summary of existing structure,

Existing structure

Database

1. Travel
2. CRM
3. Product

With travel app being their key app which perform search and collects/updates data from other two DB's.

Proposal

One of the suggestion is that to have Azure Search or Elastic search index on top of DB's so all the application will look to index via API instead of direct sql query.

However with the requirements and existing data structure sitting on different db, having a single index or split indexes for each db. Question relies on how to keep the data sync? or is there a possibility to perform a single search on different index at once?

Much appreciate for any feedback.

Answer 1

Hi @Uday Solanki ,

Question relies on how to keep the data sync? or is there a possibility to perform a single search on different index at once?

So do you want to query cross the database? Are they Azure databases or on -premise databases?

Quote from this doc.: cross-database-queries-in-azure-sql-database
The short set of steps is listed here. We will follow them in more detail below:

Create a login and on the target server/database
Create a master key in the source database
Create a database scoped credential in the source database
Create an external data source in the source database
Create an external table in the source database

More information: select-over-multiple-databases-tsql

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

@Uday Solanki , Adding to Mia; from Azure Search perspective.

You could denormalize the data and create a single search index – there is guidance on that here.
You could also index them in separate indexes and simply submit concurrent queries from the app tier. Separate indexes allows for distinct index configurations, including field tokenization, relevancy, etc. but then you are maintaining multiple configurations and submitting multiple queries.

There is a lot that feeds into this strategy: you could also weigh-in on:

• Are the data sources related? For example, does the Travel database have related records in the CRM or Product databases?
• What are the application requirements?

Note: Kindly conceal any private or PII information on the public forum.

Hope this helps!

Answer 3

Hi @ajkuma

I've been looking at denormalizing data but due application search request makes things complicated. For example looking at Hotel and rooms sample and lets say there's application which allows you book multiple hotel and generate invoice by customer, in this scenario it needs to store

• Customer details
• Booking details
• Hotel details
• Rooms details
• Invoice details

Now let's assume this details are stored in different DB's , suppose there's an application and has a grid of all the booking that has happened today which displays following info,

1. BookReference
2. CustomerName
3. BookDate
4. HotelName
5. NoOfRoom Booked
6. InvoiceStatus Which is done using crossed db queries (as the current problem). So the question is that even by splitting to different index, the api has to request data from different indexes, consolidate those data and return.

Answer 4

My first question would be, if these databases are tightly coupled, why are they in different databases? Why don't you combine them into one database?

Answer 5

Hi @Uday Solanki ,

Quote from this doc. : search-howto-connecting-azure-sql-database-to-azure-search-using-indexers

Depending on several factors relating to your data, the use of Azure SQL indexer may or may not be appropriate. If your data fits the following requirements, you can use Azure SQL indexer.

And quote from this doc.: search-howto-connecting-azure-sql-database-to-azure-search-using-indexers-faq

Q: Can I use Azure SQL indexer with SQL databases running on-premises?

Not directly. We do not recommend or support a direct connection, as doing so would require you to open your databases to Internet traffic. Customers have succeeded with this scenario using bridge technologies like Azure Data Factory. For more information, see Push data to an Azure Cognitive Search index using Azure Data Factory.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.