Why would I use this (windows server 2k16 + Microsoft SQL) vs a linux image + Postgres? Does anyone have any insight in this comparison? It seems to me that this stack is expensive and will likely have less documentation, but I could be wrong on this and there may be very good benefits. Comments appreciated.
To be honest with you, if you have to ask that question, you don't need SQL Server.
In order not to mistake my comment with an attack (it really isn't) one reason is tooling. Postgres doesn't have 1/10th of the tooling available for SQL Server. Another is BI. There isn't a better BI stack out there than Microsoft's. Also the price if very small compared to the competition and licensing costs are dwarfed by implementation costs, in general. So no, SQL Server is not expensive at all. Also the documentation is actually very, very good.
Also, SQL Server gets you some pretty nice benchmarks virtually out of the box. You can beat it on PostGres sometimes, but only after considerable configuration/optimization. And, like you said, the toolchain. SQL Studio will spoil you.
But you need to reboot on install, updates and uninstall, all these operations that can take up to two hours for no reason. And the express edition is capped at 10gb db. So, no, SQL server is nothing better than alternatives at least if you aren't in the big ones and DBA is your job.
Microsoft SQL Server and tooling (SQL Management Studio) is actually very nice to work with. Agreeably there's not much you can do with SQL Server that you can't do with Postgresql, but perhaps you're already a Microsoft shop, and then SQL Server simply fit in better with your deployment, backup and administration software.
In regards to documentation: Microsoft have very good documentation for developers. The SQL Server documentation is at least AS good as the Postgresql documentation, if not better.
It's really weird to approach Microsoft from the developer side, because they have absolutely wonderful products like Visual Studio, C#, Exchange, Active Directory, and SQL Server, but at the same time they have Windows. I feel that Windows, at least the desktop version, is somehow a second tier product, where the quality isn't on par with their other products.
I hate (for fear of the backlash) to ask but what is the problem with Windows me and my colleagues frequently run it for weeks non-stop on desktop. On the server-side it just runs without any issues like any other os..? i see comments like this and genuinely wonder ...
I'm not sure that's really a great comparison (and I really like PostgreSQL) - SQL Server is really a family of products that all depend on the database server component of SQL Server (SSAS, SSIS, SSRS) and that comes with easy integration with lots of applications from Microsoft (e.g. the Dynamics CRM and ERP products) as well as hundreds of 3rd party products.
Internal company workloads. Never use SQL Server for something web facing unless you are ready to have your scaling strategy dictated by licensing costs.
Read replicas, master master, failovers, streaming updated to outside data stores like elastic search before the feature sets are even compared have huge associated costs in workarounds.
Because very often it's not up to the developer/architect to pick the technology to work with. Also what seems expensive to you, very often is not so for the customer.
At the place I work SQL with CALS and software assurance runs 300k. In a free market, I expect that soon people will build solutions on open source alternatives and undercut the competition dramatically.
I would want to use Postgres over SQL Server not because of cost, but because I wouldn't want to lock my data into a proprietary database. I've been doing this long enough to see it go horribly wrong for those poor people married to Oracle.
I have dealt with many applications that use SQL Server Express in production. Those limitations aren't even brushed against with small CRUD style apps. When you are only really serving two or three users and running maybe 100's of tps it's perfectly fine. Postgres is a wonderful database but I can't coach over the phone the average person off the street who is not a sysadmin to get it setup properly, but for SQL Server more than any other DB I certainly could.
I've been using the 'Express' version of MS SQL Server in production for some years now with a handful of small clients, including my current 'in-office' employer. It's not hard to setup a 3rd-party backup solution, and - touch wood - I've never ever had any issues with either the databases or the dependent applications.
No doubt other databases might be 'better' in various ways, but the Express version really is nice to work with and (yes, sorry to admit) it's an easier sell to customers than non-MS solutions (at least for me).
I'm a big supporter of PostGRES, and I think you should use it wherever possible. BUT: MSSQL is not that expensive. To compare apples to apples, you need to compare it to EnterpriseDB. EnterpriseDB is slightly cheaper than MSSQL, but not a lot cheaper. They're both so much cheaper than Oracle that the difference is negligible in comparison.
1. For large corporations internal stack is Windows and CAL is mostly there
2. Existing developers on VB.Net/C# stack -- this is not real issue but still
3. Have couple of large SQL Server boxes cluster and vertically grow and keep adding databases as you like. Go for processor license.. Really scales well for in corporate conccurrent < 10k users scenarios
If the only thing one has is a DB connection and tables, maybe some stored procedures, it's irrelevant. Move freely between databases most of the time with minimal efforts.
You care in the case when your predecessors fell for the lock-in tricks. Database doing job scheduling, actual code in the database (SSIS and the rest), and thing like that.
Others have covered the things that fall out of the relational engine below, including SSIS[0] for ETL and dataflow programming, SSAS[1] for semantic modeling and analytics workloads, SSRS[2][3] for reporting.
There's also MDS[4] for master data management, native support for an elastic database (partition across on-prem and Azure SQL)[5], built in HA [6], and a whole lot more around monitoring and instrumentation.
This is not meant as a fanboy post, but you asked what is part of SQL Server that you don't get with Postgres. As I've mentioned elsewhere, I work for a Microsoft partner so I get exposed to this stuff all day long.
T-SQL is the poorest rdbms language ever existed. Microsoft didn't bother to implement very simple string, datetime functions for 15 years where Postgresql is very rich in that department. So please don't speak about how microsoft tooling is rich.
ruh-roh, master-master replication, multithreaded eexecution, vacuuming tables, in-memory OLTP. Yes postgres is cutting edge and without blame. Stay away from the evil SQL Server, nothing to be gained there.
What's the benefit over giving a VM to a developer? What does a developer gets from setting up a SQL Server in a container that he doesn't get from setting a SQL server in a VM?
- Reduced boot time: I assume this is the case, and it is certainly desirable for firing up a new instance of a web server, but does that really apply to a SQL database? I can only think of marginal use cases.
- consistent environments: a standard docker image is not adding anything over a standard VM image.
- security: I am not a specialist but I doubt containers offer the same level of hardware enforced separation that VM offer
- flexible isolation: what do you have in mind?
- better clustering options: but the SQL server software itself is doing the clustering? Otherwise you will end up with inconsistent datasets
But what I don't understand is the concept of a standard container image. That means this only applies to scenario with static data in the database? Unless this is for database sharding, where you fire up a new empty database to extend a database pool?
Is this actually for developers? It says > requires Windows Server 2016 - how many devs run that locally? Or is this for running this image inside of a windows server 2016 vm?
> Is this actually for developers? It says > requires Windows Server 2016 - how many devs run that locally?
It probably only needs the Windows container-feature. This was first made available in Windows Server 2016.
But today, if you run Windows 10 Pro/Enterprise and have Anniversary Update installed, you should be able to enable the Windows Container-feature there as well.
I'd love to see MS's efforts on the linux subsystem bake to a point where Window's container system can support linux containers, which will bring a MUCH more rich ecosystem of possibilities.
All this is nice, but once the SQL Server database crosses a certain size, there are things one needs to do like separating transaction logs, data files, tempdb files to separate disks for acceptable performance. I am not sure how that will work with SQL Server inside a container.
The purpose of containers is that computers are now large enough that one computer can do the job of many previously smaller computers.
One your workload exceeds that of a normal run of the mill server containers are no longer a good idea. So basically once you start needing to do that which is mostly pointless now anyway with SSDs that would about the time when containers would also be ill suited to your workload.
Also, if you're running docker in prod, have fun :)
If you schedule it on Kubernetes as a pet set you could give each container a consistent virtual disk from your cloud provider. Of course... your cloud provider also has SQL as a service, so that's probably a cheaper option.
Docker on windows complicates things prematurely. So many moving parts to configure. In fact, i think the same thing about docker to begin with, but if i was using docker, i would just use linux.
Readit News
In order not to mistake my comment with an attack (it really isn't) one reason is tooling. Postgres doesn't have 1/10th of the tooling available for SQL Server. Another is BI. There isn't a better BI stack out there than Microsoft's. Also the price if very small compared to the competition and licensing costs are dwarfed by implementation costs, in general. So no, SQL Server is not expensive at all. Also the documentation is actually very, very good.
Dead Comment
In regards to documentation: Microsoft have very good documentation for developers. The SQL Server documentation is at least AS good as the Postgresql documentation, if not better.
It's really weird to approach Microsoft from the developer side, because they have absolutely wonderful products like Visual Studio, C#, Exchange, Active Directory, and SQL Server, but at the same time they have Windows. I feel that Windows, at least the desktop version, is somehow a second tier product, where the quality isn't on par with their other products.
I'm not sure that's really a great comparison (and I really like PostgreSQL) - SQL Server is really a family of products that all depend on the database server component of SQL Server (SSAS, SSIS, SSRS) and that comes with easy integration with lots of applications from Microsoft (e.g. the Dynamics CRM and ERP products) as well as hundreds of 3rd party products.
Read replicas, master master, failovers, streaming updated to outside data stores like elastic search before the feature sets are even compared have huge associated costs in workarounds.
I'm not convinced anyone would use express for production purposes anyway due to the fairly severe memory/thread limitations.
No doubt other databases might be 'better' in various ways, but the Express version really is nice to work with and (yes, sorry to admit) it's an easier sell to customers than non-MS solutions (at least for me).
You care in the case when your predecessors fell for the lock-in tricks. Database doing job scheduling, actual code in the database (SSIS and the rest), and thing like that.
Deleted Comment
In-memory columnstore indices (since 2012, but realistically since 2014/2016). Huge benefit for large analytical queries. https://msdn.microsoft.com/en-us/library/gg492088.aspx
Native row-level security (2016). https://msdn.microsoft.com/en-us/library/dn765131.aspx
Native always-encrypted support - database never sees plaintext (2016). Streamlines workflow, but this is doable with Postgres - questionable inclusion. https://msdn.microsoft.com/en-us/library/dn765131.aspx
Dynamic data masking - don't use for security, though (2016). https://msdn.microsoft.com/en-us/library/mt130841.aspx
R engine in DB (2016). https://msdn.microsoft.com/en-us/library/mt604845.aspx
Others have covered the things that fall out of the relational engine below, including SSIS[0] for ETL and dataflow programming, SSAS[1] for semantic modeling and analytics workloads, SSRS[2][3] for reporting.
There's also MDS[4] for master data management, native support for an elastic database (partition across on-prem and Azure SQL)[5], built in HA [6], and a whole lot more around monitoring and instrumentation.
This is not meant as a fanboy post, but you asked what is part of SQL Server that you don't get with Postgres. As I've mentioned elsewhere, I work for a Microsoft partner so I get exposed to this stuff all day long.
[0] https://msdn.microsoft.com/en-us/library/ms141026.aspx
[1] https://msdn.microsoft.com/en-us/library/bb522607.aspx
[2] Traditional paginated reporting platform and also a newer mobile dashboarding tool based on an acquisition.
[3] https://msdn.microsoft.com/en-us/library/ms159106.aspx
[4] https://msdn.microsoft.com/en-us/library/ff487003.aspx
[5] https://azure.microsoft.com/en-us/documentation/articles/sql...
[6] https://msdn.microsoft.com/en-us/library/ff877884.aspx
- lower overhead, better resource utilisation: agree
- deployment speed: agree
- consistent environments: a standard docker image is not adding anything over a standard VM image.
- security: I am not a specialist but I doubt containers offer the same level of hardware enforced separation that VM offer
- flexible isolation: what do you have in mind?
- better clustering options: but the SQL server software itself is doing the clustering? Otherwise you will end up with inconsistent datasets
But what I don't understand is the concept of a standard container image. That means this only applies to scenario with static data in the database? Unless this is for database sharding, where you fire up a new empty database to extend a database pool?
It probably only needs the Windows container-feature. This was first made available in Windows Server 2016.
But today, if you run Windows 10 Pro/Enterprise and have Anniversary Update installed, you should be able to enable the Windows Container-feature there as well.
And that is a much more likely developer-setup.
From their latest newsletters it appears that they intensified cooperation with MS. Hope it does a good turn!
One your workload exceeds that of a normal run of the mill server containers are no longer a good idea. So basically once you start needing to do that which is mostly pointless now anyway with SSDs that would about the time when containers would also be ill suited to your workload.
Also, if you're running docker in prod, have fun :)