ms sql параметры сжатия backup database

Сжатие резервных копий (SQL Server)

Преимущества

Так как сжатая резервная копия занимает меньше места, чем распакованная резервная копия тех же данных, для сжатия резервной копии обычно требуется меньше операций ввода-вывода какого-либо устройства, что обычно существенно повышает скорость резервного копирования.

Ограничения

К сжатым резервным копиям применяются следующие ограничения.

В наборе носителей не допускается одновременное существование сжатых и несжатых резервных копий.

В предыдущих версиях SQL Server считывание сжатых резервных копий не поддерживается.

Воздействие сжатых резервных копий на производительность

По умолчанию сжатие существенно повышает загрузку ЦП. Дополнительная нагрузка на ЦП может помешать выполнению других операций. Поэтому может потребоваться создать сжатые резервные копии с низким приоритетом в сеансе, для которого использование ЦП ограничиваетсярегулятором ресурсов. Дополнительные сведения см. ниже в подразделе Использование регулятора ресурсов для ограничения загрузки ЦП при сжатии резервной копии (Transact-SQL).

В целях оптимизации производительности операций ввода-вывода резервной копии их можно изолировать от устройств или закрепить за устройствами в зависимости от оценки следующих видов счетчиков производительности.

Счетчики производительности операций ввода-вывода Windows, такие как счетчики физического использования дисков.

Счетчик Пропускная способность устройства, байт/с объекта SQLServer:Backup Device

Счетчик Пропускная способность резервного копирования и восстановления/с объекта SQLServer:Databases

Сведения о счетчиках Windows см. в справке Windows. Сведения о работе со счетчиками SQL Server см. в разделе Использование объектов SQL Server.

Вычисление коэффициента сжатия для сжатой резервной копии

Чтобы вычислить коэффициент сжатия резервной копии, используйте значения столбцов backup_size и compressed_backup_size из таблицы журнала backupset для резервной копии следующим образом:

backup_size:compressed_backup_size

Например, коэффициент сжатия 3:1 обозначает, что экономится около 66% места на диске. Чтобы запросить эти столбцы, можно выполнить следующую инструкцию Transact-SQL:

Коэффициент сжатия уже сжатой резервной копии зависит от подвергаемых сжатию данных. На полученный коэффициент сжатия оказывают влияние различные факторы. К основным факторам относятся следующие.

Символьные данные можно сжать сильнее, чем другие типы данных.

Согласованность данных в строках на странице.

Как правило, если несколько строк на странице содержат одно и то же значение в каком-либо поле, для этого значения можно добиться существенного сжатия. И наоборот, в базах данных, содержащих случайные значения или одну большую строку на странице, сжатые резервные копии будут практически такого же размера, что и несжатые резервные копии.

Зашифрованные данные подвергаются сжатию в значительно меньшей степени, чем незашифрованные. Например, если данные шифруются на уровне столбцов с помощью функции Always Encrypted или другого шифрования на уровне приложения, сжатие резервных копий может не дать существенного уменьшения размера.

Дополнительные сведения о сжатии баз данных, зашифрованных с использованием прозрачного шифрования данных (TDE), см. в разделе Сжатие резервных копий с использованием TDE.

Сжатие базы данных.

Если база данных уже сжата, сжатие резервных копий может лишь незначительно сократить размер или не сократить его вообще.

Сжатие резервных копий с использованием TDE

Начиная с SQL Server 2016 (13.x);, установка MAXTRANSFERSIZE на размер более 65536 (64 KB) включает оптимизированный алгоритм сжатия для баз данных с прозрачным шифрованием данных (TDE), который сначала расшифровывает страницу, сжимает ее, а затем зашифровывает снова. Если MAXTRANSFERSIZE не указан или при использовании MAXTRANSFERSIZE = 65536 (64 КБ) сжатие резервных копий в базах данных с включенным шифрованием TDE напрямую сжимает зашифрованные страницы и может не обеспечивать хорошее сжатие. Дополнительные сведения см. в разделе Сжатие резервных копий для баз данных с включенным шифрованием TDE.

Дополнительные сведения см. в разделе BACKUP (Transact-SQL).

Выделение пространства для файла резервной копии

При резервном копировании со сжатием конечный размер файла резервной копии зависит от степени сжимаемости исходных данных, которая становится известной только после окончания операции резервного копирования. Вследствие этого изначальный размер файла резервной копии, создаваемого компонентом Database Engine при резервном копировании базы данных со сжатием, определяется с помощью алгоритма предварительного выделения. Согласно данному алгоритму, размер создаваемого файла резервной копии соответствует стандартному проценту от размера базы данных. Если для операции резервного копирования необходимо больше пространства, компонент Database Engine увеличивает размер файла. Если конечный размер меньше выделенного пространства, в конце операции резервного копирования компонент Database Engine сжимает файл до фактического конечного размера резервной копии.

Флаг трассировки 3042 позволяет создать файл резервной копии минимального размера, с последующим увеличением при необходимости до конечного размера. При использовании флага трассировки 3042 операция резервного копирования не задействует алгоритм предварительного выделения пространства для резервной копии со сжатием. Этот флаг трассировки полезен для экономии пространства, так как выделяется только фактическое пространство, необходимое для резервной копии со сжатием. Однако, при использовании данного флага трассировки возможно небольшое уменьшение производительности (увеличение продолжительности операции резервного копирования).

Источник

SQL Server. Ускорения создания backup.

Начиная с SQL Server 2008 появилась возможность сжимать резервные копии при их создании. Изначально она была доступна только в Enterprise Edition, но начиная с SQL Server 2008 R2 её добавили и в Standart Edition. Я использую сжатие резервных копий практически всегда, она позволяет экономить свободное место и ускоряет процесс создания backup. Следует быть готовым к тому, что сжатие потребляет дополнительные ресурсы.

Следующим способом ускорить процесс создания резервной копии является деление backup на разные файлы и диски, то есть создание backup не из одного файла, а из множества. Это позволит выполнить создание резервных копий в несколько потоков. Данный метод работает только если у вас достаточно ресурсов (IO + CPU, если используем сжатие резервных копий).

Третьим способом является указание дополнительных параметров создания резервных копий.

Давайте начнём с презентации оборудования для тестирования — 96 ядер Unisys ES7000 model 7600R с 2 DSI Solid State Disk пропускная способность которых 6+ GB/sec IO. Для замера скорости создания резервных копий мы использовали счётчики Disk Read Bytes/sec и Disk Write Bytes/sec.

Сжатие резервных копий (Database Backup Compression):

По-умолчанию сжатие резервных копий отключено, но это поведение можно изменить через SQL Server Management Studio (SSMS) или через запрос. Через SSMS можно включить как глобально, так и указать опцию при создании backup. Для включении опции глобально необходимо нажать правой кнопкой мышки на экземпляре > Свойства > Database Settings > там выбрать галочку «Compress backup».

Для того, чтобы указать опцию только для конкретного backup, нужно: нажать правую кнопку мышки на БД > Tasks > Backup > Backup Option > Выбрать в «Set backup compression» опцию «Compress backup».

Чтобы включить сжатие для всех backup через скрипт, необходимо:

Источник

Сжатие базы данных

В этом разделе содержатся инструкции по сжатию базы данных в SQL Server с помощью объектов SQL Server Management Studio или Transact-SQL.

Сжатие файлов данных позволяет освободить неиспользуемое пространство путем перемещения страниц данных с конца файла в незанятое пространство ближе к началу файла. Когда в конце файла образуется достаточно свободного места, страницы данных в конце файла могут быть освобождены и возвращены в файловую систему.

В этом разделе

Перед началом работы

Сжатие базы данных с помощью:

Дальнейшие действия. Сжатие базы данных

Перед началом

Ограничения

Размер базы данных нельзя сделать меньше минимального размера базы данных. Минимальный размер — это первоначальный размер, заданный при создании базы данных, или последний размер, явно установленный операцией изменения размера файла (например, DBCC SHRINKFILE). Если, допустим, база данных была создана с размером 10 МБ и затем увеличилась до 100 МБ, ее можно сжать только до 10 МБ, даже если удалить из нее все данные.

Невозможно сжать базу данных во время создания ее резервной копии. И наоборот, нельзя создать резервную копию базы данных во время операции сжатия.

Рекомендации

Просмотр количества свободного (нераспределенного) пространства в базе данных. Дополнительные сведения см. в разделе Отображение данных и сведений о пространстве журнала для базы данных.

Обратите внимание на следующие сведения при планировании сжатия базы данных.

Наибольший эффект от операции сжатия достигается при ее применении после операции, создающей много неиспользуемого пространства, например после усечения таблицы или удаления таблицы.

Большинству баз данных требуется некоторое свободное пространство для выполнения обычных ежедневных операций. Если сжатие базы данных производится регулярно, но она снова увеличивается в размерах, это означает, что место, освобожденное при сжатии, необходимо для нормальной работы. В таких случаях повторное сжатие базы данных бессмысленно.

Операция сжатия не избавляет от фрагментации индексов в базе данных и обычно приводит к еще более сильной фрагментации. Это еще одна причина, по которой не стоит выполнять регулярное сжатие базы данных.

Не следует устанавливать параметр базы данных AUTO_SHRINK в значение ON без достаточных на то оснований.

безопасность

Permissions

Использование среды SQL Server Management Studio

Сжатие базы данных

В обозревателе объектов подключитесь к экземпляру компонента Компонент SQL Server Database Engineи разверните его.

Разверните узел Базы данных и щелкните правой кнопкой мыши базу данных, которую нужно сжать.

В меню наведите указатель на пункт Задачи, Сжать и выберите команду База данных.

База данных
Отображает имя выбранной базы данных.

Выделенное в данный момент место
Отображает суммарное используемое и неиспользуемое пространство для выбранной базы данных.

Доступное свободное место
Отображает суммарное свободное место для файлов журналов и данных в выбранной базе данных.

Реорганизовать файлы перед освобождением неиспользованного места
Установка данного флажка эквивалентна выполнению инструкции DBCC SHRINKDATABASE с заданием целевого процентного параметра. Снятие этого флажка равнозначно выполнению процедуры DBCC SHRINKDATABASE с параметром TRUNCATEONLY. По умолчанию при открытии диалогового окна этот флажок не установлен. Если этот флажок установлен, то пользователь должен задать целевое процентное значение.

Максимальное свободное пространство в файлах после сжатия
Введите максимальный процент свободного пространства, которое должно остаться в базе данных после ее сжатия. Допустимы значения от 0 до 99.

Нажмите кнопку ОК.

Использование Transact-SQL

Сжатие базы данных

Установите соединение с компонентом Компонент Database Engine.

На панели «Стандартная» нажмите Создать запрос.

Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере инструкция DBCC SHRINKDATABASE используется для уменьшения размера данных и файлов журнала в базе данных UserDB и для выделения 10 процентов свободного пространства в этой базе данных.

Дальнейшие действия. После сжатия базы данных

Данные, перемещаемые в процессе сжатия файла, могут быть разбросаны по любым доступным местам в файле. Это вызывает фрагментацию индекса и может увеличить время выполнения запросов, выполняющих поиск в диапазоне индекса. Чтобы устранить фрагментацию, предусмотрите возможность перестроения индексов файла после сжатия.

Источник

Сжатие данных

SQL Server, База данных SQL Azure и Управляемый экземпляр SQL Azure поддерживают сжатие строк и страниц для таблиц и индексов rowstore, а также поддерживают columnstore и архивное сжатие columnstore для таблиц и индексов columnstore.

Для таблиц и индексов rowstore используйте функцию сжатия данных, чтобы уменьшить размер базы данных. Помимо экономии места, сжатие данных позволяет повысить производительность при рабочих нагрузках с интенсивным вводом-выводом, поскольку данные хранятся в меньшем количестве страниц и в запросах требуется считывать меньше страниц с диска. Однако для сжатия и распаковки данных при обмене данными с приложениями требуются дополнительные ресурсы ЦП на сервере баз данных. Можно настроить сжатие строк и страниц для следующих объектов баз данных:

Для таблиц и индексов columnstore все таблицы и индексы columnstore всегда используют сжатие columnstore, и этот режим не настраивается пользователем. Используйте архивное сжатие columnstore, чтобы еще больше сократить размер данных в случаях, когда можно предоставить дополнительное время и ресурсы ЦП для хранения и извлечения данных. Можно настроить архивное сжатие columnstore для следующих объектов базы данных:

Рекомендации по сжатию строк и страниц

При использовании сжатия строк и страниц следует учитывать следующее.

Подробности сжатия данных могут меняться без предварительного уведомления в пакетах обновлений или в последующих версиях.

Сжатие доступно в База данных SQL Azure

Для системных таблиц сжатие недоступно.

С помощью сжатия можно хранить больше строк в странице, максимальный размер строки таблицы или индекса при этом не изменяется.

Данные вне строки не сжимаются при включении сжатия данных. Например, запись XML, размер которой превышает 8060 байт, будет использовать страницы вне строки, которые не сжимаются. — Сжатие данных не влияет на несколько типов данных. Дополнительные сведения см. в статье Влияние сжатия строк на хранение.

Если указан список секций, для каждой отдельной секции можно установить тип сжатия ROW, PAGE или NONE. Если список секций не был указан, для всех секций устанавливается свойство сжатия данных, указанное в инструкции. При создании индекса или таблицы для свойства сжатия устанавливается значение NONE, если не было указано другое значение. При изменении таблицы сохраняется существующее сжатие, если не было указано иное.

При указании списка секций или секции, выходящей за пределы диапазона, выдается ошибка.

Некластеризованные индексы не наследуют свойство сжатия таблицы. Чтобы сжать индексы, необходимо явно задать для них свойство сжатия. По умолчанию при создании индексов для них устанавливается режим сжатия NONE.

При создании кластеризованного индекса в куче кластеризованный индекс наследует состояние сжатия кучи, если не указано другое состояние сжатия.

Если для кучи было настроено сжатие уровня страницы, для страниц такое сжатие будет реализовываться только следующими методами.

Для новых страниц, размещенных в куче в процессе выполнения операций DML, сжатие страниц не будет использоваться до тех пор, пока куча не будет перестроена. Перестройте кучу. Для этого удалите и повторно примените сжатие либо создайте и удалите кластеризованный индекс.

Чтобы изменить параметры сжатия кучи, необходимо перестроить все некластеризованные индексы в таблице. Это обеспечивает наличие в них указателей на новые расположения в куче.

Включить или отключить сжатие типа ROW или PAGE можно в оперативном или режиме вне сети. Включение сжатия для кучи является однопоточным для операции в сети.

Чтобы включить или отключить сжатие строки или страницы, необходимо столько же места на диске, как и для создания или перестройки индекса. Для секционированных данных объем пространства, необходимый для включения или отключения сжатия, можно сократить, выполняя включение или отключение сжатия последовательно для каждой секции.

Чтобы определить состояние сжатия секций в секционированной таблице, выполните запрос столбца data_compression из представления каталога sys.partitions.

При сжатии индексов страницы конечного уровня можно сжать как сжатием строк, так и сжатием страниц. Для страниц, расположенных не на конечном уровне, нельзя использовать сжатие страниц.

Ввиду их размера, типы данных больших значений иногда хранятся отдельно от нормальных данных строк на страницах для особых целей. Сжатие данных недоступно для данных, хранящихся отдельно.

Таблицы, для которых в SQL Server 2005 (9.x) был реализован формат хранения vardecimal, сохраняют эту настройку и после обновления. К таблице, в которой присутствует формат хранения vardecimal, можно применить сжатие строк. Однако, поскольку сжатие строк является надмножеством формата хранения vardecimal, причин для сохранения данного формата нет. Для десятичных значений не происходит никакого дополнительного сжатия при сочетании формата хранения vardecimal и сжатия строк. Сжатие страниц можно применить к таблице, в которой присутствует формат хранения vardecimal, однако для столбцов данного формата дополнительного сжатия, скорее всего, не произойдет.

Все поддерживаемые версии SQL Server поддерживает формат хранения vardecimal, однако сжатие данных достигает тех же целей, поэтому данный формат является устаревшим. В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

Columnstore и архивное сжатие Columnstore

Таблицы и индексы Columnstore всегда сохраняются со сжатием columnstore. Можно еще более уменьшить размер данных columnstore, настроив дополнительное сжатие, именуемое архивным сжатием. Чтобы выполнить архивное сжатие, SQL Server применяет к данным алгоритм сжатия XPress корпорации Майкрософт. Добавьте или удалите архивное сжатие, используя следующие типы сжатия данных.

Чтобы добавить архивное сжатие, используйте инструкцию ALTER TABLE (Transact-SQL) или ALTER INDEX (Transact-SQL) с параметром REBUILD и DATA COMPRESSION = COLUMNSTORE_ARCHIVE.

Чтобы удалить архивное сжатие и восстановление данных для сжатия columnstилиe, используйте ALTER TABLE (Transact-SQL) или ALTER INDEX (Transact-SQL) с параметром REBUILD и DATA COMPRESSION = COLUMNSTORE.

В следующем примере устанавливается сжатие данных columnstore в некоторых секциях и архивное сжатие columnstore в других секциях.

Производительность

Сжатие индексов columnstore с использованием архивного сжатия приводит к увеличению времени обработки индекса по сравнению с индексами columnstore, к которым архивное сжатие не применяется. Используйте архивное сжатие только при наличии дополнительного времени и ресурсов ЦП для сжатия и получения данных.

При архивном сжатии удается уменьшить объем хранилища, что полезно для данных, доступ к которым выполняется нечасто. Например, если имеется секция для данных за каждый месяц и основная часть активности пришлась на последние месяцы, то можно архивировать предыдущие месяцы, чтобы уменьшить требования к хранилищу.

Метаданные

Следующие системные представления содержат сведения о сжатии данных для кластеризованных индексов.

Процедура sp_estimate_data_compression_savings (Transact-SQL) также может применяться к индексам columnstore.

Влияние на секционированные таблицы и индексы

При использовании сжатия данных в секционированных таблицах или индексах следует учитывать следующее.

При разбиении секций с помощью инструкции ALTER PARTITION обе секции наследуют атрибут сжатия данных исходной секции.

При слиянии двух секций результирующая секция унаследует атрибут сжатия данных секции назначения.

Для переключения секции свойство сжатия данных секции должно совпадать со свойством сжатия таблицы.

Существуют две вариации синтаксиса, которые можно использовать для изменения сжатия секционированной таблицы или индекса.

Следующий синтаксис перестраивает только упоминаемую секцию:

Следующий синтаксис перестраивает всю таблицу, используя существующий режим сжатия для всех неупоминаемых секций:

При удалении кластеризованного индекса соответствующие секции кучи сохраняют настройки сжатия данных, если только не была изменена схема секционирования. Если схема секционирования подверглась изменениям, все секции перестраиваются в распакованное состояние. Чтобы удалить кластеризованный индекс и изменить схему секционирования, необходимо выполнить следующие шаги.

Удаление кластеризованного индекса в режиме вне сети происходит очень быстро, поскольку удаляются только верхние уровни кластеризованных индексов. При удалении кластеризованного индекса в режиме в сети SQL Server должен перестроить кучу два раза — один для первого шага, один для второго.

Влияние сжатия на репликацию

При использовании сжатия данных с репликацией следует учитывать следующее.

В следующей таблице показываются настройки репликации, управляющие сжатием в процессе репликации.

Влияние на другие компоненты SQL Server

Сжатие происходит в подсистеме хранилища, и данные предоставляются большинству других компонентов SQL Server в распакованном состоянии. Это ограничивает влияние сжатия на другие компоненты следующим.

Источник

Настройка сжатия резервных копий (SQL Server)

При установке сжатие резервных копий по умолчанию отключено. По умолчанию способ сжатия резервных копий определяется параметром конфигурации backup compression default уровня сервера. Однако настройку по умолчанию на уровне сервера можно переопределить при создании отдельной резервной копии или во время планирования ряда регулярных операций резервного копирования. Чтобы изменить это параметр, см. раздел Параметр конфигурации сервера «Просмотр или настройка параметра сжатия резервных копий по умолчанию».

Переопределение сжатия резервных копий по умолчанию

Способ сжатия резервных копий вы можете изменить для отдельной резервной копии, задания резервного копирования или для конфигурации доставки журналов.

Transact-SQL

Для переопределения настроек сжатия резервных копий по умолчанию используйте параметр WITH NO_COMPRESSION или WITH COMPRESSION в инструкции BACKUP в процессе создания резервной копии.

Для конфигурации доставки журналов поведение сжатия резервных копий можно контролировать с помощью хранимой процедуры sp_add_log_shipping_primary_databasesp_change_log_shipping_primary_database (Transact-SQL).

SQL Server Management Studio

Сведения о просмотре или настройке параметра сжатия резервных копий по умолчанию для экземпляра SQL Server см. в разделе Параметр конфигурации сервера «Просмотр или настройка параметра сжатия резервных копий по умолчанию».

Можно переопределить сжатие резервных копий по умолчанию для сервера, выбрав Сжимать резервные копии или Не сжимать резервные копии в одном из следующих диалоговых окон:

При резервном копировании базы данных можно управлять сжатием резервной копии для отдельной базы данных, файла или журнала.

Мастер планов обслуживания позволяет управлять сжатием резервных копий для каждого запланированного набора полных или разностных резервных копий базы данных или журнала.

Сжатием резервных копий можно управлять при создании пакета для резервного копирования отдельной базы данных или нескольких баз данных.

Можно управлять поведением сжатия резервных копий журналов.

Источник