[SSAS]SSAS operations Part1 - moxuemeivip/Microsoft-Analysis-Service-Or-Fabric GitHub Wiki
SSAS administrative operations: backup/restore, synchronize, attach/detach, switch between modes ReadOnly/ReadWrite.
backup/restore
Backup and Restore of Analysis Services Databases
--Scenarios
you can **recover **a database and its objects from a particular point in time. Backup and restore is also a valid technique for migrating databases to upgraded servers, moving databases between servers, or deploying a database to a production server. For the purposes of data recovery, if you do not already have a backup plan and your data is valuable, you should design and implement a plan as soon as possible.
--Invovled resources
For a full backup that includes source data, you have to back up the database which contains detail data. Specifically, if you are using ROLAP or DirectQuery database storage, detail data is stored in an external SQL Server relational database that is distinct from the Analysis Services database. Otherwise, if all objects are tabular or multidimensional, the Analysis Services backup will include both the metadata and source data.
--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. Tabular databases can only be restored to Analysis Services running in tabular mode. Multidimensional databases require an instance running in multidimensional mode.
--Step by step instructions
How to Backup Analysis Services Database in SSMS
How to Automate Backup of Analysis Services Database Using SQL Server Agent Job
Synchronize
Synchronize Analysis Services Databases
--Scenarios
Deploy a database from a staging server onto a production server.
Update a database on a production server with the changes made to the data and metadata in a database on a staging server.
**Generate XMLA script **that can be run in the future to synchronize the databases.
In distributed workloads where cubes and dimensions are processed on multiple servers, use database synchronization to merge the changes into a single database.
--Involved resources
Synchronization feature can make two SQL Server Analysis Services databases equivalent by copying the data and metadata a database on a source server to a database on a destination server.
--Prerequisites
On the **destination **(or target) server from which you initiate database synchronization, you must be a member of the Analysis Services server administrator role. On the source server, your Windows user account must have Full Control permissions on the source database.
**TCP port 2383 must be open **on both servers to allow remote connections between default instances.
Both the source and destination servers must be the same version and service pack.The edition of each installation must support database synchronization.
--Steps
attach/detach
Attach and Detach Analysis Services Databases
--Scenarios
There are often situations when an SQL Server Analysis Services database administrator (dba) wants to take a database offline for a period, and then bring that database back online on the same server instance, or on a different one. These situations are often driven by business needs, such as moving the database to a different disk for better performance, gaining room for database growth, or to upgrade a product. For all those cases and more, the Attach and Detach commands enable the SQL Server Analysis Services dba to take the database offline and bring it back online with little effort.
Attach and Detach commands The Attach command enables you to bring online a database that was taken offline. You can attach the database to the original server instance, or to another instance. When you attach a database the user can specify the ReadWriteMode setting for the database. The **Detach **command enables you to take offline a database from the server.
The Attach command is used to bring online an existing database structure. If the database is attached in **ReadWrite **mode, it can be attached only one time to a server instance. However, if the database is attached in **ReadOnly **mode, it can be attached multiple times to different server instances. However, the same database cannot be attached more than one time to the same server instance.
--Involved resources
The database to SSAS instance
--Prerequisites
Server or database administrator privileges are required to execute the Detach command.
Server administrator privileges are required to execute the Attach command.