patternsqlMinor
Why is the backup sizes of AlwaysON replicas different?
Viewed 0 times
whythereplicassizesdifferentalwaysonbackup
Problem
We have SQL Server 2014 Always ON set up with a primary and just one secondary replica. I have been using Dell LiteSpeed to take Database backups (full & tlog) on secondary replica in order to off load that traffic from Primary. We had one developer truncated an important table so we had to restore the primary database from the full backup and tlog backups. I took a full database backup on primary first before restoring and noticed that it is just 34 GB as opposed to the backup on secondary which is almost 70 GB. After restore, people complaint that performance is slow. My question is that why the database backup on secondary replica is double the size of primary. TIA.
Solution
The size of the database is always exactly the same on both primary and secondary replicas. The difference you are noticing on the backup file size could be because one of the two reasons
-
You didn’t use compression on secondary and applied compression when you took the backup on primary server.
-
As you already mentioned you were using Dell Lite Speed, which has different algorithm and compression level which also make a difference in your backup file size.
The performance issue could be because of different reasons. After you restore the database, make sure to check and if necessary to update the statistics, Index fragmentation level, and the query plan cache which usually has significant performance influence after a database restore.
-
You didn’t use compression on secondary and applied compression when you took the backup on primary server.
-
As you already mentioned you were using Dell Lite Speed, which has different algorithm and compression level which also make a difference in your backup file size.
The performance issue could be because of different reasons. After you restore the database, make sure to check and if necessary to update the statistics, Index fragmentation level, and the query plan cache which usually has significant performance influence after a database restore.
Context
StackExchange Database Administrators Q#147650, answer score: 5
Revisions (0)
No revisions yet.