1) You need to recommend a high-availability and disaster recovery (HA/DR) strategy for a SQL Server on Azure Virtual Machines instance. The solution must meet the following requirements:
- All databases, including system databases, must be protected.
- SQL Server Agent jobs must be protected.
- Downtime must be minimized.
- The failover process must be done automatically.
What should you include in the recommendation?
a. an Always On availability group
b. an Always On failover cluster instance
c. log shipping
d. transactional replication
a. differential backup frequency
b. long-term retention
c. point-in-time-restore
A long-term retention policy allows you to specify how many weeks, months, and years a backup should be kept. The maximum time is 10 years.
A point-in-time-restore policy only allow you to keep backups for 35 days, and a differential backup frequency policy specifies how often you want differential backup to be taken.
Back up and restore databases - Training | Microsoft Learn
Azure SQL Database: Manage long-term backup retention - Azure SQL Database | Microsoft Learn
a. active geo-replication
b. Always On availability groups
c. auto-failover groups
d. long-term backup retention
Active geo-replication creates a secondary database replica in another region that is asynchronously kept up-to-date, and the auto-failover groups feature allows you to manage the replication and failover of some or all databases on a logical server to another region.
The Always On availability groups feature is a high-availability and disaster recovery (HA/DR) solution, but it is not used for Azure SQL Database. Long-term backup retention stores database backups in Azure Blob storage.
Describe active geo-replication for Azure SQL Database - Training | Microsoft Learn
a. active geo-replication
b. Always On availability groups
c. auto-failover groups
d. long-term backup retention
The auto-failover groups feature allows you to manage the replication and failover of some or all databases on a logical server to another region, which allows you to configure high availability in the event of a regional outage.
Active geo-replication creates a secondary database replica in another region, but it is asynchronously kept up-to-date for Azure SQL Database. The Always On availability groups feature is a high-availability and disaster recovery (HA/DR) solution, but it is not for Azure SQL Database. Long-term backup retention stores database backups in Azure Blob storage.
a. SQL Database Advisor
b. Query Performance Insight
c. Azure SQL Insights
Azure SQL Insights is a comprehensive solution for monitoring any product in the Azure SQL family.
Query Performance Insight provides intelligent query analysis for single and pooled databases but cannot provide information about a distributed availability group.
SQL Database Advisor provides performance recommendations but cannot provide information about a distributed availability group.
a. Intelligent Insights
b. Query Performance Insight
c. SQL Database Advisors
d. SQL Insights
SQL Insights is a comprehensive solution for monitoring any product in the Azure SQL family. As such, it allows you to monitor the health of AG1 and diagnose any performance issues.
Query Performance Insight provides intelligent query analysis for single and pooled databases, SQL Database Advisor provides performance recommendations, and Intelligent Insights uses built-in intelligence to continuously monitor database usage through artificial intelligence (AI) and detect disruptive events that cause poor performance.
7) You have an Azure subscription.
You plan to deploy 100 Azure SQL Databases.
You need to automate the deployment. The solution must be parameterizable, repeatable, and be able to be versioned in source control.
Which three components can you use? Each correct answer presents a complete solution.
- Azure Cloud Shell
- Azure Policy
- Azure PowerShell scripts
- Azure Resource Manager (ARM) templates
- Bicep templates
ARM templates, Bicep templates, and PowerShell scripts can all be parameterized and versioned.
Cloud Shell is an interactive, authenticated, browser-accessible shell, and Azure Policy helps to enforce organizational standards and to assess compliance at-scale.
Automate deployment by using Azure Resource Manager templates and Bicep - Training | Microsoft Learn
- Create an Azure SQL Database.
- Create an Azure SQL elastic pool server.
- Create an Elastic Job agent.
- Create the Elastic Job targets.
You need to create an Azure SQL Database to keep the elastic job configuration.
The Elastic Job agent can be configured once the database and the elastic job configuration is prepared.
You cannot create Elastic Job targets until you have an agent.
You do not need to create an elastic pool server for this task.
Job automation overview with Elastic Jobs - Azure SQL Database | Microsoft Learn
9) You have an on-premises Microsoft SQL Server 2016 instance that uses FileTable.
You need to migrate the instance to Azure. The solution must support the existing application code.
What should you use?
a. Azure SQL Database HyperScale
b. Azure SQL Database serverless
c. Azure SQL Managed Instance
d. SQL Server on Azure Virtual Machines
SQL Server on Azure Virtual Machines supports FileTable. It can be used to migrate the instance to Azure and support the existing application code.
Azure SQL Database serverless and Azure SQL Database Hyperscale are tiers of Azure SQL Database that do not support FileTable. Azure SQL Managed Instance does not support FileTable.
Explore Azure SQL Database Managed Instance - Training | Microsoft Learn
Compare the database engine features of SQL Database and SQL Managed Instance - Azure SQL Database & SQL Managed Instance | Microsoft Learn10) You have a fact table that contains 500 million rows. The table has a clustered index. Most queries select data from the current week and rarely select data that is more than a month old.
a. an in-memory table
b. page compression
c. row compression
d. table partitioning
The data of partitioned tables and indexes are divided into units that might be spread across more than one filegroup in a database or stored in a single filegroup. The partition that contains the most current data does not block any queries that are related to old data, so this will benefit the performance.
Row and page compression changes the internal table storage, but it does not help solve the performance issues. The in-memory table is best for transactional workloads, and the fact table is the analytical workload.
Deploy IaaS solutions with Azure SQL - Training | Microsoft Learn
11) You have several databases in an Azure SQL Managed Instance that uses the General Purpose vCore service tier.
a. Add more files to the database.
b. Add more virtual cores to the instance.
c. Increase the file size.
d. Change the service tier.
You should increase the file size to improve performance. If you have a file that is up to 128 GB, then you can have up to 500 IOPS. If you change the file size to 1 TB, then you can have up to 5,000 IOPS.
You should not add more vCores to the instance in the General Purpose tier, as this will not change the available IOPS. Adding more files to the database also does not help, as the number of available IOPS is calculated per file size, not by the number of files. Changing the service tier will change the type of CPU, the maximum file size, and the amount of memory reserved per CPU.
12) You are planning a migration from on-premises Microsoft SQL Server to Azure using Azure Database Migration Service.
You need to keep databases online during the migration.
Which two services can you use? Each correct answer presents a complete solution.
- Azure Cosmos DB
- Azure SQL Database
- Azure SQL Managed Instance
- SQL Server on Azure Virtual Machines
SQL Server on Azure Virtual Machines and Azure SQL Managed Instance both support online migration.
Azure SQL Database does not support online migration and Azure CosmosDB is not a relational database.
13) You have an on-premises Microsoft SQL Server 2019 instance that hosts 10 databases.
You plan to migrate the databases to Azure SQL Database.
You need to validate the compatibility of the databases with Azure SQL Database.
Which two tools can you use? Each correct answer presents a complete solution.
- App and Data Modernization Readiness Tool
- Data Migration Assistant (DMA)
- Microsoft Assessment and Planning (MAP) Toolkit for SQL Server
- the Azure Migrate Discovery and assessment tool
- the SQL Server Management Studio (SSMS) Copy Database Wizard
a. action
b. package
c. predicate
d. target
SQL Server Extended Events Packages - SQL Server | Microsoft Learn
Describe performance monitoring - Training | Microsoft Learn
a. Query the sys.dm_exec_requests dynamic management view.
b. Use Auditing for Azure SQL Database to investigate.
c. Use Query Store to investigate the metrics.
d. Use SQL Server Profiler to gather a trace.
Using Query Store will enable a historical investigation, which will allow you to look at the metrics to identify the cause of the poor performance.
sys.dm_exec_requests returns information about currently running requests, but it does not allow you to look into past performance issues. Auditing for Azure SQL Database tracks database events. A SQL Server Profile trace will gather information about current activity, but it does not allow you to do a historical investigation of the issue.
Monitor performance by using the Query Store - SQL Server | Microsoft Learn
Describe performance monitoring - Training | Microsoft Learn
- an Azure SQL Database ledger
- Extended Events
- Microsoft Defender for SQL
- Query Store
- SQL Insights
a. Configure automatic tuning.
b. Configure Extended Events.
c. Create a monitoring profile.
d. Enable Azure SQL Auditing.
Creating a monitoring profile is the first requirement to configuring SQL Insights.
Automatic tuning uses AI to provide peak performance. Azure SQL Auditing tracks database events and records them. Extended Events is a lightweight performance monitoring system.
- sys.dm_exec_query_stats
- sys.dm_exec_requests
- sys.dm_exec_sessions_wait_stats
- sys.dm_os_waiting_tasks
a. sys.dm_exec_query_stats
b. sys.dm_exec_requests
c. sys.dm_exec_session_wait_stats
d. sys.dm_exec_sessions
a. REPAIR_ALLOW_DATA_LOSS
b. REPAIR_REBUILD
c. REPAIR_FAST
Adding the REPAIR_REBUILD option to the DBCC CHECKDB command performs repairs that have no possibility of data loss. This option may include quick repairs, such as repairing missing rows in nonclustered indexes, and more time-consuming repairs, such as rebuilding an index.
The REPAIR_FAST option maintains syntax for backward compatibility only. No repair actions are performed. The REPAIR_ALLOW_DATA_LOSS option can introduce some data loss in the database.
DBCC CHECKDB (Transact-SQL) - SQL Server | Microsoft Learn
Configure databases for optimal performance - Training | Microsoft Learn
a. In db1, run the CREATE USER statement with the FROM LOGIN option.
b. In db1, run the CREATE USER statement with the FROM EXTERNAL PROVIDER option.
c. In the master database, run the CREATE LOGIN statement with the FROM EXTERNAL PROVIDER option.
d. In the master database, run the CREATE LOGIN statement with the WITH PASSWORD option.
a. db_ddladmin
b. dbmanager
c. db_owner
d. loginmanager
a. Add a WITH RECOMPILE clause to the stored procedure.
b. Add the SELECT permission to the Sales table for the users.
c. Add an EXECUTE AS [owner] clause to the stored procedure.
You should add the SELECT permission to the Sales table because executing dynamic SQL code requires that the caller has all the necessary permissions to the objects that are called in the code.
Using the EXECUTE AS [owner] clause or the WITH RECOMPILE clause does not ensure that the caller has all the necessary permissions to the objects that are called in the code.
EXECUTE AS (Transact-SQL) - SQL Server | Microsoft Learn
Configure database authentication and authorization - Training | Microsoft Learn
a. Create a certificate in the db1 database.
b. Create a certificate in the master database.
c. Create a symmetric key encrypted by a certificate.
d. Create an asymmetric key encrypted by a certificate.
The certificate created in db1 will be used to encrypt a symmetric key, which, in turn, will be used to protect data in the CreditCard column in the Sales table. The column containing encrypted data must be varbinary column.
A symmetric key, not an asymmetric key, is required for this operation. Creating the certificate in the master database would not work, as the certificate that is needed for encryption must be created in the user database.
- Apply a sensitivity classification to the column.
- Configure a Log Analytics audit log destination.
- Configure a Storage audit log destination.
- Enable Always Encrypted for the column.
- Enable Azure SQL Auditing.
- Enable Transparent Data Encryption (TDE).
a. total size of all the databases
b. geo-replication support
c. number of concurrently peaking databases * peak CPU utilization per database
d. loginmaximum number of concurrent sessions for all the databases
e. total number of databases * average CPU utilization per database
Step 1: Attach the SSISDB database
Step 2: Turn on the TRUSTWORTHY property and the CLR property
If you are restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr)
Step 3: Open the master key for the SSISDB database
Restore the master key by this method if you have the original password that was used to create SSISDB. open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'
Alter Master Key Add encryption by Service Master Key
Step 4: Encrypt a copy of the master key by using the service master key
Reference:
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog
28) You have an Azure SQL database that contains a table named factSales. FactSales contains the columns shown in the following table.
FactSales has 6 billion rows and is loaded nightly by using a batch process. You must provide the greatest reduction in space for the database and maximize performance.
Which type of compression provides the greatest space reduction for the database?
a. page compression
b. row compression
c. columnstore compression
d. columnstore archival compression
29) You have a Microsoft SQL Server 2019 database named DB1 that uses the following database-level and instance-level features.
✑ Clustered columnstore indexes
✑ Automatic tuning
✑ Change tracking
✑ PolyBase
You plan to migrate DB1 to an Azure SQL database.
What feature should be removed or replaced before DB1 can be migrated?
a. Clustered columnstore indexes
b. PolyBase
c. Change tracking
d. Automatic tuning
30) You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The instance contains a 4-TB database named DB1.
You plan to migrate DB1 to an Azure SQL Database managed instance.
What should you use to minimize downtime and data loss during the migration?
a. distributed availability groups
b. database mirroring
c. Always On Availability Group
d. Azure Database Migration Service
Azure Database Migration Service can do online migrations with minimal downtime.
Reference:
https://docs.microsoft.com/en-us/azure/dms/dms-overview
31) You have an on-premises Microsoft SQL Server 2016 server named Server1 that contains a database named DB1.
You need to perform an online migration of DB1 to an Azure SQL Database managed instance by using Azure Database Migration Service.
How should you configure the backup of DB1? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Box 1: Full and log backups only
Make sure to take every backup on a separate backup media (backup files). Azure Database Migration Service doesn't support backups that are appended to a single backup file. Take full backup and log backups to separate backup files.
Box 2: WITH CHECKSUM -
Azure Database Migration Service uses the backup and restore method to migrate your on-premises databases to SQL Managed Instance. Azure Database
Migration Service only supports backups created using checksum.
Incorrect Answers:
NOINIT Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.
UNLOAD -
Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is the default when a session begins.
Reference:
https://docs.microsoft.com/en-us/azure/dms/known-issues-azure-sql-db-managed-instance-online
32) What type of storage offers the lowest latency in Azure?
a. Ultra SSD
b. Premium SSD
c. Standard SSD
33) To reduce the cost of an Azure Virtual Machine running SQL Server, you intend to run full time for three years, which option should you choose?
a. Availability set
b. Azure Reserved VM Instances
c. Pay as You Go Licensing
34) Which option should you choose to spread workloads across data centers in a region?
a. Availability sets
b. Availability zones
c. Availability units
35) Which service performs block level replication of your Azure virtual machine?
a. Azure Site Recovery
b. Azure Backup for SQL Server
c. Availability Groups
36) What is the purpose of entering credentials in the self-hosted integration runtime?
a. To connect to the source and migrate data to the target
b. To delete the source data
c. To create a backup of the source data
37) What is the role of self-hosted integration runtime in data migration?
a. To connect to the source and target and facilitate data migration
b. To store the migrated data
c. To analyze the migrated data
38) What is the significance of selecting tables during data migration?
a. It helps in migrating only the required data from the source to the target
b. It helps in creating a backup of the source data
c. It helps in analyzing the migrated data
39) Can transactional replication be configured from the Azure portal?
a. No, transactional replication can't be configured from the Azure portal.
b. No, transactional replication can only be configured through SQL Server Management Studio.
c. Yes, transactional replication can be configured from the Azure portal.
40) You plan to assess a group of on-premises Linux web servers in preparation for migration to Azure. Which tool should you use?
a. Data Migration Assistant
b. Azure Migrate
c. Azure Site Recovery
Azure Migrate lets you discover and assess your on-premises resources and provides an assessment report.
41) Which set of attributes describes agentless migration?
a. Systems continue to run during migration, install lightweight agents on each machine being replicated, and provide dependency information between on-premises VMs.
b. Systems must be offline during migration, costs less than agent-based migration, and avoids overhead of keeping agents updated.
c. Systems continue to run during migration, providing dependency information between on-premises VMs, but increase overhead of keeping agents updated.
42) Which RBAC role is required to configure and register the Azure Migrate appliance?
a. Virtual Machine Contributor
b. Remote Management Users
c. Owner or Contributor
Owner or Contributor role is required to configure and register the Azure Migrate appliance.Virtual Machine Contributor role allows a user to create and manage virtual machines.
43) Where does the appliance store Microsoft Entra app information and appliance configuration settings?
a. Azure Storage account
b. Azure Key Vault
c. The appliance setup file
The appliance stores Microsoft Entra app information and appliance configuration settings in an Azure Key Vault.
44) what does the Hyper-V host need to send replication data to Azure Migrate?
a. Access to the URL login.microsoftonline.com
b. Outbound connections enabled on HTTPS port 443
c. Inbound connections enabled on TCP port 902
The Hyper-V host needs to have outbound connections enabled on HTTPS port 443 so that it can send VM replication data to Azure Migrate. Each VM will also need outbound connections on port 443.
45) what do you need to do to ensure your on-premises VMs can be replicated to Azure?
a. Enable Remote Desktop (RDP) and Secure Shell (SSH), disable BitLocker
b. Enable BitLocker, Ensure Windows Firewall can block RDP
c. Disable BitLocker, Enable RDP connections only
You need to be able to connect the Azure VMs after migration, both Linux and Windows machines. BitLocker isn't supported for Azure VMs and must be disabled before replication.
46) What is the order of steps to deploy the Azure Migrate appliance?
a. Download the VHD, register the appliance, configure appliance prerequisites, and then create the appliance VM.
b. Download the VHD, configure appliance prerequisites, create the appliance VM, and then register the appliance.
c. Download the VHD, create the appliance VM, configure appliance prerequisites, and then register the appliance.
47) When importing the Hyper-V VHD file, what do you need to do to ensure the Azure Migrate appliance can communicate with the Hyper-V host?
a. Specify a virtual network switch for the appliance VM to use.
b. Ensure URL and port requirements for the Hyper-V host are in place.
c. Configure the appliance computer time to be synchronized with the internet time server.
48) what components does the Hyper-V host require to replicate your VMs to Azure?
a. Mobility service, Azure Site Recovery provider, Registration key file
b. Azure Site Recovery provider, Azure Site Recovery agent, Registration key file
c. Registration appliance, Registration key file, Azure Site Recovery agent
49) During replication, where does the replication provider upload the replicated data from the on-premises Hyper-V VMs?
a. Azure Key Vault
b. Hyper-V replica disks
c. Azure storage account
No comments:
Post a Comment