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