Alternatives to MS SQL Server
-
@scottalanmiller said in IIS on prem to hosted migration:
Add to this the tendency to disastrously choose MS SQL Server as a datastore and the problems just skyrocket.
What do you typically recommend as a MS SQL replacement (given the software using it would support other solutions)?
-
@tim_g said in IIS on prem to hosted migration:
@scottalanmiller said in IIS on prem to hosted migration:
Add to this the tendency to disastrously choose MS SQL Server as a datastore and the problems just skyrocket.
What do you typically recommend as a MS SQL replacement (given the software using it would support other solutions)?
Really depends on what you're trying to accomplish. MariaDB or Postgre seem to be very popular. MySQL works fine, but it's very close to MariaDB. MariaDB is a fork of MySQL, but has some added benefits with bug fixes, added storage engines, performance gains, etc. Here's a breakdown of MySQL vs. Postgre.
-
So there are two ways to ask this... one is "what is an alternative relational database server" and one is "what is an alternative database server." As nothing is a drop in replacement to MS SQL Server you have to have the ability to use something different, whether relational or not. Most people will assume that you want to change the server, but not change the fundamental approach to the data storage. But in reality, most applications should be changing both.
-
If you are sticking with relational data, then @BBigford has it: PostgreSQL, MariaDB, and Firebird are your big options. All are completely free, very robust, and completely enterprise. Of course you have non-free options like Oracle, Sybase, Informix, DB2, and so forth, but they aren't going to offer you much of anything really special, but they will cost a lot leaving you back where you started.
PostgreSQL is the big player here. MariaDB is designed specifically around using relational storage for non-relational data; basically a vestige of an era when people were addicted to relational data and thought that nothing else was viable, but deep down they knew that they didn't have relational data and so made a product, originally MySQL, based around a relational interface (via the SQL language) to marginally relational data.
So if SQL Server is the right kind of database for your needs (however, like MySQL, SQL Server is primarily used for data it is not well suited for) then PostgreSQL is the certain replacement option. It is the best at handling truly relational data.
-
If your data is not well suited to being relational, which is generally the case, and you have the capability of changing the underlying type of storage and not just the implementation of the storage, then we have a lot of options.
The problem now becomes "what kind of data do I have?" Figuring that out first is key. You have to understand your data, then you can choose how best to store it.
Common non-relational datastores include Redis, Cassandra, MongoDB, HBase, Couchbase, Memcaches, Neo4J, etc.
-
Speaking of PostgreSQL, has anyone used it with Nextcloud?
-
@black3dynamite said in Alternatives to MS SQL Server:
Speaking of PostgreSQL, has anyone used it with Nextcloud?
No, that's a case where MariaDB is probably best
-
@scottalanmiller said in Alternatives to MS SQL Server:
@black3dynamite said in Alternatives to MS SQL Server:
Speaking of PostgreSQL, has anyone used it with Nextcloud?
No, that's a case where MariaDB is probably best
For gathering contact information, would you use a non-relational or relational DB?
-
@black3dynamite said in Alternatives to MS SQL Server:
@scottalanmiller said in Alternatives to MS SQL Server:
@black3dynamite said in Alternatives to MS SQL Server:
Speaking of PostgreSQL, has anyone used it with Nextcloud?
No, that's a case where MariaDB is probably best
For gathering contact information, would you use a non-relational or relational DB?
Non-relational. Relational is very special case and typically would be focused on high transaction financial systems and the like. Banking is the specific industry for which relational typically is needed.
Of course, any industry might have a specific set of data that would benefit from the high overhead of relational math, but it is extremely rare (meaning a tiny subset of their datastores.)
For relational to be useful requires both a large amount of data that is inter-related in complex ways; and a need for specific types of transactions.
-
@black3dynamite said in Alternatives to MS SQL Server:
Speaking of PostgreSQL, has anyone used it with Nextcloud?
At my past job we used it with PostgreSQL. I have used PostgreSQL since 2007 so I have been a great proponent of it. While I still use MySQL with Percona, PostgreSQL has been my favorite.
-
The Dynamics SL c9nsultant said MSSQL, so that's what I had to do. It's also used with other LoB software we have.
I doubt they would support any other.
With MSSQL it seems all well and good. I never did a whole lot with DBs other than set them up according to recommendations and a few performance BPs.
But then again, I'm never dealing with DBs that are doing 1m transactions a second. They are not heavily used as far as t/s goes.
-
@tim_g said in Alternatives to MS SQL Server:
The Dynamics SL c9nsultant said MSSQL, so that's what I had to do. It's also used with other LoB software we have.
I doubt they would support any other.
MS Products like that are often designed specifically to lock customers into MS SQL Server. That's part of the reason that they bother to make them in the first place.
-
@tim_g said in Alternatives to MS SQL Server:
With MSSQL it seems all well and good. I never did a whole lot with DBs other than set them up according to recommendations and a few performance BPs.
Unless it is free and you have to put in zero effort into managing licenses, it's got some pretty big caveats.
-
Ran PostgreSQL on Windows Server 2012R2 for a CRM database and had zero issues. Was not a huge database by far but I would recommend it as well.
-
@tim_g said in Alternatives to MS SQL Server:
But then again, I'm never dealing with DBs that are doing 1m transactions a second. They are not heavily used as far as t/s goes.
That's hardly the big selling point for avoiding MS SQL Server. MS SQL Server is a great product, from a technology standpoint. But it is relational, making it not ideal for 90%+ of workloads. And it is not free, making it ideal for about 0% of workloads. Given that there are "equally good" free products, it is one of those times that you really have to evaluate hard why any product made by a competent development team with any intention of making something good for customers would ever even consider looking at it.
It's all about the combination of human losses stemming from having to manage the overhead of dealing with the complexities of MS SQL Server. And about adding in the cost of buying it, which is a staggering cost.
While other databases might be faster, that's just about reducing your hardware costs, which might be measureable but are unlikely to be significant. But there are huge features that MS SQL Server doesn't do well, like global replication and more granular locking that some other databases do far better.
-
On a large scale, though, something to consider is just how slow and problematic SW online community is - partially this is because they are using a relational backend for non-relational data. They need many, huge database servers to make it as fast as it is. ML does similar traffic, but on a tiny, by comparison, NoSQL database that screams past them in performance. The difference in costs to maintain, and the differences in performance, are really significant.
-
@scottalanmiller said in Alternatives to MS SQL Server:
@tim_g said in Alternatives to MS SQL Server:
But then again, I'm never dealing with DBs that are doing 1m transactions a second. They are not heavily used as far as t/s goes.
That's hardly the big selling point for avoiding MS SQL Server. MS SQL Server is a great product, from a technology standpoint. But it is relational, making it not ideal for 90%+ of workloads. And it is not free, making it ideal for about 0% of workloads. Given that there are "equally good" free products, it is one of those times that you really have to evaluate hard why any product made by a competent development team with any intention of making something good for customers would ever even consider looking at it.
It's all about the combination of human losses stemming from having to manage the overhead of dealing with the complexities of MS SQL Server. And about adding in the cost of buying it, which is a staggering cost.
While other databases might be faster, that's just about reducing your hardware costs, which might be measureable but are unlikely to be significant. But there are huge features that MS SQL Server doesn't do well, like global replication and more granular locking that some other databases do far better.
Yes I agree and have the same viewpoint as you.
However, we've been locked in prior to this.
All I can say publicly is that we're on a forward path to SaaS.
-
@tim_g said in Alternatives to MS SQL Server:
@scottalanmiller said in Alternatives to MS SQL Server:
@tim_g said in Alternatives to MS SQL Server:
But then again, I'm never dealing with DBs that are doing 1m transactions a second. They are not heavily used as far as t/s goes.
That's hardly the big selling point for avoiding MS SQL Server. MS SQL Server is a great product, from a technology standpoint. But it is relational, making it not ideal for 90%+ of workloads. And it is not free, making it ideal for about 0% of workloads. Given that there are "equally good" free products, it is one of those times that you really have to evaluate hard why any product made by a competent development team with any intention of making something good for customers would ever even consider looking at it.
It's all about the combination of human losses stemming from having to manage the overhead of dealing with the complexities of MS SQL Server. And about adding in the cost of buying it, which is a staggering cost.
While other databases might be faster, that's just about reducing your hardware costs, which might be measureable but are unlikely to be significant. But there are huge features that MS SQL Server doesn't do well, like global replication and more granular locking that some other databases do far better.
Yes I agree and have the same viewpoint as you.
However, we've been locked in prior to this.
All I can say publicly is that we're on a forward path to SaaS.
I think a handy thing to do is to look at MS SQL Server as a flag that a product is either made by a company that is ....
- Clueless and wasting their own money (internal use.)
- Clueless and wasting your money (private use.)
- Intentionally trying to lock you in for some reason (MS back room deal.)
- Abandoned software that the vendor can't maintain.
There's no good reason to find software locked into it. So finding that it exists can be, in many cases, a good enough reason to just walk away from a potential product right then and there.
-
Sorry but we need to break the PostgreSQL bubble:
I read alot of benchmarks and reviews, and you are much better served with MariaDB 100% of the time. Facts check:
- MariaDB performance is better in every single way.
Old but realistic bench:
https://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using-drupal-5x.html
There is others, and you can do test yourself (maybe a good idea for ML user to bench them up) but at the end of the day only recently PostgreSQL performance matches up with MariaDB.
-
Alot of people complain how MariaDB allows invalid data, or is not strict , you can just over ride this with:
https://mariadb.com/kb/en/library/sql-mode/#strict-mode
We are talking editing line and adding 3 words then restarted the service, and BAM you have strict rules like PostgreSQL -
People tend to like PostgreSQL cause it is more Open that MariaDB, which does not make any sense, but it is just fall back from Oracle purchasing MySQL. and fearing something wrong will go with MariaDB, will guess what we have the source code for MariaDB, and we can fork it million times. more over the binary compatibility one with all the mature core from MySQL is good enough and sufficient 90% of the application (MariaDB 5.5.59)
-
it is stupid assumption to think that MariaDB is good for web based apps, you can use it to power anything, including enterprise application.
-
I dont get why you anyone would use PostgreSQL ? it seems to me like old Debian OS, it is there and good to be there, but you wont be actually using it for anything new.
-
Better support and tools from every company with MariaDB.
At the end your free to have choice and I respect this, but dont assume PostgreSQL is better in any way, when all the facts states it is worse.
I might back this up with Benchmark test shortly...
- MariaDB performance is better in every single way.
-
@emad-r said in Alternatives to MS SQL Server:
Sorry but we need to break the PostgreSQL bubble:
I read alot of benchmarks and reviews, and you are much better served with MariaDB 100% of the time. Facts check:
- MariaDB performance is better in every single way.
Old but realistic bench:
https://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using-drupal-5x.html
There is others, and you can do test yourself (maybe a good idea for ML user to bench them up) but at the end of the day only recently PostgreSQL performance matches up with MariaDB.
That's for non-relational data, which is exactly what I already said. MariaDB is faster than PostgreSQL in situations where neither one is a good choice at all.
- MariaDB performance is better in every single way.
