What’s the secret of easily passing new 70-465 exam? PassLeader have been updated the 70-465 149q exam dumps with the newest exam questions. We offer the latest 70-465 149q PDF dumps and VCE dumps with New Version VCE Player for free download to ensure your 70-465 exam pass. Now visit www.passleader.com and get the 100 percent pass ensure 70-465 149q braindump!
keywords: 70-465 exam,70-465 exam dumps,70-465 149q exam questions,70-465 149q pdf dumps,70-465 vce dumps,70-465 149q braindump,Designing Database Solutions for Microsoft SQL Server 2014 Exam
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
You work as a Database Administrator (DBA) for a company named ABC.com. The company uses a Microsoft SQL Server 2012 infrastructure. Computer users use custom applications that store data in SQL Server databases hosted on a server named ABC-SQL1. You discover that the Windows Application event log on ABC-SQL1 contains error events. You need to configure a solution that will run an application whenever an event with a specific event ID number is generated in the Windows Application event log. What should you configure?
A. You should configure an alert and a job.
B. You should configure an alert and a maintenance plan.
C. You should configure a trigger and a maintenance plan.
D. You should configure a trigger and a job.
E. You should configure an alert and a trigger.
You need to move a database in between servers. You need to guarantee database users will be able to login in the database with a minimum of management effort. Select the BEST answer.
A. Application role
B. Database user
C. Server login
D. Server role
You need to validate rows before they are added to a table every time a row is added using a user-defined function. What should you use? More than one answer may achieve the goal. Select the BEST answer.
A. DML Trigger
B. Default constraint
C. Foreign key
D. CHECK constraint
You have a SQL Server 2012 database named DB1. You have a backup device named Device1. You discover that the log file for the database is full. You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs). Which code segment should you execute?
A. BACKUP LCG DB1 TO Device1 WITH COPY_ONLY
B. BACKUP LOG DB1 TO Device1
C. BACKUP LOG DB1 TO Device1 WITH NCRECCVERY
D. BACKUP LOG D31 TO Device1 WITH TRUNCATE ONLY
You use SQL Azure to store data used by an e-commerce application. You develop a stored procedure named sp1. Sp1 is used to read and change the price of all the products sold on the e-commerce site. You need to ensure that other transactions are blocked from updating product data while sp1 is executing. Which transaction isolation level should you use in sp1?
A. read committed
B. repeatable read
Drag and Drop Question
You are designing a database for a university. The database will contain two tables named Classes and StudentGrades that have the following specifications:
– Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths.
– StudentGrades must be backed up on a separate schedule than the rest of the database.
You need to identify which SQL Server technology meets the specifications of each table. Which technologies should you identify? To answer, drag the appropriate technology to the correct table in the answer area.
You use SQL Server 2014 to maintain the data used by applications at your company. You want to execute two statements. You need to guarantee that either both statements succeed, or both statements fail together as a batch. Which code should you use?
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
Your company has a SQL Azure subscription. You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1.Sp1 reads data from Table1 and inserts data into Table2. A user named User1 informs you that he is unable to run sp1. You verify that User1 has the SELECT permission on Table1 and Table2. You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1. What should you do?
A. Grant User1 the INSERT permission on Table2.
B. Add User1 to the db_datawriter role.
C. Change sp1 to run as the sa user.
D. Grant User1 the EXECUTE permission on sp1.
You deploy a SQL Server instance named SQLProd that uses SQL Server 2014. You need to recommend a solution to monitor the transactions that are running currently against SQLProd. The solution must minimize the amount of custom code required. What should you recommend?
B. A dynamic management view
C. A trigger
D. User-defined views
Your network contains an Active Directory domain that has two groups named Group1 and Group2. The domain contains two SQL Server instances named SQLDev and SQLProd. Each SQL Server instance has access to various storage media. The SQL Server instances have a database that contains a table named Table1. Table1 contains a column named Column1. The value for Column1 can be either Value1 or Value2. You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1. What should you recommend?
A. A dynamic management view
C. Snapshot isolation
D. User-defined views
You are the new database administrator for a SQL Server 2014 instance. You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring. You want to set up monitoring to see if query performance is being affected. You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed. What should you do?
A. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key columns in the database.
B. Use the missing_column_statistics extended event.
C. Query the sys.statistics system view to see all cases where the statistics were last needed.
D. Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical.
You have two databases named DB1 and DB2 that are located on the same server. You plan to create a stored procedure named SProc1 in DB1. SProc1 will query a table named Table2 in DB2. You need to recommend a solution to ensure that SProc1 can access Table2 without granting users direct access to Table2. What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.
A. Contained databases
B. Application roles
C. Cross-database ownership chaining
D. Digital certificates
You have a server named Server1 that has 16 processors. You plan to deploy multiple instances of SQL Server 2014 to Server1. You need to recommend a method to allocate processors to each instance. What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.
A. Processor affinity
B. Windows System Resource Manager (WSRM)
C. Max Degree of Parallelism
D. Resource Governor
You have two SQL Server 2014 instances named SQLDev and SQLProd. You plan to create a new database on SQLProd that will use SQL Server Authentication. You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev even if they do not have a login on the SQLDev instance. What should you use? More than one answer choice may achieve the goal. Select the BEST answer.
B. Contained database
C. SQL Server Analysis Services (SSAS) scripts
D. Extended Events
E. SQL Server Integration Services (SSIS) scripts
You have a query that is used by a reporting dashboard. Users report that the query sometimes takes a long time to run. You need to recommend a solution to identify what is causing the issue. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Set the blocked process threshold, and then run SQL Server Profiler.
B. Set the blocked process threshold, and then create an alert.
C. Enable trace flag 1204, and then create an alert.
D. Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.