UNSOLVED
Error during federated backup
Simple setup:
- two nodes (eg. phy1, phy2)
- AG with with DBs
- backups configured over vir
- clients defined:
- phy1 for fs backup and SQL backup of system DBs (MSSQL:) - this is secondary
- phy2 for fs backup and SQL backup of system DBs (MSSQL:) - this is primary
- vir for SQL backup of AG instance (MSSQL#AG)
Backup preference for AG is Secondary when available. When backup is started it fails with:
43708:(pid 6628):Start time: Wed Apr 22 00:06:13 2015
43621:(pid 6628):Computer Name: PHY2 User Name: SYSTEM
29446:(pid 6628):Local host has following SQL Server online:
37977:(pid 6628): MSSQL server : PHY2
29085:(pid 6628):Microsoft SQL Server Provider error:
38006:(pid 6628):Login failed for user 'DOMAIN\PHY2$'..
66212:(pid 6628):Cannot login to SQL Server PHY1.
29085:(pid 6628):Microsoft SQL Server Provider error:
38006:(pid 6628):Conversion failed when converting from a character string to uniqueidentifier..
43709:(pid 6628):Stop time: Wed Apr 22 00:06:19 2015
It seems to suggest that access to secondary replica over (from) first node is not authorized (never seen this DOMAIN\HOST$ user syntax before), but I'm also a bit puzzled by MS error about conversion failed... Anyone seen this before and perhaps any hint where to look next? Our SQL admin was away today so could not chat with him so if anyone has hints - feel free This is SQL2012 using NMSAP 8.2SP1.
1242
0
Posted September 4th, 2015 03:00
I didn't get to the bottom of this, but I suspect for this to work I need to create AD user and use that user for backup instead. I was on longer vacation so I left this as it is. Not sure how others are doing it so perhaps someone who already did it can share some details.
1242
0
Posted September 4th, 2015 05:00
thanks
I search many resource and suspect that we need disable "UAC" then everything is OK
1243
0
Posted September 4th, 2015 10:00
UAC handles user logins (actually, it handles permissions for execution) at OS level; this is error with login at SQL error so I think it is not related.
1243
0
Posted September 5th, 2015 04:00
buddy
I just want to backup MSSQL on primary node at always on enviroument, I try time but failed
maybe it really UAC problem
1243
0
Posted September 5th, 2015 06:00
The error I showed was from secondary node replica - not primary one. Which error do you get?
1243
0
Posted September 5th, 2015 23:00
Neither primary one or secondary one .
I did not success on both of them
How do you do backup it successful on primary node ?
1243
0
Posted September 6th, 2015 01:00
I define save set and run backup - nothing special there. Once again, what is the error you get?
1243
0
Posted September 6th, 2015 08:00
dear
my enviroument is two active node & one passive node just for fail over (not read only)..
I make the save set as
MSSQL:MESP1
then I get error
3708:(pid 6184):Start time: Mon Aug 24 16:17:44 2015
43621:(pid 6184):Computer Name: LCHDMESPNB41 User Name: SYSTEM
97546:(pid 6184):Back up of MESP will be excluded as it is part of Availability group. It can be backed up using SQL federated workflow.
29211:(pid 6184):At least 1 valid backup target is required
43709:(pid 6184):Stop time: Mon Aug 24 16:19:48 2015
Unable to find any full backups of the save set 'LCHDMESPNB41:MSSQL:MESP' in the media database. Performing a full backup.
LCHDMESPNB41:MSSQL:MESP: retried 1 times.
453
0
Posted September 6th, 2015 08:00
Please check VDI SQL admin guide - you will see that for AG backup, you must use slightly different name of save set (where with # you specify AG name).
liulei_it
2 Intern
•
3209 Posts
1242
0
Posted September 3rd, 2015 20:00
hi, buddy
what about this case ?