Encryption is easier to manage and it has built in solutions for HIPPA and PCI.
SQL reporting services and the entire ecosystem of apps that run on top of MSSQL..
Better clustering, ha, revision control and backup.
Most likely better enterprise support, and you'll be able to port "legacy" applications that were designed with win/sql in mind without having to rewrite your entire DB access and query code.
What? HIPPA, point by point [0].
- Do periodic risk assessments and be able to produce the reports if demanded.
- Have a designated HIPAA compliance officer.
- Have a security policy, train employees on it, and ensure it has sufficient teeth to terminate violators.
- Follow the principle of least privilege when granting access to PHI.
- Limit physical access to facilities.
- No shared accounts, and lock workstations when idle.
- Encrypt data at rest and in transit.
- Log data access.
- Protect data against corruption.
I suppose an RDBMS can help protect data integrity through foreign key constraints and other validation tactics, but the rest of this can't really be performed at database engine layer (unless your users are logging in to MSSQL directly?) so much as the EMR application and the physical, config management (full disk encryption + screensaver password), and bureaucratic environment it's deployed in.
[0] https://www.hhs.gov/hipaa/for-professionals/security/laws-re...
> - Log data access.
> - Protect data against corruption.
It's these three that I imagine MSSQL can help with. I believe it's got a built in storage encryption setup for tables/databases and can definitely be setup to log access to the database and queries run against HIPAA data.
All the key management, split knowledge, key rotation and most importantly access management and a full audit trail are also a "few clicks" away.
Having a turn key solution for one of the most annoying things to implement in a correct manner should not be under-estimated.
Anyone who done any compliance work knows just how many weirdly stitched solutions you can have.
Something along the lines of "we manage split knowledge to our keys by splitting the key and keepass file into 2 files and storing them on USB keys in separate vaults"....
Now technically this can pass a compliance check but this is a pretty darn silly situation simply because there isn't any better solution that works out of the box, this also quite often means that anytime you restart your DB you have an entire process of getting the keys, decrypting the tables copying their content into temporary views, re-encrypting the files and remove the keys from the server..
For MSSQL it wasn't that long ago that you have to do it "by hand" also, usually have a stored procedure that would handle the encryption and have a key which is then encrypted by several KEK's whilst it did afford some split knowledge it wasn't a true m of n which also meant that if you had more than 2 DBA's you had to make sure that the DBA's are know all of the KEK's and not be in a situation where all 3 DBA's that are not off during that week know the exact same password for the KEK...
FDE won't even work for data at rest unless you can do split keys and auditing.
This isn't about security this is about complience.
But in general again this isn't about security it's about complience so it's more about doing things by the book and a very specific one.
Edit: I am not familar with much of anything microsoft related.
Windows automation kinda died when system admins stopped needing to know how to work with JET DB when needing to do one serious ad work.
I don't can't think of a single CI or automation thing that works with Linux that you can't do in Windows if pushed I can probably come with considerably more things which are opposite.
Licensing is the only thing really that makes windows machines a pain to work with unless you are big enough for a sell reporting volume licensing scheme.
> It's really not, it's that most devops engineers don't want to learn or even bother and most windows sysadmins simply don't know how.
From a hiring/project/management perspective, this is my definition of "hard".
From a sysadmin perspective, a huge problem is the amount of knowledge needed to be an effective windows sysadmin absolutely dwarfs -- by several orders of magnitude -- the amount of knowledge needed to be effective on Linux.
How many sysadmins have you heard suggest, with all credulity that the solution is downtime and a reinstall on Windows? on Linux? Once a sysadmin knows how to move files around, run strace and grep, there is very little hidden from them on Linux, while on Windows they still don't even know how to take a backup, which is helped less by Microsoft refusing to publish any documentation that makes them look stupid -- like it being impossible to take an on-line backup of a server running Microsoft Exchange 2007 prior to SP2.
> I don't can't think of a single CI or automation thing that works with Linux that you can't do in Windows if pushed I can probably come with considerably more things which are opposite.
I agree wholeheartedly with that statement.
I hope that UNIX sysadmins want to learn, and bother to learn well the Windows environment better: UNIX sysadmins already know how a system is supposed to work, and it is by large the sheer number of inexperienced Windows sysadmins that make Windows look stupid. UNIX sysadmins have a better chance of running Windows well simply because they won't put up with "have you tried reinstalling everything"?
There's also some really good ideas in the Windows ecosystem that Linux (and even OSX) could really benefit from.
> Licensing is the only thing really that makes windows machines a pain to work with unless you are big enough for a sell reporting volume licensing scheme.
An aside: I'm not so sure this is true anymore, at least not with Azure for infrastructure, and Open programs for desktops/laptops.
I agree wholeheartedly with that statement.
I hope that UNIX sysadmins want to learn, and bother to learn well the Windows environment better: UNIX sysadmins already know how a system is supposed to work, and it is by large the sheer number of inexperienced Windows sysadmins that make Windows look stupid. UNIX sysadmins have a better chance of running Windows well simply because they won't put up with "have you tried reinstalling everything"?"
Powershell is the key that unlocks Windows administration, I think. Certainly, as somebody that does both platforms, learning Powershell made a big difference to how I work and think when using Windows.
Having said that, Windows Server is still fundamentally bad. The logging is a mess, remote access is awkward compared to SSH on Linux, the failure to properly integrate package management hurts every day, the Registry is still...there, and the list goes on. The disparity of DevOps tooling is another issue. I would agree with the comment that automating Windows takes several times as long as Linux. It's just not happy fun.
At this point, Windows is probably not retrievable. The Windows Server folks will keep improving it bit by bit, but I can't see an argument for continuing to flog the horse and use Windows Server outside of full-dress Microsoft corporate environments.
Also wmi always worked and still does.
Logging is great you just need to configure it and register your own triggers and monitors really depends on what you want to log.
It's very easy to do logging of every action and even completely custom and send it to the event log, you can also register custom events if you want to add your own application logging or use the stubs and just push the information into the text fields.
Again this simply comes to 2 things tooling and knowing windows internals well, sadly because there are usually very little negative side effects to rebooting a windows machine and that the market is flooded GUI administrators with mcsa/mcse certs gained through memorizing exam dumps it's hard to find people that know or want to know how to administrate windows machines to their full potential.
To me, that's sort of where Windows is now. It has some really impressive stuff: Windows 2000 was an avalanche of innovations, but most of it was built for the corporate LANs of a decade ago. Put Windows Server on a cloud, and most of this stuff is not relevant or useful, and Windows is not great at some fundamentals.
Logging is one of those. If you are interested in this kind of thing, I'd genuinely encourage you to spin up a Fedora or Debian system and look at the thing work. We are currently in transition between logging systems, but the feature sets are impressive and the error messages are useful. Windows logging is even more patchwork, and the (Microsoft) products that bother with the core logging system tend to fill it with chaff. It might get better, one day, but it's not a good experience today, and hasn't ever been.
Another is software management. Package-based software management has to be deeply integrated into the OS for it to provide it's full value. Debian have done this for years, and Red Hat have caught up. Microsoft have a muddle of multiple software management systems, and individual admins can't fix it. We just have to work around it and do the best that we can, or decide that the platform is what it is, and move on.
In an ideal world you wouldn't have to deal with 32-bit versions of Windows, or EOL'd 64-bit versions. The real world with lots of legacy stuff doesn't work that way. Meanwhile EOL'd versions of Linux chugged away just fine.
Iteration on the Windows instances was also quite a bit slower than with the Linux ones. Applying all the requisite compliance stuff resulted in a number of additional reboot cycles. I could get a Linux instance bootstrapped in half the time, without having to bake a custom image.
This is a tooling issue, dont expect to use a tool that was hacked together for all intents a purposes rather than a native solution and get good results.
https://www.reddit.com/r/sysadmin/comments/21z62d/sccm_2012_...
And this:
https://www.windows-noob.com/forums/topic/9357-how-can-you-m...
And I'm seeing three things:
1.) SCCM is not nearly as easy to automate as the alternatives.
2.) SCCM is expensive.
3.) SCCM has mediocre support for Linux.
And there ya go. Meanwhile things like Ansible, Chef, and Puppet can handle your Linux, BSD, and OSX servers with aplomb. Where Ansible, Chef, and Puppet use mature, cross-platform scripting languages SCCM uses Powershell. Windows is the odd man out here, and skills from nearly every other OS don't really transfer well to dealing with Windows. That's why Windows is seen as more tedious.
And, sure, our Windows guys were dropping to Powershell constantly from their Chef cookbooks. But then you get to deal with maintaining PS 2 and PS 4 scripts. The versions of Ruby and Python you'll find with Ansible/Chef/Puppet are significantly better with backwards compatibility in this regard.
But, let's say that we had bought into SCCM. That doesn't really fix the problems with the other parts of the stack, nor does it fix the inconsistencies across Windows versions.
And you got this from what exactly?
Windows does automation just fine, you are throwing this word around without even trying to explain what is it you want to automate.
SCCM has ok to decent support for Linux; again depending on your use cases and distro.
I don't understand why are you even bringing random Reddit and forum posts for a 5 year old version of SCCM.
SCCM isn't expensive it's usually "free" when you buy enough licenses.
Also you missed a very critical point; Chef, Puppet and Ansible can invoke the SCCM agent.
Their built-in agents for Windows suck, so you either write your own automation (e.g. Powershell) and invoke it via Chef/Puppet/w/e or you use a decent agent that was actually built for purpose.
You are constantly complaining about things that at the end stem from a single source; incorrect tooling.
Also tools like puppet and chef for example have an easier time provisioning Unix like machines than Windows (although this has been changing, and the situation could be much better I haven't dealt with it for a bit).
Also you don't have to deal with licenses for Windows server instances which can be another huge painpoint.
Oracle support is being dropped left and right MSSQL is there because it was the defacto enterprise DB for ages and it also has a free version via MSSQL express.
And lastly having more options is never a bad thing.
By whom? I think "big" enterprise software (SAP, Peoplesoft, etc.) has and will support Oracle for a long time. Many of their customers have huge investments in Oracle, not only financial but training and institutional knowledge.
Are there? I'm genuinely curious about what these products, are that run on Linux and use SQL Server.
Although if you're virtualizing Windows Server, it may be effectively free to run an additional Windows Server instance (since you pay per core regardless); but if your hypervisor's resources are already saturated then you could take SQL Server off of it and put it onto a inexpensive Linux Server.
Against which other product did you compare against? There are many database systems supporting Linux. IBM DB2, Oracle Database, to mention a few.
Anyone who has to do that is doing (or did, in the past) something seriously wrong. For me, the biggest reason not to change database after you accumulate a couple dozen terabytes of data is the inconvenience of dumping and restoring it and the downtime it'll cause.
Publishing that on a blog somewhere would make things easier for a lot of people. Have you considered it?
That's the argument against those things - it makes it difficult to switch your database. But there are also some compelling performance arguments in some cases, so in a well-designed system those parts of the code that live in the DB also tend to be important ones.
But if they only exist for the critical parts then porting is a lot easier.
In this case, it's still wrong, but faster than doing it right.
In fact, even SQL Server Management Studio is a Windows-only application.
The app stack is usually more platform agnostic than you think. Even if it's asp.net you most likely going to be able to run it or easily port it to .net core.
Basically having cross platform .net is pointless without having cross platform everything else especially MSSQL.
I've moved more away from the MS environments the past few years now, but have missed some of the things that MSSQL makes very easy, and by comparison PostgreSQL makes relatively hard. Of course you're paying for it. Having the option to run under Linux is really nice though imho.
Could you give some examples?
IIRC geo indexing was easier in mssql, although much more configurable in pgsql.
Not on pg, but linq to entities for .net is really nice, and the pg adapter (iirc) wasn't nearly as good. The node adapters for each are about equal though.
Out of the box performance on mssql, and query performance in general was better last time I compared (admittedly about 2 years ago).
The installs are on separate ISO's now if I'm not mistaken...
https://marketplace.visualstudio.com/items?itemName=ms-mssql...
Still missing several things that SSMS has, but it's a nice start!