У кожного є обмеження, і база даних Access – не виняток. Наприклад, база даних Access має обмеження на розмір до 2 ГБ і не може підтримувати понад 255 одночасних користувачів. Тому, коли потрібно переходити до нового рівня бази даних Access, можна виконати перенесення до SQL Server. SQL Server (локально або в Azure хмарі) підтримує більші обсяги даних, більше одночасних користувачів і має більшу ємність, ніж обробник баз даних JET/ACE. Цей посібник допоможе вам спокійно розпочати SQL Server, допомагає зберегти створені вами зовнішні рішення Access і, сподіваємося, мотивує вас використовувати Access у майбутніх рішеннях баз даних. Щоб успішно виконати перенесення, використовуйте Microsoft SQL Server Migration Assistant (SSMA), виконайте наведені нижче дії.
Підготовка
У наведених нижче розділах наведено довідкову інформацію та інші відомості, які допоможуть розпочати роботу.
Розділення бази даних
Усі об'єкти бази даних Access можуть бути розташовані в одному файлі або зберігатися у двох файлах баз даних: клієнтській і серверній. Це називається розділенням бази даних . Вона покликана полегшити спільний доступ у мережевому середовищі. Файл серверної бази даних має містити лише таблиці та зв'язки. Файл зовнішньої частини має містити лише всі інші об'єкти, зокрема форми, звіти, запити, макроси, модулі VBA і зв'язані таблиці з серверною базою даних. Перенесення бази даних Access схоже на розділення бази даних: SQL Server виконує роль нового внутрішнього сервера для даних, розміщених на сервері.
У результаті ви все ще можете підтримувати клієнтську базу даних Access із таблицями, зв'язаними з таблицями SQL Server. По суті, ви можете скористатися перевагами швидкої розробки програм, яку надає база даних Access, а також масштабованістю SQL Server.
SQL ServerПереваги SQL Server
Усе ще потрібно переконати перейти на SQL Server? Ось деякі додаткові переваги, про які варто подумати:
- Більша кількість одночасних користувачів SQL Server може обслуговувати значно більше одночасних користувачів, ніж Access, і мінімізує вимоги до пам'яті, коли додано більше користувачів.
- Підвищена доступність За допомогою SQL Server можна динамічно створювати резервні копії бази даних (інкрементні або завершальні) під час її використання. Відповідно, для резервного копіювання бази даних не потрібно змушувати користувачів вийти з бази даних.
- Висока продуктивність і масштабованість База даних SQL Server зазвичай працює ефективніше, ніж база даних Access, особливо якщо база даних має великий терабайтовий розмір. Крім того, SQL Server обробляє запити набагато швидше й ефективніше, обробляючи запити паралельно, використовуючи кілька власних потоків в межах одного процесу для обробки запитів користувачів.
- Покращена безпека Використовуючи надійне з'єднання, SQL Server інтегрується із системою Windows System Security, щоб забезпечити єдиний інтегрований доступ до мережі та бази даних, використовуючи найкращі з обох систем безпеки. Це значно полегшує адміністрування складних схем безпеки. 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 ServerSQL Server. Однак є невелика кількість SQL Server функцій, які недоступні, і немає гарантованого точного часу обслуговування.
- Azure Віртуальна машина Цей параметр дає змогу запускати SQL Server у віртуальній машині в Azure хмарі. Ви маєте повний контроль над обробником SQL Server і можете легко переносити дані. Але потрібно керувати резервними копіями, виправленнями та відновленням.
Докладні відомості див. в статтях "Вибір способу перенесення бази даних до Azure" та "Що таке Azure SQL?".
Перші кроки
Є кілька проблем, які можна вирішити заздалегідь, щоб спростити процес перенесення перед запуском SSMA:
- Додавання індексів таблиць і первинних ключів Переконайтеся, що кожна таблиця Access має індекс і первинний ключ. SQL ServerДля SQL Server потрібно, щоб усі таблиці мали принаймні один індекс, а якщо таблицю можна оновлювати, то первинний ключ – наявність первинного ключа у зв'язаної таблиці.
- Перевірка зв'язків первинних і зовнішніх ключів Переконайтеся, що ці зв'язки базуються на полях з узгодженими типами даних і розмірами. SQL ServerSQL Server не підтримує об'єднані стовпці з різними типами даних і розмірами в обмеженнях зовнішніх ключів.
- Видалення стовпця "Вкладення " SSMA не переносить таблиці, які містять стовпець «Вкладення».
Перш ніж запускати SSMA, виконайте наведені нижче перші кроки.
- Закрийте базу даних Access.
- Переконайтеся, що наявні користувачі, підключені до бази даних, також закрили базу даних.
- Якщо базу даних .mdb формат файлів, видаліть систему безпеки на рівні користувача.
- Створіть резервну копію бази даних. Докладні відомості див. в статті "Захист даних за допомогою процесів резервного копіювання та відновлення".
Порада Розгляньте можливість інсталювати на настільному комп'ютері версію Microsoft SQL Server ExpressSQL Server Express, яка підтримує до 10 ГБ і є безкоштовним і простішим способом виконати перенесення та перевірити його. Під час підключення використовуйте LocalDB як екземпляр бази даних.
Порада Якщо можливо, використовуйте автономну версію Access.
Запуск SSMA
Корпорація Майкрософт надає Microsoft SQL Server Migration Assistant (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. Хоча ключове слово 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. Нові драйвери не лише працюють швидше, але й підтримують нові функції в AzureAzure SQL, яких немає в попередніх драйверах. Драйвери можна інсталювати на кожному комп'ютері, де використовується перетворена база даних. Додаткові відомості див. у статті Microsoft OLE DB Driver 18 для SQL ServerSQL Server і драйвер Microsoft ODBC 17 для SQL Server SQL Server.
Перенісши таблиці Access, можна створити зв'язок із таблицями в SQL Server, де тепер розміщено дані. Зв'язування безпосередньо з Access також забезпечує простіший спосіб перегляду даних порівняно з використанням складніших засобів керування SQL Server. Ви можете створювати запити та редагувати зв'язані дані залежно від дозволів, настроєних адміністратором бази даних SQL Server.
Примітка Якщо ви створюєте DSN ODBC під час зв'язування з базою даних SQL Server під час зв'язування, створіть те саме DSN на всіх комп'ютерах, що використовують нову програму, або програмно використайте рядок підключення, збережені у файлі DSN.
Докладні відомості див. в статтях "Зв'язування або імпорт даних із бази даних Azure SQL Server та "Імпорт або зв'язування з даними в базі даних SQL Server".
Порада Не забувайте використовувати диспетчер зв'язаних таблиць у програмі Access, щоб зручно оновлювати та повторно зв'язувати таблиці. Докладні відомості див. в статті "Керування зв'язаними таблицями".
Перевірка та перевірка
У розділах нижче описано поширені проблеми, які можуть виникнути під час перенесення, і способи їх усунення.
Запити
Перетворюються лише вибіркові запити; інші запити, зокрема вибіркові запити, які приймають параметри, – ні. Деякі запити можуть перетворюватися не повністю, а SSMA повідомляє про помилки запиту під час перетворення даних. Ви можете вручну редагувати об'єкти, які не перетворюються, використовуючи синтаксис T-SQL. Синтаксичні помилки також можуть потребувати перетворення спеціальних функцій і типів даних Access вручну на SQL Server. Докладні відомості див. в статті "Порівняння мови SQL в Access із мовою SQL Server TSQL".
Типи даних
Access і SQL Server мають схожі типи даних, але зверніть увагу на вказані нижче потенційні проблеми.
Велике число Тип даних "Велике число" дає можливість зберігати негрошові числові значення. Він сумісний із типом даних 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 бази даних Access повертає #deleted.
Використовуйте тип даних "Дата й час" в Access для зіставлення з типом даних "Дата-час". Використовуйте тип даних Access "Розширений доступ до дати й часу", щоб зіставити тип даних Datetime2 , який має більший діапазон дат і часу. Докладні відомості див. в статті "Використання розширеного типу даних "Дата й час".
Під час створення запиту дат у SQL Server враховуйте не лише час, а й дату. Наприклад:
- DateOrdered Between 01.01.2019 і 31.01.2019 можуть включати не всі замовлення.
- DateOrdered Between 01.01.19 00:00:00 AM And 1/31/19 23:59:59 включає всі замовлення.
Вкладення Тип даних "Вкладення" зберігає файл у базі даних 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 ServerSQL Server не може виконувати ці користувацькі функції. Можливо, знадобиться вручну переробити ці функції та перетворити їх на збережені процедури на SQL Server.
Оптимізація продуктивності
Безумовно, найважливіший спосіб оптимізувати продуктивність під час роботи з новим внутрішнім SQL Server – це вирішити, коли використовувати локальні чи віддалені запити. Перенесення даних до SQL Server призводить також до переходу від файлового сервера до клієнт-серверної моделі обчислень. Дотримуйтесь цих загальних рекомендацій:
- Для швидкого доступу виконуйте невеликі запити лише для читання на клієнті.
- Виконуйте довгі запити на читання й записування на сервері, щоб скористатися перевагами більшої обчислювальної потужності.
- Мінімізуйте мережевий трафік за допомогою фільтрів і агрегації для передачі тільки потрібних даних.
Докладні відомості див. в статті "Створення наскрізного запиту".
Нижче наведено додаткові, рекомендовані рекомендації.
Поставте логіку на сервер Програма також може використовувати подання, користувацькі функції, збережені процедури, обчислювані поля та тригери, щоб централізувати та використовувати логіку програми, бізнес-правила та політики, складні запити, перевірку даних і код цілісності даних на сервері, а не на клієнті. Запитайте себе, чи можна виконати цей запит або завдання на сервері краще та швидше? Нарешті, перевірте кожен запит, щоб забезпечити оптимальну продуктивність.
Використання подань у формах і звітах Виконайте наведені нижче дії в Access.
- Для форм використовуйте як джерело записів подання SQL для форм, доступних лише для читання, і індексоване подання SQL для форм читання й записування.
- Для звітів використовуйте подання SQL як джерело записів. Однак створіть окреме подання для кожного звіту, щоб легко оновлювати певний звіт, не впливаючи на інші звіти.
Мінімізація завантаження даних у формі або звіті Не відображайте дані, доки користувач не запитає про це. Наприклад, залиште властивість recordsource пустою, попросіть користувачів вибрати фільтр у формі, а потім заповніть властивість джерела записів фільтром. Або скористайтеся реченням where DoCmd.OpenForm і DoCmd.OpenReport, щоб відобразити точні записи, потрібні користувачу. Радимо вимкнути навігацію по записам.
Будьте обережні з різнорідними запитами Уникайте виконання запиту, який поєднує локальну таблицю Access і SQL Server зв'язану таблицю, який іноді називають гібридним запитом. Для цього типу запитів необхідно, щоб програма Access завантажила всі дані SQL Server на локальний комп'ютер, після чого виконала запит. У SQL Server запит не виконується.
Використання локальних таблиць Радимо використовувати локальні таблиці, щоб отримувати дані, які рідко змінюються (наприклад, список областей або провінцій у країні чи регіоні). Статичні таблиці часто використовуються для фільтрування, і їх можна підвищити продуктивність на зовнішньому інтерфейсі Access.
Докладні відомості див. в статті Помічник із настроювання ядра бази даних "Оптимізація бази даних Access за допомогою Аналізатор ефективності та "Оптимізація підключених до SQL Server програм Microsoft Office Access.
Додаткові відомості
Довідник із перенесення баз даних Azure
Доступ Microsoft до SQL Server для перенесення, перетворення та перетворення