Мигриране на база данни на 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 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 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 предоставя помощника за мигриране на 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.

Забележка Избягвайте объркващо rowversion с времеви клейма. Въпреки че времевото клеймо на ключовата дума е синоним за rowversion в SQL Server, не можете да използвате rowversion като начин за времево клеймо на запис на данни.
За да зададете точни типове данни, изберете Инструменти за преглед > Настройки на project > Съпоставяне на типове. Ако например съхранявате само текст на английски език, можете да използвате varchar , а не nvarchar тип данни.

Конвертиране на обекти

SSMA преобразува обекти на Access в обекти на SQL Server, но не копира обектите веднага. SSMA предоставя списък на следните обекти за мигриране, така че да можете да решите дали искате да ги преместите в база данни на SQL Server:

Таблици и колони
Изберете Заявки без параметри.
Първични и външни ключове
Индекси и стойности по подразбиране
Check constraints (allow zero length column property, column validation rule, table validation)

Като най-добра практика използвайте отчета за оценка на SSMA, който показва резултатите от конвертирането, включително грешки, предупреждения, информационни съобщения, оценки на времето за извършване на мигрирането и отделни стъпки за коригиране на грешки, които трябва да предприемете, преди действително да преместите обектите.

Преобразуването на обекти на база данни приема дефинициите на обекти от метаданните на Access, преобразува ги в еквивалентен синтаксис на Transact-SQL (T-SQL) и след това зарежда тази информация в проекта. След това можете да видите обектите на SQL Server или SQL Azure и техните свойства с помощта на SQL Server или SQL Azure Metadata Explorer.

За да конвертирате, заредите и мигрирате обекти в SQL Server, следвайте това ръководство.

Съвет След като успешно сте мигрирали вашата база данни на Access, запишете файла на проекта за последващо използване, така че да можете да мигрирате данните отново за тестване или окончателно мигриране.

Свързване на таблици

Помислете за инсталиране на най-новата версия на OLE DB и ODBC драйвери на SQL Server, вместо да използвате основните драйвери за 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 имат сходни типове данни, но имайте предвид следните потенциални проблеми.

Голямо число Типът данни "Голямо число" съхранява непарична, числова стойност и е съвместим с типа данни bigint на SQL. Можете да използвате този тип данни, за да изчислявате ефективно големи числа, но той изисква да използвате файловия формат на база данни на 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 в резултатите. За повече информация вижте Свързана таблица на Access към SQL-Server база данни връща #deleted.
Използвайте типа данни "Дата/час" на Access, за да съпоставите типа данни за дата и час. Използвайте типа данни "Удължено дата/час" на Access, за да съпоставите с типа данни datetime2 , който има по-голям диапазон от дати и часове. За повече информация вижте Използване на типа данни "Удължено дата/час".
Когато правите заявки за дати в SQL Server, вземете предвид часа, както и датата. Например:
- DateOrdered Between 1/1/19 and 31/1/19 may not include all orders.
- DateOrdered Between 1/1/19 00:00:00 AM And 31/19 11:59:59 PM включва всички поръчки.

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

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