DP-300 MS Azure Database Administrator Exam Prep Flashcards

Question

Can transactional replication be configured from the Azure Portal? A. No, transactional replication can only be configured through SQL Server Management Studio B. Yes, transactional replication can be configured from the Azure Portal C. No, transactional replication can't be configured from the Azure Portal (Migrate SQL Server workloads to Azure SQL Database)

Answer 1

C. No, transactional replication can't be configured from the Azure Portal. Transactional replication requires tools like SQL Server Management Studio (SSMS) or Transact-SQL commands for configuration Transactional replication is a method used in SQL Server to replicate data from a publisher database to one or more subscriber databases. Here's how it works and how to configure it: How Transactional Replication Works 1. **Snapshot Creation**: Initially, a snapshot of the publication database objects and data is taken. 2. **Incremental Changes**: After the snapshot, any changes made at the publisher (like inserts, updates, or deletes) are delivered to the subscriber in near real-time. 3. **Consistency**: Changes are applied to the subscriber in the same order and transaction boundaries as they occurred at the publisher, ensuring transactional consistency. Configuration Steps Transactional replication cannot be configured directly from the Azure Portal. Instead, you use tools like SQL Server Management Studio (SSMS) or Transact-SQL commands. Here's a brief overview: 1. **Prepare the Publisher**: Install SQL Server and the AdventureWorks sample database. Ensure the SQL Server Agent is running. 2. **Create a Publication**: Use SSMS to define the articles (tables or objects) you want to replicate. 3. **Set Up the Distributor**: Configure the distribution database to manage replication jobs. 4. **Add Subscribers**: Connect the subscriber databases and validate the subscription. 5. **Monitor Replication**: Use SSMS to monitor latency and ensure data consistency.

Answer 2

Platform As A Service (PAAS)

Answer 3

The individual database properties page

Answer 4

Ultra Disk

Answer 5

Data Migration Assistant

Answer 6

Azure Reserved VM Instances

Answer 7

Azure SQL Managed Instance

Answer 8

In SQL Server, distributed transactions are transactions that span multiple databases or multiple servers. They allow operations across different data sources (like another SQL Server instance or even a non-SQL Server system) to be treated as a single atomic unit—either everything commits, or everything rolls back, just like a local transaction. How It Works Distributed transactions are managed using the Microsoft Distributed Transaction Coordinator (MSDTC), which ensures the two-phase commit protocol is followed: Prepare phase: All involved systems agree they’re ready to commit. Commit phase: If all say yes, then commit. If any say no, rollback. Example Scenario Say you have: A local SQL Server instance (ServerA) A remote SQL Server instance (ServerB) You want to insert a row into a table on both servers, and either both succeed or both fail: sql Copy Edit BEGIN DISTRIBUTED TRANSACTION; INSERT INTO ServerA.Database1.dbo.Table1 (Col1) VALUES ('Test1'); INSERT INTO ServerB.Database2.dbo.Table2 (Col2) VALUES ('Test2'); COMMIT TRANSACTION; If one INSERT fails, the whole transaction rolls back. Key Points Uses linked servers to access remote data. Requires MSDTC service running and properly configured on all involved servers. Can involve SQL Server, Oracle, or other systems if using compatible providers. Slower than local transactions due to the overhead of coordination. Security & Config Ensure firewall ports for MSDTC are open. Servers must trust each other (usually same domain or trusted domains).

Answer 9

Azure SQL Database Serverless

Answer 10

Azure SQL Database Hypescale

Answer 11

create a dump and restore it at the higher version.

Answer 12

db_securityadmin

Answer 13

Ownership Chaining Ownership Chaining in SQL Server is a security feature that allows users to access multiple objects (like tables, views, stored procedures) without requiring explicit permissions on each object—as long as all the objects are owned by the same user. Think of it like this: If you own a stored procedure, and that procedure accesses a table you also own, SQL Server won’t re-check whether you have permission on that table. It trusts the chain of ownership. How it works: A user has permission to execute a stored procedure. The stored procedure queries a table. If the procedure and the table have the same owner, SQL Server does not check the user’s permission on the table. If the owners are different, SQL Server will check permissions. Why It Matters Helps simplify permissions by letting you secure at the top level (stored procedures). Lets developers encapsulate logic and protect raw data. Reduces surface area for security mistakes.

Answer 14

Master Key In SQL Server, Transparent Data Encryption (TDE) is a security feature that encrypts the entire database at rest—meaning the data and log files are encrypted on disk, but without changing how you use the database. What "Transparent" Means It’s invisible to applications and users—no changes needed in your queries, connection strings, or stored procedures. SQL Server handles the encryption and decryption automatically in the background. Why Use TDE? Protects data files from unauthorized access if they're stolen (like backup files or physical disks). Helps with regulatory compliance (e.g., GDPR, HIPAA, PCI-DSS). Works at the storage level, unlike column-level encryption which targets specific data. How It Works (High-Level Steps) Create a Database Master Key (DMK) in the master database. Create a Certificate protected by the DMK. Create a Database Encryption Key (DEK) in the target database. Enable TDE for the database using the DEK and certificate. What Gets Encrypted? Data files (.mdf, .ndf) Log files (.ldf) Backups (automatically encrypted if taken from a TDE-enabled DB) Important Notes TDE does not protect data in memory or in transit. Back up the certificate and private key—you’ll need them to restore the database on another server. Minimal performance impact (~2–5%).

Answer 15

Always Encypted Always Encrypted in SQL Server is a data protection feature that encrypts sensitive data such as credit card numbers or SSNs inside the client application, so the data is never visible in plaintext to SQL Server, including DBAs or admins. Key Idea: Data is encrypted on the client side, sent encrypted to the server, stored encrypted, and only decrypted by the client. Use Case: If you need strong privacy for certain columns (like SSNs, credit cards, PHI), and you don’t fully trust the DB server or admins, use Always Encrypted. How It Works Encryption keys are generated and stored securely outside the database (in Azure Key Vault, Windows certificate store, or a secure store). A client driver (like .NET or JDBC) handles encryption and decryption transparently. SQL Server stores the ciphertext only—no access to plaintext. Types of Encryption Deterministic: Always encrypts the same value to the same ciphertext. Allows equality comparison (WHERE, JOIN) Vulnerable to pattern analysis. Randomized: Produces different ciphertexts for the same value. Stronger security No search, filter, or join support. Key Differences vs Transparent Data Encryption (TDE) Feature Always Encrypted Transparent Data Encryption (TDE) Encrypts at Column level Database level Data in memory Encrypted Decrypted Data in transit Encrypted Not encrypted SQL Server can see? No Yes Granularity Fine-grained (columns) Coarse (entire DB) Limitations Not all SQL Server operations are supported (e.g., LIKE, range queries). Requires compatible client drivers. Can’t decrypt data on the server side (e.g., for indexing, triggers).

Answer 16

Private Link

Answer 17

Executing a TSQL statement

Answer 18

SQL Injection

Answer 19

Page Life Expectancy

Answer 20

The SQL VM Resource Povider

Answer 21

Read Committed Snapshot Isolation

Answer 22

Sys.dm_tran_locks

Answer 23

Sys.query_store_queries Query Store in SQL Server is like a "black box recorder" for your database's performance—it captures and tracks the history of query execution plans and runtime statistics, so you can analyze performance issues, identify regressions, and force good plans if needed. Purpose: Helps DBAs and developers answer: "Why did this query suddenly slow down?" "What execution plan was used last week?" "Can I force a better plan for this query?" What It Tracks: Query text Execution plans (good or bad) Runtime stats (CPU, duration, logical reads, etc.) Plan changes over time Use Cases: Troubleshooting regressions (e.g., after a deployment) Forcing a stable execution plan: sql Copy Edit EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456; Performance tuning based on long-term trends Comparing plan performance over different time windows Available In: SQL Server 2016 and later Azure SQL Database Pro Tips: It’s lightweight, but does use space—watch your Query Store size settings. Combine with Automatic Plan Correction (in Azure or SQL Server 2022) to auto-fix regressions.

Answer 24

Disk Storage

Answer 25

Batch Mode on Row Store

Answer 26

Resource Pools

Answer 27

Azure SQL Database

Answer 28

sys.dm_db_tuning_recommendations

Answer 29

Estimated execution plan

Answer 30

sys.dm_db_index_usage_stats

Answer 31

Snowflake Schema

Answer 32

Columnstore Archival

Answer 33

Clustered Columnstore

Answer 34

sys.dm_exec_quert_plan_stats

Answer 35

A mail profile

Answer 36

Ring Buffer

Answer 37

Extended Events in SQL Server are a lightweight and highly configurable performance monitoring system. They allow you to collect detailed data about the internal operations of the database engine, making them useful for troubleshooting and performance analysis. Introduced in SQL Server 2008, Extended Events replaced the older SQL Profiler due to its lower impact on system performance. With Extended Events, you can monitor various aspects of SQL Server, such as query performance, deadlocks, and long-running queries. You can create event sessions to specify which events to track and how to store the collected data. SQL Server Management Studio (SSMS) provides a graphical interface for managing Extended Events, making it easier to create, modify, and analyze event sessions

Answer 38

Target Group

Answer 39

The point to which data needs to be recovered after a failure.

Answer 40

A load balancer

Answer 41

Powershell

Answer 42

Azure Site Recovery

Answer 43

Azure Arc is a solution from Microsoft that extends Azure's management and governance capabilities to resources outside of Azure, including on-premises environments, other cloud platforms, and edge devices. It provides a unified platform to manage diverse infrastructure and applications, enabling consistent operations across hybrid and multi-cloud environments. Key Features of Azure Arc: - Centralized Management: You can manage servers, virtual machines, Kubernetes clusters, and databases as if they were running in Azure. - Hybrid Cloud Integration: Azure Arc enables you to bring Azure services like Azure Policy, Azure Monitor, and Microsoft Defender for Cloud to non-Azure resources. - Azure Data Services: Run Azure SQL Managed Instance and PostgreSQL on any infrastructure, with benefits like elastic scaling and automated updates. - Kubernetes Management: Attach and govern Kubernetes clusters anywhere, using tools like GitOps for configuration management. Azure Arc is particularly useful for organizations with complex environments spanning multiple clouds and on-premises setups. It simplifies governance, enhances security, and supports modern DevOps practices.

Answer 44

Azure Express Route or a VPN Gateway

Answer 45

Log Replay Service (LRS) is a tool that enables custom migrations of databases from on-premises SQL Servers to SQL Managed Instance in the cloud. It uses log shipping technology and is useful in cases where more control is needed, when there's little tolerance for downtime, or when Azure Data Migration Service can't be used. LRS can be used directly with PowerShell, CLI cmdlets, or API, to manually build and orchestrate database migrations to SQL Managed Instance. Some of the reasons to consider using LRS include: More control over the database migration project Little tolerance for downtime on migration cutover Inability to install DMS executable in the environment Lack of file access to database backups Inability to open networking ports from the environment to Azure

Answer 46

Mode Description Recommended for Backup Chain Availability 1. Autocomplete Automatically finishes the migration when the last backup file is restored Passive workloads Entire backup chain must be available in advance 2. Continuous Continuously scans for new backup files and restores them, allowing for data catch-up Active workloads Backup chain can be added during migration Regardless of the mode, plan to complete the migration within 30 days, as the LRS job will be automatically canceled after this time.

Answer 47

A. server audit specification -useful when there is a pool of PII databases B. SQL Server Audit -Correct C. database audit specification -Correct D. a server principal -There is a Service Principal not a Server Principal

Answer 48

D. Register VM1 to the Microsoft Virtual Machine Resource provider

Answer 49

The Microsoft Virtual Machine Resource Provider is part of Azure's infrastructure that enables you to manage virtual machines and related resources. It operates within the Azure Resource Manager framework, which is responsible for deploying, managing, and organizing resources in Azure. Key Functions: - Namespace: The resource provider for virtual machines is identified as Microsoft.Compute. It includes operations for creating, updating, and deleting virtual machines. - Resource Types: It supports various resource types, such as virtual machines, virtual machine scale sets, and images. - Integration: It works seamlessly with other Azure services, allowing you to configure networking, storage, and security settings for your virtual machines. This provider is essential for deploying and managing virtual machines in Azure, whether you're setting up a single VM or scaling out with a cluster. If you'd like to explore more, you can find detailed information here.

Answer 50

B. Set the Alert Logic Threshold to Dynamic D. Set the threshold sensitivity to low

Answer 51

A. DBCC SHRINKDATABASE -Correct need to shrink all the db data and log files B. sp_clean_db_free_space -Wrong C. sp_clean_db_file_free_space -Wrong D. DBCC SHRINKFILE -Wrong, need to shrink more than one db data or log file

Answer 52

D. Run dynamic management view based queries. -Can produce information concerning TempDB contention.

Answer 53

B. Decrease by half the value of Data Flush Interval -Since SQL Server 2016 Query Store default is ON. Allowing the memory alotted for Query Store to fill. The Data Flush will reduce the amout of data Query Store can hold in memory.

Answer 54

D. 64 -For low latency TempDb should be confingured for at least one file per CPU, increasing by multiples of 4. Since we will be running 16vCPUs the next higher availble number of files, with a miltiple of 4, is 64.

Answer 55

D. Create an Alert Rule that uses an Activity Log signal type. -The Activity Log will record increases in database size and pricing Tier

Answer 56

C. SQL Databases

Answer 57

C. 8 -The tumbrule for TempDB Data Files is 8 files for 1-8 CPUs. Since the machaine is scaled up to 8vCPUs there is no need to add more than 8 TempDB files.