SQL Service Broker: Collecting data from distribut

Question

Summary: I need to collect data reliably from the satelite SQL servers using the SQL Server Service Broker. I need to design the protokol that allows a kind of plug-in another satelite SQL server smoothly. (I am going to refine the question based on your suggestions -- including pictures. But I need to start somehow.)

Background: I do have one central SQL server (Microsoft, SQL 2008 R2 Standard ed.) and several small ones near the production machines (the same version, Express edition). The small server collects temperatures from sensors into the table defined this way:

CREATE TABLE dbo.line_sensor_values (
    UTC DATETIME NOT NULL,
    line_no TINYINT NOT NULL,   -- line number: 1, 2, 3, etc.
    sensor_no TINYINT NOT NULL, -- sensor number: 1, 2, 3, etc.
    sensor_value float NULL,    -- the measured value

  PRIMARY KEY CLUSTERED (
    UTC ASC,
    line_no ASC,
    sensor_no ASC
  )
)

The line_no is constant for the small SQL at the production line. The same table is created at the central SQL server, that can be temporarily physically disconnected from the small servers. (You know, the real, physical environment.)

The goal is to transwer all the collected data from the small SQL servers to the central ones. All the servers have the tables created; however, they know nothing about the data at the other side of communication. This way, some protocol must be designed to make the data collection working. A kind of hanshake must be designed to know where to continue with the data transfer after reconnection or after the failure of the sensor data collection.

The central server uses the collected sensor data to be processed as finalisation of some tasks. Say, the data points of certain sensor from certain line (known to the task) must be processed to form the chart. The task knows the time interval for which the sensor values are to be collected. However, the task database environment is not synchronized by events with collection of data. This way, UTC interval is the only way to determine whether the sensor data belong to the task or not.

Again, the data sensor sampling interval is independent on the task, and the SQL servers may be disconnected temporarily. Sometimes, the sensor may be broken, or there can be another reason for missing physical data from the sensor. However, if there is the sensor data with the UTC time, it means that all previous values or exist in the table or they never existed. Consequently, the way to know whether the data for the task are complete is equal to the knowledge that there are newer data for the sensor (produced after the UTC range interval for the task).

The goal is that no collected sensor value is lost. The ideal goal is that there is no need for any other special invocation of the functionality (i.e. via any kind of scheduler).

What was done already:

• Basically, the sensor inserts the data into the dedicated table (other than the mentioned dbo.line_sensor_values above). The trigger gets the data and transforms them, and inserts them to the dbo.line_sensor_values. In other words, the table at the satelite machine is already collecting the data. It already works. This trigger or another mean could be used to send the sensor value via the Service Broker.

• The stored procedure that takes the task, checks the table at the central SQL server for the sensor data, and makes the chart if the data is present was already designed and it works. However, it was use only manually as a proof of the concept.

• The Service Broker setting was already suggested earlier. But the Service Broker communication for the purpose was not designed nor partly tested, yet.

I understand this is a broad question. This way I am going to split it to separate questions...

Separate questions to be solved:

1. SQL Service Broker: Collecting data — plug-in scenario analysis

Thanks for your time and experience, Petr

Answer 1

Sounds like something you don't need Service Broker for. You could add a new column IsReplicated bit not null default(0) to the slave machines. Then you need to regularly copy all data where IsReplicated = 0 to the central server and mark the data as IsReplicated = 1 at the slaves.

This is a very simple synchronization scheme. Would that work for you?

Answer 2

The only purpose of this comment is to close the question that should be considered a summary of the questions that discuss related details. I do not consider the existing answer really answering the question (no offence).