We are excited to announce the August release of SQL Operations Studio is now available.
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:
- Open a query editor with an active connection.
- Open Command Palette (Ctrl+Shift+P), and type Get Current Connection String and then press Enter.
- 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