Introducing SQL Server 2016….
It case you have missed all the “data drivel”, sorry “data driven” marketing around the release of SQL Server 2016, Microsoft’s latest offering is available to buy on 1st June. There is already a lot of information available around the new version but it’s either marketing hype (picture fast blue car overtaking slightly slower (Oracle) red car) or a propeller headed in depth technical analysis. I wanted to try and have a stab at summarising what’s changing for the less technical person that wants to get underneath the hyperbole.
Hype aside, SQL Server 2016 looks like it’s going to be a significant release and it seems like Microsoft are really defending their territory against a wave of new database technologies, stepping up to match Oracle for a number of enterprise features and starting to flex their cloud muscle. Here’s a quick rundown of the key points, note I have flagged which editions (Standard or Enterprise) benefit from the feature:
Stretch Database: This new feature is really aimed at customers with millions/ billions of rows in their tables and the best use case is for archiving cold data. At a granular level (row level) you can pick the data that is infrequently used and farm it out to Azure, it’s a very simple wizard that handles the migration of data in the background. Therefore, if you are considering a blended storage strategy for your database which includes archiving this could be a good fit. You’ll need to buy a compute node on Azure (from £568 per month) and then the great news is you only need the RA-GRS blob storage which is £0.0229/GB/ Month, there are some outbound data transfer costs but if the data isn’t requested very often this should be minimal. As this is all handled natively in SQL Server the application doesn’t need to worry that some of the data isn’t held locally, you can’t beat the speed of light so there will be some network latency but the integration is very impressive. (STD/ ENT)
A new BAG: Basic Availability Groups makes an entrance for Standard edition replacing Database Mirroring. BAG is a bit of a taster for AAG with a couple of restrictions namely, you can’t read from the second node and it only covers one database. Having done a bit of testing it looks like you can create multiple BAG’s but it may end up a bit messy. It will prove useful in a few use cases, but I don’t think will help too many customers who buy Enterprise for just the AAG functionality (which is greatly improved in 2016) and run multiple databases on an instance. (STD)
Vastly improved security: A big improvement area in Standard Edition are two security features called Data Masking and Row Level Security. They provide a lot more control around user’s ability to view data, Data Masking will obfuscate data at table or column level so the query data remains protected from prying eyes (useful for credit cards or personal data). Row Level Security provides the access policy at a row level around which user can see what data, this will be very useful to prevent human error especially in SaaS environments where you need to be very vigilant with multiple customers. The killer Enterprise feature is Always Encryption which builds on the previous TDE feature to ensure data is encrypted in memory and in transit, moving to a model whereby the encryption keys stay on the application “trusted” side.
RIP BI Edition: It looks like Business Intelligence edition has been removed therefore, if you are currently using this and have a CAL based agreement I would check with your Microsoft rep around your options. There will be an upgrade path but you will more than likely need to move up to Enterprise. Therefore, it may be worth bagging a few extra CALs over the next couple of weeks for any growth requirements in your current financial year.
Happy Developers: One of the biggest requested features for 2016 was JSON support. With more and more development teams using object orientated languages you may have heard the moans around all the extra pain working with a RDBMS system and a business case for NoSQL may have even landed on your desk. Microsoft hasn’t gone the whole hog, they haven’t provide a specific JSON data type as it’s represented as a NVARCHAR type, but it’s a step in the right direction and will certainly help development teams. (STD/ENT)
Happy Analysts: Your analysts and data scientists can roll around in a grassy meadow full of wonderful new and improved integration, topped off with improved in-memory analytics. Support for R will be well received by the rapidly growing user community and improved Polybase provides the ability to use T-SQL queries to run against data stores such as Hadoop. With the demand especially in industries such as Retail and Finance for real time analytics to either send a product offer in real time whilst you are shopping or detect fraud, the enhanced in-memory capability provides more scope to keep a larger working set in RAM minimising query time significantly. Microsoft are even questioning the need in some use cases for a separate data warehouse, it seems all these new features may reflect a trend for using Hadoop for Offline Analytics and a RAM heavy database for Real time. (Largely ENT, Basic R integration in STD).
Free Licences: If you are an unhappy Oracle customer and are thinking of moving to SQL Server, then you need to get your skates on as Microsoft are offering free licences until the end of June (may be extended). There are a few stipulations i) There is a 50 core minimum that you’ll need to agree to for SQL Server, this can be phased though over time; ii) you do need to buy software assurance for the free licences and iii) you’ll need to sign up to a Server Cloud Enrolment (SCE) agreement more information on the offer can be found here.
I have picked out the main points there are more, but hopefully it gives you a flavour of what’s coming in SQL Server 2016.