17th Jan 2019 8 minutes read

Microsoft SQL Server Pros and Cons

Thinking about using Microsoft SQL Server? If so, you’re in the right place. In this article, we’ll go over the pros and cons of Microsoft SQL Server and evaluate the platform from both a company’s and a data specialist’s perspective.

What Is Microsoft SQL Server?

Before diving into the pros and cons of Microsoft SQL Server, I’ll explain what it is. In technical terms, it is a relational database management system (RDBMS) developed by Microsoft. Essentially, it’s software that allows you to store, change, and manage relational data effectively. Along with Oracle, MySQL, PostgreSQL and DB2, Microsoft SQL Server is one of the most popular relational database management systems on the market.

One of the most essential functions of SQL Server is to provide data to other software applications (clients). To interact with SQL Server databases and manage or query their data, you can use the Transact-SQL (T-SQL) language, an extension of the SQL standard.

Here’s a useful graphic depicting the entire client-server architecture and the role of an RDBMS:

Microsoft SQL Server Editions

Microsoft SQL Server comes in various editions, each offering different functionalities so customers can choose the right one for them.

Here’s a list of all available Microsoft SQL Server 2017 editions:

SQL Server Edition	Description
Enterprise	The premium offering, SQL Server Enterprise edition delivers comprehensive high-end data center capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence—enabling high service levels for mission-critical workloads and end-user access to data insights.
Standard	SQL Server Standard edition delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premise and cloud—enabling effective database management with minimal IT resources.
Web	SQL Server Web edition is a low total-cost-of-ownership option for web hosters and web VAPs to provide scalability, affordability, and manageability capabilities for small to large scale Web properties.
Developer	SQL Server Developer edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition but is licensed for use as a development and test system, not as a production server. SQL Server Developer is an ideal choice for people who build and test applications.
Express	Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications. It is the best choice for independent software vendors, developers, and hobbyists building client applications. If you need more advanced database features, SQL Server Express can be seamlessly upgraded to other higher end versions of SQL Server. SQL Server Express LocalDB is a lightweight version of Express that has all of its programmability features. It runs in user mode and has a fast, zero-configuration installation and a short list of prerequisites.

Source: link

Microsoft Data and Business Intelligence Platform

Notably, SQL Server is not just a database engine; it provides many other services, tools, and applications.

When installing SQL Server, you can optionally select the following components:

SQL Server Integration Services (SSIS)—building ETL solutions.
SQL Server Data Quality Services (DQS)—building a knowledge base and performing data cleansing.
SQL Server Master Data Services (MDS)—comprehensive data management.
SQL Server Analysis Services (SSAS)—online analytical processing and data mining.
SQL Server Reporting Services (SSRS)—creating reports and visualizations.

Microsoft also offers many other products that work well with SQL Server. For example, when developing ETL processes using SSIS, you can use SQL Server Data Tools for Visual Studio. For developing interactive and live dashboards and reports, you can use Power BI Desktop and deploy them to Power BI Report Server. And when writing T-SQL queries, you can also use SQL Server Management Studio or Azure Data Studio.

Here’s a full summary of the Microsoft Business Intelligence (BI) Platform:

Microsoft SQL Server Pros and Cons: Overview

When it comes to MSSQL for both company and database specialist use, the benefits certainly outweigh the drawbacks. Here’s a brief overview of all the Microsoft SQL Server pros and cons:

Use case	Pros	Cons
Company	Various supported editions Online product documentation Microsoft Premier support On-premises and cloud database support Plenty of tools and applications Support for use on Linux	Expensive enterprise edition ($14,256 for a per-core license) Difficult licensing process that’s always changing
Database specialist	Career opportunities Free developer edition (with all enterprise features) Thriving online community Plenty of online documentation	Complex performance tuning features No native support for source control

Let’s take a look at some of the specifics in more detail.

Company Use: SQL Server Benefits

1. Various editions to choose from. What will be especially important for your company on our Microsoft SQL Server pros and cons list is the choice of available editions. You can choose from among the following ones, according to your needs:

Enterprise
Standard
Developer
Web
Express

2. Product documentation and Microsoft Premier support. With Microsoft Premier support, you’ll have access to a network of skilled specialists from Microsoft’s dedicated support teams. If you have any problems using MSSQL, they’ll help you sort things out.

3. On-premises and cloud database support. If it suits your business goals, you can offload the administration and management of your databases to the cloud with Azure SQL Database or SQL Server on Azure Virtual Machines.

4. SQL Server tools and applications. Microsoft SQL Server comes with many useful tools that will speed up database design, development, troubleshooting, and maintenance, including (but not limited to):

SQL Server Management Studio
Azure Data Studio
SQL Server Data Tools
SQL Server Profiler

For example, companies that have a data warehouse running on the MSSQL platform can easily integrate it with Analysis Services and Power BI without having to buy third-party software; everything is available on the MS Data platform.

5. Support for use on Linux. SQL Server 2017 is the first version that also runs on Linux. Here’s a list of supported Linux distributions:

Red Hat Enterprise Linux
SUSE Linux Enterprise Server
Ubuntu 16.04
Docker Engine

This makes it much easier for companies that already have many servers running on Linux to install and run SQL Server on those machines—no need to buy new Windows Server licenses, for example.

Company Use: SQL Server Drawbacks

1. Expensive pricing. The first drawback on our Microsoft SQL Server pros and cons list is purely financial. Many organizations cannot afford to pay for the Enterprise edition. The current cost is $14,256 for a per-core license.

2. Complicated licensing. SQL Server licensing can be quite difficult to understand and is always changing.

Database Specialist Use: SQL Server Benefits

1. Plenty of career opportunities. Are you interested in database administration, performance tuning, or T-SQL programming? Would you like to design and implement data cleansing and extract, transform, and load operations? Are you keen on data mining and online analytical processing? Or do you see yourself as a dashboard and interactive reports designer or developer? As we mentioned in our list of pros and cons of Microsoft SQL Server, the platform gives you many possibilities to suit your career aspirations.

2. Free Developer edition. The SQL Server Developer edition is completely free and includes all enterprise SQL Server features. You can just download, install, and configure your own development environment and explore all features and functionalities of the latest SQL Server version.

3. A growing community. SQL Server has a thriving community. There are many community-hosted global events, like SQL Saturday, as well as conferences, educational materials, and a great network of highly skilled professionals who are always ready to help you (the hashtag #sqlhelp works very well on Twitter).

4. Useful online documentation. Personally speaking, I find the online documentation for Microsoft SQL Server very detailed and well structured. There are also plenty of whitepapers and useful demos that help you to understand a specific topic in greater detail.

Database Specialist Use: SQL Server Drawbacks

1. Complex performance tuning. Query optimization and performance tuning can be difficult for data specialists who don’t have deep, specialized knowledge. However, this isn’t really a problem if you’re a beginner, as you won’t be performing much query optimization anyway (unless you operate on very large data sets).

2. Lack of native support for source control. The closing point of our list of pros and cons of Microsoft SQL Server is related to source control: it is not native in SQL Server. To maintain all changes made to database objects, you’ll need to use third-party tools.

Summary

There you have it! Hopefully, our list of the pros and cons of Microsoft SQL Server gives you a better sense of how SQL Server may (or may not) suit your database management needs.

SQL Server is an important component of the Microsoft Data and Business Intelligence platform. And SQL and relational databases are becoming increasingly important in our data-driven business world because it’s essential for companies to be able to manage, manipulate, and query data as quickly and efficiently as possible.

With Microsoft SQL Server, you have many opportunities to improve your skills in various areas of data science—T-SQL is definitely a skill worth picking up alongside standard SQL!

Tags: