Solved

Database and Backup size of SQL Server Analysis Services 2008

Posted on 2011-02-21
6
1,033 Views
Last Modified: 2016-02-14
Can someone tell me the approximate ratio between:

SSAS 2008 database size: SSAS 2008 backup size without compression
SSAS 2008 database size: SSAS 2008 backup size with compression

I know that it all depends on the how much data present in the database and how much full the database is. I just need the approximate values assuming that the SSAS database has the complete data in its allocated space.
0
Comment
Question by:Srinivas_Vengala
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:John Claes
Comment Utility
Source :
http://www.mssqltips.com/tip.asp?tip=2025


Normal backup with no data compression  :
the backup size is just about the same size as the database since the backup is not a compressed backup.

Compressed backup with no data compression  :
the backup size is reduced to a third of the previous backup, which is common in the case of compressed backups in SQL Server 2008.

Normal backup with row-level compression
The size of this backup file (performed in this step) is smaller than the normal backup file of the same database that we performed in Step 1. The main reason the backup file size is reduced is the decrease in the number of data pages required to store the table due to row level compression applied on the table.

Compressed backup with row-level compression
After successfully taking the backup, the below image displays the file size of the new backup along with the old ones.  As you can see we got a little more compression.

Normal backup with page-level compression
The size of this backup file (performed in this step) is drastically reduced when compared to normal backup file size of the same database that we performed in step 1. The main reason the backup file size is reduced drastically is because page level compression reduces the number of data pages required to store the table more than with row level compression.   So from this we can see that page level compression get better compression than row level compression.

Compressed backup with page-level compression
After this backup and comparing backup file sizes with those of the previous backups, backup compression does not significantly compress the backups when the database is already enabled with page level data compression.



Conclusion
Backup compression reduces the backup size by approximately 30% when compared to a regular non-compressed backup of the same database when page level compression is enabled. When row level compression is enabled, it reduces somewhat better, approximately 60 %.

Some points to be remembered
    * The results shown in this article might differ with another database with same size due to differences in the datatypes of the columns and the data occupancy in the records.
    * When page level compression is enabled on one table and row level compression is enabled on another table, the backup compression will be proportional in size with respect to the amount of data that exists in those tables.
    * On a partitioned table, one partition can be compressed using row level compression and another using page level compression, i.e both types of compression can be enabled on a single table.
0
 

Author Comment

by:Srinivas_Vengala
Comment Utility
poor_beggar: I am asking for SQL Server Analysis Services (SSAS) not for SQL Server Database Services/Engine.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
I don't know if our data follows the general rules, but I just did a test backup of our SSAS database with the "compressed" option set both ways, and it came out:

Uncompressed: 119MB
Compressed: 54MB

So it looks like compression would save us about 55% of the space. Again, not sure if that's typical, or how the size of our databases compares to yours, but there's real-world. Though honestly, between 55MB and 120MB, there's not effectively much difference.

Also, it's worth noting that the uncompressed backup was smalled once I put it in a ZIP file - 40MB vs 43MB (for the compressed one), so if you're going to be zipping the results, you're better off with the uncompressed backup.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Srinivas_Vengala
Comment Utility
ryanmccauley: can you tell me the size of the SSAS database you have backed up?

When I backed up the sample SSAS database 'Adventure Works DW' which is of 53MB, it has taken 62.7MB for uncompressed and 32.9MB for compressed backups. Just wondering why the uncompressed backup has taken more size than the actual database size.

First thing, do you see the estimated size in the SSAS database properties to check the size of an SSAS database?
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
Comment Utility
It shows that my database size (in properties) is 115MB, so you're right - the uncompressed backup is slightly larger. It does say "Estimated size", which seems odd, but maybe it's somehow only able to ballpark the size of the all the objects, and it doesn't know for sure until you try to back things up.

53MB -> 63MB seems a bit extreme - to have a backup that's 20% larger than the file - but it seems to be less of an issue if you compress it or zip the uncompressed backup. Also, if that's really just an "estimated" number (and the backup includes database objects and properties that aren't part of the "database size" number), then perhaps it makes more sense.

How large a database are you talking about here for your instance? At these sizes, it's not much in the big scheme of things, unless you're worried about saving a few dozen MB. We have years worth of data in our SSAS database, and while our list of facts isn't extensive, it's large enough that I'd never worry about our SSAS database getting over a few GB, and it's still dwarfed by the 75-100GB transaction databases that these cubes are built from.
0
 

Author Closing Comment

by:Srinivas_Vengala
Comment Utility
OK
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now