SQL Server 2017 Release Candidate 2 (RC2) is now available

Microsoft is pleased to announce availability of SQL Server 2017 Release Candidate 2 (RC2), which is now available for download.

The release candidate represents an important milestone for SQL Server.  Development of the new version of SQL Server along most dimensions needed to bring the industry-leading performance and security of SQL Server to Windows, Linux, and Docker containers is complete.  We are continuing to work on performance and stress testing of SQL Server 2017 to get it ready for your most demanding Tier 1 workloads, as well as some final bug fixes.

We invite you to test out RC2 today, and to let us know how it’s working for you.  If you have questions, you can join the discussion of SQL Server 2017 at MSDN. If you run into an issue or would like to make a suggestion, you can let us know through Connect. We look forward to hearing from you!

Get started with SQL Server 2017 RC2 today!

Try the release candidate of the SQL Server 2017 today! Get started with our updated developer tutorials that show you how to install and use SQL Server 2017 on macOS, Docker, Windows, and Linux and quickly build an app in a programming language of your choice. For more ways to get started, try the following:

Microsoft Drivers v4.3.0 for PHP for SQL Server released!

This post was authored by Meet Bhagdev, Program Manager, Microsoft

Hi all,

We are excited to announce the Production Ready release for the Microsoft Drivers v4.3.0 for PHP for SQL Server. The drivers now support Debian Jessie and macOS. The driver enables access to SQL Server, Azure SQL Database, and Azure SQL DW from any PHP application on Linux, Windows, and macOS.

Notable items for the release:

Added

Fixed

  • Fixed the assertion error (Linux) when fetching data from a binary column using the binary encoding (issue #226)
  • Fixed PECL installation errors when PHP was installed from source (issue #213)
  • Fixed issue output parameters bound to empty string (issue #182)
  • Fixed a memory leak in closing connection resources
  • Fixed load ordering issue in MacOS (issue #417)
  • Fixed the issue with driver loading order in macOS
  • Fixed null returned when an empty string is set to an output parameter (issue #308)
  • SQLSRV only
    • Fixed sqlsrv client buffer size to only allow positive integers (issue #228)
    • Fixed sqlsrv_num_rows() when the client buffered result is null (issue #330)
    • Fixed issues with sqlsrv_has_rows() to prevent it from moving statement cursor (issue #37)
    • Fixed conversion warnings because of some const chars (issue #332)
    • Fixed debug abort error when building the driver in debug mode with PHP 7.1
    • Fixed string truncation when binding varchar(max), nvarchar(max), varbinary(max), and xml types (issue #231)
    • Fixed fatal error when fetching empty nvarchar (issue #69)
    • Fixed fatal error when calling sqlsrv_fetch() with an out of bound offset for SQLSRV_SCROLL_ABSOLUTE (issue #223)
  • PDO_SQLSRV only
    • Fixed issue with SQLSRV_ATTR_FETCHES_NUMERIC_TYPE when column return type is set on statement (issue #173)
    • Improved performance by implementing a cache to store column SQL types and display sizes (issue #189)
    • Fixed segmentation fault with PDOStatement::getColumnMeta() when the supplied column index is out of range (issue #224)
    • Fixed issue with the unsupported attribute PDO::ATTR_PERSISTENT in connection (issue #65)
    • Fixed the issue with executing DELETE operation on a nonexistent value (issue #336)
    • Fixed incorrectly binding of unicode parameter when emulate prepare is on and the encoding is set at the statement level (issue #92)
    • Fixed binary column binding when emulate prepare is on (issue #140)
    • Fixed wrong value returned when fetching varbinary value on Linux (issue #270)
    • Fixed binary data not returned when the column is bound by name (issue #35)
    • Fixed exception thrown on closeCursor() when the statement has not been executed (issue #267)

Limitations

  • No support for input/output parameters when using sql_variant type

Known issue

  • When pooling is enabled in Linux or macOS
    • unixODBC <= 2.3.4 (Linux and macOS) might not return proper diagnostics information, such as error messages, warnings, and informative messages
    • Because of this unixODBC bug, fetch large data (such as xml, binary) as streams as a workaround. See the examples here.

Survey

Let us know how we are doing and how you use our driver by taking our pulse survey: https://www.surveymonkey.com/r/CZNSBYW.

Get started

Getting Drivers for PHP5 and older runtimes

You can download the Microsoft PHP Drivers for SQL Server for PHP 5.4, 5.5, and 5.6 from the download center: https://www.microsoft.com/en-us/download/details.aspx?id=20098. Version 3.0 supports PHP 5.4, version 3.1 supports PHP 5.4 and PHP 5.5, and version 3.2 supports PHP 5.4, 5.5, and 5.6.

PHP Driver Version Supported
v3.2 PHP 5.6, 5.5, 5.4
v3.1 PHP 5.5, 5.4
v3.1 PHP 5.4

Meet Bhagdev (meetb@microsoft.com)

msftlovesphp

SQL Server 2017 containers for DevOps scenarios

This post was authored by Tony Petrossian, Partner Group Program Manager, Database Systems Group

SQL Server 2017 will bring with it support for the Linux OS and containers running on Windows, Linux, and macOS. Our goal is to enable SQL Server to run in modern IT infrastructure in any public or private cloud.

With support for containers, SQL Server can now be used in many popular DevOps scenarios.  Developers working with Continuous Integration/Continuous Deployment (CI/CD) pipelines can now include SQL Server 2017 containers as a component of their applications for an integrated build, test, and deploy experience.

CI/CD automation with containers – Using containers greatly simplifies the development, testing, and deployment of applications. This is achieved by the packaging of all dependencies, including SQL Server, into a portable, executable environment that reduces variability and increases the speed of every iteration in the CI/CD pipeline. This also enforces a consistent experience for all participants since they can share the same state of an application in their containers. Developers can improve applications in their local environments during the first part of the Continuous Integration process.

The development process starts by taking a container that represents the current state of a production application, including a subset of the sanitized data. Developers can then add their features and fixes to it, while having the ability to verify the functionality of the application at any moment. Then, the container can be sent to a testing/quality assurance environment where it can be tested with a larger, more representative dataset.

Continuous Deployment is a critical part of DevOps pipelines. With a successful CD pipeline, a validated and self-contained version of the application is available. Developers can publish and share fully configured containers with all application dependencies, including SQL Server, with their peers. This can significantly improve developers’ ability to collaborate as they can all work on the same exact configurations simultaneously without having to build the complicated environment necessary for developing and testing applications with many components.

Parallel testing made fast and easy – Developers can automate the large-scale testing of containerized applications that include SQL Server. Thousands of tests can execute in parallel using high-density container deployments with managed container services. Kubernetes, Docker Swarm, or other orchestration systems can be used to easily manage a large number of test executions. Long-running test cycles can be optimized by load balancing the executions across multiple pods that spin up on demand and spin down when finished.

The Microsoft SQL Server development team is now taking advantage of these capabilities in building, testing, and publishing the new versions of SQL Server 2017. The team uses Azure Container Services to deploy hundreds of containers managed by a large Kubernetes cluster to execute all daily tests of SQL Server. Hundreds of thousands of tests are executed within hours of the availability of a new build! This methodology has enabled the team to run more tests in less time with fewer resources.

Multi-OS development, test, and production environments – With the containerization of the app, developers no longer need to be concerned about aligning stages of the development and production pipeline with the same exact distribution and version of Linux. Developers can containerize their application environment, including SQL Server, to abstract it from the operating system of the underlying host. Whether part of the pipeline is operated on Ubuntu and other parts in Red Hat Enterprise Linux (RHEL), the ability to containerize the entire application environment eliminates the need to overcome challenges of a cross-platform environment. Developers are also free to choose their preferred development environment without worrying about compatibility issues in later parts of the pipeline. With SQL Server 2017, developers can run SQL Server Linux Docker images on macOS, Windows, and Linux.

Deploying SQL Server into production – As new versions of the application are tested and verified, builds of the containers are published for use in staging and production. The exact version of the SQL Server that was used throughout the development and testing pipeline is now in the production image, and the team can be confident that the entire stack, including SQL Server, has been tested as one unit and is ready for use.

Learn more about how to better integrate your SQL Server data/database on the DevOps cycle.

First release candidate of SQL Server 2017 now available

We are pleased to announce availability of the first public release candidate for SQL Server 2017, Release Candidate 1 (RC1), which is now available for download. This means that development work for the new version of SQL Server is complete along most dimensions needed to bring the industry-leading performance and security of SQL Server to Windows, Linux, and Docker containers.

In our seven community technology previews (CTPs) to date, SQL Server 2017 has delivered:

  • Linux support for tier-1, mission-critical workloads SQL Server 2017 support for Linux includes the same high availability solutions on Linux as Windows Server, including Always On availability groups integrated with Linux native clustering solutions like Pacemaker.
  • Graph data processing in SQL Server With the graph data features available in SQL Server 2017 and Azure SQL Database, customers can create nodes and edges, and discover complex and many-to-many relationships.
  • Adaptive query processing Adaptive query processing is a family of features in SQL Server 2017 that automatically keeps database queries running as efficiently as possible without requiring additional tuning from database administrators. In addition to the capability to adjust batch mode memory grants, the feature set includes batch mode adaptive joins and interleaved execution capabilities.
  • Python integration for advanced analytics Microsoft Machine Learning Services now brings you the ability to run in-database analytics using Python or R in a parallelized and scalable way. The ability to run advanced analytics in your operational store without ETL means faster time to insights for customers while easy deployment and rich extensibility make it fast to get up and running on the right model.

Key enhancements in Release Candidate 1

In SQL Server 2017 RC1, there were several feature enhancements of note:

  • SQL Server on Linux Active Directory integration – With RC1, SQL Server on Linux supports Active Directory Authentication, which enables domain-joined clients on either Windows or Linux to authenticate to SQL Server using their domain credentials and the Kerberos protocol. Check out the getting started instructions.
  • Transport Layer Security (TLS) to encrypt data – SQL Server on Linux can use TLS to encrypt data that is transmitted across a network between a client application and an instance of SQL Server. SQL Server on Linux supports the following TLS protocols: TLS 1.2, 1.1, and 1.0. Check out the getting started instructions.
  • Machine Learning Services enhancements – In RC1, we add more model management capabilities for R Services on Windows Server, including External Library Management. The new release also supports Native Scoring.
  • SQL Server Analysis Services (SSAS) In addition to the enhancements to SSAS from previous CTPs of SQL Server 2017, RC1 adds additional Dynamic Management Views, enabling dependency analysis and reporting. See the Analysis Services blog for more information.
  • SQL Server Integration Services (SSIS) on Linux The preview of SQL Server Integration Services on Linux now adds support for any Unicode ODBC driver, if it follows ODBC specifications. (ANSI ODBC driver is not supported.)
  • SQL Server Integration Services (SSIS) on Windows Server RC1 adds support for SSIS scale out in highly available environments. Customers can now enable Always On for SSIS, setting up Windows Server failover clustering for the scale out master.

SQL Server 2017 for faster performance

SQL Server 2017 has several new benchmarks demonstrating faster performance than competitive databases, and against older versions of SQL Server:

Streamline your DevOps using SQL Server 2017

In SQL Server 2017, we have introduced support for SQL Server on Linux-based containers, a benefit for customers using containers in development or production. We’re also working to help developers get started developing an app for SQL Server as fast as possible with installation instructions, code snippets, and other handy information.

On our new microsite DevOps using SQL Server, which launched today, developers and development managers can learn how to integrate SQL Server in their DevOps tasks. Find demos, documentation, and blogs, as well as videos and conference presentations. Or, join the DevOps conversation at our Gitter channels.

Customers are already benefitting from SQL Server 2017

In fact, with our Early Adoption Program, customers can develop new applications for SQL Server 2017 or add Linux support to existing applications, and get the support and end-user license agreement that they need to go into production on SQL Server right now. Here are some customers already benefitting from SQL Server 2017 on Linux:

  • Convergent Computing A system integrator and longtime Microsoft partner, Convergent Computing was able to achieve a much faster return on server and storage hardware investments than usual by moving some tier-2 applications to inexpensive, white box servers running SQL Server 2017 on Linux.
  • dv01 – Financial technology startup dv01 started out with an open source database on a competitor cloud. But when it ran into performance and scale problems, SQL Server was able to give it 15X faster performance, plus in-database advanced analytics. And by moving to SQL Server 2017, dv01 could standardize its operating systems on Linux—all with an easy migration.

Get started with SQL Server 2017 RC1 today!

Try the release candidate of the SQL Server 2017 today! Get started with our updated developer tutorials that show you how to install and use SQL Server 2017 on macOS, Docker, Windows, and Linux and quickly build an app in a programming language of your choice. For more ways to get started, try the following:

Have questions? Join the discussion of SQL Server 2017 at MSDN. If you run into an issue or would like to make a suggestion, you can let us know through Connect. We look forward to hearing from you!

Microsoft JDBC Driver 6.2 for SQL Server released

This post was authored by Andrea Lam, Program Manager, SQL Server

We are happy to announce the full release of the Microsoft JDBC Driver 6.2 for SQL Server. The JDBC Driver provides Java database connectivity from any Java application, application server or Java-enabled applet to Microsoft SQL Server, Azure SQL Database and Azure SQL Data Warehouse. The driver is open source and available at no additional charge.

What’s new

Performance improvements for Prepared Statements
Improved performance for Prepared Statements through caching (including prepared statement handle re-use). This behavior can be tuned using new properties to fit your application’s needs.

Azure Active Directory (AAD) support for Linux
Connect your Linux applications to Azure SQL Database using AAD authentication via username/password and access token methods.

Federal Information Processing Standard (FIPS) enabled Java virtual machines
The JDBC Driver can now be used on Java virtual machines (JVMs) that run in FIPS 140 compliance mode to meet federal standards and compliance.

Kerberos Authentication Improvements
The JDBC Driver now has support for:

  • Principal/Password method for applications where the Kerberos configuration cannot be modified or is unable to retrieve a new token or keytab. This method can be used for authenticating to a SQL Server that only allows Kerberos authentication.
  • Cross-realm authentication using Kerberos integrated authentication without explicitly setting the server service principal name (SPN). The driver now automatically computes the REALM even when it has not been provided.
  • Kerberos constrained delegation by accepting impersonated user credentials as a GSS credential object via data source. This impersonated credential is then used to establish a Kerberos connection.

Added Timeouts
The JDBC driver now supports the following configurable timeouts you can change based on your application’s needs:

  • Query Timeout to control the number of seconds to wait before a timeout occurs when running a query
  • Socket Timeout to specify the number of milliseconds to wait before a timeout occurs on a socket read or accept

Get the latest JDBC jars

Add the JDBC driver to your Maven project by adding the following code to your POM file:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>6.2.0.jre8</version>
</dependency>

You can also download the JDBC driver 6.2 jars directly from:

Help us improve the JDBC Driver by filing issues on GitHub or contributing to the project.

Resources

Get started quickly with Java and SQL Server on your platform of choice with our tutorials.

For more detailed documentation, check out the JDBC documentation here.

Stay tuned for upcoming releases that will have additional feature support. This applies to our wide range of client drivers including:

Be sure to also check out other open sourced community developed drivers including:

Webcast: Intro to Microsoft SQL Server on Linux with containers

With our community technology previews of SQL Server 2017, Microsoft is bringing the industry-leading performance and security of SQL Server to Linux and containers for the first time. SQL Server running on Red Hat Enterprise Linux and Red Hat OpenShift Container Platform has advantages for both the SQL Server and Red Hat Linux communities.

Join this webinar by experts from Red Hat and Microsoft as we cover:

  • The capabilities of Microsoft SQL Server on Red Hat Enterprise Linux and Red Hat OpenShift Container Platform
  • Advantage of running Red Hat OpenShift Container Platform on-premises and in Microsoft Azure
  • A demo of application compatibility between SQL Server on Windows and SQL Server on Red Hat Enterprise Linux

Register now to attend. The webcast takes place Tuesday, July 11, 2017 at noon EDT / 9 a.m. PDT.

Speakers

Don Schenck
Director, Developer Experience, Red Hat
Don Schenck, Red Hat director of Developer Experience, focuses on Microsoft .NET on Linux. His mission is to bring .NET developers into the Linux and open source communities. Before joining Red Hat, Don was a developer advocate at Rackspace. He is the author of “Transitioning to .NET Core on Linux,” an O’Reilly book.

Vin Yu
Program manager, Microsoft
Vin Yu, Microsoft program manager, is on the SQL Server team, where he works with developers and companies adopting SQL Server on Linux. Vin is also interested in improving developer experiences, database monitoring, and tools.

Try new SQL Server command line tools to generate T-SQL scripts and monitor Dynamic Management Views

This post was authored by Tara Raj and Vinson Yu, Program Managers – SQL Server Team

We are excited to announce the public preview availability of two new command line tools for SQL Server:

  • The mssql-scripter tool enables developers, DBAs, and sysadmins to generate CREATE and INSERT T-SQL scripts for database objects in SQL Server, Azure SQL DB, and Azure SQL DW from the command line.
  • The DBFS tool enables DBAs and sysadmins to monitor SQL Server more easily by exposing live data from SQL Server Dynamic Management Views (DMVs) as virtual files in a virtual directory on Linux operating systems.

Read on for detailed usage examples, try out these new command line tools, and give us your feedback.

mssql-scripter

Mssql-scripter is the multiplatform command line equivalent of the widely used Generate Scripts Wizard experience in SSMS.

You can use mssql-scripter 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 save the generated T-SQL script to a .sql file or pipe it to standard *nix utilities (for example, sed, awk, grep) for further transformations. You can edit the generated script or check it into source control and subsequently execute the script in your existing SQL database deployment processes and DevOps pipelines with standard multiplatform SQL command line tools such as sqlcmd.

Mssql-scripter is built using Python and incorporates the usability principles of the new Azure CLI 2.0 tools. The source code can be found on Github at https://github.com/Microsoft/sql-xplat-cli, and we welcome your contributions and pull requests!

Get started with mssql-scripter

Install
$pip install mssql-scripter
For additional installation tips, visit https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/installation_guide.md.

Script Your First Database Objects
For usage and help content, pass in the -h parameter, which will also show all options:
mssql-scripter -h

Here are some example commands
# Generate DDL scripts for all database objects (default) in the Adventureworks database and output to stdout
$ mssql-scripter -S localhost -d AdventureWorks -U sa

# Generate DDL scripts for all database objects and DML scripts (INSERT statements) for all tables in the Adventureworks database and save the script to a file
$ mssql-scripter -S localhost -d AdventureWorks -U sa –schema-and-data  > ./adventureworks.sql

# generate DDL scripts for objects that contain “Employee” in their name to stdout
$ mssql-scripter -S localhost -d AdventureWorks -U sa –include-objects Employee

# Change a schema name in the generated DDL script on Linux and macOS and bash in Windows 10.
# 1) Generate DDL scripts for the Adventureworks database
# 2) Pipe results and change all occurrences of SalesLT to SalesLT_test using sed, and save the script to a file
$ mssql-scripter scripter -S localhost -d Adventureworks -U sa | sed -e “s/SalesLT./SalesLT_test./g” > adventureworks_SalesLT_test.sql

DBFS

A big part of operationalizing SQL Server is to monitor to ensure that SQL Server is performant, highly available, and secure for your applications. With SQL Server 2017, Dynamic Management Views (DMVs) on Windows are also accessible on Linux, allowing your existing scripts and tools that rely on DMVs to continue to work. Traditionally, to get this information, you would use GUI admin tools such as SSMS or command line tools such as SQLCMD to run queries.

Today, we are also introducing a new experimental Linux tool, DBFS, which enables you to access live DMVS mounted to a virtual filesystem using FUSE. All you need to do is view the contents of the virtual files in the mounted virtual directory to see the same data you would see as if you ran a SQL query to view the DMV data. There is no need to log in to the SQL Server using a GUI or command line tool or run SQL queries. DBFS can also be used in scenarios where you want to access DMV data from the context of a script with CLI tools such as grep, awk, and sed.

DBFS uses the FUSE file system module to create two zero byte files for each DMV—one for showing the data in CSV format and one for showing the data in JSON format. When a file is “read,” the relevant information from the corresponding DMV is queried from SQL Server and displayed just like the contents of any CSV or JSON text file.

Features

  • Access data in .json format if you are connected to SQL Server 2016 or later
  • Compatible with Bash tools such as grep, sed, and awk
  • Live DMV data at time of access
  • Works with both SQL Server on Windows and SQL Server on Linux

Notes

  • This tool is currently only available for Ubuntu, Red Hat, and CentOS (SUSE coming soon!).

Next Steps:
See more usage examples and read more about mssql-scripter at https://github.com/Microsoft/sql-xplat-cli and get started with the DBFS today at https://github.com/Microsoft/dbfs.

We are open to suggestions, feedback, questions, and of course contributions to the project itself.

SQL Server 2017 CTP 2.1 now available

Microsoft is excited to announce a new preview for the next version of SQL Server (SQL Server 2017). Community Technology Preview (CTP) 2.1 is available on both Windows and Linux. In this preview, we added manageability features to make it easier to configure SQL Server in Docker containers. We’re also introducing some new command line tools for managing SQL Server in our GitHub repo. And, there’s a preview of SQL Server Integration Services on Linux to try! You can try the SQL Server 2017 preview in your choice of development and test environments now: www.microsoft.com/sqlserver2017.

Key CTP 2.1 enhancements

The primary enhancement to SQL Server 2017 in this release is the ability to configure SQL Server configuration settings through environment variables passed in as parameters to docker run. This enables many of the SQL Server configuration scenarios in Docker containers such as setting the collation.

For additional detail on CTP 2.1, please visit What’s New in SQL Server 2017, Release Notes and Linux documentation.

SQL Server Integration Services on Linux

SQL Server Integration Services now supports Linux for the first time! Today we are also releasing a preview of SQL Server Integration Services for Ubuntu. The preview enables you to run SSIS packages on the Linux OS, extract data from and load it to most common sources and targets, and perform common transformations. It has a simple command line installation. For more information, see our SSIS blog.

Updated SQL Server Tooling

The latest release of SQL Server Management Studio is out! It features improvements to how it works with SQL Server on Linux so make sure you have the latest. In addition, we are excited to announce the public preview availability of two new command line tools for SQL Server:

  • The mssql-scripter tool enables developers, DBAs, and sysadmins to generate CREATE and INSERT T-SQL scripts for database objects in SQL Server, Azure SQL DB, and Azure SQL DW from the command line.
  • The DBFS tool enables DBAs and sysadmins to monitor SQL Server more easily by exposing live data from SQL Server Dynamic Management Views (DMVs) as virtual files in a virtual directory on Linux operating systems.

New lightweight installer for SQL Server Reporting Services (SSRS)

In CTP 2.1, we moved Reporting Services installation from the SQL Server installer to a separate installer. This is a packaging change, not a product change; access to SQL Server Reporting Services is still included with your SQL Server license. The new installation process keeps our packages lean and enables customers to deploy and update Reporting Services with zero impact on your SQL Server deployments and databases.

To learn more about what’s new in SQL Server 2017 Reporting Services preview, read our Reporting Services Release Notes. To download the latest preview in the new lightweight installer, go to https://aka.ms/ssrs2017download

To learn more about the recent announcement of a Power BI Report Server preview, which includes the capabilities of SQL Server 2017 Reporting Services and support for Power BI reports, you can read this blog article.

Get SQL Server 2017 CTP 2.1 today!

Try the preview of the SQL Server 2017 today! Get started with the preview of SQL Server with our updated developer tutorials that show you how to install and use SQL Server 2017 on macOS, Docker, Windows, and Linux and quickly build an app in a programming language of your choice.

Have questions? Join the discussion of SQL Server 2017 at MSDN. If you run into an issue or would like to make a suggestion, you can let us know through Connect. We look forward to hearing from you!

SQL Server 2017 on Linux surpasses 1 million Docker pulls as the next preview version rolls out

This post was authored by Rohan Kumar, General Manager, Database Systems Group

SQL Server 2017 makes it easier and simpler to work with data, with more deployment options than before and monthly preview releases offering regular innovation and improvements. The momentum behind these new options is clear. We are excited to mark a new milestone: Last week, SQL Server on Linux passed 1 million pulls of its container image! The image has been on Docker Hub for the six months since we first launched the SQL Server on Linux public preview in November 2016, with steadily growing customer use. In fact, we now have customers like dv01 going into production with SQL Server 2017 in Docker containers using the production support agreement from our Early Adoption Program (EAP). The container image is also available in the Docker Store, where it’s currently one of the featured images.

Customer interest in containers is high because of the benefits for production, and especially development and test: consistent and reliable behavior across environments, in a lightweight and easy- to-use format. Containers are fast to set up, can easily be stopped and started, and give users the ability to spin up multiple containers together using tools like docker-compose to easily start and interconnect database, application, and other services containers in a micro-services architecture.

SQL Server on Linux containers has been tested extensively in our test lab over the course of SQL Server 2017 public previews. We have been deploying SQL Server on a 150-node Kubernetes cluster in Azure to test each successive monthly Community Technology Preview (CTP). For each test pass, we automatically deploy 750 containers and run over a million tests. In addition to Kubernetes, we are testing on other container platforms with our partners and the community, including Red Hat OpenShift, Docker Swarm, and Mesosphere DC/OS.

Financial technology startup cuts database management time by 90 percent

Customers are already adopting SQL Server in containers. dv01 is a Wall Street startup, offering a reporting and analytics platform to institutional investors interested in greater insight into consumer lending markets. dv01 had initially based its solution on PostgreSQL and Amazon Redshift, but moved to SQL Server 2016 in Windows Azure Virtual Machines for faster query response times and scalability as its data grew. Because the firm runs all its other workloads on Linux, dv01 signed up for the Early Adoption Program for SQL Server 2017 to get Microsoft advice and assistance on migrating its solution to SQL Server on Linux. This move will help the company avoid managing multiple operating systems within its environment. It opted to deploy the application to production on Docker Engine, using a SQL Server 2017 on Linux image. Its choice to implement SQL Server and Docker containers has cut database management time by 90 percent, freeing its development team to focus on adding new capabilities to the product. To learn more about dv01’s SQL Server 2017 journey, you can read its story here.

“SQL Server 2016 offered the combination of performance and scalability that we needed,” said Dean Chen, VP of Engineering, dv01. “Expensive queries that were taking 30 seconds or more with our previous system now take 1-2 seconds, which means we’re able to do analytics queries in close to real time for our users.”

Making SQL Server on a Linux Docker container easy

With SQL Server 2017 CTP 2.1, available today, we continue to add to the manageability features for SQL Server on Linux Docker containers. We have introduced the ability to configure the SQL Server configuration settings through environment variables passed as parameters to docker run. This enables some of the most common SQL Server configuration scenarios in Docker containers, such as setting the server collation when creating a new SQL Server instance in a container. If you’d like to learn more about the SQL Server 2017 CTP 2.1 release, read our detailed blog for information on the other enhancements and how to get started with the preview.

We want to make it as easy as possible to get started with this technology. If you’d like to learn about how to get started with building a data-centric CI/CD pipeline using SQL Server on Linux containers, join SQL Server engineers Travis Wright and Tobias Ternstrom for this how-to video from the Microsoft Build event for developers.

Reasons to consider running SQL Server in containers

In many ways, container technology is at an inflection point much like hypervisors were 15 years ago. The benefits are immense and increasing every day and include the following:

  • Reduced size on disk for better hardware utilization
  • Reduced CPU/memory consumption, which also results in better hardware utilization
  • Reduced deployment size for faster deployments and scale up/down
  • Reduced patching for less effort, less vulnerability, less down time
  • Better composability using layers of Images, applications defined as multiple containers
  • Easier sharing with Docker Hub and Registry

But in some cases, there are still areas for improvement. For example, configuring high availability in a container platform is not well defined yet. Persistence to local and remote storage is still relatively new and is a complex area of any container platform. Because containers are still new, finding people that are experienced in working with containers can be a challenge. We look forward to working with the community to expand on and refine the capabilities of container platforms in the months to come.

The road ahead for SQL Server in containers

We are targeting support for SQL Server on Linux containers by General Availability of SQL Server 2017 later this year. Customers in our Early Adoption Program can deploy into production on containers right now with full support of our support and engineering teams. We have created a GitHub repository called mssql-docker where you can get Dockerfiles, example entrypoint scripts, and provide us with feedback and feature requests. It’s also a great place to engage with other people running SQL Server in containers.

We are also working on testing SQL Server in Windows containers, including SQL Server 2016 SP1 Developer and Express editions and SQL Server 2017 Evaluation edition. The Windows container images are available now on Docker Hub for testing and experimentation as well.

Thanks again to our community for your interest in and support for SQL Server in containers. We look forward to your continued feedback.

–Rohan Kumar, General Manager, Database Systems Group

Keeping freight moving in Denmark with SQL Server 2016

Danske Logo

Efficient data management keeps goods flowing smoothly in Denmark. Danske Fragtmaend, the country’s largest national transport and distribution firm, has been moving freight for more than a century. Today, Danske Fragtmaend delivers more than 40,000 consignments each day throughout Denmark, and businesses from small mom-and-pop operations to factories rely on its services.

The firm handles logistics in a central location, where 200 dispatchers keep an eye on the movement of thousands of trucks and their cargo. Both drivers and dispatchers need the latest information to operate efficiently, so they rely on a data platform based on SQL Server 2016. The storage system includes 160 terabytes of flash memory for fast I/O and high uptimes. Throughout the day, drivers continually scan transactions with PDAs and send shipping information including GPS coordinates to the data platform. Fast access to information is essential. Ulf Preisler, chief information officer at Danske Fragtmaend, says, “When it comes to short-term logistics, you’ve got to think like an air traffic controller more than a traditional radio dispatcher.”

Because the data changes rapidly, asynchronous replication between geographically disparate datacenters was inadequate. Instead, Danske Fragtmaend runs SQL Server on Windows 2016. Windows Server 2016 introduces a new disaster recovery and preparedness feature, Storage Replica, which enables storage-agnostic, synchronous replication of data across geographically diverse datacenters. Even if disaster strikes one location, all the data exists elsewhere, so there is no possibility of loss.

27.11.2008.Modulvogntog

Best of all, companies that combine flash storage with the latest versions of SQL Server and Windows Server can achieve a multiplying effect on performance. Danske Fragmaend’s lead software developer, Morten Vinther, ran several tests to compare the old storage stack with the new one. “After combining the new all-flash infrastructure and the features from SQL Server 2016 on Windows Server 2016, one of our BI queries ran 9,521 times faster than on the prior infrastructure. That is much more than we expected.”

To find out more about Danske Fragtmaend’s SQL Server 2016 implementation, read the customer story.

Customer Name: Danske Fragtmaend
Industry: Transportation and logistics
Country or Region: Denmark
Customer Website: www.fragt.dk
Employee Size: 900