How can I configure IIS to connect to LogShipped database?
The developers have built a web service that needs to hit a local replica of our production SQL database. SQL 2012 Std SP2 CU2 The local replica is log shipped every 30 minutes. The restore operation ends all queries immediately, lasts about a minute, and then allows new connections. 90% of the time, the application fails to logon to the database. It runs at random internvals, hundreds of times per hour. The developers are getting this error in the application: System.Data.SqlClient.SqlException (0x80131904): Cannot open database "report2014" requested by the login. The login failed. Login failed for user 'svc_RepApp9999'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) ... (I can upload the rest of the stack trace if needed) I do not have any logs in the SQL database of failed login attempts by this user, so I don't believe it to be an authentication issue. Is there some "secret sauce" to config IIS to restart the connection pool when using a log shipped database? It seems like the restore operation kills the IIS connection, and IIS never retries it until hours later. Thanks in advance for the direction!
To be honest, I don't think this is going to work. It sounds like your log shipping is using standby mode (ie. read-only) or else the web service would never be able to connect. In that case, existing connections will be broken every time the restore kicks off and no new connections will be possible. I don't think anyone has solved how to design a database-dependent system that copes well with intermittent database availability. If there's a good reason why they can't just connect to the production database, I think you need to look at other ways of providing a copy. As far as I know that would be replication, mirroring or Always On. I'm afraid I don't know much about those to advise you which might work best.