Database binnen een database

[Verwijderd]

Op het moment ben ik een architectuur aan het bedenken voor een nieuw project, waar ik een nieuwe zelfverzonnen databasetechniek aan het implementeren ben. Ik wil namelijk een systeem maken waarmee klanten zelf eigen MDF databases kunnen aanmaken, die ik opsla in een database. Wat ik dus wil doen is een .MDF database opslaan in een MSSQL database. De masterdatabase zou dan een tabel hebben met de volgende kolommen:
- ID (PK, int)
- Name (NVARCHAR(150) NOT NULL)
- MDFDatabase (VARBINARY(MAX))

Het opslaan van nieuwe MDF databases in de masterdatabase heb ik nu werkend. Dit ziet er ongeveer zo uit (waarbij ChildDatabaseTemplate.mdf een lege database is):

C#:

private void AddNewChildDatabase(string name) {
    // Load the child database template into a bytearray
    string pathToChildDatabaseTemplate = HttpContext.Current.Server.MapPath("~/App_Data/ChildDatabaseTemplate.mdf");
    byte[] bytearray = System.IO.File.ReadAllBytes(pathToChildDatabaseTemplate);

    // Insert it into the childdatabase table
    SqlCommand insertChildDatabaseCommand = new SqlCommand("INSERT INTO ChildDatabase (Name, ChildDatabaseData) VALUES (@name, @db)");
    insertChildDatabaseCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
    insertChildDatabaseCommand.Parameters.Add("@db", SqlDbType.VarBinary).Value = bytearray;
    this.ExecuteSqlCommand(insertChildDatabaseCommand);
}

Voor het ophalen heb ik nu de volgende code:

C#:

private DataTable GetChildDatabase(int id) {
    SqlCommand insertChildDatabaseCommand = new SqlCommand("SELECT * FROM ChildDatabase WHERE ID = @id");
    insertChildDatabaseCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;
    return this.ExecuteSqlCommand(insertChildDatabaseCommand);
}

Mijn vraag is nu als volgt: Hoe kan deze .MDF databases weer ophalen uit de masterdatabase en hier queries op uitvoeren? Eerlijk gezegd heb ik zelf nog geen idee hoe ik dit moet aanpakken. Ik sta open voor jullie ideeën en suggesties.

[Amras]

Voordat je een query kunt uitvoeren, zul je sowieso de gehele subdatabase uit die MDFDatabase kolom moeten gaan lezen; iets wat je volgens mij niet moet willen. Bedenk bijvoorbeeld eens wat er gebeurt als die subdatabases groot gaan worden, dan moet je om één record te lezen eerst de hele subdatabase in geheugen lezen. Vervolgens moet je die set aan bytes uit die MDFDatabase kolom nog naar iets gaan converteren waar je daadwerkelijk een query op kunt doen, lijkt mij ook nog niet erg eenvoudig.

Ik zou je architectuur eens gaan herzien, als ik jou was.

[Verwijderd]

Mag ik vragen waarom juist? Het lijkt me een gruwelijk slecht idee als ik eerlijk mag zijn.

Als klanten in hun DB willen werken gaan ze sowieso eerst een lokale kopie moeten maken aangezien je mdf database niet rechtsreeks aanspreekbaar is vanuit mssql (lees, de hele blob moet volledig uit mssql overgepompt worden). Als ze klaar zijn mag je vervolgens niet vergeten om die DB terug te committen naar mssql (wat niet instant gaat zijn gezien de grootte). Daarmee gaat het hele punt van databases gebruiken (concurrency/reliability) verloren.
Los daarvan gaat dit -enorm- traag zijn zodra je wat gaat schalen.

*edit* en Gomez haalt een goed punt aan. Jij wil geen sql server maar een fileserver.

[ Voor 16% gewijzigd door Verwijderd op 28-06-2013 14:05 ]

[Gomez12]

1 tip als je dit daadwerkelijk wilt gaan doen, vervang je mssqlserver door een fileserver.

Dan kan je er in ieder geval direct bij.

[M-Opensource]

DBception...? Ik vind het in ieder geval een eng idee, dit gaat nooit goed performen als de MDF bestanden een beetje gaan groeien.

[Amras]

Waarom maak je niet voor iedere klant een database aan in SQL Server? SQL Server heeft zelf een master database, dus dan hoef je dat concept niet opnieuw te verzinnen. Verder kun je dan ook gewoon queryen op de databases en verlies je niet direct alle voordelen die een DB(MS) je biedt.

[alwinuzz]

[ Voor 15% gewijzigd door alwinuzz op 28-06-2013 15:03 ]

[Verwijderd]

Bedankt voor jullie adviezen en tips!

Over de performance maak ik mij op dit moment nog geen zorgen. Ik probeer altijd eerst een werkende implementatie te maken en als ik er dan achter kom dat de performance slecht is, kan ik het altijd nog wat tweaken. Bovendien was ik van plan om de masterdatabase op SSD's in RAID0 te draaien.

Ik heb nu een methode gevonden om de MDF's weer weg te schrijven naar een tijdelijke directory en dynamisch een connectionstring te genereren. Dit ziet er zo uit in code:

C#:

private string WriteChildDatabaseToDisk(DataTable childDatabase) {
    int childDatabaseId = (int)childDatabase.Rows[0]["Id"];
    byte[] childDatabaseData = (byte[])childDatabase.Rows[0]["ChildDatabaseData"];
    
    // Get a path to the database file and write it to a temp directory
    string dbFilePath = String.Format(@"\\epd-testomgeving\databases\childdb{0}.mdf", childDatabaseId);
    System.IO.File.WriteAllBytes(dbFilePath, childDatabaseData);
    
    // Return the connectionstring
    return String.Format(@"Data Source=.\SQLEXPRESS;AttachDbFilename={0};Integrated Security=True;User Instance=True", dbFilePath);
}

[ Voor 7% gewijzigd door Verwijderd op 28-06-2013 15:25 ]

[P_de_B]

Met alle respect, maar dit gaan echt niet werken zoals ik het nu zie.
Disk I/0 is uiteraard wel een beperkende factor, maar de problemen waar je tegenaan gaat lopen zijn veel meer dan alleen I/O. En los daarvan, RAID0 lijkt me nu niet een goede oplossing voor productieomgevingen.

Kun je uitleggen waarom je dit wilt? Misschien is er wel een betere oplossing voor.

[Verwijderd]

Neem aub het advies serieus, want dit is absoluut geen acceptabele manier.

1) Waarom bewaar je files in een database ipv een fileserver? Binary blobs in een DB moet je sowieso vermijden en jij gebruikt het exclusief voor deze reden.
2) Waarom heeft iedere klant een individuele file nodig? Je kan ofwel voor iedere klant een DB maken in mssql, oftewel 1 grote DB maken waarin je met customerID's werkt.

[Amras]

Verwijderd schreef op vrijdag 28 juni 2013 @ 15:20:
Over de performance maak ik mij op dit moment nog geen zorgen. Ik probeer altijd eerst een werkende implementatie te maken en als ik er dan achter kom dat de performance slecht is, kan ik het altijd nog wat tweaken. Bovendien was ik van plan om de masterdatabase op SSD's in RAID0 te draaien.

Is het niet handiger om eerst te bedenken of het een goed idee is, voordat je flink wat uren hebt verbrand en het blijkt niet te gaan werken? Prima voor hobbyprojecten natuurlijk, want dan leer je er natuurlijk van, maar voor 'echte' projecten lijkt het mij niet echt een geschikte benadering. Performance is ook typisch iets waar je bij het opstellen van je architectuur al over na moet denken, want dat valt niet altijd 'even te tweaken' achteraf.

[Paul]

Dat EPD in de code, dat staat toch niet voor Elektronisch Patiënten Dossier hoop ik? Doe jezelf, de patiënten, het verplegend persooneel, de artsen en niet in de laatste plaats de ICT-afdeling van het ziekenhuis een groot plezier en laat deze aanpak varen...

Wat probeer je te bereiken wat je op een andere manier (eentje die wel performt, te onderhouden valt, waarschijnlijk veiliger is en niet constant corrupt raakt) niet voor elkaar zou krijgen?

[P_de_B]

Verwijderd schreef op vrijdag 28 juni 2013 @ 15:26:
Neem aub het advies serieus, want dit is absoluut geen acceptabele manier.

1) Waarom bewaar je files in een database ipv een fileserver? Binary blobs in een DB moet je sowieso vermijden en jij gebruikt het exclusief voor deze reden.

offtopic:
nou, dat is wel heel kort door de bocht. Er zijn veel goede overwegingen om een blob te gebruiken, zoals consistente transactieafhandeling, security en backups. De tijd dat je geen plaatje in een fatsoenlijke db mocht stoppen is echt wel voorbij

[Voyage]

Wat ik me afvraag is waarom je een database in een database wilt? Welke voordeel hoop je daarmee te behalen?

[storeman]

We weten nog steeds niet precies waarom je dit wil doen. Custom Data? Dan zou je kunnen overwegen om XML/JSON/HSTORE te gebruiken of een echt document based database systeem, bijvoorbeeld MongoDB, MariaDB of één van de vele andere mogelijkheden.

Al lijkt je voorbeeld inderdaad te suggereren dat je echt een DB in een DB wilt doen. Normaal gesproken is de naam van de database het "database id", dus gewoon met meerdere databases werken binnen de server, of zelfs met prefixed table names.

Wat je nu aan het doen bent, is een heel slecht idee.

[ Voor 38% gewijzigd door storeman op 28-06-2013 15:34 ]

[sig69]

Verwijderd schreef op vrijdag 28 juni 2013 @ 15:20:
als ik er dan achter kom dat de performance slecht is, kan ik het altijd nog wat tweaken.

Hier valt niets aan te tweaken. Het zal vast wel kunnen werken (alsin: technisch werkt het, wil niet zeggen dat het ook werkbaar is), maar het enige wat je ermee bereikt is voornamelijk heel veel I/O verbranden voor niks.

[leuk_he]

En nou komt de schok... oracle heeft zoiets bijna geïmplementeerd:

http://www.oracle-base.com/articles/11g/dbfs-11gr2.php

Alleen wordt hier niet een database in een database gedaan, maar een file systeem in een database. En in dat file systeem kun je uiteraard een mdf file neerzetten.

Echter je wil die Database objecten dan wel op een efficiënte manier kunnen benaderen. niet vooraf de hele blob wegschrijven naar een file , benaderen via mdf connect string , en dan weer terugschrijven.

Dan kun je beter een database maken die beschrijft waar bepaalde .mdf files staan.

Maar bovenal: als je de beschikking hebt tot een echt MSSQL database, gebruik die dan gewoon, ook daarin kun je regelen wat bepaalde gebruikers mogen of niet, en ik denk dat de tools die mdf files kunnen benaderen ook wel een echte SQL database kunnen benaderen. Je idee is leuk om een keer op een zolderkamertje te bedenken, maar je lost er geen echte problemen mee op.

Als je (mdf) files files wilt beheren, laat het lekker files. door ze in een database te laden los je niks op.

[Xiphalon]

En hoe wilde je bijvoorbeeld concurrency afvangen, als 2 gebruikers de mdf uit de masterdatabase hebben gehaald hoe stop je beide wijzigingen terug...

[NMe]

darkmage schreef op vrijdag 28 juni 2013 @ 16:57:
En hoe wilde je bijvoorbeeld concurrency afvangen, als 2 gebruikers de mdf uit de masterdatabase hebben gehaald hoe stop je beide wijzigingen terug...

Alleen al daarom moet je dit absoluut niet willen. Doet me denken aan die jongen die een filesystem in zijn filesystem wilde stoppen en dacht daar een sneller systeem mee te bakken.

Nogmaals: wat is het doel hiervan? Ik kan niet anders dan me aansluiten bij de rest: dit is by far het slechtste idee dat ik in maanden gehoord heb...

[Viper®]

leuk_he schreef op vrijdag 28 juni 2013 @ 16:51:
En nou komt de schok... oracle heeft zoiets bijna geïmplementeerd:

http://www.oracle-base.com/articles/11g/dbfs-11gr2.php

Alleen wordt hier niet een database in een database gedaan, maar een file systeem in een database. En in dat file systeem kun je uiteraard een mdf file neerzetten.

Echter je wil die Database objecten dan wel op een efficiënte manier kunnen benaderen. niet vooraf de hele blob wegschrijven naar een file , benaderen via mdf connect string , en dan weer terugschrijven.

Dan kun je beter een database maken die beschrijft waar bepaalde .mdf files staan.

Maar bovenal: als je de beschikking hebt tot een echt MSSQL database, gebruik die dan gewoon, ook daarin kun je regelen wat bepaalde gebruikers mogen of niet, en ik denk dat de tools die mdf files kunnen benaderen ook wel een echte SQL database kunnen benaderen. Je idee is leuk om een keer op een zolderkamertje te bedenken, maar je lost er geen echte problemen mee op.

Als je (mdf) files files wilt beheren, laat het lekker files. door ze in een database te laden los je niks op.

Interresant. MS SQL 2008 R2 ondersteunt een soortgelijk iets, namelijk remote BLOB storage, gebaseerd op filestreams: MSDN: Remote BLOB Store

Hierbij wordt het bestand zelf op een filestore gezet en zit de referentie data in SQL.

Al met al nog steeds geen goed idee dunkt mij

[Hydra]

Dat databasesysteem van je bestaat al een tijdje, google maar eens op "FS".

[Zoijar]

Dus dit is hoe het EPD globaal geimplementeerd gaat worden: ik heb er nu al vertrouwen in!

Let go... use MSSQL.

[Hydra]

Zoijar schreef op vrijdag 28 juni 2013 @ 18:27:
Dus dit is hoe het EPD globaal geimplementeerd gaat worden: ik heb er nu al vertrouwen in!

Nee, dit soort geneuzel ontstaat als er behoefte is aan centrale systemen, vanuit de overheid 'niks' gedaan wordt, en suffe amateurs spullen in mekaar gaan prutsen.

Er wordt wel enorm allergisch gereageerd op de EPD plannen maar denkt men dat artsen op dit moment nog met kaartenbakken werken ofzo? Nee hoor, die hebben ook IT systemen, alleen zijn deze vaak in elkaar geklust door 'het neefje dat wel handig is met websites enzo'.

[NMe]

Ik weet niet wat het EPD hier ineens mee te maken heeft maar kunnen we die discussie bewaren voor een topic waarin 'ie thuishoort?

[CaVeFiSh]

Technisch gezien kun je een mdf file wel in een blob type zetten, maar je gaat tegen problemen aan lopen:
- Gezien alle databases dan voor de Agent in 1 database staan, betekend dit dat je dus een gigantische backup file van je main database krijgt. Je heb dus geen mogelijkheden meer tot scheduling.
- Om op deze databases te kunnen querien zullen de databases moeten voorkomen in de sys.databases tabel. Op het moment dat je een database toevoegt op de normale manier dan wordt deze automatisch geladen in deze tabel. Volgens mij is het niet mogelijk om in deze tabel te refereren naar een blob bestand dat weer is opgeslagen in een andere database.
- Je kunt geen resources toedelen vanuit de MSSQL tooling voor de "subdatabases". Hierbij denkend aan CPU,memory,etc..
- Verder zul je nog tegen veel meer problemen gaan aanlopen.

Maar buiten dit alles is het gewoon een slecht idee. Het heeft technisch gezien geen voordelen om het op die manier op te lossen. Kun jij mij uitleggen waarom 100 databases opgeslagen in 1 database beter zou zijn als 100 databases in 1 instantie? Alleen vanuit beheerstechnisch oogpunt zou ik het al niet doen.

[Hydra]

NMe schreef op vrijdag 28 juni 2013 @ 19:40:
Ik weet niet wat het EPD hier ineens mee te maken heeft maar kunnen we die discussie bewaren voor een topic waarin 'ie thuishoort?

Vanwege 't "\\epd-testomgeving\databases\childdb{0}.mdf" in z'n 2e post.

[Merethil]

Hydra schreef op vrijdag 28 juni 2013 @ 19:58:
[...]


Vanwege 't "\\epd-testomgeving\databases\childdb{0}.mdf" in z'n 2e post.

't kan natuurlijk gewoon simpele zelfbedachte testdata zijn he!? Ik pleur ook altijd random namen in testdata, maar wel zo dat de data in een format is dat ik ga verwerken.
Een beetje fantasie hoort erbij toch?

[Hydra]

Merethil schreef op vrijdag 28 juni 2013 @ 20:16:
't kan natuurlijk gewoon simpele zelfbedachte testdata zijn he!? Ik pleur ook altijd random namen in testdata, maar wel zo dat de data in een format is dat ik ga verwerken.
Een beetje fantasie hoort erbij toch?

Ik geloof ook echt niet dat die knakker aan een serieus EPD project werkt hoor Z'n eerste 2 posts zijn in dit forum, volgens mij is 't gewoon een troll

[ Voor 8% gewijzigd door Hydra op 28-06-2013 20:33 ]

[sig69]

Laten we het hopen..

[Verwijderd]

Ik zag juist deze post,

Ik kon uit de mededelingen niet opmaken of de gebruikers toegang bleven nodig hebben tot de hoofd db.
Maar anders kan je de rootdb gebruiken voor de connection info en dan in je c# reconnecten naar de eigen db.
Eventueel een apparte connectie naar de hoofdDB in een entity opslaan (al naargelang de grootte) en afhandelen bij het afsluiten.

Naar mijn ervaring is het altijd een slecht idee om meer in het geheugen te proppen dan nodig.
dus kijk ook eens naar layzy loading voor de entities.

Als het om versleutelde data gaat ( zelfs admin mag de klant db's niet in kunnen), kan je misschien beter met encrypted folders werken en een fs zoals eerder aanbevolen.

workflow: klant logged in -> mdf wordt ontsleuteld -> connectie naar klant mdf wordt geïnitieerd.

[leuk_he]

Verwijderd schreef op zondag 30 juni 2013 @ 12:01:
Als het om versleutelde data gaat ( zelfs admin mag de klant db's niet in kunnen), kan je misschien beter met encrypted folders werken en een fs zoals eerder aanbevolen.

En ook daar hebben de grote databases als oracle en MS een oplossing voor. Zelf ben ik wel eens in de oracle kant erin gedoken:

http://www.oracle-base.co...0gr2.php#encrypted_column

Een kleine zoektocht geeft aan dat MSSQL een soortgelijk mechanisme heeft, met iets meer applicatie aanpassingen, en een soortgelijke overhead

Het probleem is echter dan toch vooral het key-management. Hoe sla je de key op dat alleen de gebruiker erbij kan? De oplossing is meestal dat de en/decryption key een soort van shared secret is in de applicatie. Het helpt alleen tegen database manipulatie door Database administrators die weinig applicatie kennis hebben. En er zit dan ook nog eens een aardige overhead op.

[Verwijderd]

Als je het mij vraagt mis je ook het hele idee van een admin als die juist niet de data kan beheren, en zoals hierboven gezegd wordt zul je toch ergens een sleutel op moeten slaan ... op een plek waar een admin bij kan.

helaas geeft de TS geen echte reden waarom hij dit zou willen doen en zijn er al genoeg argumenten waarom hij het NIET zou moeten doen, naast common sense natuurlijk

Dit lijkt me meer op een 'omdat het mogelijk' is thingy.