Solved

Database and Backup size of SQL Server Analysis Services 2008

Posted on 2011-02-21
6
1,132 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:John Claes
ID: 34943038
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
ID: 34943157
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
ID: 34944880
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:Srinivas_Vengala
ID: 34950121
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
ID: 34951953
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
ID: 35189912
OK
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

627 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