Microsoft SQL Server 2016 Express

[slmyers]

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.

[blahi]

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.

[mrweasel]

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.

[brightball]

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.

[mvitorino]

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.

[snarfy]

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.

[mobiuscog]

It's exceedingly useful for developers that just need to fire this up to test something.

I'm not convinced anyone would use express for production purposes anyway due to the fairly severe memory/thread limitations.

[catmanjan]

Useful for developers targeting organisations on Microsoft stacks

[NoGravitas]

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.

[jsudhams]

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

[quickben]

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.

[jbergens]

SQL Express is free in itself. You do have to pay for the Windows OS on all servers though but that has little to do with the application.

[greggyb]

Memory-optimized transactional engine (since 2014). Native compilation of stored procedures. In-memory tables that can optionally be persisted to disk. https://msdn.microsoft.com/en-us/library/dn133186.aspx

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

[serguzest]

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.

[devopsproject]

SQL Server Management Studio is light years ahead of any pgsql management tool

[blahi]

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.

[cm2187]

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?

[benjaminwootton]

Reduced boot time, lower overhead, deployment speed, consistent environments, better resource utilisation, security, flexible isolation, better clustering options, no VMWare licensing fees, image signing workflows & a few more...

[netcraft]

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?

[windowsworkstoo]

Multiple copies of a large base db, for free...you only pay for the data when the differences start to flow in.

[sztwiorok]

Docker is the best thing since sliced bread!

From their latest newsletters it appears that they intensified cooperation with MS. Hope it does a good turn!

[tracker1]

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.

[ashtavakra]

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.

[karma_fountain]

Good for development environments though. Probably don't want to be running SQL Express for large databases anyway.

[mattmanser]

You can only have a maximum 10 gb database on SQL Express, no point doing any of those things with that.

[fleitz]

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 :)

[jdoliner]

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.

[andrewchambers]

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.

[scotty79]

Is express still limited to 10gb databases?