Миграция базы данных Access на сервер SQL Server
У всех нас есть ограничения, и база данных Access не является исключением. Например, размер базы данных Access составляет 2 ГБ и не может поддерживать более 255 пользователей одновременно. Поэтому, когда нужно перейти на следующий уровень для базы данных Access, вы можете перейти на SQL Server. SQL Server (локально или в облаке Azure) поддерживает большие объемы данных, больше пользователей одновременно и имеет больший объем, чем яд баз данных JET/ACE. Это руководство поможет вам приступить к SQL Server пути, сохранить созданные вами решения переднего поколения и, возможно, мотивирует вас использовать Access для создания будущих решений для работы с базами данных. Мастер переноса в Access 2013 удален, поэтому теперь вы можете использовать помощник по переносу 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, особенно в крупной базе данных размером 1 ТБ. Кроме того, SQL Server обработка запросов гораздо быстрее и эффективнее за счет параллельной обработки запросов, используя несколько нароженных потоков в одном процессе для обработки запросов пользователей.
Улучшенная безопасность Используя надежное подключение, SQL Server интегрируется с системой безопасности Windows, обеспечивая единый интегрированный доступ к сети и базе данных, используя лучшие из обеих систем безопасности. Это значительно упрощает администрирование сложных схем безопасности. SQL Server идеально подходит для хранения конфиденциальных сведений, таких как номера социального страхования (SOCIAL), данных кредитных карт и конфиденциальных адресов.
Немедленное восстановление Если в операционной системе происходит сбой или отключение питания, 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 Server в Azure.
Первые шаги
Прежде чем запускать SSMA, можно решить несколько проблем, которые помогут вам упростить процесс миграции.
Добавление индексов таблиц и первичных ключей Убедитесь, что каждая таблица Access имеет индекс и первичный ключ. SQL Server требуется, чтобы у всех таблиц был хотя бы один индекс, а для связанной таблицы был первичный ключ, если таблицу можно обновить.
Проверка связей первичного и внешнего ключей Убедитесь, что эти связи основаны на полях с согласованными типами данных и размерами. SQL Server не поддерживает связанные столбцы с разными типами данных и размерами в ограничениях внешнего ключа.
Удаление столбца «Вложение» SSMA не переносит таблицы, содержащие столбец «Вложение».
Перед запуском SSMA необходимо выполнить следующие первые действия:
Закроем базу данных Access.
Убедитесь, что текущие пользователи, подключенные к базе данных, также закроют ее.
Совет. Вы можете установить Microsoft SQL Server Express на компьютере, который поддерживает до 10 ГБ, и это бесплатный и простой способ проверить миграцию. При подключении в качестве экземпляра базы данных используйте LocalDB.
Совет. По возможности используйте автономные версии Access. Если вы можете использовать только Microsoft 365, используйте обдвижку баз данных Access 2010 для переноса базы данных Access при использовании SSMA. Дополнительные сведения см. в распространяемом распространяемом обдвижке ЯД Microsoft Access 2010.
Запуск SSMA
Майкрософт предоставляет Microsoft SQL Server миграции (SSMA), чтобы упростить миграцию. SSMA в основном переносит таблицы и выбирает запросы без параметров. Формы, отчеты, макрос и модули VBA не преобразуются. В SQL Server метаданных Access отображаются объекты базы данных Access и SQL Server, позволяющие просмотреть текущее содержимое обеих баз данных. Эти два подключения сохраняются в файле миграции, если вы решите перенести дополнительные объекты в будущем.
Примечание. Процесс миграции может занять некоторое время в зависимости от размера объектов базы данных и объема данных, которые необходимо перенести.
Чтобы перенести базу данных с помощью SSMA, сначала скачайте и установите программное обеспечение, дважды щелкнув скачаный MSI-файл. Установите для компьютера соответствующую 32- или 64-битную версию.
После установки SSMA откройте его на рабочем столе предпочтительно на компьютере с файлом базы данных Access.
Ее также можно открыть на компьютере, на компьютере с доступом к базе данных Access из сети в общей папке.
Следуйте инструкциям в SSMA, чтобы предоставить основные сведения, такие как расположение SQL Server, база данных Access и объекты, которые нужно перенести, сведения о под соединении и нужно ли создавать связанные таблицы.
Если при переходе на SQL Server 2016 или более поздней версии необходимо обновить связанную таблицу, добавьте столбец rowversion, выбрав «Средства проверки» > Параметры project > Общие.
Поле 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, установите последнюю версию драйверов OLE DB и OD SQL Server BC. Они не только ускоряют использование новых драйверов, но и поддерживают новые функции в 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 на всех компьютерах, где используется новое приложение, или программным путем используйте строку подключения, храняную в файле DSN.
Дополнительные сведения см. в SQL Server и импорте данных из базы данных Azure SQL Server, а также об импорте или связываи с данными SQL Server базе данных.
Совет. Не забудьте использовать диспетчер связанных таблиц в Access для удобного обновления и повторного связываия таблиц. Дополнительные сведения см. в таблице «Управление связанными таблицами».
Проверка и исправление
В следующих разделах описаны распространенные проблемы, которые могут возникнуть при миграции, и описаны их решения.
Запросы
Преобразуются только запросы на выборки. другие запросы не являются, включая запросы на выборки, в которых принимаются параметры. Некоторые запросы могут не полностью преобразовываться, а во время преобразования отчеты SSMA пересылают их по ошибке. Вы можете вручную редактировать объекты, которые не преобразуют, SQL T-синтаксис. Кроме того, для синтаксиса может потребоваться вручную преобразовать функции и типы данных Access в SQL Server функции. Дополнительные сведения см. в статье Сравнение языков Access SQL и SQL Server TSQL.
Типы данных
Access и SQL Server похожи, но при этом следует помнить о следующих потенциальных проблемах.
Большое число Тип данных bigint хранит неденежные числовые значения и совместим с типом SQL bigint. С помощью этого типа данных можно эффективно вычислять большие числа, но для этого требуется формат файла базы данных Access 16 (16.0.7812 или более поздней версии) ACCDB и более эффективная версия 64-битной версии Access. Дополнительные сведения см. в документах «Использование типа данных «Большое число» и «Выбор между 64- или 32-битной версией Office».
Логический По умолчанию столбец Access Yes/No преобразуется в SQL Server бит. Чтобы избежать блокировки записей, убедитесь, что в битовом поле установлено значение NULL. IN SSMA, you can select the bit column to set the Allow Nulls property to NO. В TSQL используйте выписки CREATE TABLE или ALTER TABLE.
Дата и время Существует несколько факторов, которые необходимо учитывать как с даты, так и со временем.
Если уровень совместимости базы данных — 130 (SQL Server 2016) или более высокий, а связанная таблица содержит один или несколько столбцов даты и времени даты и времени 2, таблица может вернуть сообщение #deleted в результатах. Дополнительные сведения см. в связанной таблице Access SQL-Server возвращаемой #deleted.
Тип данных «Дата/время» в Access можно использовать для соемерности с типом данных даты и времени. Тип данных Access «Дата/время» используется для карты с типом данных «Дата и время2», который имеет больший диапазон дат и времени. Дополнительные сведения см. в типе данных «Дата/время».
При запросе даты в SQL Server учитываются не только время, но и дата. Например:
DateOrdered Between 01.01.19 and 31.01.19 может включать не все заказы.
По дате с 01.01.19 00:00:00 до 31.31.19 21:59 будут включены все заказы.
Вложение Тип данных «Вложение» хранит файл в базе данных Access. В SQL Server параметров можно учесть несколько вариантов. Вы можете извлечь файлы из базы данных Access, а затем хранить связи с файлами в SQL Server базе данных. Кроме того, вы можете использовать FILESTREAM, FileTables или удаленное хранилище BLOB-файлов (RBS) для хранения вложений в SQL Server базе данных.
Гиперссылка В таблицах Access есть столбцы гиперссылки, SQL Server не поддерживаются. По умолчанию эти столбцы в SQL Server преобразуются в столбцы nvarchar(max), но вы можете настроить сопоставление, чтобы выбрать более мелкий тип данных. В решении Access можно по-прежнему использовать поведение гиперссылки в формах и отчетах, если для свойства «Гиперссылка» для объекта управления установлено истинное поведение.
Многоценное поле Многоценное поле Access преобразуется в 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.
Оптимизация производительности
На сегодняшний день самый важный способ оптимизировать производительность при использовании нового, back-end-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 связанных с SQL Server.