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.

  • I don't really understand your problem. Can you create a Linked Server from the SQL Server instance in the cloud to the Express Edition instance data source?
    – J.D.
    Commented Sep 20, 2023 at 12:26
  • @J.D. no I can't, this is the issue. Because data source change quite frequently and are behind firewall/NAT. So basically central SQL Server don't know how to reach data source until receive a connection from them
    – Skary
    Commented Sep 20, 2023 at 13:19
  • "data source change quite frequently" - Wouldn't that mean any solution you implement on the data source, you'll need to update every time the data source changes? (No different then if you had to manage it from the cloud instance?)
    – J.D.
    Commented Sep 20, 2023 at 14:01
  • @J.D. sorry english isn't my mother tongue, probably I messed up my explanation. Data sources are SQL Server Express edition on normal PC inside customer network. Sometimes customer move PC, sometimes network change layout/IP (customer update it) and sometimes the product is installed to another new customer. Each customer may have many instance of the software, that collect and place data into the local SQL Express. When data are ready SQL Express istances send it to the cloud (a SQL Server SE on which they can connect) and push the data. But push is inefficient. Hope to have explained better
    – Skary
    Commented Sep 20, 2023 at 14:59
  • Ps : i prefer to clarify it further, when i say data sources change frequently I mean that SQL Server Express installation (PC) changes. Now i am quite sure I can't do much to change the fact that edge nodes have to proactively connect to central server, but maybe there is a way to let the edge node to request a data pull executed by central node instead of actually send data.
    – Skary
    Commented Sep 20, 2023 at 15:13

2 Answers 2


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.

  • we know that data pull is better than data push because many benchamark about that. I find it well documented like here : mssqltips.com/sqlservertip/5338/… Have i missinterpreted such benchmark? That said, in other situation that does not invole SQL Server, i had an edge that send a request to central node to execute some logic on the edge. In the initial request there are all the data needed to make central node connect to edge, so no central list is required. I am looking for something similar for SQL Server
    – Skary
    Commented Sep 20, 2023 at 15:07
  • 1
    I honestly wouldn't use this at all, as I stated, for various reasons one of which is performance if you're that worried about top notch performance. Again, take SQL out of it and use an application, you can create an API at an endpoint to get information from either side and do whatever needs to be done. Commented Sep 20, 2023 at 15:24
  • 1
    @Skary 100% agreed with Sean, was about to comment the same thing until I read his response. What you've described so far sounds like a problem that should be solved in the application layer, which may make sense to create an API for.
    – J.D.
    Commented Sep 20, 2023 at 15:58
  • Yhea you probably right, at the end of the day I moved the logic to application level and improved performance a lot (~10x). I hoped for a solution more "integrated/native" so I didn't need to maintain/install another application, but seems that it will be too complex/tricky so guess I stick to that solution.Thanks again for help and time
    – Skary
    Commented Sep 20, 2023 at 21:59
  • :) Glad to hear! Commented Sep 21, 2023 at 12:47

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.

  • More chatgpt again Commented Sep 20, 2023 at 20:21

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.