What’s new in SSMS 17.5: Data Discovery and Classification

This post is authored by Alan Yu, Program Manager, SQL Server.

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

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

SSMS 17.5 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.5 comes with several exciting new features:

  • Data Discovery and Classification is a new feature for discovering, classifying, labeling and reporting sensitive data in your databases.
  • Query Editor now has support for a SkipsRows option for the Delimited Text External File Format for Azure SQL Data Warehouse.
  • Showplan has enabled the display of estimated plan button for SQL Data Warehouse.

SSMS 17.5 also includes key bug fixes to Showplan, Query Editor, SMO, and Templates, which can be found in the Release Notes.

Data Discovery and Classification

SQL Data Discovery and Classification introduces a new tool built into SQL Server Management Studio (SSMS) for discovering, classifying, labeling and reporting the sensitive data in your databases.

Discovering and classifying your most sensitive data (business, financial, healthcare, PII, etc.) can play a pivotal role in your organizational information protection stature. It can serve as infrastructure for:

  • Helping meet data privacy standards and regulatory compliance requirements, such as GDPR.
  • Controlling access to and hardening the security of databases/columns containing highly sensitive data.

Data Discovery & Classification includes a set of new capabilities, forming a new SQL Information Protection paradigm aimed at protecting the data, not just the database:

  • Discovery and recommendations – The classification engine scans your database and identifies columns containing potentially sensitive data. It then provides you an easy way to review and apply the appropriate classification recommendations, as well as to manually classify columns.
  • Labeling – Sensitivity classification labels can be persistently tagged on columns.
  • Visibility – The database classification state can be viewed in a detailed report that can be printed/exported to be used for compliance and auditing purposes, as well as other needs.

To learn more, go to our get started page. Try it out and let us know what you think! You can message us on our twitter @SQLDataTools.

The February release of SQL Operations Studio is now available

This post is authored by Alan Yu, Program Manager, SQL Server.

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

The February release includes several major repo updates and feature releases, including:

  • Added Auto-Update Installation feature
  • Added Connection Dialog ‘Database’ Drop-down
  • Added functionality for new query tabs keeping active connection
  • Fixed bugs in SQL Editor and auto-completion

For complete updates, refer to the Release Notes.

Auto-Update Installation

We want to use the February Insiders builds to test the auto-update feature. The 0.26.2 build will be released as an auto-update to 0.26.1 (assuming there are no issues that require publishing a new build to successfully support auto-update).

To discover updates faster, we have added auto-update functionality to ensure you have the latest version of SQL Operations Studio. A notification will pop up on the gear icon on the bottom left as shown in the image above. In addition, you can also check for updates by clicking Help on the toolbar.

Connection Dialog Database Drop-down

The Connection Dialog Database field is now a dynamically populated drop-down list that will contain a list of databases populated from the specified server.

New query tabs keeping active connection

A top voted feature by our users was for new query tabs to keep their active connection. In the February release, users can now click on a server name and press Ctrl + N, which will create a new query tab and retain the active connection instead of having to manually set the connection. This can be seen in the gif below.

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 or tweet us @SQLOpsStuido.

Sync SQL data in large scale using Azure SQL Data Sync

This post is authored by Xiaochen Wu, Program Manager, SQL Server.  

Azure SQL Data Sync allows users to synchronize data between Azure SQL Databases and SQL Server databases in one-direction or bi-direction. This feature was first introduced in 2012. By that time, people didn’t host a lot of large databases in Azure. Some size limitations were applied when we built the data sync service, including up to 30 databases (five on-premises SQL Server databases) in a single sync group, and up to 500 tables in any database in a sync group.

Today, there are more than two million Azure SQL Databases and the maximum database size is 4TB. But those limitations of data sync are still there. It is mainly because that syncing data is a size of data operation. Without an architectural change, we can’t ensure the service can sustain the heavy load when syncing in a large scale. We are working on some improvements in this area. Some of these limitations will be raised or removed in the future. In this article, we are going to show you how to use data sync to sync data between large number of databases and tables, including some best practices and how to temporarily work around database and table limitations.

Sync data between many databases

Large companies and ISVs use data sync to distribute data from a central master database to many client databases. Some customers have hundreds or even thousands of client databases in the whole topology. Users may hit one of the following issues when trying to sync between many databases:

  1. Hit the 30 databases per sync group limitation.
  2. Hit the five on-premises SQL Server databases per sync group limitation.
  3. Since all member databases will sync with the hub database, there’s significant performance impact to workload running in the hub database.

To work around the 30 databases or five on-premises databases per sync group limitation, we suggest you use a multi-level sync architecture. You can create a sync group to sync your master database with several member databases. And those member databases can become the hub databases of the sub sync groups and sync data to other client databases. According to your business and cost requirement, you can use the databases in the middle layers as client databases or dedicated forwarders.

There are benefits from this multi-level sync architecture even you don’t hit the 30 databases per sync group limitation:

  • You can group clients based on certain attributes (location, brand…) and use different sync schema and sync frequency.
  • You can easily add more clients when your business is growing.
  • The forwarders (member databases in the middle layers) can share the sync overhead from the master database.

To make this multi-level sync topology work in your system, you will need a good balance between how many client databases in a single sync group and how many levels in the overall system. The more databases in a single sync group, the higher impact it will add to the overall performance in the hub database. The more levels you have in your system, the longer it takes to have data changes broadcasted to all clients.

When you are adding more member databases to the system, you need to closely monitor the resource usage in the hub databases. If you see consistent high resource usage, you may consider upgrading your database to a higher SLO. Since the hub database is an Azure SQL database, you can upgrade it easily without downtime.

Sync data between databases with many tables

Currently, data sync can only sync between databases with less than 500 tables. You can work around this limitation by creating multiple sync groups using different database users. For example, you want to sync two databases with 900 tables. First, you need to define two different users in the database where you load the sync schema from. Each user can only see 450 (or any number less than 500) tables in the database. Sync setup requires ALTER DATABASE permission which implies CONTROL permission over all tables so you will need to explicitly DENY the permissions on tables which you don’t want a specific user to see, instead of using GRANT. You can find the exact privilege needed for sync initialization in the best practice guidance. Then you can create two sync groups, one for each user. Each sync group will sync 450 tables between these two databases. Since each user can only see less than 500 tables, you will be able to load the schema and create sync groups! After the sync group is created and initialized, we recommend you follow the best practice guidance to update the user permission and make sure they have the minimum privilege for ongoing sync.

Optimize the sync initialization

After the sync group is created, the first time you trigger the sync, it will create all tracking tables and stored procedures and load all data from source to target database. The initial data loading is a size-of-data operation. Initializing sync between large databases could take hours or even days if it is not set up properly. Here are some tips to optimize the initialization performance:

  1. Data sync will initialize the target tables using bulk insert if the target tables are empty. If you have data on both sides, even if data in source and target databases are identical (data sync won’t know that!), data sync will do a row-by-row comparison and insertion. It could be extremely slow for large tables. To gain the best initialization performance, we recommend you consolidate data in one of your databases and keep the others empty before setting up data sync.
  2. Currently, the data sync local agent is a 32 bits application. It can only use up to 4GB RAM. When you are trying to initialize large databases, especially when trying to initialize multiple sync groups at the same time, it may run out of memory. If you encountered this issue, we recommend you add part of the tables into the sync group first, initialize with those tables, and then add more tables. Repeat this until all tables are added to the sync group.
  3. During initialization, the local agent will load data from the database and store it as temp files in your system temp folder. If you are initializing sync group between large databases, you want to make sure your temp folder has enough space before you start the sync. You can change your temp folder to another drive by set the TEMP and TMP environment variables. You will need to restart the sync service after you update the environment variable. You can also add and initialize tables to the sync group in batch. Make sure the temp folder is cleaned up between each batch.
  4. If you are initializing data from on-premises SQL Server to Azure DB, you can upgrade your Azure DB temporarily before the initialization. You can downgrade the database to the original SLO after the initialization is done. The extra cost will be minimum. If your target database is SQL Server running in a VM, add more resources to the VM will do the same.

Experiment of sync initialization performance

Following is the result of a simple experiment. I created a sync group to sync data from a SQL Server database in Azure VM to an Azure SQL database. The VM and SQL database are in the same Azure region so the impact of network latency could be ignored. It was syncing one table with 11 columns and about 2.1M rows. The total data size is 49.1GB. I did three runs with different source and target database configuration:

In the first run, the target database is S2 (50 DTU), and source database is running in D4S_V3 VM (4 vCPU, 16GB RAM). It takes 50 min to extract data to the temp folder and 471 min to load the data from the temp folder to the target database.

I upgraded the target database to S6 (400 DTU) and the Azure VM to D8S_V3 (8 vCPU, 32GB RAM) for the second run. It reduced the loading time to 98 min! The data extracting surprisingly took longer time in this run. I can’t explain the regression since I didn’t capture the local resource usage during the run. It might be some disk I/O issue. Even though, upgrading the target database to S6 reduced the total initialization time from 521 min to 267 min.

In the third run, I upgraded the target database to S12 (3000 DTU) and used the local SSD as temp folder. It reduced data extract time to 39 min, data loading time to 56 min and the total initialization time to 95 min. It was 5.5 time faster than the first configuration with extra cost of a cup of coffee!

Conclusion

  1. Upgrade the target database (Azure DB) to higher SLO will help to improve the initialization time significantly with manageable extra cost.
  2. Upgrade the source database doesn’t help too much since the data extract is an I/O bound operation and 32bits local agent can only use up to 4GB RAM.
  3. Using attached SSD as temp folder will help on the data extract performance. But the ROI is not as high as upgrading target database. You also need to consider if the temp files can fit into the SSD disk.
Runs Target database SLO (Azure DB) Source database SLO (VM) Total Initialization time Data extract time Data load time
1 S2 D4S_V3 521 min 50 min 471 min
2 S6 D8S_V3 267 min *169 min 98 min
3 S12 D8S_V3, Attached SSD 95 min 39 min 56 min

In this article, we provided some best practices about how to sync data using Azure SQL Data Sync service between many databases and databases with many tables. Please find more information about data sync in the online documentation. More data sync best practice is available at Best Practices for Azure SQL Data Sync.

SQL Server 2017 on Linux webcast series

The world’s leading database is now available on Linux by bringing Microsoft SQL Server to Linux, Microsoft continues to embrace open source solutions.

SQL Server 2017 brings the best features of the Microsoft relational database engine to the enterprise Linux ecosystem, including SQL Server Agent, Azure Active Directory (Azure AD) authentication, best-in-class high availability/ disaster recovery, and unparalleled data security.

Note that SQL Server on Linux is not a port or rewrite. This is the same world-class Microsoft relational database management system (RDBMS) now available on more operating systems (like Red Hat Enterprise Linux, SUSE Linux Enterprise Server, and Ubuntu) and more cloud and container platforms (like Docker).

Join us for one or all of a three-part webcast series now available on demand as we explore how SQL Server 2017 brings the industry-leading Microsoft relational database engine to the enterprise Linux ecosystem with our partners from Intel, Red Hat and HPE.

Session One:

SQL Server 2017 on Linux- #1 in price and performance—with massive scale
Learn how you can get record breaking performance with SQL Server on Linux. SQL Server consistently leads in the TPC-E OLTP workload, the TPC-H data warehousing workload, and real-world application performance benchmarks.

Presented by Bob Ward, Microsoft; Nicholas Gerasimatos, Red Hat; Wendy Harms, HPE.

Session Two:

SQL Server 2017 on Linux, Providing Industry leading security
Learn how Security innovations in SQL Server 2017 help secure data for mission-critical workloads with layers of protection, adding Always Encrypted technology along with row-level security, dynamic data masking, transparent data encryption (TDE), and robust auditing.

Presented by Joachim Hammer, Microsoft; Ken LeTourneau, Intel; Nicholas Gerasimatos, Red Hat.

Session Three:

SQL Server 2017 on Linux, In-memory technologies
Learn how in-memory technologies in SQL Server help to optimize the performance of your transaction processing, analytics, as well as hybrid (HTAP) workloads.

Presented by Jos de Bruijn, Microsoft; Ken LeTourneau, Intel; Wendy Harms, HPE.

Register now>>

Webinar: Modernize your applications with cloud and on-premises data solutions from Microsoft

Customers today demand the latest innovations in every solution you deliver. How can you make sure your data infrastructure not only keeps up, but drives innovation?

Data is the core of modern applications. Two key trends that help organizations extract the most from their data are the adoption of cloud technologies and the ability to drive new customer experiences with artificial intelligence. Organizations that modernize and harness data, cloud, and AI outperform their competition and are becoming leaders in their field. The most digitally transformed enterprises earn an additional $100 million in operating income!

Join our speakers Claudia Backus, Prem Prakash, and Frederico Rezende for a webinar on how you can transform your applications and enable new customer experiences using the Microsoft data platform.

In this webinar, you’ll learn:

  • How to leverage the performance, security and flexibility of the entire Microsoft database portfolio from SQL Server 2017 and Azure SQL Database to open-source databases like Azure Database for MySQL and Azure Database for PostgreSQL.
  • How to accelerate your move towards a cloud-based application with the new Azure Database Migration Service.
  • How the Microsoft Data Accelerator program can help you modernize your apps across on-premise and cloud.

Register now for this webinar to find out how Microsoft can help you successfully transform your existing applications and ultimately become a SaaS provider with cloud technologies.

The January release of SQL Operations Studio is now available

This blog post was authored by Alan Yu, Program Manager, Microsoft SQL Server.

We are excited to announce the January 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-main

SQL Operations Studio was announced for public preview on November 15, 2017 at Connect(), and this January release is the second major update since the announcement. If you missed the December release announcement, you can learn more on the SQL Server blog.

The January release includes several major repo updates and feature releases, including:

  • Enable the HotExit feature to automatically reopen unsaved files.
  • Add the ability to access saved connections from Connection Dialog.
  • Set the SQL editor tab color to match the Server Group color.
  • Fix the broken Run Current Query command.
  • Fix the broken pinned Windows Start Menu icon.

For a complete list of updates, please refer to the release notes.

HotExit feature

hotexit

A highly requested feature for SQL Operations Studio is to remember unsaved changes when the program is exited, similar to VSCode and other editors. We are excited to announce that HotExit has been enabled as demonstrated in the GIF above. SQLQuery2 represents an unsaved query, and the user can quit SQL Operations Studio and simply reload it again to retain the unsaved query.

This feature is not enabled by default. To enable HotExit by default, go to Settings with Ctrl + , and  copy “files.hotExit”: “onExit” into your settings.

Saved connections available in Connection Dialog

saved-connections

The Connection Dialog now contains a “Saved Connections” tab next to “Recent Connections”. This contains a list of saved server connections from the server’s “Object Explorer” viewlet, as well as the Server Group.

SQL editor tab color to match Server Group color

sql-editor-tab-color

SQL query editor and dashboard windows will have the same color as the Server Group used to open the window. This feature is not enabled by default. To enable Tab Colors by default, go to Settings with Ctrl + , and  copy “sql.enableTabColors”: true into your settings.

Contact us

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

The December release of SQL Operations Studio is now available

This post is authored by Alan Yu, Program Manager, SQL Server.

We are excited to announce the December 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 December release is the first major update since the announcement.

The December release includes several major repo updates and feature releases, including:

  • Migrating SQL Ops Studio Engineering to public GitHub repo
  • Azure Integration with Create Firewall Rule
  • Windows Setup and Linux DEB/RPM installation packages
  • Manage Dashboard visual layout editor
  • “Run Current Query with Actual Plan” command

For complete updates, refer to the Release Notes.

Migrating SQL Ops Studio Engineering to public GitHub repo

To provide better transparency with the SQL Operations Studio community, we have decided to migrate the Github internal branch to the public repo. This means any bug fixes, feature developments, or even test builds can be publicly viewed before an update is officially announced.

We made this move because we want to collaborate with the community to continually deliver features that our users want. This gives you the opportunity to see our changes in action to address your top voted issues. Visit our GitHub page and give us your feedback.

Azure Integration with Create Firewall Rule

Now let’s get into new features. A common issue when connecting to Azure SQL DB instances is that the connection can fail due to server firewall rules. This would require loading Azure Portal to configure firewall rules so that you can connect to your database, which can be inconvenient.

To speed up this process, we have enabled Azure Integration with Create Firewall Rule dialog. When your connection to an Azure SQL DB instance fails because of firewall settings, this dialog will appear, allowing the user to use their Azure subscription account to automatically configure the client IP address with the server. This retains the same experience as configuration on Azure Portal, except you can do it all through SQL Operations Studio.

Windows Setup installation and Linux DEB/RPM installation packages

We are always looking for new ways to improve the installation experience. With the December release, we have added Windows Setup installation to simplify installation on Windows. This wizard will allow the user to:

  • Select installation location
  • Select start menu folder
  • Option to add to path

In addition to Windows Setup, we have also added Linux DEB/RPM installation packages. These will add new ways for Linux users to download SQL Operations Studio for their choice of installation.

Feel free to try out these new installation experiences on our download page.

Manage Dashboard visual layout editor

In the initial release, there were not many options to customize the visual layout of the dashboards. With the December release, you can now resize and move your widgets by enabling the visual layout editor mode by clicking the pencil on the top right of the Manage Dashboard screen. This gives users greater control of their dashboard in addition to building their own custom insight widgets.

 

Run Current Query with Actual Plan command

Another new feature we have enabled is Run Current Query with Actual Plan, which is a command that will execute the current query and return the actual execution plan with the query results. This feature area is still in-progress as we work through the best UX for integrating this command directly into the query editor. While that design work is in-progress the functionality is still available via the Command Palette and you can define a keyboard shortcut if using this feature frequently.

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 or tweet us @sqlopsstudio.

Bring clarity to complex data with graph databases on SQL Server 2017

SQL Server 2017, in addition to processing relational data, now fully integrates with graph database models, all on the same familiar system. This will bring clarity to the increasing amounts of data businesses generate every day.

What’s the difference between graph and relational databases?

Relational databases, like SQL Server, use foreign keys to manage relationships between entities and tables. Foreign keys adequately query one-to-many relationships; however, as relationships between various data entities become more complex, queries also become more complex and performance may diminish as a result.

In these cases, developers may opt for graph database models to manage complex relationships and enable operational agility. In a graph database, edges are heterogeneous in nature—a single edge can be used to connect different type of nodes to each other. This is not easy to achieve using foreign keys in a relational database. For example, consider a social graph where a person (node) likes another person (edge) or organization (node) or restaurant (node). Here the same ‘likes’ edge is used to connect three different types of nodes and entities to each other—person to person; person to organization; and person to restaurant.

So, how do graph databases work?

Graph databases are comprised of a collection of nodes and edges. Nodes represent a given entity (a noun), while edges (a verb) represent the relationship between the two nodes they connect. For example, pitcher Randy Johnson (node) faces (edge) batter John Kruk (node).

When do you choose a graph database model over a relational model?

While graph and relational schema are capable of achieving the same ends, graph models make it easier to express certain queries. Since it’s easier to manage many-to-many relationships, graph models are better suited for applications where relationships between data entities are the focus. They’re also better for applications that need to traverse connected data sources to generate insights, such as applications with hierarchical data where the queries may involve traversing the hierarchies. Graph databases allow you to express traversal or navigation queries easily, using join-free pattern matching.

The T-SQL Graph extension offers the best of both worlds.

If you’ve already licensed SQL Server 2017, there’s no need to purchase a second system to manage more complex database needs. SQL Server 2017 fully integrates with Transact-SQL (T-SQL) to create and query graph objects. Maintain your existing SQL Server database and benefit from pairing graph capabilities with other SQL Server technologies like columnstore, HA, R services, and others. SQL graph database also supports all the security and compliance features available with SQL Server.

Download and try SQL Server 2017 on the platform of your choice today, and see for yourself.

Try mssql-cli, a new interactive command line tool for SQL Server

This post is authored by Alan Yu, Program Manager, SQL Server.

We are excited to announce the Public Preview release of mssql-cli, a new and interactive command line query tool for SQL Server. This open source tool works cross-platform and is a proud member of the dbcli community.

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

Read on to learn more about mssql-cli features, how to submit feature requests or issues, and our open source collaboration story to bring you this great tool.

mssql-cli auto-completion that is context aware

Features

Mssql-cli is a new and interactive command line tool that provides the following key enhancements over sqlcmd in the Terminal environment:

  • T-SQL IntelliSense
  • Syntax highlighting
  • Pretty formatting for query results, including Vertical Format
  • Multi-line edit mode
  • Configuration file support

Mssql-cli aims to offer an improved interactive command line experience for T-SQL. It is fully open source under the BSD-3 license, and a contribution to the dbcli organization, an open source suite of interactive CLI tools for relational databases including SQL Server, PostgresSQL, and MySQL. The command-line UI is written in Python and the tool leverages the same microservice backend (sqltoolsservice) that powers the VS Code SQL extension, SQL Operations Studio, and the other Python CLI tool we announced earlier, mssql-scripter.

mssql-cli multi-line queries to build readable queries. Also includes syntax highlighting and “smart” auto-completion.

Try it now

In order to install mssql-cli, you must have Python on your machine. Please refer to the installation guide for OS-specific methods to get Python.

Mssql-cli is installed via pip. If you know pip, you can install mssql-cli using command.

$ pip install mssql-cli

This command may need to run as sudo if you are installing to the system site packages. Mssql-cli can be installed using the –user option, which does not require sudo.

$ pip install --user mssql-cli

If you are having installation issues or would like more specific instructions, please see the installation guide.

Once you have mssql-cli installed, connect to your database with the command:

$ mssql-cli -S <server name> -U <user name> -d <database name>

Contact us

We are open to any questions, feedback, or any feature suggestions for future releases, which can be submitted on our GitHub Issues.

Our Open Source Story

Over the years, customers have told us we need to modernize sqlcmd. This feedback got significantly amplified after SQL Server 2017 was released on Linux and macOS (Docker). Customers asked us for richer experiences on the command line including tab completion for T-SQL keywords and database objects and syntax highlighting.

Thus, the SQL Developer Experiences team set out to create a modern version of sqlcmd. As we worked through our scenarios, we discovered an awesome and immensely popular set of open source command line tools for databases on GitHub at https://github.com/dbcli maintained by the dbcli organization. The repo contains open source cross-platform CLI tools for databases, which support “smart” auto-completion, syntax highlighting and easy installation. We were particularly impressed with the pgcli and mycli tools, as well as how active and passionate their community was in creating better database cli tools.

We reached out to Amjith Ramanujam, the founder of the dbcli organization, did an in-depth demo, explained what we were trying to do, and explored if mssql-cli could be included in the dbcli family. Simply put, Amjith and the other maintainers of the project were delighted and welcomed us with open arms!

This is a momentous occasion for the SQL Developer Experiences team because it represents the first time our team is contributing source code to an existing open source organization with a commitment to be a good citizen in an existing open source community. It is also an exciting moment to build an awesome tool by collaborating with the OSS community, which we will continue to do in the future.

What’s new in SSMS 17.4: SQL Vulnerability Assessment

This post is authored by Ronit Reger, Senior Program Manager, SQL Data Security and Alan Yu, Program Manager, SQL Server

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

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

SSMS 17.4 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.4 comes with an exciting new feature: SQL Vulnerability Assessment!

What is Vulnerability Assessment?

SQL Vulnerability Assessment (VA) is your one-stop-shop to discover, track and remediate potential database vulnerabilities. It can be used as an excellent preventative security measure, providing visibility into your security state and offering actionable steps to investigate, manage and resolve security issues and enhance your database fortifications. It is designed to be usable even for non-security-experts – getting started and seeing an initial actionable report takes only a few seconds.

Vulnerability Assessment report in SSMS

VA truly enables you to focus your attention on the highest impact actions you can take to proactively improve your database security stature! In addition, if you have data privacy requirements, or need to comply with data protection regulations like the EU GDPR – then VA is your built-in solution to simplify these processes and monitor your database protection status. For dynamic database environments where changes are frequent and hard to track, VA is invaluable in detecting the settings that can leave your database vulnerable to attack.

How does Vulnerability Assessment work?

The VA service runs a scan directly on your SQL database or server. VA employs a knowledge base of rules that flag security vulnerabilities and deviations from best practices, such as misconfigurations, excessive permissions, and exposed sensitive data. The rule base grows and evolves over time, to reflect the latest security best practices recommended by Microsoft.

Results of the assessment include actionable steps to resolve each issue and provide customized remediation scripts where applicable. An assessment report can be customized for each customer environment and tailored to specific requirements. This process is managed by defining a security Baseline for the assessment results, such that only deviations from the custom Baseline are reported.

VA is supported for SQL Server 2012 and later, and can also be run on Azure SQL Database.

Get started now!

To gain the benefits of a Vulnerability Assessment on your database, all you need to do is run a Scan, which will scan your database for vulnerabilities. The scan is lightweight and safe. It takes a few seconds to run and is entirely read-only. It does not make any changes to your database!

To learn more about VA, check out this demo on Channel 9:

Also, take a look at Getting Started with Vulnerability Assessment for more details on how to run and manage your assessment.

Try it out, and let us know what you think!