Together with our partners, SQL Server continues to innovate with high-performing, enterprise-ready solutions that deliver unparalleled price performance. We’re pleased to share the results for the TPC-H (the industry standard for benchmarking data warehouse) performance at 3TB with HPE that currently holds the record as the industry’s fastest 2-socket non-clustered solution, and 10TB with Cisco, as well as the TPC-E (the industry standard for benchmarking online transaction processing) performance at 10TB with Lenovo. Check out the results below.

The table above shows the TPC-H data warehousing results published on SQL Server 2017 with configuration (size). The table below shows the TPC-E OLTP results published on SQL Server 2017 with throughput and price/performance ratio.

These new results for TPC-H at 3TB scale prove the superior combination of SQL Server 2017 Enterprise Edition, SUSE Linux Enterprise Server, and HPE ProLiant DL380 Gen10. The benchmark showcases the secure, resilient, and versatile 2P/2U HPE ProLiant DL380 Gen10 as an industry-leading server for multi-workload compute, ideal for structured data analysis such as decision support for large databases.

In addition, a new record for the 10TB non-clustered TPC-H benchmark has been accomplished with a winning combination of SQL Server 2017, Red Hat Linux 7.6, and Cisco UCS C480 M5 Server. Cisco’s 4-socket Cascade Lake system surpassed the previous #1 10TB non-clustered result by 11.6 percent in performance along with a cost reduction.

The TPC-E results showcase SQL Server 2017 blazing the trail together with our strategic partner Lenovo. Lenovo’s ThinkSystem SR650 on a 2-socket Cascade Lake system was the fastest 2-socket system when combined with SQL Server 2017 and Windows Server 2016. It surpassed the previous 2-socket Sky Lake publication by 3 percent, and was also the best overall  price performance leader.

Learn more about SQL Server 2017.

All claims in this post were made as of 5/15/2019.

Learn more

• SQL Server 2019 is now in public preview.
• Learn more about big data clusters in the SQL Server 2019 big data clusters white paper and sign up to try out the new features for big data, analytics, and AI in SQL Server 2019 Big Data Clusters by signing up for the SQL Server 2019 Early Adoption Program.

The post SQL Server 2017 achieves top TPC benchmarks for OLTP and DW on Linux and Windows appeared first on Microsoft SQL Server Blog.

The CTP 2.5 preview brings the following new features and capabilities to SQL Server 2019:

Big data clusters

• For more control and flexibility over the big data cluster layout and configuration settings, we’re introducing a new deployment mechanism that uses configuration files to deploy your cluster. You can start from the built-in configurations that come with the mssqlctl utility and customize them to accommodate the platform you want to run the big data cluster on.
• To streamline the deployment process, mssqlctl utility enables an interactive deployment experience that guides you through the steps to initiate the deployment with prompts for required inputs. You can also automate the entire process using mssqlctl configuration commands available to list, customize, or deploy using configuration files.
• To avoid incompatibilities between client utility and big data cluster server versions, you can now verify you installed the right version of the utility by using mssqlctl –version command.
• The new MSSQL Spark connector that uses SQL Server bulk write APIs to provide high performance Spark to SQL Server writes for high volume data.

Database engine

• PolyBase enables you to run a T-SQL query inside SQL Server to pull data from Cloudera or Hortonworks and return it in a structured format—all without moving or copying the data.  As part of CTP 2.5, we are now introducing the ability to install PolyBase on Linux for the first time. This includes the new connectors in SQL Server 2019 for additional data sources such as Oracle, Teradata, other SQL Servers, and MongoDB.
• The new Java language SDK for SQL Server can be used to simplify the development of Java programs that can be run inside of SQL Server.

Getting started

Ready to learn more?

• To get started with SQL Server 2019 CTP 2.5, you can find the download instructions on the SQL Server 2019 web page or use a SQL Server 2019 CTP virtual machine in Microsoft Azure
• Join the SQL Server 2019 Early Adopter Program for access to SQL Server big data clusters
• Check out what features were introduced in SQL CTP 2.4
• Register for our upcoming webinar on May 16th on Run SQL Server on Linux and Kubernetes
• Watch this free on-demand webinar to discover how SQL Server 2019 big data clusters can help you gain business insight.

The post SQL Server 2019 community technology preview 2.5 is now available appeared first on Microsoft SQL Server Blog.

Recently, we blogged about performing automated machine learning on SQL Server 2019 big data clusters. In today’s post, we will present a complementary automated machine learning approach leveraging Azure Machine Learning service (Azure ML) invoked from SQL Server. While the previous post dealt with a Spark-based implementation tuned for big data, this post presents an approach that runs directly in SQL Server running on a single server. This is well suited for use with data residing in SQL Server tables and provides an ideal solution for any version of SQL Server that supports SQL Server Machine Learning Services.

Azure Machine Learning service

Azure Machine Learning service is a cloud service. We call the service from SQL Server to manage and direct the automated training of machine learning models in SQL Server. Automated machine learning tries a variety of machine learning pipelines.  It chooses the pipelines using its own machine learning model based on the scores from previous pipelines. Automated machine learning can be used from SQL Server Machine Learning Services, python environments such as Jupyter notebooks and Azure notebooks, Azure Databricks, and Power BI.

Starting in SQL Server 2017, SQL Server includes the ability to run Python code using the sp_execute_external_script stored procedure. This allows SQL Server to call Azure ML automated machine learning.  Instructions and code for running the following example are available on GitHub.

Example: Energy demand forecasting

Automated machine learning can be used for regression (the prediction of continuous values), classification, or forecasting. This example focuses on energy demand forecasting, where the goal of a power grid operator is to predict future energy demand given forecasted weather data. Although the context of our example is energy demand forecasting, the methods used can be applied to many other contexts and use cases.

This example uses an energy demand data set consisting of a table with four columns: timeStamp, demand, precip, and temp. We use three Transact-SQL stored procedures including AutoMLTrain, AutoMLGetMetrics, and AutoMLPredict. The AutoMLTrain procedure returns a model that predicts the label column – in this case, demand – based on the remaining columns that include timeStamp, precip and temp. Here, we run the AutoMLTrain procedure using data in rows with a time stamp before February 1, 2017:

The resulting model is stored in a SQL Server table so that it can be used later for prediction. The training can be viewed in the Azure Portal under Machine Learning service workspaces:

Optional parameters on the AutoMLTrain allow for explicit train/validate split, cross validation, sample weights, number of iterations, exit score, blacklist and whitelist models, forecasting, and time limits.

The AutoMLGetMetrics stored procedure returns several metrics for each pipeline.  These can be stored and queried in SQL Server.

Finally, the AutoMLPredict stored procedure can predict new values based on the model returned by AutoMLTrain. Having trained our model using data before February 1, 2017, we will forecast demand for February 1, 2017 and later:

Conclusion

In this blog post, we have seen how Azure ML automated machine learning can be used from within SQL Server to train models and then predict new values. SQL Server developers can now train and use machine learning models without needing to learn Python and without needing detailed knowledge of machine learning.

Get started

• You can set up the configuration described in this blog post using the instructions on GitHub.
• Learn more about SQL Server Machine Learning Services.
• Learn more about Azure ML automated machine learning.

The post Automated machine learning from SQL Server with Azure Machine Learning appeared first on Microsoft SQL Server Blog.

Download Azure Data Studio and review the Release Notes to get started.

Note: If you are currently using the preview version, SQL Operations Studio, and would like to retain your settings when you upgrade to the latest version, follow these instructions. When you download Azure Data Studio, remember to enable preview features by default on first launch, and then you can disable in settings if you don’t need it otherwise you will be missing preview experiences like Query Plans, certain extension support, and more.

Azure Data Studio is a new cross-platform desktop environment for data professionals using the family of on-premises and cloud data platforms on Windows, MacOS, and Linux. To learn more, visit our GitHub.

Azure Data Studio was announced Generally Available last month at Microsoft Ignite. If you missed the GA announcement, you can see “Azure Data Studio for SQL Server” on the SQL Server blog. You won’t want to miss the great orthogonality matrix included comparing SSMS and Azure Data Studio and answers to many of your questions.

In the October’s version of the monthly release blog, we will be covering features shipped in the September GA release as well as what is new in the October release.

This includes:

• Announcing the SQL Server 2019 Preview extension
  • Support for SQL Server 2019 features including big data cluster support
  • Azure Data Studio Notebooks
  • SQL Server Polybase Create External Table Wizard
• Azure Resource Explorer
  • The Azure Resource Explorer view lets you browse data-related endpoints for your Azure accounts and create connections to them in Object Explorer. In this release Azure SQL Databases and servers are supported.
• SQL Server Agent Extension improvements
• Improve Object Explorer and Query Editor connectivity robustness
• Added Connection name option to provide an alternative display name in the Servers view-let
• VS Code source code refresh from 1.23 to 1.26.1 with Grid Layout and Improved Settings Editor (Preview)
• Thank you to contributors

For complete updates, refer to the Release Notes.

SQL Server 2019 Preview extension

As announced at Microsoft Ignite, one of the most exciting extensions to share in our September GA release was the release of the SQL Server 2019 Preview extension. If you were following the blog announcements, starting with SQL Server 2019 preview, SQL Server big data clusters allow you to deploy scalable clusters of SQL Server, Spark, and HDFS Docker containers running on Kubernetes.

These components are running side by side to enable you to read, write, and process big data from Transact-SQL or Spark. SQL Server big data clusters allow you to easily combine and analyze your high-value relational data with high-volume big data. To learn about all the excitement of SQL Server Big Data Clusters, follow the documentation here.

These experiences are built as an extension to Azure Data Studio. We can go into full depth about all the great capabilities this extension includes, but deep-diving into any one of these features can be a full blog post itself. Here is a high-level summary of these features, and then you can see a full demo of the features below.

• Support for SQL Server 2019 features including big data cluster support
  • Connect to the HDFS/Spark Gateway shipped with SQL Server 2019
  • Browse HDFS, upload files, save files, and launch useful actions such as Analyze in Notebook for CSV files
  • Submit Spark jobs from the dashboard or right-click on a HDFS/Spark connection in Object Explorer
• Azure Data Studio Notebooks
  • Create or open Notebooks using an integrated Notebook viewer. In this release the Notebook viewer supports connecting to local kernels and the SQL Server 2019 big data cluster only.
  • Use the PROSE Code Accelerator libraries in your Notebook to learn file format and data types for fast data preparation.
• SQL Server Polybase Create External Table Wizard
  • Create an external table and its supporting metadata structures with an easy to use wizard. In this release, remote SQL Server and Oracle servers are supported.

To download the extension, follow the instructions here.

Introducing the Azure Resource Explorer

As part of our goal to unify data management experiences, we have made it easier to manage your Azure subscriptions through the Azure Resource Explorer. Originally shipped as an extension, this feature is now built into the core product of Azure Data Studio.

After downloading the latest version, you will now see an Azure icon on the left bar, which you can click on to navigate to the Azure Resource Explorer.

With this feature, you can now manage your Azure SQL Server, Azure SQL Database, and the recently GA’ed Azure SQL Managed Instance resources easily. By clicking the filter icon to the right of the explorer, you can select which subscriptions you want to have displayed.

After drilling down to your target SQL instance through the explorer, you can then click on the plug icon next to each SQL instance to open up the connection dialog to directly connect to that resource and instantly start querying.

To learn more about the Azure Resource Explorer, check out our documentation.

SQL Server Agent extension improvements

One of our engineering focuses is to improve our first party extensions, which include SQL Server Agent, SQL Server Profiler, and SQL Server Import. As one of the first steps, we have brought a lot of UI and functionality fixes in SQL Server Agent, particularly in the Edit Job experience.

Now you can edit your Job steps, schedules, alerts, and notifications within the dialog.

If you are an avid user of SQL Server Agent, this is your chance to have a say in the new Agent experience in Azure Data Studio. You can report an issue directly on GitHub or go to Help->Report an issue to report directly from the product. Let us know your daily scenarios and how we can help empower you to use SQL Agent on Azure Data Studio daily.

To learn more about SQL Agent or how to acquire the extension, check out our documentation.

Improve Object Explorer and Query Editor connectivity robustness

As part of addressing customer reported issues, we put an emphasis on improving connectivity robustness across Object Explorer and Query Editor. In particular, queries that lose a connection will automatically attempt to reconnect.

To see a full list of the connection investments, see below:

• Load properties used for OE node status by group instead of individually
• Clear OE node error message when populating children
• Reopen connections prior to creating query execution data readers
• Fix OE and Binding Queue reliability bugs

Custom connection name option to provide alternative name

As requested on our GitHub issues page, you can now provide friendly connection names for your connections. This is particularly useful if your connection instance is an ip address, very long, or want to hide the name of the server in a public facing demo or docs.

This shows up as the last input box in the connection dialog as you can see in the screenshot below:

This will then appear in your Servers pane.

VS Code refresh from 1.23 to 1.26.1

Since Azure Data Studio forks from Visual Studio Code, our team continues to periodically “refresh” Azure Data Studio with stable and mature VS Code releases. This directly benefits users especially in editor and configuration experience.

The latest refresh picks up the latest changes from the July release of Visual Studio Code. This was implemented in the September release, but is still good to highlight for those coming from SQL Operations Studio.

A summary of changes:

• Preview support of improved settings editor
• Small UI changes in grid editor

To see the full list of changes, you can view the updates at the Visual Studio Code updates page. Be sure to view the changes in also the May and June changes.

Thank you to contributors

If you would like to help make Azure Data Studio a great product, share any feedback or report issues through our Issues page. Our engineering team is regularly going through the untriaged issues and assigning issues into different monthly milestones so that you can know we are working on it. Your votes on issues helps us prioritize.

In addition to submitting issues, users can also contribute by submitting pull requests for potential quick fixes, and we welcome those submissions. Here is a shout-out to some of the customers who have submitted PR’s that have been included in the product:

• AlexFsmn Feature: Ability to add connection name (#2332)
• AlexFsmn Disabled connection name input when connecting to a server. (#2566)
• philoushka for center the icon (#2760)
• anthonypants for Typo (#2775)
• kstolte for Fix Invalid Configuration in Launch.json (#2789)
• kstolte for Fixing a reference to SQL Ops Studio (#2788)

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 @AzureDataStudio.

The post The October release of Azure Data Studio is now available appeared first on Microsoft SQL Server Blog.

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

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

SSMS 17.9 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.9 comes with several new features:

• ShowPlan improvements
• Azure SQL support for vCore SKUs
• Bug Fixes

View the Release Notes for more information.

ShowPlan improvements

Graphical Showplan now shows the new row mode memory grant feedback attributes when the feature is activated for a specific plan: IsMemoryGrantFeedbackAdjusted and LastRequestedMemory added to the MemoryGrantInfo query plan XML element. For more on row mode memory grant feedback, see Books Online.

For more on row mode memory grant feedback, view the Adaptive Query Processing documentation.

Image from Adaptive Query Processing

Azure SQL support for vCore SKUs

Added support for vCore SKUs in Azure DB creation. For more information on vCore, check out the full blog.

Image from Azure SQL DB blog

Bug fixes

In SSMS 17.9, there were many bug fixes.

• Replication Monitor
  • Fixed an issue that was causing Replication Monitor (SqlMonitor.exe) not to start (User Voice item)
• Import Flat File Wizard
  • Fixed the link to the help page for “Flat File Wizard” dialog
  • Fixed issue where the wizard did not allow changing the destination table when the table already existed: this allows users to retry without having to exit the wizard, delete the failed table, and then re-enter the information into the wizard (User Voice item)
• Import/Export Data-Tier Application
  • Fixed an issue (in DacFx) which was causing the import of a .bacpac could fail with a message like “Error SQL72014: .Net SqlClient Data Provider: Msg 9108, Level 16, State 10, Line 1 This type of statistics is not supported to be incremental. ” when dealing with tables with partitions defined and no indexes on the table
• Intellisense
  • Fixed an issue where Intellisense completion was not working when using AAD with MFA.
• Object Explorer
  • Fixed an issue where the “Filter Dialog” was displayed on random monitors instead of the monitor where SSMS was running (multi-monitor systems)
• Azure SQL
  • Fixed an issue related to enumeration of databases in the “Available Databases” where “master” was not displayed in the dropdown when connected to a specific database.
  • Fixed an issue where trying to generate a script (“Data” or “Schema and Data”) was failing then connected to the SQL Azure DB using AAD with MFA.
  • Fixed an issue in the View Designer (Views) where it was not possible to select “Add Tables” from the UI when connected to a SQL Azure DB.
  • Fixed an issue where SSMS Query Editor was silently closing and reopening connections during MFA token renewal. This will prevent side effects unbeknownst to the user (like closing a transaction and never reopening again) from happening. The change adds the token expiration time to the properties window.
  • Fixed an issue where SSMS was not enforcing password prompts for imported MSA accounts for AAD with MFA login
• Activity Monitor
  • Fixed an issue that was causing “Live Query Statistics” to hang when launched from Activity Monitor and SQL Authentication was used.
• Microsoft Azure integration
  • Fixed an issue where SSMS only shows the first 50 subscriptions (Always Encrypted dialogs, Backup/Restore from URL dialogs, etc)
  • Fixed an issue where SSMS was throwing an exception (“Index out of range”) while trying to log on to a Microsoft Azure account which did not have any storage account (in Restore Backup from URL dialog)
• Object Scripting
  • When scripting “Drop and Create”, SSMS now avoids generating dynamic T-SQL
  • When scripting a database object, SSMS now does not generate script to set database scoped configurations, if they are set to default values
• Help
  • Fixed a long outstanding issue where “Help on Help” was not honoring the online/offline mode
  • When clicking on “Help | Community Projects and Samples” SSMS now opens the default browser that points to a Git page and shows no errors/warnings due to the old browser being used

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 Hyning’s twitter @SQLToolsGuy.

The post SSMS 17.9 is now available appeared first on Microsoft SQL Server Blog.

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

Highlights for this release include the following.

• Announcing the SQL Server Import extension
• SQL Server Profiler Session management
• New community extension: First responder kit
• Quality of Life improvements: Connection strings
• Bug bash galore

For complete updates, refer to the Release Notes.

Announcing the SQL Server Import extension

It all started from a simple idea: Take the #1 most used wizard in SSMS in the past year and bring this wizard to SQL Operations Studio. When we first released our Wizard and Dialog extensibility API’s in June, this was the perfect candidate to test our wizards and highlight to the community that these UI components are ready to incorporate in community extensions.

To provide some background, the Import Flat File Wizard was first released and announced on October 2017 in SSMS 17.3 (shameless plug alert: coincidentally my first SQL Server blog post and first project at Microsoft.) Outside of featuring in a Channel9 video, the wizard did not receive any additional marketing. Fast forward a few months, and it was suddenly the #1 most used wizard in SSMS. How did this happen?

A very common scenario for SQL Server users is that they simply want to take a .txt or .csv file and import it to their SQL database as a table. As much as we love the ever reliable Import and Export Wizard, for users unfamiliar with the wizard, there were many configuration options that can make first experience difficult. If a user simply wants to import a text file, how can we make a simple scenario easier? By creating a whole new wizard of course!

The Import Flat File Wizard utilizes a Microsoft Research framework known as Program Synthesis using Examples (PROSE) to import .txt and .csv files into a SQL table. It is a powerful framework for data wrangling, and it is the same technology that powers Flash Fill in Microsoft Excel and featured in many publications and demos led by Sumit Gulwani. This technology turns the Import Flat File experience into a 6 click experience to go from selecting a file and importing into your database. Clearly, incorporating PROSE into everyday database tasks is a delighter for our users, and we will continue to put investment into creating experiences with PROSE.

Logically, it made perfect sense to have an AI-powered feature be our first wizard experience in SQL Operations Studio, but our engineers were at full capacity, so we had to be a little creative to make this possible.

Every year since Satya Nadella became CEO, Microsoft holds a global, company-wide hackathon where employees spend 3 days working on any project ranging from Hack for Good projects, VP-sponsored challenges, or from a random idea on the drive back home. This was the perfect opportunity to pitch bringing the Import Flat File Wizard to SQL Operations Studio, while also promoting cross-platform and open source development to our fellow co-workers.

By the time of the Hackathon, we had 4 interns and 7 full-time employees signed up for the project. More importantly, we asked why external team members chose our project, and we were blown away by the passion the interns and external team members had for SQL Server, and how they wanted to work on projects to improve SQL Server user experience.

Starting from mockups featured on PowerPoint slides, we shared the vision of the project while quickly onboarding new team members to our tech stack. It was not the most productive first day, but we did end up finding our rhythm. Using this momentum, we iterated quickly and were nailing our checkpoint sync-ups throughout the day, but there was still a lot to get done. However, with one hour to go before the Hackathon tents closed, we completed the first SQL Server Import experience in SQL Operations Studio end-to-end. Very proud of this team for getting a shippable deliverable within the allocated Hackathon time.

Our intern, Amir Omidi, worked on the fit and finish for the wizard for the remainder of his internship, and we are grateful for his hard work.

Now, we are ready to share this extension with the community. You can get this extension from the Extensions Manager. This feature not only brings the same simplicity as the SSMS wizard, but also brings this experience cross-platform to our macOS and Linux users. You can start the wizard with the same right click experience or press Ctrl + I.

Overall, this project taught us several things:

• Our SQL Server users love AI-assisted features, and this is the first of many AI experiences in SQL Operations Studio.
• Interns are very talented. Invest in their growth.
• Keep things simple. Bring our users with us on our journey.

If you have ideas of what you would like to see in this extension, let us know through our community feedback. We look forward to bringing more PROSE experiences into SQL Operations Studio in the future.

SQL Server Profiler Session management

Since the June release, we have been making improvements with SQL Server Profiler. We are excited to announce the Profiler extension now supports session management. With session management, you can now configure your most popular sessions as you can in SSMS.

To try out this feature, you will need to make an active connection to a SQL Server instance. You can then launch Profiler by clicking on a server or database in the Object Explorer and pressing Alt + P.

This will pop up a New Session dialog as shown in the gif. Here you can give an easy to remember name like Profiler and hit create. If you don’t want to create a new profiler session, simply press Cancel.

To select the session you just created, simply click on the dropdown next to Start/Stop and select Profiler.

You can now start profiling your SQL Server events. Note that there is also a new Create button where you can pop up the Create Session dialog.

With template support released last month and session support in this release, we hope to continue to improve the Profiler extension for all the avid Profiler users in the SQL world. A big thank you to Madeline MacDonald for her hard work in shipping Profiler over the course of her internship.

New community extensions: First Responder Kit

Continuing our extensibility story, our marketplace now includes Brent Ozar’s First Responder Kit. For those unfamiliar with the First Responder Kit, this toolkit helps users understand why their SQL Server is down or slow. Specifically, there are five main scripts featured:

• sp_blitz: Overall health check
• sp_blitzcache: Most resource-intensive queries
• sp_blitzfirst: Why is server slow
• sp_blitzindex: Indexes missing or slow
• sp_blitzwho: Queries currently running

To leverage these features, you will first need an internet connection. Then, open the command palette with Ctrl+Shift+P and type > first responder kit: import to see a list of scripts to import. Then select the script with arrow keys and press enter.

Once the scripts are loaded to the database, you can run the scripts by again opening command palette and type > first responder kit: run to view the list of available scripts to run. Then select the script with arrow keys and press enter.

A big thank you to Drew Skwiers-Koballa for using our extensibility API’s to create a SQL Operations Studio extension. Also thank you to Brent Ozar Unlimited team for having these awesome scripts easily available for the community.

In addition to having a great new extension, Drew shared his story for creating a SQL Operations Studio extension through a detailed blog post. If you are interested in leveraging extensions API’s or have a great idea for an extension, we would highly recommend checking out his blog.

Drew also wanted to let the community know about his extension authorship experience:

“I want everyone to understand that in 1-2 hours you can go through an extension writing tutorial and start working on crafting your own solutions. The development platform is just as accessible as the application itself – Windows, Mac, and Linux. Building a SQL Operations Studio extension is a great way to be a data community contributor, especially for someone who might not be keen on giving talks or writing blog posts. I’m extremely excited for the future possibilities in SQL Operations Studio as more APIs are created each month.”

Quality of Life improvements: Connection Strings

As requested by the community, we have also made it easier for you to handle connection strings in SQL Operations Studio.

Generate Connection String

If you need to quickly generate a connection string, you can follow these three steps:

1. Open a query editor with an active connection.
2. Open Command Palette (Ctrl+Shift+P), and type Get Current Connection String and then press Enter.
3. Copy connection string from notification pop-up.

Note: Password will be removed from the returned string.

You can now use or share the connection string.

Populate info from Connection String

If you have a valid connection string such as from the Azure Portal, you can now copy your connection string and paste the string into the connection dialog and it will auto-populate the fields based on the connection string.

Bug bash galore

In addition to the new features, we dedicated time to fix many of the top user reported bugs.

To highlight high impact ones:

• Cursor position no longer loses context when switching between tabs #1744
• Script As now auto-connects to the server connection. #825
• .sql files now are associated with SQL Operations Studio #1836

All fixed customer reported issues:

• Parse SQL in a Query Editor window by using the Parse Syntax command
• Save edit data scroll position when switching tabs #2129
• View as Chart options are cut off at the bottom #1497
• Cancel change connection disconnects current connection #1474
• Bug: Error message when saving Excel file second (and subsequent) time #1748
• Update document icon for Dashboard and Profiler documents #2107
• SQL Tab DB Icon is red #387
• Added more saveAsCsv options #2099
• Feature Suggestion: Get Connection String for existing connection #1620
• Agent: Enabled button to import queries from sql files #2042
• Copy from query results grid is off by 1 column #1985
• Add VS Code version to About dialog #1998
• double-click not selecting @ in variable name #143
• Typing N” autocompletes to N”’ #1850
• Results Grid Row Indicator Zero Based #2152
• Fix the decimal separator #1317
• SelectBox doesn’t change color when disabled #1624
• Save as JSON/EXCEL/CSV not work #1728
• Shell/Dashboard: Main viewlet icons are draggable and can crash the app #1524
• Can’t use Ctrl+C shortcut to copy from result pane #2091
• Updating causes application icon to be removed/replaced in Windows #1285
• Not able to expand/collapse remote file browser folder by clicking name #1578
• sqlops.desktop [Desktop Entry] – redundant value for Name & Comment #1278
• Edit data: cell doesn’t revert to original value on hitting Escape key #1782

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.

The post The August release of SQL Operations Studio is now available appeared first on Microsoft SQL Server Blog.

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 job’s 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 API’s.

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 aren’t 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 D’Antoni 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:

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

The post The July release of SQL Operations Studio is now available appeared first on Microsoft SQL Server Blog.

Today, we’re excited to introduce a free e-book, SQL Server on Linux: A guide to re-platforming and modernizing your data workloads. This is the third in a series of technical 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 choice—whether that’s Windows, Linux, or even containers.

In this guide, you get the 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 you’re a database architect, administrator, or developer working with data.

Here’s what’s covered:

• An overview of SQL Server on Linux—understand platform options, features, pricing, and more.
• Getting the most out of SQL Server on Linux—review our planning considerations to prepare your Linux system to best support your business goals—such as performance optimization, high availability, or enterprise-grade security.
• Tools and management—get a rundown of the many tools available from Microsoft that make managing your mixed environment easier.
• Migrations and upgrades—discover 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 choice—improving support while enhancing data management and data-driven applications.

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

The post Re-platforming and modernizing your data workloads with SQL Server on Linux appeared first on Microsoft SQL Server Blog.

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 Hyning’s twitter @sqltoolsguy.

The post SSMS 17.8 is now available appeared first on Microsoft SQL Server Blog.

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 Preview extension 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.

If you 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 Cunnane’s blogs:

• Writing a SQL Operations Studio extension in 15 minutes
• Publishing an extension for SQL Operations Studio

Both the Extension Guide and Kevin’s 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.

• Easier way to open large files
• Syntax highlighting optimization
• Highlighted indent guides
• Column Selection using middle mouse button

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.

The post The June release of SQL Operations Studio is now available appeared first on Microsoft SQL Server Blog.