SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.


Leave a comment on the original post [skamie.wordpress.com, opens in a new window]

Loading comments...