Sql tabel kopieren naar andere database

[Verwijderd]

Beste forumleden,

Mijn probleem is als volgt:

Ik heb hier een database draaien waarop alle alle data staat voor een compleet administratiepakket. Daarop staat ook een tabel medewerkers. Deze tabel bevat informatie over de geboortedatum maar ook de vakantiedagen, salaris, burgelijke staat enzovoorts.

Nu zijn we hier bezig met de inrichting van een intranet waarop een smoelenboek moet komen met alle medewerkers die in dienst zijn. Die medewerkers staat in de database van het administratiepakket maar daar mag het intranet wegens de veiligheid niet in kijken.

Wat wil ik dus:
Een query uitvoeren op de administratiedatabase en vervolgens de uitkomst wegschrijven naar de database van het intranet. Hierdoor komen er alleen gegevens in de database van intranet die niet gevoelig zijn, zoals bijvoorbeeld het salaris.

Het staat allemaal wel op 1 server maar in verschillende databases.

Het pakket dat we gebruiken voor de database is SQL Server 2000 waarbij we volgende maand over gaan op SQL Server 2005. Om de copy te doen zat ik eerste te denken aan een trigger maar dan kan ik de data niet overkrijgen naar een andere server. Verder heb ik termen gelezen als Replication en Mirroring. Mirroring lijkt niet het goed omdat dat lijkt op een exacte kopie, replication weet ik niet zeker...

Kan iemand mij uit het donker helpen en zeggen welke methode ik hiervoor het beste kan gebruiken

Thanks!!

Willio

[P_de_B]

Als het gaat om specifieke gegevens kun je gewoon INSERT-queries schrijven die gegevens van de ene naar de andere database kopieert. Een andere techniek is een view op de intranet-database te maken, die de gewenste gegevens uit de administratie-database haalt.

Door objecten volledige te benoemen kun je - als de rechten dat toestaan - gegevens uit een andere database halen:

SQL:

-- query op intranet database
CREATE VIEW vwSmoelenboek

AS

SELECT Naam, bla /* niet alle velden, alleen de relevante */
FROM servernaam.administratiedatabase.eigenaar.deTabel

Welke keuze het beste is, hangt af van verschillende omstandigheden zoals gewenste actualiteit en ingestelde rechten. De mogelijkheid met de view is de meest eenvoudige, en altijd actueel.

[whoami]

Ik denk ook dat een view een goede optie is. Op die manier dupliceer je de gegevens niet.
Die view bestaat dan in je 'intranet db', en haalt de gewenste gegevens op uit je admin db.

[lier]

Die medewerkers staat in de database van het administratiepakket maar daar mag het intranet wegens de veiligheid niet in kijken.

Ben bang dat tenzij de rechten gegeven worden de database ook niet middels een view benaderd mag worden (hoe netjes deze oplossing ook is !).

Wat je je af kan vragen is hoe vaak deze gegevens gewijzigd worden en wat de noodzaak van "real-time" (ja, ik weet wat dat is !) informatie in het smoelenboek is.

Misschien is het voldoende om dagelijks/wekelijks een job te draaien waarin alle gegevens over gezet worden.

Misshcien kan je eerst meer over de eisen en wensen vertellen ?

[P.O. Box]

ik ben het met lier eens... hoe belangrijk kan het zijn dat een smoelenboek realtime mee loopt? laat gewoon iedere nacht een DTS (of binnenkort SSIS pakket) draaien die de nieuwe medewerkers voor je kopieert naar de andere database...
als het overigens alleen om medewerkergegevens gaat (er van uit gaande dat het bedrijf waar je voor werkt niet 1.000.000 werknemers heeft) kun je de DTS/SSIS ook wel iedere uur, ieder kwartier, of zelfs iedere minuut laten draaien.... lijkt me onzinnig, maar kan wel als je dat wilt...

[P_de_B]

Je kunt met een view (juist!) wel heel nauwkeurig instellen wie wat mag zien. Dus ik zie op zich een rechtenissue niet als groot probleem (of er moet een hyperzenuwachtige dba zijn).

[Niemand_Anders]

Gebruik de export mogelijkheid welke standaard in SQL 2000 zit. Daarin selecteer je de source- en destination database en kun je eventueel de mapping aanpassen. Net voordat je de export laat uitvoeren geeft je aan dat je de 'job' wilt opslaan. Er wordt dan automatisch een DTS package aangemaakt. Deze kun je vervolgens weer laten schedulen om bijvoorbeeld elk ochtend om 7 uur te draaien of gewoon elke 6 uur.

Omdat je de job onder DBO (sa) rechten kunt laten draaien kun je zeer eenvoudig de relevante velden kopieren naar de intranet database. Op die manier weten de twee databases zelf niets van elkaar.

Overigens raad ik je aan een speciale gebruiker voor de DTS job te maken welke alleen lees rechten heeft op de user tabel uit het administratie pakket en lees/schrijf rechten op de user tabel van de intranet database.

Maak in elke geval voordat je begint aan wijzigingen aan een database eerst even een volledige backup en zorg dat het recovery model van beide database op 'FULL' staat zodat je de transactie logs kunt gebruik om de wijzigen sinds de backup terug te zetten tot het moment waarop jouw query werd uitgevoerd.

[Verwijderd]

DTS klinkt als en goede optie alleen zit ik met bepaalde id's te kijken. Locatie bijvoorbeeld staat in een andere tabel dan de medewerkers dus daarom lijkt het me dat ik een query moet uitvoeren.

De view ziet er goed uit die ga ik dadelijk even testen.

Kan ik andere opties als mirroring en replication vergeten?? Volgens mij was ik iets te moeilijk aan het denken

Willio

Edit: Ik ben het met lier eens theoretisch mag de intranet database niet op de admin komen. Admin moet eigen iets naar de intranet "pushen".

[ Voor 15% gewijzigd door Verwijderd op 13-07-2007 13:33 ]

[P_de_B]

Mirror en replication moet je zeker vergeten

[Verwijderd]

P_de_B schreef op vrijdag 13 juli 2007 @ 13:33:
Mirror en replication moet je zeker vergeten

Dat scheeld een boek doornemen van 1002 pagina....

flusing mind

[ Voor 48% gewijzigd door Verwijderd op 13-07-2007 14:05 ]

[sig69]

Replication kan zeker wel, maar lijkt me zeer zware overkill in dit geval. Een DTS job lijkt mij ook the way to go

[whoami]

replication kan, maar is niet opportuun voor dit doel. Een view volstaat ruimschoots.

[P_de_B]

Verwijderd schreef op vrijdag 13 juli 2007 @ 13:31:
Edit: Ik ben het met lier eens theoretisch mag de intranet database niet op de admin komen. Admin moet eigen iets naar de intranet "pushen".

Het ligt er maar aan hoe strikt je bent. Een view is gewoon een bril om naar bepaalde gegevens te kijken, je kunt met security regelen wie er bij de gegevens mag, en je kunt in het ontwerp van de view regelen welke gegevens gezien mogen worden. Je kunt zeker dezelfde mate van beveiliging garanderen bij een view als bij het kopieeren (via DTS, of gewoon een insert query runnen vanuit de scheduler)

* P_de_B stemt view

[Niemand_Anders]

Het nadeel van de view is dat de intranet user lees rechten moet krijgen op de administratie database. Een DTS package oplossing staat tussen de administratie en intranet database in. En beide applicaties weten niet van het bestaan van de ander.

Als je toch voor de view oplossing kiest, maak dan de view in de administratie database en geef de intranet user daarop lees rechten. Het zal namelijk niet de eerste keer zijn dat een intranet database user 'db owner' rechten heeft. Een slimme gebruiker zou dan de view in de intranet database kunnen aanpassen.

De view in de administratie database kun je dan benaderen als select * from administratie..vwUsers (let op de dubbele punt).

Persoonlijk houd ik liever de database gescheiden van elkaar. Want als bijv. de administratie database verplaats wordt naar een andere server, dan moet je met linked servers gaan werken. In de DTS package hoef je alleen de connectie gegevens te veranderen.

[Verwijderd]

Niemand_Anders schreef op zaterdag 14 juli 2007 @ 10:28:
Persoonlijk houd ik liever de database gescheiden van elkaar. Want als bijv. de administratie database verplaats wordt naar een andere server, dan moet je met linked servers gaan werken. In de DTS package hoef je alleen de connectie gegevens te veranderen.

Dus jullie zeggen eigenlijk dat een DTS beter werkt en makkelijker is. Dan zou ik stom zijn om het niet te doen. Als ik DTS goed snap maak ik dus een tabel aan op de intranet die gegevens bevat die ik bepaal in het script. Dan lijkt het me dat die DTS op de administratie staat....?

Willio

[P_de_B]

Nou, ik lees dat Niemand Anders zegt dat een view goed kan werken, mits je een beetje nadenkt over de beveiliging etc. Je moet de intranet-db leesrechten geven op de admin-db, daar moet je uiteraard goed over nadenken, maar als je de rechten goed zet heb je daar weinig problemen mee hoor.

Met DTS maak je een 'script' die periodiek de gewenste gegevens van de ene naar de andere database kopieert.

[Verwijderd]

P_de_B schreef op maandag 16 juli 2007 @ 09:52:
Nou, ik lees dat Niemand Anders zegt dat een view goed kan werken, mits je een beetje nadenkt over de beveiliging etc. Je moet de intranet-db leesrechten geven op de admin-db, daar moet je uiteraard goed over nadenken, maar als je de rechten goed zet heb je daar weinig problemen mee hoor.

Met DTS maak je een 'script' die periodiek de gewenste gegevens van de ene naar de andere database kopieert.

Ja precies, en met DTS heeft de administratie geen rechten nodig op de andere database. En dat bevalt me wel. Aangezien daar echt alles instaat van het bedrijf. Dus hoe dichter hoe beter. Dan doe ik liever de deur op slot, in plaats van dat ik iemand een kleine sleutel geef.

Hoe kan ik een andere database aanspreken met DTS?

Willio

[Creepy]

Misschien handig om je eens in te lezen wat DTS nu precies is en hoe je een DTS package / script maakt en wat je daar in kan doen? Twee verschillende DB's aanspreken (zelfs op twee verschillende MS SQL servers) is geen probleem.

[LuCarD]

Misschien is het handiger om een simpel sqljob te schedulen die de tabel elke dag overkopieert?

SQL:

SELECT veld1, veld2, etc 
INTO <intranet_db>.<eigenaar_intranet_table>.<intranet_table> 
FROM <personeel_db>.<eigenaar_personeel_table>.<personeel_table> 

DTS en SSIS is een beetje overkill voor dit soort dingen.

[BtM909]

Willio, je hoeft niet onder elke post je naam te vermelden, zie ook: Het algemeen beleid #reageren

Daarnaast lijkt het me handig om eerst even in de materie te verdiepen voordat je je vraag op GoT vraagt, zie hiervoor: Development Tools & Environments Beleid

[Verwijderd]

Akkoord,

Ik heb een hoop tips gekregen en ga daarmee aan de slag. Mocht ik er daarna nog niet uitkomen kom ik hier terug

[P.O. Box]

LuCarD schreef op maandag 16 juli 2007 @ 10:40:
Misschien is het handiger om een simpel sqljob te schedulen die de tabel elke dag overkopieert?

SQL:

SELECT veld1, veld2, etc 
INTO <intranet_db>.<eigenaar_intranet_table>.<intranet_table> 
FROM <personeel_db>.<eigenaar_personeel_table>.<personeel_table> 

DTS en SSIS is een beetje overkill voor dit soort dingen.

een SQLjob is toch in feite een DTS / SSIS... misschien met maar 1 of 2 elementen... ?

[Robtimus]

Edwardvb schreef op dinsdag 17 juli 2007 @ 09:12:
een SQLjob is toch in feite een DTS / SSIS... misschien met maar 1 of 2 elementen... ?

Een SQL job kan bestaan uit het aanroepen van DTS / SSIS, maar kan nog zoveel meer. Zo kun je meerdere DTSen / SSISen* aanroepen, T-SQL uitvoeren, maintenance plans uitvoeren, ActiveX uitvoeren, OS commands uitvoeren, etc. In SQL 2005 zijn er wel 11 opties die je per stap kan uitvoeren, en je kan meerdere stappen achter elkaar plaatsen zonder problemen. SQL 2000 heeft er slechts 8, hoewel die DTSen uitvoert via T-SQL.

* SSIS zowel in SQL als in files. Werkt ideaal icm version control

[Niemand_Anders]

Nee, via een sqljob kun je een DTS / SSIS package schedulen, maar ook zoals je aangeeft gewoon een query uitvoeren. Overigens heeft de select into oplossing hetzelfde nadeel als de view oplossing mocht de database naar een andere server worden verplaatst.

Als ik zou moeten kiezen tussen de select into en een view, dan zou ik toch zeker voor de view kiezen omdat dan de gegevens 'realtime' beschikbaar zijn. De select into constrcutie heeft wel als voordeel dat beide databases, net als bij de DTS package, elkaar niet 'kennen'.

De DTS package heeft als toegevoegde waarde dat tijdens het kopieer process velden aangepast kunnen worden, dat je een workflow kunt integreren en mocht een van de database naar een andere server gaan, hoef je alleen de connectie properties aan te passen.

[curry684]

Niemand_Anders schreef op zaterdag 14 juli 2007 @ 10:28:
Het nadeel van de view is dat de intranet user lees rechten moet krijgen op de administratie database. Een DTS package oplossing staat tussen de administratie en intranet database in. En beide applicaties weten niet van het bestaan van de ander.

Als je toch voor de view oplossing kiest, maak dan de view in de administratie database en geef de intranet user daarop lees rechten. Het zal namelijk niet de eerste keer zijn dat een intranet database user 'db owner' rechten heeft. Een slimme gebruiker zou dan de view in de intranet database kunnen aanpassen.

De view in de administratie database kun je dan benaderen als select * from administratie..vwUsers (let op de dubbele punt).

Persoonlijk houd ik liever de database gescheiden van elkaar. Want als bijv. de administratie database verplaats wordt naar een andere server, dan moet je met linked servers gaan werken. In de DTS package hoef je alleen de connectie gegevens te veranderen.

Binnen een multinational zou er inderdaad een view met beperkte toegangsrechten gemaakt worden op de medewerkertabel, en vervolgens een DTS package scheduled opgezet worden die op vaste regelmaat de originele view uitleest en de tabel van de website volstort. Dit is vanuit systeemarchitect-oogpunt de zuiverste oplossing, omdat de 2 praktijkdatabases niet van elkaars bestaan weten, de web-DB gewoon dagelijks stomtoevallig ineens z'n nieuwe data klaar heeft staan, en de security nergens open hoeft te worden gezet, en zelfs de credentials van de view-accessor-user enkel bij de DBA bekend hoeven te zijn, evenals de owner van de DTS-package.

Voor een gemiddeld bedrijf van <500 werknemers is het gewoon hopeloos overkill en zijn er al genoeg simpeler en sneller op te zetten oplossingen langs gekomen.

[P.O. Box]

IceManX schreef op dinsdag 17 juli 2007 @ 12:47:
[...]

Een SQL job kan bestaan uit het aanroepen van DTS / SSIS, maar kan nog zoveel meer. Zo kun je meerdere DTSen / SSISen* aanroepen, T-SQL uitvoeren, maintenance plans uitvoeren, ActiveX uitvoeren, OS commands uitvoeren, etc. In SQL 2005 zijn er wel 11 opties die je per stap kan uitvoeren, en je kan meerdere stappen achter elkaar plaatsen zonder problemen. SQL 2000 heeft er slechts 8, hoewel die DTSen uitvoert via T-SQL.

* SSIS zowel in SQL als in files. Werkt ideaal icm version control

ach gut ja... ik ben niet scherp de laatste tijd... terwijl ik regelmatig met die dingen werk.... iemand een pilletje voor me?

[Verwijderd]

Oke,

Proud to say: De DTS werkt .

Blijf nu met 1 kleine keuzemogelijkheid zitten.

Ik kan de DTS schedulen, dat is een makkelijke en uitgebreid optie alleen niet helemaal realtime.

Ik kan de DTS laten afgaan met een: EXEC master.dbo.xp_cmdshell 'dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password' in de trigger.

Wat heeft hier voorkeur of maakt dat niks uit?

[Niemand_Anders]

Ik denk dat het verstandig is dat je nagaat of het wel nodig is dat de gegevens in de intranet database realtime worden geupdate. Stel je update de database om 07:30 en 13:30 uur, dan loopt het intranet slechts enkele uurtjes achter als er daadwerkelijk een wijziging wordt gedaan. Maar wordt het smoelenboek dan echt meerdere keren per dag geraadpleegd?

Ons intranet (bedrijf met ruim 200 medewerkers verdeeld over 3 locaties) werd elke ochtend geupdate. De administratie deed gemiddeld slechts 1 update per week.

Ik zou in elk geval geen triggers gebruiken om DTS packages te starten. Dan moet je namelijk de betreffende database user system administrator rechten geven (anders mag er geen systeem process worden gestart). Wen website database users met SA rechten is vragen om moeilijkheden.

[Verwijderd]

Hmmz ja,

Nodig niet echt maar als het mogelijk is en het is weinig belasting voor het systeem...waarom dat niet. Maar moet je wel gelijk geven dat het niet ECHT nodig is. Ik ga hem dan ook schedulen op waarschijnlijk 1 keer in het uur binnen de werktijden.

[curry684]

Synchronizaties realtime uitvoeren is eigenlijk altijd onzin en overbodige verkrachting van je netwerk en servercapaciteit.