Миграция базы данных Access на сервер SQL Server

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

Подготовка

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

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

Все объекты базы данных 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 и Что такое SQL Azure?

Первые шаги

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

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

Перед запуском SSMA выполните следующие первые действия.

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

Совет Рассмотрите возможность установки выпуска Microsoft SQL Server Express на вашем рабочем столе, который поддерживает до 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 с метками времени. Хотя ключевое слово timestamp является синонимом 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 сохраните файл проекта для последующего использования, чтобы снова перенести данные для тестирования или окончательной миграции.

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

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

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

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

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

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

Тестирование и изменение

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

Запросы

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

Типы данных

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

Большое число Тип данных Large Number хранит неденежное числовое значение и совместим с типом данных BIGINT SQL. Этот тип данных можно использовать для эффективного вычисления больших чисел, но для этого требуется использовать формат accDB-файла базы данных Access 16 (16.0.7812 или более поздней) и работать лучше с 64-разрядной версией Access. Дополнительные сведения см. в разделах Использование типа данных Большое число и Выбор между 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 Date/Time для сопоставления с типом данных datetime. Используйте расширенный тип данных даты и времени доступа для сопоставления с типом данных datetime2 , который имеет больший диапазон даты и времени. Дополнительные сведения см. в разделе Использование расширенного типа данных даты и времени.
При запросе дат в SQL Server учитывайте время и дату. Например:
- ДатаЗаказа с 1.01.19 по 31.01.19 может включать не все заказы.
- DateOrdered С 1/1/19 00:00:00 AM и 31.01.19 11:59:59 PM включает все заказы.

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

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

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

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

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

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

Visual Basic

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

Функции VBA в запросах Запросы доступа поддерживают функции 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.