Microsoft is trying to close the divide between the worlds of software development and database programming, offering better SQL support in Visual Studio Code, and extending more SQL Server Enterprise features for developers.
There are few applications these days that don’t work with data but traditionally — certainly in the Microsoft world — database developers have been a completely different category from general developers.
In fact, the term “database developer” can be code for a database admin who also writes scripts and services that connect to databases.
That divide between developers who need to access data in applications and database developers who also create apps makes Microsoft’s database technologies look less relevant in today’s mobile and cloud world.
SQL Server 2016 adding JSON support and R Services is one way Microsoft is aiming to bring these two worlds closer together. R Services inside SQL Server is now getting new machine learning and deep neural network functionality. That brings “increased speed, performance and scale, especially for handling a large corpus of text data and high-dimensional categorical data,” according to Microsoft CVP for data products, Joseph Sirosh. There are R examples and machine learning templates for SQL Server on GitHub.
There’s also the Hadoop integration enabled by Polybase — with the 2016 release, that became a feature in SQL Server Standard Edition, so it is no longer something you have to buy the Analytics Platform System to get.
Being able to create external tables in SQL that point to a Hadoop cluster and query that as if it was just another table in a data warehouse makes it much more accessible to a database developer who is familiar with SQL. They can query Hadoop clusters running on multiple platforms (including unstructured Azure Blob storage) directly using T-SQL instead of having to learn Hive. At best Hive is different; at worst, it has a number of restrictions and doesn’t support all the query types you’re used to in SQL.
Microsoft is also approaching this from the other end, with better tools for working with SQL Server for general developers. Sirosh promised “improved developer experiences on Windows, Mac and Linux for Node.js, Java, PHP, Python, Ruby, .NET core and C/C++. Our JDBC Connector is now published and available as open source which gives developers more access to information and flexibility on how to contribute and work with the JDBC driver. Additionally, we’ve made updates to ODBC for PHP driver and launched a new ODBC for Linux connector, making it much easier for developers to work with Microsoft SQL-based technologies.” That’s on top of updating SQL Server Management Studio and the SQL Server Data Tools so they work with SQL Server on Linux.
There’s also a new official SQL plugin for Visual Studio Code, to let database developers use the new tools Microsoft is bringing out — which enable SQL development on Linux and Mac as well as on Windows.
“We’re bringing out a VS Code plugin for SQL with updated connectors and tools because we want to make this more seamless,” Microsoft’s Mitra Azizirad told InApps. “We want to make it more seamless for developers to build with SQL Server and Azure SQL Database and Azure Data Warehouse. Being able to connect to SQL Server, including SQL Server on Linux, from VS Code will, make it easier to work with. Azizirad is the CVP of what’s now the developer and data division.
“Data has come into my cloud app developer platform team,” she told us, “and that’s representative of a cultural shift internally around applications, and understanding that data is a core part of any application. We recognize that we’re moving from what might be considered data-aware apps to truly data-driven intelligent applications. If you want these immersive experiences, where you’re making reason out of the data, in real time, with predictive analyses and having context awareness, you don’t do that without data. You can’t have that immersive experience without data. So we really need to be reaching out to data developers and all developers to make it easier.”
There are a handful of SQL extensions for Visual Studio Code already, but the most popular only supports MySQL and the others only let you connect to SQL Server. Being able to edit and even execute T-SQL from VS Code makes it a powerful development tool for a whole new audience — which means those database developers can more easily work with all of the other languages and patterns VS Code supports.
You can connect to SQL Server running on-premises on Linux, Windows or docker in macOS, or in any cloud, to Azure SQL Database, and to Azure SQL Data Warehouse. Connection Profiles help you manage connections to multiple databases and set advanced properties (like the TCP port and connection timeout); you can use the standard pick-list in VS Code to switch the active database connection.
Once you’re connected, you can open existing .sql files or create new T-SQL statements and queries (both DML and DDL) in the T-SQL editor in VS Code. The editor gives you T-SQL colorization, context-aware schema autocomplete, syntax & schema error diagnostics, and T-SQL code snippets. Error diagnostics show up as red squiggles in the editor and as error messages in the error list tool window.
You can run T-SQL queries right from VS Code (either just the selected text or everything you have in the editor) and see results and messages in the result preview window. You can have multiple result-sets; they’re shown in a stacked, collapsible results pane, And you can export the results as CSV or JSON files.
“Visual Studio itself has rich integration with SQL Server and so it stands to reason to begin to take some of this integration to VS Code,” IDC research director Al Hilwa told us. “SQL Server has always been a widely deployed relational engine with a fair amount of use from other development environments and languages, so this makes sense given that VS Code is aimed at a broad developer audience.”
Microsoft is also about to announce SQL Server 2016 SP1, which will bring more of the new features in this version to the Standard Edition, so that developers have a single programming model to work with, across all the editions — a long-standing request. “This means ISVs, partners and developers can build to a single app programming surface when they create their applications, and then use the edition that scales to the application needs,” said Azizirad.
The Developer Edition has always had the full set of Enterprise Edition features to develop against, but now you’ll get in-memory OLTP and in-memory columnstore analytics and partitioning for deployment in Standard and even the Express editions.
Adding basic high availability, with a 2-node single database failover and a non-readable secondary means you can create more robust systems. Getting the ‘always encrypted’ option in Standard Edition is a big security improvement; developers can access an encrypted database from an ASP.NET website without needing to decrypt it and only trusted apps with the column master key can decrypt the data. That makes using the stretch database feature — moving data you don’t access as frequently to Azure Database — which was already a feature in the Standard Edition, much more appealing.
Microsoft is announcing a slew of other database technology at its Connect event, from the general availability of Azure Data Lake — both the big data processing and the analytics services — R Server for HDInsght and Operational Analytics for Azure SQL Database, to a local emulator of DocumentDB, to the public preview of SQL Server on Linux. These tools are about making those relevant to a much wider range of developers.
Feature image: Microsoft’s Scott Guthrie, on stage at Connect 2016.
