I wrote this post a few months ago… and it crashed my blog. Gone. Needed to be restored from backup…

…hopefully this time I’ll have more luck!

One of the advantages of being in the MVP Reconnect programme is that I occasionally get invited to webcasts that open my eyes to technology I’ve not had a lot to do with previously. For many years, one of the big holes in my knowledge was around Microsoft SQL Server. That was until I saw Brian Kelley (@kbriankelley)’s “Brief overview of SQL Server”. The content’s not restricted, so I thought I’d republish some of it here for others who are getting their head around the major on-premises components of the Microsoft Data Platform.

SQL Server Editions

There are several editions of SQL Server available and these are the key differences (updated for 2017):

• Express Edition (previously known as MSDE) is a free version, with some limitations around database size, etc.
• Standard Edition lacks some enterprise features but has high availability and suits many application workloads.
• Enterprise Edition is the full functionality product (but can be expensive)
• Developer Edition (not licenced for use in production) offers the full feature set but can also run on a client operating system whereas enterprise will only run on server-based operating systems
• Web Edition has reduced functionality and is intended for public websites (only available to service providers)
• Compact Edition is another free version, intended for embedded databases in ASP.NET websites and Windows desktop applications

Although SQL Server is often thought of as an RDBMS product, it’s really a suite of systems, under the SQL Server name. Usually that means the database engine but there are many parts, each of which has a distinct setup (i.e. you don’t need the database service for SQL Server Analysis Services and vice versa).

SQL Server Analysis Services (SSAS)

SSAS (since 2007) is an online analytical and transaction processing (OLAP) tool intended for data warehousing and data mining.

One advantage of OLAP is to run jobs during the night for pre-generated calculations (used for roll-ups – e.g. totals and averages, etc.). It can provide fast results to business users who would otherwise need complex calculations in a transactional system (e.g. sales data based on region, month, quarter, etc. can be done ahead of time).

SSAS is comparable to IBM Cognos or Oracle Essbase (normally packaged with Hyperion for accounting, etc.).

Some SSAS jargon includes:

• Star schema/snowflake schema – database design differs from transactional design. You can do these things in RDBMS but use SSAS on top.
• Cubes
• Dimensions
• Tabular model
• Data analysis expressions (DAX) – a language to do things in SSAS

SQL Server Integration Services (SSIS)

SSIS (since 2005) is heavily used for extract, transform and load (ETL) workloads – i.e. to get data from a source, manipulate it and pass it to a destination. It can be used to build a data warehouse, then data marts or to move data between systems. Basically, it’s a back-end batch processing system that performs the data mining.

SSIS is a replacement for Data Transformation Services (DTS). It’s not limited to SQL Server for source/destination so can talk to Oracle, Excel spreadsheets, other ODBC connections, etc.

The drag and drop interface is very powerful with the full functionality and flexibilityof Microsoft.NET behind it.

SSIS is comparable with Informatica (or Clover, etc.).

Some SSIS jargon includes:

• Packages (whatever is processing, contains all the logic)
• Tasks (what’s being carried out)
• Dataflow tasks (how you go from source to destination – could be multiples)
• Transformation (manipulating data)
• Business Intelligence Markup Language (BIML)

SQL Server Reporting Services (SSRS)

SSRS was introduced 2005 and became so popular it was ported back to SQL Server 2000!
It is a reporting engine, used to publish reports in-browser. Early versions were built on IIS but since 2008, SSIS has run directly on http.sys.

SSRS can be integrated with SharePoint (for report security based on SharePoint security) or the native, standalone mode is browser-based to look at folders, find reports, and run a report with parameters. Used to print via ActiveX control but now (since 2016) prints to PDF (or opens with a PDF reader).

There are two ways to build reports: Report Builder (a web-side interface for BA-type power user) or Report Designer (a full product for complex designs). There is also a subscription capability so users can subscribe to reports.

SSRS can be compared with IBM Business Objects and Tableau.

SSRS jargon includes:

• Reports
• Data sources
• Datasets
• ReportServer (API to integrate with other products)
• Native mode vs. integrated mode (SharePoint)

SQL Server Database Engine

The SQL Server database engine is what most people think of when SQL Server is mentioned.
It is traditionally a relational database management system (RDBMS) although it now contains many other database capabilities. It was originally derived from a Sybase product (until SQL Server 6.5).

SQL Server supports both multiple databases per instance (which can connect and join across) and multiple instances per server (from 2000) – the first is a default instance, then named instances can be created.

SQL Server uses a SQL language variant called T-SQL to interact. A GUI is provided in SQL Server Management Studio but it’s also possible interact via PowerShell.

SQL Server also has a scheduler (the SQL Server Agent), which can alert on success/failure and allows the creation of elaborate scheduling routines with notifications and the ability to run code. It is comparable with IBM DB2, Oracle, PostgreSQL, Sybase, MySQL and MariaDB.

SQL Server 2016 features include:

• High availability options, including Always On failover clusters; Always On availability groups (which are more flexible because they don’t have to replicate and fail over everything); Database mirroring (one database on multiple systems; deprecated now in favour of availability groups); log shipping.
• Several encryption options including built-in (certificate, asymmetric keys, symmetric keys); Enterprise Edition also has Transparent Data Encryption (TDE) to encrypt database at rest and stop copies of the database from being loaded elsewhere; connection encryption (SSL/TLS since 2005); Always Encrypted is new for 2016 (transparent to the application and to SQL Server) – data stored in encrypted form within the database.
• SQL Server and Windows authentication (server or Active Directory). Can have Windows or both, but not just SQL Server-based logins.
• Replication options to move data between servers.

Other security features include audit objects (who did what?); granular security permissions; login auditing (failed logins are written to the SQL Server Error Log text file and to the application event log); dynamic data masking (depending on who needs to see it – e.g. store social security numbers and only show part of the data; only obfuscation as data is still in clear text); row-level security (to filter rows).

Each new version brings performance enhancements, e.g. columnstore indexes, in-memory OLTP tables, query optimisation.

New Technologies in 2016 include:

• JSON support. Query and return data in JSON format. Administrators have been able to use SOAP and XML since 2005 but this is now deprecated in favour of JSON (which is popular for RESTful systems).
• Master data services.
• Polybase (not to be confused with a clustering solution – it’s about talking to other data sources, e.g. Hadoop, Cloudera and Azure storage, to be expanded to include Oracle, Teradata, Mongo, Spark and more).
• R Services/R Server (R within the database and also R Server for data science/big data queries).

2017 builds on 2016 to include:

• Linux and Docker support. Starting with SQL Server 2017, SQL Server is available for either Windows or Linux systems and it’s available as an installable application or for Docker containers.
• SQL Server R Services has been renamed SQL Server Machine Learning Services, to reflect support for Python in addition to R.

There are many more features in the Microsoft documentation but these are the most significant updates.

But what about the cloud?

This post provided a quick run-down of some of the major on-premises SQL Server components but, just as with Microsoft’s other products, there are cloud alternatives too. I’m planning a follow-up post to cover these so watch this space!