Migrace databáze Accessu na SQL Server

Platí pro

Access pro Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

Získání pomoci s Copilotem

Všichni máme svá omezení a databáze Accessu není výjimkou. Například accessová databáze má limit velikosti 2 GB a nemůže podporovat více než 255 souběžných uživatelů. Takže když nastane čas, aby vaše accessová databáze postoupila na další úroveň, můžete migrovat na SQL Server. SQL Server (ať už místně nebo v cloudu Azure) podporuje větší objemy dat, více souběžných uživatelů a má větší kapacitu než databázový stroj JET/ACE. Tato příručka vám pomůže hladce začít pracovat se systémem SQL Server, pomůže zachovat front-endová řešení Accessu, která jste vytvořili, a doufáme, že vás bude motivovat k používání Accessu pro budoucí databázová řešení. Pokud chcete migraci úspěšně provést, postupujte podle těchto kroků pomocí nástroje Pomocník s migrací s migrací na Microsoft SQL Server (SSMA).

Fáze migrace databáze na SQL Server

Než začnete

Následující části obsahují základní a další informace, které vám pomůžou začít.

Rozdělené databáze

Všechny databázové objekty Accessu můžou být buď v jednom databázovém souboru, nebo můžou být uložené ve dvou databázových souborech: front-end databázi a back-end databázi. Říká se tomu rozdělení databáze a má to usnadnit sdílení v síťovém prostředí. Back-end databáze musí obsahovat pouze tabulky a relace. Soubor front-end musí obsahovat pouze všechny ostatní objekty, včetně formulářů, sestav, dotazů, maker, modulů VBA a tabulek propojených s back-end databází. Když migrujete accessovou databázi, postupuje se podobně jako u rozdělené databáze v tom, že SQL Server funguje jako nový back-end pro data, která jsou teď umístěná na serveru.

Díky tomu můžete pořád udržovat front-end databázi Accessu s propojenými tabulkami s tabulkami na SQL Server tabulkách. Efektivně tak můžete využívat výhod rychlého vývoje aplikací, který poskytuje databáze aplikace Access, a škálovatelnosti systému SQL Server.

Výhody systému SQL Server

Potřebujete migraci na SQL Server ještě trochu přesvědčit? Tady jsou některé další výhody, na které byste měli myslet:

Více souběžných uživatelů SQL Server zvládne mnohem více souběžných uživatelů než Access a minimalizuje nároky na paměť při přidání více uživatelů.
Zvýšená dostupnost: Pomocí SQL Serveru můžete dynamicky zálohovat databázi během jejího používání, a to buď přírůstkově, nebo úplně. Není proto třeba nutit uživatele k zavření databáze, aby bylo možné vytvořit zálohu dat.
Vysoký výkon a škálovatelnost Databáze SQL Server obvykle pracuje lépe než databáze aplikace Access, zejména u velké databáze v řádech terabajtů. SQL Server také zpracovává dotazy mnohem rychleji a efektivněji tím, že zpracovává dotazy paralelně a zpracovává požadavky uživatelů pomocí několika nativních vláken v rámci jednoho procesu.
Vylepšené zabezpečení: Pomocí důvěryhodného připojení se SQL Server integruje se zabezpečením systému Windows a poskytuje jediný integrovaný přístup k síti a databázi s využitím toho nejlepšího z obou systémů zabezpečení. To výrazně usnadňuje správu složitých schémat zabezpečení. SQL Server je ideálním úložištěm pro citlivé informace, jako jsou rodná čísla, údaje o platebních kartách nebo důvěrné adresy.
Okamžitá obnovitelnost Pokud dojde k havárii operačního systému nebo výpadku napájení, může SQL Server automaticky obnovit databázi do konzistentního stavu během několika minut bez zásahu správce databáze.
Používání VPN Access a virtuální privátní sítě (VPN) spolu nevycházejí. Ale s SQL Server můžou vzdálení uživatelé dál používat front-end databázi Accessu na stolním počítači a back-end SQL Server umístěný za bránou firewall VPN.
Azure SQL Server Kromě výhod SQL Server nabízí dynamickou škálovatelnost bez výpadků, inteligentní optimalizaci, globální škálovatelnost a dostupnost, eliminaci nákladů na hardware a nižší správu.

Volba nejlepší možnosti pro Azure SQL Server

Pokud migrujete na Azure SQL Server, máte na výběr ze tří možností, z nichž každá přináší jiné výhody:

Jednoúčelová databáze nebo elastické fondy Tato možnost má vlastní sadu prostředků spravovaných prostřednictvím serveru SQL Database. Jednoúčelová databáze se podobá obsažené databázi v systému SQL Server. Můžete také přidat elastický fond, což je kolekce databází se sdílenou sadou prostředků spravovaných prostřednictvím serveru SQL Database. Nejčastěji používané funkce SQL Server jsou dostupné s integrovanými zálohami, opravami a obnovením. Neexistuje však žádná zaručená přesná doba údržby a migrace z SQL Server může být obtížná.
Spravovaná instance Tato možnost je kolekce systémových a uživatelských databází se sdílenou sadou prostředků. Spravovaná instance je jako instance databáze SQL Server, která je vysoce kompatibilní s místním serverem SQL Server. Spravovaná instance má integrované zálohování, opravy a obnovu a je snadné ji migrovat z SQL Server. Existuje však malý počet funkcí systému SQL Server, které nejsou k dispozici, a není zaručena přesná doba údržby.
Virtuální počítač Azure Tato možnost umožňuje spouštět SQL Server uvnitř virtuálního počítače v cloudu Azure. Máte plnou kontrolu nad modulem SQL Server a snadný způsob migrace. Musíte však spravovat zálohy, opravy a obnovení.

Další informace naleznete v tématech Volba cesty migrace databáze do Azure a Co je Azure SQL?.

První kroky

Existuje několik problémů, které můžete vyřešit předem a které můžou pomoct zjednodušit proces migrace před spuštěním SSMA:

Přidání tabulkových indexů a primárních klíčů Zkontrolujte, jestli má každá accessová tabulka index a primární klíč. SQL Server vyžaduje, aby všechny tabulky měly alespoň jeden index, a pokud je možné tabulku aktualizovat, vyžaduje, aby propojená tabulka měla primární klíč.
Kontrola primárního a cizího klíče Ujistěte se, že tyto relace jsou založeny na polích s konzistentními datovými typy a velikostmi. SQL Server nepodporuje v omezeních cizího klíče spojené sloupce s různými datovými typy a velikostmi.
Odebrání sloupce Příloha SSMA nemigruje tabulky, které obsahují sloupec Příloha.

Před spuštěním SSMA proveďte následující první kroky.

Zavřete databázi aplikace Access.
Ujistěte se, že současní uživatelé připojení k databázi také zavírají databázi.
Pokud je databáze uložena ve formátu .mdb souboru, pak Odeberte uživatelskou úroveň zabezpečení.
Zálohujte databázi. Další informace najdete v tématu Ochrana dat pomocí procesů zálohování a obnovení.

Tip Zvažte instalaci sady Microsoft SQL Server Express Edition na stolní počítač. Tato možnost podporuje až 10 GB a představuje bezplatný a jednodušší způsob provádění a kontroly migrace. Po připojení použijte LocalDB jako instanci databáze.

Tip Pokud je to možné, používejte samostatnou verzi Accessu.

Spuštění SSMA

Microsoft poskytuje nástroj Microsoft Pomocník s migrací s migrací Microsoft SQL Serveru (SSMA), který vám migraci usnadní. SSMA migruje hlavně tabulky a vybírá dotazy bez parametrů. Formuláře, sestavy, makra a moduly VBA se nepřevedou. Průzkumník metadat serveru SQL Server zobrazí databázové objekty Accessu a objekty serveru SQL Server, abyste si mohli prohlédnout aktuální obsah obou databází. Tato dvě připojení se uloží do souboru migrace pro případ, že byste se v budoucnu rozhodli přenést další objekty.

Poznámka V závislosti na velikosti databázových objektů a množství dat, která je nutné přenést, může proces migrace trvat delší dobu.

Pokud chcete databázi migrovat pomocí SSMA, nejprve stáhněte a nainstalujte software poklikáním na stažený soubor MSI. Ujistěte se, že instalujete odpovídající 32bitovou nebo 64bitovou verzi pro váš počítač.
Po instalaci SSMA ho otevřete na ploše, nejlépe z počítače se souborem databáze Accessu.
Můžete ji taky otevřít na počítači, který má přístup k databázi Accessu ze sítě ve sdílené složce.
Postupujte podle počátečních pokynů v SSMA a zadejte základní informace, jako je umístění SQL Serveru, databáze Accessu a objekty, které se mají migrovat, informace o připojení a to, jestli chcete vytvořit propojené tabulky.
Pokud migrujete na SQL Server 2016 nebo novější a chcete aktualizovat propojenou tabulku, přidejte sloupec rowversion výběrem Nástroje> kontrolyNastavení projektu>Obecné.
Pole rowversion pomáhá zabránit konfliktům záznamů. Access používá toto pole rowversion v propojené tabulce systému SQL Server k určení, kdy byl záznam naposledy aktualizován. Pokud přidáte pole rowversion do dotazu, Access ho použije k opětovnému výběru řádku po operaci aktualizace. To zvyšuje efektivitu, protože se vyhnete chybám konfliktů zápisu a scénářům odstranění záznamů, ke kterým může dojít, když Access zjistí jiné výsledky než původní odeslání, například u datových typů čísel s plovoucí desetinnou čárkou a aktivačních událostí, které mění sloupce. Nepoužívejte však pole rowversion ve formulářích, sestavách nebo kódu jazyka VBA. Další informace najdete v tématu rowversion.
Poznámka Nezaměňujte rowversion s časovými razítky. I když je klíčové slovo timestamp synonymem pro rowversion v systému SQL Server, nemůžete použít rowversion jako způsob, jak opatřit položku dat časovým razítkem.
Pokud chcete nastavit přesné datové typy, vyberte Nástroje> kontrolyMapování typů Nastaveníprojektu>. Pokud například ukládáte pouze anglický text, můžete použít datový typ varchar místo nvarchar .

Převedení objektů

SSMA převádí objekty Accessu na objekty SQL Server, ale objekty nekopíruje hned. SSMA poskytuje seznam následujících objektů, které je potřeba migrovat, abyste se mohli rozhodnout, jestli je chcete přesunout do databáze SQL Server:

Tabulky a sloupce
Výběr dotazů bez parametrů
Primární a cizí klíče
Indexy a výchozí hodnoty
Kontrolní omezení (povolit vlastnost sloupce nulové délky, ověřovací pravidlo sloupce, ověření tabulky)

Jako osvědčený postup použijte sestavu posouzení SSMA, která zobrazuje výsledky převodu včetně chyb, upozornění, informačních zpráv, odhadů času pro provedení migrace a jednotlivých kroků opravy chyb, které je potřeba provést před skutečným přesunutím objektů.

Převod databázových objektů přebírá definice objektů z metadat Accessu, převádí je na ekvivalentní syntaxi jazyka Transact-SQL (T-SQL) a pak tyto informace načítá do projektu. Objekty systému SQL Server nebo SQL Azure a jejich vlastnosti pak můžete zobrazit pomocí serveru SQL Server nebo Průzkumníka metadat služby SQL Azure.

Při převodu, načítání a migraci objektů na SQL Server postupujte podle této příručky.

Tip Po úspěšné migraci accessové databáze uložte soubor projektu pro pozdější použití, abyste mohli data migrovat znovu k testování nebo konečné migraci.

Propojení tabulek

Zvažte instalaci nejnovější verze ovladačů SQL Server OLE DB a ODBC namísto používání nativních ovladačů SQL Server dodávaných se systémem Windows. Novější ovladače jsou nejen rychlejší, ale podporují nové funkce v Azure SQL, které předchozí ovladače nemají. Ovladače můžete nainstalovat do každého počítače, ve kterém se převádí databáze. Další informace najdete v tématu Ovladač Microsoft OLE DB 18 pro SQL Server a ovladač Microsoft ODBC 17 pro SQL Server.

Po migraci tabulek Accessu se můžete propojit s tabulkami na SQL Serveru, který je teď hostitelem vašich dat. Propojení přímo z Accessu taky poskytuje jednodušší způsob zobrazení dat namísto použití složitějších nástrojů pro správu SQL Server. Můžete vytvářet dotazy a upravovat propojená data v závislosti na oprávněních, která nastavil správce databáze serveru SQL Server.

Poznámka Pokud vytvoříte název zdroje dat ODBC při propojování databáze SQL Server během procesu propojování, vytvořte buď stejný název DSN na všech počítačích, které používají novou aplikaci, nebo programově použijte připojovací řetězec uložený v souboru DSN.

Další informace najdete v tématech Propojení nebo import dat z databáze Azure SQL Server a Import nebo propojení dat v databázi SQL Server.

Tip Nezapomeňte použít Správce propojených tabulek v Accessu, který umožňuje pohodlnou aktualizaci a opětovné propojení tabulek. Další informace naleznete v tématu Správa propojených tabulek.

Testování a opakování

Následující části popisují běžné problémy, se kterými se můžete setkat během migrace, a postupy pro jejich řešení.

Dotazy

Převedou se jenom výběrové dotazy. ostatní dotazy to nejsou, včetně výběrových dotazů, které přijímají parametry. Některé dotazy se nemusí úplně převést a SSMA hlásí chyby dotazů během procesu převodu. Objekty, které se nepřevádějí, můžete ručně upravit pomocí syntaxe T-SQL. Kvůli chybám syntaxe může být také nutné ručně převést funkce a datové typy specifické pro Access na funkce a datové typy pro SQL Server. Další informace najdete v článku Porovnání Accessového SQL s SQL Serverem TSQL.

Datové typy

Access a SQL Server mají podobné datové typy, ale mějte na paměti následující potenciální problémy.

Velké číslo Datový typ Velké číslo uloží nepeněžní číselnou hodnotu a je kompatibilní s datovým typem bigint SQL. Tento datový typ můžete použít k efektivním výpočtům velkých čísel, ale vyžaduje použití formátu databázového souboru .accdb Accessu 16 (16.0.7812 nebo novější) a funguje lépe s 64bitovou verzí Accessu. Další informace najdete v tématech Použití datového typu Velké číslo a Volba mezi 64bitovou nebo 32bitovou verzí Office.

Ano/Ne Ve výchozím nastavení se sloupec Ano/Ne v Accessu převede na bitové pole systému SQL Server. Chcete-li zabránit uzamčení záznamu, ujistěte se, že bitové pole je nastaveno tak, aby nepovolovalo hodnoty NULL. V SSMA můžete vybrat bitový sloupec a nastavit vlastnost Allow Nulls na NE. V TSQL použijte příkazy CREATE TABLE nebo ALTER TABLE .

Datum a čas Je potřeba vzít v úvahu několik aspektů data a času:

Pokud je úroveň kompatibility databáze 130 (SQL Server 2016) nebo vyšší a propojená tabulka obsahuje jeden nebo více sloupců data a času nebo datetime2, může tabulka vrátit zprávu #deleted ve výsledcích. Další informace najdete v článku Access propojená tabulka na SQL-Server vrací databázi #deleted.
K mapování použijte datový typ Datum a čas v Accessu. Pomocí datového typu Rozšířené datum a čas v Accessu můžete mapovat datový typ datetime2 , který má větší rozsah dat a času. Další informace najdete v tématu Použití datového typu Rozšířené datum a čas.
Při zadávání dotazů na kalendářní data na serveru SQL Server vezměte v úvahu čas i datum. Příklady:
- DateOrder: Between 1/1/19 and 1/31/19 nemusí zahrnovat všechny objednávky.
- DateOrdered Between 1/1/19 00:00:00 AM And 1/31/19 11:59:59 PM zahrnuje všechny objednávky.

Příloha Datový typ Příloha ukládá soubor do databáze Accessu. Na SQL Server máte ke zvážení několik možností. Soubory můžete extrahovat z databáze Accessu a potom zvážit uložení odkazů na soubory v databázi serveru SQL Server. Alternativně můžete použít FILESTREAM, FileTables nebo vzdálené úložiště objektů BLOB (RBS) k uchovávání příloh uložených v databázi SQL Server.

Hypertextový odkaz Tabulky Accessu obsahují sloupce hypertextových odkazů, které SQL Server nepodporuje. Ve výchozím nastavení se tyto sloupce převedou na nvarchar(max) sloupce na SQL Server, ale můžete přizpůsobit mapování a zvolit menší datový typ. V řešení pro Access můžete i nadále používat chování hypertextového odkazu ve formulářích a sestavách, pokud nastavíte vlastnost Hypertextový odkaz pro ovládací prvek na hodnotu true.

Pole s více hodnotami Pole s více hodnotami v Accessu se převede na SQL Server jako pole ntext, které obsahuje sadu hodnot s oddělovači. Protože SQL Server nepodporuje datový typ s více hodnotami, který modeluje relaci M:N, může být zapotřebí další návrh a převod.

Další informace o mapování datových typů Accessu a SQL Server najdete v tématu Porovnání datových typů.

Poznámka Pole s více hodnotami se nepřevedou.

Další informace naleznete v tématu Typy data a času, Řetězcové a binární typy a Číselné typy.

Visual Basic

I když SQL Server jazyk VBA nepodporuje, mějte na paměti následující možné problémy:

Funkce jazyka VBA v dotazech Dotazy Accessu podporují funkce jazyka VBA pro data ve sloupci dotazu. Dotazy Accessu, které používají funkce VBA, ale nejde spouštět na SQL Serveru, takže všechna požadovaná data se předávají Microsoft Accessu ke zpracování. Ve většině případů by se tyto dotazy měly převést na předávací.

Funkce definované uživatelem v dotazech Dotazy Microsoft Accessu podporují použití funkcí definovaných v modulech VBA ke zpracování dat, která jsou jim předávána. Může se jednat o samostatné dotazy, příkazy SQL ve zdrojích záznamů formulářů nebo sestav, zdroje dat v polích se seznamem a seznamů ve formulářích, sestavy a pole tabulek a výchozí výrazy nebo výrazy ověřovacích pravidel. SQL Server nemůže tyto uživatelem definované funkce spustit. Možná budete muset tyto funkce ručně přepracovat a převést je na uložené procedury na SQL Server.

Optimalizace výkonu

Zdaleka nejdůležitějším způsobem optimalizace výkonu nového back-endového systému SQL Server je rozhodnutí, jestli použít místní nebo vzdálené dotazy. Při migraci dat na SQL Server přecházíte také ze souborového serveru na počítačový databázový model klient-server. Postupujte podle těchto obecných pokynů:

Spouštění malých dotazů jen pro čtení na klienta pro co nejrychlejší přístup
Spouštějte na serveru dlouhé dotazy pro čtení a zápis, abyste využili větší výpočetní výkon.
Minimalizujte síťový provoz pomocí filtrů a agregace, abyste mohli přenést jenom data, která potřebujete.

Optimalizace výkonu modelu databáze klient-server Další informace najdete v tématu Vytvoření předávacího dotazu.

Následují další doporučené pokyny.

Nastavení logiky na serveru Aplikace může také používat zobrazení, uživatelem definované funkce, uložené procedury, počítaná pole a aktivační události k centralizaci a sdílení logiky aplikace, obchodních pravidel a zásad, složitých dotazů, ověřování dat a kódu referenční integrity na serveru místo na klientovi. Položte si otázku, zda lze tento dotaz nebo úlohu provést na serveru lépe a rychleji? Nakonec otestujte každý dotaz, abyste zajistili optimální výkon.

Použití zobrazení ve formulářích a sestavách V Accessu postupujte takto:

U formulářů použijte jako zdroj záznamů zobrazení SQL pro formulář jen pro čtení a indexované zobrazení SQL pro formulář pro čtení i zápis.
V případě sestav použijte jako zdroj záznamů zobrazení SQL. Vytvořte ale pro každou sestavu samostatné zobrazení, abyste mohli snadněji aktualizovat konkrétní sestavu, aniž by to mělo vliv na jiné sestavy.

Minimalizace načítání dat ve formuláři nebo sestavě Nezobrazujte data, dokud o ně uživatel nepožádá. Například vlastnost Zdroj záznamů ponechejte prázdnou, přimějte uživatele, aby ve vašem formuláři vybrali filtr, a pak naplňte vlastnost Zdroj záznamů svým filtrem. Nebo použijte klauzuli where DoCmd.OpenForm a DoCmd.OpenReport k zobrazení přesných záznamů, které uživatel potřebuje. Zvažte vypnutí procházení záznamů.

Buďte opatrní s heterogenními dotazy Nespouštějte dotaz, který kombinuje místní tabulku Accessu a propojenou tabulku serveru SQL Server a někdy se nazývá hybridní dotaz. Tento typ dotazu stále vyžaduje, aby Access stáhl všechna data serveru SQL Server do místního počítače a potom dotaz spustil. Nespustí se v systému SQL Server.

Kdy používat místní tabulky Pro data, která se mění jen zřídka, například pro seznam států nebo provincií v zemi nebo oblasti, zvažte použití místních tabulek. K filtrování se často používají statické tabulky, které mají lepší výkon na front-endu Accessu.

Další informace najdete v tématech Poradce pro optimalizaci databázového stroje, Použití nástroje Analyzátor výkonu k optimalizaci databáze Accessu a Optimalizace aplikací systém Microsoft Office Accessu propojených se serverem SQL Server.