pdgb avatar image
pdgb asked

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!
sql server 2012iisweb-service
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

David Wimbush avatar image
David Wimbush answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks David! As I see it, my options going forward: 1) Rebuild the reporting database to use replication a. High-ish effort to implement 2) Update the SQL connection string to use the production database instead of the reporting database a. Low effort, but possible impact to performance on server 3) Create a 2nd database on the reporting instance along with a scheduled SSIS job a. The job will load the proper data on a tight interval b. Update IIS connection string to use this database instead c. More application development effort, minimal infrastructure effort
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.