This weekend I had quite a lot of activities related to a migration of Analysis Services 2000 from one machine to another. With the limited capabilities of AS 2000 and the approach we chose – backup/restore, I thought it will be a quite straight forward process. Well…it wasn’t. I had to discover so many different things that I even surprised myself. In this post I will try to summarize them, despite the fact that they might not ever get handy for you.
Number 1: Backup and restore for
The archival process of AS 2000 is quite easy and straight forward – you have the button within the context menu and the only thing you should configure is the path and filename of the backup file and the actual data location (the folder structure of the AS databasesm which is usually configured on instance level). Here we have several “gotchas”:
Remote backup – you can initiate a remote archival process (from a remote machine, having Analysis Manager installed). But within the data folder path you must be careful and enter the accessible network path of the AS 2000 instance, being source for the backup. Note that if you do not point it correctly you will not include the data in the backup.
Backup speed – it is just awful! First of all, all archives are compressed into a CAB files…and you cannot change that at all…you are just stuck with the archive. But this is not the problem…the problem is that the archival process is using only one logical CPU. Lame, uh? This whole stuff made the backup I was running to run for 5GBs/hour…what a speed!!!
Restore speed – as any other SQL, once you initiate restore, the process first must allocate the space before starts “unpacking” the data. And if your service account does not have the “Perform volume maintenance tasks” privilege enabled, you will have to wait quite a lot of time and for the restore. (Info on “Perform volume maintenance tasks” can be found here).
The backup does not start – well, yeah…I configure the backup, files, paths, the whole bunch of settings required, but at the end it is just not starting…I was checking the registry, installation versions, tried the command line, etc….it was just not starting….Then I went to check the settings of the AS instance. It seemed that the instance did not have default DATA and TEMP dir:
AS2000 lets you save such configuration. And does not really have an issue with it being empty. But if you run a backup, it is just not starting if you are running the archive locally on the server. For remote backup – it is just fine, because there you can point the UNC path to the DATA folder.
Error Log free AS 2000 – this part bugs me the most! It is just not very pleasant to have something not working/starting/failing, and not to have any detailed log to dig into and find the root cause. With AS2000 you just have to look around and test all kinds of possible solutions. I suppose you understand how risky is that. Good thing is that the engine does not really have that much options to play with :D
Well, for two days in a row we managed to migrate(move) en entire instance from one machine to another, but the 200GBs of data took us forever to backup and restore. And imagine, that some of the archives were corrupted – it was just a pain in the a** to have to start the archival process once again.
Oh, I forgot to mention one more thing – once the app team started testing, the processing of one of the databases was causing the entire AS instance to stop with the following error:
“The MSSQLServerOLAPService service terminated unexpectedly” and “There was a fatal error during transaction commit in the database <DB NAME>. Server will be restarted to preserve the data consistency”
I found some articles saying that a restore might help, tried it, failed once again…it seemed that the backup was corrupted. It was a 50+ GB database and if I have to start the archiving again it was another 10 hours of waiting and getting close to the deadline. So I took another approach – copied directly the files from the source server and overwritten the ones on the destination. The AS databases are holding all their structures and data within a single folder so it was a straight forward copy/paste job. And it was fine as the database was already restored and part of the AS metadata databases. Luckily it was also fine with the app team who managed to process the database.
Quite a lot of fun with archeological (:D) AS instances where I had to learn and observer so many things during a weekend. The good thing is that it almost made it up for the lack of sleep and frustration I had last two days :)