Sychronised Mirrored SQL servers/databases

[original PM]

Hoping I can get some help from you clever techy guys here

I have a sql server (2008) holding a number of databases - and as a dr/backup solution it is mirrored and sychronized -- which is great.

However being the perfectionist I am I want to be able to check the mirror peridoically and ensure that the data is indeed kept up to date and therefore will be available if needed.

However our techy guys tell me I cannot see the data on the other server - can anyone offer any advice on this situation?

I do not want to call them a bunch of lying toads but I cannot see that MS would offer a solution of this type which does not allow a check to make sure it is working.

Otherwise the first time you find out it is not working is when you try to use it -- which is a tad late I would imagine.

Cheers!

[eek]

Depending on the mode you are using you can't do much.

Mirroring States explains the mirroring states.

Do you have witness servers monitoring the systems?

[PAH]

Surely each database is backed up on their respective server as a measure against individual server problems.

Get a copy of both backups (preferably taken as close together as possible), restore to a test server, and do a full database compare including data to ensure they are in sync.

Allow a slight discrepancy for when the backups were done and any data pending syncing at that point.

If the above is not possible then I'd guess your setup is not 'optimal'.

[rsingh]

Create a snapshot against the mirror and query the snapshot.

[fullyautomatix]

It is true, there is no easy way to dynamically query a mirror. I think the mirror works in way that every query executed against the main database is executed against the mirror. So allowing a query against the mirror might break the logic of maintaining data integrity. What should happen if you execute a query which modifies the data in the mirror ?

[Spacecadet]

It is true, there is no easy way to dynamically query a mirror. I think the mirror works in way that every query executed against the main database is executed against the mirror. So allowing a query against the mirror might break the logic of maintaining data integrity. What should happen if you execute a query which modifies the data in the mirror ?

i.e it breaks ACID compliance

[original PM]

useful info all.

Many thanks