What Is Microsoft SQL Server?
In technical terms, Microsoft SQL Server 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.|
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:
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 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. As a company, you can choose from among the following SQL Server editions, according to your needs:
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. 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? With the Microsoft Data Platform, there are 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. Source control is not native in SQL Server. To maintain all changes made to database objects, you’ll need to use third-party tools.
There you have it! Hopefully, this 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!