Running SQL Server on Linux: What to Know

Microsoft always listen to the technical communities around the world and a long request from the community is the cross-platform sql server. Since a large number of the world’s database servers are powered by Linux, Microsoft launched its own by 2017. It’s lean, mean, stable, powerful and with higher security.

SQL Server 2017 brings the best features of the Microsoft relational database engines to the enterprise Linux ecosystem, including SQL Server Agent, Azure Active Directory (Azure AD) authentication, best-in-class high availability / disaster recovery, and unparalleled data security.

Note that SQL Server on Linux is not a port or code rewrite specifically for Linux. This is the same world-class Microsoft relational database management system (RDBMS) now available on more operating systems (like Red Hat Enterprise Linux, SUSE Linux Enterprise Server, and Ubuntu) and more cloud and container platforms (like Docker).

How this works in Linux?

From SQL Server 2005, the hardware resource management has been incorporated into SQL Server as a separate component known as SQLOS (SQL Server Operating System). 

The objective of this initiative was to improve SQL Server performance by assigning database own tasks to manage itself so that SQL Server can perform better. The SQLOS then acts as a non-preemptive schedule to service database own tasks by cooperating with Windows OS. As a result, SQL Server knows how to manage the hardware resources better than Windows OS.

Microsoft's porting effort is based on an acompatibility layer called SQLPAL(Platform Abstraction Layer). This enables the subset of Windows libraries used by SQL Server to run on Linux. Even on Windows, SQL Server does its own memory and thread management via SOS (SQL Operating System). In Linux SQL Server, SOS becomes part of SQLPAL so that SOS calls native Linux APIs directly.

Migration:

We can migrate our existing SQL Server database into SQL Server on Linux through SSMA and DMA.

SQL Server Migration Assistant (SSMA):

SSMA allows you to convert database schemas to SQL Server schemas, upload the schemas and migrate data to the target server. SSMA manages the migration process with a graphical user interface that walks you step by step through the conversion, upload and data migration process, highlighting any issues encountered during the conversion

Data Migration Assistant (DMA):

DMA enables you to upgrade to a modern data platform by detecting compatibility issues that can affect database functionality on your new version of SQL Server. It recommends performance and reliability improvements for your target environment.
 

Monitoring SQL Server on Linux:

DMVs:

SQL Server Dynamic Management Views (DMVs) are built-in views and functions that return information about the server that can be used to monitor health and performance of an instance of SQL Server. The focus of these views and functions ranges from database information such as file and log space usage or partition statistics to Availability Group states. Starting with SQL Server 2017, a set of DMVs was specifically designed to access catalog metadata for SQL Server running on Linux.

Activity Monitor:

The Activity Monitor in SQL Server Management Studio is useful for ad hoc views of current activity and graphically displays information about processes running on an instance of SQL Server, such as blocked processes, locks, and user activity.

Management Tools for SQL Server on Linux:

Graphical Tools:

We can use all existing graphical tools that run on Windows to access SQL Server on Linux. These include tools from Microsoft such as SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), and third-party tools.

SSMS:

SQL Server Management Studio is a free graphical tool that is offered by Microsoft. Using SSMS, we can design and manage databases, Analysis services, Integration services and Reporting services.SSMS provides database administrators (DBAs) with the interface to perform HA/DR, database configuration and maintenance plans, SQL Server Agent management, query performance, and much more.

SSDT:

SQL Server Data Tools available for free with all versions of visual studio.Using SSDT we can do Database operations such as Database development and designing. SSDT supports Analysis Services, Integration Services, and Reporting Services, and it offers tools such as schema and data comparison.

Command line tools:

sqlcmd and bcp are natively available on Linux, MacOS, and Windows. In addition, the new mssql-scripter command line tool on Linux, macOS, or Windows to generate T-SQL scripts for your SQL database running anywhere.

SQLCMD: The sqlcmd command line utility is available on Linux, MacOS, and Windows and lets you connect to SQL Server running anywhere, and execute adhoc T-SQL statements, system procedures, and script files at the command prompt.

BCP: The bcp command line utility is available on Linux, macOS, and Windows and lets you efficiently bulk copy a large number of rows into SQL Server tables or export data out of tables into data files in a user-specified format.

mssql-scripter: The new mssql-scripter command line utility on Linux, macOS, and Windows to generate data definition language (DDL) and data manipulation language (DML) T-SQL scripts for database objects in SQL Server running anywhere, Azure SQL Database, and Azure SQL Data Warehouse. You can choose to save the generated T-SQL script to a .sql file or pipe it to well-known Linux utilities such as sed, awk, and grep for further transformations.

Conclusion

SQL Server on Linux brought the capabilities of SQL Server to Linux environments, giving organizations more flexibility in choosing their operating system and infrastructure. With migration tools, monitoring options, graphical interfaces, and command-line utilities, it provides a practical platform for running and managing SQL Server on Linux.

At Mafiree, we help businesses migrate, manage, and optimize database environments to achieve better performance, reliability, and scalability.