MS SQL Server OLAP - SSAS 2005 Backup and Recovery

Author

SSAS 2005 Backup and Recovery

Kyle Henly

2005-11-29, 7:23 am

I don't get it... why is there only the one option to backup a complete
database in SSAS?

In a VLDB SQL database you can do partitial backups, filegroup backups etc,
its all geared towards the management of a VLDB.

SSAS can also scale to quite some size and some managability has been built
in to the product, such as partitioning.

It strikes me as really odd, after all the effort to achieve scalability, it
all stops when it comes to SSAS backups.

If i have say 36 monthly fact tables and i'm only processing the most recent
month, why would i want to run a SSAS full database backup on what could
potentially be a huge database? Why would i want to backup nearly 3 years of
facts unnecessarily?

Is there another approach i could take to backing up a very large SSAS
database? More in line with the way VLDB's are backed up in SQL Server 2005?

What i'd like to do (i an ideal world) is take advantage of the split/mirror
technology of the SAN and put dorment SSAS partitions on different
mountpoints to one which are being processed.

Really struggling to get anywhere with this so help/advice would be most
appreciated!

Many thanks.
Kyle.

Denny Lee

2005-11-29, 8:24 pm

Hmm - could you use the Synchronization Utility to synchronize your primary
server with your secondary / staging server and use that as your backup
mechanism instead?

Another option is to copy the only the files you want from the Olap data
folder. In your example below, if you have 36 monthly fact tables, you
could design your cubes to have monthly Olap cube partitions. Within the
file system of the Olap data folder (e.g. c:\Program Files\Microsoft SQL
Server\MSSQL.2\Olap\Data), there is: Database folder > Cube folder > Cube
Partition folders. And you could file copy backup just those new folders
and any other files that may have changed. Note this particular option is
not an officially supported method to backup dBs (I had used it quite a bit
in AS2k but have yet to try it out in AS2k5).

--
HTH!
Denny Lee
< dennyglee_at_hotmail
_dot_com>

Blog at:: http://spaces.msn.com/members/denster/

"Kyle Henly" <Kyle Henly@discussions.microsoft.com> wrote in message
news:D8CF7E44-A80F-4DE6-B11C- C2A82AB37D2D@microso
ft.com...
>I don't get it... why is there only the one option to backup a complete
> database in SSAS?
>
> In a VLDB SQL database you can do partitial backups, filegroup backups
> etc,
> its all geared towards the management of a VLDB.
>
> SSAS can also scale to quite some size and some managability has been
> built
> in to the product, such as partitioning.
>
> It strikes me as really odd, after all the effort to achieve scalability,
> it
> all stops when it comes to SSAS backups.
>
> If i have say 36 monthly fact tables and i'm only processing the most
> recent
> month, why would i want to run a SSAS full database backup on what could
> potentially be a huge database? Why would i want to backup nearly 3 years
> of
> facts unnecessarily?
>
> Is there another approach i could take to backing up a very large SSAS
> database? More in line with the way VLDB's are backed up in SQL Server
> 2005?
>
> What i'd like to do (i an ideal world) is take advantage of the
> split/mirror
> technology of the SAN and put dorment SSAS partitions on different
> mountpoints to one which are being processed.
>
> Really struggling to get anywhere with this so help/advice would be most
> appreciated!
>
> Many thanks.
> Kyle.

Akshai Mirchandani [MS]

2005-11-29, 8:24 pm

Copying files is a little more difficult to do in AS 2005 because of the
versioning of data. And transactions cause more issues -- if processing is
occurring during the Copy operation, then you may copy different and
incorrect intermediate versions of objects.

This would be safer and cause no downtime for the primary server:
- Startup secondary server
- Sync database from primary to secondary server (only copies modified
data)
- Stop secondary server
- Copy modified files
- Backup modified files

Kyle, this is great feedback and we'll include it in planning for the next
version.

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Denny Lee" <dennyglee@hotmail.com> wrote in message
news:uqVzdPT9FHA.740@TK2MSFTNGP11.phx.gbl...
> Hmm - could you use the Synchronization Utility to synchronize your
> primary server with your secondary / staging server and use that as your
> backup mechanism instead?
>
> Another option is to copy the only the files you want from the Olap data
> folder. In your example below, if you have 36 monthly fact tables, you
> could design your cubes to have monthly Olap cube partitions. Within the
> file system of the Olap data folder (e.g. c:\Program Files\Microsoft SQL
> Server\MSSQL.2\Olap\Data), there is: Database folder > Cube folder > Cube
> Partition folders. And you could file copy backup just those new folders
> and any other files that may have changed. Note this particular option is
> not an officially supported method to backup dBs (I had used it quite a
> bit in AS2k but have yet to try it out in AS2k5).
>
> --
> HTH!
> Denny Lee
> < dennyglee_at_hotmail
_dot_com>
>
> Blog at:: http://spaces.msn.com/members/denster/
>
>
>
> "Kyle Henly" <Kyle Henly@discussions.microsoft.com> wrote in message
> news:D8CF7E44-A80F-4DE6-B11C- C2A82AB37D2D@microso
ft.com...
>
>

Kyle Henly

2005-11-30, 7:23 am

Hi folks, thanks for the comments.

Just one further question about the Syncronization. My understanding from
what Akshai said " - Sync database from primary to secondary server (only
copies modified
data)" is that the Syncronization only copies files that are changed. So:

1. Hows does the Sync process detect the changes (data and metadata) and:
2. How can i identify\log the files that were copied synchronized?

Sorry - that was 2 questions!

I feel it's particularly important to know this because we are going to use
Syncronization as a "put live" process so we need to be able to audit what
goes in to production. If someone messes up the source database and you copy
that error to production using a syncronization, you would ideally want
something trace-able that you could unwind or at least use to find the rogue
stuff that was put live.

I'm starting to think that perhaps a best practice would be to use profiler
to trace the syncronization?

Regards,
Kyle.

--
Kyle Henly
DBA
Nationwide Building Society
UK

"Kyle Henly" wrote:

> I don't get it... why is there only the one option to backup a complete
> database in SSAS?
>
> In a VLDB SQL database you can do partitial backups, filegroup backups etc,
> its all geared towards the management of a VLDB.
>
> SSAS can also scale to quite some size and some managability has been built
> in to the product, such as partitioning.
>
> It strikes me as really odd, after all the effort to achieve scalability, it
> all stops when it comes to SSAS backups.
>
> If i have say 36 monthly fact tables and i'm only processing the most recent
> month, why would i want to run a SSAS full database backup on what could
> potentially be a huge database? Why would i want to backup nearly 3 years of
> facts unnecessarily?
>
> Is there another approach i could take to backing up a very large SSAS
> database? More in line with the way VLDB's are backed up in SQL Server 2005?
>
> What i'd like to do (i an ideal world) is take advantage of the split/mirror
> technology of the SAN and put dorment SSAS partitions on different
> mountpoints to one which are being processed.
>
> Really struggling to get anywhere with this so help/advice would be most
> appreciated!
>
> Many thanks.
> Kyle.

Sponsored Links