Post reply

Is it good to enable database compression at server instance?

  • June 2, 2014 at 2:44 pm

    #286037

    I think all my database backups can be run in compression mode, so decide to change it at server instance level.

    Is is a good practice, or should I do it at whereever I need to execute backup.

    THanks

  • June 2, 2014 at 10:17 pm

    #1718403

    As long as you have the right version, absolutely.

  • June 3, 2014 at 9:48 am

    #1718584

    Thanks I just wonder if it is good to setup compression at server level or in the indivual backup like maintenance plan or T-SQL backup?

  • June 4, 2014 at 12:11 pm

    #1718949

    I would also look at database size, CPU load and potentially using the Resource Governor to keep normal SQL Server performance levels within acceptable ranges.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • June 4, 2014 at 12:32 pm

    #1718965

    sqlfriends (6/3/2014)

    Thanks I just wonder if it is good to setup compression at server level or in the indivual backup like maintenance plan or T-SQL backup?

    I generally enable compression at the server scope. Reasons I won't do that relate to whether TDE is enabled for a database or not.

    As for the MP v. TSQL Backup, I'd get rid of the MP versions and use TSQL to do the backups.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 4, 2014 at 12:38 pm

    #1718969

    Thanks, got it.

    We are here not enable TDE, also read from online:

    Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended

  • June 4, 2014 at 1:18 pm

    #1718978

    If / when you upgrade to SQL 2014, you can do encrypted compressed backups. It is a little different than TDE and is a good thing. Keep that in mind for the future.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply