June 1, 2009 at 2:57 am
Hi All,
My client has got two databases. On the first one (the Master BD)it is where they make changes to the data manually. They want to have all the changes made in particular day be imported into the secondary DB. they will actually give a file to be imported everyday. This is how they want it to work, say the Master db has Member Records with these fields; Fullname, Id_no, ref_no,Date_Of_Birth, Marital Status.
Now when a a member gets married, on the Master BD they will update fullname and marital status fields. Now when doing the import it should update this member's fullname and marital status on the secondary BD. I would like to know if this possible and how it can be done.
Many Thanks.
June 1, 2009 at 3:18 am
You can use high availability options.
I think mirroring will do.
Tanx 😀
June 1, 2009 at 3:49 am
What is purpose of the secondary database?
If it is for High-Availabilty then you can use database mirroring,
but if it is for reporting then you will not be able to use mirroring as the mirror will not be accesible.
A better option for reporting would be to use a form of replication, either transactional or snap-shot depending on your needs.
Otherwise you could write a SSIS packages or write some custom t-sql.
If you can post some more details about what you want to acheive then more specifc advice can be given.
June 1, 2009 at 3:59 am
Hi All,
Thanks for all the responses so far. The secondary BD is actually a CRM system. they will use info from it for all correspondence with their customers. So what they want is to update a member record on one system (or DB) and have those changes updated on the CRM system.
Thanks.
June 1, 2009 at 4:08 am
phumlo1 (6/1/2009)
Hi All,Thanks for all the responses so far. The secondary BD is actually a CRM system. they will use info from it for all correspondence with their customers. So what they want is to update a member record on one system (or DB) and have those changes updated on the CRM system.
Thanks.
Do the two systems use the same table?
June 1, 2009 at 5:39 am
Hi SteveB
No, they use seperate tables with common fields.
June 1, 2009 at 7:00 am
phumlo1 (6/1/2009)
Hi SteveBNo, they use seperate tables with common fields.
With different tables then it will be difficult to setup replication (as far as i am aware) You may want to look at using SSIS to transfer the data between tables, depending on how up-to-date you require the destination table to be, you could schedule the package to run hourly or nightly.
June 1, 2009 at 7:17 am
The way they want it to happen is, they will forward me a File with changes from the Master DB and. I would then be expected to import that file into the CRM system weekly or biweekly.
June 1, 2009 at 7:22 am
phumlo1 (6/1/2009)
The way they want it to happen is, they will forward me a File with changes from the Master DB and. I would then be expected to import that file into the CRM system weekly or biweekly.
Okay, I was unfer the impression that you wanted to transfer from DB-to-DB.
But a flat file makes it very easy, just create a SSIS package to import the file into the your database, you can then set this to run weekly or bi-weekly as you get the flat file.
For new data it is easy to import the new rows, for updates you may need to first import to a staging table and then update form there, or you could write some update procedures, which approach you pick would depend on how many rows you are expecting to import..
June 2, 2009 at 1:58 am
Thanks I think i'm gonna go with a staging table approach.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply