I am investigating the possibility to split one DB into multiple. We decided to move some tables into another database, but we have queries with join on these tables. I found a few solutions about how to achieve that:
But I don`t know what the difference between them and what to choose.
Thanks for any help!
Azure SQL Database Elastic Queries and External data sources are two names for the same concept.
My suggestion is to avoid cross database queries and avoid splitting one database into multiples because query performance involving external data sources won't be the same no matter what strategy you choose to query those external tables.
If you still want to stick with the plan of splitting the database into multiple databases, then know that cross database queries show good performance when the remote tables are not big. When remote tables are big, this article shows you how to perform joins remotely using table variables and improve performance. This other article shows you also how to push parameterized operations to remote databases and improve performance.
if you are thinking to split your DB into multiple SQL server DB with the different host then you can prefer Linked server which has flexible to join across SQL servers
