Rajendra Gupta
Server property dialog box

Database Administration Tool Extension for Windows in Azure Data Studio

December 17, 2019 by

Azure Data Studio provides a modern and productive experience for managing on-premise and cloud-based SQL Server instances. Previously, we discussed many useful features of it, and you can explore these articles in Azure Data Studio category.

Microsoft provides monthly release of Azure Data Studio and provides enhancements to its features for bringing exciting features and bug fixes. At the time of writing this article, the latest release of ADS is 1.13.1 (release date – November 15, 2019).

Install or update ADS to the latest version on appropriate platform Windows, macOS or Linux:

Azure Data Studio installation

An extension adds new features to Azure Data Studio. We can download extensions from the marketplace in-built in ADS. In this article, we will explore the SQL Server Schema Compare extension.

Overview of Database Administration Tool Extension

Launch Azure Data Studio and navigate to extensions in the marketplace:

Extensions overview

In this extension, you can see an extension Database Administration Tool Extension for Windows:

Database Administration Tool Extension for Windows

It is a recommended extension by Azure Data Studio. Installation is straightforward for this. Click on the Install button, and it enables the extension globally:

Installation of Database Administration Tool Extension for Windows

We do not require a restart of ADS. This extension provides the following features:

  • SSMS Property Dialog
  • Generate script wizard

SSMS property dialog box

You might be familiar with server and database property dialog in SSMS. We get much useful information using this property dialog. Once you have installed database administration tool extension, connect to a SQL instance.

Server property dialog in Azure Data Studio

We get SSMS property dialog for the server and database properties.

Right-click on the server instance and you get an option Properties in the contex menu bar:

Server property

It opens the dialog box for server property in Azure Data Studio similar to SQL Server Management Studio:

Server property dialog box

It is interesting. Right! You can check the SQL instance property similar to SSMS in the Azure Data Studio as well. Let’s explore the information provided in the server property dialog box.

General

This page gives information about SQL Server instance, edition, version, host operating system, language, RAM (total server memory), processors, language, SQL Server installation root directory, server collation. We can also determine whether the SQL instance is part of a cluster or not.

Memory

It is a best practice to define a minimum and maximum memory for SQL instance. SQL Server 2019 introduced memory configurations in the installation wizard as well. In this memory section, we can view existing memory configurations and define the minimum, maximum memory appropriately. We can also control index creation memory and minimum memory per query as well. We should make changes with these parameters if required to do:

Memory page

Processors

We can define the processors, their affinity setting for SQL Server using this page. By default, SQL Server uses all processors available in the system. You should be careful in making changes in processors for SQL Server. It might affect queries performance, as well.

Another configuration is maximum worker threads. By default, SQL Server uses the maximum worker threads as per the logical processors. You can follow the article Max Worker Threads for SQL Server Always on Availability Group databases for more details. It’s default value is zero.

Processors configurations

Security

We can control the SQL Server security configuration using this page. It provides the following configurations.

Server Authentication

We can define a server authentication model, Windows or mixed (Windows and SQL) authentication using this option.

Login auditing

SQL Server provides individual options for auditing SQL Server logins. Here we can define the following options:

  • No audit login
  • Audit logins for failed logins
  • Audit logins for successful logins only
  • Audit logins for both failed and successful logins

Security configuration

Apart from this, we can define the following configurations as well:

  • Enable and define the server proxy account
  • Enable common criteria compliance
  • Enable C2 auditing
  • Cross-database ownership chaining

Connections

In the connections page, we define the following options:

  • Maximum number of concurrent connections
  • We can enable query governor for preventing long-running queries
  • We can define connection options such as ANSI Warning, ANSI Padding, quote identifier, No Count. We should be careful in changing these options at the instance level as it might change query behavior. It is advisable to set query options at connection level for an individual session
  • By default, SQL Server allows remote connections with query timeout 600 seconds

Database settings

We can define the following configurations in this database-setting page:

  • Default index fill factor
  • Backup and restore setting such as enable compress backup, enable backup checksum for all database backups
  • We can also define the recovery interval in minutes. We should be cautious while making a change in this parameter. It might affect database recovery and availability
  • We also define default database locations for data file, log file and backup. By default, any new SQL Server database uses this path unless we specify a separate directory explicitly

Database setting page

Advanced

In the advanced page, we do SQL Server instance advanced configurations that affect overall server and query behavior.

  • Enable or disable contained database
  • Define SQL FILESTREAM feature access level
  • We can define the blocked process threshold
  • We can enable query optimizer behaviour for Ad-hoc workloads
  • We can define parallelism settings such as the Cost threshold for parallelism and the max degree of parallelism. We should change this configuration with proper testing in the lower environment

Advanced page

Permissions

In this page, we can grant or revoke server-level permissions such as alter any database, alter any login, alter trace, create server role, view any database, view any definition, etc.:

Server permissions

Database properties dialog

We can also check database property similar to SSMS in the Azure Data Studio admin tool extension as well.

Right-click on the database and click on Properties.

Database properties

It opens the following database properties page:

Database properties general page

Let’s explore the database properties configurations as well.

General

We get the following information on the general page:

  • Database name
  • Database status
  • DB owner
  • DB creation date and size
  • Number of users in the database
  • Database collation
  • Memory allocated to memory optimized objects
  • Database last full and log backup timestamp

Files

It gives the following configurations:

  • View or change the database owner
  • View the database files (primary, secondary and log file) along with its path, filegroup and auto-growth configuration
  • Add or remove database secondary or additional log files. We cannot remove the primary data file and transaction log file

database files

Filegroups

We can view and add filegroup for database files in this page. If we use SQL FILESTREAM and Memory-optimized feature, we define FILESTREAM and memory-optimized filegroup on this page:

filegroup information

Options

We control the database behavior using the configurations available in Azure Data Studio. These configurations options are as follows:

  • Database collation
  • Recovery model – full, bulk-logged and simple recovery
  • Database compatibility level
  • Containment option
  • Database scoped configurations such as MAXDOP, Parameter sniffing, legacy cardinality estimation. This configuration depends upon the SQL Server version, and you might get different options
  • Query Configuration – snapshot isolation, delayed durability, Parameterization, ANSI options
  • Recovery configuration – Page verification and target recovery
  • Database states – database read-only, restrict access

database options

Change tracking

We can enable, disable and configure change tracking for SQL Server database:

Change tracking

Permissions

We can view and modify database properties using this permissions section. A few useful configurations are, as follows:

  • Alter any database
  • Alter any database trigger
  • Alter any schema
  • Create function, procedure, full-text catalog
  • Showplan
  • View database state
  • View definition

database Permissions

Mirroring, Transaction log shipping and Query Store

We can use the corresponding page for configuration of the database mirroring, transaction log shipping and query store.

Generate Scripts in database admin tool extension of Azure Data Studio

We can also generate scripts in a database similar to an SSMS generate script wizard.

Right-click on a database and click on Generate scripts:

Generate Scripts in database admin tool extension of Azure Data Studio

It opens the following wizard that we use for generating a script for database objects:

Generate script wizard

We can use article Six different methods to copy tables between databases in SQL Server and refer the following section to learn this wizard.

Article reference

Conclusion

In this article, we explored Database Administration Tool Extensions for Windows and its usage for database administrators. It is useful for database developers as well to check specific configurations that might affect query performance. You should explore this tool in the Azure Data Studio.

Rajendra Gupta
1,305 Views