Sql 2020 r2: Microsoft SQL Server Standard Edition

Основные средства обеспечения безопасности в SQL Server

В этой статье мы рассмотрим средства SQL Server для обеспечения безопасности и лучшие практики, связанные с настройкой и обеспечением безопасности в этой СУБД.

Для начала вспомним базовые концепции безопасности SQL Server. MSSQL управляет доступом к объектам через аутентификацию и авторизацию.

• Аутентификация — это процесс входа в SQL Server, когда пользователь отправляет свои данные на сервер. Аутентификация устанавливает личность пользователя, который проходит аутентификацию;
• Авторизация — это процесс определения того, к каким защищаемым объектам может обращаться пользователь, и какие операции разрешены для этих ресурсов.

Многие объекты SQL Server имеют свои разрешения, которые могут наследоваться от вышестоящего объекта. Разрешения могут быть предоставлены отдельному пользователю, группе или роли.

Аутентификация в SQL Server

Аккаунт SQL Server можно разделить на 2 части: Имя входа и Пользователь.

• Имя входа – это глобальный логин для всего экземпляра SQL Server. С помощью него вы проходите процесс аутентификации;
• Пользователь – это участник базы данных, привязанный к определенному Имени Входа.

Например, ваше имя входа на сервер может быть domain\username, а пользователь в базе данных, привязанный к этому имени входа может называться domain_databaseUser. Практически всегда имя входа и пользователь в базе данных совпадают по названию, но нужно иметь в виду что они могут и различаться, иметь разные имена.

SQL Server поддерживает 2 режима аутентификации:

• Аутентификация Windows (Windows Authentication) – аутентификация осуществляется с помощью системы безопасности Windows. Пользователям, которые уже аутентифицированы в Windows и имеют права на SQL Server не нужно предоставлять дополнительные учетные данные.
• Смешанный режим аутентификации (Mixed Mode Authentication) – в этом режиме помимо аутентификации Windows поддерживается аутентификация самого SQL Server через логин и пароль.

Microsoft рекомендует использовать аутентификацию Windows, если есть такая возможность. Для аутентификации посредством логина и пароля, данные (логин и пароль) передаются по сети, хоть и в зашифрованном виде. При Windows аутентификации по сети передаётся серия зашифрованных сообщений, в которых не участвует пароль пользователя.

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

SQL Server поддерживает три типа Login Name (имен входа):

• Локальная учетная запись пользователя Windows или учетная запись домена/доверенного домена.
• Группа Windows. Предоставление доступа локальной группе Windows или группе из AD домена. Позволяет предоставить доступ ко всем пользователям, которые являются членами группы.
• Логин SQL Server (SQL Server authentication). SQL Server хранит имя пользователя и хэш пароля в базе данных master, используя методы внутренней аутентификации для проверки входа в систему.

SQL Server автоматически интегрируется с Active Directory. Если вы хотите раздать права доменной учетной записи, вам нужно использовать NetBios имя домена и логин учетной записи. Например для пользователя username в домене domain.local будет верным “domain\username”.

Авторизация в SQL Server

Для авторизации SQL Server использует безопасность на основе ролей, которая позволяет назначать разрешения для роли или группы Windows/домена, а не отдельным пользователям. В SQL Server есть встроенные роли сервера и баз данных, у которых есть предопределенный набор разрешений.

В SQL Server есть 3 уровня безопасности, их можно представить, как иерархию от высшего к низшему:

• Уровень сервера – на этом уровне можно раздать права на базы данных, учетные записи, роли сервера и группы доступности;
• Уровень базы данных включают в себя схемы, пользователи базы данных, роли базы данных и полнотекстовые каталоги;
• Уровень схемы включают такие объекты, как таблицы, представления, функции и хранимые процедуры.

Встроенные роли сервера

Схема ролей SQL Server:

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

Встроенные роли базы данных

Так же стоит отдельно выделить специальные роли в базе данных msdb.

Заметка: имейте в виду, что участники ролей dc_ssisadmin и dc_admin могут повысить свои права до уровня sysadmin.

Схема по встроенным ролям баз данных в SQL Server:

Роли приложений

Роль приложения – это объект базы данных (такой же, как и обычная роль базы данных), который позволяет с помощью аутентификации через пароль менять контекст безопасности в базе данных. В отличие от ролей баз данных, роли приложений по умолчанию находятся в неактивном состоянии и активируются, когда приложение выполняет процедуру sp_setapprole и вводит соответствующий пароль.

В отличие от обычных ролей, роли приложений практически никогда не используются. Как исключение, их применение можно найти в multi-layer приложениях.

Фильтрация данных в SQL Server

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

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

Фильтрация данных через Row-Level Security

Безопасность на уровне строк или Row-Level Security (RLS) позволяет фильтровать данные таблицы для разных пользователей по настраиваемому фильтру. Это осуществляется через SECURITY POLICY в T-SQL

На данном скриншоте политика настраивается таким образом, что пользователь Sales1 будет видеть строки таблицы, в которых значение столбца Sales равняется имени пользователя (Sales1), а пользователь Manager будет видеть все строки.

Схемы в SQL Server

У некоторых объектов SQL Server (таблицы, процедуры, представления, функции) есть схема. Схемы можно представить, как контейнеры для различных объектов (или пространство имён/namespace, если вы знакомы с программированием).

Например, если у пользователя есть права на select из схемы, то пользователь так же может делать select со всех объектов этой схемы. То есть объекты, принадлежащие схеме, наследуют её разрешения. Когда пользователи создают объекты в схеме, объекты принадлежат владельцу схемы, а не пользователю. Разрешения не наследуются от схемы пользователями. Т.е. у пользователей со схемой dbo по умолчанию, нет разрешений которые предоставлены этой схеме – они должны быть явно указаны.

Главное отличие схем от ролей в том, что разрешения на схемы могут быть предоставлены ролям. Например, у роли testrole могут быть разрешения select со схемы schema1 и разрешения на select/update на схеме schema2. Объект может принадлежать всего одной схеме, но права на него могут быть у нескольких ролей.

Встроенные схемы

В SQL Server есть встроенные системные схемы:

• dbo
• guest
• sys
• INFORMATION_SCHEMA

Схема dbo является схемой по умолчанию для новых баз данных, а пользователь dbo является владельцем схемы dbo. По умолчанию, новые пользователи в базе данных имеют схему dbo в качестве схемы по умолчанию. Другие встроенные схемы нужны для системных объектов SQL Server.

Шифрование данных средствами SQL Server

SQL Server может шифровать данные, процедуры и соединения с сервером. Шифрование возможно с использованием сертификата, асимметричного или симметричного ключа. В SQL Server используется иерархичная модель шифрования, то есть каждый слой иерархии шифрует слой под ним. Поддерживаются все известные и популярные алгоритмы шифрования. Для реализации алгоритмов шифрования используется Windows Crypto API.

Самыми распространенными типами шифрования являются TDE (Прозрачное шифрование данных) и Always Encrypted.

Прозрачное шифрование данных

Прозрачное шифрование данных или Transparent Data Encryption шифрует всю базу целиком. При краже физического носителя или .mdf/.ldf файла, злоумышленник не сможет получить доступ к информации в базе данных.

Диаграмма, для того чтобы представить весь процесс

Базовое шифрование базы данных через T-SQL:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
go
CREATE CERTIFICATE ServerCert WITH SUBJECT = 'DEK Certificate';
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO

Always Encrypted

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

Для шифрования Always Encrypted использует 2 ключа:

• Column Encryption Key (CEK)
• Column Master Key (CMK)

Все процессы шифрования и дешифрования данных происходят на клиенте, в базе данных хранятся только зашифрованное значение ключа шифрования (CEK).

Always Encrypted так же позволяет ограничить доступ к данным даже для DBA, таким образом давая возможность не беспокоиться о том, что администратор получит доступ к данным, к которым не должен.

Когда стоит использовать шифрование SQL Server?

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

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

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

Использование Group Managed Service Accounts для SQL Server

Групповые управляемые учетные записи службы или gMSA – это специальная учетная запись, которая автоматически управляется Active Directory. gMSA это развитие технологии MSA, так как MSA было невозможно использовать в кластерных сценариях.

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

Имейте в виду, что версия Windows Server для работы с gMSA должна быть не ниже 2012.

Оценка уязвимостей SQL Server через SSMS

В SQL Server Management Studio есть функция оценки уязвимостей для базы данных.

Выберите базу данных -> Tasks -> Vulnerability Assessment -> Scan For Vulnerabilities.

Сканнер оценит базу данных на предмет популярных ошибок в конфигурации безопасности и даст соответствующие рекомендации.

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

Аудит активности в SQL Server

SQL Server предоставляет возможность вести аудит любой пользовательской активности в экземпляре сервера.

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

Рассмотрим базовую настройку аудита:

В SSMS, во вкладке Security -> Audits создайте новый аудит.

Затем, для аудита нужно создать Спецификацию (Audit Specification), для указания событий, которые будут отслеживаться.

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

Общие рекомендации по безопасности SQL Server

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

Принцип наименьших привилегий

Когда вы заводите новых пользователей, рекомендуется использовать принцип LUA (Least-privileged User Account или Аккаунт с Наименьшими Правами). Этот принцип является важной частью безопасности сервера и данных.

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

Предоставление прав ролям, а не пользователям

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

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

Можно предоставить права пользователю на схему. В этом случае пользователи сразу смогут работать с вновь созданными объектами в этой схеме, в отличии от ролей, когда при создании нового объекта, роли нужно будет раздать на него права.

Средства обеспечения высокой доступности в MS SQL Server

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

Резервные копии — это хорошо, но, когда счёт идёт на минуты, а порой и секунды, поможет только избыточность данных и четкий план отработки отказа. SQL Server предоставляет разные способы для реализации избыточности и высокой доступности данных.

Зеркалирование баз данных (Database mirroring) в SQL Server

• Доступно в редакциях: Standard (только синхронный режим), Enterprise, Web/express – только режим Witness
• Работает на уровне: Базы данных
• Версия SQL Server: SQL Server 2005, SQL Server 2008

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

Зеркалирование работает на уровне базы данных (может еще быть на уровне объектов) и обеспечивает автоматический/ручной переход между серверами в случае отказа. Резервная база в любом из режимов работы зеркалирования будет находиться в состоянии постоянного восстановления, поэтому обращаться к ней не выйдет.

У зеркалирования есть 2 режима работы: Синхронный и асинхронный.

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

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

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

Асинхронный или режим высокой производительности — работает также, за исключением того, что главный сервер после отправки транзакционного лога не ждёт ответа от резервного об успешной записи на диск.

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

Зеркалирование стоит использовать только если у вас совпадение по всем условиям

• SQL Server 2008 или SQL Server 2005
• Низкая сетевая задержка (latency) между основным сервером и резервным
• Вам критична потеря даже одной транзакции

Если ваш случай не подпадает под все условия, стоит рассмотреть другие варианты.

Доставка журналов (Log shipping) в SQL Server

• Доступно в редакциях: Standard, Web, Enterprise
• Работает на уровне: Базы данных
• Версия SQL Server: SQL Server 2005 и выше

Технология доставки журналов (Log shipping) позволяет автоматически отправлять резервные копии журналов транзакций из базы данных источника в одну или более баз данных получателей и затем восстанавливает их в базах данных получателей. Опционально может быть третий сервер, который будет выполнять роль службы мониторинга – отслеживать выполнение операций резервного копирования и восстановления журналов.

Средства мониторинга и диагностики SQL Server рассмотрены в отдельной статье.

После настройки доставки журналов создаются Задания (jobs). Принцип работы таков:

1. Первое задание отвечает за резервное копирование журнала транзакций на основном сервере
2. Второе задание отвечает за распространение бекапа на все сервера-получатели
3. Третье задание восстанавливает журналы во все базы данных получателей. Восстановление доступно в режимах No recovery mode или Standby mode

Это более простая технология, относительно зеркалирования и Always On. Доставку журналов стоит использовать, когда:

• Допустима разница в данных между основным сервером и серверами получателями. Стандартное расписание выполнение заданий – каждые 15 минут. Можно поставить и меньше, но нужно учитывать скорость передачи данных по сети и время на восстановление журналов.
• Вы хотите обращаться к базам данных получателей для read доступа. Это возможно, когда режим восстановления установлен в Standby mode. Но имейте в виду, обращаться к базе вы сможете только в промежутках между восстановлением журнала.

Репликация в Microsoft SQL Server: обзор методов

• Доступно в редакциях: Standard и Web – с ограничениями, Enterprise
• Работает на уровне: Объекта базы данных
• Версия SQL Server: SQL Server 2000 и выше

Существует различные типы репликации:

• Репликация транзакций
• Одноранговая репликация транзакций
• Репликация моментальных снимков
• Репликация слиянием

Есть ещё 2 топологии, основанные на репликации транзакций:

• Двунаправленная репликация транзакций
• Обновляемые подписки для репликации транзакций (функция поддерживается в версиях SQL Server с 2012 по 2016)

Репликация может применяться для различных целей, но в основном её используют для разгрузки OLTP серверов select запросами и для высокой доступности. Хотя Microsoft не позиционирует репликацию как средство для достижения высокой доступности, она вполне может выполнять эту роль.

Заметка: в модели репликации SQL Server есть 3 типа серверов:

• Publisher (издатель) – сервер который издаёт статьи
• Distributor (распространитель) – сервер который распространяет статьи на сервера-подписчики
• Subscriber (подписчик) – сервер который получает распространяемые статьи

Изменение которые проходят в выбранных объектах на издателе, отправляются сначала на распространителя, затем распространитель рассылает эти изменения подписчикам.

Рассмотрим 4 основные типа репликации

Репликация транзакций (Transactional Replication)

Этот тип репликации используется для «near real time» репликации данных, то есть данные на подписчиках появляются практически сразу, с учетом времени копирования данных по сети.

Транзакции с издателя отправляются на распространитель, распространитель отправляет эти транзакции на подписчиков. Распространитель может отправлять данные подписчикам немедленно, либо по определенному расписанию. Объекты на подписчике, которые участвуют в репликации должны использоваться только для read only доступа, иначе данные станут несогласованные и возникнет конфликт.

Одноранговая репликация транзакций (Peer-To-Peer Transactional Replication)

Одноранговая репликация или Peer-To-Peer Transactional Replication похожа на обычную репликацию транзакций, но она может работать сразу с несколькими серверами.

Одноранговую репликацию можно назвать master-master репликацией (для обычной транзакционной репликации было бы master-slave). Рассмотрим схему из документации Microsoft

Каждый экземпляр SQL Server который участвует в одноранговой репликации может обрабатывать read и write операции. Так же в таком типе репликации предусмотрен механизм разрешения конфликтов, когда на несколько серверах одновременно приходит одна и та же операция, например, update запрос. Но даже с учетом этого механизма не рекомендуется записывать данные в несколько экземпляров одновременно.

Такой тип репликации может использоваться для балансировки нагрузки, в том числе для update/insert/delete операций.

Репликация моментальных снимков (Snapshot replication)

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

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

Этот вид репликации стоит использовать когда:

• Данные редко меняются
• Допустима разница в данных между издателем и подписчиком
• Большой объём изменений за короткий период времени

Репликация слиянием (Merge replication)

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

• Репликация слиянием может иметь только одного издателя и несколько подписчиков, когда как в peer-to-peer репликации все экземпляры равны между собой (одновременно являются и издателями, и подписчиками
• В репликации слиянием подписчики могут получать разные данные, когда в одноранговой репликации все сервера имеют одни данные
• Репликация слиянием может разрешать конфликты, одноранговая – нет
• Одноранговая репликация доступна только в Enterprise редакции

Репликацию слиянием стоит применять тогда, когда вам нужно консолидировать данные.

Двунаправленная репликация транзакций иОбновляемые подписки для репликации транзакций

Двунаправленная репликация (Bidirectional Transactional) это топология, когда обычная репликация транзакций настроена на репликацию одни тех же данных. Параметр @loopback_detection parameter в sp_addsubscription должен быть выставлен в TRUE

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

Группы доступности Always On в SQL Server

• Доступно в редакциях: Standard (с ограничениями), Enterprise (
• Работает на уровне: Базы данных
• Версия SQL Server: SQL Server 2012 и выше

Always On availability groups появились в релизе SQL Server 2012. Это альтернатива (хотя скорее развитие) технологии зеркалирования баз данных.

Группы доступности Always On работают на основе Windows Server Failover Cluster, но начиная с 2017 версии появилась возможность использовать Always On без WSFC. Always on похож на зеркалирование баз данных (синхронный и асинхронный режимы) но вторичных реплик может быть до 8 штук. Always On поддерживает автоматическую отработку отказа (то есть, при падении основного экземпляра кластера WSCF выбирает новую основную реплику и перенаправляет write запросы на неё).

Каждый экземпляр в группе доступности может быть либо primary (основным), либо secondary (вторичным). Вторичные реплики могут быть либо в read-only, либо в режиме No recovery. Каждый экземпляр в группе доступности содержит в себе копии баз данных группы доступности. Имейте в виду, что в синхронном режиме скорость проведения транзакций будет зависеть от самого «медленного» участника группы доступности.

В базовой настройке Always On прост, после установки SQL Server всё можно настроить с помощью мастера (WSFC через оснастку в Windows, а сами группы доступности через мастер в SSMS). Но при большом количестве серверов и сложной инфраструктуре придется хорошо изучить документацию.

Рекомендуется использовать Always On в тех же ситуациях, когда и зеркалирование, или если вам нужна балансировка нагрузки select запросов. Также резервные копии рекомендуется делать именно с вторичных реплик, это еще одно применение групп доступности.

Более подробно мы о группах доступности Always On в SQL Server читайте в статье.

SQL Server предоставляет много разнообразных решений для обеспечения высокой доступности данных. При наличии Enterprise редакции и SQL Server 2012 (и выше) лучше использовать Always On. Репликацию можно использовать для разгрузки OLTP систем select запросами и для частичной избыточности (хотя одноранговая репликация позиционируется как полноценное средство избыточности данных). Доставку журналов транзакций и зеркалирование баз данных можно использовать в более старых версиях SQL Server или если условия вынуждают использовать именно эти технологии.

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

Download Microsoft SQL Server 2008 R2 RTM

RTM-версия Microsoft® SQL Server® 2008 R2 Express — это мощная и надежная система управления данными, предоставляющая широкий набор функций для обеспечения защиты данных и повышения производительности для внедренных приложений, упрощенных веб-сайтов, веб-приложений и локальных хранилищ данных.
Эта загружаемая версия предназначена для простого развертывания и быстрого создания прототипов. Она включает поддержку Sysprep — программы подготовки систем Майкрософт для развертывания в операционной системе Microsoft Windows.

Выпуск SQL Server Express предназначен для простого развертывания и быстрого создания прототипов.
Этот выпуск разработан таким образом, чтобы полностью интегрироваться с другими продуктами, входящими в серверную инфраструктуру.
В эту загружаемую версию не входят средства управления.
Чтобы установить SQL Server Express со средствами управления, загрузите пакет Microsoft SQL Server Express со средствами управления.

Чтобы узнать о новых возможностях в SQL Server® 2008 R2, щелкните здесь.

Здесь можно ознакомиться с заявлением о конфиденциальности SQL Server®.

Примечание.
Некоторые компоненты в Microsoft® SQL Server® 2008 R2 поддерживают два метода выполнения установки.

• Интерактивная установка. Для сбора всех данных и подтверждений, необходимых для завершения установки, применяется пользовательский интерфейс.
  В процессе интерактивной установки выполняется отображение, принятие и архивирование действующих условий лицензии SQL Server.
• Автоматическая установка. Этот процесс выполняется из командной строки DOS и не поддерживает интерактивный пользовательский интерфейс для задач установки.
  В этом случае во время установки будет необходимо ввести параметр, который свидетельствует о принятии условий лицензии.
  
  Здесь можно ознакомиться с копией условий лицензии Microsoft® SQL Server® 2008 R2.

Установка Microsoft SQL Server 2019

Инструкция по установке Microsoft SQL Server 2019, чтобы не забыть. Будем устанавливать Microsoft SQL Server 2019 на Windows Server 2016.

Бесплатная книга «Introducing Microsoft SQL Server 2019»:
https://info.microsoft.com/ww-landing-introducing-sql-server-2019-content.html

Редакции MS SQL Server 2019

SQL Server 2019 Editions Datasheet.pdf

• Express — бесплатная версия
  • до 4 ядер CPU
  • до 1 ГБ памяти
  • размер базы до 10 ГБ
• Web — бесплатная версия
  • до 16 ядер CPU
  • до 64 ГБ памяти
• Standard — не все функции, может быть лицензирована по модели SERVER + CAL или PER CORE
  • до 24 ядер CPU
  • до 128 ГБ памяти
• Enterprise — включает в себя все возможные функции и компоненты, может быть лицензирована только по модели PER CORE
• Developer — бесплатная версия с опциями Enterprise
  • для некоммерческого использования
  • для разработчиков и тестировщиков
• Evaluation — бесплатная пробная версия с опциями Enterprise
  • 180 дней пробный период

Лицензирование MS SQL Server 2019

• PER CORE — по ядрам сервера
• SERVER + CAL — на сервер + каждого пользователя
• Есть возможность лицензирования контейнеров, виртуальных машин и кластеров Big Data

Требования к установке

Основные требования:

• Нельзя устанавливать MS SQL Server 2019 на диски со сжатием, сетевые диски и диски, доступные только для чтения.
• Нельзя монтировать дистрибутив через удалённый рабочий стол, вам нужно скачать его на сервер перед началом установки.
• Операционная система:
  • Windows 10 Th2 1507 или более поздней версии
  • Windows Server 2016 или более поздней версии

Дополнительные требования к установке можно найти здесь:

https://docs.microsoft.com/ru-ru/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-ver15?view=sql-server-ver15

Момент, на который стоит обратить внимание:

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

Иначе получим ошибку:

MSSQL Server — ошибка The SQL Server license agreement cannot be located for the selected edition

Операционная система

Минимальная версия серверной операционной системы для MS SQL Server 2019 — Windows Server 2016. Подготовлю виртуальную машину с этой операционной системой.

Создаю виртуалку. Я использую VMware.

Выбираю конфигурацию оборудования для Windows Server 2016.

Выделю для сервера 8 CPU, 16 Гб оперативной памяти. Диск 200 Гигабайт. Установку операционной системы здесь не рассматриваю, для этого у меня уже была статья:

ESXi 6.7 — установка Windows Server 2016 на виртуальную машину

Дистрибутив для установки MS SQL Server 2019

Качаем дистрибутив для установки пробной версии:

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2019

Скачиваем SQL2019-SSEI-Eval.exe.

Запускаем.

Выбираю вариант «Скачать носитель».

Выбираем язык. Язык должен соответствовать языку операционной системы Windows Server 2016.

У меня операционная система на английском языке, поэтому выбираю «Английский». Скачиваем ISO образ установщика.

Ожидаем.

Скачивание успешно выполнено. Получаем файл SQLServer2019-x64-ENU.iso.

Установка MS SQL Server 2019

Монтирую ISO образ установщика.

Запускаю setup.exe под администратором. Открывается SQL Server Installation Center.

Слева выбираем пункт Installation.

Нажимаю New SQL Server stand-alone installation or add features to an existing installation.

В разделе Product Key нас просят выбрать тип бесплатной установки или указать ключ продукта. Доступны бесплатные версии:

• Evaluation
• Developer
• Express

Для установки Standard или Enterprise введите соответствующий ключ. В процессе установки содержимое пунктов слева будет меняться. Next.

В разделе License Terms принимаем лицензионное соглашение. Next.

Раздел Global Rules я как-то проскочил, здесь проходят проверки перед установкой. Если проблем не найдено, то мы попадаем в следующий раздел.

Раздел Product Updates тоже проскочил, здесь можно включить поиск обновлений через Windows Update. Я предпочитаю устанавливать сервис-паки, этот раздел мне не нужен.

В разделе Install Setup Files подготавливаются файлы для установки.

Автоматически попадаем в раздел Install Rules. Снова идут проверки установки. У меня есть предупреждение, читаю.

Инсталлятору не нравится Windows Firewall. Предупреждают, что у пользователей может не работать доступ, но с этим я потом разберусь. Ok. Next.

В разделе Feature Selection нужно выбрать нужные нам компоненты. Я выбираю минимум — движок, а именно Database Engine Services.

И выбираю фичи совместимости: Client Tools Connectivity, Client Tools Backwards Compatibility. Пути для установки оставляю без изменений. Next.

Про некоторые компоненты

• Database Engine Services — движок SQL Server.
• SQL Server Replication — службы репликации.
• Machine Learning Services and Language Extensions — поддержка R/Python/Java скрипта в SQL Server. Machine Learning.
• Full-Text and Semantic Extractions for Search — полнотекстовый и семантический поиск.
• Data Quality Services — управление качеством данных, DQS.
• PolyBase Query Service For External Data — доступа к внешним данным, SQL Server или Oracle Database.
• Analysis Services — аналитика (SSAS). Бизнес отчеты (BI), кубы OLAP.
• Machine Learning Server (Standalone) — Machine Learning без движка SQL Server.
• Data Quality Client — DQS без движка SQL Server.
• Client Tools Connectivity — ODBC, OLE DB и некоторые другие.
• Integration Services — службы интеграции данных (SSIS).
• Client Tools Backwards Compatibility — устаревшие DMV и системные процедуры.
• Client Tools SDK — пакет для разработчиков.
• Distributed Replay Controller/Client — улучшенный SQL Server Profiler. Для моделирования нагрузки и тестирования производительности.
• SQL Client Connectivity SDK — ODBC/OLE DB SDK для разработчиков.
• Master Data Services — компонент (MDS) из Business Intelligence.

В разделе Feature Rules идёт проверка на наличие возможность установки выбранных компонентов.

В разделе Instance Configuration можно выбрать нужный инстанс. У меня одна БД, выбираю Default instance. Next.

В разделе Server Configuration во вкладке Service Accounts меняю пользователя под которым будет запускаться SQL Server и SQL Server Agent. Тип запуска агента меняю на Automatic.

Обратите внимание на галку Grand Perform Volume Maintenance Task privilege to SQL Server Database Engine Service. Полезная штука для оптимизации производительности. Настройка влияет на файл данных. Когда файл автоматически вырастает, то новый кусок заполняется нулями, в этот момент SQL может тормозить. Instant File Initialization (IFI) позволяет отключить это зануление.

Эту опцию я уже использовал для оптимизации SQL Server 2014:

Настройка MSSQL для работы с 1С — мифы и реальность

Во вкладке Collation можно изменить кодировку. Я оставляю по умолчанию кириллицу. Next.

Переходим в раздел Database Engine Configuration, здесь много вкладок.

Вкладка Server Configuration. Authentication Mode — меняю на Mixed Mode, задаю пароль на пользователю sa. Здесь же кнопкой Add Current User делаю свою учётку админом БД.

Вкладка Data Directories. Здесь можно и иногда нужно изменить пути к файлам. Я оставляю по умолчанию.

Вкладка TempDB. Здесь настраиваются параметры для базы tempdb. Автоматические параметры нормальные. Я оставляю без изменений.

Вкладка MaxDOP. Настраиваем количество параллельных потоков для выполнения запросов. Автоматические параметры зависят от количества процессоров вашего сервера. У меня 8 процессоров, инсталлятор предложил 8 параллельных потоков. 0 — отключает настройку, при этом SQL Server будет использовать все ядра.

Вкладка Memory. Выделяем память для SQL Server. Я 4 Гб ОЗУ оставляю операционной системе, а остальное отдаю SQL серверу.

Вкладка FILESTREAM. Здесь можно при желании включить эту поддержку.

FILESTREAM позволяет приложениям на основе SQL Server хранить в файловой системе неструктурированные данные, например документы и изображения. Приложения могут одновременно использовать многопоточные API-интерфейсы и производительность файловой системы, тем самым обеспечивая транзакционную согласованность между неструктурированными и соответствующими им структурированными данными.

Next.

В разделе Feature Configuration Rules снова идут проверки. Next.

Попадаем в раздел Ready to Install. Проверяю что всё верно. Install.

Начинается установка SQL Server 2019.

Установка SQL Server 2019 успешно завершена. Close.

SQL Server Management Tools

Для управления SQL сервером нам понадобится SQL Server Management Tools (SSMS). Приложение SSMS можно установить на тот же сервер, что и SQL Server 2019, однако, иногда удобнее выполнить установку на компьютер системного администратора, DBA или разработчика.

Сейчас это отдельный продукт, который не входит в пакет установки SQL Server 2019. Однако, в инсталляторе SQL Server 2019 есть на него ссылка.

Ссылка ведёт на сайт:

https://docs.microsoft.com/ru-ru/sql/ssms/download-sql-server-management-studio-ssms

Скроллим вниз.

Нажимаем «Скачайте SQL Server Management Studio (SSMS)». При этом скачается версия для вашего языка. Если поискать на страничке, то можно найти ссылки на версии SSMS на других языках. Я скачиваю английскую версию SSMS-Setup-ENU.exe.

Запускаю SSMS-Setup-ENU.exe.

Выбираю путь установки, Install.

Загружается пакет.

Устанавливаются необходимые компоненты.

Начинается установка SSMS.

Установка SQL Server Management Studio завершена, требуется перезагрузка. Перезагружаю сервер — Restart.

Установка завершена.

Проверка работы SQL Server 2019

Запускаю SQL Server Management Studio.

Подключаюсь к SQL серверу под sa.

Установлена версия SQL Server 15.0.2000.5.

Основные средства обеспечения безопасности в SQL Server

В этой статье мы рассмотрим средства обеспечения безопасности SQL Server и лучшие практики, связанные с настройкой и настройкой безопасности в этой СУБД.

Для начала вспомним базовые концепции безопасности SQL Server. MSSQL управляет доступом к объекту через аутентификацию и авторизацию .

• Аутентификация — это процесс входа в SQL Server, когда пользователь отправляет свои данные на сервер.Аутентификация устанавливает личность пользователя, который проходит аутентификацию;
• Авторизация — это процесс определения того, каким защищаемым объектомм может обращаться пользователь, и какие операции разрешены для этих ресурсов.

Многие объекты SQL Server имеют свои, которые могут наследоваться от вышестоящего объекта. Разрешения могут быть предоставлены отдельному пользователю, группе или роли.

Аутентификация в SQL Server

Аккаунт SQL Server можно разделить на 2 части: Имя входа и Пользователь .

• Имя входа — это глобальная логин для всего экземпляра SQL Server. С помощью него вы проходите процесс аутентификации;
• Пользователь — это участник базы данных, привязанный к определенному Имени Входа.

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

SQL Server поддерживает 2 режима аутентификации :

• Аутентификация Windows (Windows Authentication) — аутентификация осуществляется с помощью системы безопасности Windows. Пользователи, которые уже аутентифицированы в Windows, имеют права на SQL Server.
• Смешанный режим аутентификации (Смешанный режим аутентификации) — в этом режиме помимо аутентификации Windows действует аутентификация самого SQL Server через логин и пароль.

Microsoft рекомендует использовать аутентификацию Windows, если есть такая возможность. Для аутентификации посредством логина и пароля данные (пароль) передаются в сети, хоть и в зашифрованном виде. При аутентификации Windows по сети передаётся серия зашифрованных сообщений, в которых не участвует пароль пользователя.

Некоторые приложения, особенно старые, не входят в систему Windows.

SQL Server поддерживает три типа Имя входа (имен входа):

• Локальная учетная запись пользователя Windows или учетная домена / доверенного домена.
• Группа Windows . Предоставление доступа к локальной группе Windows или группе из AD домена. Позволяет предоставить доступ всем пользователям, которые входят в состав группы.
• Логин SQL Server (аутентификация SQL Server). SQL Server хранит имя пользователя и хэш пароля в базе данных master , используя внутреннюю аутентификацию для проверки входа в систему.

SQL Server автоматически интегрируется с Active Directory. Если вы хотите раздать права доменной учетной записи, вам нужно использовать NetBios имя домена и логин учетной записи. Например для пользователя username в домене domain.local будет верным «домен \ имя пользователя».

Авторизация в SQL Server

Для авторизации SQL Server использует безопасность на основе ролей, которая позволяет назначать разрешение для роли или группы Windows / домена, а не для своих пользователей.В SQL Server есть встроенные роли сервера и баз данных, у которых есть предопределенный набор разрешений.

В SQL Server есть 3 уровня безопасности, их можно представить как иерархию от высшего к низшему:

• Уровень сервера — на этом уровне можно раздать права на базы данных, учетные записи, роли сервера и группы доступности;
• Уровень базы данных включает в себя схемы, пользователи базы данных, роли базы данных и полнотекстовые каталоги;
• Уровень включает такие объекты, как таблицы, функции и хранимые процедуры.

Встроенные роли сервера

Схема ролей SQL Server:

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

Встроенные роли базы данных

Так же стоит отдельно роли в базе данных msdb.

Заметка может повысить уровень: они могут иметь свои права в административных административных единицах, что участники ролей dc_ssis.

Схема по встроенным ролям баз данных в SQL Server:

Роли приложений

Роль приложения — это объект базы данных (такой же, как и обычная роль базы данных), которая позволяет с помощью аутентификации через менять контекст безопасности в базе данных. В от ролей базовых данных, роли приложений по умолчанию находятся в неактивном состоянии и активируются, когда действие функции sp_setapprole и вводит соответствующий пароль.

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

Фильтрация данных в SQL Server

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

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

Фильтрация данных через безопасность на уровне строк

Безопасность на уровне строк или Безопасность на уровне строк ( RLS ) позволяет фильтровать данные таблицы для разных пользователей по настраиваемому фильтру. Это осуществляется через ПОЛИТИКУ БЕЗОПАСНОСТИ в T-SQL

. На данном скриншоте настраивается таким образом, что пользователь Sales1 будет видеть строки таблицы, в которых значение столбца Sales равняется имени пользователя (Sales1), и пользователь Manager будет видеть все строки.

Схемы в SQL Server

У некоторых объектов SQL Server (таблицы, процедуры, представления, функции) есть схема. Схемы можно представить, как контейнеры для различных объектов (или пространство имён / пространство имен, если вы знакомы с программированием).

Например, если у пользователя есть права на выбор из схем, то пользователь так же может делать выбор со всех этих объектов схемы. То есть объекты, принадлежащие схеме, наследуют её разрешение. Когда используются объекты в схеме, объекты принадлежат владельцу схемы, а не пользователю.Разрешения не наследуются от схемы пользователями. Т.е. у пользователей со схемой dbo по умолчанию нет разрешений, которые предоставлены этой схеме — они явно указаны.

Главное отличие схем от ролей в том, что разрешения схемы могут быть предоставлены ролям. Например, у роли testrole могут быть разрешения выбрать со схемы schema1 и разрешение на выбор / обновить на схеме schema2. Объект может принадлежать всего одной схеме, но права на него могут быть у нескольких ролей.

Встроенные схемы

В SQL Server есть встроенные системные схемы:

• dbo
• guest
• sys
• INFORMATION_SCHEMA

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

Шифрование данных средств SQL Server

SQL Server может шифровать данные, процедуры и соединения с сервером. Шифрование возможно с использованием сертификата, асимметричного или симметричного ключа. В SQL Server используется иерархическая модель шифрования, то есть каждый уровень иерархии шифрует слой под ним. Поддерживаются все известные и популярные алгоритмы шифрования.Для реализации алгоритмов шифрования используется Windows Crypto API.

Самыми распространенными типами шифрования являются TDE (Прозрачное шифрование данных) и Always Encrypted.

Прозрачное шифрование данных

Прозрачное шифрование данных или Transparent Data Encryption шифрует всю базу целиком. При краже физического носителя или .mdf / .ldf файла, злоумышленник не сможет получить доступ к информации в базе данных.

Диаграмма, для того, чтобы представить весь процесс

Базовое шифрование базы данных через T-SQL:

USE master;
GO
СОЗДАТЬ ШИФРОВАНИЕ МАСТЕР-КЛЮЧА ПО ПАРОЛЮ = 'пароль';
go
СОЗДАТЬ СЕРТИФИКАТ ServerCert С СУБЪЕКТОМ = 'Сертификат DEK';
go
USE AdventureWorks2012;
GO
СОЗДАТЬ КЛЮЧ ШИФРОВАНИЯ БАЗЫ ДАННЫХ
С АЛГОРИТМОМ = AES_128
ШИФРОВАНИЕ СЕРТИФИКАТОМ СЕРВЕРА ServerCert;
GO
ИЗМЕНИТЬ БАЗУ ДАННЫХ AdventureWorks2012
ВКЛЮЧИТЬ ШИФРОВАНИЕ;
GO

Always Encrypted

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

Для шифрования Always Encrypted используется 2 ключа:

• Column Encryption Key (CEK)
• Column Master Key (CMK)

Все процессы шифрования и дешифрования данных происходят на клиенте, в базе данных хранятся только зашифрованное значение ключа шифрования (CEK).

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

Когда стоит использовать шифрование SQL Server?

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

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

Внедрение шифрования должно быть хорошо спланировано: работают с вашим сервером шифрования других типов на своей стороне и многие нюансы.

Использование Group Managed Service Accounts для SQL Server

Групповые управляемые учетные записи службы или gMSA — это специальная учетная запись, которая автоматически управляется Active Directory.gMSA — это развитие технологии MSA, так как MSA невозможно использовать в кластерных сценариях.

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

Имейте в виду, что версия Windows Server для работы с gMSA должна быть не ниже 2012 года.

Оценка уязвимостей SQL Server через SSMS

В SQL Server Management Studio есть функция оценки уязвимостей для базы данных.

База данных -> Задачи -> Оценка уязвимости -> Поиск уязвимостей .

Сканнер оценит базу данных на предмет популярных ошибок в конфигурации безопасности и соответствующие рекомендации.

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

Аудит активности в SQL Server

SQL Server предоставляет возможность вести аудит любой пользовательской активности в экземплярах сервера.

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

Рассмотрим базовую настройку аудита:

В SSMS, во вкладке Безопасность -> Аудиты создайте новый аудит.

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

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

Общие рекомендации по безопасности SQL Server

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

Принцип наименьших привилегий

Когда вы заводите новых пользователей, рекомендуется использовать принцип LUA ( Учетная запись с минимальными привилегиями или Аккаунт с Наименьшими Правами ).Этот параметр является частью безопасности сервера и данных.

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

Предоставление прав ролям, не пользователям

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

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

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

.

Загрузить Microsoft SQL Server 2008 R2 RTM

RTM-версия Microsoft® SQL Server® 2008 R2 Express — это мощная и надежная система управления данными, предоставляющая широкий набор функций для защиты данных и повышения производительности для внедрения приложений, упрощенных веб-сайтов, веб-приложений и локальных хранилищ данных.
Эта загружаемая версия предназначена для простого развертывания и быстрого создания прототипов. Она включает поддержку Sysprep — программы подготовки систем Майкрософт для развертывания в операционной системе Microsoft Windows.

Выпуск SQL Server Express предназначен для простого развертывания и быстрого создания прототипов.
Этот выпуск разработан таким образом, чтобы интегрироваться с другими продуктами, входящими в серверную инфраструктуру.
В эту загружаемую версию не входят средства управления .
Чтобы установить SQL Server Express со средствами управления, загрузите пакет Microsoft SQL Server Express со средствами управления.

Чтобы узнать о новых возможностях в SQL Server® 2008 R2, щелкните здесь.

Здесь можно ознакомиться с заявлением о конфиденциальности SQL Server®.

Примечание.
Некоторые компоненты в Microsoft® SQL Server® 2008 R2 содержат два метода выполнения установки.

• Интерактивная установка. Для сбора данных и подтверждений, необходимых для завершения установки, пользовательский интерфейс.
  В процессе интерактивной установки выполняется отображение, выполнение и архивирование условий лицензии SQL Server.
• Автоматическая установка. Этот процесс выполняет из системы DOS и не поддерживает интерактивный пользовательский интерфейс для задач установки.
  В этом случае установки будет установлен параметр, который принимает участие в установлении условий лицензии.
  
  Здесь можно ознакомиться с копией условий лицензии Microsoft® SQL Server® 2008 R2.

.

Средства обеспечения высокой доступности в MS SQL Server

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

Резервные копии — это хорошо, но, когда счёт идет на минуты, а порой и секунды, поможет только избыточность данных и четкий отработанный планки отказа. SQL Server предоставляет разные способы реализации избыточности и высокой доступности данных.

Зеркалирование базовых данных (зеркалирование базы данных) в SQL Server

• Доступно в редакциях: Standard (только синхронный режим) , Enterprise, Web / express — только режим Witness
• Работает на уровне: Базы данных
• Версия SQL Server: SQL Server 2005, SQL Server 2008

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

Зеркалирование работает на уровне базы данных (может еще быть на уровне объектов) и обеспечивает автоматический / ручной переход между серверами в случае отказа. Резервная база в любом режиме работы зеркального покрытия будет находиться в состоянии постоянного восстановления, поэтому обращаться к ней не выйдет.

У зеркалирования есть 2 режима работы: Синхронный и асинхронный.

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

В синхронном режиме есть возможность использовать сервер Witness. Сервер в режиме свидетеля следит за работоспособностью серверов зеркалирования и может запустить отработку отказа, если есть переход сервера в активное состояние.

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

Асинхронный или режим высокой производительности — работает также, за исключением того, что главный сервер после отправки транзакционного лога не ждёт ответа от резервного сервера после успешной записи на диск.

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

Зеркалирование стоит использовать только при совпадении по всем условиям

• SQL Server 2008 или SQL Server 2005
• Низкая сетевая задержка (задержка) между основным сервером и резервным
• Вам критична потеря даже одной транзакции

Если ваш случай не подпадает под все условия, стоит рассмотреть другие варианты.

Доставка журналов (Доставка журналов) в SQL Server

• Доступно в редакциях: Standard, Web, Enterprise
• Работает на уровне: Базы данных
• Версия SQL Server: SQL Server 2005 и выше

Технология доставки журналов (Log shipping) позволяет автоматически отправлять резервные копии журналов транзакций из источников баз данных в одну или более данных получателей.Опционально может быть третий сервер, который будет роль службы мониторинга — выполнение операций резервного копирования и восстановления журналов.

Средства мониторинга и диагностики SQL Server рассмотрены в отдельной статье.

После настройки доставки журналов задержания (вакансии). Принцип работы таков:

1. Первое задание отвечает за резервное копирование журнала транзакций на основном сервере
2. Второе задание отвечает за распространение бекапа на все сервера-получатели
3. Третье задание восстанавливает журналы во все базы данных получателей.Восстановление доступно в режимах Без режима восстановления или в режиме ожидания

Это более простая технология, относительно зеркалирования и Always On. Доставку журналов стоит использовать, когда:

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

Репликация в Microsoft SQL Server: обзор методов

• Доступно в редакциях: Standard и Web — с ограничениями, Enterprise
• Работает на уровне: Объекты базы данных
• Версия SQL Server: SQL Server 2000 и выше

Существуют различные типы репликаций:

• Репликация транзакций
• Одноранговая репликация транзакций
• Репликация моментальных снимков
• Репликация слиянием

Есть ещё 2 топологии, основанные на репликации транзакций

. Обновленные подписки для репликации транзакций (функция поддерживается в версии SQL Server с 2012 по 2016)

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

Заметка: в модели репликации SQL Server есть 3 типа серверов:

• Publisher (издатель) — сервер который издаёт статьи
• Distributor (распространитель) — сервер распространяет статьи на сервера-подписчики
• Subscriber (подписчик ) — сервер который получает распространяемые статьи

Изменение, которое проходит в выбранных объектах на издателе, отправляются сначала на распространителя, затем распространитель рассылает эти изменения подписчикам.

Рассмотрим 4 основных типа репликации

Репликация транзакций (Transactional Replication)

Этот тип репликации используется для репликации данных «почти в реальном времени», то есть данные на подписчиках появляются практически сразу, с учетом времени копирования данных по сети.

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

Одноранговая репликация транзакций (Peer-To-Peer Transactional Replication)

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

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

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

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

Репликация моментальных снимков (Репликация моментальных снимков)

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

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

Этот вид репликации стоит использовать когда:

• Данные редко меняются
• Допустимая разница в данных между издателем и подписчиком
• Большой объём изменений за короткий период времени

Репликация слиянием (репликация слияния)

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

• Репликация слиянием может иметь только одного издателя и несколько подписчиков, когда в одноранговой сети репликации все экземпляры равны между собой (одновременно являются и издателями, и подписчиками
• В репликации слиянием подписчики могут получать разные данные, когда в одноранговой репликации все сервера имеют одни данные
• Репликация слиянием может разрешать конфликты, одноранговая — нет
• Одноранговая репликация доступна только в Enterprise редакции

Репликация слиянием стоит применять тогда, когда вам нужно консо лидировать данные.

Двунаправленная репликация транзакций иОбновляемые подписки для репликации транзакций

Двунаправленная репликация (двунаправленная транзакция) это топология, когда обычная репликация транзакций на репликацию одни тех же данных. Параметр Параметр @loopback_detection в sp_addsubscription должен быть выставлен в TRUE

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

Группы доступности Always On в SQL Server

• Доступно в редакциях: Standard (с ограничениями) , Enterprise (
• Работает на уровне: Базы данных
• Версия SQL Server: SQL Server 2012 и выше

Группы доступности Always On появились в выпуске SQL Server 2012. Это альтернатива (хотя скорее развитие) технологии зеркалирования баз данных

Группы доступности Always On работают на основе Windows Server Failover Cluster , но начиная с 2017 года появилась возможность использовать Always On без WSFC .Всегда включен на зеркалирование баз данных (синхронный и асинхронный режимы) но вторичных реплик может быть до 8 штук. Always On поддерживает автоматическую отработку отказа (то есть, при падении основного экземпляра WSCF выбирает новую основную реплику и перенаправляет запись запросов на нее).

Каждый экземпляр в группе доступности может быть либо первичный (основной), либо вторичный (вторичный). Вторичные реплики могут быть либо в только для чтения , либо в режиме Нет восстановления .Каждый экземпляр в группе доступности содержит в себе копии баз данных группы доступности. Имейте в виду, что в синхронном режиме скорости транзакций будет зависеть от самого «медленного» участника группы доступности.

В настройке Always On прост, после установки SQL Server всё можно настроить с помощью мастера (WSFC через оснастку в Windows, а сами группы доступности через мастер в SSMS). Но при большом количестве серверов и сложной инфраструктуры придется изучить документацию.

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

Более подробно мы о группах доступности Always On в SQL Server читайте в статье.

SQL Server предоставляет много разнообразных решений для обеспечения высокой доступности данных. При наличии Enterprise редакции и SQL Server 2012 (и выше) лучше использовать Always On.Репликацию можно использовать для разгрузки OLTP систем, выберите запросы и для частичного избыточности (хотя одноранговая репликация позиционируется как полноценное средство избыточности данных). Поставку журналов транзакций и зеркалирование базовых данных можно использовать в более старых версиях SQL Server или если вынуждают использовать именно эти технологии.

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

.

Установка Microsoft SQL Server 2019

Инструкция по установке Microsoft SQL Server 2019, чтобы не забыть. Будем устанавливать Microsoft SQL Server 2019 на Windows Server 2016.

Бесплатная книга «Знакомство с Microsoft SQL Server 2019»:
https://info.microsoft.com/ww-landing-introduction-sql-server-2019-content.html

Редакции MS SQL Server 2019

SQL Server 2019 Editions Datasheet.pdf

• Экспресс — бесплатная версия
  • до 4 ядер CPU
  • до 1 ГБ памяти
  • размер базы до 10 ГБ
• Web — бесплатная версия
  • до 16 ядер CPU
  • до 64 ГБ памяти
• Standard — не все функции, может быть лицензирована по моделям SERVER + CAL или PER CORE
  • до 24 ЦП
  • до 128 ГБ памяти
• Enterprise — включает в себя все возможные функции и компоненты, может быть лицензирована только по моделям PER CORE
• Developer — бесплатная версия с опциями Enterprise
  • для некоммерческого использования
  • для разработчиков и тестировщиков
• Evaluation — бесплатная пробная версия с опциями Enterprise
  • 180 дней пробный период

Лицензирование MS SQL Server 2019

• НА ЯДРО — по ядрам сервера
• SERVER + CAL — на сервер + каждого пользователя
• Есть возможность лицензирования контейнеров, виртуальных машин и кластеров Big Data

Требования к установке

Основные требования:

• Нельзя установить MS SQL Server 2019 на диски со сжатием, сетевые диски и диски, доступные только для чтения.
• Нельзя монтировать дистрибутив через удалённый рабочий стол, вам нужно скачать его на сервер перед началом установки.
• Операционная система:
  • Windows 10 Th2 1507 или более поздней версии
  • Windows Server 2016 или более поздней версии

Дополнительные требования к установке можно найти здесь:

https://docs.microsoft.com/ru-ru/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-ver15?view=sql-server-ver15

Момент, на который стоит обратить внимание:

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

Иначе получим ошибку:

MSSQL Server — ошибка Не удается найти лицензионное соглашение SQL Server для выбранного выпуска

Операционная система

Минимальная версия серверной операционной системы для MS SQL Server 2019 — Windows Server 2016. Подготовлю виртуальную машину с этой операционной системой.

Создаю виртуалку. Я использую VMware.

Выбираю конфигурацию оборудования для Windows Server 2016.

Выделю для сервера 8 CPU, 16 Гб оперативной памяти. Диск 200 Гигабайт. Установку операционной системы здесь не рассматриваю, для этого у меня уже была статья:

ESXi 6.7 — установка Windows Server 2016 на виртуальную машину

Дистрибутив для установки MS SQL Server 2019

Качаем дистрибутив для установки пробной версии:

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2019

Скачиваем SQL2019-SSEI-Eval.exe .

Запускаем.

Выбираю вариант «Скачать носитель».

Выбираем язык. Язык должен соответствовать языку операционной системы Windows Server 2016.

У меня операционная система на английском языке, поэтому выбираю «Английский». Скачиваем ISO образ установщика.

Ожидаем.

Скачивание успешно выполнено. Получаем файл SQLServer2019-x64-ENU.iso .

Установка MS SQL Server 2019

Монтирую ISO образ установщика.

Запускаю setup.exe под администратором. Открывается Центр установки SQL Server.

Слева выбираем пункт Монтаж .

Нажимаю Новая автономная установка SQL Server или добавление функций к существующей установке .

В разделе Ключ продукта нас просят выбрать тип бесплатной установки или указать ключ продукта.Доступны бесплатные версии:

• Оценка
• Разработчик
• Экспресс

Для установки Standard или Enterprise введите соответствующий ключ. В процессе установки содержимого пунктов слева будет меняться. Следующий.

В разделе Условия лицензии принимаем лицензионное соглашение. Следующий.

Раздел Глобальные правила я как-то проскочил, здесь проходят проверку перед установкой. Если проблем не найдено, то мы попадаем в следующий раздел.

Раздел Обновления продукта тоже проскочил, здесь можно включить поиск обновлений через Центр обновления Windows. Я предпочитаю устанавливать сервис-паки, этот раздел мне не нужен.

В разделе Install Setup Files подготавливаются файлы для установки.

Автоматически попадаем в раздел Правила установки . Снова идут проверки установки. У меня есть предупреждение, читаю.

Инсталлятору не нравится Брандмауэр Windows.Предупреждают, что у пользователей может не работать доступ, но с этим я потом разберусь. В порядке. Следующий.

В разделе Выбор функций нужно выбрать нужные нам компоненты. Я выбираю минимум — движок, именно Службы ядра СУБД .

И выбираю фичи совместимости: Возможности подключения клиентских инструментов , Обратная совместимость клиентских инструментов . Пути для установки оставляю без изменений. Следующий.

Про некоторые компоненты

• Database Engine Services — движок SQL Server.
• SQL Server Replication — службы репликации.
• Machine Learning Services и языковые расширения — поддержка скрипта R / Python / Java в SQL Server. Машинное обучение.
• Полнотекстовые и семантические извлечения для поиска — полнотекстовый и семантический поиск.
• Data Quality Services — управление качеством данных, DQS.
• PolyBase Query Service For External Data — доступ к данным, SQL Server или Oracle Database.
• Analysis Services — аналитика (SSAS).Бизнес отчеты (BI), кубы OLAP.
• Machine Learning Server (Standalone) — Машинное обучение без движка SQL Server.
• Data Quality Client — DQS без движка SQL Server.
• Client Tools Connectivity — ODBC, OLE DB и некоторые другие.
• Integration Services — службы интеграции данных (SSIS).

Обратная совместимость клиентских средств

• — устаревшие DMV и системные процедуры.
• Client Tools SDK — пакет для разработчиков.
• Контроллер / клиент распределенного воспроизведения — улучшенный SQL Server Profiler.Для моделирования нагрузки и тестирования производительности.
• SQL Client Connectivity SDK — ODBC / OLE DB SDK для разработчиков.
• Master Data Services — компонент (MDS) из Business Intelligence.

В разделе Правила функции Идёт проверка на наличие возможности установки выбранных компонентов.

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

В разделе Конфигурация сервера во вкладке Учетные записи служб меняю пользователя под которым будет запускаться SQL Server и Агент SQL Server . Тип запуска агента меняю на Автомат .

Обратите внимание на галку Grand Perform Volume Maintenance Task для службы ядра СУБД SQL Server. Полезная штука для оптимизации производительности. Настройка влияет на файл данных.Когда файл автоматически вырастает, то новый кусок заполняется нулями, в этот момент SQL может тормозить. Мгновенная инициализация файла (IFI) позволяет отключить это зануление.

Эту опцию я уже использовал для оптимизации SQL Server 2014:

Настройка MSSQL для работы с 1С — мифы и реальность

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

Переходим в раздел Конфигурация ядра СУБД , здесь много вкладок.

Вкладка Конфигурация сервера . Authentication Mode — меняю на Mixed Mode, задаю пароль для пользователя sa . Здесь же кнопкой Добавить текущее использование r делаю свою учётку админом БД.

Вкладка Справочники данных . Здесь можно и иногда нужно изменить пути к файлам. Я оставляю по умолчанию.

Вкладка TempDB . Здесь настраиваются параметры для базы tempdb . Автоматические параметры нормальные.Я оставляю без изменений.

Вкладка MaxDOP . Настраиваем количество параллельных потоков для выполнения запросов. Автоматические параметры зависят от количества процессоров вашего сервера. У меня 8 процессоров, инсталлятор 8 параллельных потоков. 0 — отключает настройку, при этом SQL Server будет использовать все ядра.

Вкладка Память . Выделяем память для SQL Server. Я 4 Гб ОЗУ оставляю операционную систему, а остальное отдаю SQL серверу.

Вкладка FILESTREAM . Здесь можно при желании включить эту поддержку.

FILESTREAM позволяет приложениям на основе SQL Server хранить в файловой системе неструктурированные данные, например документы и изображения. Приложения могут использовать одновременно многопоточные API-интерфейсы и производительность файловой системы, тем самым предоставленными транзакциями согласованность между неструктурированными и предоставленными им структурированными данными.

Далее.

В разделе Правила настройки функций снова идут проверки. Следующий.

Попадаем в раздел Готово к установке . Проверяю что всё верно. Установить.

Начинается установка SQL Server 2019.

Установка SQL Server 2019 успешно завершена. Закрыть.

Средства управления SQL Server

Для управления SQL сервером нам понадобится Средства управления SQL Server (SSMS).Приложение SSMS можно установить на тот же сервер, что и SQL Server 2019, однако, иногда удобнее выполнить установку на компьютер системного администратора, DBA или разработчика.

Сейчас это отдельный продукт, который не входит в пакет установки SQL Server 2019. Однако в инсталляторе SQL Server 2019 есть ссылка.

Ссылка ведёт на сайт:

https://docs.microsoft.com/ru-ru/sql/ssms/download-sql-server-management-studio-ssms

Скроллим вниз.

Нажимаем «Скачайте SQL Server Management Studio (SSMS)». При этом скачается версия для вашего языка. Если поискать на страничке, то можно найти ссылки на версию SSMS на других языках. Я скачиваю английскую версию SSMS-Setup-ENU.exe .

Запускаю SSMS-Setup-ENU.exe.

Выбираю путь установки, Install.

Загружается пакет.

Устанавливаются необходимые компоненты.

Начинается установка SSMS.

Установка SQL Server Management Studio завершена, требуется перезагрузка. Перезагружаю сервер — Перезагрузить.

Установка завершена.

Проверка работы SQL Server 2019

Запускаю SQL Server Management Studio.

Подключаюсь к SQL серверу под sa.

Установлена ​​версия SQL Server 15.0.2000.5.

.