So I'm looking to buy/scrap together a server for PLM (Product Lifecycle Management) and PDM (Product Data Management) for my University's Formula SAE team (Engineering competition to design and build a racecar). For those not familiar, PLM and PDM is data storage management. The server will be running Windows Server with SQL server and Aras PLM. After looking through the Aras documentation, it looks like our workload won't be very bad at all compared to what the software is built for. They have examples of single server systems designed for 25,000 concurrent users. We'll have 100 at absolute max, normally closer to around 20. All we're really wanting is reliability, not performance.
Since we're students, we don't have the funds to shell out for a brand new system. We do however have access to our University's Surplus area where other departments drop off old gear they don't want anymore (some of which is in perfectly functioning order).
What are the opinions for using used hardware for an application where downtime is a very bad thing (ie. it's not a sandbox for experimenting)? I would guess there are somethings that would be recommended to be newer than others (like harddrives).
Any opinions on reburbished hard drives? I'm not super familiar with enterprise gear, but wouldn't having all the drives in RAID (or ZFS or whatever) eliminate any issues with unreliable hard drives?
As long as the hardware has enough power to meet the needs (which seems very possible with your use case) I think it should not be a problem. Just take proper precautions. If you can get the hardware cheap account for extra drives and bays. This way you can spend extra disk space for mirroring and assign a couple of hot spares. Also store some extra drives and maybe a couple of PSUs near the physical server so they can be hotswapped easily.
Hell, if the hardware is cheap enough you could pick up more of the same servers to use as fail-over or for spare parts.
Could you give us an idea of the hardware the surplus has in stock?
I wish. So how the Surplus essentially works is people at the university drop off stuff they don't want. It then goes into the storage place where it sits for people to claim. What isn't claimed internally then goes to the State (we're a public university) and they decide whether they want it. If no one wants it, it gets auctioned to the public.
The last part of the cycle (State and public auction) happens every 3 or so months, so the turn around is really high. I've seen computers from the 90's turned in all the way up to $2000, 2 year old PA systems that were in perfect working order (if you open up the speaker and plug the obviously purposfully unpluged internal power connector).
With regards to an on premise deployment (I assume you get free MS Licenses?), SQL Server 2016 can directly use data-drives that are in an ReFS pool, smb shares hosted on another server, or VHDX virtual disks hosted in Hyper-V that are in an ReFS pool. You can also build Highly Available SQL Server Clusters that are on physical servers or VM's. If you protect your database with a synchronous Availability Group you get Automatic Page Repair for corrupt data-pages in the database.
Lot's of options, but if you can't use MS products due to licence restrictions etc. then building a PostgreSQL cluster on Linux or FreeBSD would be my recommendation.
That's not a bad idea at all. Aras offers assistance with making a cloud based solution, so that might be exacly what we need. Plus all the software licenses (besides Aras) is going through Microsoft student stuff (DreamSpark), so they might be able to lend us a hand with Azure. I'd still like to have local backup though just in case. Plus I want data access in case internet goes down (been known to happen).
You are speaking well above my knowledge base here. lol. I'm just a Mechanical Engineering student who likes computers. Seems like you are suggesting ways to get around using Microsoft Server OS and SQL Server? I'm covered on the software side of things with DreamSpark and Aras Edcuation sponorship. If you're talking about something else though, could you try rephrasing? lol
Don't do it. Refurbished hard drives are one of those things. They are mechanical, and have a finite life.
Can your university provision a VM for you? A VM and a bit of SQL space? That could leverage their existing licensing and do the least damage. That's the cheapest way to do it, and what else is their infrastructure for.
Or maybe hit up a hosted services company to see if they'd donate the VMs to you for the duration of your project in exchange for a little advertising?
With Windows Server & SQL Server you have different options available to build a resilient data-platform to host your databases. At a basic level this breaks down into;
Use a Hyper-V cluster to host a VM containing SQL Server that can restarted on a separate host.
Build a 2-node Windows Server Failover Cluster with shared storage (assumption that you have a SAN, NAS or Cluster Shared Volumes available) that can host a Fail-over Clustered Instance of SQL Server that restart on a separate host.
Build a 2-node Windows Server Failover Cluster without shared storage to host a replica databases that you can failover to using SQL Servers Availability Groups feature.
Build 2 separate SQL Server's and set-up Log-Shipping to keep a copy of the database on the second server.
You can also combine some or all of the above to increase protection e.g. use Virtual Machines to host a cluster with Availability Groups, but it starts getting more complicated...
There is stacks of info out there about all these methods to protect a SQL Server database.
ReFS is Microsoft's newest file-system, it's nowhere near as fully featured as ZFS but can provide protection against corruption and also supports hosting virtual hard disks for Hyper-V clusters. It's still early days using it to directly host SQL Server databases though; hopefully Microsoft might have addressed most of the bugs with Windows & SQL Server 2016...
I'll have to check with our IT to see if they can do something for us. I'll for sure need to talk to them to make sure the server is accessible to everyone on the network at a bare minimum, so I'll ask them and see if they can offer anything of interest.
If I'm understanding you right, these are just ways to backup the database? Or are these automatic fail over systems that kick in if something goes wrong, eliminating downtime? I'll definitely want to keep backups of the database (in addition to the data vault).
I'm definitely interested in that. If I had a dollar for every SolidWorks (CAD) file that's corrupted in the last three years, I could probably pay for a new server.
Also, news on the Azure thing: Thanks for pointing that out! I have a Microsoft IMAGINE account which gives me access to the Azure. Plus Aras already has a Azure Marketplace App, which saves me the headache of figuring out how to actually setup everything (theoretically anyways). Unfortunately, the wording on the descriptions for that put it out as more of a web development/coding service rather than just straight cloud compute, so I've sent them an email to try and clarify that.
That's good news on the Azure front, it might make everything easier for you.
With regards to the question;
These are ways to be able to fail-over the database, some more automatic than others with different Recovery Times that are usually dependent on how expensive your hardware & infrastructure is :-)
You should always take backups of databases regardless of RAID and Infrastructure resilience. With SQL Server the usual practice is to:
1) Run a DBCC CHECKDB 2) Execute a Full backup 3) Verify the Backup file 4) Backup the Transaction Log every 15 Minutes.
By doing this you will know you have backed up a DB without corruption, the backup file itself is valid, and you can the restore the database to within 15 minutes should something untoward happen during the working day.
For the best information on SQL Server backups/Database Maintenance Ola Hallagren is the man, and he has published a bunch of SQL scripts under the MIT licence (open-Source) to set up jobs to cover these: https://ola.hallengren.com/
If you do end up deploying SQL Server and being responsible for it Chrissy LeMaire has done a wonderful job leading the GPL licenced DBATools. Which consists of loads of useful powershell for deploying, managing and migrating SQL Server databases: https://dbatools.io/
One of things that makes SQL Server a good RDBMS platform is the amount of published information about it (despite being proprietary without access to source code) and the community of geeks all to happy to help each other out. SQLServerCentral.com has some good people lurking in the forums who can probably help you more than I can...
Thanks for the info. I'll bookmark those references for future use.
Update on the Azure front. I initially contacted the Educator Grant team who pointed me to the Imagine and Azure support teams. They came back and said the Azure through the Imagine program cannot be used for virtual machines (which is exactly what I need). :(
I sent an email back to the Educator Grant team to see if I can get something through them and also asked if there's anyone else I should contact.