[SQL Server 2000] Waar plaats ik m'n history tabellen.

[whoami]

Momenteel ben ik bezig met het ontwikkelen van een 'redelijk' grote database.

De gegevens in deze databank zullen regelmatig ge-updated worden (zowel manueel als door 'automatische processen'.
Eén van de requierements van dit systeem is dat de 'historische data' bijgehouden wordt.

Nu had ik gedacht om dit zo eenvoudig mogelijk op te stellen, nl. dmv insert/update triggers op de tabellen die bij een insert of update dus de 'oude' versie van de gegevens naar een history tabel zet.
Nu wil ik natuurlijk dat de database zo performant mogelijk blijft, en vraag ik me dus af waar ik het best deze history tables plaats.
Is het voldoende als ik deze tabellen in een nieuwe FILEGROUP binnen dezelfde database zet, of zou het toch beter zijn als ik deze history tabellen in een aparte databank zet ?

Heeft iemand hier ervaring mee, of heeft er hier iemand een weloverwogen voorkeur die hij kan onderbouwen ?

[whoami]

Ik had zelf eerst gedacht om m'n history tables in dezelfde DB, maar op een andere filegroup te zetten, maar nu neig ik toch meer en meer om ze in een aparte DB te stoppen.
Mocht de server waarop de 'live' DB staat het toch niet meer aankunnen, dan is het relatief makkelijk om die history databank simpelweg naar een andere machine te zetten.

Dan is er nog 1 ding waar ik nog niet volledig uit ben:
Zorg ik ervoor dat de data-veranderingen dmv Triggers op de 'live' database in m'n historische databank komen, of plaats ik die logica in m'n 'Core Components' (Dit is zowiezo de enige plek die toegang heeft tot de databank).
Als ik die logica in m'n core components plaats, dan heb ik als voordeel dat ik flexibeler kan omspringen als de history database niet beschikbaar is. (Ik kan er dan voor zorgen dat de wijzigingen toch nog doorgevoerd worden in de 'live' database, maar dan zijn er wel geen sporen van te vinden in de historische DB (iets wat imo niet echt gewenst is, maar dat moet ik nog eens met de projectleader overleggen).

[TeeDee]

whoami schreef op woensdag 08 december 2004 @ 16:48:
Ik had zelf eerst gedacht om m'n history tables in dezelfde DB, maar op een andere filegroup te zetten,....

Zelf weinig tot geen ervaring die lijkt op jouw vraag, maar logisch nagedacht lijkt me dit inderdaad het meest "verantwoordelijke".

Dan is er nog 1 ding waar ik nog niet volledig uit ben:...

Hier zeg je het zelf al: Je bent flexibeler.
Wat je eventueel zou kunnen doen aan het feit of je "hdb" niet aanwezig is, om het tijdelijk op een backup hdb op de 'live' db. Het is misschien allemaal omslachtig, maar zo ben je imho verzekerd van de historische data.

[ Voor 4% gewijzigd door TeeDee op 08-12-2004 16:54 ]

[bigbeng]

Nog een extra mogelijkheid (hoe realistisch ie is, dat laat ik aan anderen over):
Historische data in een externe db die je bijhoudt dmv je core components.
Indien de externe db offline is, dan store je historische data in de normale db (waar je dus ook history tabellen in hebt).
Komt vervolgens de andere db weer online, dan synchroniseer je (dit kan dmv subscriptions geloof ik) de data in je externe history tabellen met die van je lokale history.

Op deze manier altijd history, met als nadeel dat je bij wegvallen van de externe db je extra load krijgt op de normale db.

Phoewie, dat was best even nadenken, ik hoop dat ik mijzelf ook duidelijk heb kunnen maken

[P_de_B]

Wat is het voordeel van een externe DB? Historietabellen zullen niet een zware belasting zijn denk ik, het zijn voornl. inserts. Er zal denk ik niet erg intensief gewerkt worden met de tabellen. De mogelijkheid om het naar een externe db te plaatsen lijkt me niet echt relevant.

Door het in een andere db, of zelfs een andere server te doen krijg je wel te maken met dingen die je zelf al een beetje noemt; wat te doen als de historie tabel offline is op het moment van acties op de gewone tabellen? Ook moet je rechten etc op meerdere plekken bijhouden.

Ik zou het op een andere filegroup in dezelfde database gebruiken. Eventueel kan het zelfs op een eenvoudige schijf. Het bijhouden van de historietabellen is denk ik het netste in de core components, maar ik kan dat niet erg goed beargumenteren. We hebben zelf net een oplossing met triggers gemaakt, puur omdat dat het meest eenvoudige was om op korte termijn toe te passen. Ik denk dat voor beide mogelijkheden wel iets te zeggen is. Houd er wel rekening mee dat je @@Identity niet meer kunt gebruiken als je met triggers te maken hebt.

[Verwijderd]

offtopic:
Maak je zo geen 'oneigenlijk' gebruik van triggers? Het is waarschijnlijk een puristen discussie, maar ik dacht dat deze vooral bedoeld waren om 'foreign contraints' (of wat de offciele term ook is) goed af te handelen. In principe misbruik je ze nu om iets te bereiken wat je eigenlijk in je programma moet afhandelen en niet aan de DBMS moet overlaten, toch...?

Gewoon ff wat geblaat ter discussie

[Verwijderd]

whoami schreef op woensdag 08 december 2004 @ 16:48:
Als ik die logica in m'n core components plaats, dan heb ik als voordeel dat ik flexibeler kan omspringen als de history database niet beschikbaar is. (Ik kan er dan voor zorgen dat de wijzigingen toch nog doorgevoerd worden in de 'live' database, maar dan zijn er wel geen sporen van te vinden in de historische DB (iets wat imo niet echt gewenst is, maar dat moet ik nog eens met de projectleader overleggen).

Ik zou die afhandeling in de database willen houden. Een paar redenen
- De history tabellen zijn feitelijk "verplicht", anders zou je ze niet willen bijhouden. Je flexibiliteit is dus geen reeel voordeel
- Wijzigingen die rechtstreeks op de database gedaan worden houden de historie in stand.
- De transactie wordt een stuk simpeler voor de ontwikkelaar van de client. Simpel de aanpassing doen die je wilt, de db doet de rest.

Wat betreft de locatie aparte DB of Filegroup ben ik het met P de B eens, het heeft weinig voordeel en maakt een hoop zaken onnodig lastig. Wel is het misschien een overweging waard om je OLTP database te repliceren naar een andere database voor data analyse. Die (meestal nogal zware) queries verstoren dan de normale transactie verwerking niet.

Maak je zo geen 'oneigenlijk' gebruik van triggers? Het is waarschijnlijk een puristen discussie, maar ik dacht dat deze vooral bedoeld waren om 'foreign contraints' (of wat de offciele term ook is) goed af te handelen. In principe misbruik je ze nu om iets te bereiken wat je eigenlijk in je programma moet afhandelen en niet aan de DBMS moet overlaten, toch...?

Foreign keys controleer je in principe gewoon door ze te definieren op de tabel. Triggers zijn bedoeld om Bussiness Logic die verplicht moet worden uitgevoerd bij een actie op een tabel uit te voeren. Denk hierbij aan het controleren van zaken zoals de correctheid van een bankrekeningnummer maar dus ook van het updaten van gerelateerde tabellen. Als het moet gebeuren bij een update van de tabel is de trigger een juiste plaats. Maak je gebruik van een applicatie server (multi tier constructie) kun je de business rules ook op de applicatieserver controleren maar zelf vind ik dat alles wat database inconsistentie kan veroorzaken én waar "client" programma's geen weet van hoeven te hebben het beste op de server afgehandeld kan worden.

[whoami]

P_de_B schreef op woensdag 08 december 2004 @ 22:29:
Wat is het voordeel van een externe DB? Historietabellen zullen niet een zware belasting zijn denk ik, het zijn voornl. inserts. Er zal denk ik niet erg intensief gewerkt worden met de tabellen. De mogelijkheid om het naar een externe db te plaatsen lijkt me niet echt relevant.

De History tabellen zullen serieus groeien, en ze zouden de performance van de DB (de 'live' tables dan) eigenlijk zo weinig mogelijk mogen beïnvloeden.
Ik weet niet of dat het voldoende is om het op een aparte filegroup te plaatsen. Misschien kan ik er idd nog voor opteren om ze op een filegroup te zetten die op een aparte disk staat.

Door het in een andere db, of zelfs een andere server te doen krijg je wel te maken met dingen die je zelf al een beetje noemt; wat te doen als de historie tabel offline is op het moment van acties op de gewone tabellen? Ook moet je rechten etc op meerdere plekken bijhouden.

Hmm, da's wel waar.

Ik zou het op een andere filegroup in dezelfde database gebruiken. Eventueel kan het zelfs op een eenvoudige schijf. Het bijhouden van de historietabellen is denk ik het netste in de core components, maar ik kan dat niet erg goed beargumenteren. We hebben zelf net een oplossing met triggers gemaakt, puur omdat dat het meest eenvoudige was om op korte termijn toe te passen. Ik denk dat voor beide mogelijkheden wel iets te zeggen is. Houd er wel rekening mee dat je @@Identity niet meer kunt gebruiken als je met triggers te maken hebt.

Indien de history in een andere DB komt, dan is het idd misschien wel de beste oplossing om het in de core components te doen.
Indien ik de history op een andere DB zet, en het toch via triggers regel, dan zou ik er moeten voor kunnen zorgen dat m'n triggers niet binnen dezelfde transactie zitten.
(Identity gebruik ik niet, alle PK's zijn GUID's)

[ Voor 3% gewijzigd door whoami op 09-12-2004 08:45 ]

[whoami]

Verwijderd schreef op woensdag 08 december 2004 @ 23:16:

offtopic:
Maak je zo geen 'oneigenlijk' gebruik van triggers? Het is waarschijnlijk een puristen discussie, maar ik dacht dat deze vooral bedoeld waren om 'foreign contraints' (of wat de offciele term ook is) goed af te handelen. In principe misbruik je ze nu om iets te bereiken wat je eigenlijk in je programma moet afhandelen en niet aan de DBMS moet overlaten, toch...?

Gewoon ff wat geblaat ter discussie

Triggers zijn zeker niet uitsluitend bedoeld om foreign keys af te handelen.
Sterker zelfs, ik zie geen reden wat triggers met foreign keys te maken hebben. Als je cascaded deletes wil, dan kan je dat definieren op het niveau van de foreign key constraint en hoef je de records in gerelateerde tabellen niet via de triggers te gaan verwijderen (althans, dit is zo vanaf Sql Server 2000 dacht ik).
Om even op je punt in te gaan: is het iets dat het programma moet afhandelen? Ik weet het niet, het is een beetje vaag: is het data logica of applicatie logica ? IMHO is het bijhouden van history informatie data-logica die best op niveau van het DBMS kan gebeuren.

[whoami]

Verwijderd schreef op donderdag 09 december 2004 @ 02:55:
[...]


Ik zou die afhandeling in de database willen houden. Een paar redenen
- De history tabellen zijn feitelijk "verplicht", anders zou je ze niet willen bijhouden. Je flexibiliteit is dus geen reeel voordeel
- Wijzigingen die rechtstreeks op de database gedaan worden houden de historie in stand.
- De transactie wordt een stuk simpeler voor de ontwikkelaar van de client. Simpel de aanpassing doen die je wilt, de db doet de rest.

In een ideale situatie zouden de history tabellen idd verplicht moeten zijn.
Wat de ontwikkelaar van de client betreft: die gebruikt gewoon de core components, en zou zich dus eigenlijk ook niets moeten aantrekken van het hele history gebeuren.

[P_de_B]

Als ik bovenstaande lees denk ik dat het bijhouden in triggers het beste is, zeker als je ervoor besluit het in dezelfde db te doen. Jan Klaasen heeft een aantal goede argumenten. Daarnaast gaat mijn definitieve stem naar aparte filegroup op aparte disk(s) in dezelfde db.

Even een beetje offtopic, waarom gebruik je GUIDs voor je PK's ? Heb je veel met replicatie tussen databases te maken?


Overigens, wat triggers met constraints te maken hebben begrijp ik ook niet.

[whoami]

P_de_B schreef op donderdag 09 december 2004 @ 09:12:


Even een beetje offtopic, waarom gebruik je GUIDs voor je PK's ? Heb je veel met replicatie tussen databases te maken?

De database zal door 3 sites gebruikt worden, deze sites hebben dus elk hun eigen server met die DB en die 3 db's zullen dmv merge replication 'gesynchroniseerd' worden.

[P_de_B]

whoami schreef op donderdag 09 december 2004 @ 09:24:
[...]

De database zal door 3 sites gebruikt worden, deze sites hebben dus elk hun eigen server met die DB en die 3 db's zullen dmv merge replication 'gesynchroniseerd' worden.

Ok dan is het goed, dit is in mijn optiek de enige goede reden om guids als pk te gebruiken.

[JJvG]

Uit eigen ervaring raad ik je aan om een aparte database te maken. Trek een kopie van je originele structuur en sla daarin je historische records op.

Bij een grote nederlandse bank, waar ik opdrachten heb gedaan met SQL Server, groeide de recordsets vrij snel (10000 records per dag) en na een maand of drie begin je dat toch te merken in je queries. Groei was hier echter door nieuwe data en niet door history bijhouden.

Als je dus historische gegevens wilt bewaren, groeien je recordsets ook vrij snel en krijg je dus hetzelfde. Daarnaast is het zo dat mensen vaker snel willen werken met actuele data en dat inzage in historische gegevens langer mag kosten (bijv. management-info over de afgelopen weken/maanden).

Daarnaast is het zo dat je kan overwegen om aparte tabellen tabellen te maken voor historische data. Bijvoorbeeld met een workflow, kun je in een aparte tabel statushistory bijhouden. Sorteren op datum levert dan de meest recente status op.

Misschien dat je iets meer kunt vertellen over de achtergrond van de data, want dat kan nog verschil maken in je overweging.

[whoami]

P_de_B schreef op donderdag 09 december 2004 @ 09:28:
[...]


Ok dan is het goed, dit is in mijn optiek de enige goede reden om guids als pk te gebruiken.

Nouja... de enige goede reden.... Wat is het nadeel dan wel van GUID's tov gewone 'identity primary keys' als je geen gebruik maakt van replicatie? (Behalve dan dat een GUID wat meer plaats in neemt dan een int bv).

[whoami]

JJvG schreef op donderdag 09 december 2004 @ 09:30:
Als je dus historische gegevens wilt bewaren, groeien je recordsets ook vrij snel en krijg je dus hetzelfde. Daarnaast is het zo dat mensen vaker snel willen werken met actuele data en dat inzage in historische gegevens langer mag kosten (bijv. management-info over de afgelopen weken/maanden).

Idd, maar daarom zou ik die history tables dus ook op een aparte filegroup en op een aparte schijf zetten, maar dan wel in dezelfde DB.

Misschien dat je iets meer kunt vertellen over de achtergrond van de data, want dat kan nog verschil maken in je overweging.

Ik denk niet dat dit terzake doet.

[P_de_B]

whoami schreef op donderdag 09 december 2004 @ 09:30:
[...]


Nouja... de enige goede reden.... Wat is het nadeel dan wel van GUID's tov gewone 'identity primary keys' als je geen gebruik maakt van replicatie? (Behalve dan dat een GUID wat meer plaats in neemt dan een int bv).

Lastiger te zoeken, meer ruimte. Ik zou het eigenlijk willen omdraaien, er zijn volgens mij geen voordelen

whoami schreef op donderdag 09 december 2004 @ 09:32:

Idd, maar daarom zou ik die history tables dus ook op een aparte filegroup en op een aparte schijf zetten, maar dan wel in dezelfde DB.

Precies.

[Verwijderd]

whoami schreef op donderdag 09 december 2004 @ 08:50:
[...]

In een ideale situatie zouden de history tabellen idd verplicht moeten zijn.
Wat de ontwikkelaar van de client betreft: die gebruikt gewoon de core components, en zou zich dus eigenlijk ook niets moeten aantrekken van het hele history gebeuren.

Vanuit het perspectief van de database zijn de core componenten (in de applicatieserver) deel van de client. Ook de applicatieserver zou geen weet hoeven te hebben van de gebruikte methodes om de history in de database bij te houden. Enkel dat de history bestaat en hoe die opvraagbaar is.

[whoami]

De core components zijn eigenlijk geen deel van de client. De core components staan op de server en worden dmv remoting aangesproken.
Het enige wat de client doet, is een method van die core components aanroepen, die ervoor zorgt dat alles gesaved wordt. Hoe en naar waar dit alles gesaved wordt, daar heeft de client niets mee te maken.
Die core components gaan dan componenten/methods van de DAL gaan oproepen die ervoor zorgen dat alles daadwerkelijk gesaved wordt, maar ik denk toch dat ik het mbhv triggers zal doen.

Ik heb een 'redelijk ruwe' schets in m'n gedachten, maar ik moet eerst nog eens overleggen met de andere mensen van het team. Ik zal die hier dan nog wel posten.

[P_de_B]

whoami schreef op donderdag 09 december 2004 @ 10:25:
De core components zijn eigenlijk geen deel van de client. De core components staan op de server en worden dmv remoting aangesproken.
Het enige wat de client doet, is een method van die core components aanroepen, die ervoor zorgt dat alles gesaved wordt. Hoe en naar waar dit alles gesaved wordt, daar heeft de client niets mee te maken.
Die core components gaan dan componenten/methods van de DAL gaan oproepen die ervoor zorgen dat alles daadwerkelijk gesaved wordt, maar ik denk toch dat ik het mbhv triggers zal doen.

Ik heb een 'redelijk ruwe' schets in m'n gedachten, maar ik moet eerst nog eens overleggen met de andere mensen van het team. Ik zal die hier dan nog wel posten.

Ik blijf bezig

Wat is jullie overweging geweest remoting te gebruiken, en geen webservices?
Ik ben wel benieuwd naar de argumenten, het is geen aanval op jullie keuze.

[whoami]

Performance.
Met remoting kunnen we de binary formatter gebruiken, waardoor de gegevens compacter kunnen getransmit worden dan met soap.
De remoted objects staan wel in IIS gehost.

[P_de_B]

whoami schreef op donderdag 09 december 2004 @ 10:31:
Performance.
Met remoting kunnen we de binary formatter gebruiken, waardoor de gegevens compacter kunnen getransmit worden dan met soap.
De remoted objects staan wel in IIS gehost.

Ah, ok. Dat kan inderdaad een overweging zijn.

Al moet ik zeggen dat de snelheid van webservices mij niet tegenvalt, we hebben net een projectje gehad met de Mappoint.Net webservice en de performance van het ophalen van de kaarten was best aardig.

[Verwijderd]

whoami schreef op donderdag 09 december 2004 @ 10:25:
De core components zijn eigenlijk geen deel van de client. De core components staan op de server en worden dmv remoting aangesproken.
Het enige wat de client doet, is een method van die core components aanroepen, die ervoor zorgt dat alles gesaved wordt. Hoe en naar waar dit alles gesaved wordt, daar heeft de client niets mee te maken.
Die core components gaan dan componenten/methods van de DAL gaan oproepen die ervoor zorgen dat alles daadwerkelijk gesaved wordt, maar ik denk toch dat ik het mbhv triggers zal doen.

Ik moet echt leren om duidelijker te formuleren

Je hebt 2 of 3 lagen
Client
|
Middleware (applicatieserver, EJB container, ....) Niet noodzakelijk in een client/server app
|
Database

De database ziet alles boven hem als een database client. Wat de functie in het process is, is voor de database niet relevant. De database zelf moet, hoe die ook gebruikt wordt, er altijd voor zorgen dat de data consistent blijft. Processen die altijd moeten gebeuren bij het updaten van data kun je dan ook het beste in de database uitvoeren. Zaken die samen kunnen gaan (bv master/detail updates) kun je bijeenrapen in transacties.

Dus de structuur van een Multitier applicatie is ongeveer als volgt
- Presentatie van gegevens in Client
- Business Logic in Applicatie Server
- Data Logic in Database.

Waar de lagen staan is ook niet relevant, noch hoe ze aangesproken worden.
Maar aangezien het up to date houden van je historie tabellen verplicht is, valt het wat mij betreft dus onder de data logic en hoort het in de database.