Transcript SQLSaturday579_SqlOnLinuxx

SQL Server on Linux
CTP 1.1
Florian Mader
@flomader
Our Sponsors
Agenda






Quickstart
Supported Platforms & Features
Platform Abstraction Layer
Performance
SQL Server on Linux HA
SQL Server on Linux & Docker
Quickstart
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p
1433:1433 -d microsoft/mssql-server-linux
Requirements
•
Same high quality & security standards
•
Same value in terms of functionality and scale
•
Application compatibility between SQL Server on
Windows and Linux
•
Features and fixes need to appear immediately
across platforms
•
Lay a foundation for future SQL Server suite
services (SSIS)
How?
•
•
Same high quality code across OSes
Enabled through Platform Abstraction Layer (PAL)
Supported Platforms & Features
Supported Platforms
• Ubuntu 16.10, Ubuntu 16.04 LTS and earlier
• Red Hat Enterprise Linux 7.3 Workstation, Server, and Desktop and earlier
• Docker Engine 1.8 and later
Features
• Core Database Engine
• Including CLR, In-Memory OLTP, Columnstore Index, Query Store, DMV
Tooling
• Administration: SSMS, mssql-conf, Powershell
• Development: SSMS, Visual Studio Code, SSDT, Connectivity libraries (C#, Java, PHP, Node.js, Python,
Ruby, C++)
Unsupported Features (CTP 1.1)
Database Engine
High Availability
Security
Services
Full-Text Search
Always On Availability Groups
Active Directory Authentication
SQL Server Agent
Replication
Database Mirroring
Windows Authentication
SQL Server Browser
Stretch DB
Extensible Key Management
SQL Server R Services
Polybase
Use of user-provided certificate
for SSL or TLS
StreamInsight
Distributed Query
Analysis Services
CLR assemblies with the
EXTERNAL_ACCESS or UNSAFE
permission set
Reporting Services
Filetable
Integration Services
System extended stored
procedures (XP_CMDSHELL,
etc.)
Data Quality Services
Master Data Services
Platform Abstraction Layer
Goal
•
Remove or abstract away SQL Server’s dependencies on Windows
Dependencies
•
•
•
“Win32” (ex. user32.dll)
NT Kernel (ntdll.dll)
Windows application libraries (such as MSXML, MS DTC, )
SQL Server Operating Systems (SOS)
•
•
•
•
Resource management
Improved performance
Supportability
Not a full abstraction layer
Drawbridge
•
•
•
•
Microsoft Research Project
reducing the virtualization resource when hosting many VMs on the same hardware
user mode Library OS (LibOS)
1500+ Windows ABIs in user mode
SOSv2
•
•
SOS and LibOS merged
81 MB of uncompressed Windows libraries, SQL PAL itself ~8MB
Performance*
Script
Windows
Linux
Skype.sql
Columnstore Index,
logical reads
00:00:08.063
00:00:09.846
00:00:10.139
00:00:06.614
00:00:07.050
00:00:06.491
28%
ThankYou.sql
LIKE ‘%x%’, physical
reads
00:16:53.153
00:14:02.776
00:17:47.322
00:17:34.863
-14%
Top500AnswerersOnTh
eSite.sql
JOIN, Clustered Index
(B-Tree)
00:11:19.213
00:11:17.638
00:11:15.901
00:09:49.230
7%
UserRetention.sql
PIVOT, tempdb
00:11:03.486
00:11:13.968
00:12:17.326
00:10:17.352
-1%
SQLStress
Rows/Insert=1
2 7 9 3 Tx n / S e c
2 0 4 6 Tx n / S e c
1 6 0 9 Tx n / S e c
1 6 5 7 Tx n / S e c
-33%
SQLStress
Rows/Insert=100
7 5 Tx n / S e c
7 8 Tx n / S e c
5 2 Tx n / S e c
4 3 Tx n / S e c
-38%
* my personal (not official Microsoft) query test results, selected queries against StackOverflow dataset; Azure VMs, Standard D3, Windows Server
2012 R2, Ubuntu 15.10; not supposed to be an extended benchmark, rather quick tests to grasp a first impression on performance
SQL Server on Linux HA
Failover Cluster Instance on RHEL 7.3
• based on Red Hat Enterprise Linux (RHEL) HA add-on
• Uses Corosync and Pacemaker
Currently only high-level
functional testing,
not a production setup
Linux Server 2
Linux Server 3
SQL Server 1
SQL Server 2
Pacemaker
Corosync
Linux Server 1
NFS
SQL Server on Linux & Docker
Requires 4GB RAM
Runs on Docker for Windows & Docker for Mac
Demo
How did you like it?
Please give us feedback!
to the event:
www.sqlsaturday.com/579/eventeval.aspx
to me as a speaker:
www.sqlsaturday.com/579/sessions/sessionevaluation.aspx
Ressources
https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/16/sql-server-on-linux-howintroduction/
https://www.microsoft.com/en-us/research/project/drawbridge/
http://arstechnica.com/information-technology/2016/12/how-an-old-drawbridge-helped-microsoft-bringsql-server-to-linux/
http://social.technet.microsoft.com/wiki/contents/articles/36376.sql-server-getting-started-with-ms-sqlserver-on-ubuntu.aspx
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-whats-new
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes
Thank You!