MS SQL 2014 (or possibly 2012 R2) Server Build suggestions

So I have a hardware implementation meeting on Monday for deciding how we are going to setup the production servers for my company's SharePoint 2013 installation.

At the moment we are thinking of setting up a physical MS SQL 2014 server a HP Blade (I asked the project manager what the specs of the server where and he said its a big server with 256Gig Ram).

But we might end up using MS SQL 2012 R2 because our current backup software doesn't integrate with MS SQL 2014. So if we where to use MS SQL 2014 it would be harder for them to do transaction log shipping and the Data Management Team would only really guaranty the file backup of the hourly full backups of the database.
I would prefer MS SQL 2014 because it has some new features to allow running individual tables directly in ram which could improve performance.

The company is looking at upgrading or changing the backup solution they use so that we would eventualy be able to do point in time backups.

And my view on the database backups is that if we ever got to the point that we had to restore SharePoint from a database backup then we have a very serious issue and most of the time we will be using the versioning features in SharePoint to recover information any way. 

Do you have any suggestions on what would be better?

MS SQL 2014 with hourly Full backups and running tables in RAM to improve performance.

OR

MS SQL 2012 with Point in time backups?

so....given the options i would probably just push the backup system upgrade through WITH the new sql server. that said i would also look into using say mysql or some other type of sql database. i mean sqlite would be super easy to backup too. you could backup an entire database and only grab a file. but again given your options, use the newer version and push the upgrade to the backup system with it. otherwise delay the sql server until the backup system is ready. otherwise it may end up costing more to upgrade backup system AND the database

We are looking at possibly moving backups to AWS Glacier storage with Amazon, or just continuing to put in more server racks, but both would involve spending hundreds of thousands of dollar and would probably take several months to get planed approved and implemented and that is all stuff the data management team have to sort out and pay for. The project I am on is tied to funding that has to be spent before the end of the year.

Because the Database is going to be used to run SharePoint 2013 the only choices are MS SQL 2012R2 or MS SQL 2014.

We also have standards about what technologies we are allowed to use in the company, and we are only allowed Microsoft SQL or Oracle SQL for database servers.

 

 

i have looked into using AWS Glacier as well for offsite backups. but back to topic, SQL2014 will have more support long term than 2012R2.

I am thinking 2014 as well. For our backups we currently have a HP onsite cloud solution where HP has their Racks in out server rooms and we just pay HP for the storage we use, and that makes it fairly cheap. But they are having to upgrade the generators that run the buildings when the power is out and it's those costs that are making them think down sizing what is in the server rooms would be better.And they have already moved some of the web servers to EC2 servers with AWS.

 

AWS is very popular with many businesses. easily scales as needs grow. probably cheaper to move some of the servers to AWS than it is to upgrade the generators

This response is probably late to be of much use but you might find it informative anyway.

Firstly just to clarify some points;

1) There is no SQL Server 2012 R2, its just SQL Server 2012 (there is Windows Server 2012 R2 which creates the confusion)

2) There was no change how the basic user database backup mechanism works between SQL Server 2012 and  2014; they both offer FULL, DIFF and TLOG backups when running user databases under the full recovery model. Under simple recovery it is just FULL and DIFF. 

3) SQL Server 2014 offers support for in-memory tables but SharePoint 2013 does not use this feature, nor does it use Column Store Indexes.

SQL Server 2014 offers a few nice improvements over 2012 and will be supported for longer, so by default that is what you should seek to install.

The main question you need to answer is which Edition of SQL server will you be able to install? 

Most DBA's have to choose between Standard Edition and Enterprise Edition. Standard Edition is a hell of a lot cheaper to licence but does not support proper scale out or scale up functionality;  you can still build a 2 node active/passive cluster and use Logshpping or Mirroring to get full local High Availability and a warm DR server though.

SQL Server 2014 Standard Edition is limited to supporting only 16 logical processors and 128Gb Ram per instance. 2012 Standard Edition only supports 64Gb of Ram per instance.

If that is plenty of CPU and Memory grunt and a 2 node cluster offers enough local HA protection then the reasons to deploy Enterprise fall away. If however your database will be really busy and in use 24/7 then Enterprise is worth considering as the DB engine is more optimal and it also supports things like online index rebuilds.

As for backup strategy, just backup user databases to disk with compression enabled, daily FULL and hourly DIFF backups with TLog backups every 15 minutes will allow you to quickly restore to a point-in-time. You can then use the backup utility to sweep the flat files off to tape or whatever your new backup solution is.

PS Don't bother writing your own backup or maintenance jobs, download Ola Hallengrens - they are free and also support the SQL 2014 ability to backup databases direct to Azure Storage Blobs, should your company go that way instead of AWS.

https://ola.hallengren.com/

 

Regards

BGL

 

 

 

 

 

 

 

 

 

 

 

+1 this right here. Kudos. Ola Hallengrens stuff is magical. And idk why OP wouldn't run the "free" active/passive if they didn't have enterprise and needed more 'immediate' recovery.