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

Access для Office 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007

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

Подготовка

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

Разделение баз данных

Все объекты базы данных 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    Access и виртуальные частные сети (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 Server в Azure .

Первые шаги

Есть несколько проблем, которые можно устранить, чтобы упростить процесс миграции перед запуском ССМА.

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

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

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

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

Совет    По возможности используйте отдельную версию Access. Если вы можете использовать только Office 365, то при использовании ССМА используйте ядро базы данных Access 2010 для миграции базы данных Access. Дополнительные сведения можно найти в распространяемом компоненте Microsoft Access Database Engine 2010 .

Запуск ССМА

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

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

1. Чтобы перенести базу данных с помощью ССМА, сначала Скачайте и установите программное обеспечение, дважды щелкнув ЗАГРУЖЕНный MSI-файл. Убедитесь, что вы установили соответствующую версию 32 или 64 для вашего компьютера.
2. После установки ССМА откройте ее на рабочем столе, лучше всего с компьютера с помощью файла базы данных Access.

Вы также можете открыть ее на компьютере, который имеет доступ к базе данных Access из общей папки.

3. Следуйте инструкциям в ССМА, чтобы предоставить основные сведения, например расположение SQL Server, базу данных и объекты Access для миграции, сведения о подключении и указать, нужно ли создавать связанные таблицы.
4. Если вы переходите на SQL Server 2016 или более поздней версии и хотите обновить связанную таблицу, добавьте столбец rowversion, выбрав инструменты проверки > Параметры проекта > Общие.

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

Примечание    Старайтесь не путать параметр rowversion с метками времени. Несмотря на то, что ключевое слово timestamp является синонимом значения rowversion в SQL Server, нельзя использовать rowversion для ввода данных метки времени.

5. Чтобы задать точные типы данных, выберите инструменты проверки > Параметры проекта > Сопоставление типов. Например, если вы сохраняете только текст на английском языке, вы можете использовать тип данных varchar вместо nvarchar .

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

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

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

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

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

Ниже приведены инструкции по преобразованию, загрузке и миграции объектов в SQL Server.

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

Связь с таблицами

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

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

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

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

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

Проверка и пересмотр

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

Запросы

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

Типы данных

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

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

Логический    По умолчанию столбец "да/нет" в Access преобразуется в битовое поле SQL Server. Чтобы избежать блокировки записей, Убедитесь, что в поле bit задано значение "Запретить пустые значения". В ССМА можно выбрать столбец bit, чтобы установить для свойства Разрешить пустые значения значение нет. В TSQL используйте инструкции CREATE TABLE или ALTER TABLE .

Дата и время    Существуют следующие моменты, касающиеся даты и времени:

• Если уровень совместимости базы данных — 130 (SQL Server 2016) или выше, а связанная таблица содержит один или несколько столбцов datetime или datetime2, она может возвращать сообщение #deleted в результатах. Дополнительные сведения можно найти в разделе связанная таблица Access с базой данных SQL-Server, которая возвращает #deleted .
• Используйте тип данных datetime2 с большим диапазоном дат, чем DateTime.
• При запросе дат в SQL Server следует учесть время и дату. Пример.

• Датеордеред между 1/1/19 и 1/31/19 могут включать не все заказы.
• Датеордеред между 1/1/19 00:00:00 AM и 1/31/19 11:59:59 PM включают все заказы.

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

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

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

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

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

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

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. ОткрытьОтчет, чтобы отобразить точные записи, необходимые пользователю. Попробуйте отключить навигацию по записям.

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

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

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