September 14, 2012 at 9:28 am
I have a tricky situation where we have a server with a default and an instance.In the default we have 4 databases
for example (A,B,C,D) and we mirror them to instance using the asynchronous with high performance let us say these mirrored
databases are (A,B,C,D) in instance.We even have another database 'E' in the instance.We again create database snapshots of these
mirrored databases in regular intervals let's say they are (A1,B1,C1,D1).We created seperate job's for the creation of each snapshots
and each job has two steps.
step-1 is to drop the existing snapshot and recreate the snapshot.
step-2 is to run the stored procedure which is in database'E'.
this stored procedure is used to create the synonyms of the tables of the mirrored databases
if they are not actually present in the database 'E' as we store synonyms of all mirrored databases in database 'E'.
We have seperate store procedure for each mirrored DB.
Once the job failed and We got an error saying the job of DB('A') has failed because the database ('B') is still recovering
and this error is not always but just appears some times and the databases are even not the same previuosly it referrend to ('B')
is recovering some times ('C') job has failed because ('A') is recovering.It says [SQL STATE 42000 error-922].
Is there any solution to this problem?
September 14, 2012 at 9:44 am
devullapallymanidhar (9/14/2012)
I have a tricky situation where we have a server with a default and an instance.In the default we have 4 databasesfor example (A,B,C,D) and we mirror them to instance using the asynchronous with high performance let us say these mirrored
databases are (A,B,C,D) in instance.We even have another database 'E' in the instance.We again create database snapshots of these
mirrored databases in regular intervals let's say they are (A1,B1,C1,D1).We created seperate job's for the creation of each snapshots
and each job has two steps.
step-1 is to drop the existing snapshot and recreate the snapshot.
step-2 is to run the stored procedure which is in database'E'.
this stored procedure is used to create the synonyms of the tables of the mirrored databases
if they are not actually present in the database 'E' as we store synonyms of all mirrored databases in database 'E'.
We have seperate store procedure for each mirrored DB.
Once the job failed and We got an error saying the job of DB('A') has failed because the database ('B') is still recovering
and this error is not always but just appears some times and the databases are even not the same previuosly it referrend to ('B')
is recovering some times ('C') job has failed because ('A') is recovering.It says [SQL STATE 42000 error-922].
Is there any solution to this problem?
Your details are a little confusing, so let me try to rephrase.
You have a default instance and a named instance on the same server. On the default instance, there are 4 original / source databases that are being mirrored over on the named instance. The named instance has an extra database (E) that keeps all the code, database names, synonyms and other data needed for DBA sanity.
Every day you create a database snapshot of the destination databases (the mirrored versions located on the named instance) after dropping the old snapshot, then run the stored procedures in the E database against those databases.
I'm not clear on why you have to recreate synonyms on the mirrored databases since I would think they still exist. Or are you running the synonym creation against the database snapshots? (Is that even possible?)
So the setup looks like this:
Default--------Named
Source A -> Destination A -> Snapshot A
Source B -> Destination B -> Snapshot B
Source C -> Destination C -> Snapshot C
Source D -> Destination D -> Snapshot D
No Source --Destination E -> Create Synonyms
So running the proc for Destination B complains that Destination A is restoring??
Have any of your databases ended up in SUSPECT mode? Check the GUI in SSMS and refresh the database list to see if any of them say (Restoring) or (Suspect).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply