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? -
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.
-
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.:
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:- Never process the file until it is completely written/received
- 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?
