MSSQL db sync-and-delete question

From Visual Basic to GNU C, this is the place to talk programming.

Moderators: SecretSquirrel, just brew it!

MSSQL db sync-and-delete question

Postposted on Wed Jul 08, 2009 10:48 am

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.
Semi-official TR gaming group at http://www.coldblackeyes.com/forums/
emkubed
Gerbil Elder
 
Posts: 5848
Joined: Mon Jun 02, 2003 9:28 am
Location: Limbo

Re: MSSQL db sync-and-delete question

Postposted on Wed Jul 08, 2009 2:24 pm

I guess first I'd want to know how much data we are talking about, and the frequency of synchronization before I comment further.
Corsair 600T | ASUS P8P67 PRO | Intel 2500k @ 4.4Ghz | EVGA 560 TI | G.SKILL Ripjaws Series 8GB | Corsair HX650 650W
steelcity_ballin
Gerbilus Supremus
Silver subscriber
 
 
Posts: 11911
Joined: Mon May 26, 2003 5:55 am
Location: Pittsburgh PA

Re: MSSQL db sync-and-delete question

Postposted on Wed Jul 08, 2009 4:05 pm

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.
Semi-official TR gaming group at http://www.coldblackeyes.com/forums/
emkubed
Gerbil Elder
 
Posts: 5848
Joined: Mon Jun 02, 2003 9:28 am
Location: Limbo

Re: MSSQL db sync-and-delete question

Postposted on Wed Jul 08, 2009 5:38 pm

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:
Code: Select all
$array = $connectionA{ SELECT * FROM table }
foreach $array {
 connectionB{UPDATE * ON table WHERE item_id = $item_id and other conditions where applicable}
 or if the above fails
 connectionB{INSERT INTO table VALUES ($item_id,....)}
}
if each item in $array matches one line in connectionB then {
 foreach $array {
  connectionA{ REMOVE FROM table WHERE item_id =$item_id}
 }
}
else teh failz!


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.
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
titan
Grand Gerbil Poohbah
 
Posts: 3276
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains

Re: MSSQL db sync-and-delete question

Postposted on Thu Jul 09, 2009 2:12 pm

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.
Corsair 600T | ASUS P8P67 PRO | Intel 2500k @ 4.4Ghz | EVGA 560 TI | G.SKILL Ripjaws Series 8GB | Corsair HX650 650W
steelcity_ballin
Gerbilus Supremus
Silver subscriber
 
 
Posts: 11911
Joined: Mon May 26, 2003 5:55 am
Location: Pittsburgh PA

Re: MSSQL db sync-and-delete question

Postposted on Mon Jul 20, 2009 4:49 pm

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.
Semi-official TR gaming group at http://www.coldblackeyes.com/forums/
emkubed
Gerbil Elder
 
Posts: 5848
Joined: Mon Jun 02, 2003 9:28 am
Location: Limbo

Re: MSSQL db sync-and-delete question

Postposted on Mon Jul 20, 2009 10:32 pm

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!
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37659
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: MSSQL db sync-and-delete question

Postposted on Tue Jul 21, 2009 12:20 am

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.
Semi-official TR gaming group at http://www.coldblackeyes.com/forums/
emkubed
Gerbil Elder
 
Posts: 5848
Joined: Mon Jun 02, 2003 9:28 am
Location: Limbo

Re: MSSQL db sync-and-delete question

Postposted on Tue Jul 21, 2009 12:32 am

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.
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37659
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: MSSQL db sync-and-delete question

Postposted on Tue Jul 21, 2009 12:34 am

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.
Semi-official TR gaming group at http://www.coldblackeyes.com/forums/
emkubed
Gerbil Elder
 
Posts: 5848
Joined: Mon Jun 02, 2003 9:28 am
Location: Limbo

Re: MSSQL db sync-and-delete question

Postposted on Tue Jul 21, 2009 12:37 am

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.
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37659
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: MSSQL db sync-and-delete question

Postposted on Tue Jul 21, 2009 12:41 am

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... :wink:
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37659
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: MSSQL db sync-and-delete question

Postposted on Tue Jul 21, 2009 12:46 am

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... :wink:


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

Thanks.
Semi-official TR gaming group at http://www.coldblackeyes.com/forums/
emkubed
Gerbil Elder
 
Posts: 5848
Joined: Mon Jun 02, 2003 9:28 am
Location: Limbo

Re: MSSQL db sync-and-delete question

Postposted on Tue Jul 21, 2009 1:04 am

This can be done using a Trigger or Replication of the table.
b_naresh
Gerbil First Class
 
Posts: 177
Joined: Wed Mar 22, 2006 4:25 pm

Re: MSSQL db sync-and-delete question

Postposted on Tue Jul 21, 2009 8:56 am

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".
Semi-official TR gaming group at http://www.coldblackeyes.com/forums/
emkubed
Gerbil Elder
 
Posts: 5848
Joined: Mon Jun 02, 2003 9:28 am
Location: Limbo


Return to Developer's Den

Who is online

Users browsing this forum: No registered users and 1 guest