Unsolved
This post is more than 5 years old
144 Posts
1
3812
false alarm error on SQL mirror backup with NMSQL 5.2.2
We are backing up SQL 2008 mirror system. We backup both nodes. A DB can be active on node A for some time, and then move over to node B, thus become mirrored to node A
Now, our problem is that if a db is in mirror mode on node A, and that SQL server is backed up with saveset MSSQL: the backup will fail. But all the db's that are not in mirror mode are backed up fine. The result is, that this SQL server always flags as backups are failing, but the backups are not failing.
client and server run NetWorker 7.6.1.3
NMSQL 5.2.2
In the work log in nsr\logs\sg we get this message if a DB is in mirror mode:
"
Can not backup a mirror database in a connected mirror session. Please backup the principal database
Database %s is a mirror partner and is inaccessible. Back up of %s will be skipped. 2 0 10 TestMirror 0 10 TestMirror
"
The backup detects that the mirror db is a mirror database. So I don't understand why it has to fail the backup. Of course we configure NetWorker to backup Node B also, and that node also fails the backup for the same reason because of the mirror db's node B contains from node A.
We have followed the documentation and configured the credentials both in services.msc on the nodes as well as in remote user in the client resource.
The work log acknowledges the logon of the user and the user is SA admin and Domain admin in the domain.
Is there a way to backup both nodes of a SQL 2008 mirror system without the group / backup ending with failed? Please help.
Regards,Johannes
ble1
14.3K Posts
0
April 1st, 2011 01:00
I never seen mirror database backup ignored by SQL (in terms of error reporting) so I would assume with NMSQL this is not possible, but it may be nice RFE.
ble1
14.3K Posts
1
April 3rd, 2011 15:00
By default when you run BACKUP DATABASE on db which is mirrored you will always get error message. Perhaps EMC could make change to code to ignore this and report is as warning, but SQL interface does report this as error so under VDI standard I'm not sure if EMC would break rules of standard here. Since VDI will get replaced by VSS I guess such change may happen only in NMM (I have no idea how VSS approach handles mirrored database, might be different from VDI - I really have no clue). What I do is to define MSSQL$INSTANCE:DB rather then MSSQL: and I backup all my database individually. We use mirroring on storage level and failover concept is based on VMotion. I think we only used mirrored db concept while migrating them from old solution to new one. In older version of SQL you could use sp_databases, but not sure if that works under 2008 (nor does it state in what state database is). I guess SQL admin could write simple SQL script that would fill input file a bit before you schedule it from server from which you would call it.
joka2
144 Posts
0
April 3rd, 2011 15:00
Do you know if other backup vendors have the same issue?
Would it be possible for EMC to make it possible for the backup to just detect that a db is in mirror mode (restore mode) and continue and finish without saying it failed?
I'm now working on using the -l option with nsrsqlsv that reads db's from a text file. I just need to find out how I can tell MSSQL to create the text file.
Johannes
joka2
144 Posts
0
April 6th, 2011 04:00
I regret to say, that our DBA has deceided not to use NetWorker to backup SQL 2008 mirror system. He says it's unacceptable how EMC is supporting backing up SQL 2008 mirror. The DBA is there talking about that it's not possible to rely on NetWorker reporting about failed backups of SQL 2008 mirror since NetWorker lets a mirrored db failed the group.
In a way I understand this decicion. It should be very easy for EMC to fix this in cooperation with Microsoft.
Regards,
Johannes
ble1
14.3K Posts
0
April 6th, 2011 09:00
http://msdn.microsoft.com/en-us/library/ms186865.aspx
So, if you trigger database of such database, it will fail as SQL API will return failure.
The only thing EMC engineering would do is to check for error core/message and if it indicates mirror database to ignore it. As this is not current design, as it assume you do not backup mirrored database, then what module does is correct.
JanHoekstra
1 Message
0
August 24th, 2011 01:00
Still waiting for the answer of the EMC engineering?
joka2
144 Posts
0
August 24th, 2011 02:00
I would accept such a solution, whereas a list of active databases is created and used with NMSQL.
How can I create such a list?
regards,
Johannes
ble1
14.3K Posts
0
August 24th, 2011 02:00
Solution should not be provided by NW as far as I see it. SQL module will either backup whole instance as defined in savseset list or particular database. If any of those is not in state to backup, it will result in error code. Simple as that. If you wish to backup each time only what is ready for backup without any monitoring/logging then you can run script where you list databases which are ready for backup and you call those with -I.
joka2
144 Posts
0
August 24th, 2011 02:00
We dont have a NetWorker solution for this issue yet.
All I want is just let the NMSQL module only backup databases that are online and in a state to be backed up at the time the backup starts.
Johannes
ble1
14.3K Posts
0
August 24th, 2011 03:00
You will probably need to some VBS to achieve that.
joka2
144 Posts
0
August 26th, 2011 06:00
Does anyone here have a SQL script for .bat or powershell or any other script type, that creates a list of databases on a MSSQL server that are in a state to be backed up?
I'm sure many other admins have the same issue as I have with backing up MSSQL mirror.
Johannes
ble1
14.3K Posts
0
September 2nd, 2011 03:00
Your SQL admin should know this - just ask him. I'm not SQL admin, but search engines today can make you superman if you want. Following link should get you going (from there it is small step towards full goal):