SQL Server DBA Interview Question Accenture

In this post we will provide some SQL Server DBA Interview Question Accenture, these questions asked from one of friend recently go for interview in accenture for SQL Server DBA position.

Qus: Difference between Log Shipping and Database Mirroring?

Ans:

Log shipping	DB Mirroring
Primary server, secondary server and monitor server are the components in log shipping set up.	Principal server, mirror server, and witness server are the components involve in database mirroring set up.
Monitor server is an optional.	Witness server is an optional but it is a must for setting up automatic failover since witness is a watchdog instance to check if principal server is working.
Log shipping is a manual failover process.	Database mirroring is an automatic failover process.
There will not be an automatic application connection redirection. It has to be redirected manually.	Application connection can be redirected automatically with proper configuration.
Log shipping will have multiple secondary databases for the synchronization.	Database mirroring will not have multiple database destinations for mirroring the principal database. It will have one mirror database synchronizes with principal database.
There will be data transfer latency.	There will not be data transfer latency.
In log shipping, secondary database can be used for reporting solution.	In database mirroring, mirror database cannot be used for reporting solution. If need comes, database snapshot should be created to set up for the reporting solution.
Both committed and uncommitted transactions are transferred to the secondary database.	Only committed transactions are transferred to the mirror database.
Log shipping supports both bulk logged recovery model and full recovery model.	Mirroring supports only Full Recovery model.

Qus: Can we configure mirroring and replication on same database?

Ans: Yes we can configure Replication on database on which mirroring has been configured.

Qus: Can we configure Database mirroring and Log Shipping on same database?

Ans: Either one of Log Shipping or Database Mirroring.

Qus: Difference between types of Replication?

Ans:

Snapshot replication – As the name implies snapshot replication takes a snapshot of the published objects and applies it to a subscriber. Snapshot replication completely overwrites the data at the subscriber each time a snapshot is applied. It is best suited for fairly static data or if it’s acceptable to have data out of sync between replication intervals. A subscriber does not always need to be connected, so data marked for replication can be applied the next time the subscriber is connected. An example use of snapshot replication is to update a list of items that only changes periodically.
Transactional replication – As the name implies, it replicates each transaction for the article being published. To set up transactional replication, a snapshot of the publisher or a backup is taken and applied to the subscriber to synchronize the data. After that, when a transaction is written to the transaction log, the Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. Only committed transactions are replicated to ensure data consistency. Transactional replication is widely applied where high latency is not allowed, such as an OLTP system for a bank or a stock trading firm, because you always need real-time updates of cash or stocks.
Merge replication – This is the most complex types of replication which allows changes to happen at both the publisher and subscriber. As the name implies, changes are merged to keep data consistency and a uniform set of data. Just like transactional replication, an initial synchronization is done by applying snapshot. When a transaction occurs at the Publisher or Subscriber, the change is written to change tracking tables. The Merge Agent checks these tracking tables and sends the transaction to the distribution database where it gets propagated. The merge agent has the capability of resolving conflicts that occur during data synchronization. An example of using merge replication can be a store with many branches where products may be centrally stored in inventory. As the overall inventory is reduced it is propagated to the other stores to keep the databases synchronized.

Qus: What is copy only backup?

Ans: A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

Qus: How to copy one database from instance A to instance B?

Ans:

Backup and Restore

Attach\Detach

Copy Database

Qus: Can we do Database Mirroring when Filestream is enabled on database?

Ans: No

Qus: Prerequisites to configure Database Mirroring?

Ans:

For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.
The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.
The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database. For information about recovery model.
Verify that the mirror server has sufficient disk space for the mirror database.
When you are creating the mirror database on the mirror server, make sure that you restore the backup of the principal database specifying the same database name WITH NORECOVERY. Also, all log backups that were created after that backup was taken must also be applied, again WITH NORECOVERY.