1 Reply Latest reply: Jan 12, 2017 3:06 AM by Nick Dyer RSS

    Several questions regarding SQL Server and compression

    James Yang Wayfarer
    Visibility: Open to anyone

      Good morning!

       

      As you can see from my thread title, I have several questions about Nimble and SQL Server with compression/dedupe.

       

      I am setting up my SQL Server 2016 volumes:

      1. I am using the SQL Server 2012 performance policy (I'm assuming that performance policy is best suited for SQL Server 2016) for my data volumes
      2. I am using the SQL Server Logs performance policy for my log volumes
      3. Which performance policy would you recommend using for a volume with system databases (excluding TempDB)?
      4. Which performance policy would you recommend using for SQL Server backups volume?
      5. Which performance policy would you recommend using for a drive letter mount volume?
      6. Which performance policy would you recommend using for a MSDTC volume?
      7. Is it recommended to use compression and dedupe on SQL volumes?

       

      Also, I am using SQL Server's row data compression on most of my tables and SQL Server's backup compression.

      1. Will Nimble's compression/dedupe be affected by this?
      2. Is it recommended to turn off SQL Server's native compression?
      3. Is Nimble's compression/dedupe better than SQL Server's compression?
      4. I assume the AF5000's controllers are more than able to handle this additional load?

       

      I'm sorry for bombarding you with all of these questions.  I truly would appreciate it if someone could please answer them!

       

      Thank you!

        • Re: Several questions regarding SQL Server and compression
          Nick Dyer Pioneer

          Hello James,

           

          There's quite a few threads with regards to SQL and some of your questions on the forums already - worth a search i'd say.

           

          In short; if you compress within SQL you are not going to see any data reduction on the array from compression; and you'll be burning CPU cycles within the AF5000 to do so. Therefore i'd switch off compression at the SQL layer and let the array do it's job. It's certainly comparable - if not better, because it allows for lesser CPU overhead within the application and allows for cheaper SQL licensing (if that was a requirement).

           

          You'll want to use the standard SQL Server policy (which is 8K block aligned, Compression = ON) for SQL DBs. You could also use this for TempDB if you wanted to, however I would create a custom one called TempDB and set it to 4K just in case. A policy is designed to tell NimbleOS what block alignment and compression settings should be set on an associated volume.

           

          Hope this helps.