question

UdaySolanki-8092 avatar image
0 Votes"
UdaySolanki-8092 asked MiaMiao-MSFT answered

How to decouple cross databases query?

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.

sql-server-generalazure-cognitive-search
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered UdaySolanki-8092 commented

Hi @UdaySolanki-8092,

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.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @UdaySolanki-8092,

Is the issue solved?

BR,
Mia


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

0 Votes 0 ·

Hi @MiaMiao-MSFT

So currently we already have cross db query which im trying to decouple, so each db will perform CRUD individually instead of cross db updates.

0 Votes 0 ·
ajkuma-MSFT avatar image
0 Votes"
ajkuma-MSFT answered UdaySolanki-8092 commented

@UdaySolanki-8092, 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!



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ajkuma-MSFT see see comment below,


0 Votes 0 ·
UdaySolanki-8092 avatar image
0 Votes"
UdaySolanki-8092 answered UdaySolanki-8092 published

Hi @ajkuma-MSFT

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ErlandSommarskog commented

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?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It's a legacy system which is still running on prod. Plan is decommission these legacy applications gradually, hence the new development we would like to built on top of these such as using search indexes etc.

0 Votes 0 ·

I think it is very difficult for us to give advice, since all we have is an outline of the application and databases.

Also, I think we need to know more about your ultimate goal is. I take it the legacy system is running on-prem, and now you want to move it to Azure.

Have you already settled on Azure SQL DB? Or would Azure Managed Instance be an option for you? In that case, you can keep three databases and run cross-db queries.

0 Votes 0 ·
MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered

Hi @UdaySolanki-8092,

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.

41915-20201124whentouseazuresearch.png

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered

Hi @UdaySolanki-8092,

Is the reply helpful?

BR,
Mia


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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.