[.NET/SQL Server/Access] Synchronisatie

[Verwijderd]

Situatie

Er is momenteel een applicatie geschreven in .NET die gebruik maakt van een SQL server database. Deze database bevat vaste systeem informatie en variabele informatie toegevoegd door gebruikers. Deze opstelling kan beschouwd worden als "online".

Dezelfde applicatie is ook beschikbaar in combinatie met een Acccess database. Deze database bevat dezelfde informatie (schema's en data) als de SQL server database. Echter, deze database wordt alleen gebruikt om de variabele informatie te kunnen muteren. (De systeem informatie wordt alleen gebruikt.) Deze opstelling kan beschouwd worden als "offline".

Eens in de zoveel tijd kunnen er wijzigingen plaatsvinden aan de schema's van zowel de systeem als variabele tabellen. En eens in de zoveel tijd kunnen er wijzingen plaatsvinden in de informatie van de systeem tabellen. Dit gebeurt in de "online" opstelling en deze wijzigingen moeten dus gesynchroniseerd worden met de "offline" opstelling.

Wat wil ik?

Mijn eerste idee is een synchronisatie laag (webservice, applicatie, dat is nu nog niet belangrijk) te maken die de schema's van de tabellen en de systeem informatie bijwerkt.

Dit heb ik al eens eerder gemaakt, en zou dus bestaan uit de volgende workflow:
- open connectie met de "online" database;
- bepaal wijzigingen door de tabel schema's in te lezen en de systeem informatie;
- sluit connectie met de "online" database;
- open connectie met de "offline" database;
- voer wijzingen door;
- sluit connectie met de "offline" database.

Wat is nu het probleem?

Op zich is zo'n laag wel te maken, maar dit gaat waarschijnlijk erg groot en complex worden en alvorens er wordt besloten om hiermee te beginnen, wil ik eerst mijn mogelijkheden onderzoeken.

Wat heb ik uitgezocht?

Gezocht op Tweakers forum en Google naar synchronisatie tussen databases. Helaas krijg ik veel topics over het volgende:

- O/R mapping/ Dirty/ Versioning
- Upsizing van Access naar SQL server

Dat wil ik dus niet. Ik zoek dus een patroon, richtlijn o.i.d. om het synchroniseren van tabel schema's en data tussen twee databases (ongeacht welke) te realiseren. (Als dit betekent dat ik dat dan stap-voor-stap voor elke tabel en kolom moet nalopen, dan mag dat ook gezegd worden .)

[lier]

Ik denk dat je in ieder geval moet kijken naar de verbinding met je server.
Gaat het synchroniseren via een ADSL lijn, op de zaak via een LAN verbinding of betreft het een inbelverbiding (UMTS).

Voor het oplossen van deze problematiek, zijn natuurlijk ontzettend veel mogelijkheden.

Wat bedoel je met tabel schema's ?
Is het (ook in jouw geval) zo dat de synchronisatie twee kanten op moet werken ?

Een eenvoudige oplossing is een mutatiedatum bij je gegevens op te slaan. Op basis hiervan kan je zien of een mutatie later heeft plaats gevonden dan de laatstse update. Uiteraard moet je dan wel één systeem leidend maken en is de datumtijd instelling in betrokken systemen van levensbelang.

De grap is dat ik op dit moment aan exact zo'n systeem werk. Wij gaan hier voor een webservice waarmee de synchronizatie uitgevoerd gaat worden.

[Verwijderd]

lier schreef op dinsdag 22 augustus 2006 @ 11:42:
Wat bedoel je met tabel schema's ?

Tabelnaam;
Kolomnamen;
Kolomtypen;
Pk's;
Indices;
Etc.

lier schreef op dinsdag 22 augustus 2006 @ 11:42:
Is het (ook in jouw geval) zo dat de synchronisatie twee kanten op moet werken ?

Nee. Alleen van "online" naar "offline".

lier schreef op dinsdag 22 augustus 2006 @ 11:42:
Een eenvoudige oplossing is een mutatiedatum bij je gegevens op te slaan. Op basis hiervan kan je zien of een mutatie later heeft plaats gevonden dan de laatstse update. Uiteraard moet je dan wel één systeem leidend maken en is de datumtijd instelling in betrokken systemen van levensbelang.

Maar dat betekent hoe dan ook dat je toch wel alle tabellen en kolommen moet nalopen?

lier schreef op dinsdag 22 augustus 2006 @ 11:42:
De grap is dat ik op dit moment aan exact zo'n systeem werk. Wij gaan hier voor een webservice waarmee de synchronizatie uitgevoerd gaat worden.

Succes

[lier]

Ik neem aan dat de schema's niet wijzigen !?

Bij een mutatie kan je een vinkje zetten dat een bepaalde mutatie in het systeem uitgevoerd is. Dan kan tijdens het synchronizeren deze gegevens aangeboden worden. Wij hebben hierbij voor een apparte tabel met mutaties gekozen, hier kan heel snel queries op uitgevoerd worden.

We horen graag hoe jij het aanpakt !

[bazzs2001]

Het kan aan de hoeveelheid data liggen, maar wat ik recent met iets vergelijkbaars heb gedaan (van MySQL naar Access (maar niet met exact dezelfde structuur), is het gewoon kopiëren van alle data (dus eerst de tabel van de kopie leeg maken).

Dit kan natuurlijk heel tijdrovend zijn als je elke keer dezelfde data overstuurt, maar je moet in principe toch de data overhalen om te kijken naar verschillen.

Het is misschien wel heel onhandig als het mis gaat (waardeloze kopie).

offtopic:
ps. Vrieler gaan we nog wat drinken dinsdag de 5e?

[joepP]

Als het niet om teveel data gaat kan je ook best alles kopieren, ik neem niet aan dat de database zo heel vaak van structuur wijzigd?

Het uitlezen van de systeemtabellen kan lastig worden, en foutgevoelig. Je hebt te maken met de volgorde van de aanpassingen: die weet je niet, en is wel belangrijk inzake keys. Als de database niet al te vaak wijzigd kan je door middel van normale code de Access database aanpassen. Dan moet je wel voor elke aanpassing een stukje updatecode schrijven, maar de overhead daarvan valt waarschijnlijk wel mee: als je aan de database gaat rommelen moet je toch al stukken van je applicatie herschrijven.

[ Voor 7% gewijzigd door joepP op 24-08-2006 11:45 ]

[Verwijderd]

Voornamelijk de inhoud van de systeemtabellen zullen vaak veranderen. Dus een complete DELETE en opnieuw een INSERT van alle data is een mogelijkheid en zal waarschijnlijk beter zijn dan alle individuele wijzingingen bij te houden.

Wat betreft de wijzigingen aan de structuur van de systeemtabellen EN gebruikerstabellen zitten we te denken aan inderdaad een ALTER statement per wijziging bij te houden. Op deze manier laten we alle data intact.

We kunnen deze statements dan in een SQL script stoppen en pompen naar de diversen Access databases die her en der verspreid zijn.

Wat denken jullie van deze opzet? Afgezien van het feit dat elke wijziging bijgehouden moet worden (wat niet echt een probleem is), moet er telkens een nieuw SQL script gemaakt worden. En daarnaast weet ik ook niet zeker of Access deze scripts wel kan verwerken?

offtopic:
bazzs2001, dinsdag de 5de wordt een beetje problematisch om bier te nuttigen, aangezien ik 's middags weer moet werken .
A.s. maandag is ook een optie, als jij kan. Anders moeten we een keer 's avonds wat afspreken (donderdag of zo).

[bazzs2001]

Verwijderd schreef op donderdag 24 augustus 2006 @ 12:32:
Wat betreft de wijzigingen aan de structuur van de systeemtabellen EN gebruikerstabellen zitten we te denken aan inderdaad een ALTER statement per wijziging bij te houden. Op deze manier laten we alle data intact.

We kunnen deze statements dan in een SQL script stoppen en pompen naar de diversen Access databases die her en der verspreid zijn.

Wat denken jullie van deze opzet? Afgezien van het feit dat elke wijziging bijgehouden moet worden (wat niet echt een probleem is), moet er telkens een nieuw SQL script gemaakt worden. En daarnaast weet ik ook niet zeker of Access deze scripts wel kan verwerken?

Is het niet mogelijk om de structuur van de database ook uit te lezen en te kopiëren naar de offline versie? Volgens mij moet het wel mogelijk zijn om dit te doen via een odbc connectie (of ADO.NET, maar ik weet niet hoe goed ADO werkt met Access)

offtopic:
Kan jij niet aankomende maandag, maar de volgende? 's avonds?

[Verwijderd]

Het kan wel met Data Transformation Packages in SQL server en naar Access, daarin kun je volgens mij ook ALTER statements in kwijt.

Maar het probleem is dat die Access databases op een onbereikbare plek staan (misschien had ik dat even moeten zeggen ), nl. lokaal bij de gebruiker. Dus het liefst moeten de database wijzigingen worden uitgevoerd tijdens de installatie van een nieuwe versie (of alleen de database wijzigingen als er geen nieuwe versie is, bijv. alleen wijzigingen in de data).


offtopic:
Dat kan geregeld worden. Maandag 4 sept. we bellen nog wel.

[bazzs2001]

Verwijderd schreef op donderdag 24 augustus 2006 @ 13:52:
Het kan wel met Data Transformation Packages in SQL server en naar Access, daarin kun je volgens mij ook ALTER statements in kwijt.

Maar het probleem is dat die Access databases op een onbereikbare plek staan (misschien had ik dat even moeten zeggen ), nl. lokaal bij de gebruiker. Dus het liefst moeten de database wijzigingen worden uitgevoerd tijdens de installatie van een nieuwe versie (of alleen de database wijzigingen als er geen nieuwe versie is, bijv. alleen wijzigingen in de data).

Ow, dus het is min of meer eenmalig (per versie) dat de database gekopiëerd wordt?

Dan zou ik helemaal gewoon een kopie maken van de database (inclusief structuur), ook omdat je anders bij moet gaan houden per gebruiker wat er gewijzigd is.

(ik ga er hier wel van uit dat de inhoud uit de Access DB niet meer relevant is)

offtopic:
offtopic verder over mail...

[Verwijderd]

bazzs2001 schreef op donderdag 24 augustus 2006 @ 16:18:
(ik ga er hier wel van uit dat de inhoud uit de Access DB niet meer relevant is)

Ja en dat is nu net het punt. De inhoud is juist wel relevant, want de gebruikers die op locatie data invoeren (in de gebruikerstabellen dus) willen natuurlijk dat na een wijziging van de database en/of een nieuwe versie, al hun data intact blijft.

De enige mogelijkheid die ik, en mijn collega, het nu over eens zijn, is dat we een soort van SQL script telkens moeten maken die (stilzwijgend) de aanpassingen aan systeem- en gebruikerstabellen doet (zij het nodig) en de data in de systeemtabellen "ververst" (dit is natuurlijk nooit een probleem, dat bepalen wij namelijk).

We moeten alleen nog een mooie workflow zien te definiëren over hoe we nu zo'n SQL script gaan maken, zonder veel overhead te krijgen. (Zodat je niet na een half jaar krijgt: "ow, hoe deden we dat ook alweer?" )

[bazzs2001]

Verwijderd schreef op donderdag 24 augustus 2006 @ 21:02:
[...]


Ja en dat is nu net het punt. De inhoud is juist wel relevant, want de gebruikers die op locatie data invoeren (in de gebruikerstabellen dus) willen natuurlijk dat na een wijziging van de database en/of een nieuwe versie, al hun data intact blijft.

Hmm, dat wordt lastig als je de structuur gaat wijzigen...

De enige mogelijkheid die ik, en mijn collega, het nu over eens zijn, is dat we een soort van SQL script telkens moeten maken die (stilzwijgend) de aanpassingen aan systeem- en gebruikerstabellen doet (zij het nodig) en de data in de systeemtabellen "ververst" (dit is natuurlijk nooit een probleem, dat bepalen wij namelijk).

Tip test alles goed(met dezelfde data als de klant) voordat je de daadwerkelijke update, want er wil nog wel eens wat fout gaan door onverwachte data (overbodige tip misschien)

We moeten alleen nog een mooie workflow zien te definiëren over hoe we nu zo'n SQL script gaan maken, zonder veel overhead te krijgen. (Zodat je niet na een half jaar krijgt: "ow, hoe deden we dat ook alweer?" )

Dat krijg je toch wel... (des te meer reden om een mooie workflow te genereren)

[Verwijderd]

Microsoft heeft afgelopen jaar een paar leuke .NET buzzwords in het leven geroepen: Smart Client en ClickOnce. Met de eerste kun je o.a. ook offline verder werken met de data die je nodig hebt (is zelfs een vereiste om je applicatie 'smart client' te mogen noemen), en met ClickOnce kun je simpel je applicatie en prerequisites updaten wanneer je weer online bent.

Hoe ze dat allemaal willen doen weet ik niet (al werkt ClickOnce wel heel erg gemakkelijk, deployen is een makkie), maar aangezien zowel MSSQL als Access MS producten zijn zal daar toch ook wel aan gedacht zijn?

[bazzs2001]

Verwijderd schreef op donderdag 24 augustus 2006 @ 23:06:
Microsoft heeft afgelopen jaar een paar leuke .NET buzzwords in het leven geroepen: Smart Client en ClickOnce. Met de eerste kun je o.a. ook offline verder werken met de data die je nodig hebt (is zelfs een vereiste om je applicatie 'smart client' te mogen noemen), en met ClickOnce kun je simpel je applicatie en prerequisites updaten wanneer je weer online bent.

Hoe ze dat allemaal willen doen weet ik niet (al werkt ClickOnce wel heel erg gemakkelijk, deployen is een makkie), maar aangezien zowel MSSQL als Access MS producten zijn zal daar toch ook wel aan gedacht zijn?

Het klinkt heel mooi (to good to be true?), maar werkt het ook als de databasestructuur gewijzigd wordt?

Ik weet van MySQL dat je een "replicate" server kan hebben draaien die zichzelf update wanneer je offlne gaat(waarbij volgens mij ook de structuur meegenomen wordt naar het kopie), maar wijzigingen in de kopie gaan verloren (of van alles kan mis gaan) bij synchronizatie.

[joepP]

Verwijderd schreef op donderdag 24 augustus 2006 @ 21:02:
[...]

De enige mogelijkheid die ik, en mijn collega, het nu over eens zijn, is dat we een soort van SQL script telkens moeten maken die (stilzwijgend) de aanpassingen aan systeem- en gebruikerstabellen doet (zij het nodig) en de data in de systeemtabellen "ververst" (dit is natuurlijk nooit een probleem, dat bepalen wij namelijk).

Let wel goed op dat je die scripts uitvoerig test en documenteert, want verkeerde drivers of andere versies van Access kunnen misschien problemen geven. Het maken van een backup van de complete database alvorens de scripts uit te voeren lijkt me geen overbodige luxe, inclusief een mooie logfile van wat er precies is uitgevoerd enzo. Anders ben je gruwelijk zuur in het geval van een mislukte update: een database die niet consistent is.

En vergeet het backup/restore gebeuren ook niet goed te testen

[MTWZZ]

Ik weet uit ervaring dat je van zowel access als SQL Server gewoon de structuur uit kunt lezen en die ook direct programmatisch kunt wijzigen (access iig en SQL Server lijkt me ook).
Wat ik me afvraag, mede omdat de structuur alleen wijzigt bij een versie change, is of je niet gewoon een SQL script kunt genereren met daarin je structuur changes.
Bij het project waar ik nu op zit doen we dit ook. Het stelt wel wat meer eisen aan je versiebeheer procedures etc, maar je kunt dit soort scripts dan gewoon implementeren in je installer dmv custom actions oid.

[H!GHGuY]

Ik vraag me af of je niet gewoon een tabel kan maken met queries en een timestamp.

de client kan dan alle queries opvragen later dan zijn eigen laatste timestamp.
Per record voert hij de query uit en voegt hij deze record toe aan zijn eigen tabel met updates.

een update kan dan bestaan uit bvb INSERT, ALTER en DELETE statements met timestamps zodat ze op volgorde zijn.

[bazzs2001]

HIGHGuY schreef op zaterdag 26 augustus 2006 @ 14:34:
Ik vraag me af of je niet gewoon een tabel kan maken met queries en een timestamp.

de client kan dan alle queries opvragen later dan zijn eigen laatste timestamp.
Per record voert hij de query uit en voegt hij deze record toe aan zijn eigen tabel met updates.

een update kan dan bestaan uit bvb INSERT, ALTER en DELETE statements met timestamps zodat ze op volgorde zijn.

Maar wat als je een DELETE doet dat effect heeft op iets wat in de database van de klant anders is als in de originele?

Vrieler nog nieuws over de aanpak?

[Verwijderd]

bazzs2001 schreef op dinsdag 29 augustus 2006 @ 09:25:
[...]

Maar wat als je een DELETE doet dat effect heeft op iets wat in de database van de klant anders is als in de originele?

Dat zou in principe niet aan de orde moeten zijn, aangezien we alleen ALTER statements aan de structuur doen. Data die we eventueel moeten verwijderen, geschiedt alleen in de Systeemtabellen en daar hebben wij de controle over

Vrieler nog nieuws over de aanpak?

We moeten inderdaad nog beginnen , maar er zijn even wat andere implementaties die een hogere prioriteit hebben.

We zitten overigens wel te denken aan het idee van MTWZZ, of dit idee te combineren met het idee van HighGuy. Het ligt eraan hoe makkelijk een Installer is te Customizen en mijn ervaring met Installshield zegt dat dit niet zo makkelijk is.

We zouden namelijk ook bij iedere distributie een klein Access-database-je met een enkele tabel kunnen meedistibueren waarin alle SQL statements staan die uitgevoerd dienen te worden (bijv. in de opstart routine van de applicatie). Dit Access-database-je kan dan na uitvoer worden verwijderd.

[MTWZZ]

Verwijderd schreef op dinsdag 29 augustus 2006 @ 11:01:
We zitten overigens wel te denken aan het idee van MTWZZ, of dit idee te combineren met het idee van HighGuy. Het ligt eraan hoe makkelijk een Installer is te Customizen en mijn ervaring met Installshield zegt dat dit niet zo makkelijk is.

Tsja met installshield heb ik geen ervaring maar .Net deployment projects kunnen het vrij eenvoudig.

[Verwijderd]

MTWZZ schreef op dinsdag 29 augustus 2006 @ 11:20:
[...]

Tsja met installshield heb ik geen ervaring maar .Net deployment projects kunnen het vrij eenvoudig.

Niet aan beginnen , één van de redenen waarom ik nu in een .NET project bezig ben. Ik ga eens uitzoeken hoe ik dit kan bewerkstelligen met Deployment projects. Ik laat mijn bevindingen hier nog wel zien.

[MTWZZ]

Meer!

Maar wees gewaarschuwd VS2005 icm een database project en een deployment project == braque meuk

VS2005 heeft een (KNOWN bij de weg) bug dat deployment projecten gecorrupt worden als er een database project in je solution zit. Erg irritant. Dit geintje kost bij ons gauw een uur extra bij een release

[Verwijderd]

Bedankt voor de tip. Overigens zijn we hier nog bezig in VS2003, dus dat moet wel lukken. (Mochten we ooit gaan migreren, dan hebben ze het hopelijk opgelost. )

[H!GHGuY]

Het doel van de tabel zoals ik ze zeg is om de structuur van de DB te vervormen naar de vorm zoals jij ze hebt. Zie het als een versie-systeem waarbij elke query n als supplement op de reeksen queries (n-1, n-2, ... 1) dient. Dus elke query stelt een versie voor.

Uiteindelijk kun je dan geen DELETE doen op iets wat niet bestaat aangezien de versie hetzelfde is.
(als je je aan de volgorde houdt natuurlijk)

[Verwijderd]

HIGHGuY schreef op dinsdag 29 augustus 2006 @ 18:35:
Uiteindelijk kun je dan geen DELETE doen op iets wat niet bestaat aangezien de versie hetzelfde is.
(als je je aan de volgorde houdt natuurlijk)

Een DELETE zou in ons geval wel kunnen, aangezien er Systeem data in de tabellen (bij de gebruiker) staan die wellicht inconsistent is met onze Systeem data.

Op zich is een versiesysteem wel ideaal, aangezien je zou kunnen ROLLBACK-en in geval van een fout. Het enige nadeel zou kunnen zijn, dat we de gegevens in deze Versiebeheertabel niet gemakkelijk bij een gebruiker lokaal krijgen. Daarom was het idee van een klein Access database of een script meedistribueren bij de installatie (dus een databaseproject in het Setup Deployment Project van VStudio) ontstaan.

Versiebeheer is in ieder geval een concept waar we rekening mee moeten gaan houden.