It can be operationally challenging to deploy a new AS database version without cube downtime. Typically, if you deploy a new version of an AS database, the database will be offline until the database has completed a full process. This might be a long time in an environment where the large measure groups are very large and are only incrementally processed (on a daily, hourly, minute etc. schedule), since the full process may take many hours. This can be a problem in SQL Server OLAP as we have been operationally used to a cube being available 24 by 7, since SQL Server 7.0.One workaround is to deploy a new version of the database with a slightly different name, for example the existing database might be Perfmon and the new version might be Perfmon_V2. In this way,
the new database can be deployed and processing while the existing database is online and available. The trick comes when the new database has completed processing. The old database could be deleted and the new database renamed to the original name. This has a slight snag, since when you rename a database, the databaseID remains the same, and the DatbaseID is used by SSIS in AS processing tasks. So you would need to change all the SSIS packages that refer to the database. This would be confusing to manage since the database names would be out of sync with the databaseIDs. The extra trick is to back up the new AS database after processing and restore it over the original database. When you restore an AS database, the database name and databaseID are in sync.Hence, for an AS database that takes a long time to fully process, to deploy a new database version follow these steps.
- Deploy the new AS database with a suffix of _V2
- Fully process the new AS database
- Backup the new AS database
- Restore the AS database over the original database (Allow Database Overwrite)
The downtime is limited to the duration of the restore operation.
Note, the original AS database could continue with incremental processing during the new database full process, although some consideration needs to be given to the way you manage high water marks in the measure groups. This workaround is suitable for SQL Server 2005 and SQL Server 2008.
For realtime online OLAP and data mining demonstrations see http://RichardLees.com.au/Sites/Demonstrations
3 comments:
the problem would be that the old connections to the original database would be lost, in the new AS database, so something might need to considered for their timeout and/or reconnection.
Here are some other articles on SSAS Processing: http://ssas-wiki.com/w/Articles#Processing
I have created a Connect Item in hope that Microsoft will add the ability for administrators to update the SSAS database ID (not just the name):
https://connect.microsoft.com/SQLServer/feedback/details/1134252
Post a Comment