QUESTION 87You 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:CORP\Employees – All company employeesCORP\HRAdmins – HR administrators onlyHR Administrators are also company employees.You need to grant users access according to the following requirements:CORP\Employees should have SELECT access to the Employees table.Only users in CORP\HRAdmins should have SELECT access to the SalaryDetails table.Logins are based only on Windows security groups.What should you do?

A. Create a database role called Employees.Add CORP\Employees to the db_datareader role.Add all company employees except HR administrators to the Employees role.Deny SELECT access to the SalaryDetails table to the Employees role.B. Create a database role called HRAdmins.Add all company employees except HR administrators to the db_datareader role.Add all HR administrators to the HRAdmins role.Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the db_datareader role.C. Create two database roles: Employees and HRAdmins.Add all company employees to the Employees role.Add HR administrators to the HRAdmins role.Grant SELECT access to all tables except SalaryDetails to the Employees role.Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the Employees role.D. Create a database role called Employees.Add all HR administrators to the db_datareader role.Add all company employees to the Employees role.Grant SELECT access to all tables except the SalaryDetails table to the Employees role.Deny SELECT access to the SalaryDetails table to the Employees role.

QUESTION 88You 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 wizardB. Use the Database Transfer wizardC. Use SQL Server Management Studio to deploy the database to Windows Azure SQL DatabaseD. Backup the database from the local server and restore it to Windows Azure SQL Database

QUESTION 89You administer a Microsoft SQL Server 2012 database named Orders.Orders contains a table named OrderShip that is defined as follows:

A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL.Customers require a procedure that will return orders for all customers from a specified country.You create a new procedure:

Performance on this procedure is slow.You need to alter the schema to optimize this query.Objects created must use a minimum amount of resources.Which Transact-SQL statement should you use?

A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULLB. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCode IS NOT NULLC. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL

Answer: BExplanation:Here creating statistics is relevant. The CREATE STATISTICS command creates query optimization statistics on one or more columns of a table, an indexed view, or an external table. For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics with CREATE STATISTICS or modify the query design to improve query performance.https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-transact-sql

QUESTION 90You administer a Microsoft SQL Server 2012 database.The database is currently configured to log ship to a secondary server.You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online.You want to perform a tail-log backup.You need to leave the primary database in a restoring state.Which option of the BACKUP LOG command should you use?

A. NO_TRUNCATEB. NORECOVERYC. STANDBYD. FORMAT

Answer: BExplanation:It is recommended that you take a tail-log backup in the following scenarios:* If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.Note: A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server

QUESTION 91You administer a Microsoft SQL Server 2012 database.You want to make a full backup of the database to a file on disk.In doing so, you need to output the progress of the backup.Which backup option should you use?

A. STATSB. COMPRESSIONC. CHECKSUMD. IN IT

Answer: AExplanation:STATS is a monitoring option of the BACKUP command.STATS [ =percentage ]Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

QUESTION 92You are a database administrator for a Microsoft SQL Server 2012 database named AdventureWorks2012.You create an Availability Group defined by the following schema. (Line numbers are included for reference only.)

You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:Production transactions should be minimally affected. The secondary server should allow reporting queries to be performed. If the primary server goes offline, the secondary server should not automatically take over.Which Transact-SQL statement should you insert at line 06?

QUESTION 93You 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 the Local System account.B. Configure the SQL Server Agent service to run under the Local System account. Configure the Subscriber to use the SQL Server Agent service account.C. 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.D. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1.

QUESTION 94You are a database administrator for a Microsoft SQL Server 2012 environment.You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.Which feature should you use?

Answer: BExplanation:Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-replication

QUESTION 95You 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 filesC. Modify application code to use table variablesD. Move the tempdb files to a local disk

Answer: DExplanation:The use of local disks for TempDB allows us to have more flexibility when configuring for optimal performance. It is a common performance recommendation to create the TempDB database on the fastest storage available. With the capability to utilize local disk for TempDB placement we can easily utilize disks that are larger, have a higher rotational speed or use SSD disks.https://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/

QUESTION 96You administer a Microsoft SQL Server 2012 database that includes a table named Application.Events. Application.Events contains millions of records about user activity in an application.Records in Application.Events that are more than 90 days old are purged nightly.When records are purged, table locks are causing contention with inserts.You need to be able to modify Application.Events without requiring any changes to the applications that utilize Application.Events.Which type of solution should you use?