Migracja i integracja Migracja i integracja bazy danych bazy danych napisanej w napisanej w

MS Access do MS SQL MS Access do MS SQL 2005  2005 

WstępWstęp

►Omówienie środowiska MS AccessOmówienie środowiska MS Access► Przygotowanie do migracjiPrzygotowanie do migracji►Migracja danych z bazy Access do Bazy MS SQLMigracja danych z bazy Access do Bazy MS SQL►Migracja zapytań SQL-a z Access-a do MS SQL-aMigracja zapytań SQL-a z Access-a do MS SQL-a► Integracja MS SQL-a z zewnętrznymi źródłami Integracja MS SQL-a z zewnętrznymi źródłami

danychdanych►Wykorzystanie funkcjonalności MS SQL w Wykorzystanie funkcjonalności MS SQL w

codziennej pracy po migracjicodziennej pracy po migracji► Zagrożenia związane z migracją Zagrożenia związane z migracją

Środowisko MS AccessŚrodowisko MS Access

► Relacyjna baza danych zintegrowana z Relacyjna baza danych zintegrowana z środowiskiem programistycznymśrodowiskiem programistycznym

► Ścisła integracja z VBA i DAOŚcisła integracja z VBA i DAO►Wszechobecne kreatoryWszechobecne kreatory► Baza i aplikacja w postaci pojedynczego Baza i aplikacja w postaci pojedynczego

plikupliku►W wersji podstawowej baza wymaga W wersji podstawowej baza wymaga

aplikacji Access z możliwością jej aplikacji Access z możliwością jej skompilowania do samodzielnego plikuskompilowania do samodzielnego pliku

►Wbudowane mechanizmy importu i eksportu Wbudowane mechanizmy importu i eksportu danychdanych

►Możliwość protezowania SQL-a za pomocą Możliwość protezowania SQL-a za pomocą funkcji VBAfunkcji VBA

Dostępne elementyDostępne elementy

► Zróżnicowany dostęp do danychZróżnicowany dostęp do danych Silnik JET i linki ODBC do zewnętrznych źródeł Silnik JET i linki ODBC do zewnętrznych źródeł

danych - MDBdanych - MDB „„projekt programu Microsoft Accessprojekt programu Microsoft Access” - ADP” - ADP

► Formularze Formularze ► RaportyRaporty► Strony dostępu HTMLStrony dostępu HTML►MakraMakra►Moduły VBAModuły VBA

Silnik JETSilnik JET

►Brak TriggerówBrak Triggerów►Baza danych jest tylko w postaci plikuBaza danych jest tylko w postaci pliku►BlokadyBlokady►TransakcjeTransakcje►Model relacyjny z możliwością Model relacyjny z możliwością

kaskadowego poprawiania i kasowaniakaskadowego poprawiania i kasowania►Podstawowe bezpieczeństwo oparte o Podstawowe bezpieczeństwo oparte o

zewnętrzny plik lub hasło w baziezewnętrzny plik lub hasło w bazie

Ograniczenia JET-aOgraniczenia JET-a

►Maksymalny rozmiar bazy danych to Maksymalny rozmiar bazy danych to 2 GB2 GB (w tej przestrzeni muszą się również (w tej przestrzeni muszą się również zmieścić obiekty systemowe, np. indeksy), zmieścić obiekty systemowe, np. indeksy),

►Maksymalna liczba obiektów w bazie: Maksymalna liczba obiektów w bazie: 32 32 768768, ,

►Maksymalna liczba jednoczesnych Maksymalna liczba jednoczesnych użytkowników: użytkowników: 255255 (w praktyce dużo mniej), (w praktyce dużo mniej),

►Maksymalna liczba pól w tabeli: Maksymalna liczba pól w tabeli: 255255, , ►Maksymalny rozmiar tabeli: Maksymalny rozmiar tabeli: 2 GB2 GB (wraz z (wraz z

obiektami systemowymi)obiektami systemowymi)

JET bez Access-aJET bez Access-a

►Możliwość tworzenia plików oraz Możliwość tworzenia plików oraz dostęp do danych za pomocą dostęp do danych za pomocą ADOX/ADO z poziomu np. skryptu VBSADOX/ADO z poziomu np. skryptu VBS

►Pliki MDB są obsługiwane przez inne Pliki MDB są obsługiwane przez inne aplikacje bez konieczności aplikacje bez konieczności instalowania Access-ainstalowania Access-a

Częste praktyki występujące w Częste praktyki występujące w środowisku Accessśrodowisku Access

► Architektura Frontend – BackendArchitektura Frontend – Backend► Kaskadowanie kwerendKaskadowanie kwerend► Tworzenie baz pośredniczącychTworzenie baz pośredniczących►Dynamiczne tworzenie kwerend za pomocą Dynamiczne tworzenie kwerend za pomocą

DAODAO►Wykorzystanie dynamicznych kwerend Wykorzystanie dynamicznych kwerend

krzyżowych – PIVOT-ykrzyżowych – PIVOT-y►Odwołania do formularzy wprost z SQL-aOdwołania do formularzy wprost z SQL-a► Umieszczanie funkcji z VBA w kodzie SQLUmieszczanie funkcji z VBA w kodzie SQL►Wykonywanie kodu SQL z poziomu VBAWykonywanie kodu SQL z poziomu VBA

Środowisko doceloweŚrodowisko docelowe

►SQL Server 2005/2008 ExpressSQL Server 2005/2008 ExpressSQL Server 2005/2008 Express Edition SQL Server 2005/2008 Express Edition with Advanced Serviceswith Advanced Services Pojedyncza baza do 4 GBPojedyncza baza do 4 GB 1 GB RAM1 GB RAM 1 CPU1 CPU

►SQL Server 2005/2008SQL Server 2005/2008 Brak ograniczeńBrak ograniczeń

Przygotowanie do migracjiPrzygotowanie do migracji

► Zapoznanie z migrowaną aplikacją i jej Zapoznanie z migrowaną aplikacją i jej specyfikąspecyfiką

►Określenie celu migracjiOkreślenie celu migracji Ominięcie ograniczeń Ominięcie ograniczeń Przyspieszenie działaniaPrzyspieszenie działania Całkowita migracja do nowego środowiskaCałkowita migracja do nowego środowiska

►Określenie elementów które chcemy migrować Określenie elementów które chcemy migrować ►Oszacowanie czasu potrzebnego na wykonanie Oszacowanie czasu potrzebnego na wykonanie

zadaniazadania► Zminimalizowanie ewentualnych szkód w Zminimalizowanie ewentualnych szkód w

przypadku niepowodzeniaprzypadku niepowodzenia

Warunki niezbędne do Warunki niezbędne do powodzeniapowodzenia

►Kopia bezpieczeństwa migrowanych danychKopia bezpieczeństwa migrowanych danych►Dostęp do bazy SQL Serwer oraz Dostęp do bazy SQL Serwer oraz

Odpowiedni poziom uprawnień w ramach Odpowiedni poziom uprawnień w ramach SQL Serwer-aSQL Serwer-a

►W przypadku gdy dane są współdzielone W przypadku gdy dane są współdzielone dostęp do serwera dla innych użytkownikówdostęp do serwera dla innych użytkowników

►Odpowiednią ilość miejsca na dysku (plik Odpowiednią ilość miejsca na dysku (plik danych i pliku log)danych i pliku log)

Włączenie obsługi TCP\IP dla Włączenie obsługi TCP\IP dla instancji SQLinstancji SQL

► Konieczny restart instancjiKonieczny restart instancji► Dodanie wyjątku w zaporze Dodanie wyjątku w zaporze

– port 1434 TCP i UDP– port 1434 TCP i UDP

http://support.microsoft.com/kb/841251/pl

Połączenie z InstancjąPołączenie z Instancją

► Jeżeli docelowa baz danych jest bez Jeżeli docelowa baz danych jest bez specyficznej nazwy łączymy się z nią specyficznej nazwy łączymy się z nią wpisując:wpisując: NAZWA-KOMPUTERA // nazwę w sieci lokalnejNAZWA-KOMPUTERA // nazwę w sieci lokalnej 192.168.11.12 // IP w sieci lokalnej192.168.11.12 // IP w sieci lokalnej (local) // jeżeli łączymy się lokalnie(local) // jeżeli łączymy się lokalnie

► Jeżeli docelowa instancja bazy posiada Jeżeli docelowa instancja bazy posiada nazwę nazwę SQLEXPRESSSQLEXPRESS to: to: NAZWA-KOMPUTERA\SQLEXPRESSNAZWA-KOMPUTERA\SQLEXPRESS 192.168.11.12\SQLEXPRESS192.168.11.12\SQLEXPRESS (local)\SQLEXPRESS(local)\SQLEXPRESS

MigracjaMigracja

Kreator rozszerzeniaKreator rozszerzenia

Wynik MigracjiWynik Migracji

PodsumowaniePodsumowanie

►Migruje tylko Tabele wraz z Migruje tylko Tabele wraz z ograniczeniamiograniczeniami

►Całkiem nieźle sobie radzi z relacjamiCałkiem nieźle sobie radzi z relacjami►Tworzy linki po zakończeniu procesuTworzy linki po zakończeniu procesu►Baza jest gotowa do użycia tuż po Baza jest gotowa do użycia tuż po

migracjimigracji

Microsoft SQL Server Migration Microsoft SQL Server Migration Assistant for AccessAssistant for Access

Prezentacja procesu Prezentacja procesu migracjimigracji

Wynik MigracjiWynik Migracji

PodsumowaniePodsumowanie

►Migracja Tabel i KwerendMigracja Tabel i Kwerend►Część relacji się nie migrujeCzęść relacji się nie migruje►Nie linkują się widokiNie linkują się widoki►Dziwny sposób migrowania obiektówDziwny sposób migrowania obiektów►Konieczne poprawki ręczne, Konieczne poprawki ręczne,

niewygodne przy większych projektachniewygodne przy większych projektach►Tworzone są linki do bazy docelowejTworzone są linki do bazy docelowej►Baza po migracji jest gotowa do pracyBaza po migracji jest gotowa do pracy

Migracja RęcznaMigracja Ręczna

PodsumowaniePodsumowanie

►Migracja dowolnego obiektu Migracja dowolnego obiektu zwracającego dane (Tabela, kwerenda, zwracającego dane (Tabela, kwerenda, tabela połączona)tabela połączona)

►Konieczność wykonania poprawek po Konieczność wykonania poprawek po przeniesieniu danych, np. Włączenie przeniesieniu danych, np. Włączenie autonumerowania, relacje, ograniczenia autonumerowania, relacje, ograniczenia itp.itp.

►Konieczność stworzenia linku po Konieczność stworzenia linku po przeniesieniu danychprzeniesieniu danych

Kreator DTSKreator DTS

PodsumowaniePodsumowanie

► Jako jedyny pozwala wybrać schemat Jako jedyny pozwala wybrać schemat do którego migrujemy danedo którego migrujemy dane

►Nie migruje informacji o relacjachNie migruje informacji o relacjach►Kwerendy są migrowane jako tabeleKwerendy są migrowane jako tabele►Konieczność odbudowania struktury Konieczność odbudowania struktury

bazy danychbazy danych

Ręczne podlinkowanie Ręczne podlinkowanie obiektuobiektu

Migracja zapytańMigracja zapytań

Kwerenda: Kwerenda: Add or Delete ReportsAdd or Delete Reports

SELECTSELECT Reports.ReportName Reports.ReportName

FROMFROM Reports Reports

ORDERORDER BYBY Reports.ReportName; Reports.ReportName;

CREATECREATE VIEWVIEW dbo.[Add or Delete Reports] dbo.[Add or Delete Reports]

ASAS /* migrowane kreatorem */ /* migrowane kreatorem */

SELECTSELECT TOPTOP 9223372036854775807 WITH TIES9223372036854775807 WITH TIES Reports.ReportNameReports.ReportName

FROMFROM Reports Reports

ORDERORDER BYBY Reports.ReportName Reports.ReportName

► Prostsze zapytania można migrować kreatorem lub Prostsze zapytania można migrować kreatorem lub przenieść bezpośrednio do tworzonego obiektuprzenieść bezpośrednio do tworzonego obiektu

Migracja SQL-aMigracja SQL-a

► NZ(Value, ValueToReturnIfNull)NZ(Value, ValueToReturnIfNull)► WHERE Column LIKE "*string*" WHERE Column LIKE "*string*" ► IIF(Condition, ReturnIfTrue, IIF(Condition, ReturnIfTrue,

ReturnIfValue)ReturnIfValue)► WHERE SomeDate = WHERE SomeDate =

#1/1/2005# #1/1/2005# ► date(), Time(), Format()date(), Time(), Format()► TRUE, FALSETRUE, FALSE

► ISNULL(Value, ValueToReturn)ISNULL(Value, ValueToReturn)

► WHERE Column LIKE '%string%'WHERE Column LIKE '%string%' ► CASE WHEN Condition THEN CASE WHEN Condition THEN

ReturnIfTrue ELSE ReturnIfFalse ENDReturnIfTrue ELSE ReturnIfFalse ENDWHERE SomeDate = '1/1/2005‘WHERE SomeDate = '1/1/2005‘

► Za pomocą funkcji użytkownikaZa pomocą funkcji użytkownika► 1, 01, 0

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

AccessAccess MS SQLMS SQL

Zapytania zawierające Zapytania zawierające parametryparametry

PARAMETERS [Forms]![Sales by Year PARAMETERS [Forms]![Sales by Year Dialog]![BeginningDate] DateTime, Dialog]![BeginningDate] DateTime, [Forms]![Sales by Year Dialog]![Forms]![Sales by Year Dialog]![EndingDate] DateTime;[EndingDate] DateTime;

SELECTSELECT Orders.ShippedDate, Orders.ShippedDate, Orders.OrderID, [Order Orders.OrderID, [Order Subtotals].Subtotal, Subtotals].Subtotal, Format Format ([ShippedDate],’yyyy’)([ShippedDate],’yyyy’) ASAS [Rok] [Rok]

FROMFROM Orders Orders INNERINNER JOINJOIN [Order [Order Subtotals] Subtotals] ONON Orders.OrderID=[Order Orders.OrderID=[Order Subtotals].OrderIDSubtotals].OrderID

WHEREWHERE (((Orders.ShippedDate) (((Orders.ShippedDate) IsIs NotNot NullNull AndAnd (Orders.ShippedDate) (Orders.ShippedDate) BetweenBetween Forms![Sales by Year Dialog]!Forms![Sales by Year Dialog]![BeginningDate][BeginningDate] AndAnd Forms![Sales by Forms![Sales by Year Dialog]![EndingDate]Year Dialog]![EndingDate]));));

DECLARE @D_START datetimeDECLARE @D_START datetime

DECLARE @D_STOP datetimeDECLARE @D_STOP datetime

SET @D_START = '1/1/1994'SET @D_START = '1/1/1994'

SET @D_STOP = '1/1/2999'SET @D_STOP = '1/1/2999'

SELECT Orders.ShippedDate, SELECT Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal, Orders.OrderID, [Order Subtotals].Subtotal, cast(year(Orders.ShippedDate)as cast(year(Orders.ShippedDate)as nvarchar(4))nvarchar(4)) as Rok as Rok

FROM Orders INNER JOINFROM Orders INNER JOIN

[Order Subtotals] ON [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderIDOrders.OrderID = [Order Subtotals].OrderID

WHERE (Orders.ShippedDate IS NOT NULL) WHERE (Orders.ShippedDate IS NOT NULL) AND (Orders.ShippedDate BETWEEN AND (Orders.ShippedDate BETWEEN @D_START@D_START AND AND @D_STOP@D_STOP))

AccessAccess MS SQLMS SQL

Do czego przenosić obiektyDo czego przenosić obiekty

► KwerendaKwerenda► Kilka kwerend Kilka kwerend

uruchamianych uruchamianych makremmakrem

► Kwerenda Kwerenda ParametrycznaParametryczna

►Dynamiczna Dynamiczna kwerenda kwerenda generowana z VBA generowana z VBA (Dialekt JET)(Dialekt JET)

►WidokWidok► ProceduraProcedura

► ProceduraProcedura

►Dynamiczna Dynamiczna kwerenda kwerenda przekazująca przekazująca (Dialekt T-SQL)(Dialekt T-SQL)

AccessAccess MS SQLMS SQL

Migracja użytkownikówMigracja użytkowników

► Brak możliwości bezpośredniego importu Brak możliwości bezpośredniego importu użytkowników i grup z pliku użytkowników i grup z pliku System.mdwSystem.mdw

►W razie potrzeby można importować te W razie potrzeby można importować te informacje za pomocą odpowiednio informacje za pomocą odpowiednio spreparowanego SQL-a:spreparowanego SQL-a:SELECT DISTINCT Accounts.NameSELECT DISTINCT Accounts.Name

FROM FROM MSysAccountsMSysAccounts AS Accounts IN ‘ AS Accounts IN ‘DD:\:\System.mdwSystem.mdw''

WHERE (((Accounts.Name)<>WHERE (((Accounts.Name)<>'Creator''Creator' And (Accounts.Name)<> And (Accounts.Name)<>'Engine''Engine') AND ) AND ((Accounts.FGroup)=0));((Accounts.FGroup)=0));

►Użytkownicy Użytkownicy CreatorCreator i i EngineEngine to konta to konta systemowesystemowe

►Nie ma możliwości importu hasełNie ma możliwości importu haseł

Migracja kodu VBAMigracja kodu VBA

►Usunięcie dynamicznie generowanych Usunięcie dynamicznie generowanych kwerend z kodu na rzecz kwerend z kodu na rzecz parametryzowanych obiektówparametryzowanych obiektów

►Przejście z DAO do ADO – najlepiej Przejście z DAO do ADO – najlepiej przed migracją do SQL-aprzed migracją do SQL-a

Migracja obiektów Migracja obiektów korzystających z migrowanego korzystających z migrowanego

plikupliku►Arkusze Excel-owe i aplikacje Arkusze Excel-owe i aplikacje

zewnętrznezewnętrzne Podmiana ConnectionString-aPodmiana ConnectionString-a Migracja zapytania SQL-a lub wybranie Migracja zapytania SQL-a lub wybranie

nowego obiektunowego obiektu

Wykorzystanie procedur z Wykorzystanie procedur z MSSQL-aMSSQL-a

►ADO z poziomu kodu VBAADO z poziomu kodu VBA►Kwerendy przekazująceKwerendy przekazujące

Integracja SQL z AccessIntegracja SQL z Access►Linked Servers - KreatorLinked Servers - Kreator

Linked Servers z T-SQLLinked Servers z T-SQL

EXEC EXEC sp_addlinkedserversp_addlinkedserver @server = 'AccessSource', @server = 'AccessSource', @provider = '@provider = 'Microsoft.Jet.OLEDB.4.0Microsoft.Jet.OLEDB.4.0', ', @srvproduct = 'OLE DB Provider for Jet', @srvproduct = 'OLE DB Provider for Jet', @datasrc = '@datasrc = 'D:\Downloads\01017508.mdbD:\Downloads\01017508.mdb''

SELECT * FROM AccessSource... SELECT * FROM AccessSource... EmployeesEmployees

SELECT * FROM SELECT * FROM OPENQUERY(AccessSource,’SELECT * FROM OPENQUERY(AccessSource,’SELECT * FROM EmployeesEmployees’)’) AS T AS T

Wymagane są odpowiednie uprawnienia do dodania

Korzystać może każdy użytkownik

OENROWSETOENROWSET

SELECT * SELECT * FROM FROM OPENROWSET('Microsoft.Jet.OLOPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\EDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'; SAMPLES\Northwind.mdb';

'admin';'',Customers)'admin';'',Customers)

OPENDATASOUROPENDATASOURCECE

SELECT *SELECT *

FROM FROM OPENDATASOURCE(OPENDATASOURCE(

'Microsoft.Jet.OLEDB.4.0','Microsoft.Jet.OLEDB.4.0',

'Data Source="C:\Program 'Data Source="C:\Program Files\Microsoft Office\Files\Microsoft Office\OFFICE11\SAMPLES\OFFICE11\SAMPLES\Northwind.mdb";Northwind.mdb";

User ID=Admin;Password='User ID=Admin;Password='

))... Customers... Customers

Wymagane są odpowiednie uprawnienia do skorzystania

Wymagają włączenia:

sp_configure 'show advanced options', 1reconfigure

Nie wymaga dodawania Linked Servers

UPDATE i DELETEUPDATE i DELETE

UPDATE UPDATE OPENQUERY(AccessSource, OPENQUERY(AccessSource, 'select * from Employees')'select * from Employees')

SET Title = 'test'SET Title = 'test'

WHERE (EmployeeID = 2)WHERE (EmployeeID = 2)

DELETE FROM DELETE FROM OPENQUERY(AccessSource, OPENQUERY(AccessSource, 'select * from Employees')'select * from Employees')

WHERE (EmployeeID = 2)WHERE (EmployeeID = 2)

Wykorzystanie funkcjonalności Wykorzystanie funkcjonalności MS SQL w migrowanej aplikacjiMS SQL w migrowanej aplikacji

► Wykorzystanie mechanizmów bezpieczeństwa i Wykorzystanie mechanizmów bezpieczeństwa i integracji ze środowiskiem domenowymintegracji ze środowiskiem domenowym SQL wie kto jest użytkownikiem bez podania loginu i hasłaSQL wie kto jest użytkownikiem bez podania loginu i hasła

► Triggery – Triggery – uwaga na akcje w formularzachuwaga na akcje w formularzach► Możliwość przeniesienia logiki na serwer Możliwość przeniesienia logiki na serwer

(procedury, funkcje i inne)(procedury, funkcje i inne)► Zaawansowany SQL np. CTEZaawansowany SQL np. CTE► Stworzenie punktu wyjściowego do dalszego Stworzenie punktu wyjściowego do dalszego

rozwoju środowiska np.. Migracja do ASP.NET lub rozwoju środowiska np.. Migracja do ASP.NET lub VB.NET / C#VB.NET / C#

► Server Agent i Job-y (tylko w wersji pełnej)Server Agent i Job-y (tylko w wersji pełnej) Import danychImport danych BackupyBackupy Inne funkcjeInne funkcje

Zagrożenia związane z Zagrożenia związane z migracjąmigracją

► Niepełna migracja struktury bazy danychNiepełna migracja struktury bazy danych► Trudności z przetworzeniem zapytań do Trudności z przetworzeniem zapytań do

postaci natywnej MSSQLpostaci natywnej MSSQL► Problemy z dostępem do danych z Problemy z dostępem do danych z

podlinkowanych tabelachpodlinkowanych tabelach Dodanie kolumny typu TimestampDodanie kolumny typu Timestamp Konieczność ponownego linkowania tabeli po Konieczność ponownego linkowania tabeli po

zmianie w SQL-uzmianie w SQL-u► Specyficzna konstrukcja aplikacji – Specyficzna konstrukcja aplikacji –

bezpośrednie manipulacje na obiektach bazy bezpośrednie manipulacje na obiektach bazy danychdanych

► Błędy merytoryczne w przenoszeniu Błędy merytoryczne w przenoszeniu funkcjonalnościfunkcjonalności

Pytania i DyskusjaPytania i Dyskusja