Mirrored Database as SSRS Data Source

Recently one of our report started to fail with error like Unable to Connect to Database error. Then only we came to know that the said database is in a mirror setup. In the mirrored environment, we have to always connect to Principal, Mirrored database will be inaccessible to the applications.  When the failover happens, Principal becomes Mirror and vice versa. And this flip can happen automatically also.

So our reports should be intelligent enough to identify which one is Principal. If it tries to access the Mirror, then it will error out. However SSRS spares us from this headache. The only thing we need to tell SSRS is which servers form Principal/Mirror pair. And this information has to be mentioned in the connection string itself like below

Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=DB1

Thats it. When failover happens SSRS will do a retry, if required, and connect to the appropriate Principal Server.

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in SSRS and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s