January 28, 2022 at 12:36 pm
Hi fellow DB colleagues
In our company we explore ways to store SSAS multidimensional databases on fileshares instead of dedicated drives of servers. We use virtual servers.
So I am testing the possibility to deploy SSAS databases on a fileshare. I run my test on a virtual server and a fileshare that are located in the same data center.
In my test, I take a list of SSAS abf backup files and try to restore them one by one to the share. I just execute this xmla:
<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>\\BACKUP FILESHARE\SSAS DB BACKUP.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<DatabaseName>SSAS DB test restore fileshare</DatabaseName>
<DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">\\SSAS FILESHARE\SSAS\</DbStorageLocation>
</Restore>
The query starts running, but sooner or later (sometimes after a few seconds, sometimes after 30 minutes) it fails. The file system error from SSAS looks like this:
<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
<Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />
<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
<Error ErrorCode="-1056833535" Description="Dateisystemfehler: Fehler beim Öffnen der Datei
'\\SSAS FILESHARE\SSAS\AF0AA8F34B484B3E8991\SSAS DB.db\Some dimension.16.dim\Some tiny file.sstore'." Source="Microsoft SQL Server 2016 Analysis Services" HelpFile="" />
<Error ErrorCode="-1055129594" Description="Server: Der aktuelle Vorgang wurde aufgrund eines Fehlers in einem anderen Vorgang in der Transaktion abgebrochen." Source="Microsoft SQL Server 2016 Analysis Services" HelpFile="" />
</Messages>
</root>
</return>
The error is in German, but basically it means: File system error: The following error occurred while opening the file '\\SSAS FILESHARE\SSAS\AF0AA8F34B484B3E8991\SSAS DB.db\Some dimension.16.dim\Some tiny file.sstore': The system cannot find the path specified.
Server: The current operation was cancelled because another operation in the transaction failed.
When I restore the same SSAS backup file to a local drive of the server, it completes with no error.
I had hypotheses:
Do you have any ideas?
January 28, 2022 at 10:17 pm
Is it always failing on the same file? If so, then it is likely something with that file and the restore. If it is sometimes failing on that file and sometimes succeeding, then it is probably something else.
That being said, the 255 character limit could still be the problem. The filesystem could be different resulting in different limitations. FAT has different limitations than FAT32 which has different limitations than NTFS which has different limitations than exFAT. And once you throw network file shares in, is that a SAMBA share or some other format? Just because it works on an NTFS local disk doesn't mean it will work from a SAMBA share (for example).
As for the network, that is where I would be looking. I would talk to the networking team to get them to monitor bandwidth usage during that window. If they have any tools to throttle the bandwidth when they notice spikes, that could be the problem. Alternately, you could use perfmon to watch the network bandwidth during the restore and see if it ever has a sudden drop. And the windows event log is always a good place to look for errors.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 31, 2022 at 11:48 am
I have looked further into the error and saw that it always stops on the same file, where path length is 288 characters.
That being said, the 255 character limit could still be the problem.
Therefore, the path length above 255 characters is my main suspicion. We will try to map a network drive and see if SSAS can work with that "local-looking" path.
Still puzzling though, that there are local paths of SSAS files that are well above 255 characters in length, and these work fine.
January 31, 2022 at 1:40 pm
We ran a few more tests trying to restore an SSAS backup to a fileshare. The XMLA command failed every time with the "File system error" and same file path that was 288 characters long.
I have also checked with our network folks, and connectivity does not seem to be the bottleneck: between the server and storage there is a 10GB channel, and both are located in the same datacenter.
I also found out that there is no possibility to use something as "Map network drive" with SSAS, because behind the scenes Windows will make the storage calls as with a UNC path.
Which leaves me with the following facts:
So, I have no possibility to deploy my existing SSAS DB backups to a fileshare, unless I also reduce the path lengths (which is not possible because the paths of SSAS DB files are defined by the DB logic, such as names of partitions, dimensions, attribute names, etc. - and if I change the names, I will have to update the downstream reports with the new names, etc.) 🤷♀️
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply