Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database and Backup size of SQL Server Analysis Services 2008

Posted on 2011-02-21
6
Medium Priority
?
1,164 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 1500 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

715 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