SSMS 17.8 is now available

This post is co-authored byPam Lahoud, Senior Program Manager, SQL Server.

We are excited to announce the release of SQL Server Management Studio (SSMS) 17.8!

Download SSMS 17.8 and review the Release Notes to get started.

SSMS 17.8 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.8 comes with several new features:

  • Database Properties | FileGroups: This improvement exposes the “AUTOGROW_ALL_FILES” configuration option for Filegroups.
  • SQL Editor: Improved experience with Intellisense in SQL Azure DB when the user lacks master access.
  • Scripting: General performance improvements, especially over high-latency connections.
  • Bug Fixes

View the Release Notes for more information.

Database Properties | FileGroups:

In this release of SQL Server Management Studio, we have introduced UI and scripting support for the AUTOGROW_ALL_FILES database filegroup property. This property was introduced in SQL Server 2016 to replace trace flag 1117, but it was only settable via T-SQL script. Now you can set the property via a checkbox in the Database Properties -> Filegroups page:

You can also use the Script button to script out the change:

This will generate the following script:

USE [AdventureWorks2016_EXT]

GO

declare @autogrow bit

SELECT @autogrow=convert(bit, is_autogrow_all_files) FROM sys.filegroups WHERE name=N'PRIMARY'

if(@autogrow=0)

ALTER DATABASE [AdventureWorks2016_EXT] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES

GO

Scripting support has also been added to the Generate Scripts task and the Script Database as context menu in the Object Explorer:

Bug fixes:

In SSMS 17.8, there were many bug fixes.

To highlight a few of these:

  • Maintenance Plans.
    Fixed an issue when editing maintenance plans with Sql Authentication where “Notify Operator Task” was failing when using SQL authentication.
  • Scripting.
    Fixed an issue where PostProcess actions in SMO lead to resource exhaustion and SQL login failures.
  • SMO.
    Fixed an issue where Table.Alter() fails if adding a column with a default constraint and the table already has data. More details available here.
  • Always Encrypted.
    Fixed an issue (in DacFx) which was causing a lock timeout error when enabling Always Encrypted on a partitioned table.

To learn more about other bug fixes covered in this release, check the Release Notes.

Call to action

Try it out and let us know what you think! You can message us on our Twitter @sqldatatools or reach out to Ken Van Hynings twitter @sqltoolsguy.

The June release of SQL Operations Studio is now available

We are excited to announce the June release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our GitHub.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this June release is the seventh major update since the announcement. If you missed it, the May release announcement can be viewed here.

The June public preview release is focused on improving our Extensibility experience with the release of new extensions as well as addressing top GitHub issues.

Highlights for this build include the following.

  • SQL Server Profiler for SQL Operations Studio Preview extension initial release
  • Azure SQL Data Warehouse extension
  • Edit Data Filtering and Sorting
  • SQL Server Agent for SQL Operations Studio Preview extension enhancements for Jobs and Job History views
  • Build your own SQL Ops Studio extension
  • Visual Studio Code Refresh
  • Fix GitHub Issues

For complete updates, refer to the Release Notes.

SQL Server Profiler for SQL Operations Studio Preview

The SQL Server Profiler for SQL Operations Studio Previewextension is now available in the Extension Manager. This is the initial preview release for a new lightweight XEvent-based profiler. The SQL Server Profiler extension makes it simple to quickly trace server activity for troubleshooting and monitoring.

Ifyou want to try out the SQL Server Profiler extension, follow these instructions:

  1. Install the extension from the Extensions Manager.
  2. After you reload SQL Operations Studio, press Ctrl+Shift+P or open the Command Palette from the Gear icon on the bottom left.
  3. Type Profiler and press Enter on Profiler: New Profiler
  4. Press start to see your Extended Events (view gif below)

We’ll continue to enhance this extension over the next couple releases. This is still a preview version and we would love to hear initial feedback from the community.

Shortly after this blog publishes, an issue will be opened in GitHub issues where we will share our design process for each screen for SQL Server Profiler. We welcome community input there as we work on improving SQL Server Profiler in SQL Operations Studio.

Azure SQL Data Warehouse Extension

Last month, the Azure SQL Data Warehouse team blogged on creating a customizable management experience with SQL Operations Studio. Instead of asking new users to manually add JSON snippets in Settings, we reached out to the DW team to see if they would be interested in building an extension to improve acquisition for users. After working with us on building a custom insight extension, the team is excited to announce the preview release of the Azure SQL Data Warehouse Extension! You can now seamlessly install the extension from the SQL Operations Studio Extensions Manager surfacing a pre-built dashboard for your data warehouse.

Once you install the extension, you simply make a connection to your SQL DW instance, view the server dashboard, and you can see the extension as a tab.

Insight widgets are generated by using T-SQL scripts embedded within SQL Operations Studio. All monitoring scripts are uploaded to the following GitHub repo: SQL Data Warehouse samples.

We are looking to improve this extension and would love to hear your feedback to improve this extension. Please feel free to contribute or provide feedback.

Edit data filtering and sorting

Edit data on a table opens the top 200 rows by default. To modify the query, click on the “Show SQL Pane” toolbar button to open the query pane. The query can be modified in the SQL editor pane to filter or sort the resultset. Check out the gif below to see this feature in action.

Build your own extension

In the last couple releases, you may have noticed how our Extensions Manager has grown with extensions created by Azure SQL Data Warehouse, Redgate, and community members. We want to continue to work with the community and our partners to create SQL Operations Studio extensions.

If you want to get started, you can learn to build a sample insight extension through our Extension Guide we created for Microsoft Build.

You can further learn how the SSMS keymap extension was created by following Kevin Cunnanes blogs:

Both the Extension Guide and Kevins blog posts use the generator-sqlops yeoman generator to help make it even easier to make an extension.

If any of these extensions in this generator interest you, feel free to try it out! If you are interested in hosting your extension in our Extensions Manager or would like help building an extension, feel free to reach out to the team on GitHub or email alayu@microsoft.com.

Our team also brought in Wizard and Dialog extensibility support, so feel free to reach out to us if you want to learn more and add wizards and dialogs to your extension. You can learn more here.

Want to learn more about our roadmap for extensions? Learn from our Microsoft build session if you missed it!

Visual Studio Code refresh

Since SQL Operations Studio forks from Visual Studio Code, our team periodically includes refreshes based on latest VS Code monthly releases. This usually brings nice features for the editor and configuration.The latest refresh picks up VS Code v1.23, which includes the March and April 2018 release.

For more information, check the VS Code release notes.

Fix GitHub issues

Here is a summary of issues addressed:

  • #1204 Feature request: Please make the results grid auto-fit column width to data, and/or remember manual changes if the same query is re-run
  • #1398 Should show add message and add account button when linked account is empty
  • #1399 Linked account tab is broken when the view is collapsed
  • #1374 SQL Tools Service crashes when opening .sql file from disk
  • #1372 Missing SQL keyword “BETWEEN”
  • #1395 ‘MATCH’ keyword crashes SQL Tools Service
  • #1496 “New Profiler” context menu option in Object Explorer does nothing.
  • #1495 Query editor “Explain” query plan is broken

Contact us

If you have any feature requests or issues, please submit to our GitHub issues page. For any questions, feel free to comment below, message us on Gitter, or tweet us @SQLOpsStudio.

SQL Server Automatic Tuning around the world…

..and in the cloudsWhen I first saw early builds of SQL Server 2017, one of the features that caught my eye immediately was Automatic Tuning with an option called Automatic Plan Correction. SQL Server 2017 was released in October of 2017 right on the heels of one of my favorites releases, SQL Server 2016. In SQL Server 2016, we brought to the product a new feature called Query Store. When Query Store is enabled for a database via ALTER DATABASE, the SQL Server engine will start collecting query performance telemetry in memory and system tables in the database. No longer do you need to poll Dynamic Management Views and store them into your own tables. This performance telemetry is collected by the SQL Server engine itself when queries are compiled and executed.

Like many features we build into SQL Server, you can use T-SQL queries to find out the details of this performance data through a series of catalog views. Query store opens up all types of cool performance insights and we have documented some of the key usage scenarios. One of these scenarios is called a query plan regression (also known as plan choice regressions).

Imagine this scenario. You have a stored procedure that takes a single integer parameter. This integer parameter is used in the WHERE clause of a SELECT statement in the stored procedure. The first time the stored procedure is compiled, the plan for this procedure is inserted into cache based on the value of the first execution of the procedure. And this plan may be a good plan for most users. Now for unexpected reasons, perhaps memory pressure, the plan is evicted from cache. Lets say a user then executes the procedure through an application but this time with a different integer parameter value. This could result in a different query plan that leads to poor performance. Compiling a plan for a stored procedure based on the parameter value is called parameter sniffing. This concept is discussed in our Query Processing Architecture Guide in the documentation (which in itself is a cool read). Parameter sniffing is designed to be a good thing, but in some situations where the data in the table associated with the parameter is skewed, a performance problem could occur.

So, in SQL Server 2016, you can use our reports in SQL Server Management Studio or run queries against Query Store catalog views to see whether a query plan regression has caused a performance problem. Now comes along SQL Server 2017 with some automation. Why not bake into the engine some automation behind the rich telemetry of Query Store? Turns out the folks in our engineering team that own the Query Store feature were already working on these kinds of features in the cloud for Azure SQL Database. Using our cloud-first approach for engineering we started working on these features in Azure, tested and verified their functionality, and then brought them to SQL Server 2017.

SQL Server 2017 on Windows

The approach we took for SQL Server 2017 for Automatic Tuning is recommended then automate. There is no better way to show you how this works then with a demo. Watch this video on our SQL YouTube channel about how Automatic Tuning works on SQL Server 2017 on Windows. I use the popular WideWorldImporters sample database for this demonstration.

Want to run the demo yourself? Download it from my GitHub repo.

Almost everyone Ive shown this demo to has been amazed. My recommendation to use this feature is the following:

  • Enable Query Store and configure it to your needs. See our documentation on best practices.
  • Monitor any recommendations we have for you by examining the Dynamic Management View (DMV), dm_db_tuning_recommendations.
  • If you are comfortable with our recommendations, experiment with turning on Automatic Plan Correction using this T-SQL syntax.
ALTER DATABASE current

SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
  • Whetheryou just look at recommendations or use automation, I always recommend you find the cause of the query plan regression problem and take more long-term corrective action.

SQL Server 2017 on Linux

One of the key points Ive been making to our customers about SQL Server on Linux is that the core database engine is the same as on Windows. Since Automatic Tuning is built into the core database engine, this feature works exactly the same on SQL Server on Linux.

As you watched my demo of Automatic Tuning for SQL Server on Windows, I used the Windows Performance Monitor to show the workload performance when automatic tuning corrects a query plan regression problem. But what about Linux? Windows Performance Monitor will not work with that platform.

Turns out I have some smart people working with me at Microsoft. I got help from Pedro Lopes from our Tiger Team and the engineering team who built SQL Operations Studio to show automatic tuning using that tool.

  • Pedro helped me build scripts that would query the DMV, sys.dm_os_performance_counters, which runs on SQL Server on Linux as it does on Windows (because it is built into the core engine). These scripts store results from this DMV in a temp table, and then query the temp table.
  • The folks at SQL Operations Studio showed me how to collect the result set from the temp table in a manner that would allow me to show the data in a Time Series chart in the tool. Kind of like a static perfmon chart of SQL Server counters.

The result is the same demo for Automatic Tuning I did for SQL Server on Windows but run against SQL Server on Linux. How did I get the WideWorldImporters sample database to work against Linux for this demo? I just downloaded it from the web into my Linux server and restored it!

The following is an example of the chart from SQL Operations Studio after Automatic Tuning corrected a query plan regression problem from the demo:

Check out this demo of Automatic Tuning on SQL Server on Linux from this video on our SQL Server YouTube channel.

You can grab the demo scripts here.

Azure SQL Database Managed Instance

Ive now tuned the world of SQL Server instances on Windows and Linux. Why not tune the cloud as well? I have been speaking at many customer events boasting that we have a great consistency story with SQL Server including the cloud. This story includes features, functionality, tools, and the T-SQL language across SQL Server and our Azure Data Services. One of these services that was just announced is Azure SQL Database Managed Instance. One of the promises about this service is that I can just take a database backup from SQL Server and restore it to Azure (can you see a pattern here for compatibility? Backup, Restore, and done!).

A quick search on the web led me to this article.Seems pretty simple. Watch the video to see how I restored my WideWorldImporters backup and then ran the same Automatic Tuning demo as I did for SQL Server on Linux.

Automatic Tuning in the real world

Does this feature really make a difference? Do any customers use this? I recently heard from one of the leading SQL Community leaders, Tracy Boggiano from Channel Advisor, on the topic. auto plan correction in query store is awesome we are seeing a 20% reduction on CPU utilization on 98% of our servers. Automatic Tuning is not just a shiny new feature. It works!

I think I accomplished my mission. I wanted to see how Automatic Tuning works with SQL Server everywhere it runs. And because of our great compatibility story I was able to do this with SQL Server on Windows, Linux, and in the clouds with Azure SQL Database Managed Instance all with the same database backup.

If you want to read more about Automatic Tuning check out our documentation.

Subscribe to our new SQL Server YouTube channel to keep up with more great videos and demos about SQL Server.

Microsoft releases the latest update of Analytics Platform System

Microsoft is pleased to announce that the Analytics Platform System (APS) appliance update 7 (AU7) is now generally available. APS is Microsofts scale-out Massively Parallel Processing (MPP) system based on SQL Server for data warehouse specific workloads on-premises.

Customers will get significantly improved query performance and enhanced security features with this release. APS AU7 builds on appliance update 6 (APS 2016) release as a foundation. Upgrading to APS appliance update 6 is a prerequisite to upgrade to appliance update 7.

Faster performance

APS AU7 now provides the ability to automatically create statistics and update of existing outdated statistics for improved query optimization. APS AU7 also adds support for setting multiple variables from a single select statement reducing the number of redundant round trips to the server and improving overall query and ETL performance time. Other T-SQL features include HASH and ORDER GROUP query hints to provide more control over improving query execution plans.

Better security

APS AU7 also includes latest firmware and drivers along with the hardware and software patch to address the Spectre/Meltdown vulnerability from our hardware partners.

Management enhancements

Customers already on APS2016 will experience an enhanced upgrade process to APS AU7 allowing a shorter maintenance window with the ability to uninstall and rollback to a previous version.
AU7 also introduces a section called Feature Switch in configuration manager giving customers the ability to customize the behavior of new features.

Flexibility of choice with Microsofts data warehouse portfolio

The latest update is an addition to already existing data warehouse portfolio from Microsoft, covering a range of technology and deployment options that help customers get to insights faster. Customers exploring data warehouse products can also consider SQL Server with Fast Track for Data Warehouse or Azure SQL Data Warehouse, a cloud-based fully managed service.

Next Steps

For more details about these features, please visit our online documentation.

Cloud data and AI services training roundup May 2018

To help you stay up to date on online training opportunities, were releasing a monthly list of the latest free Data and Artificial Intelligence (AI) sessions in one convenient post.

SQL Database

Azure SQL Database is the intelligent, fully managed relational cloud database service that provides the broadest SQL Server engine compatibility, so you can migrate your SQL Server databases without changing your apps. Accelerate app development and make maintenance easy and productive using the SQL tools you love to use. Heres a rundown of recent and upcoming training sessions to help you learn more.

Upgrade Your Data Estate to the New Azure SQL Database Managed Instance

Getting ahead means embracing digital transformation to leverage the cloud. As you take your first steps toward modernization, its critical to ensure that your IT operations are optimized to effectively manage growth. Our new fully managed relational database-as-a-service solution lets you lift and shift your SQL Server instances with no application code changes. Register for this session to learn how to start your migration journey.

Optimize Your Performance with Azure SQL Database

If you are looking to take steps to optimize your database performance at minimal cost, this session is for you. We highlight some of the capabilities of Azure SQL Database that will help with this process, including (1) dynamic resource scaling: understanding how to scale when needed, (2) database performance features: in-memory technologies designed to help you make the most of your existing resources, and (3) tips for intelligent monitoring and tuning. Before you pay for more resources, watch this session to ensure youre getting the most out of the ones you already have.

Intelligence (AI)

Infuse your apps, websites, and bots with intelligent algorithms to see, hear, speak, understand, and interpret your user needs through natural methods of communication. The Microsoft AI platform offers a comprehensive set of flexible AI services for any scenario and enterprise-grade AI infrastructure that runs AI workloads anywhere at scale.

Develop Bots with Microsofts Bot Builder SDK

Learn to build a great conversationalist. The Microsoft Bot Framework provides just what you need to build and connect intelligent bots that interact naturally wherever your users are talking, including text/SMS, Skype, Slack, and Office 365 email. Watch this training for demos and details about the Bot Builder SDK component of the framework which provides a familiar way for .NET and Node.js developers to build bots.

Big data and analytics

Deliver better experiences and make better decisions by analyzing massive amounts of data in real time. Get the insight you need to deliver intelligent actions that improve customer engagement, increase revenue, and lower costs.

How Big Data Gives Your Applications an Edge

Learn to take advantage of relational and non-relational data created by applications, as well as data available through third parties, marketplaces, and social applications. In this demo-heavy training session, youll get a close look at Azures big data and advanced analytics capabilities. Watch this session and well show you how to build a big data processing pipeline to ingest and process massive volumes of data, extract business insights from unstructured data and apply real-time analytics to get instant updates.

SQL Server at the Red Hat Summit 2018

This blog post is authored byBob Ward, Principal Architect, Microsoft; Travis Wright, Principal Program Manager, Microsoft; and Jamie Reding, Senior Program Manager, Microsoft.

A few weeks ago, developers from around the world gathered for the Microsoft Build Conference. It was an amazing display of Microsofts products and cloud services to meet the needs of all types of applications. I missed the //build event this year because I found myself in San Francisco at the Red Hat Summit 2018. I cant even imagine five years ago someone telling me I would represent Microsoft and SQL Server at an open-source based event.

Travis Wright, Jamie Reding, and I travelled to the event to speak and show amazing demos of SQL Server running on Red Hat Enterprise Linux (RHEL) and OpenShift. We were part of the Microsoft team attending the event to continue to show the great partnership we have and are building with Red Hat. Get an overview of the Microsoft presence at the summit from Robin Ginns blog post.

This was not the first time for Travis or Jamie attending this event. Microsoft demonstrated SQL Server on Linux at the Red Hat Summit 2016. Travis and Jamie both presented at the 2017 Summit for SQL Server on RHEL and Open Shift. I thought it would be interesting to write about our experiences and what we learned at this years Summit.

The presentation Travis and I gave on SQL Server was rich with demos. We worked hard on these demos, so we thought why not show everyone, not just the people at the Summit. We have packaged them all on the brand-new SQL Server YouTube channel. Ive included links to these videos throughout the rest of this blog post.

Its no longer Why are you here?

As the event ended, I asked Jamie and Travis what was one of the key differences between previous Summit events and this year. Travis summarized it as Customers at the booth have switched from asking us Why are you here? and What is SQL Server? two years ago to specific questions about running SQL Server on RHEL/OpenShift.”

The most common question we received was How do you install it? I think I must have demonstrated at our booth 20 or more times the SQL Server deployment experience, along with the great set of tools we have available for SQL Server on Linux including SQL Server Operations Studio and mssql-cli. Check out this video that shows the complete deployment experience on RHEL as well as a survey of our tools. Cross-platform and open-source tools is how we roll these days!

Is it the same as SQL Server on Windows?

Part of the above video shows how to restore a database and connect with SQL Server tools. Linux admins and architects want to be more comfortable with how we integrate with Linux, and I met many folks at the Summit who didnt know much SQL Server but knew Linux. Many of their colleagues who manage SQL Server in their environment couldnt attend so they wanted to know if SQL Server is the same as on Windows?

My first answer to this question was Tell your SQL friends to install SQL Server on Linux, backup their database on Windows, and just restore it on Linux. Then fire up SQL Server Management Studio (SSMS), connect, and start running queries. When I showed this simple experience, it made the Linux folks incredibly happy. They saw our deployment experience aligned with package managers like yum. But they also could tell their SQL Server colleagues Hey, its pretty much just SQL Server. Since many of them didnt use Windows, I then showed them SQL Operations Studio and mssql-cli which runs native on Linux and macOS as well as Windows. I heard comments like Now that is impressive.

We used these opportunities to talk beyond just the basics. We showed customers how fast SQL Server can be with technologies like Columnstore Indexes. Check out this demo with PowerBi and Columnstore.

We showed new SQL Server 2017 capabilities of intelligence performance like Automatic Tuning powered by Query Store. Check out this demo which includes built in performance telemetry through SQL Server Dynamic Management Views and charting capabilities in SQL Operations Studio.

We also talked about security and authentication. SQL Server supports both SQL Server authentication and integration with Active Directory. Watch this demo of SQL Server connecting with Active Directory Authentication.

What about new capabilities?

One of the most compelling new features of SQL Server 2016 and 2017 is in database Machine Learning Services. Keeping data associated with data science models and projects together with SQL Server provides security, data freshness, and scalability.
While SQL Server 2017 on Linux supports Native Scoring, support for R and Python did not make it into the release. But we are committed to bringing this type of feature for SQL Server on Linux. Check out this demo to see SQL Server on Linux with Python and Native Scoring with a real-world prediction example.

Does it perform?

As we described the architecture of SQL Server on Linux, the first question most people asked us was Does it perform?. Sometimes it is always best to answer questions like these with data.

The current top two 1TB TPC-H benchmarks are SQL Server 2017 on Linux 1 2. We talked to customers about how SQL Server can scale from your laptop to the biggest servers in the market. And we especially love showing how fast it can run and scale on enterprise-class machines. Check out this demo where Travis Wright shows how SQL Server in Linux can scan billions of rows and run aggregation functions in seconds on an HPE Superdome computer with 12TB of RAM and 480 CPUs.

How does SQL Server support Containers?

I have come to realize over the last few months how popular containers are spreading. It is fast becoming not just an interest, but part of production implementation plans. SQL Server is ready to be a part of this wave. Deploying in a container itself can be an amazingly easy way to get up to speed fast on SQL Server on Linux. In fact, I brought along my MacBook Pro with me and demonstrated to attendees the SQL Server on Mac challenge. For those who know me in the SQL Server community Im sure you fell off your chair over this.

Running a single container is interesting, but to run containers in a production environment, you need something bigger. This is where Kubernetes comes in. And this is also where Red Hats Kubernetes-based system called OpenShift can make a difference. At the Summit, Microsoft announced a new managed OpenShift service in Azure and SQL Server fits right into this offering. Kubernetes and OpenShift have built-in high availability capabilities with shared persistent storage. SQL Server works well within this model. Try it yourself with this tutorial.

Always on Availability Groups (AG) is the flagship feature for SQL Server High Availability. So, we are working on new capabilities for SQL Server to integrate AGs with environments like OpenShift. Watch this demo to see this in action.

Licensing

One last interesting topic from attendees was around licensing and offers. Attendees wanted to know if SQL Server on Linux licensing were the same as with Windows. First, it is important to know the Editions of SQL Server are the same, I found many attendees did not know this:

  • SQL Server Evaluation A full-featured version of SQL Server for evaluation only purposes with a 180-time limit.
  • SQL Server Express A free, entry-level version of SQL Server for learning or building small desktop applications.
  • SQL Server Developer A full-featured version of SQL Server license only for development and testing.
  • SQL Server Standard The basic version of SQL Server for departments and small organizations. Limits exist for this edition compared to Enterprise, but many features previously only available in Enterprise are in Standard today.
  • SQL Server Enterprise The premium version of SQL Server for mission critical and applications that need maximum scalable performance and high-availability.

For a complete breakdown of editions, check out our documentation.

The licensing of these editions is the same as SQL Server on Windows. Licensing for containers was also a big question we saw at the summit and our SQL Server Licensing Guide shows licenses for containers is similar to licensing for virtual machines. There are some unique offers today for customers looking at SQL Server on Linux including Migrating from ORACLE, SQL Server on Linux subscription, and special offer with Red Hat Enterprise Linux.

The experience at the Red Hat Summit for me personally was humbling and rewarding. It is the first time for me to be at a big event where Microsoft was not the central focus. It forced me to work harder explaining to attendees the value of SQL Server on Linux and not assume they already knew SQL Server. I saw a new perspective from customers that want SQL Server to work within the natural ecosystem of Linux, while also not losing the excellent features and tools that have made SQL Server a force in the industry.

We also had an opportunity to meet several engineers from Red Hat. We shared ideas on how to make SQL Server and RHEL a better experience including discussions on performance monitoring and OpenShift.

I look forward to continuing getting the word out and showing off SQL Server on Linux. It was a remarkable achievement to launch last year, but it is the satisfaction of seeing it now in the mainstream of customer conversations that is most rewarding. It is no longer a what conversation. It is now a when project plan.

Get started with SQL Server on Linux or dive deeper with our free Virtual Academy Training for SQL Server on Linux.

A new update for mssql-cli, an interactive CLI, is now available

We have released our second major update for mssql-cli since our public preview announcement in December. You can view the public preview announcement here.

mssql-cli is a new and interactive command line query tool for SQL Server. This open source tool works cross-platform and is part of the dbcli community.

See the install guide to download mssql-cli and get started.

mssql-cli auto-completion in action

In this release (v0.13.0), highlights include:

  • Apt-get and Linux packaging support
  • GDPR Compliance
  • New demo video

Apt-get and Linux packaging support

One of the key issues Linux users run into when setting up mssql-cli for the first time is not having the right version of Python or having to install Python for the first time. We want to make the first experience with mssql-cli painless, thus we added apt-get support in order to package Python with your installation to help improve the acquisition experience.

For full instructions to acquire mssql-cli for each Linux distribution, please check out the Linux installation guide.

Note: For those who already installed mssql-cli via pip install, please run

sudo pip uninstall mssql-cli

Then, follow the installation instructions.

GDPR compliance

As many of us are familiar with, GDPR is approaching and we made some updates. In the past, file history stored entire T-SQL queries. However, if the query contained any secrets or passwords, it wasnt smart enough to scrub those out. This is no longer the case, and now file history has been updated to no longer store secrets or passwords.

In addition, we have added 24-hour rotation of UserID when we collect telemetry.

New Channel9 video

One of our engineers, Abhi Abhishek, presented a demo of mssql-cli for Channel9, the Microsoft Developer studio. If you are new to mssql-cli, please check out this video and please share with those you feel would benefit from using this tool.

Contact us

We are open to any questions, feedback, or any feature suggestions for future releases, which can be submitted on our GitHub Issues. You can also Tweet at us @sqldatatools.

Visualize geographic spatial data from SQL Server directly in Tableau 2018.1

This is a partner post authored with the Tableau team.

As part of Microsofts open and flexible platform for data and analytics, were always excited when partners add features that will expand customer options and extend business functionality. Business intelligence partner Tableau has recently introduced enhancements that will make it easier for their users to process spatial data stored in SQL Server.

The SQL Server database engine has two spatial types geometry and geography. The geography spatial type helps organize geospatial mapping data into SQL Server tables and works with several SQL-native graphing functions to answer questions like how far apart two geographic locations are, or what locations fall within a certain radius.

Before, customers had to process geography spatial data stored in SQL Server into Shapefiles before they could access it from Tableau. Now with Tableau 2018.1, customers can connect to and visualize data stored in SQL Server directly. This means Tableau will recognize spatial data in your SQL Server tables without any intermediate steps, and customers can leverage spatial operations for geography supported by SQL Server or custom queries to work with geographic data stored in SQL Server. Alongside support for native spatial data, Tableau 2018.1 also includes new RAWSQL functions that can leverage the spatial operations from the database and allow you to create calculations that return generated spatial objects.

Here are some steps to try it out:

Get started with spatial data

Tableau will recognize if your table has a spatial field and allow you to utilize it, with no intermediary steps. Just drag and drop the geographic field to get started.

Using spatial operations

Connections to SQL Server support Custom SQL queries. You can use a Custom SQL query to leverage operations supported by the database. Lets say you wanted to understand how many parks are located near schools. You could perform a proximity analysis using the query below to produce the following visualization. This query uses a spatial join based on a buffer of the schools point geometry.

SELECT
S.[common nam] as SchoolName,
P.[common nam] as ParkName,
S.geom.STBuffer(750) as circle_geom,
P.[Geom] as park_geom

FROM
TestSpatial.dbo.seattleelementaryschools S

LEFT JOIN
TestSpatial.dbo.allseattleparks P

ON
S.geom.STBuffer(750).STIntersects(P.geom) = 1

Make the data dynamic by adding parameters to the query. You can also apply a buffer to the park geometries to make them more prominent and to create a polygon geometry that can be used in further analysis.

SELECT
S.[common nam] as SchoolName,
P.[common nam] as ParkName,
S.geom.STBuffer(<Parameters.Radius>) as circle_geom,
P.[Geom] as park_geom

FROM
TestSpatial.dbo.seattleelementaryschools S

LEFT JOIN
TestSpatial.dbo.allseattleparks P

ON
S.geom.STBuffer(<Parameters.Radius>).STIntersects(P.geom.STBuffer(200)) = 1

Expand your analysis with RAWSQL

Support for SQL Server Spatial includes a new set of RAWSQL functions that are usable in calculations. RAWSQL functions allow you to pass values into a SQL statement where the query is executed when the calculated field is utilized within the worksheet. This means you can leverage the spatial operations supported by the database to generate a new spatial object. The calculation below returns the INTERSECTION of two spatial objectsin this case, the school and park buffers. STIntersection is like a cookie-cutter operation. The resulting geometry will represent only the area that was shared by the two inputs.

RAWSQL_SPATIAL("Select %1.STIntersection(%2.STBuffer(200))",[circle_geom], [park_geom])

You canput buffered parks and the intersection results on a dual axis to produce a visualization that only shows the areas that overlap between the two fields.

Join the pre-release community to test new geospatial features as theyre released in beta. Were excited to see how you leverage this feature to create even more beautiful (and useful) maps!

For more information on this feature, read up on SQL Server documentation for geography types or visit the Tableau Help page. Learn about other features in the 2018.1 release on our feature highlights page.

SSMS 17.7 is now available

This post is co-authored bySandy Winarko, Principal PM, SQL Server.

We are excited to announce the release of SQL Server Management Studio (SSMS) 17.7!

Download SSMS 17.7 and review the Release Notes to get started.

SSMS 17.7 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.7 comes with several exciting new features:

  • Support package scheduling in Azure-SSIS integration runtime.
  • Support for SSIS package scheduling in SQL Agent on SQL Managed instance. It is now possible to create SQL Agent jobs to execute SSIS packages on the managed instance.
  • Replication monitor now supports registering a listener for scenarios where publisher database and/or distributor database is part of Availability Group. So with this release of SSMS, you can monitor replication environments where publisher database and/or distribution database is part of Always On.
  • Fixed bug fixes

Scheduling SSIS Activities in ADF Pipelines via SSMS

Unlike on-premises SQL Server/Azure SQL Managed Instance (MI) that have a native/first-class SSIS job scheduler as part of SQL Server/MI Agent, Azure SQL Database does not have any. Consequently, if you use Azure SQL Database to host SSISDB, you can automatically generate Azure Data Factory (ADF) pipelines/activities/triggers to schedule SSIS package executions via SSMS with UI that is similar to using on-premises SQL Server Agent. This SSMS scheduling feature offers rapid authoring of ADF pipelines/activities/triggers that can be edited/extended on ADF app.

For more info on ADF pipelines/activities/triggers, see:

Bug fixes

In SSMS 17.7, there were many bug fixes.

To highlight a few of these:

Intellisense:

  • Perf improvement: reduced volume of intellisense queries for column data. This is especially beneficial when working on tables with a huge number of columns.

SMO:

  • Exposed Error Log Size setting in SMO. See more details here.
  • Fix linefeed scripting in SMO on Linux
  • Miscellaneous perf improvement when retrieving rarely used properties

Maintenance Plan:

  • Fixed an issue where trying to change the schedule of an existing Maintenance Plan was throwing an exception. See more details here.

Scripting:

  • Fixed an issue where scripting stored procedure against Azure SQLDW was not working for non-admin user
  • Fixed an issue where scripting a database against Azure SQLDB was not scripting the “SCOPED CONFIGURATION” properties

Telemetry:

  • Fixed issue where SSMS crashes then trying to connect to a server, after opting out of sending telemetry.

To learn more about other bug fixes covered in this release, check the Release Notes.

Call to Action

Try it out and let us know what you think! You can message us on our twitter @sqldatatools or reach out to Ken Van Hynings twitter @sqltoolsguy.

The May release of SQL Operations Studio is now available

This post is co-authored by Karl Burtram, Senior Software Engineer, SQL Server.

We are excited to announce the May release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our Github.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this May release is the sixth major update since the announcement. If you missed it, you can view the April release announcement.

The May Public Preview release is focused on improving our Extensibility experience with the release of new extensions as well as addressing top Github issues.

Highlights for this build include the following.

  • Announcing Redgate SQL Search extension available in Extension Manager
  • Community Localization available for 10 languages: German, Spanish, French, Italian, Japanese, Korean, Portuguese, Russian, Simplified Chinese and Traditional Chinese!
  • GDPR-compliant build has reduced telemetry collection, improved opt-out experience and in-product links to Privacy Statement
  • Extension Manager has improved Marketplace experience to easily discover community extensions
  • SQL Agent extension Jobs and Job History view improvement
  • Updates for whoisactive and Server Reports extensions
  • Fix GitHub Issues

For complete updates, refer to the Release Notes.

Announcing Redgate SQL Search extension

Redgate SQL Search in SQL Operations Studio extension is now available for installation through the Extension Manager Marketplace!The SQL Search extension lets you find database objects across an active connection. The search results makes it simple to jump to the object in the Object Explorer or to view the object’s definition.

We definitely recommend installing this extension. As SQL Ops Studio evolves, we will rely heavily on extensions to provide core functionality, so we expect that most users will want to have several extensions installed for the best experience.

Startup Performance improvements

We want SQL Operations Studio to be fast and efficient to maximize user productivity. Reducing the time it takes to launch SQL Ops Studio is one of the key scenarios where we’ve been making continual investments. Fortunately, our upstream platform, Visual Studio Code, is also optimizing this same scenario. One key improvement we merged from VS Code’s February release was ASAR Node module bundling.

We use product telemetry from the Startup scenario to understand how long users are waiting for SQL Ops Studio to launch. This telemetry allowed us to confirm that the April Public Preview delivered approximately 30-40 percent startup time improvements for most users. Particularly users experiencing the slowest startups have seen the largest improvements.

The below chart shows startup times bucketed into key percentiles: 25, 50, 75, 90, 95, 98, 99 percent. At the risk of being pedantic, this would mean, for example, at the 50 percent bucket half the startups were faster than that time and half the startups were slower than that time.

Community Localization available

Community Localization resources for German, Spanish, French, Italian, Japanese, Korean, Portuguese, Russian, Simplified Chinese and Traditional Chinese are available for the May Public Preview. We’ll continue working over the next couple months to get the product 100 percent localized.

We’d like to thank the translation community for helping to make SQL Operations Studio available to users in their preferred language!

The below screenshot shows an example using the Italian locale.

GDPR compliance

There are several updates in May Public Preview that were needed for GDPR compliance. Specifically, we reduced the telemetry being collected, improved the opt-out experience by adding a notification on first launch, and added a Privacy Statement item to the Help menu.

Extension Manager enhancements

The Extension Manager Recommended section has been replaced with a Marketplace section. The Marketplace will display all available extensions. Recommended extensions are sorted to the top.

Check out some of our 1st-party and community contributed extensions. And take a look at our Extensibility Getting Started page to see how easy it is to add your own extension to the marketplace!

Fix GitHub issues

Here is a summary of issues addressed:

  • #703 Entering HTML-like text in edit data causes value to display incorrectly until refresh
  • #821 sqlopsstudio.deb package dependency
  • #1260 keyword ‘distinct’ not highlighted
  • #1332 Edit data revert row doesn’t work
  • #1215 SQL Agent extension and the status bar
  • #1316 SQL Agent Dont resize after change windows size
  • Improve Manage Dashboard Properties scrolling

Contact us

If you have any feature requests or issues, please submit to our Github issues page. For any questions, feel free to comment below, message us on Gitter, or tweet us @SQLOpsStudio.