4

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.

5

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

  1. You didn’t use compression on secondary and applied compression when you took the backup on primary server.

  2. 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.

  • 2
    Thank you for the insight. It looks like database on the secondary replica is little different from the database on the primary replica due to row versioning in secondaries. In my case the database backup on secondary replica is almost the double the size of primary replica. I wonder is it even a good practice to only take database backups on secondary but not on primary? – Satish Aug 24 '16 at 17:33
  • 2
    Your database on secondary replica is the same exact copy of your primary node. As I stated earlier, that could be the only cause of your backup size difference or the database might not be part of the availability group databases or it is not synchronized. Taking a backup only from secondary does not affect it, but it will lowers the additional backup burden of your primary node. However, rather than making it backup on Secondary only, I would favor to make the backup Prefer secondary and increase the backup priority value – Hailegziabher Dechassa Aug 25 '16 at 13:35
  • 1
    LiteSpeed uses dynamic compression. It'll automatically adjust compression based on CPU, read speed, and write speed. You can easily see different compression ratios on different servers that way. – Brent Ozar Sep 6 '16 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.