StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat mechanism to be used for asynchronous communication between two SQL Servers in the case?
text
Body
copied!<p>We use a <strong>central</strong> SQL Server (2008 Standard edition) and several smaller, <strong>dedicated</strong> SQL Servers (Express editions). We need to implement some mechanism for transferring data <strong>asynchronously*</strong> from the dedicated decentralized SQL Server (bigger volume, see below) and back from the central SQL Server (few records, basically some notifications for the machines and possibly some optimization hints). </p> <p>The dedicated SQL Servers are physically located near technology machines, and they are collecting say <code>datetime, temperature</code> rows in regular intervals (think about few seconds interval). There are about 500 records for one job, but the next job follows immediately (the machine does not know it is a new job--being quite stupid in the sense -- and simply collects the temperatures on and on).</p> <p>The technology machines must be able to work without the central SQL Server, and the central SQL Server must work also when the machine is not accessible (i.e. its dedicated SQL engine cannot be reached, switched off with the machine). In other words, the solution need not to be super fast, but must be robust in the sense that no collected data is lost.</p> <p>The basic idea is to move the collected data <strong>from the dedicated</strong> SQL Server (preprocessed to the normalized format with ID of the machine) to the well known table on the central SQL Server. Only the newer data should be sent to minimize the amount of the data. That transfer should be started by the dedicated SQL Server in regular intervals (say one hour) if the connection is OK. If the connection is not OK, the data will be sent after next hour, etc.</p> <p>Another well known table on the central SQL Server will be used to send notifications for the dedicated SQL Server engines. This way the dedicated engine can be told (for example) what data was already processed/archived on the central SQL Server (i.e. the hint for what records may already be deleted from the local database on the dedicated machine), or whatever information that is hinted from the central (just hints or other not the real-time requirements). The hints will be collected by the dedicated SQL Server (i.e. also the machine responsibility). In other words, the central SQL Server only processes the well known, local tables. It does not try to connect the dedicated SQL Server machines.</p> <p>The solution should use only the standard mechanisms -- SQL commands (via stored procedures), no external software. What kind of solution should I focus on?</p> <p>Thanks, Petr</p> <p><strong>[Edited later]</strong> The SQL servers are at the same Local Area Network.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload