So Long, Microsoft SQL Server 2008 I think that you’re supposed to know when it’s time to say goodbye. - Judy Sheindlin We get it. You’ve been running your SQL Server 2008 for te...
Challenges with running SQL Server on Linux
Managing customer’s data and applications anywhere, we’ve got hands-on experience of installing, configuring, and running SQL Server on Linux. As an early adopter of SQL Server 2017 on Linux, the DB Best solution architects know all about the most common customer’s challenges with SQL Server on Linux.
Microsoft SQL Server on Linux Upgrades
Microsoft regularly releases updates of SQL Server, will I get them if I run SQL Server on Linux?
Yes, Microsoft releases the SQL Server on Linux updates to add the currently unsupported features to the product. So, you can update Microsoft SQL Server 2017 instances on Linux using existing package update/upgrade processes. So, with the Linux-native user experience, you can be sure that you’re always running the latest version of the Microsoft SQL Server on Linux.
SQL Server on Linux licensing
What about the licenses for the Microsoft SQL Server on Linux?
Buying a SQL Server license gives you the option to use it on Windows Server, Linux, or Docker. Regardless of where you run it — VM, Docker, physical, cloud, on-premises — the licensing model is the same; available features depend on which edition of SQL Server you use.
Moreover, you can move your existing enterprise licenses from Windows to Linux with no extra charge. Generally speaking, Microsoft didn’t apply any changes to the licensing model of SQL Server the release of the Linux edition.
Limitations and unsupported features
What are the SQL Server's limitations when running on Linux, and which features are not supported?
Generally speaking, SQL Server on Linux provides a familiar experience to Windows users. You don’t need to worry as approximately 95 percent of the features you regularly use just work — anything app or coding related. Some features have partial support, for example, SQL Server Agent will not launch a Windows command prompt. Some of the SQL Server features are still in progress. And all of the BI tools, like reporting or integration services, are yet to be supported.
There are some features SQL Server on Linux will never support. For example, FileTable, where you have a win32 share to place files that show up in the engine. Discover the full list of features not currently supported on Linux on https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes.
Why consider Microsoft SQL Server on Linux?
Microsoft provides you with the Top 6 reasons companies make the move to SQL Server on Linux. Still not convinced? Here’s what DB Best has to say about this.
Microsoft SQL Server on Linux has hit the top of the popular TPC-H benchmark, leaving Windows-powered servers biting the dust. That’s not a surprise — SQL Server with its great functionality, performance, and scale can take advantage of the open-source Linux platform.
- No differences at all
Microsoft has focused on providing a Linux-native user experience for SQL Server, starting with the very beginning — the installation and upgrade processes. You can use almost all the SQL Server functions, applications, statements, and even scripts, that you normally use on the Windows Server to manage your SQL Server on Linux database instance. You can leverage such Windows tools as SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), and PowerShell module (sqlps) to manage SQL Server on Linux from a Windows instance.
The Visual Studio Code extension for SQL Server can run on macOS, Linux, or Windows. And because the Linux and Windows versions of SQL Server use the same code base, existing applications, drivers, frameworks, and tools will connect to and operate with SQL Server on Linux without modification.
Most business people can agree, this is the most important and profitable reason to run your SQL Server on Linux because it significantly drops the running price. Simply put, you don’t need to pay for your Windows Server license. And if you consider a move to the Azure cloud, you will save up to 40% on the hourly rate if you run Azure virtual machines on Linux with the exactly the same configuration opposed to running them on Windows Server.
- Advanced features
And the last, but not least item in this list. SQL Server brings new cool features compared to open source databases like MySQL, MariaDB or PostgreSQL running on Linux.
Getting started offers
How can you start running the SQL Server on Linux? There are several options. For example, you can upgrade your aging SQL Server 2008 databases to SQL Server 2017 or higher, moving them to the Linux OS. Or you may be looking for high availability options, considering SQL Server on Linux for secondary replicas. You can even want to migrate existing open-source databases running on Linux to SQL Server with no need to buy the Windows Server License.
At DB Best, we stay platform-agnostic. So, no matter where you run your current databases, we are ready to help you get the most out of running Microsoft SQL Server on Linux. We can offer you several ways to get started.
With our unique Data Management Optimization service, you can save up to 60% in operating costs. We measure the performance of your data infrastructure and discover how to optimize licensing costs while maximizing performance in a move to the cloud.
Everyone is talking about the need for a digital transformation, but how do you get there? See how our future-state architecture design can use cutting edge technology to meet your organization’s needs.
Running on outdated versions of SQL Server? You're not alone! 60% of all databases are running on versions of SQL Server that are out of support or extended support. Learn why you need DB Best to accelerate your SQL Server upgrade projects.
Best practices on running SQL Server on Linux
We are happy to share some of our SQL Server on Linux experience with you. Feel free to contact us for additional information.
Getting started with SQL Server on Linux
Installing Microsoft SQL Server on Linux is relatively easy. The installation process follows a familiar to Linux administrators pattern.
First, you need to add the SQL Server repository to your package manager. Then, after you update the package list, you can install the mssql-server package. Finally, you need to run mssql-conf setup to configure the SA password and edition.
The upgrade process also provides the Linux-native user experience. Administrators can update SQL Server 2017 instances on Linux using their existing package update/upgrade processes.
What’s available on Linux?
Here’s a short list of features that you can use running SQL Server on Linux.
- Support for RHEL, Ubuntu, SLES, Docker
- Package-based installs
- Support for Open Shift, Docker Swarm
- Failover clustering via Pacemaker
- SSMS on Windows connected to Linux
- Command-line tools: sqlcmd, bcp
- Transparent Data Encryption
- Backup Encryption
- SCOM management pack
- Table partitioning
- SQL Server Agent
- Full-Text Search
- Integration Services
- Active Directory (integrated) authentication
- TLS for encrypted connections
- In-Memory OLTP
- Columnstore indexes
- Query Store
- Always Encrypted
- Row-Level Security, Data Masking
- Service Broker
- JSON, XML
Architectural solutions with SQL Server on Linux
With SQL Server on Linux, you can dramatically simplify high availability and disaster recovery features of your mission-critical systems. You can now create availability groups that span Windows and Linux nodes and scale out your analytics workloads across multiple operating systems.
Here’s an example of an enterprise solution with mission-critical HA and hybrid DR:
- Multiple node failover clustering on Windows and Linux (3 synchronous replicas and up to 9 total replicas)
- Multiple database failover as a group
- Fully readable secondaries for backup or reporting operations
- Distributed Availability Groups provide DR resiliency and enhance read scale by increasing the number of readable secondaries across geographies
- Read scale-out without cluster dependency
You can also improve read concurrency with read-scale availability groups. This is a cool new feature, implemented in SQL Server 2017. You can create read-scale availability groups without the need for a cluster manager. You can add up to 17 read-only replicas to a read-scale availability group. Read-only replicas might be geographically distributed. Read-scale Availability Groups support both Linux and Windows. So, you can improve the reliability of your database system with SQL Server on Linux, saving costs on replica licenses.
You can configure a shared-storage high-availability cluster with Linux to allow the SQL Server instance to failover from one node to another. Failover clusters provide instance level protection to improve recovery time by allowing SQL Server to failover between two or more nodes.
You can make Failover Cluster Instances and Availability Groups work together to ensure data is accessible despite failures, as shown on the picture below.
You can then even opt for running a primary replica in FCI in the primary data center for HA, and then place multiple secondary replicas in the remote data centers for DR.
DBMSys is a lightweight tool designed by DB Best specifically around collecting data from multiple environments to help develop a business case for a modernization to upgrade path. The...