После перенесения данных из программы Access на сервер SQL Server у вас появилась база данных клиента или сервера. База данных может быть локальной или представлять собой гибридное облачное решение Azure. В любом случае программа Access теперь обеспечивает презентацию данных, а SQL Server — их непосредственную обработку. Теперь самое время еще раз рассмотреть разные аспекты вашего решения, особенно производительность запросов, безопасность и непрерывность бизнес-процессов. Это позволит улучшить и масштабировать базу данных.
Если пользователь Access сначала соответствует SQL Server и Azure документации может оказаться сложной. Это руководство поможет вам ознакомиться с основными функциями. Завершив эту экскурсию, вы будете готовы к изучению достижений в области технологий баз данных и отправиться в более длительный путь.
В этой статье
Непрерывность бизнес-процессов
Используя программу Access, вы стремитесь свести до минимума перебои в ее работе, но возможности внутренней базы данных в Access ограничены. Для защиты данных крайне важно создавать резервные копии базы данных Access, но для этого пользователь должен перейти в автономный режим. Кроме этого, случаются незапланированные простои из-за обновления оборудования или программного обеспечения, отказа сети или сбоя питания, неисправности оборудования, нарушения безопасности или даже кибератаки. Чтобы свести к минимуму простои и их последствия для бизнеса, можно создать резервную копию базы данных SQL Server прямо во время ее использования. Кроме того, в программе SQL Server также предусмотрены стратегии высокой доступности (HA) и аварийного восстановления (DR). Сочетание этих двух технологий называется HADR. Дополнительные сведения см. в разделах Непрерывность бизнес-процессов и восстановление базы данных и Обеспечение непрерывности бизнес-процессов с помощью SQL Server (электронная книга).
Резервное копирование во время использования базы данных
Программа SQL Server использует резервное копирование на базе веб-служб, которое проводится во время работы базы данных. Можно сделать полную, частичную резервную копию или резервную копию файла. Чтобы обеспечить полное восстановление, делают резервные копии данных и журналов транзакций. Особенно в случае локальной базы данных важно понимать различия между простой и полной моделью восстановления и то, как это влияет на размер журнала транзакций. Дополнительные сведения см. в разделе Модели восстановления.
Большинство операций резервного копирования происходят сразу же, за исключением управления файлами и сжатия базы данных. С другой стороны, при попытке создать или удалить файл базы данных во время резервного копирования происходит сбой. Дополнительные сведения см. в разделе Обзор резервного копирования.
HADR
Два наиболее распространенных способа достижения высокой доступности и непрерывности бизнес-процессов — это зеркальное отражение и кластеризация. SQL Server интегрирует технологии зеркального отражения и кластеризации с помощью экземпляров отказоустойчивого кластера AlwaysOn и групп доступности AlwaysOn.
Зеркальное отображение обеспечивает на уровне базы данных почти мгновенную отработку отказа. Это возможно с помощью резервной базы данных, полной копии или зеркала активной базы данных на отдельном оборудовании. Сеанс может проходить в синхронном режиме (высокий уровень безопасности), когда входящая транзакция фиксируется на всех серверах одновременно, или в асинхронном (высокая производительность), когда входящая транзакция фиксируется в активной базе данных, а затем в определенной точке копируется в зеркало. Зеркальное отображение работает на уровне баз данных. Оно используется только для баз данных с полной моделью восстановления.
Кластеризация работает на уровне серверов. Она объединяет серверы в единое хранилище данных и воспринимается пользователями как один экземпляр. Пользователи подключаются к этому экземпляру, не беспокоясь о том, какой сервер сейчас активен. Если один сервер выходит из строя или его необходимо отключить для обслуживания, взаимодействие с пользователем не изменится. Диспетчер кластеров контролирует каждый сервер в кластере с помощью пакетов пульса. Когда он обнаруживает, что активный сервер перешел в автономный режим, он оперативно переключается на следующий сервер в кластере. Переключение вызывает задержку разной длительности.
Дополнительные сведения см. в разделе Always On Экземпляры отказоустойчивого кластера и группы доступности Always On: решение для обеспечения высокой доступности и аварийного восстановления.
Безопасность SQL Server
Хотя базу данных Access можно защитить с помощью центра управления безопасностью и шифрования базы данных, сервер SQL Server имеет дополнительные функции безопасности. Давайте рассмотрим три возможности, которые выделяются для пользователя Access. Дополнительные сведения см. в разделе Защита SQL Server.
Проверка подлинности базы данных
SQL Server использует четыре способа проверки подлинности базы данных, любой из которых можно указать в строке подключения ODBC. Дополнительные сведения см. в статье Ссылка на данные или импорт данных из базы данных SQL Server Azure. У каждого из этих способов есть свои преимущества.
Интегрированная проверка подлинности Windows использование учетных данных Windows для проверки пользователей, ролей безопасности и ограничения пользователей функциями и данными. В программе можно воспользоваться учетными данными домена и легко управлять правами пользователей. При необходимости введите имена субъектов-служб (SPN). Дополнительные сведения см. в разделе Выбор режима проверки подлинности.
SQL Server Пользователи проверки подлинности должны подключаться с учетными данными, настроенными в базе данных, введя идентификатор входа и пароль при первом доступе к базе данных в сеансе. Дополнительные сведения см. в разделе Выбор режима проверки подлинности.
Azure встроенная проверка подлинности Active Directory Подключитесь к базе данных сервера Azure SQL с помощью Azure Active Directory. Если настроена проверка подлинности Azure Active Directory, вводить имя пользователя и пароль не нужно. Дополнительные сведения см. в статье Подключение к База данных SQL с помощью Azure проверки подлинности Active Directory.
Проверка подлинности по паролю Active Directory Подключитесь с учетными данными, настроенными в Azure Active Directory, введя имя входа и пароль. Дополнительные сведения см. в статье Подключение к База данных SQL с помощью Azure проверки подлинности Active Directory.
Совет Используйте обнаружение угроз для получения оповещений об аномальных действиях базы данных, указывающих на потенциальные угрозы безопасности для базы данных Azure SQL Server. Дополнительные сведения см. в разделе обнаружение угроз База данных SQL.
Безопасность приложений
SQL Server поддерживает две функции безопасности на уровне приложений, которыми можно воспользоваться в программе Access.
Динамическое маскирование данных Скрытие конфиденциальной информации путем маскирования ее от пользователей, не являющихся привилегированными. Например, можно частично или полностью скрыть номера социального страхования.
. Частичная маска данных |
Полная маска данных |
|---|
Маску данных можно задать несколькими способами. Она применяется к разным типам данных. Маскирование данных выполняется на уровне таблицы и столбца для определенного набора пользователей и применяется к запросу в режиме реального времени. Дополнительные сведения см. в разделе Динамическое маскирование данных.
Безопасность на уровне строк Вы можете управлять доступом к определенным строкам базы данных с конфиденциальной информацией на основе характеристик пользователей с помощью Row-Level Security. Эти ограничения доступа применяются к системе базы данных, благодаря чему система безопасности становится надежнее.
Существует два типа предикатов безопасности:
- Предикат фильтра фильтрует строки из запроса. Фильтр прозрачный, и пользователь не подозревает о его использовании.
- Предикат блока предотвращает несанкционированное действие и создает исключение, если действие не может быть выполнено.
Дополнительные сведения см. в разделе Безопасность на уровне строк.
Защита данных с помощью шифрования
Защищайте данные при хранении, передаче и использовании, не снижая производительности базы данных. Дополнительные сведения см. в разделе шифрование SQL Server.
Шифрование неактивных данных Чтобы защитить персональные данные от атак на автономный носитель на физическом уровне хранилища, используйте шифрование неактивных данных, также называемое прозрачным шифрованием данных (TDE). Тогда ваши данные будут защищены даже в том случае, если физический носитель украден или неправильно уничтожен. Функция TDE выполняет шифровку и расшифровку баз данных, резервных копий и журналов транзакций в режиме реального времени, не внося каких-либо изменений в ваши приложения.
Шифрование при передаче Чтобы защититься от слежки и атак типа "злоумышленник в середине", можно зашифровать данные, передаваемые по сети. SQL Server поддерживает протокол TLS 1.2 для обеспечения высокого уровня безопасности соединения. Протокол TDS также используется для защиты соединения в ненадежных сетях.
Шифрование, используемое на клиенте Чтобы защитить персональные данные во время использования, вы хотите использовать Always Encrypted. Личные данные шифруются и расшифровываются драйвером на клиентском компьютере. Ключи шифрования для ядра СУБД не сообщаются. В результате зашифрованные данные видны только людям, ответственным за управление этими данными, а не другим пользователям с высоким уровнем привилегий, которые не должны иметь доступа. В зависимости от выбранного типа шифрования, функция Always Encrypted может ограничивать некоторые функции базы данных, такие как поиск, группирование и индексирование зашифрованных столбцов.
Соблюдение конфиденциальности
Проблемы с конфиденциальностью настолько распространены, что Европейский Союз определил соответствующие юридические требования в Общем регламенте по защите данных (GDPR). К счастью, серверная часть SQL Server удовлетворяет этим требованиям. Далее описаны три этапа реализации регламента GDPR.
, шаг 1. Оценка рисков соответствия требованиям и управление ими
Регламент GDPR требует, чтобы были найдены личные данные, содержащиеся в таблицах и файлах, и велся их учет. Это могут быть любые из перечисленных ниже данных: имя, фотография, адрес электронной почты, банковские реквизиты, записи в социальных сетях, медицинская информация или даже IP-адрес.
Новое средство обнаружения и классификации данных SQL, встроенное в SQL Server Management Studio, помогает обнаруживать, классифицировать, присваивать метки и создавать отчеты по конфиденциальные данные, применяя два атрибута метаданных к столбцам:
- Метки Определение конфиденциальности данных.
- Типы сведений Для обеспечения дополнительной детализации типов данных, хранящихся в столбце.
Другой механизм обнаружения — это полнотекстовый поиск, при котором используются предикаты CONTAINS и FREETEXT и такие функции со значением набора строк, как CONTAINSTABLE и FREETEXTTABLE, а также инструкция SELECT. С помощью полнотекстового поиска можно искать в таблицах слова, сочетания слов, а также варианты слов, например синонимы или флективные формы. Дополнительные сведения см. в разделе Полнотекстовый поиск.
Шаг 2. Защита личных данных
Регламент GDPR требует защитить личные данные и ограничить доступ к ним. Помимо стандартных действий по управлению доступом к сети и ресурсам, например настройка параметров брандмауэра, можно использовать функции безопасности SQL Server, которые помогут вам управлять доступом к данным.
- Проверка подлинности SQL Server, чтобы управлять удостоверением пользователя и предотвращать несанкционированный доступ.
- Безопасность на уровне строк, чтобы ограничить доступ к строкам в таблице в зависимости от связи между пользователем и данными.
- Динамическое маскирование данных, чтобы ограничить доступ к личным данным, скрыв их от непривилегированных пользователей.
- Шифрование для защиты личных данных во время передачи и хранения и для защиты от компрометации, в том числе на стороне сервера.
Дополнительные сведения см. в статье Безопасность SQL Server.
Шаг 3. Оперативные ответы на запросы
Регламент GDPR требует, чтобы велись записи об обработке личных данных и чтобы они предоставлялись в органы надзора по требованию. Если возникли проблемы, связанные со случайным разглашением данных, элементы управления защитой позволяют быстро отреагировать на них. Если необходимо предоставить отчет, следует обеспечить быстрый доступ к данным. Например, GDPR требует, чтобы о нарушении личных данных сообщалось в надзорный орган "не позднее чем через 72 часа после того, как стало известно об этом".
SQL Server 2017 облегчает вам создание отчетов несколькими способами.
- SQL Server аудит помогает убедиться, что существуют постоянные записи о действиях доступа к базе данных и обработки. Эта функция проводит детальный аудит и отслеживает деятельность в базе данных. Это помогает выявить потенциальные угрозы, предполагаемые нарушения и проблемы безопасности. Вы можете легко провести экспертизу данных.
- Темпоральные таблицы SQL Server — это пользовательские таблицы с системным управлением версиями, которые позволяют сохранить всю историю изменений данных. Их можно использовать, чтобы легко создавать отчеты и проводить анализ на определенный момент времени.
- Оценка уязвимостей SQL помогает обнаруживать проблемы с безопасностью и разрешениями. При обнаружении проблемы вы также можете детализировать отчеты базы данных, чтобы найти решение проблемы.
Дополнительные сведения см. в разделах Создание платформы доверия (электронная книга) и Переход к соответствию GDPR.
Создание моментальных снимков базы данных
Моментальный снимок базы данных — это статическое представление базы данных SQL Server в определенный момент времени, доступное только для чтения. Хотя можно скопировать файл базы данных Access, чтобы оперативно создать моментальный снимок базы данных, в Access не встроена такая технология, как в SQL Server. Моментальный снимок базы данных используется для подготовки отчетов, основанных на данных на момент создания моментального снимка. Кроме того, моментальный снимок базы данных можно использовать для сохранения исторических данных, например за каждый финансовый квартал, для сведения отчетов за отчетный период. Советуем следовать таким рекомендациям:
- Назовите snapshot Каждому snapshot базы данных требуется уникальное имя базы данных. Чтобы облегчить идентификацию, добавьте к имени назначение и временные рамки. Например, создайте три моментальных снимка базы данных AdventureWorks за день с 06:00 до 18:00 (24-часовой формат времени) с интервалом 6 часов и присвойте им такие имена: AdventureWorks_snapshot_0600, AdventureWorks_snapshot_1200 и AdventureWorks_snapshot_1800.
- Ограничение количества моментальных снимков Каждая база данных snapshot сохраняется до явного удаления. Так как каждый следующий моментальный снимок будет увеличиваться, может потребоваться сэкономить место на диске, удалив более старый снимок после создания нового. Например, если вы создаете ежедневные отчеты, храните моментальные снимки в течение 24 часов, а затем удаляйте их и заменяйте новыми.
- Подключитесь к правильному snapshot Чтобы использовать snapshot базы данных, интерфейсу Access необходимо знать правильное расположение. Чтобы заменить имеющийся моментальный снимок новым, необходимо перенаправить Access к новому снимку. Чтобы подключиться к нужному моментальному снимку базы данных, добавьте логику во внешний интерфейс Access.
Вот как создать базу данных snapshot.
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_snapshot_0600' )
AS SNAPSHOT OF AdventureWorks;
Дополнительные сведения см. в разделе Моментальные снимки базы данных (SQL Server).
Управление параллелизмом
Когда несколько человек одновременно пытаются изменить данные в базе данных, необходима система управления, чтобы изменения, вносимые одним пользователем, не влияли негативно на изменения, сделанные другим пользователем. Это называется управлением параллелизмом. Имеются две основные стратегии блокировки: пессимистическая блокировка и оптимистическая блокировка. Блокировка не позволяет пользователям изменять данные так, чтобы это влияло на других пользователей. Блокировка также обеспечивает целостность базы данных, особенно в запросах. В противном случае результаты могут быть неожиданными. Между тем, каким образом базы данных Access и SQL Server реализуют эти стратегии управления параллелизмом, есть важные отличия.
В программе Access стратегия блокировки по умолчанию оптимистическая и дает право на блокировку первому пользователю, который делает запись. В Access отображается диалоговое окно Конфликт записи для другого пользователя, старающегося сделать ту же запись в то же время. Чтобы разрешить конфликт, другой пользователь может сохранить запись, скопировать ее в буфер обмена или удалить изменения.
Чтобы изменить стратегию управления параллелизмом, можно также воспользоваться свойствомRecordLocks. Это свойство влияет на формы, отчеты и запросы и имеет три параметра.
- Без блокировок В форме пользователи могут одновременно изменить одну и ту же запись, но может появиться диалоговое окно Конфликт записи . В отчете записи не блокируются во время просмотра или печати. В запросе записи не блокируются во время его выполнения. Так применяется оптимистическая блокировка в программе Access.
- Все записи Все записи в базовой таблице или запросе блокируются при открытии формы в представлении формы или в режиме таблицы, во время предварительного просмотра или печати отчета или во время выполнения запроса. Пользователи могут читать записи во время блокировки.
- Измененная запись Для форм и запросов страница записей блокируется, как только любой пользователь начинает редактировать любое поле в записи и остается заблокированным, пока пользователь не перейдет к другой записи. В результате каждый раз запись может редактировать только один пользователь. Так применяется пессимистическая блокировка в программе Access.
Дополнительные сведения см. в разделах Диалоговое окно "Конфликт записи" и Свойство RecordLocks.
В SQL Server управление параллелизмом выполняется следующим образом:
- Пессимистично После выполнения пользователем действия, которое вызывает блокировку, другие пользователи не могут выполнять действия, конфликтующие с блокировкой, пока владелец не отпустит ее. Такое управление параллелизмом в основном применяется в средах с высокой конкуренцией за использование данных.
- Оптимистично При управлении оптимистическим параллелизмом пользователи не блокируют данные при их чтении. Когда пользователь обновляет данные, система проверяет, не изменил ли их другой пользователь после прочтения. Если другой пользователь обновил данные, отображается ошибка. Увидев ошибку, пользователь обычно откатывает транзакцию и начинает заново. Такое управление параллелизмом в основном применяется в средах с низкой конкуренцией за использование данных.
Чтобы задать тип управления параллелизмом, выберите несколько уровней изоляции транзакции с помощью инструкции SET TRANSACTION. Они определяют уровень защиты транзакции от изменений, сделанных другими транзакциями.
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
| Уровень изоляции | Описание |
|---|---|
| Чтение незафиксированных данных | Транзакции изолированы ровно настолько, чтобы не допустить чтения физически поврежденных данных. |
| Чтение фиксированных данных | Транзакции считывают данные, не ожидая, пока другая транзакция завершит считывание этих данных. |
| Чтение с повтором | Чтение и запись выбранных данных блокируются до конца транзакции, но возможно чтение фантомов. |
| Моментальный снимок | Чтобы обеспечить согласованность чтения на уровне транзакции, используется версия строки. |
| Сериализуемость | Транзакции полностью изолированы друг от друга. |
Дополнительные сведения см. в разделе Руководство по блокировке транзакций и управление версиями строк.
Улучшение производительности запросов
После того как вы работали с запросом к серверу Access, воспользуйтесь прогрессивными средствами SQL Server для более эффективной работы.
В отличие от базы данных Access, SQL Server инициирует параллельные запросы, чтобы оптимизировать выполнение запросов и индексировать операции для компьютеров, на которых установлено несколько микропроцессоров (ЦП). Так как SQL Server может параллельно выполнять запросы и индексировать операции с помощью нескольких системных рабочих потоков, это выполняется быстро и эффективно.
Запросы — это критически важный компонент для повышения общей производительности базы данных. Неправильные запросы выполняются, пока не истечет время ожидания, они расходуют такие ресурсы, как ЦП и память, и действуют, как бандит в сети. Это мешает доступу к критически важной бизнес-информации. Даже один неправильный запрос может вызвать серьезные проблемы с производительностью базы данных.
Дополнительные сведения см. в статье Ускорение запросов с помощью SQL Server (электронная книга).
Оптимизация запросов
Несколько средств работают вместе, чтобы помочь проанализировать производительность запроса и улучшить ее: Оптимизатор запросов, планы выполнения и хранилище запросов.
Оптимизатор запросов
Оптимизатор запросов — один из наиболее важных компонентов SQL Server. Используйте оптимизатор запросов, чтобы проанализировать запрос и определить наиболее эффективный способ доступа к необходимым данным. Входные данные для оптимизатора запросов включают запрос, схему базы данных (определение таблиц и индексов) и статистику базы данных. План выполнения — это результат работы оптимизатора запросов.
Дополнительные сведения см. в разделе Оптимизатор запросов SQL Server.
План выполнения
План выполнения определяет последовательность доступа к исходным таблицам и то, какие методы использовать для извлечения данных из каждой таблицы. Оптимизация — это процесс выбора одного плана выполнения из многих потенциально возможных планов. У каждого из возможных планов выполнения есть сопутствующие затраты в размере стоимости использованных вычислительных ресурсов. Оптимизатор запросов выбирает план с наименьшей оценочной стоимостью.
База данных SQL Server также должна динамически настраиваться с учетом изменяющихся условий. Регрессия в планах выполнения запросов может сильно повлиять на производительность. Изменения, внесенные в базу данных, делают план выполнения неэффективным или недействительным с учетом нового состояния базы данных. SQL Server обнаруживает изменения, которые делают план выполнения недействительным, и помечает план соответствующим образом.
После этого необходимо составить новый план для следующего подключения, которое выполнит запрос. Требования, которые делают план недействительным:
- изменения, внесенные в таблицу или представление, на которые ссылается запрос (ALTER TABLE и ALTER VIEW);
- изменения, внесенные в индексы, используемые планом выполнения;
- обновление статистики, используемой планом выполнения, автоматическое или в ответ на инструкцию UPDATE STATISTICS.
Дополнительные сведения см. в разделе Планы выполнения.
Хранилище запросов
Хранилище запросов дает представление о выборе плана выполнения и производительности. Это упрощает решение проблем с производительностью, помогая быстро находить различия в производительности, вызванные изменениями в плане выполнения. Хранилище запросов собирает данные телеметрии, такие как журнал запросов, планы, статистику выполнения и статистику ожидания. Чтобы реализовать хранилище запросов, используйте инструкцию ALTER DATABASE.
ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;
Дополнительные сведения см. в разделе Мониторинг производительности с помощью хранилище запросов.
Автоматическое исправление плана
Возможно, что самый простой способ улучшить производительность запроса — это автоматическое исправление плана (функция доступна в базе данных SQL Azure). Просто включите эту функцию. Она постоянно проводит мониторинг и анализ плана выполнения, обнаруживает проблемные планы выполнения и автоматически исправляет проблемы с производительностью. Функция автоматического исправления плана использует стратегию, состоящую из четырех этапов (изучение, адаптация, проверка и повторение).
Дополнительные сведения см. в статье Автоматическая настройка.
Адаптивная обработка запросов
Кроме того, запросы выполняются быстрее, если просто обновить программу до версии SQL Server 2017, в которой используется новая функция адаптивной обработки запросов. SQL Server выбирает варианты плана запросов с учетом характеристик среды выполнения.
Оценка количества строк показывает, сколько приблизительно строк обрабатывается на каждом этапе плана выполнения. Неточная оценка может привести к длительному времени ответа на запрос, использованию ресурсов (памяти, ЦП и операции ввода-вывода) без необходимости и снижению пропускной способности и количества параллельных подключений. Чтобы адаптироваться к характеристикам рабочей нагрузки приложений, имеется три способа.
- Отзывы о предоставлении памяти в пакетном режиме Низкие оценки кратности могут привести к тому, что запросы "перетекают на диск" или занимают слишком много памяти. SQL Server 2017 настраивает временно предоставленные буферы памяти в зависимости от количества результатов выполнения запросов и оптимизирует параллелизм для повторных запросов.
- Адаптивные соединения в пакетном режиме Адаптивные соединения динамически выбирают лучший тип внутреннего соединения (соединения вложенных циклов, объединения слиянием или хэш-соединения) во время выполнения на основе фактических входных строк. Поэтому во время выполнения запросов план может динамически переключаться на более подходящие соединения.
- Чередуемое выполнение Функции с табличным значением с несколькими операторами традиционно рассматривались как "черный ящик" при обработке запросов. SQL Server 2017 может лучше оценить количество строк, и это улучшает нисходящие операции.
Если включить для базы данных уровень совместимости 140, рабочая нагрузка автоматически станет пригодной для адаптивной обработки данных.
ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 140;
Дополнительные сведения см. в статье Интеллектуальная обработка запросов в базах данных SQL.
Методы запросов
На сервере SQL Server предусмотрено несколько способов выполнения запросов, и каждый из них имеет свои преимущества. Важно узнать о них больше. чтобы выбрать подходящий вариант для Access. Лучший способ создания запросов TSQL — интерактивно редактировать и тестировать их с помощью редактора Transact-SQL SQL Server Management Studio (SSMS), который содержит intellisense, помогающий выбрать правильные ключевые слова и проверка для синтаксических ошибок.
Представления
В SQL Server представление похоже на виртуальную таблицу, данные которой берутся из одной или нескольких таблиц или других представлений. Но в запросах ссылки на представления такие же, как на таблицы. Представления позволяют скрыть сложность запросов и защитить данные путем ограничения набора строк и столбцов. Вот пример простого представления.
CREATE VIEW HumanResources.EmployeeHireDate AS
SELECT p.FirstName, p.LastName, e.HireDate
FROM HumanResources.Employee AS e JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID;
Чтобы добиться оптимальной производительности и редактировать результаты представления, создайте индексированное представление. Оно хранится в базе данных так же, как таблицы, имеет свое дисковое пространство, и его можно запрашивать так же, как любую таблицу. Чтобы использовать его в Access, подключитесь к нему так же, как к таблице. Вот пример индексированного представления.
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
Однако имеются ограничения. Невозможно обновить данные, если затронуто несколько базовых таблиц или представление содержит агрегатные функции или предложение DISTINCT. Если SQL Server возвращает сообщение об ошибке, так как не знает, какую запись нужно удалить, может потребоваться добавить в представление триггер удаления. Кроме того, в отличие от программы Access для запросов не используется предложение ORDER BY.
Дополнительные сведения см. в разделах Представления и Создание индексированных представлений.
Хранимые процедуры
Хранимая процедура — это группа из одной или нескольких инструкций TSQL, которые принимают входные параметры, возвращают выходные параметры и указывают на успех или отказ с помощью значения состояния. Они служат промежуточным уровнем между внешним интерфейсом Access и серверной частью SQL Server. Хранимые процедуры могут быть простыми, как инструкция SELECT, или сложными, как любое приложение. Вот пример.
CREATE PROCEDURE HumanResources.uspGetEmployees
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName
AND EndDate IS NULL;
Если хранимая процедура используется в Access, она обычно возвращает результат в виде формы или отчета. Однако он может выполнять другие действия, которые не возвращают результаты, например инструкции DDL или DML. При использовании проходного запроса убедитесь, что вы правильно установили свойство Returns Records.
Дополнительные сведения см. в разделе Хранимые процедуры.
Обобщенные табличные выражения
Обобщенные табличные выражения (CTE) похожи на временную таблицу, в которой создается результирующий именованный набор. Такое выражение используется для выполнения только одного запроса или инструкции DML. Выражение CTE встроено в ту же строку кода, что и инструкция SELECT или DML, в которой оно используется, тогда как создание и использование временной таблицы или представления обычно выполняется в два этапа. Вот пример.
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
Выражение CTE имеет несколько указанных ниже преимуществ.
- Поскольку CTA являются временными, их не нужно создавать в качестве постоянных объектов базы данных, таких как представления.
- В запросе или инструкции DML можно ссылаться на одно и то же выражение CTE несколько раз. Это упрощает управление кодом.
- Чтобы определить курсор, можно использовать запросы с ссылкой на выражение CTE.
Дополнительные сведения см. в разделе WITH common_table_expression.
Пользовательские функции
Пользовательские функции (UDF) могут выполнять запросы и вычисления и возвращать либо скалярные значения, либо результирующие наборы данных. Они похожи на функции в языках программирования, которые принимают параметры, выполняют действие, например сложные вычисления, и возвращают результат действия в виде значения. Вот пример.
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int WITH SCHEMABINDING -- Helps improve performance
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek int;
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
-- Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
-- Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1;
RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';
Функция UDF имеет определенные ограничения. Например, они не могут использовать некоторые недетерминированные системные функции, выполнять инструкции DML или DDL или выполнять динамические SQL-запросы.
Дополнительные сведения см. в разделе Определяемые пользователем функции.
Добавление ключей и индексов
В любой системе баз данных есть ключи и индексы.
Клавиши
В SQL Server необходимо создать первичные ключи для каждой таблицы и внешние ключи для каждой связанной таблицы. В SQL Server имеется функция, аналогичная функции типа данных для поля счетчика в Access. Это свойство Identity, которое используется для создания значений ключей. Если применить это свойство к какому-либо числовому столбцу, он становится доступным только для чтения и сохраняется системой баз данных. Если вставить запись в таблицу, содержащую столбец идентификаторов, система автоматически увеличит значение столбца на 1 и начиная с 1, но этими значениями можно управлять с помощью аргументов.
Дополнительные сведения см. в разделе CREATE TABLE, IDENTITY (Property).
Индексы
Как всегда, выбор индексов — это поиски оптимального соотношения между скоростью выполнения запроса и стоимостью обновления. В программе Access имеется один тип индекса, а в SQL Server — двенадцать. К счастью, с помощью оптимизатора запросов можно с уверенностью выбрать самый эффективный индекс. В SQL Azure можно воспользоваться автоматическим управлением индексами. Это функция автоматической настройки, которая рекомендует вам добавить или удалить индексы. В отличие от Access, на сервере SQL Server необходимо создать собственные индексы для внешних ключей. Кроме того, чтобы повысить производительность запроса, можно создавать индексы в индексированном представлении. Недостатком индексированного представления является увеличение накладных расходов при изменении данных в базовых таблицах представления, так как представление также должно быть обновлено. Дополнительные сведения см. в статье Руководство по архитектуре и проектированию индексов SQL Server и индексы.
Транзакции
Выполнять оперативную обработку транзакций (OLTP) трудно с программой Access, но довольно просто с SQL Server. Транзакция — это единица работы с данными, которая фиксирует все изменения данных в случае успеха и откатывает их в случае отказа. У транзакции есть четыре свойства, часто называемые ACID.
- Атомарность Транзакция должна быть атомарной единицей работы; либо все изменения данных выполняются, либо не выполняются.
- Согласованности По завершении транзакция должна оставить все данные в согласованном состоянии. Это означает, что применяются все правила целостности данных.
- Изоляции Изменения, внесенные параллельными транзакциями, изолированы от текущей транзакции.
- Прочность После завершения транзакции изменения становятся постоянными даже в случае сбоя системы.
Транзакция используется для обеспечения гарантированной целостности данных, например при снятии наличности в банкомате или автоматическом переводе зарплаты на счет. Транзакции бывают явными, неявными и пакетными. Вот два примера TSQL.
-- Using an explicit transaction
BEGIN TRANSACTION;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
COMMIT;
-- the ROLLBACK statement rolls back the INSERT statement, but the created table still exists.
CREATE TABLE ValueTable (id int);
BEGIN TRANSACTION;
INSERT INTO ValueTable VALUES(1);
INSERT INTO ValueTable VALUES(2);
ROLLBACK;
Дополнительные сведения см. в разделе Транзакции.
Ограничения и триггеры
Во всех базах данных есть способы сохранения целостности данных.
Ограничения
В программе Access целостность данных на уровне таблиц обеспечивается с помощью пар ключей (внешний-первичный), каскадного обновления и удаления и правил проверки. Дополнительные сведения смотрите в публикациях Руководство по связям между таблицами и Ограничение ввода данных с помощью правил проверки.
В SQL Server используются ограничения UNIQUE и CHECK. Это объекты базы данных, обеспечивающие целостность данных в таблицах SQL Server. Чтобы проверить, допустимо ли значение в другой таблице, используйте ограничение внешнего ключа. Чтобы проверить, находится ли значение в столбце в пределах определенного диапазона, используйте проверочное ограничение. Такие объекты — первая линия обороны, и они предназначены для эффективной работы. Дополнительные сведения см. в разделах Ограничения уникальных данных и Контрольные ограничения.
Триггеры
В программе Access отсутствуют триггеры базы данных. В SQL Server можно использовать триггеры, чтобы выполнять правила целостности сложных данных и эту бизнес-логику. Триггер базы данных — это хранимая процедура, которая выполняется при определенных действиях в базе данных. Триггер — это событие, например добавление или удаление записи в таблице, которое запускает и выполняет хранимую процедуру. Хотя база данных Access может обеспечить целостность данных, когда пользователь обновляет или удаляет данные, сервер SQL Server поддерживает усовершенствованный набор триггеров. Например, можно запрограммировать триггеры, чтобы массово удалить записи и обеспечить целостность данных. Триггеры даже можно добавлять в таблицы и представления.
Дополнительные сведения см. в разделах Триггеры — DML, Триггеры — DDL и Проектирование триггера T-SQL.
Вычисляемые столбцы
В программе Access можно создать вычисляемый столбец, если добавить его в запрос и построить выражение, например:
Extended Price: [Quantity] * [Unit Price]
Эквивалентная функция в SQL Server называется вычисляемый столбец. Это виртуальный столбец, который физически не сохраняется в таблице, если он не помечен как PERSISTED. Как и вычисляемый столбец в программе Access, он использует в выражении данные из других столбцов. Чтобы создать вычисляемый столбец, добавьте его в таблицу. Пример.
CREATE TABLE dbo.Products
(
ProductID int IDENTITY (1,1) NOT NULL
, QtyAvailable smallint
, UnitPrice money
, InventoryValue AS QtyAvailable * UnitPrice
);
Дополнительные сведения см . в разделе Указание вычисляемых столбцов в таблице.
Добавление метки времени
При создании записи иногда можно добавить поле таблицы для метки времени, чтобы вести журнал ввода данных. В Access можно просто создать столбец даты со значением =Now()по умолчанию . Чтобы записать дату или время в SQL Server, используйте тип данных datetime2 со значением SYSDATETIME()по умолчанию .
Примечание Избегайте путаницы при добавлении метки времени в данные. Ключевое слово timestamp — это синоним rowversion в SQL Server, но использовать следует ключевое слово rowversion. В SQL Server столбец rowversion — это тип данных, который представляет собой автоматически созданные уникальные двоичные числа в базе данных. Он обычно используется для добавления штампа версии строк в таблице. Тип данных rowversion — это только увеличивающееся число, которое не сохраняет дату и время и не предназначено для добавления метки времени строки.
Дополнительные сведения см. в разделе rowversion. Дополнительные сведения о том, как использовать rowversion, чтобы минимизировать конфликты записи, см. в статье Миграция базы данных Access на сервер SQL Server.
Управление большими объектами
В Access вы управляете неструктурированными данными, такими как файлы, фотографии и изображения, используя тип данных вложения. Согласно терминологии SQL Server, неструктурированные данные называются BLOB-объектами (большими двоичными объектами), и с ними можно работать несколькими способами.
FILESTREAM Использует тип данных varbinary(max) для хранения неструктурированных данных в файловой системе, а не в базе данных. Дополнительные сведения см. в статье Доступ к данным FILESTREAM с помощью Transact-SQL.
Filetable Большие двоичные объекты хранятся в специальных таблицах FileTables и обеспечивают совместимость с приложениями Windows, как если бы они хранились в файловой системе, и без внесения изменений в клиентские приложения. В таблицах FileTable используется FILESTREAM. Дополнительные сведения см. в разделе FileTables.
Удаленное хранилище BLOB-объектов (RBS) Хранит большие двоичные объекты (BLOB-объекты) в решениях для хранилища товаров, а не непосредственно на сервере. Это позволяет сэкономить место и аппаратные ресурсы. Дополнительные сведения см. в разделе Данные больших двоичных объектов (BLOB-объектов).
Работа с иерархическими данными
Хотя реляционные базы данных, такие как Access, очень гибкие, работа с иерархическими отношениями — это исключение, часто требующее сложные инструкции или код SQL. Примеры иерархических данных: организационная структура, файловая система, таксономия языковых терминов и график связей между веб-страницами. Чтобы легко хранить и запрашивать иерархические данные, а также управлять ими, SQL Server имеет встроенный тип данных hierarchyid и набор иерархических функций.
Дополнительные сведения см. в разделах Иерархические данные и Учебник. Использование типа данных hierarchyid.
Работа с текстом JSON
Нотация объектов JavaScript (JSON) — это веб-служба, которая использует удобочитаемый текст для передачи данных в виде пар “атрибут-значение” с помощью асинхронной связи “браузер-сервер”. Пример.
{
"firstName": "Mary",
"lastName": "Contrary",
"spouse": null,
"age": 27
}
В Access нет встроенных способов управления данными JSON, но в SQL Server вы можете без проблем хранить, индексировать, запрашивать и извлекать данные JSON. Текст JSON можно преобразовать в таблицу и сохранить, а можно форматировать данные непосредственно в тексте JSON. Например, вам может потребоваться отформатировать результаты запроса в формате JSON для веб-приложения или добавить структуры данных JSON в строки и столбцы.
Примечание JSON не поддерживается в VBA. В качестве альтернативы можно использовать формат XML в VBA с помощью библиотеки MSXML.
Дополнительные сведения см. в статье Данные JSON в SQL Server.
Ресурсы
Теперь самое время больше узнать о SQL Server и языке Transact SQL (TSQL). Как вы уже видели, существует множество функций, таких как Access, но и возможности Access просто не имеют. Чтобы расширить свои знания, вы можете ознакомиться с приведенными ниже ресурсами.
| Ресурс | Описание |
|---|---|
| Выполнение запросов с помощью Transact-SQL | Видеокурс |
| Учебники по ядру СУБД | Практические занятия по использованию SQL Server 2017 |
| Microsoft Learn | Учебный курс по использованию Azure |
| обучение и сертификация SQL Server | Станьте экспертом |
| SQL Server 2017 | Основная целевая страница |
| Документация по SQL Server | Справочная информация |
| Документация по базе данных Azure SQL | Справочная информация |
| Основное руководство по данным в облаке (электронная книга) | Общие сведения об облаке |
| Таблица SQL Server 2017 г. | Визуальное представление новых функций |
| Сравнение версии Microsoft SQL Server | Краткий обзор функций по версиям |
| Выпуски Microsoft SQL Server Express | Скачивание SQL Server Express 2017 |
| Примеры баз данных SQL | Скачать примеры баз данных |