April 23, 2015 at 12:37 pm
A heavily-selected database will be in an inconsistent state for several hours during a batch process. For that time, a database snapshot is created and accessed instead. To allow constant client read access to the database, a database that only contains synonyms exists. Those synonyms point to the main database except during the batch process, at which time they point to the database snapshot.
To switch the synonyms, each synonym is dropped and then created pointing to the database snapshot (after its creation, of course). The drop/create occurs inside a transaction. Roughly, the SQL looks like this:
SET XACT_ABORT ON;
BEGIN TRANSACTION;
DROP SYNONYM [dbo].[some_proc];
CREATE SYNONYM [dbo].[some_proc] FOR [snapshot_db].[dbo].[some_proc];
GRANT EXECUTE, SELECT ON [dbo].[some_proc] TO public;
COMMIT TRANSACTION;
When the batch update is completed, the process is reversed with "snapshot_db" replaced with "regular_db". The SQL snippet above is dynamic SQL. What I've pasted is the dynamic SQL that is executed as a single batch.
While this switch is happening, clients are accessing the procedures through the synonyms, potentially at a high request rate. Testing reveals that clients can get the error:
Error=-2147217900, Id=0, Meaning=IDispatch error #3092,
Source=Microsoft OLE DB Provider for ODBC Drivers,
Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.some_proc'.
This error only occurs once. If the same SPID retries its request and the transaction has not completed (for testing, a delay was added), then it blocks until the transaction completes.
Does anyone know why this happens and if there is any way to prevent it besides a client-side retry?
April 23, 2015 at 2:19 pm
If it only happens the first time you could just try to do something very small expecting it to error then continue on. Sounds like it might be a communication timeout. Depending on how you're connecting you can change the timeout parameter (though as I recall there are several places to change it if using OLEDB for instance).
April 27, 2015 at 10:15 am
Would not it be easier to change connection string on the client side?
With this approach any connection opened to the main DB would be completed against the main DB, no matter how long would it take. And at the same time newly opened connections would be using the "substitution" DB.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply