Looking for an alternative database to MS SQL to process a text file.



  • I need to bounce ideas of someone.

    Everyday my company receives text files from our parent company that includes hazardous chemical information on products we make. We receive this file daily because we make new items everyday and need updated hazardous information.

    I’ve created a batch process that imports the text files into Microsoft SQL databases, and then time stamps the files and archives them on our file server. Our label software then connects to the MS SQL databases to print label data. Our company is moving to a new ERP system next year and I will have a chance to update this process. The ERP database is hosted overseas, and our parent company does not give us direct access to the DB. The initial process will still start by receiving text files with the necessary data.

    Now to my question. I want to investigate other options to process this text file. These databases are less the 1GB. We are currently using Microsoft SQL Standard servers to import these text files into. I want to investigate moving to a different database and stop paying MS SQL software fees and SQL CAL licenses.

    I still have a lot to learn when it comes to DB administration. I’m looking for something easy to setup and maintain for someone not very knowledgeable with DB administration. My first thought is setting up a MariaDB server to import the text files to instead, and create the ODBC connections in our label software to the MariaDB. I could also look at MS SQL Express, but then I get very limited and less flexible.

    You may say that I could just have the label software connect to the text file on a file server, but it’s possible I will be receiving this text file every 30min and I’m worried about the label software connection to the file every time the file get updated/overwritten. Depending on how often I am getting the updated text file (which I don’t know yet), connecting directly to the text file could be an option. What is a good open source DB server an IT generalist can setup and maintain in this scenario? Or is their something else out there that you can point me in the direction of to look at? My label software just needs a text file or ODBC connection.



  • You are correct, MariaDB is almost certainly the right tool here. Free and unlimited use, insanely common so everyone knows how to use it and support it, and very easy to use. I think most people would fine MariaDB a bit easy to use than MS SQL Server. I know that I do. There is just so much "less" to know. Basically just install and go.

    Other options would include MySQL, PostgreSQL, Firebird, etc. that are all free, unlimited use, open source, widely used, and retain the relational database structure that you have been using, so nothing new to really learn. But of all these options, MariaDB is probably best.

    SQLite could do this, too, but is not an RDBMS and you'd have to come up with a way to connect to it remotely, which is a complication that you don't need.



  • @magicmarker said in Looking for an alternative database to MS SQL to process a text file.:

    You may say that I could just have the label software connect to the text file on a file server, but it’s possible I will be receiving this text file every 30min and I’m worried about the label software connection to the file every time the file get updated/overwritten.

    In theory this should be fine. Don't hold the connection open. Open it and important the data. Close it when done. Should take a split second. A text file is a form of database. A silly one, but a database nonetheless. There is a tiny, tiny chance that it could be overwritten at that exact second that you have it open. But not very likely.



  • Since you are looking to learn here, as well as accomplish a goal, it is worth noting that a Relational Database like SQL Server, MariaDB, Oracle, Informix, DB2, PostgreSQL, MySQL, etc. is not the right kind of database for this work. This does NOT mean that you should change now, you've done the work, and learning something completely new would be silly. And at this scale, anything that does the job is fine.

    Relational Databases are designed around doing complicated relational integrity. They are "big and heavy" systems because of this. What you are doing is SO basic. What you would want, in an ideal world, is something way simpler from the NoSQL database family (NoSQL simply means that it is something other than a relational database, it can be ANYTHING else.)

    A really simple key-value store sounds like it would meet your needs better. Way simpler, less to learn, less to know, less to do. REDIS would be the obvious choice due to market penetration, popularity and ease of use.

    Again, not saying to switch. Saying that when it comes to learning about this, this is a better way to have approached it. REDIS is purpose built for this kind of need.



  • For what I understand, your label software uses ODBC to connect to the database. For a 1GB Database SQL Server Express sounds right unless you have something not included in that edition. Otherwise, Postgres and MariaDB are great.
    How are you importing the text files, a script?



  • You can easily deploy SQL Server Express on CentOS 7 and have zero licensing needs to worry about.

    SQL Server Express allows up to 10GB databases.

    I cannot recall if SQL Server Express lets you make an inbound ODBC connection. That would be the only possible limitation.

    Beyond that, move to Fedora and MariaDB as mentioned.



  • @scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:

    Since you are looking to learn here, as well as accomplish a goal, it is worth noting that a Relational Database like SQL Server, MariaDB, Oracle, Informix, DB2, PostgreSQL, MySQL, etc. is not the right kind of database for this work. This does NOT mean that you should change now, you've done the work, and learning something completely new would be silly. And at this scale, anything that does the job is fine.

    Relational Databases are designed around doing complicated relational integrity. They are "big and heavy" systems because of this. What you are doing is SO basic. What you would want, in an ideal world, is something way simpler from the NoSQL database family (NoSQL simply means that it is something other than a relational database, it can be ANYTHING else.)

    A really simple key-value store sounds like it would meet your needs better. Way simpler, less to learn, less to know, less to do. REDIS would be the obvious choice due to market penetration, popularity and ease of use.

    Again, not saying to switch. Saying that when it comes to learning about this, this is a better way to have approached it. REDIS is purpose built for this kind of need.

    Not simpler when he needs to use relational database connections to pull the data back out (ODBC). That is not under his control.



  • @scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:

    @magicmarker said in Looking for an alternative database to MS SQL to process a text file.:

    You may say that I could just have the label software connect to the text file on a file server, but it’s possible I will be receiving this text file every 30min and I’m worried about the label software connection to the file every time the file get updated/overwritten.

    In theory this should be fine. Don't hold the connection open. Open it and important the data. Close it when done. Should take a split second. A text file is a form of database. A silly one, but a database nonetheless. There is a tiny, tiny chance that it could be overwritten at that exact second that you have it open. But not very likely.

    What that in mind, just connecting to the text file may work just fine. It would make the process a little less complicated and save me from setting up a MariaDB server and coming up with a process to import the text file to the MariaDB. The text file will just sit on a file server shared folder. I would have label software installed on 15 computers that could have the connection to the text file. The 15 computers are at all my different branch offices connecting back to the HQ file server over SD-WAN/MPLS connections (I'm currently migrating to SD-WAN). In theory I may be able to get away with this correct?



  • @JaredBusch
    I just tried: ODBC can be used to connect to SQL Server Express, just be carefull with the instance name



  • @dave_c said in Looking for an alternative database to MS SQL to process a text file.:

    For what I understand, your label software uses ODBC to connect to the database. For a 1GB Database SQL Server Express sounds right unless you have something not included in that edition. Otherwise, Postgres and MariaDB are great.
    How are you importing the text files, a script?

    I created a SSIS job to import the text file into the MS SQL server table. SSIS is very powerful, but it's daunting to just create simple tasks. If I go the MariaDB route I was looking at this an application called SQL File Import to import my text file into MariaDB. Anyone heard of SQL File Import or used it?



  • @JaredBusch said in Looking for an alternative database to MS SQL to process a text file.:

    @scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:

    Since you are looking to learn here, as well as accomplish a goal, it is worth noting that a Relational Database like SQL Server, MariaDB, Oracle, Informix, DB2, PostgreSQL, MySQL, etc. is not the right kind of database for this work. This does NOT mean that you should change now, you've done the work, and learning something completely new would be silly. And at this scale, anything that does the job is fine.

    Relational Databases are designed around doing complicated relational integrity. They are "big and heavy" systems because of this. What you are doing is SO basic. What you would want, in an ideal world, is something way simpler from the NoSQL database family (NoSQL simply means that it is something other than a relational database, it can be ANYTHING else.)

    A really simple key-value store sounds like it would meet your needs better. Way simpler, less to learn, less to know, less to do. REDIS would be the obvious choice due to market penetration, popularity and ease of use.

    Again, not saying to switch. Saying that when it comes to learning about this, this is a better way to have approached it. REDIS is purpose built for this kind of need.

    Not simpler when he needs to use relational database connections to pull the data back out (ODBC). That is not under his control.

    Why? ODBC isn't limited to RDBMS.

    https://redislabs.com/redis-enterprise/connectors/odbc-jdbc-connectors/



  • @JaredBusch said in Looking for an alternative database to MS SQL to process a text file.:

    You can easily deploy SQL Server Express on CentOS 7 and have zero licensing needs to worry about.

    SQL Server Express allows up to 10GB databases.

    I cannot recall if SQL Server Express lets you make an inbound ODBC connection. That would be the only possible limitation.

    Beyond that, move to Fedora and MariaDB as mentioned.

    I can't imagine that it would not. It's not like SQLite. It would be a crippling limitation that would undermine Microsoft's own goals.



  • @magicmarker
    I am almost sure SSIS needs Standard+
    A python, powershell, etc. script would do but needs programming time.

    SQL File Import looks nice. Can it be automated?



  • @magicmarker said in Looking for an alternative database to MS SQL to process a text file.:

    @scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:

    @magicmarker said in Looking for an alternative database to MS SQL to process a text file.:

    You may say that I could just have the label software connect to the text file on a file server, but it’s possible I will be receiving this text file every 30min and I’m worried about the label software connection to the file every time the file get updated/overwritten.

    In theory this should be fine. Don't hold the connection open. Open it and important the data. Close it when done. Should take a split second. A text file is a form of database. A silly one, but a database nonetheless. There is a tiny, tiny chance that it could be overwritten at that exact second that you have it open. But not very likely.

    What that in mind, just connecting to the text file may work just fine. It would make the process a little less complicated and save me from setting up a MariaDB server and coming up with a process to import the text file to the MariaDB. The text file will just sit on a file server shared folder. I would have label software installed on 15 computers that could have the connection to the text file. The 15 computers are at all my different branch offices connecting back to the HQ file server over SD-WAN/MPLS connections (I'm currently migrating to SD-WAN). In theory I may be able to get away with this correct?

    Oh, you are trying to do client server, no app to control access. That adds complication. But should still work here as long as they are not writing to the file.



  • @magicmarker said in Looking for an alternative database to MS SQL to process a text file.:

    @dave_c said in Looking for an alternative database to MS SQL to process a text file.:

    For what I understand, your label software uses ODBC to connect to the database. For a 1GB Database SQL Server Express sounds right unless you have something not included in that edition. Otherwise, Postgres and MariaDB are great.
    How are you importing the text files, a script?

    I created a SSIS job to import the text file into the MS SQL server table. SSIS is very powerful, but it's daunting to just create simple tasks. If I go the MariaDB route I was looking at this an application called SQL File Import to import my text file into MariaDB. Anyone heard of SQL File Import or used it?

    SSIS requires full SQL server. SO if that is your process, SQL Express is out. Just go with Fedora and MariaDB.



  • @scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:

    @JaredBusch said in Looking for an alternative database to MS SQL to process a text file.:

    You can easily deploy SQL Server Express on CentOS 7 and have zero licensing needs to worry about.

    SQL Server Express allows up to 10GB databases.

    I cannot recall if SQL Server Express lets you make an inbound ODBC connection. That would be the only possible limitation.

    Beyond that, move to Fedora and MariaDB as mentioned.

    I can't imagine that it would not. It's not like SQLite. It would be a crippling limitation that would undermine Microsoft's own goals.

    I expected it woudl work, but I had not tested it.



  • @JaredBusch
    And what is the SSIS equivalent for MariaDB?



  • @scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:

    @JaredBusch said in Looking for an alternative database to MS SQL to process a text file.:

    @scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:

    Since you are looking to learn here, as well as accomplish a goal, it is worth noting that a Relational Database like SQL Server, MariaDB, Oracle, Informix, DB2, PostgreSQL, MySQL, etc. is not the right kind of database for this work. This does NOT mean that you should change now, you've done the work, and learning something completely new would be silly. And at this scale, anything that does the job is fine.

    Relational Databases are designed around doing complicated relational integrity. They are "big and heavy" systems because of this. What you are doing is SO basic. What you would want, in an ideal world, is something way simpler from the NoSQL database family (NoSQL simply means that it is something other than a relational database, it can be ANYTHING else.)

    A really simple key-value store sounds like it would meet your needs better. Way simpler, less to learn, less to know, less to do. REDIS would be the obvious choice due to market penetration, popularity and ease of use.

    Again, not saying to switch. Saying that when it comes to learning about this, this is a better way to have approached it. REDIS is purpose built for this kind of need.

    Not simpler when he needs to use relational database connections to pull the data back out (ODBC). That is not under his control.

    Why? ODBC isn't limited to RDBMS.

    https://redislabs.com/redis-enterprise/connectors/odbc-jdbc-connectors/

    Also something I had not looked into.



  • @scottalanmiller

    Why? ODBC isn't limited to RDBMS.

    https://redislabs.com/redis-enterprise/connectors/odbc-jdbc-connectors/

    Yes, I remember using it to connect to Excel.



  • @dave_c said in Looking for an alternative database to MS SQL to process a text file.:

    @magicmarker
    I am almost sure SSIS needs Standard+
    A python, powershell, etc. script would do but needs programming time.

    SQL File Import looks nice. Can it be automated?

    Yes, you can completely automate the text import into your DB with SQL File Import. You create your job, then setup a scheduled task in the application.



  • @magicmarker

    Yes, you can completely automate the text import into your DB with SQL File Import. You create your job, then setup a scheduled task in the application.

    Then I think that you have your solution: SQL File Import connects to every major data source, even the free ones discussed here



  • @dave_c said in Looking for an alternative database to MS SQL to process a text file.:

    @magicmarker

    Yes, you can completely automate the text import into your DB with SQL File Import. You create your job, then setup a scheduled task in the application.

    Then I think that you have your solution: SQL File Import connects to every major data source, even the free ones discussed here

    Right, but I'm also going to test just using using the text file on a file share folder. My label software will easily process and handle the text file connection (using BarTender label software). The MariaDB solution with SQL File Import will be plan B. If I don't have to make more complicated than I have to, then I was going to leave it simple. I just don't know if using the text file is going to work out.



  • @magicmarker
    I see 2 conditions for that to work:

    1. Never process the file until it is completely written/received
    2. The bar code software must not lock the file exclusively so all branches can use it (possibly) at the same time


  • @scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:

    Since you are looking to learn here, as well as accomplish a goal, it is worth noting that a Relational Database like SQL Server, MariaDB, Oracle, Informix, DB2, PostgreSQL, MySQL, etc. is not the right kind of database for this work. This does NOT mean that you should change now, you've done the work, and learning something completely new would be silly. And at this scale, anything that does the job is fine.

    Relational Databases are designed around doing complicated relational integrity. They are "big and heavy" systems because of this. What you are doing is SO basic. What you would want, in an ideal world, is something way simpler from the NoSQL database family (NoSQL simply means that it is something other than a relational database, it can be ANYTHING else.)

    A really simple key-value store sounds like it would meet your needs better. Way simpler, less to learn, less to know, less to do. REDIS would be the obvious choice due to market penetration, popularity and ease of use.

    Again, not saying to switch. Saying that when it comes to learning about this, this is a better way to have approached it. REDIS is purpose built for this kind of need.

    Thanks for posting this Scott. Very interesting. I quickly googled REDIS, but it's over my head. Any other good resources you can provide to help me learn more about this?



  • @dave_c said in Looking for an alternative database to MS SQL to process a text file.:

    @magicmarker
    I see 2 conditions for that to work:

    1. Never process the file until it is completely written/received
    2. The bar code software must not lock the file exclusively so all branches can use it (possibly) at the same time

    The BarTender label software does not lock the text file. I have confirmed that with support today. BarTender just does read-only against the text file when it queries the file. The only problem that I could see happening is someone attempting to lookup data (print a label) on the text file the split second it takes to overwrite the file as Scott mentioned. BarTender does not read the file until you query the text file for a lookup. It think it would be very rare that that a lookup happens the exact time the file saves/overwrites, but it could happen.



  • @magicmarker said in Looking for an alternative database to MS SQL to process a text file.:

    @dave_c said in Looking for an alternative database to MS SQL to process a text file.:

    @magicmarker
    I see 2 conditions for that to work:

    1. Never process the file until it is completely written/received
    2. The bar code software must not lock the file exclusively so all branches can use it (possibly) at the same time

    The BarTender label software does not lock the text file. I have confirmed that with support today. BarTender just does read-only against the text file when it queries the file. The only problem that I could see happening is someone attempting to lookup data (print a label) on the text file the split second it takes to overwrite the file as Scott mentioned. BarTender does not read the file until you query the text file for a lookup. It think it would be very rare that that a lookup happens the exact time the file saves/overwrites, but it could happen.

    Should be locked during the write. Probably not a concern.



  • Hmm, I work with these things quite often.

    I suggest changing the name of the text file. That will be an atomic operation. I suggest doing it after the file has been written to disk. If you try rename operation while the file is written to, it will fail. After it has been renamed you have all the time in the world.

    But first you want to check the integrity of the text file before import. How can this be done? Also you want to have a log file created so you can trace things back. You want to make damn sure your stuff is working and have traceability. We are talking about hazardous stuff, liability etc.

    Then you can put the data in the database. I suggest either archiving the text files and/or writing timestamped info in the db. You could have one table with all the hazardous info ever received and another one with just the current one under production.

    To do this you need some simple programming. Any script language will do. For instance php in cli mode, python or whatever you feel comfortable with. If you don't feel comfortable, hire a pro. To me it sounds like a days work, maybe two, all in all.

    Oh, I forgot that you should look into having your importing utility send an email if something goes wrong. Or monitor it some other way. Software like this have a tendency to keep working unnoticed and then no one notices when they stop working for some reason.

    PS. SQL Express has a 10GB data size limit, not 1GB. Otherwise if you are running windows I suggest MySQL as an alternative. They have a nice Workbench software that you can use to administer the db. https://www.mysql.com/products/workbench/

    This is where you find the free (community) version of MySQL:
    https://www.mysql.com/products/community/



  • @Pete-S said in Looking for an alternative database to MS SQL to process a text file.:

    Hmm, I work with these things quite often.

    I suggest changing the name of the text file. That will be an atomic operation. I suggest doing it after the file has been written to disk. If you try rename operation while the file is written to, it will fail. After it has been renamed you have all the time in the world.

    So the flow would look like this then? Receive text file > Rename text file > copy text file to file server shared folder > rename original text file > rename new copy to original name > archive old text file.

    Did I understand that right?



  • @Pete-S said in Looking for an alternative database to MS SQL to process a text file.:

    PS. SQL Express has a 10GB data size limit, not 1GB. Otherwise if you are running windows I suggest MySQL as an alternative. They have a nice Workbench software that you can use to administer the db. https://www.mysql.com/products/workbench/

    I said my databases are under 1GB, not that MS SQL Express has the 1GB size limit.



  • @magicmarker said in Looking for an alternative database to MS SQL to process a text file.:

    @Pete-S said in Looking for an alternative database to MS SQL to process a text file.:

    PS. SQL Express has a 10GB data size limit, not 1GB. Otherwise if you are running windows I suggest MySQL as an alternative. They have a nice Workbench software that you can use to administer the db. https://www.mysql.com/products/workbench/

    I said my databases are under 1GB, not that MS SQL Express has the 1GB size limit.

    And it was stated in like post 6 that Express was 10GB. 👍