1

My scenario is likely common: Many product test stations sending test data to a centralized (master) database. (In my case the product being tested is a high frequency transceiver)

My goal: Keep the test stations running if the network connection goes down and the test stations cannot connect to the database. i.e. I need a way to save test data locally, and then to write it to the database when the test station server (the database client) can connect to the database server again. Once the data is written to the central DB server I want to delete it from the test station server.

What I'm looking for: An overall approach and the terminology I need to know so that I can find what I need to read and understand. I don't need a lot of details beyond that, but feel free if you like! :)

What I know already: SQL, SQL Server, Database Design, SSIS, SQL Server Agent.

What I've found so far: Suggestions on this site and elsewhere to use SSIS and replication. (I doubt I'll be using SQL Server. I'll most likely use an open source DB.) (Edit: From what J.D. has said I will likely try to talk my employer into paying for SQL Server once I understand how its features allow me to do what I have outlined in this question)

What I would do if left to my own devices:

  1. Create a centralized database on a server.
  2. Create local databases on the clients, the test stations, with the same schema as the DB server. I'd need a way to propagate schema changes from the DB server to all of the clients)
  3. Have my hardware test application write to the local DB.
  4. Have a process running somewhere that transfers data to the central DB, deleting the local data as it goes.

I know I can write code to do this, but I suspect that I'd be reinventing the wheel, and my wheel would likely be sub-optimal. I'll read more on replication to see if that solves my problem.

Thanks, for any help!

DavidG
  • 11
  • 3
  • So your question is valid and a common goal indeed, but I think unfortunately the lack of response here likely comes from when you said "I doubt I'll be using SQL Server. I'll most likely use an open source DB" after naming off all features that are Microsoft SQL Server specific, and therefore all bets are off if any of those features exist in any other database system. If you were to choose SQL Server as your database server, any and all of those options you mentioned could potentially solve your problem and I'd even be able to provide you specific insights and recommendations on each... – J.D. Mar 17 '21 at 23:48
  • ...of them. But outside of that, the only really common terminology across database systems is Replication, which varies in how it works between each system. So I can only advise you pick a database system first and then look into the specifics of how Replication is handled in that system, or what alternative data synchronization features that database system offers. – J.D. Mar 17 '21 at 23:50
  • "Once the data is written to the central DB server I want to delete it from the test station server" -- this pretty much excludes "normal" replication, which would propagate data deletion just as any other DML. – mustaccio Mar 18 '21 at 00:01
  • @mustaccio While that is generally true, I just want to mention for anyone else who may be reading this, that it is an out-of-the-box property of Microsoft SQL Server replication to be able to replicate everything but deletions, as noted in Options to not replicate SQL Server DELETE commands. Yay Microsoft . – J.D. Mar 18 '21 at 02:55
  • Thanks for the insights. I like MS SQL Server, and might be able to talk my employer into paying for it if I can justify the cost. It is always less expensive to pay upfront for something that works rather than to write and maintain your own code. So any insights into using SQL Server are appreciated. – DavidG Mar 18 '21 at 15:12
  • Message queues might be an option. – John K. N. Mar 25 '21 at 08:07
  • This might be of interest? – Vérace Mar 29 '21 at 21:56

0 Answers0