July 26, 2012 at 10:00 am
Hi,
I believe I should use Replication in this scenario, but wanted to make sure. We have 4 db servers (total of 8 databases) setup at different locations at this time. And do to some business requirements, I can't combine all the data into one database at this time.
That being said, I'm preparing to setup 2 new features in the system:
* internet access to the data across all databases (But not all the tables).
This data will need to be filtered based on a flag set in our clients tables. At this time this data will be read only.
* Our intake department will also require access to the data as above.
The intake dept will be entering in client assessments for our clients and will need access to all tables in the system.
What I was thinking of doing was creating a Intake database and a Internet portal database. then setting up Replication between these db's and the main data sources.
Questions:
1. Can the data being replicated be filtered before it gets to, say the internet portal db? Not a problem if it can't because the procs requesting the data for the internet user can do the filtering. But if it can, it will at least reduce the amount of data that needs to be sent over the wire and also reduce the storage requirements.
2. At this time each database does not contain a ProgramId as part of the primary key of the primary tables used in the system. But the IntakeDb and InternetDB will require the addition of the programID to now where the data came from. Can this ProgramID be added to the stream during the replication, or do I need to update the table design to include this ID as part of the primary key? I was hoping to do it in the stream as changing the design of a table that contains data, sometimes requires the removal/creation of the table.
3. Is there a better solution than using replication to do what I need to do? I know combining all the data into one database would probably be the easiest, but do to possible down times do to loss of internet access between sites, we wanted to keep the databases separate.
Thanks for any light you can shed of the subjects above.
Michael
July 26, 2012 at 10:16 am
Couple of questions to better understand the scenario...
1- Why two databases e.g. intake and portal? when it appears like one would do the trick?
2- Intake will act as a source for reporting, is that correct?
3- Does Intake database data has to be near-real-time updated or can it be updated as it was - let's say at 5AM?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 26, 2012 at 11:26 am
Hi Paul,
Thanks for the reply.
1. I was thinking that since the portal was read only, and intake would need the ability to add/update data, that a separate db's might be best. But if this is not the case, then I guess I could go with a single database. Actually it might make it simpler if I only use one database.
2. Intake dept will be entering in assessments on clients to be admitted to one of the programs (we are drug treatment clinic). Once the assessment is saved, data from this info will be used to create a new client record (if brand new client) and also creating a new admission record.
At first I was just going to have all the assessments saved in the Intakedb only, but after thinking a little more about this, maybe its better to have Assessment table in each program database, and when intake saves the assessment, just have the new data replicated back to the appropriate program database. You think that is a better design?
3. The intake database need to be near real time, where as the portal I might be able to get away with updates every couple of hours, and maybe even once a day. The user of the portal will not be employees of the company, but will be state workers that will need limited access to the data.
Hope this help clarify what I'm trying to.
Michael
July 26, 2012 at 11:48 am
Hi Paul,
I thought it also may help a little bit to give a little more detail as to how we are setup now:
Note, database structure is the same for all Programs. Each program has there own database.
Site A (Queens)
Program 1
Program 2
Intake dept for Program 1 & 2
Site B (Brooklyn)
Program 3
Program 4
Intake dept for Program 3 & 4
Site C (Bronx)
Program 5
Program 6
Program 7
Program 8
Intake dept for Programs 5,6,7 and 8
Hope this helps.
Michael
July 26, 2012 at 12:36 pm
Let me test my understanding of this...
a) Portal is a read-only subset of tables from each database
b) Intake will support an OLTP system
c) Phisical locations appear to be pretty close to each other e.g. Bronx, Queens and Brooklyn.
IF Intake is expected to work by "program" meaning that every single Intake transaction targets a specific "program" - which equates to a particular database in a particular location - I may consider:
1) Use the current "program" databases to support the Intake application and let the Intake application point to the right one.
2) Build a Portal database to support read-only reporting. This database could source data replicated from the "program" databases.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply