SQL Server DBA Interview Questions and Answers – Database Mirroring – 2

Continuing with my previous post of Interview Questions series on Database Mirroring, this is the second blog on Database Mirroring.

1) What is Database Snapshot?

A database snapshot is a view of what the source database looked like at the time at which the snapshot was created. This means that all the objects will be the same as what it was when the snapshot was taken and all of the data will be exactly as it was then. To use database snapshots to recover from an unwanted DML statement, you need to have a suitable snapshot in place. Snapshots can only be created by using a T-SQL statement.

2) How to create a Database Snapshot?

CREATE DATABASE AdventureWorks2012_S1 ON (NAME = [AdventureWorks2012_Data], FILENAME = 'D:\ MSSQL\DATA\AdventureWorks2012_Data_SS_1.ss') AS SNAPSHOT OF AdventureWorks2012

3) How does a snapshot work?

High level tasks of snapshot involves

• When you create a snapshot a sparse file is created for each data file
• When data is modified in the source database for the first time, the old value of the modified data is copied to the sparse file
• If the same data is the subsequently changed again, those changes will be ignored and not copied to the snapshot
• When you query the snapshot, it first checks if the data is available in the snapshot. If it’s there it reads if from the snapshot. If it’s not there, I reads through to the source database and gets the data from there instead, because that means the data has not yet changed since the time the snapshot was taken

4)  What is SPARSE file?

When a database snapshot is created, a sparse file is added for each database file in the database of which the snapshot was taken. A sparse file is basically an empty file. It does not contain any data until a change is made to the source database

5) What are important things to remember about sparse files?

• The maximum size a sparse file can grow to is the size of the original file at the time of the database creation
• Sparse files are limited to 16 GB on Windows 2008 and 64 GB on Windows 2003
• Sparse files grow in increments of 64 KB

6) How can we check the SPARSE files?

SELECT name, is_sparse FROM sys.database_files

7) Can we change the permissions in a database snapshot?

No it’s not possible.

8)Can we take a backup of a database snapshot?

No.

9) Can we detach a database snapshot?

No.

10) What are the restrictions in Database Snapshots?

• Database snapshots depend on the source database. They can only be created on the same server as where the database resides (or the server to which the database is mirrored.)
• While there are snapshots present, you cannot drop the database or add any new files to it
• Once a database snapshot becomes suspect it cannot be saved. It just has to be deleted. This can happen if the snapshot runs out of space or reaches the maximum sparse file size limit
• You cannot create indexes on snapshots, they are strictly read only
• The user permissions are exactly the same as it was in the source database. You cannot grant a user access to a snapshot. You have to grant the access in the source database and then take another snapshot first before the user will be able to access it

11) What are the benefits of Database Snapshots?

• Snapshots can be used for reporting purposes.
• Using a mirror database that you are maintaining for availability purposes to offload reporting.
• Safeguarding data against administrative and user error

12) Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?

No it’s not possible, both principal and mirror should have same edition

13) Is it possible to take backup of mirrored database in mirror server?

No

14) Can I create multiple endpoints for configuring different databases for mirroring and point each database to unique endpoint?

No

15) Can I configure a single database to be mirrored to more than one server. i.e) One source & many destination like logshipping?

No

16) How to know the database mirroring connection time out in Mirroring?

SELECT Mirroring_Connection_Timeout FROM sys.database_mirroring WHERE database_id = db_id('Database Names')

17) How can I increase Heartbeat time between principal and mirror server? By default its 10 sec.

ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30

18) What status of mirroring has if secondary is down?

If secondary is down principle or Mirror show status disconnected

19) What status of mirroring has if principle is down?

If principle is down mirror will be disconnected with in recovery instead of synchronized with restoring

20) What status of mirroring has if mirroring is paused?

If mirroring is set to paused from principle then both principle & mirror in suspending

21) How to monitoring Mirroring?

There are six methods are available for monitoring the Database Mirroring
a) Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.
To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.
b) SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.
c) SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occur it will be logged to SQL Server log and Windows event log.
d) Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.
e) Profiler:- Profiler many events are providing the status of the Database mirroring
f) System Stored Procedures:-
sp_dbmmonitoraddmonitoring
sp_dbmmonitorchangemonitoring
sp_dbmmonitorhelpmonitoring
sp_dbmmonitordropmonitoring

22) What will be the impact if we add data files to the Primary database in Database Mirroring?

Data files will be added to the Mirror database if the identical location is present at the mirrored server without any issues.

23) What will be the impact if we add data files to the Primary database with a location which is not present on the Mirrored server in Database Mirroring?

Database mirroring will not work in this case. We have to break the Mirroring and take a Log backup of the Primary database and restore it on the Mirror database using “WITH MOVE” and “NORECOVERY” option. Once it’s done then Reestablish the mirroring.

24) If we add any users to the Primary database, will it be copied to the Mirror databases or not?

Yes, It will be copied to the Mirror database with the permissions. However you have to create the login manually with the same SID.s

25) How to disable mirroring by scripts?

ALTER DATABASE <sdatabase_name> SET PARTNER OFF

26) How to do manual failover to Mirror when principle is working fine?

ALTER DATABASE <DB Name> SET PARTNER FAILOVER

27) What is Transparent Client Redirection?

Database mirroring provides a feature of automatically redirection of the Application connection to Mirror database in case of Primary database failures. Database connections configured as below to enable this functionality.

.NET Example
Data Source=SQLA;Failover Partner=SQLB;Initial Catalog = AdventureWorks2008R2 ; Integrated Security=True

28) What is Log buffer?

Log buffer is a small contiguous memory structure, only 60k maximum, to host in memory transaction logs per database. When the transaction is committed, entire buffer will be written to transaction log file before commit finishes.

29) What is Automatic Page Repairing?

Automatic page repair is supported by database mirroring. After certain types of errors corrupt a page, making it unreadable, a database mirroring partner (principal or mirror) attempts to automatically recover the page. The partner/replica that cannot read the page requests a fresh copy of the page from its partner. If this request succeeds, the unreadable page is replaced by the readable copy, and this usually resolves the error, this whole process is called Automatic Page Repair.

30) What are the error types which cause and Automatic Page Repair attempt?

31) What are the page types which are not repairable using Automatic Page repair option?

Automatic page repair cannot repair the following control page types:

• File header page (page ID 0).
• Page 9 (the database boot page).
• Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

http://extremeexperts.com/sql/articles/DBMirroring1.aspx

http://sql-articles.com/articles/high-availability/advantages-a-disadvantages-of-database-mirroring/

http://dcx.sap.com/1100/en/dbadmin_en11/backup-s-3422468.html

http://sql-articles.com/articles/high-availability/types-and-operating-modes-in-mirroring/

http://solutioncenter.apexsql.com/using-sql-server-database-snapshots-protect-yourself-against-accidental-data-modification/