0

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.

1
  • I guess it depends on many factors... How big is the employee table ? What kind of query do you run on it ? (query with join to other tables or just against employee). How ofter is the employee table modified? If there is a lot of data and you are doing join queries, the linked server will probably give you poor performance. If it's not so big, and you run expensive query on it on both side, then having it duplicated may not be a problem (and you could implement incremental load with SSIS if the data is not moving that much). Otherwise, having 2 1T BD on an instance is ok as well Jan 6, 2023 at 13:44

1 Answer 1

0

Reasonable options are

-Move the Payroll and the PP databases in one instance; you'll have to scale the instance to handle the storage and compute requirements of both workloads.

-Replicate the employee data between databases on separate instances. Make Payroll.Employee the master and copy all changes through ETL or Transactional Replication to the PP.Employee

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.