SQL Server 2017 on Linux
Stéphane Fréchette
Data Platform Solution Architect @ Microsoft
twitter: @sfrechette | blog: stephanefrechette.com
A must read...
Let's Get Started!
Install Linux
Install Linux on a physical machine or a virtual machine (VM)
Or create and use a pre-configured Linux VM in Azure. There is also a VM image with SQL Server vNext CTP2.0 already installed, see Create a Linux SQL Server 2017 virtual machine with the Azure portal
SQL Server on Linux
Supported platforms (installation guide)
Install SQL Server
SQL Server on Linux
Install SQL Server tools
sqlcmd: Command-line query utility
bcp: Bulk import-export utility
Optional Packages
SQL Server on Linux
Install SQL Server Agent
Run scheduled SQL Server jobs
Optional Packages
SQL Server on Linux
Install SQL Server Full-Text Search
full-text queries against character-based data
Optional Packages
SQL Server on Linux
How-to's
Connect and query
SQL Server on Linux
Connect using sqlcmd on Linux
# On your Linux box, open a command terminal
# Run sqlcmd with parameters for your SQL Server name (-S), the user name (-U), and the password (-P)
# The following command connects to the local SQL Server instance (localhost) on Linux
sqlcmd -S localhost -U SA -P '<YourPassword>'
# Connect to a remote instance, specify the machine name or IP address for the -S parameter
sqlcmd -S 192.222.2.222 -U SA -P '<YourPassword>'SQL Server on Linux
Query SQL Server
--Returns the name of all of the databases
SELECT Name from sys.Databases;
GO
--Create a database using the SQL Server default settings
CREATE DATABASE Sandbox;
GO
--Use the database
USE Sandbox;
GO
--Create a table in the current database
CREATE TABLE product (id INT, name NVARCHAR(50), quantity INT);
GO
--Insert some data
INSERT INTO product VALUES (1, 'Bottle', 250);
INSERT INTO product VALUES (2, 'Hat', 95);
INSERT INTO product VALUES (3, 'Shirt', 128);
GO
--Select from the table
SELECT * FROM product WHERE quantity > 100;
--End your sqlcmd session, type QUIT
QUIT
SQL Server on Linux
Visual Studio Code
Develop
- Download and install VS Code on your machine
-
Install the mssql extension
- Press CTRL+SHIFT+P (or F1) to open the Command Palette in VS Code (for macOS the Command key is equivalent to the CTRL key on Linux and Windows)
- Select Install Extension and type mssql
- Click install mssql
SQL Server on Linux
Use SQL Server Management Studio
Develop
SQL Server on Linux
Use SQL Server Data Tools
Develop
SQL Server on Linux
Develop
SQL Server on Linux
Manage
SQL Server on Linux
Migrate
- Migrate from SQL Server on Windows
- Migrate from other database servers
- Migrate structured data
SQL Server on Linux
Business continuity
Support high-availability and disaster recovery
SQL Server on Linux
Security
SQL Server on Linux currently has the following limitations:
- Only SQL Server authentication is currently available.
- A standard password policy is provided. MUST_CHANGE is the only option you may configure.
- Extensible Key Management is not supported.
- Using keys stored in the Azure Key Vault is not supported.
- SQL Server generates its own self-signed certificate for encrypting connections. Currently, SQL Server cannot be configured to use a user provided certificate for SSL or TLS.
Get started with security features of SQL Server on Linux
SQL Server on Linux
Optimize Performance
Not unique or specific to Linux, but worth exploring:
(Works in SQL Server on Linux!)
- Create a Columnstore Index
- Use In-Memory OLTP
- Use Query Store
- Query Dynamic Management Views
SQL Server on Linux
Summary
SQL Server on Linux offers a broader range of choice for all organizations, not just those who want to run SQL on Windows. It enables SQL Server to run in more private, public, and hybrid cloud ecosystems, to be used by developers regardless of programming languages, frameworks or tools.
SQL Server on Linux
Resources
SQL Server on Linux