Acronyms in SQL Server

SQL Server can be a dizzying array of acronyms to the uninitiated – SQL itself, SSIS, SSAS, SSRS, SMO, DMO – the list seems endless. In this post, I will define some of the acronyms and give a brief description of what each does, so you can decide if you are interested in learning more about them. There are four main parts of SQL Server, plus a couple of other pieces that are very closely knit into the Server, so we’ll take a look at them as well.

First, there is the Database Engine, which is the core functionality of SQL Server. This is the part of SQL Server that controls the managing of data, accessing it and securing it. It is also the core part of the product that manages query processing, tables, indexes, the Transaction Log and memory management, among other things. The Database Engine is one of the four main parts of SQL Server.

Another of the “Big Four” parts of SQL Server is Analysis Services (SSAS). This is the core of the Business Intelligence (BI) part of SQL Server. Using BI involves looking at and managing and analyzing very large amounts of data, and this is often done by creating something called an OLAP Cube. OLAP stands for Online Analytical Processing, and this is a way of defining data so that you can actually make sense out of it at a large scale. To give you an idea of this scale, I was recently speaking to an MVP who regularly used OLAP Cubes that were 30 Gigabytes in size, so you can see where it becomes very important to have this capability or you will never make sense out of that much data.

Next on the list is SSIS, or SQL Server Integration Services. This piece of SQL Server is used to manage data from diverse sources - for instance say that you need to analyze data from your CRM System, your Order Entry system, and the Customer Database, and these three systems are all non-compatible (in other words, the data is harvested from data stores created by different vendors). Using Integration Services, you can create a Package of data that can be converted to a single format so that you can then use the rest of the SQL Analysis tool (see SSAS above) to do the analysis on the data. You can see how this can easily be a very valuable part of your overall data management system, especially in larger organizations.

The last of the four main parts of SQL Server is SSRS, or SQL Server Reporting Services. This is probably the easiest of the pieces to understand, it is pretty self-explanatory. This is the tool that you can use to query your data and give you customized reports depending on what you want to see. The main components are the Report Server, the Report Designer and the Report Builder, by using these you can get a subset of the data that is relevant to what you want to do.

These are the main parts, there is, of course, much more, but hopefully this will help you unravel some of the acronyms that you will face when learning SL Server. If nothing else, you can now tell your friends that you can create an OLAP cube using SSAS in SQL to perform BI for your business, and they will either think you are brilliant, or they will call you a Geek.