Мигриране на база данни на Access в SQL Server

Отнася се за
Access за Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

Всички имаме ограничения и една база данни на 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 Access и виртуалните частни мрежи (VPN) не се разбират. Но с SQL Server отдалечените потребители могат да използват клиентската база данни на Access на работния плот и SQL Server сървъра, разположен зад VPN защитната стена.
  • Azure SQL Server В допълнение към предимствата на SQL Server предлага динамична мащабируемост без прекъсване, интелигентна оптимизация, глобална мащабируемост и наличност, елиминиране на разходите за хардуер и намалено администриране.

Избор на опция за най-добрия Azure SQL Server

Ако извършвате мигриране към Azure SQL Server, има три опции, от които да избирате, всяка с различни предимства:

  • Единична база данни/еластични пулове Тази опция има собствен набор от ресурси, управлявани чрез сървър на SQL база данни. Единичната база данни е като съдържаща се база данни в SQL Server. Можете също да добавите elastic pool, който е колекция от бази данни със споделен набор от ресурси, управлявани чрез сървъра за 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, изпълнете първите стъпки по-долу.

  1. Затворете базата данни на Access.
  2. Уверете се, че текущите потребители, които са свързани към базата данни, също така да затворят базата данни.
  3. Ако базата данни е в .mdb файлов формат, премахнете защитата на ниво потребител.
  4. Архивирайте вашата база данни. За повече информация вж. "Защита на данните чрез архивиране и възстановяване на процеси".

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

Съвет Ако е възможно, използвайте самостоятелна версия на Access.

Изпълнение на SSMA

Microsoft предоставя помощника за мигриране на Microsoft SQL Server (SSMA), за да улесни миграцията. SSMA основно мигрира таблици и заявки за избиране без параметри. Формулярите, отчетите, макросите и VBA модулите не се конвертират. Мениджърът на метаданни на SQL Server показва обектите на вашата база данни на Access и обектите на SQL Server, което ви позволява да прегледате текущото съдържание на двете бази данни. Тези две връзки се записват във вашия файл за мигрирането, ако решите да прехвърлите допълнителни обекти в бъдеще.

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

  1. За да мигрирате база данни чрез SSMA, първо изтеглете и инсталирайте софтуера, като щракнете двукратно върху изтегления MSI файл. Уверете се, че сте инсталирали съответната 32 или 64-битова версия за вашия компютър.
  2. След като инсталирате SSMA, отворете я на работния плот, за предпочитане от компютъра с файла на базата данни на Access.
    Можете също да я отворите на компютър, който има достъп до базата данни на Access от мрежата в споделена папка.
  3. Следвайте началните инструкции в SSMA, за да предоставите основна информация, като например местоположението на SQL Server, базата данни на Access и обектите за мигриране, информация за връзка и дали искате да създадете свързани таблици.
  4. Ако извършвате мигриране към SQL Server 2016 или по-нова версия и искате да актуализирате свързана таблица, добавете колона rowversion, като изберете "Преглед на инструментите>"Настройки> на проектаОбщи.
    Полето rowversion помага да се избегнат конфликти на записи. Access използва това поле rowversion в свързана с SQL Server таблица, за да определи кога записът е актуализиран за последен път. Освен това, ако добавите полето rowversion към заявка, Access го използва, за да избере реда отново след операция за актуализиране. Това подобрява ефективността, като помага да се избегнат грешки в конфликт при писане и сценарии за изтриване на записи, които могат да се случат, когато Access открие различни резултати от първоначалното подаване, като например типовете данни с числа с плаваща запетая и активаторите, които променят колоните. Избягвайте обаче да използвате полето rowversion във формуляри, отчети или код на VBA. За повече информация вижте rowversion.
    Забележка Избягвайте да бъркате rowversion с времеви клейма. Въпреки че ключовата дума timestamp в SQL Server е синоним на rowversion, не можете да използвате rowversion като начин за времево клеймо на въвеждане на данни.
  5. За да зададете точни типове данни, изберете "Инструменти> запреглед" Project>Settings Type Mapping. Например ако съхранявате само текст на английски език, можете да използвате тип данни varchar вместо 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 база данни и ODBC, вместо да използвате драйверите на SQL Server, които се предоставят с Windows. По-новите драйвери не само са по-бързи, но и поддържат нови функции в Azure SQL, които предишните драйвери нямат. Можете да инсталирате драйверите на всеки компютър, където се използва преобразуваната база данни. За повече информация вж. Microsoft OLE DB драйвер 18 за SQL Server и Microsoft ODBC драйвер 17 за 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. За повече информация вж. "Сравняване на Access SQL с SQL Server TSQL".

Типове данни

Access и SQL Server имат сходни типове данни, но имайте предвид следните потенциални проблеми.

Голямо число Типът данни "Голямо число"съхранява непарична, числова стойност и е съвместим с типа данни SQL bigint. Можете да използвате този тип данни за ефективно изчисляване на големи числа, но това изисква да използвате файловия формат на база данни .accdb на Access 16 (16.0.7812 или по-нова версия), а работи по-добре с 64-битовата версия на Access. За повече информация вижте " Използване на типа данни "Голямо число " и "Избор на 64-битова или 32-битова версия на Office".

Да/не По подразбиране колоната "Да/не" в Access се конвертира в битово поле на SQL Server. За да избегнете заключване на записа, уверете се, че битовото поле е зададено да забранява стойности NULL. В SSMA можете да изберете колоната bit, за да зададете свойството Allow Nulls на NO. В TSQL използвайте командите CREATE TABLE или ALTER TABLE .

Дата и час Има няколко съображения за дата и час:

  • Ако нивото на съвместимост на базата данни е 130 (SQL Server 2016 г.) или по-високо и свързаната таблица съдържа една или повече колони за дата и час или дата/час2, таблицата може да върне съобщението #deleted в резултатите. За повече информация вж. "Свързана таблица с SQL-Server база данни на Access връща #deleted".

  • Използвайте типа данни на Access дата/час, за да ги съпоставите с типа данни за дата и час. Използвайте разширения тип данни на Access за дата и час, за да се съпоставите с типа данни datetime2 , който има по-голям диапазон от дати и часове. За повече информация вж. "Използване на типа данни на разширена дата и час".

  • Когато правите заявки за дати в SQL Server, вземете предвид както часа, така и датата. Например:

    • DateOrdered Between 1/1/19 and 1/31/19 може да не включва всички поръчки.
    • DateOrdered Between 1/1/19 00:00:00 AM And 1/31/19 11:59:59 PM включва всички поръчки.

Прикачен файл Типът данни "Прикачен файл" съхранява файл в база данни на Access. В SQL Server имате няколко опции, които да обмислите. Можете да извлечете файловете от базата данни на Access и след това да помислите за съхраняване на връзки към файловете във вашата база данни на SQL Server. Като алтернатива можете да използвате FILESTREAM, FileTables или отдалечено хранилище за BLOB (RBS), за да съхранявате прикачените файлове в базата данни на SQL Server.

Хипервръзка Таблиците на Access имат колони за хипервръзки, които не се поддържат от SQL Server. По подразбиране тези колони ще се конвертират в колони nvarchar(max) в SQL Server, но можете да персонализирате съпоставянето, за да изберете по-малък тип данни. Във вашето решение за Access все още можете да използвате поведението на хипервръзката във формуляри и отчети, ако зададете свойството "Хипервръзка " за контролата на "истина".

Многозначно поле Многозначното поле на 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, вие също преминавате от файлов сървър към модел на база данни клиент-сървър. Следвайте тези общи указания:

  • За най-бърз достъп изпълнявате малки заявки само за четене на клиента.
  • Изпълнявате дълги заявки за четене/запис на сървъра, за да се възползвате от по-голямата изчислителна мощност.
  • Намалете мрежовия трафик с филтри и агрегиране, за да прехвърляте само данните, от които се нуждаете.

Оптимизиране на производителността в модела на база данни на клиентския сървър За повече информация вж. "Създаване на транзитна заявка".

Следват допълнителни, препоръчителни указания.

Put logic on the server Вашето приложение може също да използва изгледи, дефинирани от потребителя функции, съхранени процедури, изчисляеми полета и превключватели, за да централизира и споделя логиката на приложението, бизнес правила и правила, сложни заявки, проверка на данни и код за цялост на връзките на сървъра, а не на клиента. Запитайте се дали тази заявка или задача може да се изпълни на сървъра по-добре и по-бързо? Накрая тествайте всяка заявка, за да осигурите оптимална производителност.

Използване на изгледи във формуляри и отчети В Access направете следното:

  • За формуляри използвайте SQL изглед за формуляр само за четене и индексиран SQL изглед за формуляр за четене/запис като източник на записи.
  • За отчети използвайте SQL изглед като източник на записи. Създайте обаче отделен изглед за всеки отчет, така че да можете по-лесно да актуализирате конкретен отчет, без да влияете на други отчети.

Намаляване на зареждането на данни във формуляр или отчет Не показвайте данни, докато потребителят не ги поиска. Например оставете свойството Recordsource празно, накарайте потребителите да изберат филтър във вашия формуляр, след което попълнете свойството Recordsource с вашия филтър. Или използвайте клаузата where в DoCmd.OpenForm и DoCmd.OpenReport за показване на точния(те) запис(и), от който се нуждае потребителят. Помислете дали да не изключите навигацията в записи.

Бъдете внимателни с разнорогенни заявки Избягвайте да изпълнявате заявка, която комбинира локална таблица на Access и свързана таблица на SQL Server, което понякога се нарича хибридна заявка. Този тип заявка все още изисква от Access да изтегли всички данни на SQL Server на локалния компютър и след това да изпълни заявката, но заявката не се изпълнява в SQL Server.

Кога да използвате локални таблици Обмислете дали да не използвате локални таблици за данни, които се променят рядко, например списъка на щатите или провинциите в дадена страна или регион. Статичните таблици често се използват за филтриране и могат да работят по-добре в клиентския сървър на Access.

За повече информация вижте "Съветник за регулиране на ядро на база данни", "Използване на Анализатор на производителността за оптимизиране на база данни на Access" и "Оптимизиране на приложения на Microsoft Office Access, свързани с SQL Server".

Вж. също

Ръководство за мигриране на бази данни на Azure

Блог на Microsoft за мигриране на данни

Мигриране, конвертиране и преоразмеряване на Microsoft Access към SQL Server

Начини за споделяне на настолна база данни