Что нового в ms sql server 2012

MS SQL Server 2012 представляет собой значительное обновление по сравнению с предыдущими версиями, внедряя ключевые функции, ориентированные на повышение производительности и упрощение администрирования. Среди них выделяется AlwaysOn Availability Groups, обеспечивающая высокую доступность и отказоустойчивость баз данных без необходимости настройки сложных кластеров Windows Server Failover Clustering.

Встроенные средства Columnstore Index позволяют ускорить обработку аналитических запросов в десятки раз за счет сжатия данных и оптимизации чтения больших объемов информации. Рекомендуется использовать Columnstore для отчетных таблиц, где критична скорость агрегации и выборки больших наборов данных.

Data-tier Applications (DAC) упрощают развертывание и миграцию приложений, позволяя переносить структуру базы данных и объекты вместе с конфигурацией. Для разработчиков это обеспечивает единый процесс упаковки и установки базы на различных средах без ручной настройки.

SQL Server 2012 также расширяет возможности T-SQL, включая функции SEQUENCE для генерации последовательностей чисел и новые оконные функции LEAD и LAG для анализа данных без сложных подзапросов. Эти инструменты упрощают подготовку отчетности и повышают читаемость запросов.

Функция FileTable интегрирует файловую систему Windows с базой данных, обеспечивая работу с неструктурированными данными через T-SQL. Это позволяет централизованно хранить документы и одновременно использовать стандартные средства поиска и управления SQL Server.

Использование Columnstore Index для ускорения аналитических запросов

Columnstore Index в MS SQL Server 2012 хранит данные по столбцам, а не по строкам, что снижает объем считываемой информации при выполнении агрегатных и аналитических запросов. В тестах Microsoft, использование Columnstore Index для таблицы размером 100 млн строк показало сокращение времени выполнения запросов SUM, COUNT и AVG в 10–20 раз по сравнению с традиционными clustered index.

Для создания Columnstore Index используется команда CREATE COLUMNSTORE INDEX. Ограничение SQL Server 2012 заключается в том, что индекс поддерживает только чтение: вставка, обновление и удаление данных в таблице с Columnstore Index невозможны до следующей версии. Поэтому его оптимально применять для фактов в хранилищах данных и исторических таблиц.

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

Рекомендации: ограничивать обновления таблиц с Columnstore Index, периодически перестраивать индекс после массовых загрузок данных с помощью ALTER INDEX REBUILD, и комбинировать Columnstore Index с фильтрованными или обычными B-tree индексами для обеспечения быстрого доступа к небольшим выборкам.

Настройка AlwaysOn Availability Groups для высокой доступности

AlwaysOn Availability Groups в MS SQL Server 2012 обеспечивают отказоустойчивость на уровне баз данных без полной репликации экземпляра сервера. Для их использования требуется включение функции AlwaysOn в SQL Server Configuration Manager на каждом узле кластера и наличие WSFC (Windows Server Failover Cluster).

Процесс настройки начинается с подготовки базы данных: все базы должны находиться в полном режиме восстановления. Для каждой базы необходимо создать резервную копию и восстановить её на всех вторичных репликах с опцией NORECOVERY.

Далее создается группа доступности через SQL Server Management Studio или T-SQL. Важно указать первичную реплику, список вторичных реплик и режим синхронизации: синхронный для локальных реплик с критически важными данными и асинхронный для удаленных реплик. Каждой реплике задается роль автоматического или ручного переключения.

Для балансировки нагрузки рекомендуется использовать read-only маршрутизацию. Это позволяет перенаправлять запросы на вторичные реплики, снижая нагрузку на первичную. Маршрутизация настраивается через свойства Availability Group Listener, указывая конкретные реплики и их приоритет.

Мониторинг AlwaysOn реализуется через представления динамического управления (DMV), такие как sys.dm_hadr_availability_replica_states и sys.dm_hadr_database_replica_states. Они показывают статус реплик, задержку синхронизации и текущие роли, что позволяет своевременно обнаруживать отклонения и предотвращать потерю доступности.

Рекомендовано автоматизировать тестовые переключения (failover) на вторичные реплики для проверки готовности системы. Скрипты T-SQL для ручного failover позволяют контролировать процесс без остановки приложений, а плановые тесты снижают риск ошибок при реальных аварийных ситуациях.

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

AlwaysOn Availability Groups в SQL Server 2012 позволяют реализовать отказоустойчивые решения без стороннего ПО, но требуют строгого соблюдения порядка настройки реплик, мониторинга и тестирования failover для обеспечения высокой доступности данных.

Внедрение Sequence объектов для управления уникальными идентификаторами

В MS SQL Server 2012 введены объекты SEQUENCE, предназначенные для генерации последовательных чисел, что позволяет управлять уникальными идентификаторами без привязки к конкретной таблице. Sequence объекты создаются отдельно от таблиц и могут использоваться одновременно в нескольких таблицах, повышая гибкость архитектуры базы данных.

Создание Sequence осуществляется командой CREATE SEQUENCE с указанием начального значения, шага и максимального значения:

Для получения следующего значения используется функция NEXT VALUE FOR:

Рекомендации по использованию Sequence:

• Использовать CACHE для повышения производительности при массовой вставке данных, например CREATE SEQUENCE dbo.OrderSeq START WITH 1000 INCREMENT BY 1 CACHE 50;.
• Применять NO CYCLE для предотвращения повторного использования идентификаторов.
• Использовать Sequence для нескольких таблиц, когда требуется унификация идентификаторов без зависимости от конкретной таблицы.
• Контролировать прогресс Sequence с помощью ALTER SEQUENCE ... RESTART WITH для сброса значения при необходимости.

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

Применение FileTable для хранения и работы с документами в базе данных

FileTable в MS SQL Server 2012 расширяет возможности FILESTREAM, позволяя интегрировать файловую систему NTFS с базой данных. Документы, сохранённые в FileTable, доступны через стандартные файловые пути, при этом все операции транзакционно контролируются SQL Server. Это обеспечивает согласованность данных и возможность отката изменений.

Для создания FileTable необходимо включить FILESTREAM на сервере, а затем создать базу данных с поддержкой FILESTREAM. После этого создаётся таблица с опцией AS FileTable, где автоматически создаются системные столбцы для хранения пути файла, имени и типа содержимого. Таблицы FileTable поддерживают стандартные операции SELECT, INSERT, UPDATE и DELETE, позволяя выполнять запросы к метаданным документов, не открывая сам файл.

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

При работе с FileTable рекомендуется: создавать отдельные таблицы для различных типов документов, использовать индексы на столбцах file_stream и name для ускорения поиска, контролировать права доступа через SQL Server и NTFS одновременно, а также планировать резервное копирование с учётом FILESTREAM, чтобы обеспечить целостность данных.

Интеграция FileTable с SQL Server позволяет реализовать гибридную стратегию хранения: документы физически находятся в файловой системе, но управляются средствами реляционной базы, что упрощает аудит, обеспечивает транзакционную безопасность и повышает производительность приложений, работающих с большими объёмами файлов.

Оптимизация работы с большими данными с помощью Data Compression

Типы сжатия:

• ROW Compression – оптимизация хранения фиксированных типов данных, уменьшает размер строк за счет эффективного кодирования числовых и символьных типов. Рекомендуется для таблиц с большим количеством числовых колонок и коротких строк.
• PAGE Compression – использует алгоритмы ROW Compression и дополнительное сжатие на уровне страниц, включая prefix и dictionary сжатие. Наиболее эффективно для таблиц с повторяющимися значениями или длинными строками.

Практические рекомендации по внедрению:

1. Провести анализ таблиц с помощью Dynamic Management Views (DMV), например sys.dm_db_index_physical_stats, для выявления больших таблиц и индексов с высокой степенью повторяемости данных.
2. Тестировать сжатие на отдельной копии таблицы, используя команду ALTER TABLE [TableName] REBUILD WITH (DATA_COMPRESSION = ROW | PAGE), чтобы оценить влияние на размер и производительность.
3. Отслеживать влияние на CPU: PAGE Compression снижает I/O, но увеличивает нагрузку на процессор при выборках и вставках. Оптимально комбинировать с индексированными представлениями и partitioning для распределения нагрузки.
4. Использовать sp_estimate_data_compression_savings для оценки потенциальной экономии места перед применением сжатия на продуктивной базе.
5. Регулярно пересматривать стратегию сжатия при росте данных и изменении паттернов доступа, чтобы избежать деградации производительности.

Data Compression в SQL Server 2012 позволяет экономить до 70% места на диске при больших таблицах и уменьшать время резервного копирования и восстановления. Оптимизация с помощью ROW и PAGE Compression должна быть адаптирована под типы данных, повторяемость значений и нагрузку на CPU.

Использование Contained Databases для упрощения миграции баз

Contained Databases в MS SQL Server 2012 позволяют минимизировать зависимость базы данных от экземпляра сервера, что упрощает перенос и миграцию баз между серверами. Основная цель – хранение учетных записей пользователей непосредственно в базе, а не на уровне сервера.

Ключевые особенности и рекомендации при использовании Contained Databases:

• Хранение пользователей на уровне базы: Контейнированные базы поддерживают SQL-пользователей с паролями, что устраняет необходимость создавать логины на каждом сервере при миграции.
• Упрощение перемещения баз: Базы можно переносить между экземплярами SQL Server с минимальными изменениями, используя BACKUP/RESTORE или Detach/Attach.
• Минимизация ошибок доступа: Снижает вероятность ошибок типа «orphaned users», возникающих при переносе обычных баз, поскольку все учетные данные остаются внутри базы.
• Поддержка идентификационных функций: Contained Databases совместимы с функциями аутентификации SQL Server и Windows, позволяя создавать смешанные режимы аутентификации для пользователей базы.

Практические рекомендации по внедрению:

1. Перед миграцией перевести существующую базу в контейнерную с помощью команды:

   ALTER DATABASE [ИмяБазы] SET CONTAINMENT = PARTIAL;

2. Создавать новых пользователей базы через CREATE USER [Имя] WITH PASSWORD = 'Пароль';, избегая логинов на уровне сервера.
3. Проверять наличие связей с внешними серверами или сервисами, так как Contained Database ограничивает использование некоторых внешних объектов.
4. Тестировать перенос на отдельной тестовой среде для проверки корректности всех зависимостей и прав доступа.

Использование Contained Databases существенно ускоряет процесс миграции, уменьшает административные задачи и повышает переносимость баз данных между разными экземплярами SQL Server.

Возможности управления ресурсами через Resource Governor

Resource Governor в MS SQL Server 2012 позволяет контролировать распределение процессорного времени и памяти между различными группами рабочих нагрузок. Он работает на уровне экземпляра сервера и обеспечивает предсказуемое поведение при одновременном выполнении тяжелых запросов.

Для настройки Resource Governor создаются три ключевых объекта: группы ресурсов (Resource Pools), классификаторы (Workload Groups) и функции классификации (Classifier Functions). Группы ресурсов определяют лимиты CPU и памяти, которые могут использоваться, например, максимальное процентное потребление процессора или максимальный объем памяти для выполнения запросов. Классы рабочих нагрузок привязываются к группам ресурсов и определяют приоритет обработки запросов.

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

Resource Governor поддерживает динамическое изменение лимитов без перезапуска сервера. Например, можно увеличить максимальный процент использования CPU для фоновой группы в ночное время или ограничить память для аналитических запросов в часы пик. Практика показывает, что точная настройка лимитов CPU и памяти для каждой группы снижает время ожидания блокировок на 20–40% в многопользовательских средах.

Мониторинг работы Resource Governor осуществляется через DMV: sys.dm_resource_governor_workload_groups и sys.dm_resource_governor_resource_pools. Эти представления позволяют отслеживать текущее использование CPU, памяти и количество запросов, что помогает корректировать настройки в реальном времени.

Для комплексного управления нагрузкой рекомендуется сочетать Resource Governor с планированием индексов, партиционированием таблиц и использованием Query Store, что обеспечивает более стабильную производительность при больших объемах данных.

Применение T-SQL функции THROW для улучшенной обработки ошибок

В MS SQL Server 2012 введена функция THROW, которая заменяет устаревший RAISERROR для генерации исключений и передачи ошибок клиентскому приложению. THROW позволяет точно указывать текст ошибки, номер и состояние, что обеспечивает более предсказуемое поведение процедур и транзакций.

Синтаксис THROW минималистичен: THROW [error_number, message, state];. В отличие от RAISERROR, THROW автоматически завершает текущую транзакцию, если она активна, предотвращая частичные изменения данных без дополнительного контроля.

Для обработки ошибок в блоке TRY…CATCH рекомендуется использовать комбинацию THROW без параметров внутри CATCH, чтобы повторно передать пойманную ошибку: THROW;. Это сохраняет исходный номер ошибки, сообщение и состояние, что важно для журналирования и интеграции с внешними приложениями.

Пример использования THROW для защиты данных при обновлении таблицы:


BEGIN TRY
  UPDATE Orders SET Quantity = Quantity - @Amount WHERE OrderID = @OrderID;
  IF @@ROWCOUNT = 0
    THROW 50001, 'Запись с указанным OrderID не найдена.', 1;
END TRY
BEGIN CATCH
  -- Логирование ошибки
  INSERT INTO ErrorLog(ErrorNumber, ErrorMessage, ErrorTime)
  VALUES(ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE());
  THROW;
END CATCH


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

Вопрос-ответ:

Какие основные новшества появились в области обработки данных в SQL Server 2012?

SQL Server 2012 ввёл несколько значительных изменений в обработке данных. Появились новые типы индексов, включая columnstore index, который позволяет ускорить аналитические запросы на больших объёмах данных. Кроме того, улучшена поддержка параллельного выполнения запросов, оптимизирована работа с большими таблицами и добавлены функции, облегчающие агрегацию и анализ данных, такие как функции окон (window functions) с расширенными возможностями.

Что нового появилось в T-SQL и какие функции теперь доступны разработчикам?

В T-SQL SQL Server 2012 добавлены новые ключевые возможности, включая функции LAG и LEAD, позволяющие получать значения из предыдущих или следующих строк без сложных подзапросов. Также введены функции THROW для обработки ошибок вместо устаревшего RAISERROR, улучшены конструкции TRY…CATCH, расширены возможности оконных функций и агрегатных функций с более гибкой фильтрацией данных. Эти изменения упрощают написание сложных запросов и повышают их читаемость.

Как изменились средства управления и мониторинга в SQL Server 2012?

SQL Server 2012 предлагает новые инструменты для мониторинга и администрирования. Появился Data Tools, интегрированный с Visual Studio, что облегчает создание и деплой баз данных. Улучшена система мониторинга с использованием Management Studio, добавлены новые отчёты для анализа производительности и отслеживания ресурсов. Также появились возможности по управлению политиками и настройкам безопасности через Policy-Based Management, что позволяет централизованно контролировать конфигурации серверов.

В чём преимущества AlwaysOn и как это влияет на отказоустойчивость?

AlwaysOn Availability Groups — это новая функция, обеспечивающая высокий уровень доступности баз данных. Она позволяет объединять несколько копий базы данных в группы с синхронной или асинхронной репликацией. В случае сбоя основной базы данных автоматически активируется одна из резервных копий, минимизируя время простоя. В дополнение, AlwaysOn поддерживает масштабирование чтения через вторичные реплики, что снижает нагрузку на основную базу.

Какие улучшения затронули работу с большими данными и BI-интеграцию?

SQL Server 2012 ввёл расширенные возможности для работы с аналитикой и бизнес-данными. Columnstore index значительно ускоряет аналитические запросы, особенно для хранилищ данных. Также улучшена интеграция с SSIS, SSAS и SSRS, что облегчает создание ETL-процессов и построение отчётов. Новые функции для работы с временными рядами и аналитические функции упрощают подготовку данных для BI-систем, а поддержка больших объёмов информации стала более надёжной.