I have two instances: payroll and PP (Production planning); currently, I have an employee(Demographic) table in both cases, and now data is loaded in both instances. I want to remove the employee table from PP and utilize the table from the payroll.

Question

Hi,
I have two instances: payroll and PP (Production planning); currently, I have an employee(Demographic) table in both cases, and now data is loaded in both instances.

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?

2) 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.

3) 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.

Answer 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?

Which version of SQL Server are you on? On SQL 2019, you can set up the table as an external table rather than using a linked server. This has some advantages, not the least with regards to security. However, external tables are read-only, so if you need to update the remote table, the linked server is a better alternative.

When it comes to speed, there is on reason to hope that the external table is faster. But since a lot depends on the query plan, and you could get a different plan with the external table, you could see major differences - in either direction. But generally, I think the overhead for Polybase is higher, since there are more services involved. This matters particularly, if you are running many short queries.

2) 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.

That is definitely a better option. You gain both in manageability and in speed. And what you lose? I don't know, but maybe there was a reason you set up two instances originally.

3) If Azure or AWS support built-in functionality to share tables across two databases, that is also fine.

I don't know about AWS, but on Azure SQL Databases, you can set up external tables, but this is different from Polybase. I don't know if they are read-write, but I believe they are a more realistic option than external tables in Polybase.

Answer 2

Hi @sivakumar veluswamy

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?

Linked Servers are instance scoped, whereas PolyBase is database scoped, which also means that PolyBase will automatically work across availability groups. Linked Servers use OLEDB providers, while PolyBase uses ODBC. There are a couple more, like the fact that PolyBase doesn’t support integrated security, but the most significant difference from a performance perspective is PolyBase’s capability to scale out ,but Linked Servers are single-threaded.Much depends on your query plan.

2) 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.

Perhaps this method is more reliable.

3) If Azure or AWS support built-in functionality to share tables across two databases, that is also fine.

Maybe you can take a look at this link and see if it helps.369126

Best regards,
Percy Tang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I was solving a similar problem where I needed to make data in a specific table available to a second server. Linked servers, Polybase or moving the database were out of the question.

In the beginning, I decided to use Service Broker technology, with which I sent changes in the data of the source table to a second server, where a copy of the data was stored and available. The disadvantage of this option is that it is more laborious during deployment and, with a higher number of transferred changes, also the speed of synchronization. Since I'm using the Enterprise edition, I switched to an easier and more efficient solution to the problem. In the source instance, I created a new database and deployed a synchronization process that will place data and changes from the appropriate table into the new database. I have created an Availability Group between the two instances and mirror a database with a table whose data should be accessible on the other server. In the case of the Enterprise edition, the database on the secondary replica can be readable, so I synchronize the data almost in real time, which I then use on another server.