3

We use a central SQL Server (2008 Standard edition) and several smaller, dedicated SQL Servers (Express editions). We need to implement some mechanism for transferring data asynchronously* 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).

The dedicated SQL Servers are physically located near technology machines, and they are collecting say datetime, temperature 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).

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.

The basic idea is to move the collected data from the dedicated 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.

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.

The solution should use only the standard mechanisms -- SQL commands (via stored procedures), no external software. What kind of solution should I focus on?

Thanks, Petr

[Edited later] The SQL servers are at the same Local Area Network.

pepr
  • 20,112
  • 15
  • 76
  • 139
  • Sounds like the model scenario for SQL Server replication - unfortunately, the Express edition can only subscribe to replications (e.g. "consume" replication data), but it cannot publish data itself (push it to the central location). – marc_s Jun 03 '12 at 14:11
  • @marc_s: I am aware of the not-full capabilities of the Express edition. But the Express edition is required. Is there any other way to access the data space of the central SQL from inside the Express? Or is there any alternative to storing the data tables at the central SQL? – pepr Jun 03 '12 at 15:24
  • Can the `linked server` be the alternative? Can the *standard edition* SQL be linked from the *express edition* SQL? – pepr Jun 03 '12 at 15:34

2 Answers2

5

If you are willing to make a mental switch and stop thinking in terms of tables and rows and instead think in terms of data and messages then Service Broker can do handle all the communication, delivery and message processing. Instead of locally (on the Express machines) doing INSERT INTO LocalTable(datetime, temperature) VALUES (...) you think in terms of:

BEGIN CONVERSATION WITH CentralServer ...; 
SEND ON conversation MESSAGE TYPE [Measurement] (<datetime...><temperature ...>)

See Using Service Broker instead of Replication or High Volume Contiguous Real Time ETL

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • I have found that the Service Broker is available from the MS SQL 2005. Is the service broker available also for the MS SQL Express editions? (I did not find the information, but searching...) – pepr Jun 03 '12 at 18:54
  • 1
    Thanks! I did not try yet, but it seems that the Service Broker is the way to go for me. The other means like replication and linked server are assymetric with respect to the SQL Express edition. However, the Service Broker with Express edition requires only that one side must be another edition. Found also here http://stackoverflow.com/a/3562356/1346705. – pepr Jun 04 '12 at 17:12
  • Also you can [freely mix 2005, 2008, 2008R2 and 2012 instances](http://rusanu.com/2007/11/28/is-service-broker-in-sql-server-2008-compatible-with-the-one-in-sql-server-2005/), which makes rolling upgrades a whole lot easier. – Remus Rusanu Jun 04 '12 at 17:48
0

Sounds like a job for merge replication.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68