You have an Azure subscription that contains an Azure SQL database named db1.You need to implement SQL insights for db1.Which two resources should you create first? Each correct answer presents part of the solution.Note: Each correct selection is worth one point.
Answer(s): A,E
You have an Azure subscription that contains the resources shown in the following table.App1 experiences transient connection errors and timeouts when it attempts to access db1 after extended periods of inactivity.You need to modify db1 to resolve the issues experienced by App1 as soon as possible, without considering immediate costs.What should you do?
Answer(s): D
Applications that connect to a cloud service such as Azure SQL Database should expect periodic reconfiguration events and implement retry logic to handle these errors instead of surfacing application errors to users.Auto-pause delayIn the Azure Serverless architecture, the database automatically pauses if it is inactive during the specified duration. By default, it is enabled and set to 1 hour (3600 seconds).Note: Serverless compute tierThe serverless compute tier for single databases in Azure SQL Database is parameterized by a compute autoscaling range and an auto-pause delay. The configuration of these parameters shapes the database performance experience and compute cost.The auto-pause delay is a configurable parameter that defines the period of time the database must be inactive before it is automatically paused. The database is automatically resumed when the next login or other activity occurs. Alternatively, automatic pausing can be disabled.
https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview https://www.sqlshack.com/automatic-pause-and-resume-an-azure-sql-database/
HOTSPOT (Drag and Drop is not supported)You have an Azure SQL database named DB1 that contains a table named Table1.You run a query to load data into Table1.The performance metrics of Table1 during the load operation are shown in the following exhibit.Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.Note: Each correct selection is worth one point.Hot Area:
Answer(s): A
Box 1: perform query tuningImplementation considerationsUse memory-optimized tables for your core transaction tables, that is, the tables with the most performance- critical transactions. Use natively compiled stored procedures to optimize execution of the logic associated with the business transaction. The more of the logic you can push down into stored procedures in the database, the more benefit you see from In-Memory OLTP.To get started in an existing application:1. Use the transaction performance analysis report to identify the objects you want to migrate.2. Use the memory-optimization and native compilation advisors to help with migration.Box 2: an In-Memory OLTP non-durableExample: tempdb object replacementUse non-durable tables and memory-optimized table types to replace your traditional tempdb based structures, such as temporary tables, table variables, and table-valued parameters (TVPs).Memory-optimized table variables and non-durable tables typically reduce CPU and completely remove log IO, when compared with traditional table variables and #temp table.Note: In-memory technologies enable you to improve performance of your application, and potentially reduce cost of your database.In-Memory OLTPIn-Memory OLTP technology provides extremely fast data access operations by keeping all data in memory. It also uses specialized indexes, native compilation of queries, and latch-free data-access to improve performance of the OLTP workload. There are two ways to organize your In-Memory OLTP data:* Memory-optimized rowstore format where every row is a separate memory object. This is a classic In- Memory OLTP format optimized for high-performance OLTP workloads. There are two types of memory- optimized tables that can be used in the memory-optimized rowstore format:Durable tables (SCHEMA_AND_DATA) where the rows placed in memory are preserved after server restart. This type of tables behaves like a traditional rowstore table with the additional benefits of in-memory optimizations.Non-durable tables (SCHEMA_ONLY) where the rows are not-preserved after restart. This type of table is designed for temporary data (for example, replacement of temp tables), or tables where you need to quickly load data before you move it to some persisted table (so called staging tables).* Memory-optimized columnstore format where data is organized in a columnar format
https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/overview-and-usage- scenarios https://learn.microsoft.com/en-us/azure/azure-sql/in-memory-oltp-overview
DRAG DROP (Drag and Drop is not supported)You have a database named db1.The log for db1 contains the following entry.You need to ensure that db1 can process transactions.Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.Select and Place:
Step 1: Remove db1 from the availability group.Step 2: Shrink the transaction log file.You can use SQL Server Management Studio or Transact-SQL to shrink a data or log file.Example:SQLUSE UserDB;GODBCC SHRINKFILE (DataFile1, 7);GOIncorrect:* Back up the transaction log file.Backup the Log file to free up space within the file (logically/virtually).Step 3: Add db1 back to the availability group.Note:SymptomsConsider the following scenario:You have Microsoft SQL Server 2012 or a later version installed on a server. The instance of SQL Server is a primary replica in Always On Availability Groups environment. The autogrow option for transaction log files is set in SQL Server. In this scenario, the transaction log may become large and run out of disk space or exceed the MaxSize option set for the transaction log at the primary replica and you receive an error message that resembles the following:Error: 9002, Severity: 17, State: 9. The transaction log for database '%.*ls' is full due to 'AVAILABILITY_REPLICA'CauseThis occurs when the logged changes at primary replica are not yet hardened on the secondary replica.WorkaroundAfter you identify the secondary database that makes this occur, try one or more of the following methods to work around this issue temporarily:* Take the database out of the availability group for the offending secondary.NoteThis method will result in the loss of the High Availability/Disaster Recovery scenario for the secondary. You may have to set up the Availability Group again in the future.* If the redo thread is frequently blocked, disable the Readable Secondary feature by changing the ALLOW_CONNECTIONS parameter of the SECONDARY_ROLE for the replica to NO.NoteThis will prevent users from reading the data in the secondary replica which is the root cause of the blocking. Once the redo queue has dropped to an acceptable size, consider enabling the feature again.* Enable the autogrow setting if it is disabled and there is available disk space.* Increase the MaxSize value for the transaction log file if it has been reached and there is available disk space.* Add an additional transaction log file if the current one has reached the system maximum of 2 TB or if additional space is available on another available volume.
https://learn.microsoft.com/en-us/troubleshoot/sql/availability-groups/error-9002-transaction-log- large https://thecloudtechnologist.com/2015/03/18/shrinking-sql-log-files-in-an-availability-group-cluster-or-database- mirror/
You have the following resources:15 SQL Server on Azure Virtual Machines instances20 Azure SQL databasesYou need to recommend a solution to centrally monitor the resources for security vulnerabilities.What should you include in the recommendation?
Answer(s): B
Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:
Our website is free, but we have to fight against AI bots and content theft. We're sorry for the inconvenience caused by these security measures. You can access the rest of the DP-300 content, but please register or login to continue.