Azure 5
C
A Microsoft SQL Server instance has a database named Employees. The database contains information about employees including their salary. You must create a custom system message as an alert when an error is raised. You need to create the custom message in the sys.messages table. Which argument in sp_addmessage should you configure? A. @with_log B. @lang C. @severity D. @replace
Increase IOPS - add new drive Increase throughput - optimization Increase only storage size - extend drive
A company has Microsoft Azure virtual machines (VMs) that host SQL Server 2017 instances. The VM require additional storage. You need to recommend storage options for several scenarios that you are considering
Explanation/Reference: Explanation: Model: Change from full to simple Recovery Model Newly created user databases use the same recovery model as the model database. The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database. Model: Set compatibility level to 120 For all installations of SQL Server, the default compatibility level is set to the version of the Database Engine. Databases are set to this level unless the model database has a lower compatibility level.
A company has an on-premises Microsoft SQL Server 2016 environment. All futures databases must meet the following requirements: The recovery model must be set to simple. The compatibility level must be set to SQL server 2014 (120). Your need to configure the SQL server 2016 environment. In the table below, identify the database you must modify for each requirement
D
A company has an on-premises Microsoft SQL Server 2017 infrastructure. The storage area network (SAN) that supports the SQL infrastructure has reached maximum capacity. You need to recommend a solution to reduce on-premises storage use without changing the application. What should you do? A. Configure an Express Route connection to Microsoft Azure. B. Configure a Microsoft Azure Key Vault. C. Configure geo-replication on the SAN. D. Configure SQL Server Stretch Database in Microsoft Azure
C
A company hosts a default installation of Microsoft SQL Server 2017 on a Linux-based server. You observe that the performance of the server has degraded. Process for allocating tables show high latency. You need to recommend a solution to improve performance when allocating tables. What should you recommend? A. Configure additional tempdb data files B. Reinstall SQL Server on Linux C. Set a memory limit for the SQL server D. Increase the Virtual Address Space limit
C
A company plans to use a Microsoft Azure SQL Database instance to manage sensitive data. The solution must allow data to be decrypted while in use but encrypted while at rest. You need to ensure that the columns are protected. What should you use? A. static data masking B. persistent data masking C. Transparent Data Encryption D. Recovery Services vault
AB
A. Address B. Gender C. LastName D. Tax Id
B
QUESTION 20 You plan to deploy an AlwaysOn failover cluster in Microsoft Azure. The cluster has a Service Level Agreement (SLA) that requires an uptime of at least 99.95 percent. You need to ensure that the cluster meets the SLA. Which cmdlet should you run before you deploy the virtual machine? A. New-AzureRmAvailabilitySet B. New-AzureRmLoadBalancer C. New-AzureRmSqlDatabaseSecondary https://www.gratisexam.com/ D. New-AzureRmSqlElasticPool E. New-AzureRmVM F. New-AzureRmSqlServer G. New-AzureRmSqlDatabaseCopy H. New-AzureRmSqlServerCommunicationLink
B. Sys_sp_rda_test_connection
QUESTION 21 You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure. A network administrator upgrades the hardware firewalls on the network. You need to verify whether data migration still runs successfully. Which stored procedure should you run? A. Sys_sp_testlinkedserver B. Sys_sp_rda_test_connection C. Sys_sp_rda_reauthorized_db D. Sp_set_firewall_rule
E
QUESTION 28 You plan to migrate on-premises Microsoft SQL Server databases to SQL Server on a Microsoft Azure virtual machine. A full back up of the databases is 3 TB. The corporate network has a 1.5 MB/sec Internet connection. You need to move the backups to Azure to ensure that you can restore the databases to the Azure virtual machine as quickly as possible. What should you do? A. Use SQL Server Backup to URL. B. Run azcopy.exe and specify the /SetContentType parameter. C. Create an availability group that is configured for automatic seeding. D. Run robocopy.exe and specify the /mir parameter. E. Use the Azure Import/Export service.
C
QUESTION 30 You have a single-server on-premises Microsoft SQL Server deployment. You must migrate the environment to an Azure virtual machine (VM). You must minimize costs while maintaining the same level of performance as the on-premises SQL environment. You need to evaluate the number and types of read/write operations for the existing deployment. Which tool should you use? A. SQL Disk Usage Standard Report B. Database Engine Tuning Advisor C. SQLIOSim D. SQL Profiler trace using TSQL template
B
QUESTION 31 A company plans to run Microsoft SQL Server on Linux-based servers. You need to ensure that performance tuning settings are automatically configured during installation. Which Linux platform should you recommend? A. Kali Linux B. RHEL C. SUSE D. Ubuntu Server
A
QUESTION 34 You administer a Microsoft SQL Server 2012 server. One of the databases on the server supports a highly active OLTP application. Users report abnormally long wait times when they submit data into the application. You need to identify which queries are taking longer than 1 second to run over an extended period of time. What should you do? A. Use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000. B. Use sp_configure to set a value for blocked process threshold. Create an extended event session. C. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step. D. Run the sp_who command from a query window. E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log
B
QUESTION 35 You administer a Microsoft SQL Server database. You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do? A. Execute sp_configure 'max log size', 2G. B. Use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter. C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log. D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.
D. Move the tempdb files to a local disk
QUESTION 39 You administer a Microsoft SQL Server 2012 instance that has multiple databases. You have a two-node SQL Server failover cluster. The cluster uses a storage area network (SAN). You discover I/O issues. The SAN is at capacity and additional disks cannot be added. You need to reduce the I/O workload on the SAN at a minimal cost. What should you do? A. Move user databases to a local disk. B. Expand the tempdb data and log files C. Modify application code to use table variables D. Move the tempdb files to a local disk
BD
QUESTION 64 You have a database named DB1 that is 3 TB. DB1 contains a fact table that is 1.2 TB. You load 200 GB of new data to the fact table from a line-of-business application. Users of DB1 notice that reports render more slowly after you loaded the data. What are two possible causes of the performance issue? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point. A. transaction log growth B. out-of-date statistics C. page corruption D. index fragmentation
C
You administer a Microsoft SQL Server 2012 database instance. You plan to migrate the database to Windows Azure SQL Database. You verify that all objects contained in the database are compatible with Windows Azure SQL Database. You need to ensure that database users and required server logins are migrated to Windows Azure SQL Database. What should you do? A. Use the copy database wizard B. Use the Database Transfer wizard C. Use SQL Server Management Studio to deploy the database to Windows Azure SQL Database D. Backup the database from the local server and restore it to Windows Azure SQL Database
C
You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server. What should you create? A. An Alert B. A Resource Pool C. An Extended Event session D. A Server Audit Specification E. A SQL Profiler Trace F. A Database Audit Specification G. A Policy H. A Data Collector Set
A
You administer a Microsoft SQL Server 2012 database that contains a table named AccountTransaction. You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index. You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process. Which Transact-SQL batch should you use? A. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REORGANIZE B. ALTER INDEX ALL ON AccountTransaction REBUILD C. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REBUILD D. CREATE INDEX IDXAccountTransactionAccountCode ON AccountTransaction.AccountCode WITH DROP EXISTING
B
You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use? A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE C. ALTER INDEX ALL ON OrderDetail REBUILD D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
BCDE
You administer a Microsoft SQL Server 2012 instance. The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. You plan to define a backup strategy for the database. You need to ensure that the following requirements are met: No more than 5 minutes worth of transactions are lost. Data can be recovered by using the minimum amount of administrative effort. What should you do? Choose all that apply. A. Configure the database to use the SIMPLE recovery model. B. Create a DIFFERENTIAL database backup every 4 hours. C. Create a LOG backup every 5 minutes. D. Configure the database to use the FULL recovery model. E. Create a FULL database backup every 24 hours. F. Create a DIFFERENTIAL database backup every 24 hours.
D
You administer all the deployments of Microsoft SQL Server 2012 in your company. You have two servers in the same data center that hosts your production database. You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs. You also need to maintain transactional consistency of the data across both servers. You need to achieve these goals without manual intervention. Which configuration should you use? A. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance B. Two servers configured in the same data centerA primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby C. SQL Server that includes an application database configured to perform transactional replication D. Two servers configured on the same subnetSQL Server Availability Group configured in Synchronous-Commit Availability Mode E. Two servers configured in different data centersSQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary F. SQL Server that includes an application database configured to perform snapshot replication G. Two servers configured in the same data centerSQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary H. Two servers configured in different data centersSQL Server Availability Group configured in Asynchronous-Commit Availability Mode
D
You are the administrator for a SQL Server 2016 instance that stores the data for an online transaction processing sales system. The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups. These backups are stored on a backup server in the company's data center. Every week, the company places the full backup on a tape and sends it to a third-party backup storage system. The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders. You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations. What should you do? More than one answer choice may achieve the goal. Select the BEST answer. A. Set up SQL Server Always On with a SQL Azure database as a replica. B. Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine. C. Put the differential backup on tape and send it to the third-party backup storage system. D. Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location.
D
You are the database administrator in your company. You plan to create 10 identical environments that use SQL Server 2016 as a database engine. Each environment has the following custom requirements: Three user databases must be preinstalled. The tempdb database must contain eight data files that are 1024 MB each. Trace flag 2371 must be turned at the instance level. The solution must meet the following requirements: The instance must be preconfigured. No other database features are required in the future. The solution must use the minimum administrative effort. You need to prepare the environments. What should you do? A. Provision 10 Azure virtual machines that each contain SQL Server 2016, installed by using the default settings. B. Create an installation configuration file and perform unattended installations of SQL Server 2016. C. Create a virtual machine template by using a prepared instance of SQL Server 2016. D. Create a virtual machine template by using a complete instance of SQL Server 2016.
A
You are tuning the performance of a virtual machine that hosts a Microsoft SQL Server instance. The virtual machine originally had four CPU cores and now has 32 CPU cores. The SQL Server instance uses the default settings and has an OLTP database named db1. The largest table in db1 is a key value store table named table1. Several reports use the PIVOT statement and access more than 100 million rows in table1. You discover that when the reports run, there are PAGELATCH_IO waits on PFS pages 2:1:1, 2:2:1, 2:3:1, and 2:4:1 within the tempdb database. You need to prevent the PAGELATCH_IO waits from occurring. Solution: You add more files to db1. Does this solution meet the goal? A. Yes B. No
b
You are tuning the performance of a virtual machine that hosts a Microsoft SQL Server instance. The virtual machine originally had four CPU cores and now has 32 CPU cores. The SQL Server instance uses the default settings and has an OLTP database named db1. The largest table in db1 is a key value store table named table1. Several reports use the PIVOT statement and access more than 100 million rows in table1. You discover that when the reports run, there are PAGELATCH_IO waits on PFS pages 2:1:1, 2:2:1, 2:3:1, and 2:4:1 within the tempdb database. You need to prevent the PAGELATCH_IO waits from occurring. Solution: You add more tempdb databases. Does this solution meet the goal? A. Yes B. No
B
You are tuning the performance of a virtual machine that hosts a Microsoft SQL Server instance. The virtual machine originally had four CPU cores and now has 32 CPU cores. The SQL Server instance uses the default settings and has an OLTP database named db1. The largest table in db1 is a key value store table named table1. Several reports use the PIVOT statement and access more than 100 million rows in table1. You discover that when the reports run, there are PAGELATCH_IO waits on PFS pages 2:1:1, 2:2:1, 2:3:1, and 2:4:1 within the tempdb database. You need to prevent the PAGELATCH_IO waits from occurring. Solution: You rewrite the queries to use aggregates instead of PIVOT statements. Does this solution meet the goal? A. Yes B. No
B
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location. A hardware failure occurs and so a new server must be installed and configured. After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database. You need to be able to restore the database. Which Transact-SQL statement should you use before attempting the restore? A. ALTER DATABASE Master SET ENCRYPTION OFF; B. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key', DECRYPTION BY PASSWORD = 'MyPassword1!'); C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'; USE Orders; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate; D. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer';
B
You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica. You have multiple queries that read data and produce reports from the database. You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. What should you do? A. Set the Availability Mode property of HA/Server02 to Asynchronous commit. B. Set the Readable Secondary property of HA/Server02 to Read-intent only. C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections. D. Set the Availability Mode property of HA/Server01 to Asynchronous commit.
A
You deploy a Microsoft SQL Server instance to support a new application. The application uses the tempdb database to maintain session state data. Users report performance issues with the application. You observe performance problems immediately and begin troubleshooting. You observe numerous instances of the page PAGELATCH_UP and PAGELATCH_EX wait events in the logs for the server. The resource description for the wait is 2:1:1. You need to resolve the performance problem. What should you do? A. Increase the number of tempdb log files. B. Use the Read Committed Snapshot Isolation mode. C. Replace temporary tables with table-valued functions. D. Increase the value of the Cost Threshold for Parallelism setting. E. Increase the size of the tempdb.
BCF
You deploy a new Microsoft Azure SQL Database instance to support a variety of mobile applications and public websites. You have the following requirements: You must be able to replicate the database to Azure datacenters in other regions by using geo-replication. Clients must only connect to the Azure SQL Database by using contained database users. You must be able to move the database to other servers in the future. Only applications in the 23.96.52.0-23.96.52.255 IP range are permitted to access the database. The firewall settings for the instance must not provide access to any other Azure services. You need to configure the firewall settings for the environment. Which three actions should you perform? Each correct answer presents part of the solution. A. In the Azure portal, enable Allow access to Azure services for the Azure SQL Database server B. Run the following Transact-SQL statement: EXECUTE sp_set_database_firewall_rule N'Applications', '23.96.52.0', '23.96.52.255' C. Run the following Transact-SQL statement: EXECUTE sp_set_firewall_rule N'Allow Windows Azure', '0.0.0.0', '0.0.0.0' D. Run the following Transact-SQL statement: EXECUTE sp_set_firewall_rule N'Applications', '23.96.52.0', '23.96.52.255' E. Run the following Transact-SQL statement: EXECUTE sp_set_database_firewall_rule N'Allow Windows Azure', '0.0.0.0', '0.0.0.0' F. In the Azure portal, disable Allow access to Azure services for the Azure SQL Database server
A
You deploy a new Microsoft Azure SQL database instance to support a variety of mobile application and public websites. You configure geo-replication with regions in Brazil and Japan. You need to implement real-time encryption of the database and all backups. Solution: You enable Transparent Data Encryption (TDE) on the primary instance. Does the solution meet the goal? A. Yes B. No
B
You deploy a new Microsoft Azure SQL database instance to support a variety of mobile application and public websites. You configure geo-replication with regions in Brazil and Japan. You need to implement real-time encryption of the database and all backups. Solution: You password protect all azure SQL backups and enable azure active directory authentication for all azure SQL server instances. Does the solution meet the goal? A. Yes B. No
B
You deploy a new Microsoft Azure SQL database instance to support a variety of mobile application and public websites. You configure geo-replication with regions in Brazil and Japan. You need to implement real-time encryption of the database and all backups. Solution: you enable Dynamic Data Masking on the primary replica. Does the solution meet the goal? A. Yes B. No
D
You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do? A. Use a FORCESCAN hint in the query. B. Add a clustered index on SalesOrderId in SalesOrderHeader. C. Use a FORCESEEK hint in the query. D. Update statistics on SalesOrderId on both tables
D
You have Microsoft SQL Server on a Microsoft Azure Virtual machine that has a 4-TB database. You plan to configure daily backups for the database. A single full backup will be approximately 1.5 TB of compressed data. You need to ensure that the last backups are retained. Where should you store the daily backups? A. Local storage B. Page blob storage C. Virtual disks D. Block blob storage.
B
You have Microsoft SQL Server on a Microsoft Azure Virtual machine. The virtual machine has a database named DB1. You need to identify the fragmentation percentage for the indexes of DB1. Which dynamic management view should you query? A. sys.dm_db_index_operational_stats B. sys.dm_db_index_physical_stats C. sys.dm_db_xtp_hash_index_stats D. sys.dm_db_index_usage_stats
C
You have Microsoft SQL Server on a Microsoft Azure virtual machine. You create a SQL Server Agent job by using the following statement. You need to send an email message if the job fails. Which stored procedure should you use? A. msdb.dbo.sp_update_alert B. msdb.dbo.sp_add_jobstep C. msdb.dbo.sp_add_notification D. msdb.dbo.sp_help_alert
C
You have Microsoft SQL Server on a Microsoft Azure virtual machine. You have two Windows accounts named ServiceAccount1 and ServiceAccount2. The SQL Server Agent runs as ServiceAccount1. You need to run SQL Server Agent job steps by using ServiceAccount2. Which cmdlet should you run first? A. Set-ADServiceAccount B. Set-SqlCredential C. New-ADServiceAccount D. New-SqlCredential
B
You have Microsoft SQL Server on a Microsoft azure virtual machine that has 12 databases. All database files are in the same Azure Blob storage account. You need to receive an email notification if I/O operations to the database files exceed 800 MB/s for more than five minutes. Solution: You run the Add-AzureRmMetricAlertRule cmdlet and specify the -MetricName 'Disk Write Operations/Sec' parameter. Does this solution meet the goal? A. Yes B. No
B
You have Microsoft SQL Server on a Microsoft azure virtual machine that has 12 databases. All database files are in the same Azure Blob storage account. You need to receive an email notification if I/O operations to the database files exceed 800 MB/s for more than five minutes. Solution: You run the Add-AzureRmMetricAlertRule cmdlet and specify the -MetricName 'Network Out' parameter. Does this solution meet the goal? A. Yes B. No
A
You have Microsoft SQL Server on a Microsoft azure virtual machine that has 12 databases. All database files are in the same Azure Blob storage account. You need to receive an email notification if I/O operations to the database files exceed 800 MB/s for more than five minutes. Solution: You run the Get-Counter cmdlet and specify the -counter '\physicaldisk:disk Transfers/sec' parameter. Does this solution meet the goal? A. Yes B. No
B
You have Microsoft SQL Server on a Microsoft azure virtual machine that has 12 databases. All database files are in the same Azure Blob storage account. You need to receive an email notification if I/O operations to the database files exceed 800 MB/s for more than five minutes. Solution: You run the Get-Counter cmdlet and specify the -counter '\physicaldisk:disk write/sec' parameter. Does this solution meet the goal? A. Yes B. No
B
You have Microsoft SQL server on a Microsoft Azure virtual machine. The virtual machine has 200 GB of data. User report a slow response time when querying the database. You need to identify whether the storage subsystem causes the performance issue. Which performance monitor counter should you view? A. Data sec/Write B. Avg.disk Read Queue Length C. % Disk Read Time D. Disk sec/Read
C. Enable Dynamic Data Masking on the column and use the default function
You have a Microsoft Azure SQL Database instance named Sales. Customer contact data is stored in a table named Customers. The customers table has a field named PhoneNo. This fields stores are code and telephone number in a format that resembles the following: 999-999-9999. Members of the sales team must be able to see all digits in the PhoneNo field. For all users, the area code must be viewable. All other digits must be hidden. You need to configure the Sales database. What should you do? A. Enable Dynamic Data Masking on the column and use the partial function B. Enable always Encrypted on the column using the Randomized encryption type C. Enable Dynamic Data Masking on the column and use the default function D. Enable Transparent Data Encryption (TDE) on the Sales database
G
You have a Microsoft Azure SQL database in the US West region. You need to create a replica in the US East region. Which cmdlet should you run first? A. New-AzureRmAvailabilitySet B. New-AzureRmLoadBalancer C. New-AzureRmSqlDatabaseSecondary D. New-AzureRmSqlElasticPool E. New-AzureRmVM F. New-AzureRmSqlServer G. New-AzureRmSqlDatabaseCopy H. New-AzureRmSqlServerCommunicationLink
\\LON-SVR1\Marketing\Marketingdata.mdf 500 MB Filegrowth=0 L:\Logs\Marketing\Marketingdata.ldf Filegrowth = 10%
You have a Microsoft SQL server that manages all of your databases. You must create a database named MarketingData. The database will initially consume 400 megabytes (MB) of storage and will grow by one percent per year. The database file must be stored on the Marketing share of the Lon-SVR1 server. The file must not automatically increase in size. Log files must be stored on the L drive of the SQL Server machine in a folder named Logs. You need to create the MarketingData database. How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area. NOTE: Each correct selection is worth one point.
A
You have a SQL Server 2016 database named DB1. You plan to import a large number of records from a SQL Azure database to DB1. You need to recommend a solution to minimize the amount of space used in the transaction log during the import operation. What should you include in the recommendation? A. The bulk-logged recovery model B. The full recovery model C. A new partitioned table D. A new log file E. A new file group
B
You have a database named DB1 that contains a table named Table1. Tabe1 has a non-clustered index named index1. You discover that index1 is corrupt. You need to repair index1. Which statement should you execute? A. DBCC CHECKDB ('db1', REPAIR_FAST) B. ALTER INDEX indx1 ON table1 REBUILD WITH (ONLINE-ON) C. ALTER INDEX index1 ON table1 REORGANIZE D. DBCC CHECKDB ('db1', DATA_PURITY)
c
You have a database named DB1 that contains a table named Table1. Table1 has 1 billion rows. You import 10 million rows of data into Table1. After the import, users report that queries take longer than usual to execute. You need to identify whether an out-of-date execution plan is causing the performance issue. Which dynamic management view should you use? A. sys.dm_xtp_transaction_stats B. sys.dm_exec_input_buffer C. sys.dm_db_index_operational_stats D. sys.dm_db_stats_properties
D. Run DBCC CHECKDB ('db1', REPAIR_REBUILD).
You have a database named DB1. You discover that DB1 is corrupt. You run DBCC CHECKDB and receive an error message within a few seconds. No pages are listed in the error message. You need to repair the database corruption as quickly as possible. The solution must minimize data loss. What should you do? A. Run DBCC CHECKDB ('db1', REPAIR_ALLOW_DATA_LOSS). B. Run DBCC CHECKDB ('db1', REPAIR_FAST). C. Delete the transaction logs and restart the Microsoft SQL Server instance. D. Run DBCC CHECKDB ('db1', REPAIR_REBUILD). E. Restore the database from a backup.
B
You use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs). You load data to the database by using Azure Data Factory. You need to reduce the amount of time it takes to load the data. Solution: You move the database to a Premium database pool that has 125 eDTUs. Does the solution meet the goal? A. Yes B. No
A
You use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs). You load data to the database by using Azure Data Factory. You need to reduce the amount of time it takes to load the data. Solution: You move the database to a Standard elastic pool that has 800 eDTUs. Does the solution meet the goal? A. Yes B. No
B. Run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in the context of Database 1.
You have a server named Serverl that is hosted in an Azure virtual machine. Server1 contains the following: One instance of SQL Server 2016 Enterprise 10 databases 500 stored procedures You have a database named Databasel that is hosted on Server1. Database1 contains 100 queries that are executed dynamically from web applications. You plan to remove data from the procedure cache on Database1. You have the following requirements: Changes to Database1 must not affect other databases that are hosted on Server1 Changes to Database1 must not affect the performance of queries that are stored in other databases. The solution must minimize administrative effort. You need to remove the data from the procedure cache as quickly as possible. What should you do? A. Run DBCC FREEPROCCACHE. B. Run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in the context of Database 1. C. Run DBCC DROPCLEANBUFFERS. D. Write a script that iterates through each stored procedure definition and add WITH RECOMPILE to the definition
sys.sp_rda_reauthorize_db sys.sp_rda_reconcile_columns
You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure. From SQL Server Management Studio (SSMS), a junior database administrator accidentally deletes several rows from the Azure SQL database and breaks the connection to Azure. You need to resume Stretch Database operations. Which two stored procedures should you use? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. A. Sys.sp_rda_reconcile_columns B. sys.sp_rda_set_rpo_duration C. sys.sp_rda_reconcile_indexes D. sys.sp_rda_reauthorize_db E. sys.sp_rda_reconcile_batch
B
You have an on-premises SQL Server database named DB1 that contains a table named TB1. TB1 is stretched to Microsoft Azure. A catastrophic hardware failure occurs on the on-premises SQL server. You deploy a new on-premises server and restore all databases to the new server. You need to resume Stretch Database operations to Azure. Which statements should you execute?
Create an automation account Create a credential Create a runbook Publish a runbook Configure a schedule
You have an on-premises database that runs several maintenance jobs. You move the database to a Microsoft Azure SQL database. You need to ensure that the maintenance jobs on indexes continue to run after the move. In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order. Create a runbook Create an automation account Configure a schedule Create a credential Publish a runbook
B
You have an on-premises server that runs Windows Server 2012 R2. The server has a Microsoft SQL Server 2016 instance that has one user database. The database is 2 TB. Your company has a Win32 application installed on 1,000 computers. The application connects to the database by using a network name of server1.contoso.local. You need to migrate the database to SQL Server 2016 on a Microsoft Azure virtual machine that runs Windows Server 2016. The solution must minimize outages to the application. What should you do? A. Copy the database files and update the records in DNS. B. Implement an availability group and update the records in DNS. C. Implement database mirroring and update the records in DNS. D. Implement database mirroring and change the connection string. E. Copy the database files and change the connection string.
B
You manage a Microsoft SQL Server environment with several databases. You need to ensure that queries use statistical data and do not initialize values for local variables. Solution: You set the value of the MAXDOP parameter to 2. Does the solution meet the goal? A. Yes B. No
AE
You manage an on-premises, multi-tier application that has the following configuration: Two SQL Server 2012 databases named SQL1 and SQL2 Two application servers named AppServerl and AppServer2 that run IIS You plan to move your application to Azure. You need to ensure that during an Azure update cycle or a hardware failure, the application remains available. Which TWO deployment configurations should you implement? Each correct answer presents part of the solution. CHOOSE ALL THAT APPLY A. Deploy AppServerl and AppServer2 in a single availability set. B. Deploy all servers in a single availability set. C. Deploy SQL1 and AppServerl in a single availability set. D. Deploy SQL2 and AppServer2 in a single availability set. https://www.gratisexam.com/ E. Deploy SQL1 and SQL2 in a single availability set.
A
You need to create an Elastic Database job to rebuild indexes across 10 Microsoft Azure SQL databases. Which powershell cmdlet should you run? A. New-AzureSqlJob B. New-AzureWebsiteJob C. New-AzureBatchJob D. New-ScheduledJobOption E. New-JobTrigger
B
You plan to migrate a Microsoft SQL Server instance between physical servers. You must migrate the metadata associated with the database instance. You need to ensure that the new instance retains the existing jobs and alerts. Solution: You restore the model database. Does the solution meet the goal? A. Yes B. No
B
You plan to migrate a Microsoft SQL server instance between physical servers. You must migrate the metadata associated with the database instance. You need to ensure that the new instance retains the existing jobs and alerts. Solution: You restore the master database. Does the solution meet the goal? A. Yes B. No
A
You plan to migrate a Microsoft SQL server instance between physical servers. You must migrate the metadata associated with the database instance. You need to ensure that the new instance retains the existing jobs and alerts. Solution: You restore the msdb database. Does the solution meet the goal? A. Yes B. No
B
You plan to migrate a Microsoft SQL server instance between physical servers. You must migrate the metadata associated with the database instance. You need to ensure that the new instance retains the existing jobs and alerts. Solutions: You restore the service master key. Does the solution meet the goal? A. Yes B. No
B
You use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs). You load data to the database by using Azure Data Factory. You need to reduce the amount of time it takes to load the data. Solution: You move the database to a Basic database pool that has 1,600 eDTUs. Does the solution meet the goal? A. Yes B. No
