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:
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:
In this extension, you can see an extension 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:
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:
It opens the dialog box for server property in Azure Data Studio similar to SQL Server Management Studio:
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:
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.
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
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
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
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.:
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.
It opens the following database properties 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
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:
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
Change tracking
We can enable, disable and configure change tracking for SQL Server database:
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
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:
It opens the following wizard that we use for generating a script for database objects:
We can use article Six different methods to copy tables between databases in SQL Server and refer the following section to learn this wizard.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023