TDP 7.1.1 SQL Rstore of AlwaysOn database

odhammar

ADSM.ORG Member
Joined
Jul 3, 2003
Messages
16
Reaction score
0
Points
0
Website
http
PREDATAR Control23

Have tried to restore a SQL alwayson database into a new database and relocating the files with no luck. I have tried both with /relocatedir and /relocate /to but TSM (tdpsqlc) tries to restore the files to the original folder??

Commands tested:
tdpsqlc restore xxxx /fromsqlserver=yyyy /querynode=alwayson /relocate=xxxx2 /to=d:\restore\xxxx2.mdf /into=xxxx2

or
tdpsqlc restore xxxx /fromsqlserver=yyyy /querynode=alwayson /relocatedir=d:\restore /into=xxxx2

Any with positive experience with a restore of a alwayson database in to a new database? It works to do the restore to original folder.
 
PREDATAR Control23

We have a long case open with IBM and they haven't so far got any closer a solution for us. But I have done a lot more research/testing on this and at least got me an answer when it doesn't work :)

When you have a SQL AlwaysOn cluster the secondary are normally not readable (= No need for licens) because it just used for replication and also normally that one you take the TDP backups on. It works to take backups on the secondary but it doesn't work to do a restore of the backup. If I change the secondary to readable everything works.
 
PREDATAR Control23

Update: IBM finally understood the problem and manage to reproduce the problem in their lab.

They have now created an report (APAR) and working on it.

Here's the APAR (Draft):
------------
Abstract:
Relocate restore operations may fail with ACO5436E and ACO5436E for AlwaysOn databases from non-readable secondary replicas.



Problem Description:
Tivoli Storage Manager for Databases: Data Protection for Microsoft
SQL Server is not able to perform a restore to alternate location of
an AlwaysOn database which was backed up using a non-readable
secondary replica.

The restore fails showing following messages:
ACO5436E A failure occurred on stripe number (0), rc = 428
ACO5436E The SQL server aborted the operation.
Restore of AAGDB failed.
An exception occurred while executing a Transact-SQL statement or
batch.
The file 'C:\Program Files\Microsoft
SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AAGDB.mdf' cannot be
overwritten. It is being used by database 'AAGDB'.

This problem only occurs if the backup of this database was taken
from a non-readable secondary replica.

Customer/L2 Diagnostics:
The problem is Data Protection for SQL Server cannot read the
file information of AlwaysOn databases from a non-readable
secondary replica.

A tdpsqlc query sql command will also return no file sizes for
these databases:

5. tdpsqlc query sql * /sqlserver=Non-readable-AlwaysOnInstance

SQL Server Name ........................ Non-readable-AlwaysOnInstance

SQL Database Name ........................ AAGDB
SQL Database AAG Name ........... ........ AAG
SQL Database Replica Role .. ............. Secondary
SQL Database Sync State Description ...... Synchronized
SQL Database Data Space Allocated ........ 0
SQL Database Data Space Used ............. 0
SQL Database Log Space Allocated ......... 0
SQL Database Log Space Used .............. 0
SQL Database Options .....................


Tivoli Storage Manager Versions Affected:
Tivoli Storage Manager for Databases Version 6.4 and 7.1:
Data Protection for Microsoft SQL Server




Initial Impact:
Medium




Additional Keywords:
TSM High Availability Primary AAG groups replication relocate position path directory


Local Fix:
The database can be restored into original location without any
problem.
Enable secondary replicas for readable access to prevent from this situation.
 
PREDATAR Control23

There is now an official APAR: IT08974, but it's not browsable on IBM site, could take up to 5 days.
 
PREDATAR Control23

Has anyone heard any updates on this? I'm still having problems restoring this to a new database server, we validate our backups and send a report and I have two always on clusters and failing to restore this to our test server. I've tried running backups on the primary server and I still get failures. I checked the IBM site, login and it shows the infamous oops, this page doesn't exist clicked here: http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1IT08974 makes me login and then the oops page.
 
PREDATAR Control23

I gets information mail from IBM about this once a month but still no solution, don't know what they are doing it's nearly 10 month now!!!
 
PREDATAR Control23

Apply fixing level when available. This fix is currently *
* projected to be available in: *
* Tivoli Storage FlashCopy Manager for Microsoft SQL Server *
* 4.1.4 *
* Tivoli Storage Manager for Databases: Data Protection for *
* Microsoft SQL Server 7.1.4
 
PREDATAR Control23

As per workaround description in http://www-01.ibm.com/support/docview.wss?uid=swg1IT08974 you can apply fixpack 7.1.3.1. Check your passport advantage if it is present there. If not ask IBM support to provide you custom fix - this should be on-demand created archive zith newer version of some files that you need to replace on server. Try to use some "clever" justification, i.e. need restore to pre-prod environment and at this point our whole deployment is freezed due to failing restore.
I had some troubles described by APAR and confirmed as program error, but fix was planned in 7.1.3 that was not out yet at that time and issue was not fixed by any patch level. IBM support provided me archive file with multiple files for my 7.1.2 installation, so I was get rid of the problem until 7.1.3 was out. After that I just updated TDP.
 
PREDATAR Control23

Just installed and tested 7.1.3.1 and I manage to do an restore, so ten (!) months later we have it up and running again.
 
PREDATAR Control23

I was a little bit to fast on the answer about it was solved, worked like I wrote on one server but not on next AlwaysOn cluster and the difference is that one doesn't work just have SQL 2014 CU3 and the one working SQL2014 CU7
 
Top