This months T-SQL Tuesday is hosted by Kenneth Fisher(b|l|t) and the invitation is found following this link.
T-SQL Tuesday was started by Adam Machanic (b|t), and this is the SQL Server community’s monthly blog party, where everyone is invited to write about a single common topic. This time, the topic is Backup (and Recovery).
Admitted, I was a bit challenged this past week as I decided to update my WordPress to a newest version 4.7. This however caused the admin module of my site to become unavailable. After creating a support ticket with Unoeuro.com I was guided to a blog post by Vashistha Pathak (b|t) which in the end led me to the solution, and I fixed it without the need of a backup. Phew… Kind of nerve wrecking not knowing if my site was backed up – fortunately I wouldn’t have lost a single character, according to Unoeuro support. That was quite reassuring and somehow coincidentally very fitting for this months topic, no?
My approach for this blog post is to accept Kenneth’s challenge from this tweet, where Kenneth triggered a thought stream using the word keys:
@vestergaardj *nod* I’m hoping to get a really broad spectrum. Everything from basic DB backup, to keys, to BI.
— Kenneth Fisher (@sqlstudent144) December 6, 2016
I immediately came to think of SSRS encryption key backup and I knew by then I had to try to cover all basics on BI solutions, this being SSAS, SSIS and SSRS as well. But honestly, I haven’t been able to set aside enough time to cover all. So without further ado, here is SSAS Backup Galore (the others will come in good time):
SSAS (MD)
For Analysis Services databases there are two (2) fairly straight forward options. However, you may want to discuss up front, how much effort you want to spend setting this up. Consider you already have a process for deploying updated cubes into production; In which scenarios would it be alright for the business to wait for a normal deploy, for the numbers to come back online?
In Brent Ozar’s (b|l|t) First Responder Kit there is a document where IT and Business need to state how quickly they would like to recover from different categories of “down time” i.e. Corruption, “Oops” Queries and so on. That’s actually still a good exercise to do, with both sides of the table, even though the document operates at a higher level.
Prerequisites
- You must have administrative permissions on the Analysis Services instance or Full Control (Administrator) permissions on the database you are backing up.
- Restore location must be an Analysis Services instance that is the same version, or a newer version, as the instance from which the backup was taken.
- Restore location must be the same server type.
- SSAS does not provide options for differential or partial backups of any kind. Nor log backups (should you be wondering in from the DBA world).
- A SSAS backup contains only one (1) database, so no option to create backup sets.
- SSAS can only have one fixed backup location at a time; It’s configurable, but not meant to be changed for each database.
GUI
We begin by connecting to the server where the database resides. Via SQL Server management Studio (SSMS) you then expand the Node Databases.
In my example here, I will be using a Database named ‘Test Database’.
When right-clicking on the selected database, we get the option to Back Up.
This kicks off a wizard where we need to select a few options for our backup.
First of all, we can edit or designate the name of the backup file. One thing to note with regards to backups on SSAS is that only one designated backup folder can be active at a time. In the server options dialog, it’s possible to change the destination for backups, but only after adding the new folder location to the attribute AllowedBrowsingFolders, which is considered an advanced setting, so remember to tick that option in order to set the new location. Also, you need to do this by opening the dialog twice; 1st add the new folder to AllowedBrowsingFolders, close and reopen. 2nd change the BackupDir to the newly added folder.
My settings are just the standard ones, yours should differ in the sense that you should put your Data, Backup and Log on different disks for optimal performance.
Well, this is why you only get to type a name for the file in the next dialog and not the complete path and file name. You also get to select, if the backup can overwrite an existing file of same name. Tick if you would allow this.
The two other options I will cover in this post are Apply Compression and Encrypt backup file.
I have not met a setup where applying compression was not an option, yet. Obviously this has a penalty cost on CPU while executing the backup, and will affect the rest of the tasks running on the server (even if you have your data and backup dir on different drives). But in my experience, the impact is negligible.
This may not be the case with the encryption option, as this has a much larger foot print on the server. You should be using this with some caution in production. Test on smaller subsets of the data if in doubt.
Another thing to keep in mind, as always when dealing with encryption, do remember the password. There is no way of retrieving the data other than with the proper password.
In my example I allow file overwrite and apply compression.
When we clicking the OK button, the backup will execute and you will be able to track the progress in the lower left corner of the dialog. The dialog will close after successful completion. You have the option to stop the backup action from the progress dialog by clicking Stop action now.
After completion the file should be sitting on disk, where you designated it.
XMLA
Same options as mentioned above in the GUI section are available when doing your backups via XMLA.
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Test Database</DatabaseID>
</Object>
<File>Test Database.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<Password>9sse6</Password>
</Backup>
Connection to the server, start an XMLA query and fire away.
To leave out Password, just leave out the entire tag. Vice versa goes for Compression which you have to specify with False, in order to not have your backup compressed. In the above example, the backup is compressed.
Often I have seen this used within a SQL Agent job, that support SSAS Commands, just as the one above. Look for the SQL Server Analysis Services Command Step Type.
Obviously setting this up in a SQL Agent job is only half the job. After the backup has been done and/or scheduled, you need to ensure that the backup files produced are stored in a secure and available location.
PowerShell
Via the PowerShell cmdlet Backup-ASDatabase you can execute the same backup as described above. In fact this is a perfect example of why you should learn PowerShell, if your not already on that band wagon. Simply put, it doesn’t get easier than this. In my example, I will create a backup of all the databases on a server, in a couple of lines of code – Behold PowerShell AwesomeSauce (PSAS)
Import-Module sqlps -DisableNameChecking sqlserver: | Out-Null cd sqlas |Out-Null cd <server>\<namedinstance>\databases dir | Backup-ASDatabase
Remember to exchange your own values for <server> and if present <namedinstance>.
This will backup all databases on the given server.
Obviously you can extend this script with all the parameters exposed by the cmdlet. For more details, see this page.
Analysis Services Management Objects
The final method I will be covering is via Analysis Services Management Objects or AMO for short. AMO exposes, as PowerShell, a great deal of methods, where backup is one.
In order to use AMO to back up an SSAS database we need to get an create or load instance of said database through the object model in AMO. This can be done fairly easy.
The AMO classes/functionality comes with the Microsoft.AnalysisServices.dll which is installed with the product. In order to utilize it, we need to load it first or reference it, if you are doing a proper .Net project in SQL Server Data Tools or Visual Studio.
One thing to note before we go to the example is that the Backup method in the Database object has several implementations. Depending on your parameters, you can call different implementations of the same method. According to Technet, these are your options:
| Backup(String) | Provides programmatic access to Backup operations for a Database with file parameter. |
| Backup(BackupInfo) | Provides programmatic access to Backup operations for a Database with BackupInfo parameter. |
| Backup(String, Boolean) | Provides programmatic access to Backup operations for a Database with file and AllowOverWrite parameters. |
| Backup(String, Boolean, Boolean) | Provides programmatic access to Backup operations for a Database with file, AllowOverWrite, and BackupRemotePartitions parameters. |
| Backup(String, Boolean, Boolean, BackupLocation[]) | Provides programmatic access to backup operations for a Database with file, AllowOverWrite, BackupRemotePartitions, and locations parameters. |
| Backup(String, Boolean, Boolean, BackupLocation[], Boolean) | Provides programmatic access to backup operations for a Database with five parameters. |
| Backup(String, Boolean, Boolean, BackupLocation[], Boolean, String) | Provides programmatic access to Backup operations for a Database with six parameters. |
I will show how to invoke the AMO functionality from <drum roll please…> PowerShell.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$Server = New-Object Microsoft.AnalysisServices.Server
$Server.connect(".\SQL2016DEV")
$BackupInfo = New-Object Microsoft.AnalysisServices.BackupInfo
$BackupInfo.File = 'Test AMO Backup.abf'
$BackupInfo.AllowOverwrite = $true
$BackupInfo.ApplyCompression = $true
$BackupInfo.Password = '9sse6'
foreach( $Database in $Server.Databases ) {
$Database.Backup( $BackupInfo );
}
You should note that only via the method that takes a BackupInfo class as parameter are you able to specify a password.
Final thoughts
I think I have covered all possible ways of executing an Analysis Services backup – If I somehow missed and option, please let me know!
But as you can see, ways to back up your SSAS database are abundant and very flexible.
You should, as with any backup, on a regular basis restore and test them on a separate environment, to ensure that you can actually recover when needed.
Depending on data update frequency this can however be rather a complex setup.
