2018 July New Micorsoft 70-462 Exam Dumps with PDF and VCE Free Updated Today! Following are some new 70-462 Real Exam Questions:
QUESTION 288
You administer a Microsoft SQL Server 2012 database named Contoso that contains a single user-defined database role namedBillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permission for all stored procedures in Contoso to BillingUsers.
Which Transact-SQL statement should you use?
A.
B. GRANT EXECUTE ON INFORMATION_SCHEMA.ROUTINES TO BillingUsers
C. EXEC sp_addrolemember ‘executor’, ‘BillingUsers’
D.
Answer: D
QUESTION 289
You administer a database that has User A, B and C.
User A must be able to create new tables and stored procedures, User B must be able to select, update, delete and insert data.
User C must be able to provide windows logins to the database.
To which role or roles should you add the users?
A. db_accessadmin
B. db_ddladmin
C. db_datawriter
D. db_datareader
E. db_owner
Answer: ABCD
QUESTION 290
You administer a Microsoft SQL Server 2012 database.
Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error.
You need to identify the root cause of the issue by retrieving the error message.
What should you do?
A. Update all stored procedure to use a TRY…CATCH block.
B. Create a SQL Profiler session to capture all ErrorLog and EventLog events.
C. Flag all stored procedures for recompilation by using sp_recompile.
D. Execute sp_who.
Answer: A
QUESTION 291
You are the administrator of a Microsoft SQL Server 2012 server.
Some applications consume significant resources.
You need to manage the server workload by restricting resource-intensive applications. You need to dynamically limit resource consumption.
What should you do?
A. Set up Service Broker to ensure that applications are not allowed to consume more than the specified amount of resources.
B. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor.
C. Configure Extended Events to monitor and restrict resource limits allowed by each application type.
D. Create a new Plan Guide with a Scope Type of SQL and define the resource limits for each application.
Answer: B
Explanation:
SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.
The following three concepts are fundamental to understanding and using Resource Governor:
Resource pools. A resource pool, represents the physical resources of the server.
Workload groups. A workload group serves as a container for session requests that have similar classification criteria.
Classification. The Classification process assigns incoming sessions to a workload group based on the characteristics of the session. You can tailor the classification logic by writing a user-defined function, called a classifier function. Resource Governor also supports a classifier user-defined function for implementing classification rules.
References: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor
QUESTION 292
You plan to migrate the database to Azure. You verify that all objects are valid for Azure SQL database. You need to ensure that users and logins are migrated to Azure.
What should you do?
A. Use the Copy Database wizard
B. Use the Database Transfer wizard
C. Use the SQL Management Studio to deploy the database to Azure
D. Back up the database from the local server and restore it to Azure
Answer: C
Explanation:
Use SQL Server Management Studio to establish a connection to your Azure SQL Database server.
Steps:
Create an empty Azure SQL database in the Azure portal (using a new or existing Azure SQL Database server)
Create a server-level firewall in the Azure portal (if not previously created)
Use the Data Migration Assistant (DMA) to import your SQL Server database into the empty Azure SQL database
Use SQL Server Management Studio (SSMS) to change database properties.
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-migrate-your-sql-server-database
QUESTION 293
You maintain several databases on a 64-bit Microsoft SQL 2005 instance.
You need to migrate the databases to SQL 2012 on the same server. You also need to ensure functionality in SQL 2012 can be used in the database after migration.
What should you do? (Each correct answer presents part of the solution.)
A. Perform an in-place upgrade to 64-bit in SQL 2012
B. Perform a side by side installation of a 64-bit SQL 2012
C. Perform a side by side installation of a 32-bit SQL 2012
D. Detach the old instance and attach it to the new one
E. Change the compatibility level of the database
Answer: BDE
Explanation:
B: In a side-by-side upgrade, SQL Server 2012 installs either along with SQL Server 2008 (or 2005) as a separate instance or on a different server. This process is essentially a new installation followed by a database migration.
D: You can use detach and attach operations to upgrade a SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2 database in SQL Server 2012. After being attached to SQL Server 2012, the database is available immediately and is automatically upgraded.
E: One important factor to contemplate during an upgrade and migration effort are your database compatibility levels, which control certain database behaviors to be compatible with a particular version of SQL Server. Unlike the database file version (which get changed when you restore a database backup in a newer version or attach an older database to a newer version of SQL Server), the database compatibility level can be changed back and forth to different values.
References:
https://logicalread.com/sql-server-2012-side-by-side-upgrade-w02/#.WuiWdYhubIU
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms189625(v=sql.110)
https://www.sqlskills.com/blogs/glenn/upgrading-sql-server-database-compatibility-levels/
QUESTION 294
You administer a Microsoft SQL Server database.
Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error.
You need to identify the root cause of the issue by retrieving the error message.
What should you do?
A. Create a SQL Profiler session to capture all ErrorLog and EventLog events.
B. Flag all stored procedures for recompilation by using sp_recompile.
C. Create an Extended Events session by using the sqlserver.error_reported event.
D. Create an Extended Events session by using the sqlserver.errorlog_written event.
Answer: C
Explanation:
Event sqlserver.error_reported: This event gets fired every time that an error happens in the server References:
http://msdn.microsoft.com/en-us/library/bb630282.aspx
QUESTION 295
You have a server named SQL1 that has SQL Server installed. SQL1 hosts several applications.
You plan to deploy a third-party application to SQL1. The application must have the following:
– A vendor-provided password for the sa account
– A vendor-provided user account that is a member of the sysadmin fixed server role
– A vendor-provided user account that is a member of the db_owner database role in the msdb database
– A vendor-provided user account that is a member of the db_owner fixed database role in the master database
– A vendor-provided user account that is a member of the SQLAgentOperatorRole fixed database role in the msdb database
You need to identify the minimum number of SQL Server instances that you must add to SQL1 to support the application requirements. The solution must use the principle of least privilege.
How many SQL Server instances should you add to SQL1?
A. 0
B. 1
C. 2
D. 3
Answer: B
Explanation:
If there are any server role requirements it’s a cue to me solution requires a dedicated SQL instance. I don’t want any user of Database X having the ability to affect Database Y (other than through resource consumption which can be controlled via Resource Governor.)
Reference: http://www.itprotoday.com/microsoft-sql-server/17-questions-every-sql-dba-should-ask-supporting-new-third-party-database
QUESTION 296
You administer two Microsoft SQL Server 2012 servers named ProdSrv1 and ProdSrv2.
ProdSrv1 is configured as a Distributor.
Both servers are configured to use the Windows NT Service virtual accounts for all SQL Services.
You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as a pull subscriber.
The distribution agent on ProdSrv2 regularly fails, displaying the following error message:
“Cannot access the file. Operating system error code 5 (Access is denied.).”
You need to configure the distribution agent by granting only the minimum required access to all accounts.
What should you do?
A. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on Prodsrv1
B. Configure the SQL Server Agent service to run under a Windows domain account. Configure the Subscriber to use the SQL Server Agent service account. Grant FULL CONTROL access for the domain account to the ReplData share on ProdSrv1
C. Configure the SQL Server Agent service to run under the Local System account. Configure the Subscriber to use the SQL Server Agent service account
D. Configure the Subscriber to use the Local System account
Answer: A
QUESTION 297
You manage a server that has SQL Server installed and hosts databases for five applications. Each application has a different login to the SQL Server instance.
You need to create a TCP endpoint to be used only by a login named Application3. The solution must ensure that Application3 only uses the new endpoint.
Which three statements should you execute? Each correct answer presents part of the solution.
A. GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3
B. DENY CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3
C. CREATE ENDPOINT Application3_EndPoint STATE=STARTED
AS TCP (LISTENER_PORT=1388)
D. GRANT .CONNECT ON ENDPOINT:: Application3_EndPoint TO Public
E. GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO PUBLIC
F. REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3
G. REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] TO PUBLIC
Answer: ACG
Explanation:
C: Create the endpoint
A: Grant permissions to Appliciation3 on the endpoint.
G: Revoke information from public on the endpoint.
Incorrect Answers:
B: We should grant, not deny, access to Application3.
D, E: Access to Public should not be granted.
QUESTION 298
You administer a Microsoft Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory.
Users need to be able to SELECT from all tables in the database and execute the stored procedure.
You need to grant only the necessary permissions.
What should you do?
A. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.
B. Add EXECUTE AS OWNER to the stored procedure. Add all users to the db_datareader role
C. Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Grant VIEW DEFINITION to the role. Add all users to the role
D. Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Add all users to the role. Add all users to the db_datareader role
Answer: D
Explanation:
The db_datareader role allows a user to be able to issue a SELECT statement against all tables and views in the database.
References: https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/
!!!RECOMMEND!!!
1.|2018 Latest 70-462 Exam Dumps (PDF & VCE) 313Q&As Download:
https://www.braindump2go.com/70-462.html
2.|2018 Latest 70-462 Exam Questions & Answers Download:
https://drive.google.com/drive/folders/1CzCDfoHpyjc9sRJ9CnfrmejAQqzb8KVQ?usp=sharing
3.|2018 Latest 70-462 Study Guide Video: