Cloud data and AI services training roundup August 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 Server

Build modern applications using the language of your choice, on-premises and in the cloud, now on Windows, Linux, and Docker containers.

  • Prepare for Windows Server 2008 and SQL Server 2008 End of Support
    Support for SQL Server 2008/ 2008 R2 and Windows Server 2008 will end in July 2019 and January 2020, respectively, which means youll no longer receive security patches for these versions. When you join this session, youll learn how to migrate your applications and data, avoid business disruptions, and adopt the most current security technologies. You will also receive guidance for your migration and find resources to help you move quickly.

Azure Database services for PostgreSQL and MySQL

Azure Database Services for PostgreSQL and MySQL provide fully managed, enterprise-ready community PostgreSQL/MySQL database as a service. These community editions help you easily lift and shift to the cloud, using languages and frameworks of your choice. On top of that, you get built-in high availability and capability to scale in seconds, helping you easily adjust to changes in customer demands.

  • How Open Source Database engines help you migrate to Azure
    Learn how to take advantage of fully managed, enterprise-ready PostgreSQL and MySQL community database engines. Join us as we cover how to use Azure Database Migration Service and what incentives are in place to help you in your migration journey.

Azure Cosmos DB

Azure Cosmos DB offers the first globally distributed, multi-model database service for building planet-scale apps.

  • Controlling your application experience with Azure Cosmos DBs consistency models
    The ability to control your application experience by changing your consistency model has been lackinguntil now. Azure Cosmos DB offers five well-defined and preconfigured consistency models, helping you navigate the tradeoffs between data consistency and app availability. In this session, learn the key differences between the five consistency models, which applications are best suited for each model and how to configure the models to ensure high performance.

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.

  • Making R-based analytics easier and more scalable
    R is an increasingly popular programming language for running predictive analytics workloads. If you are looking to scale out R-based advanced analytics to big data, Azure Databricks starts in seconds, integrates with RStudio, and automatically executes R workloads at unprecedented scale across single or multiple nodes. Join us to see how to get the ideal dataset for your needs and a detailed demonstration of the entire solution.

The July release of SQL Operations Studio is now available

We are excited to announce the July 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 eighth major update since the announcement. If you missed it, the June release announcement is available here.

Highlights for this release include the following.

  • SQL Server Agent preview extension Job configuration support
  • SQL Server Profiler preview extension Improvements
  • Combine Scripts Extension
  • Wizard and Dialog Extensibility
  • Social content
  • Fix GitHub Issues

For complete updates, refer to the Release Notes.

SQL Server Agent configuration

As part of our story of bringing over SSMS features and improving user experience, we are excited to introduce SQL Agent configuration support.

Summary of changes include:

  • Added view of Alerts, Operators, and Proxies and icons on left pane
  • Added dialogs for New Job, New Job Step, New Alert, and New Operator
  • Added Delete Job, Delete Alert, and Delete Operator (right-click)
  • Added Previous Runs visualization
  • Added Filters for each column name

In addition to jobs, users can now view Alerts, Operators, and Proxies through the icons on the left pane as demonstrated in the gif above.

We also made several improvements for the Job View. Previous Runs visualization can now be seen so that a user can quickly see a jobs history of past runs if they passed or failed.

This release also made it easier to find specific jobs in a large list of jobs. Imagine you had a list of 100+ jobs and you only wanted to see the failed jobs? Now you can by checking out the gif below using the filter column option.

With all the improvements in Views, we have added new dialogs so that users can now add Jobs, Alerts, and Operators without having to go to SSMS. To open each dialog, click New Job above each respective view.

For all the SQL Agent enthusiasts out there, we would love for you to try out the new SQL Server Agent experience and let us know what you like and what is still missing for you to use Agent day to day. As part of doing our engineering out in the open, we need your feedback so that we can create experiences that empower you to do your job (pun intended).

To learn more about SQL Server Agent, check out the documentation.

SQL Server Profiler improvements

With the release of SQL Server Profiler extension last month, our team has been working hard on improvements, especially making launching Profiler quickly.

Summary of changes include:

  • Added Hotkeys to quickly launch and start/stop Profiler
  • Added 5 Default Templates to view Extended Events
  • Added Server/Database connection name
  • Added support for Azure SQL Database instances
  • Added suggestion to exit Profiler when the tab is closed and Profiler is still running

As seen in this gif, you can quickly get Profiler open after making a server/database connection. With this release, we added Keyboard Shortcuts to Launch Profiler (Windows: Alt + P Mac: Ctrl+ALT+P) and Start/Stop Profiler (Windows: Alt + S Mac: Ctrl+ALT+S). From our user survey, the highest priority for users is to be able to start Profiling as quickly as possible. Now with two keyboard strokes, you can start Profiler.

In addition, Profiler now has added Default templates for five different views: Standard, TSQL, Tuning, TSQL_Locks, and TSQL_Duration. When you click on each one, a different list of columns will generate in your Profiler view so that you can focus on the areas that you are investigating. At the moment, it will reset the view each time.

In addition, each Profiler tab will show the server/database the Profiler instance is connected to. You can see the name in the top right of the above screenshot, which is localhost/Adventureworks2014.

Please let us know what you think and what you would like to see in Profiler.

Combine Scripts Extension

We have a new community extension published in our Extensions Manager. Created by Cobus Kruger, the Combine Scripts Extension for SQL Operations Studio is now available.

From the extension description: Ever needed to execute several scripts spread over several folders? Now you can select several files and folders, right click and click Combine Scripts, and generate a single combined file to execute or use any way you choose.

For those new to extensions, here are the instructions to access the Extensions Manager and download the Combine Scripts extension. For this extension, in particular, the install button will take you to a download link for the VSIX package. Download the VSIX, and then click File -> Install Extension from VSIX Package.

Dialog and Wizard extensibility

With this release, we are continuing to provide more opportunities for extension authors, which we highly encourage you to participate. The highlight for this release is we have now provided options for extension authors to incorporate Dialogs and Wizards in their extensions.

The differences between using dialogs and wizards are very similar to SSMS. Generally, use Wizards for step-by-step scenarios, and use dialogs for most other cases.

Extension authors can see the full list of Dialog and Wizard APIs.

To see this in action, check out our sample extension that includes this code.

We are excited to see what our extension authors can come up with these new extensibility points. If you arent an extension author but have ideas in mind, please feel free to share on Twitter or GitHub Issues.

Social content

Over the past month, we have seen a lot of great content about SQL Operations Studio as we monitored social media. We highly encourage the community that if you love this tool, consider using this tool in demos and blog posts. We will also make sure to share any of your content with the community through our Twitter handle (@sqlopsstudio).

If you would like to use SQL Operations Studio at sessions like SQL Saturdays or PASS Summit, feel free to reach out to our team and we can work with you. If there are any demo blockers, please submit an issue on our GitHub Issues. Our engineers will help unblock your scenarios.

With the launch of the Data Double-Click channel, our Principal PM Lead, Vicky Harp, discussed SQL Operations Studio with Scott Klein. Check out the conversation below.

In addition, Vicky was also interviewed by Joey DAntoni for Redmond Mag, covering the current state of SQL Server Tools development.

SQL Ops Studio also had a presence at OSCON in Portland this year, where Shayne Boyer shared SQL Operations Studio and mssql-cli.

Fixed GitHub Issues

Here is a summary of issues addressed:

  • #728No response to Add Connection on macOS
  • #1718Unable to connect to any data source
  • #1713Number of rows affected
  • #1843Better Table organization
  • #1847MFA Login to Azure SQL Databases
  • #1845Bug Scroll change tab query
  • #1612Results grid text display is messed up by international characters
  • #1749BUG: HTML data in a column gets interpreted
  • #1830Setting iconPath in ButtonComponent after component() is called does not change icon
  • #1789Extensibility: if you add a connection provider uninstall will never remove it from the list
  • #1799Top 10 DB Size chart does not work on ccase-sensitive instances
  • #1724Extension dialogs have stopped working
  • #1719TypeError when Connecting to Server
  • #1693Backup dialog: File browser UI is broken
  • #1817Error de Ortografia
  • #1791Sqlops Extensions: queryeditor.connect() connects to the target database, but UI does not show the editor is connected
  • #1814d.ts typo causing implicit ‘any’ type definition

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.

 

SQL Server 2008 end of support is the first step to tomorrow’s database

Today, Takeshi Numoto blogged about the upcoming SQL Server 2008 and 2008 R2 end of support. If youve been thinking about what to do with your SQL Server 2008 and 2008 R2 databases, youre not alone. These databases reach end of support on July 9, 2019, and many organizations have started planning in earnest for this milestone.

Which brings us to Will, a database admin at that development shop downtown. Bucking the job-hopping trends of today, Wills been a stalwart for his company, wrangling data for them since the mid-2000s. He remembers leading the installation of SQL Server 2008. Back then, connections were speedy, requests processed swiftly. Security, efficiency, and good clean design existed in abundancethere was a place for every byte and every byte was in its place. App developers loved Will because he made sure their data was right where they needed it, when they needed it. What an idyllic, peaceful time!

A lot has happened in the decade since. Over the years, Wills information transfer network has grown and evolvedforking and pooling and spidering as it has had to make new, less-efficient references. The schema, once so clear, is now muddied and confusing, obscuring paths for formerly responsive queries. Latency abounds. And lurking in every shadow? The threat of unidentified, potentially insecure, rogue requests for information. Those developers arent nearly so happy anymore. Ugh.

Fortunately, hope is not lost for Will. He can restore the organizational marvel that was yesterdays landscapeand layer on even more improvements. Yes, modernization to SQL Server 2017 or Azure SQL Database must be undertaken carefully, with planning and analysis and meticulous list-making. But its a journey worth taking. And luckily for Willand youtheres a free databasemigration guide that provides step-by-step instructions for getting from here to there.

Plus, Wills options arent limited to a single choice. The new SQL Server runs on numerous hosting environments and operating systemson-premises or in the cloud, virtual machine or Azure data services, Windows or Linux. As soon as its time to reroute those data streams, clear away the puddles, and brighten up the place, were here to help.

End of support is coming soon

As mentioned, SQL Server 2008 and 2008 R2 reach the end of support on July 9, 2019. We know it can be difficult to upgrade everything before end-of-support deadlines like this. Thus, were offering extended security updates for SQL Server 2008 when you rehost your database in Azure Virtual Machineswith no application code changes needed. Youll gain the critical patches you need to help keep your data safe for three more years after the end-of-support deadline, giving you time to plan and implement your next move. Find out all the details you need in the end-of-support blog post.

All this is to say, the future of data is bright for Will, his data-loving development team, and you.

Get started today

SQL Server 2008 and 2008 R2 are reaching end of support. What’s next?

SQL Server 2008 and 2008 R2 have had a tremendous run. But all good things come to an end, right? On July 9, 2019, Microsoft will end Extended Support, which means no more updates or support of any kind, potentially leaving you vulnerable to security and compliance issues.

The good news is, you still have plenty of time and options to avoid any heartburn caused by the technology circle of life. And well lay out all of those options for youin a webinar on July 12.

In this webinar, well show you:

  • how to migrate your applications and data to make the transition more than just an upgrade
  • how to avoid business disruptions and adopt the most current security technologies
  • the advantages of moving your legacy platforms to SQL Server 2017 and Azure
  • the range of guidance and resources available to help, no matter which path you choose

We want this transition to be as seamless and pain-free as possible for you. Register now and find out all the ways weve come up with to make that happen.

Re-platforming and modernizing your data workloads with SQL Server on Linux

This post is authored by Marko Hotti, Senior Product Marketing Manager, SQL Server.

Today, were excited to introduce a free e-book, SQL Server on Linux: A guide to re-platforming and modernizing your data workloads. This is the thirdin a series oftechnical e-books helping you get the most out of your database. With SQL Server 2017, you can harness the latest capabilities of SQL Server on the platform of your choicewhether thats Windows, Linux, or even containers.

In this guide, you getthe technical details for preparing your Linux system for SQL Server installation, managing a mixed environment, and migrating your existing data and databases. The e-book addresses flexibility, performance, and security with your data platform, whether youre a database architect, administrator, or developer working with data.

Here’s whats covered:

  • An overview of SQL Server on Linuxunderstand platform options, features, pricing, and more.
  • Getting the most out of SQL Server on Linuxreview our planning considerations to prepare your Linux system to best support your business goalssuch as performance optimization, high availability, or enterprise-grade security.
  • Tools and managementget a rundown of the many tools available from Microsoft that make managing your mixed environment easier.
  • Migrations and upgradesdiscover the tools and services available for managing your transition to SQL Server on Linux.

Microsoft is committed to enabling you to choose the best platform for your data and applications. And as deployment options expand on Windows Server, on Linux, and in virtual machines and containers,SQL Server 2017 continues the evolution of SQL Server on your platform of choiceimproving support while enhancing data management and data-driven applications.

Get your copy of the free guide today. Plus, dont miss the other e-books in the series.

Securing your data with Azure SQL Database

The General Data Protection Regulation (GDPR) is now in effect as of May 25th, 2018. GDPR has significant implications on the use and management of your customers personal data. Considerations of privacy, security, data management, and marketing practices are all top of mind. With nearly 160 new GDPR requirements, its clear that cloud technology can help accelerate your path to GDPR compliance.

The path to compliance is by no means a simple journey, but by partnering with Microsoft, you will have the right set of resources, tools, and processes to help optimize your privacy and data management practices. Luckily, if you are currently looking for a modern database, youll automatically inherit the benefit of physical and operational security in Azure SQL Database that meet regulatory standards. To help you secure your SQL database, Microsoft helps you protect your existing data, control who can access your data, run regular preventive monitoring tests and manage your security for the long run.

Join our speakers Joachim Hammer and Joanne Wong for a webinar on Azure security to learn about the intuitive, built-in features that accelerate your path to GDPR compliance. Specifically, we will demonstrate how customers use the results from our new vulnerability assessment and data classification tools to devise a security strategy that will help secure your data. Youll also see useful demos of how you can get started with protecting your data and learn more about our encryption technologies, including:

Register now to reserve your seat and start planning your data platform modernization.

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.