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

У всіх нас є обмеження, і база даних 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 на настільному комп'ютері, який підтримує до 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 в 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 Metadata Explorer.

Щоб перетворити, завантажити та перенести об'єкти до SQL Server, дотримуйтеся цього посібника.

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

Зв'язування таблиць

Радимо інсталювати найновішу версію драйверів SQL Server OLE DB та ODBC замість використання власних драйверів SQL Server, які надходять із Windows. Нові драйвери не лише швидші, але й підтримують нові функції в Azure SQL, яких немає в попередніх драйверах. Драйвери можна інсталювати на кожному комп'ютері, де використовується перетворена база даних. Докладні відомості див. в статті Microsoft OLE DB Driver 18 для SQL Server і Microsoft ODBC Driver 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. Крім того, можна використовувати FILESTREAM, FileTables або віддалене сховище BLOB-об'єктів (RBS), щоб зберігати вкладення, збережені в базі даних SQL Server.

Гіперпосилання Таблиці 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.