Migrowanie bazy danych programu Access do programu SQL Server

Przed rozpoczęciem

Poniższe sekcje zawierają informacje ogólne i inne ułatwiające rozpoczęcie pracy.

Informacje o podzielonych bazach danych

Wszystkie obiekty bazy danych programu Access mogą znajdować się w jednym pliku bazy danych lub mogą być przechowywane w dwóch plikach bazy danych: w bazie danych front end i w bazie danych zaplecza. Jest to nazywane dzieleniem bazy danych i ma na celu ułatwienie udostępniania w środowisku sieciowym. Plik bazy danych zaplecza musi zawierać tylko tabele i relacje. Plik frontonu musi zawierać tylko wszystkie inne obiekty, w tym formularze, raporty, zapytania, makra, moduły języka VBA i tabele połączone z zapleczem bazy danych. Podczas migrowania bazy danych programu Access przypomina ona podzieloną bazę danych, ponieważ program SQL Server pełni funkcję nowego zaplecza dla danych znajdujących się obecnie na serwerze.

W związku z tym nadal można obsługiwać front endową bazę danych programu Access z tabelami połączonymi z tabelami programu SQL Server. W praktyce można uzyskiwać korzyści wynikające z szybkiego opracowywania aplikacji dostarczanych przez bazę danych programu Access wraz ze skalowalnością programu SQL Server.

Korzyści programu SQL Server

Nadal potrzebujesz przekonujących informacji, aby przeprowadzić migrację do programu SQL Server? Oto kilka dodatkowych korzyści, o których warto pomyśleć:

Więcej równoczesnych użytkowników Program SQL Server obsługuje o wiele więcej równoczesnych użytkowników niż program Access i minimalizuje wymagania dotyczące pamięci w przypadku dodania większej liczby użytkowników.
Zwiększona dostępność W programie SQL Server można dynamicznie tworzyć kopie zapasowe bazy danych (przyrostowe lub ukończone), gdy jest ona w użyciu. Dzięki temu użytkownicy nie muszą zamykać bazy danych, aby umożliwić wykonanie kopii zapasowej danych.
Wysoka wydajność i skalowalność Baza danych SQL Server zazwyczaj działa lepiej niż baza danych programu Access, szczególnie w przypadku dużej bazy danych o rozmiarze terabajta. Ponadto program SQL Server przetwarza zapytania znacznie szybciej i wydajniej, przetwarzając zapytania równolegle, używając wielu natywnych wątków w ramach jednego procesu do obsługi żądań użytkowników.
Ulepszone zabezpieczenia Korzystając z zaufanego połączenia, program SQL Server integruje się z zabezpieczeniami systemu Windows w celu zapewnienia pojedynczego zintegrowanego dostępu do sieci i bazy danych, wykorzystując najlepsze z obu systemów zabezpieczeń. Znacznie ułatwia to administrowanie złożonymi systemami zabezpieczeń. Program SQL Server to idealne miejsce do przechowywania poufnych informacji, takich jak numery PESEL, dane kart kredytowych i adresy, które są poufne.
Natychmiastowa możliwość odzyskania W przypadku awarii systemu operacyjnego lub awarii zasilania program SQL Server może automatycznie przywrócić bazę danych do spójnego stanu w ciągu kilku minut bez interwencji administratora bazy danych.
Korzystanie z sieci VPN Sieci Access i Wirtualne sieci prywatne (VPN) nie dogaduje się. Jednak w programie SQL Server użytkownicy zdalni mogą nadal używać fronto endowej bazy danych programu Access na komputerze stacjonarnym i zaplecza programu SQL Server znajdującego się za zaporą sieci VPN.
Azure SQL Server Oprócz korzyści wynikających z programu SQL Server, oferuje dynamiczną skalowalność bez przestojów, inteligentną optymalizację, skalowalność i dostępność na całym świecie, eliminację kosztów sprzętu i zmniejszoną administrację.

Wybieranie najlepszej opcji programu Azure SQL Server

Jeśli przeprowadzasz migrację do programu Azure SQL Server, dostępne są trzy opcje do wyboru— każda z różnymi korzyściami:

Pojedyncza baza danych/elastyczne pule Ta opcja ma własny zestaw zasobów zarządzanych za pośrednictwem serwera sql database. Jedna baza danych przypomina zawartą bazę danych w programie SQL Server. Można również dodać elastyczną pulę, czyli kolekcję baz danych z udostępnionym zestawem zasobów zarządzanych za pośrednictwem serwera SQL Database. Najczęściej używane funkcje programu SQL Server są dostępne z wbudowanymi kopiami zapasowymi, poprawkami i odzyskiwaniem. Nie ma jednak gwarantowanego dokładnego czasu konserwacji, a migracja z programu SQL Server może być trudna.
Zarządzane wystąpienie Ta opcja to zbiór baz danych systemów i użytkowników z udostępnionym zestawem zasobów. Zarządzane wystąpienie przypomina wystąpienie bazy danych SQL Server o wysokiej zgodności z lokalnym programem SQL Server. Zarządzane wystąpienie ma wbudowane kopie zapasowe, poprawki, odzyskiwanie i jest łatwe do migracji z programu SQL Server. Istnieje jednak niewielka liczba funkcji programu SQL Server, które nie są dostępne i nie mają gwarantowanego dokładnego czasu konserwacji.
Maszyna wirtualna platformy Azure Ta opcja umożliwia uruchomienie programu SQL Server wewnątrz maszyny wirtualnej w chmurze Platformy Azure. Masz pełną kontrolę nad aparatem programu SQL Server i łatwą ścieżką migracji. Musisz jednak zarządzać kopiami zapasowymi, poprawkami i odzyskiwaniem.

Aby uzyskać więcej informacji, zobacz Wybieranie ścieżki migracji bazy danych do platformy Azure i Co to jest azure SQL?.

Pierwsze kroki

Istnieje kilka problemów, które można rozwiązać z góry, które mogą ułatwić usprawnienie procesu migracji przed uruchomieniem narzędzia SSMA:

Dodawanie indeksów tabel i kluczy podstawowych Upewnij się, że każda tabela programu Access ma indeks i klucz podstawowy. Program SQL Server wymaga, aby wszystkie tabele miały co najmniej jeden indeks i wymaga, aby tabela połączona miała klucz podstawowy, jeśli można ją zaktualizować.
Sprawdzanie relacji klucza podstawowego/obcego Upewnij się, że te relacje są oparte na polach o spójnych typach danych i rozmiarach. Program SQL Server nie obsługuje połączonych kolumn z różnymi typami danych i rozmiarami w ograniczeniach klucza obcego.
Usuwanie kolumny Załącznik Funkcja SSMA nie przeprowadza migracji tabel zawierających kolumnę Załącznik.

Przed uruchomieniem narzędzia SSMA wykonaj następujące pierwsze kroki.

Zamknij bazę danych programu Access.
Upewnij się, że obecni użytkownicy połączeni z bazą danych również zamykają bazę danych.
Jeśli baza danych jest w formacie .mdb pliku, usuń zabezpieczenia na poziomie użytkownika.
Utwórz kopię zapasową bazy danych. Aby uzyskać więcej informacji, zobacz Ochrona danych za pomocą procesów tworzenia i przywracania kopii zapasowych.

Porada Rozważ zainstalowanie na komputerze wersji programu Microsoft SQL Server Express, która obsługuje maksymalnie 10 GB i jest to bezpłatny i łatwiejszy sposób na sprawdzenie migracji. Podczas nawiązywania połączenia użyj lokalnej bazy danych jako wystąpienia bazy danych.

Porada Jeśli to możliwe, użyj autonomicznej wersji programu Access.

Uruchom SSMA

Firma Microsoft udostępnia asystenta migracji programu Microsoft SQL Server (SSMA), który ułatwia migrację. SSMA migruje głównie tabele i zapytania wybierające bez parametrów. Formularze, raporty, makra i moduły VBA nie są konwertowane. W Eksploratorze metadanych programu SQL Server są wyświetlane obiekty bazy danych programu Access i obiekty programu SQL Server umożliwiające przeglądanie bieżącej zawartości obu baz danych. Te dwa połączenia zostaną zapisane w pliku migracji, jeśli w przyszłości zdecydujesz się na transfer dodatkowych obiektów.

Uwaga Proces migracji może zająć trochę czasu w zależności od rozmiaru obiektów bazy danych i ilości danych, które należy przenieść.

Aby przeprowadzić migrację bazy danych przy użyciu interfejsu SSMA, najpierw pobierz i zainstaluj oprogramowanie, klikając dwukrotnie pobrany plik MSI. Upewnij się, że zainstalowano odpowiednią wersję 32-bitowej lub 64-bitowej dla komputera.
Po zainstalowaniu narzędzia SSMA otwórz go na komputerze stacjonarnym, najlepiej z poziomu komputera z plikiem bazy danych programu Access.

Możesz również otworzyć ją na komputerze, który ma dostęp do bazy danych programu Access z sieci w folderze udostępnionym.
Postępuj zgodnie z początkowymi instrukcjami w języku SSMA, aby podać podstawowe informacje, takie jak lokalizacja programu SQL Server, baza danych programu Access i obiekty do migrowania, informacje o połączeniu oraz informacje o tym, czy chcesz tworzyć tabele połączone.
Jeśli przeprowadzasz migrację do programu SQL Server 2016 lub nowszego i chcesz zaktualizować tabelę połączoną, dodaj kolumnę rowversion ,wybierając pozycję Narzędzia recenzowania > Ustawienia programu Project > Ogólne.

Pole rowversion pomaga uniknąć konfliktów rekordów. Program Access używa tego pola rowversion w tabeli połączonej programu SQL Server do określenia, kiedy rekord został ostatnio zaktualizowany. Ponadto jeśli dodasz pole rowversion do zapytania, program Access użyje go do ponownego zaznaczenia wiersza po operacji aktualizacji. Zwiększa to wydajność, pomagając uniknąć błędów konfliktów zapisu i rejestrować scenariusze usuwania, które mogą się zdarzyć, gdy program Access wykryje inne wyniki niż oryginalne żądanie przesłania, na przykład w przypadku typów danych numerów zmiennoprzecinkowych i wyzwalaczy modyfikujących kolumny. Unikaj jednak używania pola rowversion w formularzach, raportach lub kodach języka VBA. Aby uzyskać więcej informacji, zobacz rowversion.

Uwaga Unikaj mylenia rowversion z sygnaturami czasowymi. Chociaż sygnatura czasowa słowa kluczowego jest synonimem rowversion w programie SQL Server, nie można użyć rowversion jako sposobu na sygnaturę czasową wpisu danych.
Aby ustawić precyzyjne typy danych, wybierz pozycję Narzędzia recenzowania > Ustawienia projektu > Mapowanie typu. Jeśli na przykład jest przechowywany tylko tekst w języku angielskim, można użyć zamiast nvarchar typu danych varchar.

Konwertowanie obiektów

Narzędzie SSMA konwertuje obiekty programu Access na obiekty programu SQL Server, ale nie kopiuje ich od razu. Narzędzie SSMA udostępnia listę następujących obiektów do zmigrowania, dzięki czemu możesz zdecydować, czy chcesz przenieść je do bazy danych PROGRAMU SQL Server:

Tabele i kolumny
Wybierz pozycję Zapytania bez parametrów.
Klucze podstawowe i obce
Indeksy i wartości domyślne
Sprawdzanie ograniczeń (właściwość kolumny o zerowej długości, reguła poprawności kolumny, sprawdzanie poprawności tabeli)

Najlepszym rozwiązaniem jest skorzystanie z raportu dotyczącego oceny SSMA, który zawiera wyniki konwersji, w tym błędy, ostrzeżenia, komunikaty informacyjne, oszacowania czasu migracji oraz indywidualne kroki korekty błędów, które należy wykonać przed rzeczywistym przeniesieniem obiektów.

Konwertowanie obiektów bazy danych pobiera definicje obiektów z metadanych programu Access, konwertuje je na równoważną składnię języka Transact-SQL (T-SQL), a następnie ładuje te informacje do projektu. Następnie możesz wyświetlić obiekty programu SQL Server lub platformy SQL Azure i ich właściwości przy użyciu programu SQL Server lub Eksploratora metadanych platformy SQL Azure.

Aby przekonwertować, załadować i przeprowadzić migrację obiektów do programu SQL Server, skorzystaj z tego przewodnika.

Porada Po pomyślnej migracji bazy danych programu Access zapisz plik projektu do późniejszego użycia, aby móc ponownie przeprowadzić migrację danych na potrzeby testowania lub ostatecznej migracji.

Łączenie tabel

Rozważ zainstalowanie najnowszej wersji sterowników bazy danych OLE i ODBC programu SQL Server zamiast używać natywnych sterowników programu SQL Server dostarczanych z systemem Windows. Nowsze sterowniki nie tylko są szybsze, ale także obsługują nowe funkcje w języku Azure SQL, których nie obsługują poprzednie sterowniki. Sterowniki można zainstalować na każdym komputerze, na którym jest używana przekonwertowana baza danych. Aby uzyskać więcej informacji, zobacz Sterownik bazy danych OLE firmy Microsoft 18 dla programu SQL Server i Sterownik ODBC firmy Microsoft 17 dla programu SQL Server.

Po przeprowadzeniu migracji tabel programu Access można utworzyć połączenie z tabelami w programie SQL Server, które teraz hostują dane. Łączenie bezpośrednio z programu Access zapewnia również prostszy sposób wyświetlania danych zamiast korzystania z bardziej złożonych narzędzi do zarządzania programem SQL Server. Możesz wykonywać zapytania i edytować połączone dane w zależności od uprawnień skonfigurowanych przez administratora bazy danych SQL Server.

Uwaga Jeśli podczas łączenia z bazą danych SQL Server tworzysz nazwę DSN ODBC, utwórz tę samą nazwę DSN na wszystkich komputerach korzystających z nowej aplikacji lub programowo użyjesz parametrów połączenia przechowywanych w pliku DSN.

Aby uzyskać więcej informacji, zobacz Łączenie z danymi lub importowanie ich z bazy danych Azure SQL Server Database oraz Importowanie lub łączenie danych w bazie danych programu SQL Server.

Porada Pamiętaj, aby wygodnie odświeżać i ponownie łączyć tabele za pomocą Menedżera tabel połączonych w programie Access. Aby uzyskać więcej informacji, zobacz Zarządzanie tabelami połączonymi.

Testowanie i poprawianie

W poniższych sekcjach opisano typowe problemy, które można napotkać podczas migracji i jak sobie z nimi poradzić.

Zapytania

Konwertowane są tylko zapytania wybierające. inne zapytania nie są uwzględniane w zapytaniach wybierających, które przyjmują parametry. Niektóre zapytania mogą nie zostać całkowicie przekonwertowane, a interfejs SSMA zgłasza błędy zapytania podczas procesu konwersji. Obiekty, które nie są konwertowane, można edytować ręcznie przy użyciu składni języka T-SQL. Błędy składni mogą również wymagać ręcznego przekonwertowania funkcji i typów danych specyficznych dla programu Access na funkcje programu SQL Server. Aby uzyskać więcej informacji, zobacz Porównanie języka SQL programu Access z językiem TSQL programu SQL Server.

Typy danych

Programy Access i SQL Server mają podobne typy danych, ale pamiętaj o następujących potencjalnych problemach.

Duża liczba Typ danych Duża liczba przechowuje wartość liczbową inną niż pieniężna i jest zgodny z typem danych BIGINT JĘZYKA SQL. Za pomocą tego typu danych można wydajnie obliczać duże liczby, ale wymaga on korzystania z formatu pliku bazy danych accdb programu Access 16 (16.0.7812 lub nowszego) i działa lepiej w 64-bitowej wersji programu Access. Aby uzyskać więcej informacji, zobacz Używanie typu danych Duża liczba i Wybieranie między 64-bitową a 32-bitową wersją pakietu Office.

Tak/Nie Domyślnie kolumna Tak/Nie programu Access jest konwertowana na pole bitowe programu SQL Server. Aby uniknąć blokowania rekordów, upewnij się, że dla pola bitowego ustawiono opcję nie zezwalania na wartości NULL. IN SSMA, możesz wybrać kolumnę bitu, aby ustawić właściwość Allow Nulls (Zezwalaj na wartości Null) na NO (Nie). W tabeli TSQL użyj instrukcji CREATE TABLE lub ALTER TABLE .

Data i godzina Istnieje kilka zagadnień dotyczących daty i godziny:

Jeśli poziom zgodności bazy danych to 130 (SQL Server 2016) lub nowszy, a tabela połączona zawiera co najmniej jedną kolumnę datetime lub datetime2, tabela może zwrócić komunikat #deleted w wynikach. Aby uzyskać więcej informacji, zobacz Tabela połączona programu Access z SQL-Server bazy danych zwraca #deleted.
Typ danych Data/Godzina programu Access służy do mapowania na typ danych daty/godziny. Typ danych Rozszerzona data/godzina programu Access umożliwia mapowanie na typ danych datetime2 , który ma większy zakres dat i godzin. Aby uzyskać więcej informacji, zobacz Korzystanie z typu danych rozszerzonej daty/godziny.
Podczas wykonywania zapytań dotyczących dat w programie SQL Server weź pod uwagę godzinę i datę. Na przykład:
- DateOrdered Between 1/1/19 and 1/31/19 may not include all orders.
- DateOrdered Between 1/1/19 00:00:00 AM And 1/31/19 11:59:59 PM zawiera wszystkie zamówienia.

Załącznik Typ danych Załącznik przechowuje plik w bazie danych programu Access. W programie SQL Server masz kilka opcji do rozważenia. Możesz wyodrębnić pliki z bazy danych programu Access, a następnie rozważyć przechowywanie linków do plików w bazie danych SQL Server. Możesz również użyć strumienia PLIKÓW, tabel plików lub zdalnego magazynu obiektów BLOB (SPZ), aby przechowywać załączniki w bazie danych PROGRAMU SQL Server.

Hiperłącze Tabele programu Access zawierają kolumny hiperlinków, które nie są obsługiwane przez program SQL Server. Domyślnie te kolumny zostaną przekonwertowane na kolumny nvarchar(max) w programie SQL Server, ale możesz dostosować mapowanie, aby wybrać mniejszy typ danych. W rozwiązaniu programu Access można nadal używać zachowania hiperlinku w formularzach i raportach, jeśli właściwość Hiperlink dla kontrolki jest ustawiona na wartość true.

Pole wielowartościowe Pole wielowartościowe programu Access jest konwertowane na program SQL Server jako pole ntext zawierające rozdzielany zestaw wartości. Ponieważ serwer SQL nie obsługuje wielowartościowego typu danych, który modeluje relację wiele-do-wielu, może być potrzebna dodatkowa praca przy przeprojektowaniu i konwersji.

Aby uzyskać więcej informacji na temat mapowania typów danych programu Access i programu SQL Server, zobacz Porównanie typów danych.

Uwaga Pola wielowartościowe nie są konwertowane.

Aby uzyskać więcej informacji, zobacz Typy daty i godziny, typy ciągów i danych binarnych oraz typy liczbowe.

Visual Basic

Mimo że język VBA nie jest obsługiwany przez program SQL Server, zwróć uwagę na następujące możliwe problemy:

Funkcje języka VBA w zapytaniach Zapytania programu Access obsługują funkcje języka VBA dotyczące danych w kolumnie zapytania. Jednak zapytań programu Access korzystających z funkcji języka VBA nie można uruchamiać w programie SQL Server, więc wszystkie żądane dane są przekazywane do programu Microsoft Access w celu przetworzenia. W większości przypadków te zapytania powinny być konwertowane na zapytania przekazujące.

Funkcje zdefiniowane przez użytkownika w zapytaniach Zapytania programu Microsoft Access obsługują korzystanie z funkcji zdefiniowanych w modułach VBA do przetwarzania przekazywanych im danych. Zapytania mogą być zapytaniami autonomicznymi, instrukcjami SQL w źródłach rekordów formularza/raportu, źródłami danych pól kombi i pól list w formularzach, raportach i polach tabel oraz wyrażeniami reguły domyślnej lub poprawności. Program SQL Server nie może uruchamiać tych funkcji zdefiniowanych przez użytkownika. Może być konieczne ręczne przeprojektowanie tych funkcji i przekonwertowanie ich na procedury przechowywane w programie SQL Server.

Optymalizowanie wydajności

Zdecydowanie najważniejszym sposobem optymalizacji wydajności za pomocą nowego, zaplecza programu SQL Server jest określenie, kiedy używać zapytań lokalnych lub zdalnych. Podczas migrowania danych do programu SQL Server przenosisz również dane z serwera plików do modelu komputerowej bazy danych klient-serwer. Postępuj zgodnie z następującymi ogólnymi wskazówkami:

Uruchamianie małych zapytań tylko do odczytu na kliencie w celu uzyskania najszybszego dostępu.
Na serwerze można uruchamiać długie zapytania odczytu i zapisu, aby korzystać z większej mocy obliczeniowej.
Zminimalizuj ruch sieciowy za pomocą filtrów i agregacji, aby przesyłać tylko potrzebne dane.

Optymalizowanie wydajności w modelu bazy danych serwera klienta

Aby uzyskać więcej informacji, zobacz Tworzenie zapytania przekazującego.

Poniżej przedstawiono dodatkowe, zalecane wskazówki.

Umieść logikę na serwerze Aplikacja może również używać widoków, funkcji zdefiniowanych przez użytkownika, procedur składowanych, pól obliczeniowych i wyzwalaczy w celu scentralizowania i udostępniania logiki aplikacji, reguł biznesowych i zasad, złożonych zapytań, sprawdzania poprawności danych i kodu więzów integralności na serwerze, a nie na kliencie. Zadaj sobie pytanie, czy to zapytanie lub zadanie może być wykonywane na serwerze lepiej i szybciej? Na koniec przetestuj każde zapytanie, aby zapewnić optymalną wydajność.

Korzystanie z widoków w formularzach i raportach W programie Access wykonaj następujące czynności:

W przypadku formularzy jako źródła rekordów należy użyć widoku SQL dla formularza tylko do odczytu i widoku indeksowanego SQL dla formularza odczytu/zapisu.
W przypadku raportów użyj widoku SQL jako źródła rekordów. Utwórz jednak osobny widok dla każdego raportu, aby ułatwić aktualizowanie określonego raportu bez wpływu na inne raporty.

Minimalizowanie ładowania danych w formularzu lub raporcie Nie wyświetlaj danych, dopóki użytkownik o nie nie poprosi. Na przykład zachowaj właściwość recordsource (Źródło Rekordów) pustą, ustaw użytkowników jako filtr w formularzu, a następnie wypełnij właściwość recordsource (Źródło Rekordów) filtrem. Możesz też użyć klauzuli DoCmd.OpenForm i DoCmd.OpenReport, aby wyświetlić dokładne rekordy wymagane przez użytkownika. Rozważ wyłączenie nawigowania po rekordach.

Zachowaj ostrożność w przypadku heterogenicznych zapytań Unikaj uruchamiania zapytania, które łączy lokalną tabelę programu Access i tabelę połączoną programu SQL Server, czasami nazywaną zapytaniem hybrydowym. Ten typ zapytania nadal wymaga, aby program Access pobierał wszystkie dane programu SQL Server na komputer lokalny, a następnie uruchamiał zapytanie, ale nie uruchamia zapytania w programie SQL Server.

Kiedy używać tabel lokalnych Rozważ użycie tabel lokalnych do danych, które rzadko się zmieniają, takich jak lista województw lub prowincji w kraju lub regionie. Tabele statyczne są często używane do filtrowania i mogą działać lepiej na froncie programu Access.

Aby uzyskać więcej informacji, zobacz Doradca dostosowywania aparatu bazy danych, Optymalizowanie bazy danych programu Access za pomocą analizatora wydajności oraz Optymalizowanie aplikacji programu Microsoft Office Access połączonych z programem SQL Server.