Use table from another SQL 2016 instance

Question

Post reply

Use table from another SQL 2016 instance

vsivakumar.in

SSC Rookie

Points: 33
More actions
January 5, 2023 at 10:04 pm

#4134239

Hi,I have two SQL 2016 Enterprize instances, payroll and PP (Production planning); currently, I have an employee(Demographic) table in both instance databases, and now data is loaded in both instances' tables. I want to remove the employee table from PP and utilize the table from the payroll.
1)I decided to create the view in PP from SOARS using a linked server, which will slow the read options. Can I implement an external table using a polybase(Getexternal) data?
Move the Payroll and the PP databases in one instance so that If I create a view employee in PP for the Payroll table employee, it will also allow me to read and write.
If I move, my SQL Server instance needs to hold two databases, each having 1TB of data. Is that okay to have two databases, each with 1 TB of data?
If Azure or AWS support built-in functionality to share tables across two databases, that is also fine.
If somebody has already implemented or has an efficient way, please advise.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Quick question, can you tell us a tiny bit more about the setup?

😎

Useful information would be something like VMs, managed server instances or managed DB instances!

My guess, by the way, you worded the question, is that you are looking for hosting options rather than what is available in your current environment.

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 2

If you do go to Azure SQL Database, while there are cross-database queries possible, the architecture is very different from any other implementation, so that will be a somewhat limiting factor for future migrations.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

vsivakumar.in SSC Rookie Points: 33 More actions · Answer 3

Thank you our instances or VMs manged by hypervisor

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

vsivakumar.in wrote:

Thank you our instances or VMs manged by hypervisor

Can you please elaborate a little further?

😎

What you have told us so far is as helpful as saying that you have a sandwich constructed of two slices of bread and a filling, we need to know both the type of bread and the filling!