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.

sivakumar veluswamy 21 Reputation points
2022-12-29T15:20:13.96+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,583 questions
No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 68,371 Reputation points Microsoft MVP
    2022-12-29T22:23:52.043+00:00

    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.

    No comments

2 additional answers

Sort by: Most helpful
  1. PercyTang-MSFT 1,426 Reputation points Microsoft Employee
    2022-12-30T06:02:38.163+00:00

    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.

    No comments

  2. David Hlaváček 1 Reputation point
    2023-01-10T22:53:48.66+00:00

    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.

    No comments