Перенесення бази даних Access до сервера SQL Server

У всіх нас є обмеження, і база даних Access не є винятком. Наприклад, база даних Access має обмеження розміру 2 ГБ і не підтримує більше 255 одночасних користувачів. Тому коли знадобиться перейти до наступного рівня бази даних Access, можна перейти на SQL Server. SQL Server (локально або в хмарі Azure) підтримує більший обсяг даних, більше одночасних користувачів і має більшу ємність, ніж обробник баз даних JET/ACE. Цей посібник дає вам плавний початок SQL Server подорожі, допомагає зберегти створені вами зовнішні рішення Access і, сподіваюся, мотивує вас використовувати Access для майбутніх рішень баз даних. Майстер перетворення на формат SQL видалено з Access у програмі Access 2013, тому тепер можна скористатися помічником 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, пропонує динамічну масштабованість без простою, розумної оптимізації, глобальної масштабованості та доступності, усунення витрат на обладнання та зниження адміністрування.

Виберіть найкращий варіант SQL Server Azure

Якщо ви переносите дані до 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, який підтримує до 10 ГБ, і це безкоштовний і зручний спосіб виконати перенесення та перевірити його. Під час підключення використовуйте LocalDB як екземпляр бази даних.

Порада Якщо це можливо, скористайтеся автономною версією Access. Якщо ви можете використовувати лише Microsoft 365, скористайтеся обробником баз даних Access 2010, щоб перенести базу даних Access під час використання SSMA. Докладні відомості див. в статті Вторинний розповсюдження обробника баз даних Microsoft Access 2010.

Запустити 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. Докладні відомості див. в статті Перетворення рядків.

Примітка. Уникайте заплутаного перетворення рядків із позначками часу. Хоча позначка часу ключового слова – це синонім для перетворення рядків у SQL Server, не можна використовувати rowversion як спосіб додавання позначки часу для введення даних.
Щоб установити точні типи даних, виберіть Рецензування засобів > Параметри проекту > Зіставлення типів. Наприклад, якщо ви зберігаєте лише текст англійською мовою, можна використовувати варчар , а не тип даних 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. Нові драйвери не лише швидші, але й підтримують нові функції в Azure SQL, яких немає в попередніх драйверах. Драйвери можна інсталювати на кожному комп'ютері, де використовується перетворена база даних. Докладні відомості див. в статті Драйвер Microsoft OLE DB Driver 18 для SQL Server та Драйвер Microsoft ODBC 17 для SQL Server.

Після перенесення таблиць Access можна створити зв'язок із таблицями в SQL Server де зараз розміщено дані. Зв'язування безпосередньо з Access дає змогу простіше переглядати дані, а не використовувати складніші засоби керування SQL Server. Ви можете запитувати та редагувати зв'язані дані залежно від дозволів, настроєних адміністратором бази даних SQL Server.

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

Докладні відомості див. в статті Зв'язування та імпорт даних із бази даних SQL Server Azure та Імпорт даних або зв'язування з ними в SQL Server базі даних.

Порада Не забудьте скористатися диспетчером зв'язаних таблиць в Access, щоб зручно оновлювати та повторно зв'я язати таблиці. Докладні відомості див. в статті Керування зв'язаними таблицями.

Перевірка та змінення

У наведених нижче розділах описано поширені проблеми, які можуть виникнути під час перенесення, і способи їх вирішення.

Запити

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

Типи даних

Access і SQL Server мають схожі типи даних, але пам'ятайте про такі потенційні проблеми.

Велике число Тип даних "Велике число" зберігає негрошовий числовий тип даних і сумісний із типом даних SQL "Велике ціле". Цей тип даних можна використовувати, щоб ефективно обчислювати великі числа, але для цього потрібно використовувати формат файлу бази даних ACCDB access 16 (16.0.7812 або пізнішої версії) і ефективніше працювати з 64-розрядною версією Access. Докладні відомості див. в статтях Використання типу даних "Велике число " та Вибір 64-розрядної або 32-розрядної версії Office.

Так/Ні За замовчуванням стовпець Access "Так/Ні" перетворюється на поле SQL Server біта. Щоб уникнути блокування записів, переконайтеся, що для бітного поля встановлено заборона null-значень. In SSMA, ви можете вибрати біт стовпець, щоб установити для властивості Allow Nulls значення NO. У TSQL використовуйте інструкції CREATE TABLE або ALTER TABLE .

Дата й час Є кілька моментів у датах і часах.

Якщо рівень сумісності бази даних – 130 (SQL Server 2016) або пізніший, а зв'язана таблиця містить один або кілька стовпців дати й часу2, таблиця може повернути повідомлення #deleted в результатах. Докладні відомості див. в статті Повернення #deleted зв'язаної таблиці Access із базою даних SQL-Server.
Використовуйте тип даних "Дата й час" Access, щоб зіставити з типом даних дати й часу. Використовуйте тип даних "Розширений формат дати й часу" access, щоб зіставити з типом даних datetime2 , який має більший діапазон дат і часу. Докладні відомості див. в статті Використання типу даних "Розширений формат дати й часу".
Під час запиту дат у SQL Server враховуйте час, а також дату. Наприклад:
- DateOrdered Between 1/1/19 and 1/31/19 may not include all orders.
- DateOrdered Between 1/1/19 00:00:00 AM And 31/19 11:59:59 PM does include all orders.

вкладення; Тип даних "Вкладення" зберігає файл у базі даних 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 фільтром. Або скористайтеся реченням DoCmd.OpenForm і DoCmd.OpenReport, щоб відобразити точні записи, потрібні користувачу. Радимо вимкнути навігацію записами.

Будьте обережні з гетерогенними запитами Не запускайте запит, який поєднує локальну таблицю Access і SQL Server зв'язану таблицю, яка іноді називається гібридним запитом. Для цього типу запиту програма Access усе ще потребує завантаження всіх SQL Server даних на локальний комп'ютер, а потім виконання запиту не виконує запит у SQL Server.

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

Докладні відомості див. в статтях Помічник із настроювання ядра бази даних. Оптимізуйте базу даних Access за допомогою Аналізатор ефективності та оптимізуйте програми Microsoft Office Access, пов'язані з SQL Server.