are updated and are verified by experts. Once you have completely prepared with our you will be ready for the real 70-765 exam without a problem. We have . PASSED First attempt! Here What I Did.
Free 70-765 Demo Online For Microsoft Certifitcation:
NEW QUESTION 1
A company has an on-premises Microsoft SQL Server environment with a SQL-Server named SQL01. You need to create a local sysadmin account on SQL01 named Admin1.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
Explanation: B: First we create a login with the CREATE LOGIN command. E: Then we add it to the sysadmin role.
1. To add a member to a fixed server role
2. In Object Explorer, connect to an instance of Database Engine.
3. On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. ALTER SERVER ROLE diskadmin ADD [Domain\Juan] ;
G: Finally we add a database user for the login we created.
NEW QUESTION 2
You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails. You add two Windows groups as logins for the server:
You need to grant users access according to the following requirements: What should you do?
- A. Create a database role called Employees.Add CORP\Employees to the db_datareader rol
- B. Add all company employees except HR administrators to the Employees rol
- C. Deny SELECT access to the SalaryDetails table to the Employees role.
- D. Create a database role called HRAdmins.Add all company employees except HR administrators to the db_datareader rol
- E. Add all HR administrators to the HRAdmins rol
- F. Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the db_datareader role.
- G. Create two database roles: Employees and HRAdmin
- H. Add all company employees to the Employees role.Add HR administrators to the HRAdmins rol
- I. Grant SELECT access to all tables except SalaryDetails to the Employees rol
- J. Grant SELECT access to the SalaryDetails table to the HRAdmins rol
- K. Deny SELECT access to the SalaryDetails table to the Employees role.
- L. Create a database role called Employees.Add all HR administrators to the db_datareader rol
- M. Add all company employees to the Employees rol
- N. Grant SELECT access to all tables except the SalaryDetails table to the Employees rol
- O. Deny SELECT access to the SalaryDetails table to the Employees role.
Explanation: Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
NEW QUESTION 3
You administer a Windows Azure SQL Database database named Orders. You need to create a copy of Orders named Orders_Reporting.
Which Transact-SQL command should you use?
- A. BACKUP DATABASE Orders TO DISK = 'D:\Orders.bak'RESTORE DATABASEOrders_ReportingFROM DISK = 'D:\Orders.bak
- B. BACKUP DATABASE Orders TO DISK = 'D:\Orders.bak'CREATE DATABASEOrders_ReportingFROM DISK = 'D:\Orders.bak
- C. CREATE DATABASE Orders_Reporting AS COPY OF Orders
- D. BACKUP DATABASE Orders TO DISK = 'D:\Orders.bak'MIRROR TO DISK = 'Orders_Reporting
Explanation: BACKUP DATABASE …AS COPY OF [source_server_name.]source_database_name Is used for copying a database to the same or a different SQL Database server.
NEW QUESTION 4
You manage a Microsoft SQL Server instance named SQL1 that has 32 gigabytes (GB) of total memory. The instance supports an app named App1 that only uses a single thread. App1 frequently queries the database using the same index. The operating system and App1 combined require 8 GB of memory to function.
You need to ensure that the SQL Server does not limit the performance of App1. What configuration option should you set?
- A. min memory per query to 4 GB
- B. index create memory to 16 GB
- C. max worker threads to 1
- D. max server memory to 16 GB
Explanation: The index creates memory option controls the maximum amount of memory initially allocated for sort operations when creating indexes. The default value for this option is 0 (self-configuring). If more memory is later needed for index creation and the memory is available, the server will use it; thereby, exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.
NEW QUESTION 5
You need to open the firewall ports for use with SQL Server environment. In table below, identify the firewall port that you must use for each service.
NOTE: Make only one selection in each column.
Explanation: Report Server: 80
By default, the report server listens for HTTP requests on port 80.
NEW QUESTION 6
You are building the database platform for a multi-tenant application. The application will have one database per tenant and will have at least 30 tenants. Each tenant will have a separate resource group for billing purposes.
The application will require at least 10 GB of clustered columnstore indexes for each database.
You need to implement the database platform for the application. The solution must minimize costs. What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Explanation: The Standard tier service allows for 1TB of data. Here 30 x 10 GB, 0.3 TB, is required.
NEW QUESTION 7
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 database pool that has 800 eDTUs. Does the solution meet the goal?
- A. Yes
- B. No
Explanation: We need at least 400 eDTUs and the use of a Standard database pool.
NEW QUESTION 8
You administer a Microsoft SQL Server 2014 Enterprise Edition server that uses 64 cores.
You discover performance issues when large amounts of data are written to tables under heavy system load. You need to limit the number of cores that handle I/O.
What should you configure?
- A. Processor affinity
- B. Lightweight pooling
- C. Max worker threads
- D. I/O affinity
Explanation: The affinity Input-Output (I/O) mask Server Configuration Option.
To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce Microsoft SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association between a thread and a processor is called processor affinity.
NEW QUESTION 9
A new Azure Active Directory security principal named ReportUser@contoso.onmicrosoft.com should have access to select all current and future objects in the Reporting database. You should not grant the principal any other
permissions. You should use your Active Directory Domain Services (AD DS) account to authenticate to the Azure SQL database.
You need to create the new security principal.
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.
Explanation: Step 1:
To provision an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database (here the Reporting database) with an Azure AD identity (not with a SQL Server account) that has access to the database.
Step 2: CREATE USER ... FROM EXTERNAL PROVIDER
To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
Grant the proper reading permissions.
NEW QUESTION 10
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
Explanation: The Service Master Key is the root of the SQL Server encryption hierarchy. It does not handle alerts and jobs. The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as
SQL Server Management Studio, Service Broker and Database Mail.
NEW QUESTION 11
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)
Explanation: If REBUILD is performed online (ON) the data in this table is available for queries and data modification during the index operation.
NEW QUESTION 12
You have an on-premises database.
You plan to migrate the database to Microsoft SQL Server on a Microsoft Azure virtual machine.
You move the database files to Azure.
You need to attach the database files to the SQL Server instance on the virtual machine. The solution must ensure that you can run file snapshot backups.
How should you complete the statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation: References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-sql-server-transact-sql
NEW QUESTION 13
You plan to deploy 20 Microsoft Azure SQL Database instances to an elastic pool in Azure to support a batch processing application.
Two of the databases in the pool reach their peak workload threshold at the same time every day. This leads to inconsistent performance for batch completion.
You need to ensure that all batches perform consistently. What should you do?
- A. Create an In-Memory table.
- B. Increase the storage limit in the pool.
- C. Implement a readable secondary database.
- D. Increase the total number of elastic Database Transaction Units (eDTUs) in the pool.
Explanation: In SQL Database, the relative measure of a database's ability tohandle resource demands is expressed in Database Transaction Units (DTUs) for single databases and elastic DTUs (eDTUs) for databases in an elastic pool.
A pool is given a set number of eDTUs, for a set price. Within the pool, individual databases are given the flexibility to auto-scale within set parameters. Under heavy load, a database can consume more eDTUs to meet demand.
Additional eDTUs can be added to an existing pool with no database downtime. References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool
NEW QUESTION 14
Note: This questions is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You manage on-premises and Microsoft Azure SQL Database instances for a company. Your environment must support the Microsoft SQL Server 2012 ODBS driver.
You need to encrypt only specific columns in the database. What should you implement?
- A. transport-level encryption
- B. cell-level encryption
- C. Transparent Data Encryption
- D. Always Encrypted
- E. Encrypting File System
- F. BitLocker
- G. dynamic data masking
Explanation: To encrypt columns you can configure Always Encrypted.
SQL Server Management Studio (SSMS) provides a wizard that helps you easilyconfigure Always Encrypted by setting up the column master key, column encryption key, and encrypted columns for you.
Always Encrypted allows client applications to encrypt sensitive data and never reveal the data or the encryption keys to SQL Server or Azure SQL Database. An Always Encrypted enabled driver, such as the ODBC Driver 13.1 for SQL Server, achieves this by transparently encrypting and decrypting sensitive data in the client application.
Note: The ODBC driver automatically determines which query parameters correspond to sensitive database columns (protected using Always Encrypted), and encrypts the values of those parameters before passing the data to SQL Server or Azure SQL Database. Similarly, the driver transparently decrypts data retrieved from encrypted database columns in query results.
NEW QUESTION 15
You are tuning the performance of a virtual machines 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
You need to prevent the PAGELATCH_IO waits from occurring.
Solution: You rewrite the queries to use aggregates instead of PIVOT statements. Does this meet the goal?
- A. Yes
- B. No
Explanation: Instead you can add more files to the database.
NEW QUESTION 16
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a virtual machine (VM) in Microsoft Azure, which has a 2 terabyte (TB) database. Microsoft SQL Server backups are performed by using Backup to URL.
You need to provision the storage account for the backups while minimizing costs. Which storage option should you use?
- A. Premium P10 disk storage
- B. Premium P20 disk storage
- C. Premium P30 disk storage
- D. Standard locally redundant disk storage
- E. Standard geo-redundant disk storage
- F. Standard zone redundant blob storage
- G. Standard locally redundant blob storage
- H. Standard geo-redundant blob storage
Explanation: A URL specifies a Uniform Resource Identifier (URI) to a unique backup file. The URL is used to provide the location and name of the SQL Server backup file. The URL must point to an actual blob, not just a container. If the blob does not exist, it is created. If an existing blob is specified, BACKUP fails, unless the “WITH FORMAT” option is specified to overwrite the existing backup file in the blob.
LOCALLY REDUNDANT STORAGE (LRS) makes multiple synchronous copies of your data within a single datacenter.
Thanks for reading the newest 70-765 exam dumps! We recommend you to try the PREMIUM Surepassexam 70-765 dumps in VCE and PDF here: https://www.surepassexam.com/70-765-exam-dumps.html (209 Q&As Dumps)