Перенос базы данных Access на SQL Server

Применяется к

Access для Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

У всех нас есть ограничения, и база данных Access не является исключением. Например, база данных Access имеет максимальный размер в 2 ГБ и не может поддерживать более 255 одновременных пользователей. Таким образом, когда вам потребуется перевести базу данных Access на следующий уровень, можно будет перейти на SQL Server. SQL Server (как локальный, так и в облаке Azure) поддерживает большие объемы данных, больше одновременно работающих пользователей и имеет большую емкость, чем ядро СУБД JET/ACE. Это руководство поможет вам легко приступить к работе с SQL Server, сохранить созданные вами решения передней части Access и, как мы надеемся, мотивирует вас использовать Access для будущих решений баз данных. Для успешной миграции используйте помощника по миграции Microsoft SQL Server (SSMA) для успешной миграции, выполните следующие действия.

Этапы миграции базы данных на SQL Server

Подготовка

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

Сведения о разделенных базах данных

Все объекты базы данных Access могут находиться либо в одном файле базы данных, либо в двух файлах базы данных: внешней и внутренней. Это называется разделением базы данных и предназначено для облегчения совместного использования в сетевой среде. Файл внутренней базы данных должен содержать только таблицы и отношения. Файл переднего плана должен содержать только все остальные объекты, включая формы, отчеты, запросы, макросы, модули VBA и связанные таблицы с внутренней базой данных. Перенос базы данных Access похож на разделенную базу данных, в которой SQL Server действует как новая серверная часть для данных, которые теперь находятся на сервере.

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

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

Вам все еще нужно немного убедить для перехода на SQL Server? Вот некоторые дополнительные преимущества, о которых стоит подумать:

Больше одновременно работающих пользователей. SQL Server может обслуживать гораздо больше одновременно работающих пользователей, чем Access, и минимизирует требования к памяти при добавлении дополнительных пользователей.
Повышенная доступность В SQL Server можно динамически создавать резервное копирование, добавочное или завершенное, базы данных во время ее использования. Следовательно, вам не нужно заставлять пользователей выходить из базы данных для резервного копирования данных.
Высокая производительность и масштабируемость Как правило, база данных SQL Server работает эффективнее, чем база данных Access, особенно при работе с большими терабайтными базами данных. Кроме того, SQL Server обрабатывает запросы намного быстрее и эффективнее, обрабатывая запросы параллельно, используя несколько собственных потоков в одном процессе для обработки запросов пользователей.
Повышенная безопасность Используя доверенное подключение, SQL Server интегрируется с системой безопасности Windows, чтобы обеспечить единый интегрированный доступ к сети и базе данных, используя лучшие качества обеих систем безопасности. Это значительно упрощает администрирование сложных схем безопасности. SQL Server является идеальным хранилищем для конфиденциальной информации, такой как номера социального страхования, данные кредитных карт и конфиденциальные адреса.
Возможность немедленного восстановления В случае сбоя операционной системы или отключения питания SQL Server может автоматически восстановить базу данных до согласованного состояния за считанные минуты и без вмешательства администратора базы данных.
Использование VPN Доступ и виртуальные частные сети (VPN) не ладят друг с другом. Но с помощью SQL Server удаленные пользователи могут использовать интерфейсную базу данных Access на настольном компьютере и серверную часть SQL Server, расположенную за брандмауэром VPN.
Azure SQL Server Помимо преимуществ SQL Server, предлагает динамическую масштабируемость без простоев, интеллектуальную оптимизацию, глобальную масштабируемость и доступность, устранение затрат на оборудование и снижение затрат на администрирование.

Выбор лучшего варианта Azure SQL Server

При переходе на Azure SQL Server можно выбрать один из трех вариантов, каждый из которых имеет свои преимущества:

Единая база данных/эластичные пулы Этот вариант имеет собственный набор ресурсов, управляемых через сервер базы данных SQL. Одна база данных похожа на автономную базу данных в SQL Server. Вы также можете добавить эластичный пул, который представляет собой коллекцию баз данных с общим набором ресурсов, управляемых через сервер базы данных SQL. Наиболее часто используемые функции SQL Server доступны со встроенными резервными копиями, исправлениями и восстановлением. Но нет гарантированного точного времени обслуживания, и миграция с SQL Server может быть затруднена.
Управляемый экземпляр Этот вариант представляет собой набор системных и пользовательских баз данных с общим набором ресурсов. Управляемый экземпляр похож на экземпляр базы данных SQL Server, который обеспечивает высокую совместимость с локальной системой SQL Server. Управляемый экземпляр имеет встроенные резервные копии, исправления и восстановление, и его легко перенести с SQL Server. Однако существует небольшое количество функций SQL Server, которые недоступны, и нет гарантированного точного времени обслуживания.
Виртуальная машина Azure Этот вариант позволяет запускать SQL Server на виртуальной машине в облаке Azure. У вас есть полный контроль над ядром SQL Server и простой путь миграции. Но нужно управлять резервными копиями, исправлениями и восстановлением.

Дополнительные сведения см. в статьях "Выбор пути миграции баз данных в Azure" и "Что такое Azure SQL?".

Первые шаги

Есть несколько проблем, которые можно решить заранее, чтобы упростить процесс миграции до запуска SSMA:

Добавление индексов таблиц и первичных ключей Убедитесь в том, что каждая таблица Access имеет индекс и первичный ключ. Для SQL Server требуется, чтобы все таблицы имели хотя бы один индекс, а связанная таблица должна иметь первичный ключ, если таблицу можно обновить.
Проверка связей между основными и внешними ключами Убедитесь, что эти связи основаны на полях с согласованными типами и размерами данных. SQL Server не поддерживает соединенные столбцы с различными типами данных и размерами в условиях ограничений внешнего ключа.
Удаление столбца "Вложение" SSMA не выполняет перенос таблиц, содержащих столбец "Вложение".

Перед запуском SSMA сделайте следующее.

Закройте базу данных Access.
Убедитесь, что текущие пользователи, подключенные к базе данных, также закрывают базу данных.
Если база данных имеет .mdb формат файла, то Снимите уровень безопасности на уровне пользователя.
Создайте резервную копию базы данных. Дополнительные сведения см. в статье "Защита данных с помощью резервного копирования и восстановления".

Совет Рассмотрите возможность установки на свой компьютер Microsoft SQL Server Express Edition, который поддерживает до 10 ГБ и является бесплатным и простым способом выполнения и проверки миграции. При подключении используйте LocalDB в качестве экземпляра базы данных.

Совет По возможности используйте отдельную версию Access.

Запуск SSMA

Корпорация Майкрософт предоставляет Помощник по миграции Microsoft SQL Server (SSMA), чтобы упростить миграцию. SSMA в основном переносит таблицы и запросы на выборку без параметров. Формы, отчеты, макросы и модули VBA не преобразуются. Обозреватель метаданных SQL Server отображает объекты базы данных Access и объекты SQL Server, позволяя просматривать текущее содержимое обеих баз данных. Эти две связи сохраняются в файле миграции на случай, если вы решите перенести дополнительные объекты в будущем.

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

Чтобы перенести базу данных с помощью SSMA, сначала скачайте и установите программное обеспечение, дважды щелкнув скачанный MSI-файл. Установите соответствующую 32- или 64-разрядную версию для вашего компьютера.
После установки SSMA откройте приложение на рабочем столе, желательно с компьютера с файлом базы данных Access.
Его также можно открыть на компьютере, имеющем доступ к базе данных Access из сети в общей папке.
Следуйте начальным инструкциям в SSMA, чтобы предоставить основные сведения, такие как расположение SQL Server, база данных Access и объекты для переноса, сведения о подключении и необходимость создания связанных таблиц.
Если вы переходите на SQL Server 2016 или более поздней версии и хотите обновить связанную таблицу, добавьте столбец rowversion, выбрав "Средства> рецензирования"Общие параметры> проекта.
Поле rowversion помогает избежать конфликтов записей. Access использует это поле rowversion в связанной таблице SQL Server, чтобы определить, когда запись обновлялась в последний раз. Кроме того, при добавлении поля rowversion в запрос Access повторно выделяет строку после операции обновления. Это повышает эффективность, помогая избежать конфликтов записи и сценариев удаления записей, которые могут произойти, когда Access обнаруживает результаты, отличные от исходной отправки, например с числовыми типами данных с плавающей запятой и триггерами, изменяющими столбцы. Однако не следует использовать поле rowversion в формах, отчетах или коде VBA. Дополнительные сведения см. в разделе rowversion.
Примечание Не путайте rowversion с метками времени. Хотя метка времени ключевого слова является синонимом rowversion в SQL Server, вы не можете использовать rowversion как способ добавления метки времени для ввода данных.
Чтобы установить точные типы данных, выберите "Средства рецензирования", "Параметры> проекта", "Сопоставление> типов". Например, если вы храните только текст на английском языке, можно использовать тип данных varchar вместо nvarchar .

Преобразование объектов

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

Таблицы и столбцы
Выберите Запросы без параметров.
Первичные и внешние ключи
Индексы и значения по умолчанию
Проверяемые ограничения (разрешить свойство столбца нулевой длины, правило проверки столбца, проверка таблицы)

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

При преобразовании объектов базы данных определения объектов извлекаются из метаданных Access, преобразуются в эквивалентный синтаксис Transact-SQL (T-SQL), а затем эти сведения загружаются в проект. Затем вы можете просмотреть объекты SQL Server или SQL Azure и их свойства с помощью обозревателя метаданных SQL Server или SQL Azure.

Для преобразования, загрузки и переноса объектов на SQL Server следуйте этому руководству.

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

Связывание таблиц

Рассмотрите возможность установки последней версии драйверов OLE DB и ODBC SQL Server вместо использования собственных драйверов SQL Server, поставляемых с Windows. Новые драйверы не только работают быстрее, но и поддерживают новые функции Azure SQL, которых не было в предыдущих драйверах. Драйверы можно установить на каждом компьютере, где используется преобразованная база данных. Дополнительные сведения см. в статьях Драйвер Microsoft OLE DB 18 для SQL Server и Драйвер Microsoft ODBC 17 для SQL Server.

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

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

Дополнительные сведения см. в разделах Связывание или импорт данных из базы данных Azure SQL Server и Импорт или связывание данных из базы данных SQL Server.

Совет Не забудьте использовать диспетчер связанных таблиц в Access для удобного обновления таблиц и повторного связывания с ними. Дополнительные сведения см. в статье Управление связанными таблицами.

Протестируйте и отредактируйте

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

Запросы

Преобразуются только запросы на выборку; другие запросы таковой не являются, включая запросы выборки, принимающие параметры. Некоторые запросы могут не полностью преобразоваться, и SSMA сообщает об ошибках запросов в процессе преобразования. Объекты, которые не преобразуются, можно вручную редактировать с помощью синтаксиса T-SQL. Синтаксические ошибки также могут потребовать ручного преобразования функций и типов данных Access в функции SQL Server. Дополнительные сведения см. в статье Сравнение языков Access SQL и SQL Server TSQL.

Типы данных

Access и SQL Server имеют схожие типы данных, но следует помнить о следующих потенциальных проблемах.

Bigint Тип данных "Bigint" хранит неденежные числовые значения и совместим с типом данных bigint SQL. Этот тип данных можно использовать для эффективного вычисления больших чисел, но для него требуется формат файла базы данных ACCDB Access 16 (16.0.7812 или более поздней версии). Он также лучше работает в 64-разрядной версии Access. Дополнительные сведения см. в статье "Использование типа данных bigint " и "Выбор 64- или 32-разрядной версии Office".

Да/Нет По умолчанию столбец Access "Да" или "Нет" преобразуется в битовое поле SQL Server. Чтобы избежать блокировки записи, убедитесь, что битовое поле не допускает значений NULL. В SSMA можно выбрать столбец битов, чтобы задать свойству Allow Nulls значение NO. В TSQL используйте инструкции CREATE TABLE или ALTER TABLE .

Дата и время Существует несколько факторов, связанных с датой и временем:

Если уровень совместимости базы данных 130 (SQL Server 2016) или выше, а связанная таблица содержит один или несколько столбцов datetime или datetime2, таблица может вернуть #deleted сообщения в результатах. Дополнительные сведения см. в статье Возврат связанной таблицы SQL-Server базы данных #deleted.
Используйте тип данных "Дата и время" в Access для сопоставления с типом данных "Дата и время". Используйте тип данных "Дата и время (расширенный)" в Access для сопоставления с типом данных datetime2 , который содержит более широкий диапазон дат и времени. Дополнительные сведения см . в статье Использование типа данных Date/Time Extended.
При запросе дат в SQL Server учитывайте не только дату, но и время. Например:
- ДатаЗаказано в период с 01.01.2019 по 31.01.2019 может включать не все заказы.
- ДатаЗаказано между 01.01.2019 00:00:00 и 31.01.19 23:59:59 включает все заказы.

Вложение Тип данных "Вложение" хранит файл в базе данных Access. В SQL Server необходимо рассмотреть несколько вариантов. Вы можете извлечь файлы из базы данных Access, а затем рассмотреть возможность сохранения связей с файлами в базе данных SQL Server. Кроме того, для хранения вложений в базе данных SQL Server можно использовать FILESTREAM, FileTables или удаленное хранилище BLOB-объектов (RBS).

Гиперссылка Таблицы Access содержат столбцы гиперссылок, которые не поддерживаются в SQL Server. По умолчанию эти столбцы будут преобразованы в столбцы nvarchar(max) в SQL Server, но вы можете настроить сопоставление, выбрав меньший тип данных. В решении Access поведение гиперссылок в формах и отчетах по-прежнему можно использовать, если для свойства " Гиперссылка " элемента управления задано значение true.

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

Дополнительные сведения о сопоставлении типов данных Access и SQL Server см. в статье Сравнение типов данных.

Примечание Многозначные поля не преобразуются.

Дополнительные сведения см. в статьях Типы даты и времени, Строковые и двоичные типы и Числовые типы.

Visual Basic

Хотя VBA не поддерживается SQL Server, обратите внимание на следующие возможные проблемы:

Функции VBA в запросах Запросы Access поддерживают функции VBA для данных в столбце запроса. Но запросы Access, использующие функции VBA, не могут выполняться на сервере SQL Server, поэтому все запрашиваемые данные передаются в Microsoft Access для обработки. В большинстве случаев эти запросы следует преобразовать в запросы к серверу.

Пользовательские функции в запросах Запросы Microsoft Access поддерживают использование функций, определенных в модулях VBA, для обработки передаваемых в них данных. Запросы могут быть автономными, операторами SQL в источниках записей форм и отчетов, источниками данных в виде полей со списками и форм, отчетов и полей таблиц, выражениями по умолчанию или правилами проверки. В SQL Server не удается выполнить эти пользовательские функции. Возможно, потребуется вручную перепроектировать эти функции и преобразовать их в хранимые процедуры на сервере SQL Server.

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

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

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

Оптимизация производительности в модели базы данных клиент-сервер Дополнительные сведения см. в статье Создание запроса к серверу.

Ниже приведены дополнительные рекомендуемые рекомендации.

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

Использование представлений в формах и отчетах В программе Access сделайте следующее:

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

Минимизация загрузки данных в форму или отчет Не отображать данные, пока пользователь не запросит их. Например, оставьте свойство recordsource пустым, попросите пользователей выбрать фильтр в форме, а затем заполните свойство recordsource своим фильтром. Или используйте предложение where в DoCmd.OpenForm и DoCmd.OpenReport для отображения точных записей, необходимых пользователю. Рекомендуется отключить навигацию по записям.

Будьте осторожны с разнородными запросами Избегайте выполнения запроса, объединяющего локальную таблицу Access и связанную таблицу SQL Server. Иногда он называется гибридным запросом. Для этого типа запроса по-прежнему требуется, чтобы Access загрузил все данные SQL Server на локальный компьютер, а затем выполнил запрос. Он не выполняет запрос в SQL Server.

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

Дополнительные сведения см. в статьях "Помощник по настройке ядра СУБД", "Использование анализатора производительности" для оптимизации базы данных Access и "Оптимизация приложений Microsoft Office Access", связанных с SQL Server.