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.
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.
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:
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.
We can control the SQL Server security configuration using this page. It provides the following configurations.
We can define a server authentication model, Windows or mixed (Windows and SQL) authentication using this option.
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
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
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
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
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.
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
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
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:
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
We can enable, disable and configure change tracking for SQL Server database:
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
- 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.
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.
- Migrating your on-premises SQL databases to AWS RDS SQL Server using AWS DMS - January 25, 2021
- Synchronize logins between Availability replicas in SQL Server Always On Availability Groups - January 21, 2021
- Export Amazon Aurora MySQL or Amazon RDS snapshots to AWS S3 buckets - January 19, 2021