Backup Basics for SQL Server Analysis Services

If you ask DBAs about the backup strategy for their relational databases, you’ll likely get fairly long answers. If you ask the same question about SQL Server Analysis Services (SSAS) databases, you might get blank stares. Traditionally, SSAS hasn’t had the same set of retention and disaster recovery requirements as its relational counterpart. But as the adoption and mission-critical nature of SSAS databases continue to increase, DBAs have to consider their backup options for this data, too.

Related: Many-To-Many Currency Conversions in Microsoft's SQL Server Analysis Services

SSAS backups are fairly straightforward if you know about the structure of SSAS databases and the backup basics, including scheduling options and performance considerations. Unless specifically noted, the concepts and features discussed here apply to SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005 across all editions that include SSAS (Standard, Enterprise, and Developer, plus Datacenter in SQL Server 2008 R2).

SSAS Database Structure

Like its relational counterpart, an SSAS backup operation creates a backup file for each database. An SSAS database is made up of several components:

Conceptually, an SSAS backup file contains all these components. In practice, however, the storage mode used by the database objects affects what’s in the database and, therefore, the contents of the backup file. Specifically, the storage mode of the dimensions and measure group partitions determine what’s in the SSAS database. Multidimensional OLAP (MOLAP) partitions and dimensions create a copy of the source system data and store it along with any aggregations in the SSAS database. In Relational OLAP (ROLAP) storage, the source data and aggregations are stored in the source system. Hybrid OLAP (HOLAP) storage keeps the source data in the source system but stores aggregations in the SSAS database. To determine the storage mode that’s being used, you can look at the storage mode property for each measure group partition and dimension.

During a backup, SSAS doesn’t query the source system for additional data. So, if you back up a database containing a cube that has ROLAP partitions, the SSAS backup doesn’t back up any of the fact data or aggregations. Therefore, the source system must be backed up separately when you’re using ROLAP or HOLAP storage. Table 1 summarizes the storage modes and resulting contents of an SSAS backup file.

Besides the source system, you might need to back up other SSAS components separately:

Backup Basics

SSAS backups can be performed manually through SQL Server Management Studio (SSMS). After you connect to an SSAS instance, right-click a database and click Back Up to open the Backup Database window, which is shown in Figure 1. An SSAS backup file has a default filename extension of .abf, and it’s stored in the Microsoft SQL Serverinstance_nameOLAPBackup folder.

Figure 1: Backup Database window in SSMS

Unlike a relational database backup file, an SSAS backup file can contain only one backup. In other words, you can’t create backup sets. If you want to retain multiple backups, you must create multiple files. Also, SSAS doesn’t provide options for differential backups or log file backups.

By default, two check boxes are selected in the Backup Database window: Apply compression and Encrypt backup file. When the Apply compression option is selected, SSAS compresses the backup file, which saves disk space at the expense of higher CPU utilization during the backup operation. When the Encrypt backup file option is selected, SSAS accepts a user-supplied password, which it uses to encrypt the backup file. Note that, unlike the password for a SQL Server relational database backup set, this password prevents the reading of the backup data by other means. If you choose to encrypt SSAS backups, make sure that you keep a record of the password in a safe and separate location. For strong password considerations, see the “Back Up Password Protection” section of the SQL Server Books Online (BOL) topic "Backing Up and Restoring an Analysis Services Database."

Encryption isn’t very CPU intensive, nor does it increase the size of the backup or appear to add any significant time to the backup operation. In terms of size, Figure 2 shows the results of my testing by using two sample databases. Because SSAS already compresses most of the data in a database, you typically won’t see large compression ratios.

Figure 2: Sample backup results showing database compression

If the SSAS database uses remote partitions, you can back them up by selecting the Backup remote partition(s) check box in the Backup Database window and completing the Remote partition backup location section. For information about completing that section, see the BOL topic "Backup Database Dialog Box."

Scheduling Options

The easiest way to schedule a backup is to use the Backup Database window that’s shown in Figure 1. After you configure the backup, click the Script icon at the top of the window instead of the OK button. SSAS will automatically create an XML for Analysis (XMLA) script in a new query window, as shown in Figure 3. Note that XMLA’s Backup command supports an optional Security property that isn’t exposed in the Backup Database window. This property determines whether the backup file contains security definitions, such as roles and permissions, together with the associated Windows user accounts and groups. Excluding security definitions can be useful if you want to back up a database and restore it to a different environment, in which you intend to apply new security settings.

Figure 3: Sample XMLA script

All SSAS client applications, including SSMS and Business Intelligence Development Studio (BIDS), use the XMLA protocol to communicate with SSAS. For this reason, you can run the script shown in Figure 3 in SSMS. You can also run this script using a SQL Server Agent job, thereby automating the backup operation. Figure 4 shows a SQL Server Agent job step that executes an SSAS command.

Figure 4: SQL Server Agent job step that executes an SSAS command

Besides using SQL Server Agent, you have a few other options for automating backup operations:

Performance Considerations

Creating a backup file is an I/O intensive operation. You can improve performance by writing the backup to a high-speed disk drive. You can also put the backup file on a separate drive to avoid having read operations (from users) collide with the backup’s write operations. In terms of concurrency, the backup operation puts a read lock on the database. This lock doesn’t prevent users from querying the database or processing operations from executing. However, processing operations can’t be committed until the backup is finished.

In the days of SQL Server 2000, an SSAS backup was limited in size by the underlying .cab file technology that was used for archiving operations. The backup architecture for SQL Server 2005 is an improvement, but large databases can still cause trouble. Although there’s no specific documented size threshold, backup times in SSAS 2005 will increase exponentially when you work with very large databases. As a rule of thumb, if your SSAS 2005 database is larger than 20GB, carefully test and monitor your backups. If the backups are taking an inordinately long time to finish, consider one of the alternatives discussed in the “Alternative Backup Strategies” section. SQL Server 2008 doesn’t suffer from this problem. As stated in the “SQL Server 2008 Analysis Services” white paper, “…with SQL Server 2008 Analysis Services a new backup storage subsystem results in backup times that increase linearly with database size.” 

Database Restoration

Similar to a backup, you can restore an SSAS database manually through SSMS or through automation by using XMLA, ASSL, or AMO. As Figure 5 shows, when you restore a database from a backup file, you have the option of either overwriting the existing database or creating a new one. If you don’t enter a storage location, the restore operation uses the default storage location for the SSAS instance. All security information is restored by default; choosing the Skip Membership option restores the database roles but not the user accounts that are associated with the roles. In the Partitions page of the Restore Database window, you have the option of changing the restoration folder for each partition that’s being restored.

Figure 5: Options in the Restore Database window

Alternative Backup Strategies

When you use MOLAP storage, an SSAS database contains a copy of the data from a source system. If this source system is already being backed up, a standard SSAS backup might be redundant. If you use SQL Server 2005, very large SSAS databases might not be good candidates for standard backups. Fortunately, there are alternative backup strategies that you can use instead of, or in conjunction with, standard backups.

BIDS. The design and ongoing development of an SSAS database is done through BIDS. Specifically, the definition of an SSAS database is spread across several XML-based files that are grouped together in a Visual Studio (VS) project. Similar to other VS projects, these files can be saved to a version control system such as Team Foundation Server. If your primary goal is to be able to re-create an SSAS database in the event of hardware failure, and if you know that the source system is also backed up, this might be the only strategy that you have to implement. You must completely reprocess the SSAS database after you re-create it using this approach.

Scripting. In SSMS, you can right-click an SSAS database and click Script Database to create an XMLA script that can be executed later to re-create the SSAS database. This approach also requires that you reprocess the SSAS database. Alternatively, you can use the AMO object model to build and later execute the script.

Raw backup. You can back up the entire SSAS instance data folder. As discussed in the “Backup Strategies with SQL Server 2005 Analysis Services” white paper, there are a few factors to consider if you use this approach:

Database synchronization. If you have to maintain a warm standby server, you might want

to consider database synchronization. You can run this process manually using the Synchronize Database Wizard in SSAS or through an XMLA script. Loosely analogous to relational log shipping, the synchronization process scans for differences between two databases on two servers and transfers only the files that have been modified. According to the SQL Server Customer Advisory Team, synchronization performance has greatly improved in SSAS 2008 (see “Analysis Services Synchronization Best Practices”).

Detach/Attach. SQL Server 2008 lets you detach an SSAS database from an instance and attach it to another instance.

Strategy for Success

SSAS backups are fairly straightforward, although there are considerations when you work with large databases in SQL Server 2005. Depending on your goals for database recovery and high availability, you might end up using a combination of standard backups and one or more alternative approaches to complete the job.