Microsoft SQL Server Pros and Cons

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:

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:

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!