Microsoft Azure Database Administrator - Interview Questions

Please reach us at contactus@crystalspiders.in if you cannot find an answer to any question.

When setting up an Azure database for PostgreSQL, which deployment mode enables you to start and stop the server?

Answer: Flexible server

Flexible server installations can be halted for a maximum of seven consecutive days to reduce computing costs.

Explore more about Hyperscale, Self-Managed server and Single server deployment modes

What are the installations required for Azure Database for MySQL?

Answer: Version less

MySQL installations are created at a specific release version

Explore more about Fully Managed and IaaS installations

Fully Managed installation is an example of PaaS product offering.

MariaDB is closely associated with which other open-source database platform?

Answer: SQL Server

Which Azure product enables you to operate the identical SQL Server version and operating system found in on-premises settings?

Answer: Azure SQL Database

Azure SQL Database is not 100% identical to on premises installations

What capability of Azure SQL Database enables it to instantly access new features as soon as they are created by Microsoft?

Answer: High Availability

High Availability is a feature that ensures that as database is always accessible.

Which product classification enables users to manage and configure the operating system on a cloud-hosted server?

Answer: SaaS

Explore more about PaaS and IaaS

What about the geo-redundant storage for SQL Server VM database backups?

Answer:

- The backup copies are stored in a different region than the main database server.

- The backup copies can provide read-only access to the data.

- It creates six copies of your database backups.

You are required to transfer several databases from on-premises SQL Server and Oracle servers to a new Azure SQL Server virtual machine. Which option would be the most suitable to utilize?

Answer: Azure Data Migration Service

Explore more about the Database Migration Assistant tool, Azure Import / Export Service and Detach and Re-attach database files

When determining the cost of a virtual machine, what advantage does the B series service level offer?

Answer: Compute capacity can scale to high levels of performance for short periods of time.

Pricing for this service level does not require that you pay for the full compute capacity of the processors all the time. This is the main differentiator from A series service levels.

You place two VMs in different availability zones. what will this ensure?

Answer: The two VMs will be in different data centers within the same region. Availability zones exist within the same geographical region.

Organizations involved in the software assurance program are allowed to supply their own pre-paid license for SQL Server. What is the acronym used to refer to this method?

Answer: BYOL

Bring Your Own License

What are the two service tier purchasing models that can be used to provision an elastic pool?

Answer: vCores and DTUs

The two service tiers use either the vCore or DTU pricing model.

With Azure SQL Database, how frequently are differential backups created automatically?

Answer: Every 12 hours

Full backups are made weekly, differential every 12 hours and log backups for every 5-10 minutes.

What service tier of Azure SQL DATABASE includes the Auto-Pause feature?

Answer: Serverless compute

Explore more about DTU pricing, Provisioned compute and Hyperscale

Which deployment method should you select to reduce costs when you require multiple Azure SQL databases that need to share resources?

Answer: Elastic pool

Explore more about Resource Allocated, Balanced Load and vCore

You need to transfer a database that is very big (assume 30 terabytes) in size to Azure SQL Database. Which service tier should you select?

Answer: Hyperscale

This allows databases to grow up to 100 terabytes

Explore more about Provisioned, Business Critical and Serverless

What are the maximum number of databases in a SQL Managed Instance?

Answer: 100

Managed instances have a 100 database limit per instance.

What are the benefits of a SQL Managed Instance?

Answer:

Administrators can access TempDB
Administrators can use SQL Agent
Managed instances support automatic backups

What are the database roles are available in Azure SQL Database?

Answer: Loginmanager and DBmanager

These are two roles specific to Azure SQL Database

What steps must be taken prior to enabling Azure Active Directory authentication?

Answer: The server must have an Active Directory administrator

The administrator account is required in order to grant additional Active Directory user's permissions within the database

Which role will allow database principals to execute INSERT, UDPATE and DELETE statements?

Answer: db_datawriter

Which database login method supports multi-factor authentication?

Answer: Azure Active Directory

Explore more about SQL Server Authentication, Windows Authentication and Azure Logins for Windows

Which type of firewall rule cannot be established through the portal and must instead be created using T-SQL stored procedures?

Answer: Database level firewall rules

What is the T-SQL key word will create a database user form an Azure Active Directory login?

Answer: FROM EXTERNAL PROVIDER

With Always Encrypted, where does the data decryption and encryption process take place?

Answer: Client Side

In a database protected by TDE, in which of these scenarios is the data in an encrypted state?

Answer: The data is currently being backed up.

Which data security technology displays dates as "January 1, 1900" to unauthorized users?

Answer: Dynamic Data Masking

Which system catalog view is users to obtain data sensitivity classification information in SQL Server 2019?

Answer: sys.sensitivity_classifications

What are the sensitive lable's that can be applied to classified data?

Answer: Highly Confidential, Confidential and Sensitive

What are the reasons for enabling the Azure Monitor service on a SQL Server virtual machine?

Answer: To review the past performance of the server hardware such as CPU, memory and a storage latency. Azure monitor gives insights into the Azure hardware that the virtual machine is running on.

Which component of an alert rule defines when the alert will be triggered?

Answer: Condition

Alert rules need a signal and logic to define when they will trigger.

The built-in performance monitor tool of the Windows operating system graphs system metrics, known as?

Answer: Counters

You want to track the data gathered by Azure Intelligent Insights from within the portal. Which storage option should you select?

Answer: Log Analytics workspace

This option allows you to view the data within the portal

Explore more about Event Hub, Storage Account and Partner Solution.

What factors influence the maximum IOPS and throughput of the data drives available on an Azure virtual machine?

Answer: VM Performance Tier or size

Which Azure storage option is the most suitable for database backup files?

Answer: Blob storage

This is the best choice for large data files such as images, videos, and database backup's.

Explore more about Disk storage, azure files and table storage

Why we need to enable and configure Resource Governor?

Answer: To allocate resources based on workload types

Resource Governor places user sessions into workgroups that are assigned to specific resource pools.

Does Azure SQL Database allow end users to configure resource allocations with Resource Governor?

Answer: No.

What do index statistics measure?

Answer: The distribution of unique values within a data set.

What is the minimum version of SQL Server supports Adaptive Query Processing feature?

Answer: SQL Server 2017

You possess a database that was created on SQL Server 2012. You observe that the performance of queries declines after upgrading to SQL Server 2019 and setting the compatibility level to 150. Which database scoped option should you activate until you can make enhancements to the queries?

Answer: Legacy Cardinality Estimation

This will force the server to use the older cardinality estimator that was in use prior to 2014.

Describes an index scan operation?

Answer: Every row from the table is read. Scans access all of the rows from the table.

In query execution plan diagram, what does the thickness of the arrows between operator nodes represent?

Answer: The relative number of rows that will be passed to the next operation.

What Dynamic Management View returns fragmentation details for specific indexes?

Answer: sys.dm_db_index_physical_stats

This DMV is useful for determining if it is time to perform an index reorg or rebuild.

What does the SQL Server Query Store maintain information about?

Answer: Historical query plans and runtime statistics.

By comparing the performance of different plans, SQL Server can better locate optimal plans.

Which SQL Server platform contains a feature of Automatic Index Tuning that will automatically create and drop indexes?

Answer: Azure SQL Database only.

While all versions of SQL Server starting with 2017 include some Automatic Index Tuning features, only Azure SQL Database will automatically create or drop indexes.

What is the usage of sys.dm_exec_requests DMV?

Answer: It returns information about each query request that is currently executing in SQL Server.

Which storage type for Extended Events does not persist the collected data to disk storage?

Answer: Ring Buffer

The ring buffer is useful for live monitoring since the collected data is temporarily stored in memory and is discarded when the Extended Event session ends.

Explore more about Pair Matching, Event Counter and Event File

What are the components of an Azure Resource Manager template?

Answer: Variables, Resources and Parameters

Deployments are not component of an Azure Resource Manager template.

Azure Resource Manager templates are written in what language?

Answer: JSON

Active-Geo Replication is available for what Azure Data Platform product?

Answer: Azure SQL Database

Why would you locate multiple Virtual Machines in different availability sets?

Answer: To minimize latency between them since they'll be in the same data center.

Availability Sets ensure that each VM will be in the same datacenter but will not share hardware resources.

Which high availability option leverages a pool of shared storage for SQL Server databases?

Answer: Dynamic Data Masking

Which high availability option leverages a pool of shared storage for SQL Server databases?

Answer: Always On Failover Cluster Instances

Which of these is a measure of the amount of data that an organization is willing to lose in a disaster?

Answer: RPO (Recovery Point Objective)

The RPO is the amount of data, measure in minutes, that can be lost during disaster recovery.

Explore more about RTO, RIO and ROA

What is the primary difference between an Auto Failover Group and Active-Geo Replication?

Answer: Auto Failover Groups will re-route traffic to available replicas.

How many quorum votes are available in a two-node Windows Server Failover Cluster?

Answer: One

Why would you add additional witnesses to a Windows Server Failover Cluster?

Answer: To increase the number of available quorum votes.

You possess a Windows Server Failover Cluster consisting of three nodes along with a cloud witness. What is the highest number of failures that the cluster is capable of enduring?

Answer: Two successive node outages.

Two nodes can go offline, as long as they do son one-at-a-time.

Before establishing an Always On Availability Group, the NT AUTHORITY\SYSTEM user account needs to be assigned specific permissions. Which of the following is NOT a necessary permission?

Answer: ALTER ANY DATABASE

What are the requirements for setting up an Always On Availability Group?

Answer:

All nodes in the group must have SQL Server installed.
All nodes in the group must be members of the same Windows Server Failover Cluster.
The Always On Availability Groups feature must be enabled in each node's SQL Server Configuration Manager.

Two nodes are set to replicate transactions synchronously. When is a transaction committed on the primary replica?

Answer: After it is committed on the secondary replica.

Transactions are hardened on the secondary replica first, before they are committed on the primary replica.

What initial data synchronization option will automatically transfer databases from the primary replica to all secondary replicas during the configuration of an Availability Group?

Answer: Automatic Seeding

This method will transfer databases directly from the primary node to the secondaries.

Explore more about "Full Database and Log Backup" and " Join Only"

How often are differential backups made of Azure SQL Databases?

Answer: Every 12 hours

Full backups for Azure SQL Database are created once a week, a differential backup is made every 12 hours, and transaction log backups are made every 5-10 minutes.

Azure backup for SQL Server running in Azure VMs stores full, differential, and log backups in an Azure resource called what?

Answer: Recovery Servies Vault

What backup type cab be used for point-in-time restores?

Answer: Transaction Log backup

Explore more about Full database and Differential backups

Microsoft Azure Database Administrator - Interview Questions

This website uses cookies.