Microsoft Administering Microsoft SQL Server 2012 Databases 70-462 Exam Questions

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).

The financial database has the following characteristics:

* A data file of 2 terabytes is located on a dedicated LUN (drive D).

* A transaction log of 10 GB is located on a dedicated LUN (drive E).

* Drive D has 1 terabyte of free disk space.

* Drive E has 5 GB of free disk space.

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours.

Five percent of the existing data is modified each day.

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands.

Each data load adds 3 GB of data to the database.

These data load operations must occur in the minimum amount of time.

A full database backup is performed every Sunday at 10:00 hours.

Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

You implement log shipping of the financial database to another SQL Server 2012 instance.

You decide to failover to this secondary database.

You need to ensure that all transactions will be replicated to the secondary database.

Which backup option should you use?

Every SQL Server 2012 database must have a datafile and which of the following file types associated with it?

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 to UserB? Select all that apply.

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:

* Takes an input variable

* Returns a table of values

* Cannot be referenced within a view

Which object should you use?

If you are restoring and recovering a database, which of the following keywords must you specify if you intend to roll forward a transaction log backup after restoring a database?

You administer a Microsoft SQL Server database.

You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01.

You need to configure the availability group to prevent data loss. In the event of a database failure, the designed secondary database must come online automatically.

Which Transact-SQL statement should you use?

You have a server named SQL1 mat has SQL Server installed SQU hosts several applications. You plan to deploy a third-party application to SQU. 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 fixed 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 m 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?