TR Forums

I have two MSSQL databases where I need to do the following, but I am unsure how to do it. There seem to be products that can do it for you, but I would think you could script something without having to purchase other software to do it. Here's the scenario:

Multiple devices constantly write data to a table on Database A on Server A.

I need Database A to sync that table to Database B on Server B.

Database B will append a "1" value to the line items in that table to mark the items as "collected".

Database B will sync back to Database A, which reads the "1" and deletes the lines.

This seems simple to me, but I don't know how to go about doing it. And we don't have any real DBAs here, so this falls on me

Your suggestions would be greatly appreciated.

I guess first I'd want to know how much data we are talking about, and the frequency of synchronization before I comment further.

The table in Database A will eventually have several hundred rows that are all updated every 2 minutes from the collecting devices (PLCs in a manufacturing environment).

We would want the A-B-A synch to happen pretty much constantly.

Yeah, a cronjob script is all you really need. With one little change to your thought process.

Pick your scripting language of choice and do something that follows this pseudo-code:


Something along that line should do the trick. I don't know any details about MSSQL, so that's just how I'd approach this problem at first blush.

Synchronization to me implies that you want the data to be uniform across the two servers at least for this one table. If that's the case, you don't need this appending 1, read 1, delete 1 scenario - it's extra trips if I understand it. Whatever criteria you have for flagging something as collected in the first place can be used to delete from both tables one after the other.

This sounds like a job for an SSiS task. If you have MS SQL installed, SSiS should come with it, though perhaps not as part of the default install. Anyhow, what I would do is create a new SSiS Integration Services Project and define your two connections to the two servers and set the default database. Then, create a new sql task that implements whatever logic you have to determine your "1" flag. I'm assuming that because you're trying to maintain conformity that the tables have the same column names etc, so drag (2) instances of the "execute SQL Task" onto the control flow. From there, assign each one, one of the two possible connections to each SQL Server. Within each SQL task, copy the same query. Save this project, it will create a DTSX file.

How you run that from here is up to you, I'd just make a .BAT file to run that as a scheduled task as needed. SSiS was created as a bed-buddy to SQL, they are literally made for each other. I have grown to love it for all it's worth. It can do a lot more than just SQL stuff too. I've written some complex programs with it. If you've never used SSiS i'd give it a whirl, for this task it's basically drag and drop while creating 2 connection objects 2 Sql queries and saving a file, it took me less than 5 minutes to mock up. Check out these screen shots, the only difference is that you will obviously have 2 connection objects where my screen only shows 1.


http://mspdigitaldirect.com/outbound/ssis1.jpg
http://mspdigitaldirect.com/outbound/ssis2.jpg

Pay attention to the properties of each object at the far right describing the objects connection / query. It's pretty obvious what it does.

Thank you both for the information.

Apparently the needs of this project have changed, but unlike most of the time, it has become more simple.

I just need to have the data from one table on Database A to write to a table on Database B, and do this every 3 minutes.

I'm playing with a SSiS Integration Services Project as steelcity_ballin suggested, just have to figure out how to make it work.

emkubed wrote:

I just need to have the data from one table on Database A to write to a table on Database B, and do this every 3 minutes.

Not sure what you mean here... is it:

- Every 3 minutes B should be made to look just like A, including any inserts/deletes/alterations?

- Every 3 minutes, any new records in A get transferred to B (but any pre-existing records which have been removed or altered in A do not need to be removed or altered in B)?

- Every 3 minutes, something completely re-creates A with new data, and the whole thing needs to get dumped over to B?

- Something else?

The details of what you need to do will depend on the details of what you mean!

I should have fleshed this out in greater detail.

Every 2 minutes, collecting devices (PLCs) write new data to a table on DB A (here's the temperature of the oven at 10:02, 10:04, etc.)

Every 3 minutes (an arbitrary number, we just need the data moved somewhat frequently), we'd want the entire contents of that table written to a table on DB B.

That's all the logic I need, as the software vendor of the programs using DB B will manipulate that newly-written data for reports, the purge the table on B when it's done.

So is the entire table in DB A truncated to zero length and re-populated every 2 minutes? Or is it continuously growing? Unless there's some sort of coordination between the thing that populates the table and the thing that ships the data to the other DB, there's potential for great inefficiency and/or race conditions which could result in long delays, incorrect/incomplete data on database B, or both.

just brew it! wrote:

So is the entire table in DB A truncated to zero length and re-populated every 2 minutes? Or is it continuously growing?

I believe (I will confirm in the morning) that it just overwrites the data every 2 minutes. There's a row in the table for each PLC device.

Unless the thing populating the table and the thing shipping the data from A to B are synchronized in some way, you run a risk of trying to synchronize the tables when only some of the PLCs have written their data to database A. This could result in incomplete/inconsistent data in database B.

Either the update needs to be done as an atomic transaction, or there needs to be an interlock of some sort to prevent the race condition.

Or is the data just trickling in from the various PLCs on a 2 minute cycle (but with no coordination between the various PLCs), and all we care about is getting a snapshot of the latest values from each sensor at any given moment? If so, maybe the race condition isn't so bad.

Edit: FWIW I've actually done stuff like this in a previous life... process monitoring for RF cable and waveguide production lines. I've seen this show before...

just brew it! wrote:

Or is the data just trickling in from the various PLCs on a 2 minute cycle (but with no coordination between the various PLCs), and all we care about is getting a snapshot of the latest values from each sensor at any given moment? If so, maybe the race condition isn't so bad.

Edit: FWIW I've actually done stuff like this in a previous life... process monitoring for RF cable and waveguide production lines. I've seen this show before...

I think it's this one. I will confirm and post in the morning.

Thanks.

This can be done using a Trigger or Replication of the table.

Further clarification.

Each PLC device has 7-8 values (temp, pressure, etc.). Every 4 seconds, software on the server hosting DB A polls the PLC devices and updates a table named "Present Value".

Every 2 minutes, another routine copies the present value table and to another table, the table we want to copy to DB B. Once copied, we need to remove those rows from that table on DB A.

Apparently, we are only collecting with a handful of devices, and once we get this to work we will enabling the remainder, which will result in a ton of data unless we do the "copy and delete".