Microsoft SQL Server 2016 Express(hub.docker.com)
hub.docker.com
Microsoft SQL Server 2016 Express
https://hub.docker.com/r/microsoft/mssql-server-2016-express-windows/
144 comments
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.
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.
Just curious, What are the toolings that makes SQL server stand out? Also what is BI?
Business Intelligence - https://en.wikipedia.org/wiki/Business_intelligence
>>>Also what is BI?
Business Intelligence helps corporations analyze on their core competency in order to synergize growth strategies globally for cloud-centric and client-focused innovation
Business Intelligence helps corporations analyze on their core competency in order to synergize growth strategies globally for cloud-centric and client-focused innovation
Wow, the first thing that sentence made me think of was this:
https://www.youtube.com/watch?v=GyV_UG60dD4
xD
https://www.youtube.com/watch?v=GyV_UG60dD4
xD
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.
Correct me if I am wrong, MS Sql server isn't true MVCC database and there is some support and it is not default, you have to set READ_COMMITTED_SNAPSHOT ON for the database. They implemented it in 2005. Most microsoft shops don't bother to develop their softwares for this, they are using archaic locking transactions. I've seen many Sql Server deployments keep choking on locking transactions and and people are buyin bigger servers, more Windows and Sql Server licences for each cpu/core to deal with their choked database server.
So while Postgresql's defaults are all about technical concerns, Sql server's defaults are just convenient for Microsoft sales team.
So while Postgresql's defaults are all about technical concerns, Sql server's defaults are just convenient for Microsoft sales team.
Both are true! MS SQL Server is a "true MVCC" database (no-true Scotsman?) and you are correct. SQL Server supports snapshot/MVCC isolation for transactions, but it must be enabled. They are determined to maintain 20 years of backward compatibility, and while I think that's wrong, they leave snapshot isolation disabled by default.
https://msdn.microsoft.com/en-us/library/ms173763.aspx
https://msdn.microsoft.com/en-us/library/ms173763.aspx
It uses tempdb for tracking versions, it is not true mvcc in the engine.
I can't comment on whether this is correct, but on the MSSQL box I integrate with, lock contention and the server automatically killing deadlocked queries is a regular occurrence. Like 50+ times a day regular.
You also get SSDT with SQL Server. PG has nothing like it - https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103)....
Can you elaborate on BI stack? How MS covers all chain?
SQL Server, SSIS, SSAS, SSRS.
and PowerBI
https://powerbi.microsoft.com
https://powerbi.microsoft.com
Loving Power BI.
Dashboards, reports, etc are a pain - but its what the clients want. They don't really care what we're doing in the background, they just want their metrics and analytics reports. Power BI has made that part of my job 100x better.
Dashboards, reports, etc are a pain - but its what the clients want. They don't really care what we're doing in the background, they just want their metrics and analytics reports. Power BI has made that part of my job 100x better.
user management is a little cumbersome but overall i agree. theyre constantly adding integrations with google analytics ,tableau, marketo, etc too
It looks really pretty. But, am I right in saying that this all works by sending all your critical business data to Microsoft servers to run the analysis and generate the reports? Businesses actually do this?
Why would you send data offsite to generate pretty graphs of it? Is this really not available as a self-hosted service?
Why would you send data offsite to generate pretty graphs of it? Is this really not available as a self-hosted service?
Microsoft is cloud-first in development now.
There is a Power BI Desktop program that is 100% free as in beer available, which runs a private instance of an in-memory columnstore OLAP engine, Power Pivot (same internals as SSAS). It also includes the reporting layer and Power Query, a lightweight ETL tool.
There is an on-prem version of Power BI that the team has committed to including as a feature of SSRS in SQL Server 2016, but the timeline is still fuzzy.
The point of the Power BI hosted portal is not the pretty visuals, but a low-maintenance collaboration portal that includes natural language Q&A on your data, automatic analysis to identify trends in the data, and tight integration with Office 365.
If you want to go on-prem, there is a third party named Pyramid Analytics which offers a solution that allows Power BI hosting.
Power BI is actually a continuation of a number of products. If you want a somewhat equivalent experience on-prem, you can use Power Pivot in Excel (2010+), with Power View on SharePoint (SSRS 2012+, SharePoint 2010+), which gives you an older Silverlight-based report engine that has similar capabilities.
The real useful piece, though is the semantic layer in Power Pivot, which is identical to SSAS. The power in any BI tool is not the reporting layer (though Power View is a pretty good one for the use cases it targets), but in the power and expressivity of the data model. This is an area where Microsoft shines.
I could go on for hours about this - I work in technical presales (and do at least 50% time in a technical delivery role) for an analytics consulting firm that is a Microsoft partner.
There is a Power BI Desktop program that is 100% free as in beer available, which runs a private instance of an in-memory columnstore OLAP engine, Power Pivot (same internals as SSAS). It also includes the reporting layer and Power Query, a lightweight ETL tool.
There is an on-prem version of Power BI that the team has committed to including as a feature of SSRS in SQL Server 2016, but the timeline is still fuzzy.
The point of the Power BI hosted portal is not the pretty visuals, but a low-maintenance collaboration portal that includes natural language Q&A on your data, automatic analysis to identify trends in the data, and tight integration with Office 365.
If you want to go on-prem, there is a third party named Pyramid Analytics which offers a solution that allows Power BI hosting.
Power BI is actually a continuation of a number of products. If you want a somewhat equivalent experience on-prem, you can use Power Pivot in Excel (2010+), with Power View on SharePoint (SSRS 2012+, SharePoint 2010+), which gives you an older Silverlight-based report engine that has similar capabilities.
The real useful piece, though is the semantic layer in Power Pivot, which is identical to SSAS. The power in any BI tool is not the reporting layer (though Power View is a pretty good one for the use cases it targets), but in the power and expressivity of the data model. This is an area where Microsoft shines.
I could go on for hours about this - I work in technical presales (and do at least 50% time in a technical delivery role) for an analytics consulting firm that is a Microsoft partner.
Of course they have on-prem versions. A lot of people are content with using cloud services though, so on-prem solutions cost a pretty penny.
See my sibling. There is no Microsoft offering for on-prem Power BI. You can get close to it with sibling products on SharePoint.
There is a third party offering an on-prem solution.
There is a third party offering an on-prem solution.
There's Pyramid Analytics which is officially endorsed by Microsoft as "Power Bi onprem" and present on the Power BI's homepage.
Pyramid is the third party I mentioned in my post. It is not a Microsoft product, as I mentioned in my post.
Pyramid offers .pbix file hosting and web rendering. It does not offer the Power BI portal that is a cloud offering from Microsoft. There is a big difference between these things.
Pyramid offers .pbix file hosting and web rendering. It does not offer the Power BI portal that is a cloud offering from Microsoft. There is a big difference between these things.
PowerBI seems to work with other SQL databases as well looking at the front page? Does anybody know anything about that?
Power BI can connect directly to many database engines. Queries are generated on the fly by the reporting engine in a proprietary language called DAX, and DirectQuery is a technology that translated DAX into SQL queries.
This is a mode of operation that has been supported by Microsoft's SSAS OLAP engine for years.
It works quite well, but you do run into some performance overhead with the transpiling step.
Feel free to reach out if you have more detailed questions. I work for an analytics consultancy and we're a Microsoft partner. I live in the Microsoft data platform (SQL Server and all related products).
This is a mode of operation that has been supported by Microsoft's SSAS OLAP engine for years.
It works quite well, but you do run into some performance overhead with the transpiling step.
Feel free to reach out if you have more detailed questions. I work for an analytics consultancy and we're a Microsoft partner. I live in the Microsoft data platform (SQL Server and all related products).
And Excel has cube formulas (= MDX) which can process both SSAS and Power BI datasets.
It's not even funny how far ahead MS are in the BI space (ask $DATA shareholders). It's not healthy to be honest.
It's not even funny how far ahead MS are in the BI space (ask $DATA shareholders). It's not healthy to be honest.
I believe he is referring to Business Intelligence.
Yes, I got it, there's SSIS, DB/OLAP, but after that? and I'm not sure, that SSIS & etc. is present in Express version.
Not sure about 2016, but the 2012 version of Express did have a SKU that included SSRS. There are some limitations to the version. A looong time ago I wrote a utility[0] to work around some of those limitations as we had the licensed version but we were working with partners who had Express.
There are also some questions/answers on StackOverflow [1] that suggest you can mostly get by with doing SSIS stuff against the Express version as well, but you don't get all the capabilities.
[0] https://github.com/spc-ofp/RelinkSSRS [1] http://stackoverflow.com/questions/292564/can-i-run-ssis-pac...
There are also some questions/answers on StackOverflow [1] that suggest you can mostly get by with doing SSIS stuff against the Express version as well, but you don't get all the capabilities.
[0] https://github.com/spc-ofp/RelinkSSRS [1] http://stackoverflow.com/questions/292564/can-i-run-ssis-pac...
This is for developers building apps locally, you create your own dockerised container for prod.
Express is basically free, its the real production versions that cost.
Express is basically free, its the real production versions that cost.
What's interesting about MS too, is that depending on what you do, the production versions still aren't that much money. If you do any sort of commercial software development, MS will pretty much give you a bunch of free software (including MSSQL to a point) just to keep you on the stack and to advertise that you use their products over others. Between BizSpark and the Partnership Network, it's very reasonable.
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.
Nice, downvoted for what? facts?
This is a fact, I work with this thing for about 5 years (this is my karma) and there is not a single day where this doesn't cause a problem or makes somebody to lose 3-5 hours of work.
We have about 3000 people and this is overkill for our needs.
You know what the reason? Because people say that it is awesome, then a naive engineer decide to use it basically due to this propaganda in a LOCAL but central piece of our pipeline (something that SQLite would fit perfectly!) and then everything after becomes a problem.
Now we're stuck with it to day and nobody can change without a lot of work and trouble. I'm basically one of the people responsible in a strike team to fix SQL server issues in production.
This is a fact, I work with this thing for about 5 years (this is my karma) and there is not a single day where this doesn't cause a problem or makes somebody to lose 3-5 hours of work.
We have about 3000 people and this is overkill for our needs.
You know what the reason? Because people say that it is awesome, then a naive engineer decide to use it basically due to this propaganda in a LOCAL but central piece of our pipeline (something that SQLite would fit perfectly!) and then everything after becomes a problem.
Now we're stuck with it to day and nobody can change without a lot of work and trouble. I'm basically one of the people responsible in a strike team to fix SQL server issues in production.
My team is managing around 500 production instances of SQL Server and few times that of non production environments. Various clients various configurations. SQL Server - when configured properly - isn't generally causing any problems. If it is, it's pretty easy to troubleshoot thanks to system views which allow you to see what is happening in sql engine internals with plenty of detail. Try this with Postgres. All our engineers working with both MSSQL and PG/MSSQL praise MSSQL for this visibility into system internals.
Reboots? You can work around those. 2 hours for an upgrade? Boy what are you running this MMSQL on? Should take 20 min tops, and with cluster it's zero downtime (well, few seconds maybe). Express is capped at 10GB. Yes. For user databases (a hint).
So sure, PG stack may be better for some uses, but MSSQL is simply too good overall to just dismiss it the way you're doing it. Also, did someone mention excellent BI stack which is included in price?
I have to say, I work for a bulge bracket bank ran entirely (well, mostly) on MSSQL 2008/2012, and I don't think I've ever had any issues with it, apart from the one set of DBs that someone decided to turn into a backend for an object store that seemingly didn't know anything about database design.
Yes, but as I said in my comment our use case is nothing like a Bank.
See, using SQLExpress to build a desktop LOCAL database doesn't look like a smart move.
I suspect the downvotes (I didn't, but I can see why someone would) are related to the impression you're giving that this is useless in its entirety because it isn't something that is perfect for you.
Our tech team schedules one member to work from midnight to 2 AM on "patch day"
As an alternative you could architect for rolling updates across load-balanced systems.
I'm not personally fussed about the drawbacks either way, the mitigation is easily planned for. It becomes a part of the cost-benefit analysis.
Now consider the world of containers, startup is actually ridiculously quick. In addition they need to be patched far less frequently. They have less "cruft" from the get go.
I'd recommend this entire video, but this graph highlights some of the advantages: https://youtu.be/XVtsw-uzovA?t=500
70% fewer reboots required on containers. 90% fewer critical patches.
They both point to the same physical files. When the old machine finishes its current cycle, it shuts down gracefully.
Fully mitigated by a container workflow.
The express edition is intended to be used by developers and "small data" applications.
I don't see what the express edition license is from this link, but I'm sure it is expressly not intended to be an enterprise database.
If I were downvoting you, it would likely be because you're throwing the baby out with the bathwater on this.
Once we get SQLSERVER running in containers, there's nothing to say we can't swap out to the enterprise edition virtually seamlessly.
So, with respect, I'd suggest you're not getting downvoted for "facts" but for "tone"
Even your follow up comes across as, I almost want to say "entitled"
I'm sorry, I don't want to start a flame war with you, but I'm letting you know what my "read" of your post comes across as.
If you're offended by it I'm happy to delete.
> But you need to reboot on install, updates and uninstall,
Our tech team schedules one member to work from midnight to 2 AM on "patch day"
As an alternative you could architect for rolling updates across load-balanced systems.
I'm not personally fussed about the drawbacks either way, the mitigation is easily planned for. It becomes a part of the cost-benefit analysis.
Now consider the world of containers, startup is actually ridiculously quick. In addition they need to be patched far less frequently. They have less "cruft" from the get go.
I'd recommend this entire video, but this graph highlights some of the advantages: https://youtu.be/XVtsw-uzovA?t=500
70% fewer reboots required on containers. 90% fewer critical patches.
> all these operations that can take up to two hours for no reason.
They both point to the same physical files. When the old machine finishes its current cycle, it shuts down gracefully.
Fully mitigated by a container workflow.
> And the express edition is capped at 10gb db.
The express edition is intended to be used by developers and "small data" applications.
I don't see what the express edition license is from this link, but I'm sure it is expressly not intended to be an enterprise database.
If I were downvoting you, it would likely be because you're throwing the baby out with the bathwater on this.
Once we get SQLSERVER running in containers, there's nothing to say we can't swap out to the enterprise edition virtually seamlessly.
> So, no, SQL server is nothing better than alternatives at least if you aren't in the big ones and DBA is your job.
So, with respect, I'd suggest you're not getting downvoted for "facts" but for "tone"
Even your follow up comes across as, I almost want to say "entitled"
I'm sorry, I don't want to start a flame war with you, but I'm letting you know what my "read" of your post comes across as.
If you're offended by it I'm happy to delete.
Thanks for your reply.
Yes, but we should be able to share personal experiences in comments doesn't? Or else how can we share each other points of view?
I still fail to see what is wrong with my tone in that wording, may be a language issue as English is my third language.
By that I mean mostly: To our experience, there are better alternatives if you're not a DBA as full day job or Google, Facebook, Microsoft, etc.
And, besides that, even with container and schedule and procedures, I fail to see how rebooting still something acceptable now-days. This is highly counter productive.
Yes, but we should be able to share personal experiences in comments doesn't? Or else how can we share each other points of view?
I still fail to see what is wrong with my tone in that wording, may be a language issue as English is my third language.
By that I mean mostly: To our experience, there are better alternatives if you're not a DBA as full day job or Google, Facebook, Microsoft, etc.
And, besides that, even with container and schedule and procedures, I fail to see how rebooting still something acceptable now-days. This is highly counter productive.
Absoloutely! We should 100% be able to discuss issues and our experiences.
English as a third language would explain the "tone" issue as the "nuance" (or subtlety) of you point would likely be harder to communicate.
I think acknowledging the good parts before delivering constructive criticism might help, but then, internet voting sites are also tremendously fickle and difficult to predict.
I think this post above does manage to communicate your point without sounding as negative.
In my case, I work at an agency - so our choice of database is in some ways driven by more than just the best tool for the job, but one that inspires our clients' trust AND is cost effective to work with. MSSQL does an excellent job of "inspiring trust" but it is also amazingly easy to develop for.
Our clients aren't worried about "overkill" or buying more database than they need. In the scope of a multi-million dollar project, the overpowered database is worth the piece of mind.
None of us on this end are "married" to SQL SERVER, but since we already own the license for one, and offer shared hosting of the database server or give a pass-through-cost for a dedicated server, it seems like a perfectly reasonable choice.
But we've both got our perspective and yours offers a good perspective. "As a DBA, SQLSERVER is often overkill" but if the client prioritises stability, recover-ability, maintainability, over cost (a "one time cost" of roughly 25% of one employee's salary) & two hours of downtime a week it's not such a bad choice.
English as a third language would explain the "tone" issue as the "nuance" (or subtlety) of you point would likely be harder to communicate.
I think acknowledging the good parts before delivering constructive criticism might help, but then, internet voting sites are also tremendously fickle and difficult to predict.
I think this post above does manage to communicate your point without sounding as negative.
In my case, I work at an agency - so our choice of database is in some ways driven by more than just the best tool for the job, but one that inspires our clients' trust AND is cost effective to work with. MSSQL does an excellent job of "inspiring trust" but it is also amazingly easy to develop for.
Our clients aren't worried about "overkill" or buying more database than they need. In the scope of a multi-million dollar project, the overpowered database is worth the piece of mind.
None of us on this end are "married" to SQL SERVER, but since we already own the license for one, and offer shared hosting of the database server or give a pass-through-cost for a dedicated server, it seems like a perfectly reasonable choice.
But we've both got our perspective and yours offers a good perspective. "As a DBA, SQLSERVER is often overkill" but if the client prioritises stability, recover-ability, maintainability, over cost (a "one time cost" of roughly 25% of one employee's salary) & two hours of downtime a week it's not such a bad choice.
Normally you run SQL server in some kind of cluster, so you flip the cluster to run off the other server, do your updates, then flip back. This is fully automated for us.
Seamless from user perspective.
I assume other database products require downtime for updates, and use similar strategies to avoid user issues.
Seamless from user perspective.
I assume other database products require downtime for updates, and use similar strategies to avoid user issues.
> you need to reboot on install, updates and uninstall
I think you're missing the point of having this in a Docker container.
I think you're missing the point of having this in a Docker container.
Not really. This is why I read the post. But the comments aren't discussing the container but the SQL being better or not than something else.
Docker would help my case if it wasn't required to run on Windows Server! This could be an interesting solution if it worked on Windows 7 and Windows 10.
But even then our legacy code would require some changes to be able to communicate with it.
So, psql, mysql, sqlite or no matter what is better in any aspect for our point of view.
Docker would help my case if it wasn't required to run on Windows Server! This could be an interesting solution if it worked on Windows 7 and Windows 10.
But even then our legacy code would require some changes to be able to communicate with it.
So, psql, mysql, sqlite or no matter what is better in any aspect for our point of view.
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.
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.
Alas its a prereq for those items you listed as being wonderful. Cross platform VS can't happen soon enough.
"SQL Server that you can't do with Postgresql"
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.
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.
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 haven't used it much on the server side, so I won't comment on that part.
Printer drivers can still crash Windows, and if you screw them up, you can be force to just reinstall. Javascript in Firefox managed to crash a display driver earlier to day. Still Windows is "okay", in terms of stability, but if something goes wrong, like yesterday when my wireless NIC stopped working, there's absolutely no help from Windows in terms of figuring out why. Logging is pretty much non-existent, it's like arguing with a wacko girlfriend who's mad at you, but won't tell you why.
Really the interface is the major pain point for me. It simply feels clunky and slow. There's a serious lack of consistency across the UI. Windows 10 is really bad about this, having two control panels for instance, and still being part metro, part Windows XP. Just the whole filesystem layout of the C:\Users\<username> is weird. It's as if Windows have gone to create lengths to hide the "home folder", for no apparent reason. Generally the filesystem is just weird and confusing. Search rarely work. The "Ubuntu on Windows" does fix some of my issues though, because having a modern operating system, and no "Unix layer" is a hindrance, to me at least.
If there's a point to my rambling it's something along the line that Windows is missing an overall strategy. Currently it sits in a weird spot between being for the absolute novice, and the computer expert, while fulfilling neither role.
Printer drivers can still crash Windows, and if you screw them up, you can be force to just reinstall. Javascript in Firefox managed to crash a display driver earlier to day. Still Windows is "okay", in terms of stability, but if something goes wrong, like yesterday when my wireless NIC stopped working, there's absolutely no help from Windows in terms of figuring out why. Logging is pretty much non-existent, it's like arguing with a wacko girlfriend who's mad at you, but won't tell you why.
Really the interface is the major pain point for me. It simply feels clunky and slow. There's a serious lack of consistency across the UI. Windows 10 is really bad about this, having two control panels for instance, and still being part metro, part Windows XP. Just the whole filesystem layout of the C:\Users\<username> is weird. It's as if Windows have gone to create lengths to hide the "home folder", for no apparent reason. Generally the filesystem is just weird and confusing. Search rarely work. The "Ubuntu on Windows" does fix some of my issues though, because having a modern operating system, and no "Unix layer" is a hindrance, to me at least.
If there's a point to my rambling it's something along the line that Windows is missing an overall strategy. Currently it sits in a weird spot between being for the absolute novice, and the computer expert, while fulfilling neither role.
> Just the whole filesystem layout of the C:\Users\<username> is weird. It's as if Windows have gone to create lengths to hide the "home folder", for no apparent reason.
I don't understand this. It seems less hidden than C:\Documents And Settings\<username>. In fact, it seems fairly prominent to me. Where could they put it that would be less hidden?
I don't understand this. It seems less hidden than C:\Documents And Settings\<username>. In fact, it seems fairly prominent to me. Where could they put it that would be less hidden?
As a developer, using Windows for anything that isn't Visual Studio related can be kind of painful compared to Unix-family systems, especially in the open source world.
Getting a decent command line, until very recently, required awkward work-arounds like Cygwin, and a lot of common dev tools tend to feel like second-class citizens. Package/dependency management just wasn't there, though I hear they're starting to fix this.
Getting a decent command line, until very recently, required awkward work-arounds like Cygwin, and a lot of common dev tools tend to feel like second-class citizens. Package/dependency management just wasn't there, though I hear they're starting to fix this.
Aside from having to reboot to apply some patches and the cost, Windows Server runs fine as a server if you're running an all or mostly MS stack. TBH, some things about Windows Server I like considerably more (e.g. performance counters, the event logging system, PowerShell) than the equivalents on Linux.
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.
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 think core based licensing is reasonable for scaling.
Depends what you're doing and what your needs are. If we have one big database sitting in a data center and it would be beneficial to setup a read replica for other workloads, suddenly that's a $150,000 suggestion instead of a "We have this server over here".
Want a couple? $300,000.
Big client that you'd like to isolate on their own hardware? $$$$
Want a couple? $300,000.
Big client that you'd like to isolate on their own hardware? $$$$
What sort of machine are you running to get 150000 USD?
It's approximately 3-4 k per core using standard.
It's approximately 3-4 k per core using standard.
Enterprise on 16 core machine. $14k / core so it's actually closer to $220,000.
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.
Sure. But open source business models are not trivial to succeed or scale. RethinkDB is shutting down and MySQL ended up in Oracle's hands. Postgres is a bit unique, but still lacks good and friendly developer and management tools and better support from cloud providers.
As a counterpoint, we have a 2008 R2 instance that cost us about $13K back in 2011. Granted, it's a two processor, standard edition license, so we recently upgraded to SSD's and upgraded to higher core processors. Also, the Windows Server license was about $800, I believe. No CALs required for either Windows or SQL Server in this situation.
>No CALs required for either Windows or SQL Server in this situation.
Either you have Enterprise Windows (costs about as much as professional with the CALS), or the software you are running is only local to that server. The Microsoft treadmill is expensive.
Either you have Enterprise Windows (costs about as much as professional with the CALS), or the software you are running is only local to that server. The Microsoft treadmill is expensive.
[deleted]
There's a special web licence for website, which doesn't use CALS.
If you building some internal business app, then you need CALS.
If you building some internal business app, then you need CALS.
> There's a special web licence for website, which doesn't use CALS.
Special licenses for various uses cases is another big plus for FOSS/PostgreSQL. Life is easier when adding database nodes or spinning up a dev/qa database is purely a technical issue.
Special licenses for various uses cases is another big plus for FOSS/PostgreSQL. Life is easier when adding database nodes or spinning up a dev/qa database is purely a technical issue.
If you are taking about data center, that's 50k. Cheaper, but I doubt it add's 50k of value over the alternatives for most users.
"Windows Server 2012 R2 configured to run Web Workloads do not require CALs or External Connectors. Web workloads, also referred to as an internet web solution, are publically accessible (e.g. accessible outside of the firewall) and consist only of web pages, web sites, web applications, web services, and/or POP3 mail serving. Access to content, information, and/or applications within the internet web solution must be publically accessible. In other words, they cannot be restricted to you or your affiliate’s employees. "
SQL server != Windows Server
That also appears to only cover web servers that do not have any login capability. Why on earth would you spend that kind of money to host a static site?
That also appears to only cover web servers that do not have any login capability. Why on earth would you spend that kind of money to host a static site?
SQL sever doesn't have a "Data center" license.
You would license SQL sever on a core based licensing model, which doesn't use CAL's.
If anyone can purchase a login to your website, that's still classed as public. What its means is internal apps require licenses.
You would license SQL sever on a core based licensing model, which doesn't use CAL's.
If anyone can purchase a login to your website, that's still classed as public. What its means is internal apps require licenses.
My whole point has been that Microsoft solutions are expensive. Is 6.5K per core cheap to you?[0]
[0]https://www.microsoft.com/en-us/cloud-platform/sql-server-pr...
[0]https://www.microsoft.com/en-us/cloud-platform/sql-server-pr...
Relative to salaries required to get similar features setup on open source databases with support, yes.
EnterpriseDB which is basically support for PostGres is almost as expensive.
EnterpriseDB which is basically support for PostGres is almost as expensive.
[deleted]
It's for services available to the public. So if you wanted to build a site that required payment to sign up you could do so.
Do you have a lot of SQL servers? If not, why not just get proc licenses?
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.
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.
I'm not convinced anyone would use express for production purposes anyway due to the fairly severe memory/thread limitations.
You need Windows Server don't you? I can't imagine that many developers running Windows Server on their development machines.
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).
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).
How many users does your site have? Did you have problems with the limitation of RAM?
We've got 10-12 users connected to a SBS 2013 server with a couple of RAIDed HDDs and 8GB RAM, and - yes - the SQL Server service pegs out at the maximum allowed RAM (which I think is 1GB or so) pretty much the whole day. However the performance is deemed much more than acceptable, even though no doubt a lot of query results are getting booted out of RAM way too soon.
We've still got a trick up our sleeves in terms of performance boosting by moving the server to SSDs instead of magnetic drives.
FYI all my other clients running 'Express' consist of small companies with 10 or fewer users, and they are more than happy with performance (or at least aren't willing to pay the large MS fees for a 'proper' version of MS SQL Server).
We've still got a trick up our sleeves in terms of performance boosting by moving the server to SSDs instead of magnetic drives.
FYI all my other clients running 'Express' consist of small companies with 10 or fewer users, and they are more than happy with performance (or at least aren't willing to pay the large MS fees for a 'proper' version of MS SQL Server).
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.
Useful for developers targeting organisations on Microsoft stacks
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.
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.
Be a hero and free your company from lock in, otherwise you'll be the next person referred to as "the predeccessor [who] fell for lock in".
[deleted]
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.
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
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
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.
SQL Server Management Studio is light years ahead of any pgsql management tool
The official pg client, psql, is light years ahead of SQL Server Management "Studio".
We can rehash all the same old arguments from the 90s and 00s about command line vs gui but that would not be productive. If you want to interact with mssql using the command line, use sqlcmd
or pgcli for that matter
The editor in SSMS can do everything pgcli can do and more.
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.
Most of those SQL server can do. There isn't really a need for VACUUM on SQL sever.
What about true MVCC?
READ_COMMITTED_SNAPSHOT?
It uses tempdb for tracking versions, it is not mvcc in the core
https://news.ycombinator.com/item?id=12693127
https://news.ycombinator.com/item?id=12693127
Just because it does it in tempdb, doesn't mean its not MVCC. That's an implementation detail.
[deleted]
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, 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...
Can you actually setup multiple SQL Server 2016 Express's in a cluster? That'd be interesting given that it's free, I wonder what the limitations are?
You have to have the Enterprise Edition to setup a cluster and then it starts to get expensive.
Not quite - you can cluster essentially every edition of SQL 2016 except Express Edition.
You can't. I think they mean docker clusters of some kind.
Security? Last time I tried, Docker messed up my firewall beyond control. Definitely not more secure than a VM.
Security will be awful. I'm an SS DBA.
For one they seem to use the single known account (sa) which we disable EVERYWHERE because it's the first biggest known most common target of attack. In fact it's dangerous to use because it will lock randomly when anyone inside the company or elsewhere runs a scan and attempts a few passwords on it.
Secondly SS is made to be patched. We get a new patch every single fucking month and sometimes twice a month. I spend 4 days per month simple patching SQL across half a thousand servers. This shit will never see it. Is this patchable? Not by any standard tools.
Is this even discoverable? Fuck no. Nobody will know it exists. We don't do port scans - we look for known SQL services and use the SMO WMI ManagedComputer tools to identify them. This will work with neither.
I've seen what developers do to their environments. Hundreds of copies of their databases. No backups (so logs grow and fill the disks and then I get called). No maintenance. Of course Express has no job scheduler so you're fucked anyway which is why I don't support it and instead move everyone onto Developer edition WHICH IS FREE.
Anyone using this shit is on their own.
For one they seem to use the single known account (sa) which we disable EVERYWHERE because it's the first biggest known most common target of attack. In fact it's dangerous to use because it will lock randomly when anyone inside the company or elsewhere runs a scan and attempts a few passwords on it.
Secondly SS is made to be patched. We get a new patch every single fucking month and sometimes twice a month. I spend 4 days per month simple patching SQL across half a thousand servers. This shit will never see it. Is this patchable? Not by any standard tools.
Is this even discoverable? Fuck no. Nobody will know it exists. We don't do port scans - we look for known SQL services and use the SMO WMI ManagedComputer tools to identify them. This will work with neither.
I've seen what developers do to their environments. Hundreds of copies of their databases. No backups (so logs grow and fill the disks and then I get called). No maintenance. Of course Express has no job scheduler so you're fucked anyway which is why I don't support it and instead move everyone onto Developer edition WHICH IS FREE.
Anyone using this shit is on their own.
The image will be updated with latest patches, i don't how you would update an existing container though.
- 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.
- 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?
- 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?
Consistent environments - Yes VMs and Containers can be equivalent, but the lightweight nature of the container makes it easier to run consistent laptop/dev/test/prod
Security & Flexible Isolation - I can easily share volumes, link and build overlay networks between containers whilst also keeping everything else namespaced and isolated. containers also isolate processes from each other and allow us to control resources granted to processes (cgroups) which is a security benefit over vm
Clustering - Yes SQL server will cluster, but in a container orchestrator we will get more control of moving processes between nodes to scale, to cover failure scenarios or maximise resource usage of the underlying hosts
Standard Image - You would deploy SQL server container but keep data outside as a volume. Maybe devs would populate the database from a script or network share, or yes join a cluster.
Security & Flexible Isolation - I can easily share volumes, link and build overlay networks between containers whilst also keeping everything else namespaced and isolated. containers also isolate processes from each other and allow us to control resources granted to processes (cgroups) which is a security benefit over vm
Clustering - Yes SQL server will cluster, but in a container orchestrator we will get more control of moving processes between nodes to scale, to cover failure scenarios or maximise resource usage of the underlying hosts
Standard Image - You would deploy SQL server container but keep data outside as a volume. Maybe devs would populate the database from a script or network share, or yes join a cluster.
IIRC Containers in windows are full hyperv containers, so you do get full vm isolation[0].
I would imagine that this would be much more for testing, as it allows for an isolated, empty db for each load. Beyond that, if you're doing a completely containerized infrastructure. Though I would submit that if you're doing windows containers, odds are you're either internally publishing or publishing to azure, so for production, dedicated SQL server or azure sql would likely be better options.
[0] https://msdn.microsoft.com/en-us/virtualization/windowsconta...
I would imagine that this would be much more for testing, as it allows for an isolated, empty db for each load. Beyond that, if you're doing a completely containerized infrastructure. Though I would submit that if you're doing windows containers, odds are you're either internally publishing or publishing to azure, so for production, dedicated SQL server or azure sql would likely be better options.
[0] https://msdn.microsoft.com/en-us/virtualization/windowsconta...
FYI you can choose to use Hyper-V containers (in which case, yes, docker is effectively orchestrating complete VM's) or use the new Container implementation, which is basically filesystem and registry isolation
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.
And that is a much more likely developer-setup.
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.
Multiple copies of a large base db, for free...you only pay for the data when the differences start to flow in.
But isn't it because of the drive level deduplication of data? Surely this is not docket specific?
I don't understand your question. My point is you can bake an image with the db of your choice, lets say that comes in at 50Gb. You can then run multiple instance of that image, for only 50Gb plus watever data differences are, as opposed to having to copy the full image and launch X vms (this is a scenario i am trying now, the size is much greater though, so it makes sense)
I dont know of any hypervisors that let you do multiple instance of the same vm using diff disks?
I dont know of any hypervisors that let you do multiple instance of the same vm using diff disks?
You can do that at the storage server level:
https://technet.microsoft.com/en-us/library/hh831602(v=ws.11...
https://technet.microsoft.com/en-us/library/hh831602(v=ws.11...
Right didnt know that had found its way into standard Windows (as opposed to being a storage sapces feature or some such). In any case, its not really equivalent though, since it is only run periodically. My use case is specifically dev focused, wheras i would certainly consider data dedup on something less ephemeral
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!
From their latest newsletters it appears that they intensified cooperation with MS. Hope it does a good turn!
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.
Good for development environments though. Probably don't want to be running SQL Express for large databases anyway.
It's limited to 10GB; so even if you wanted to, it won't :)
Trivial to sub the express edition for developer edition and you are away
You know that's not allowed, right? Dev can only be used locally on your own machine, never to run anything other people can access.
Yes, why else would you be running SQL in a container for if not for dev....10GB is fuck all, and it's nice having multiple versions of a large test (or sanitized prod) backup available locally, which can easily be greater than 10GB
"fuck all" - Absolutely nothing at all, the most extreme degree of nothing.
You can only have a maximum 10 gb database on SQL Express, no point doing any of those things with that.
I think 10gb is quite a lot of data for any database system, well at least it used to be!
Even a one man band can easily afford a dedicated server to keep an entire 10Gb db in memory at all times.
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 :)
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.
Is express still limited to 10gb databases?