Exam Code: 70-465
Exam Name: Designing Database Solutions for Microsoft SQL Server 2014 Exam
You create a stored procedure that retrieves all of the rows from a table named Table1.
You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if another transaction is modifying rows in Table1 simultaneously.
What should you recommend?
A. Snapshot isolation
B. A database snapshot
Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction.
You have a SQL Server instance on a server named Server1.
You need to recommend a solution to perform the following tasks every week:
– Rebuild the indexes by using a new fill factor.
– Run a custom T-SQL command.
– Back up the databases.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A trigger
B. An alert
C. A maintenance plan
D. Windows PowerShell
E. A system policy
Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.
You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment.
All DBAs are members of the DOMAIN\JrDBAs Active Directory group.
You grant DOMAIN\JrDBAs access to the SQL Server.
You need to create a server role named SpecialDBARole that can perform the following functions:
– View all databases.
– View the server state.
– Assign GRANT, DENY, and REVOKE permissions on logins.
You need to add DOMAIN\JrDBAs to the server role.
You also need to provide the least level of privileges necessary.
Which SQL statement or statements should you use? Choose all that apply.
A. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
D. GRANT VIEW DEFINITION TO [SpecialDBARole];
E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];
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.
You administer two Microsoft SQL Server 2012 servers.
Each server resides in a different, untrusted domain.
You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers.
What should you do?
A. Configure the SQL Server service account to use Network Service.
B. Use a server certificate.
C. Use a database certificate.
D. Configure the SQL Server service account to use Local System.
You administer a Microsoft SQL Server 2012 instance.
You need to configure a new database to support FILETABLES.
What should you do? Choose all that apply.
A. Disable FILESTREAM on the Database.
B. Enable FILESTREAM on the Server Instance.
C. Configure the Database for Partial Containment.
D. Create a non-empty FILESTREAM file group.
E. Enable Contained Databases on the Server Instance.
F. Set the FILESTREAM directory name on the Database.
You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows Server 2012 servers running SQL Server 2012. The Sales department uses a database named SalesDB. SalesDB contains a large table named Orders that lists every order ever received by the company.
You want to improve the performance of SalesDB.
You want to configure the database to provide the fastest possible access to the most recent orders. Historical orders can be stored using a slower storage solution.
How can you achieve this goal?
A. By configuring database mirroring.
B. By configuring a failover cluster.
C. By partitioning the Orders table.
D. By partitioning a partitioned view of the Orders table.
You work as a Database Administrator (DBA) at ABC.com.
You are in the process of deploying new servers running SQL Server 2012.
You need to deploy a SQL Server 2012 server to host databases used to host databases used by Research and Development department.
The databases used by the Research and Development department will store sensitive data.
A company security policy states that if Research and Development department database files are moved to another server, the files must be encrypted.
Which of the following solutions would meet the encryption requirement?
A. Encrypting File System (EFS).
B. Transparent Data Encryption (TDE).
C. Windows Bitlocker Drive Encryption.
D. Secure Sockets Layer (SSL)
You work as a Database Administrator (DBA) for a company named ABC.com. The company has a Windows Azure subscription. The company uses a cloud based SQL Server environment hosted on SQL Azure. Developers in your company are creating an ecommerce website.
You are designing a database for the website. The database will be hosted on SQL Azure. The database will store and reuse web site login details and customer credit card numbers.
You need to ensure the username, passwords and credit card details are securely stored in the database.
Which of the following would be the most suitable secure storage solution?
A. Secure Sockets Layer (SSL)
C. Data encryption
D. Transparent Data Encryption (TDE)
E. Encrypting File System (EFS)
You work as a Database Administrator (DBA) for a company named ABC.com. The company uses a Microsoft SQL Server 2012 infrastructure. A server named ABC-SQL1 hosts multiple databases that are accessed using OLTP (Online Transaction Processing) applications.
You are concerned about the lack of redundancy provided by this single server solution.
You want to configure a solution that ensures high-availability for the databases while minimizing costs.
You need to ensure that the databases remain online in the event of a hardware failure of ABC-SQL1.
You configure a second server named ABC-SQL2 and install SQL Server 2012.
Which of the following solutions would provide the high-availability and enable users to run reports on both copies of the databases?
A. AlwaysOn availability groups
B. A failover cluster
C. Database mirroring
D. Transaction log shipping
If you want to pass Microsoft 70-465 exam successfully, donot missing to read latest lead2pass Microsoft 70-465 dumps.
If you can master all lead2pass questions you will able to pass 100% guaranteed.