How to make a remote SQL Server to pull local SQL Server data

Question

In my scenario, I have a SQL Server Express that is my data source. It periodically produce batch of data, that should be uploaded to a central repository (a SQL Server SE in the cloud). The data source know the central repository, but central repository does not know any possible data source (may change over time). I don't know if SQL Server Express support log shipping, nor I am familiar with this technology, anyway I would specify that for other reason it's not a viable solution.

My current solution is to upload, from data source, through linked server (point to the cloud SQL Server SE), all the data when needed. It's work no doubt, but monitoring the central SQL Server SE (in the cloud) I see a high volume of request. Reading documentation seems to me that pushing data is inefficient and will push a row at time (that explain the poor performance I experience and the volume of request on central SQL Server SE).

I am wondering if there is a way to remotely execute a pull on the central SQL Server, triggered by the remote one. That way I can exploit the fact that pulling data should be efficient that pushing it and bypassing the issue that central SQL Server does not know which data source will contact it and when.

Answer 1

That way I can exploit the fact that pulling data should be efficient [...]

How do we even know it's more efficient to go from Central->Edge rather than from Edge->Central? This seems like an XY problem.

A linked server is not efficient, whether it's going one way or the other. Ideally this would be completed by the application, not by the database engine. It also doesn't take into account firewall rules or any other items that may be required.

[...] bypassing the issue that central SQL Server does not know which data source will contact it and when.

If you want to have a list of external items, then since you know the central server information, have the external clients put their information into the central server so that it knows which to contact and when. This should also be completed via an application and not directly in the database engine.

Again, this doesn't take into account any other configurations that may be needed.

Answer 2

There are several ways you can achieve your objective of transferring data efficiently from your local SQL Server Express to a central SQL Server SE in the cloud. However, I understand that you are looking for a solution where the central server can pull data from the local server without knowing about it beforehand. This can be somewhat challenging due to security and connectivity issues, but here's a potential strategy:

Step 1: Setup a Web Service or API You would need to set up a web service or API on the side of your local server. This service could be configured to expose the necessary data to the central server. You might use technologies such as ASP.NET Core or Flask (for Python) to create this service.

Step 2: Security and Authentication Ensure that your web service or API is secure, utilizing appropriate authentication and encryption to protect your data. You might use OAuth, JWT, or other authentication mechanisms to secure your service.

Step 3: Trigger from the Local Server From the local server, whenever there is new data to be transferred, you could send a notification to the central server. This notification would essentially be a request to the central server to pull data from the local server.

Step 4: Data Retrieval by the Central Server Upon receiving the notification, the central server can make HTTP/REST calls to your web service to retrieve the data. You would have to develop a mechanism on your central server to handle these notifications and initiate the data retrieval process. This could be a service or a scheduled task that listens for notifications and retrieves data when notified.

Step 5: Data Import to the Central Server Once the data is retrieved by the central server, it would then import this data into the SQL Server database. You could use various techniques for this, such as bulk insert operations, to improve efficiency.You can potentially try using an ETL process to import data based on the frequency.

Conclusion: By following this strategy, you would effectively be setting up a mechanism where the central server can pull data from the local server upon being notified. This approach would leverage web services to facilitate data transfer, providing a flexible and secure solution to your problem.