how can we read mirrored database?

_{Sunday, December 21, 2014 3:49 PM}

 Hello everyone, is it possible to read mirrored database? if so how can we do this?

I need a step by step procedure to make my mirrored database to read and also its benefits and requirements.

Thank you.

_{Sunday, December 21, 2014 5:13 PM ✅Answered}

Hello

Hello everyone, is it possible to read mirrored database?

Is it possible? Yes.

if so how can we do this?

You'll need enterprise edition and have to use database snapshots: http://msdn.microsoft.com/en-us/library/ms175158.aspx

Alternatively, if you're using SQL Server 2012 or 2014, you can use availability groups and get this by default without needing snapshots.

I need a step by step procedure to make my mirrored database to read and also its benefits and requirements.

I'm not going to do your job for you. I've pointed you in the correct direction, you'll have to do the work.

-Sean

The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

_{Monday, December 22, 2014 3:29 PM ✅Answered}

Hi raghu,

As your description, you want to configure a readable mirror database. It is possible to read data from the mirror database. To do this, you need to create a database snapshot according to Sean's post. Below are the steps about how to read data from the mirror database.

1. Create a Database Snapshot on the Mirror according to article: http://msdn.microsoft.com/en-us/library/ms175876.aspx.

2. Now you eventually need to adjust permissions. In the master database, enable the read-only connection to the snapshot.

3. The read-only application can connect against the snapshot and read the data at its state of the time the snapshot has been created. For more information about the process, please refer to the article: http://blogs.msdn.com/b/saponsqlserver/archive/2012/12/25/reading-from-database-mirroring-or-alwayson-secondary-replicas-in-sap-configurations.aspx.

However, this only apply to enterprise edition. And a database snapshot is static, new data is not available, so the data will become stale. To make relatively recent data available to your users, you must create a new database snapshot periodically. To avoid this disadvantage of data being stale, you could use AlwaysOn instead by configuring secondary replica read-only access. For how to configure secondary replica read-only access, please refer to the article: http://www.bidn.com/blogs/PatrickLeBlanc/ssis/2513/alwayson-configuring-secondary-read-only-access

Regards,
Michelle Li

_{Monday, December 22, 2014 2:27 AM}

Hi Sean Gallardo,

First of all thanks for your post.

And what I understood from this means, if we need to read mirrored database it is not possible. that is one of the limitation of Mirrored database.

So alternative is either creating a snapshot of the source database and use it to read or by providing availability groups in SQL Server 2012 to full fill our requirements.

Is it right?