Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2009
    Posts
    6

    Unanswered: system / database architecture

    Dear All,

    Sorry if this is in the wrong section, seemed fitting as the DBs that i am using are MS SQL.

    I have been informed of a project that will be on my desk soon and i wanted to get some advice from some professionals.

    There is a need to build some bespoke web applications onto an already exisiting system, which has been in place for years. This system takes of 100,000 per year and is the backbone for the company. These bespoke websites will be used to help sell / attract business....by using the data that is already in the current architect. My project will be to use the current data and use that to power these websites.

    Now it goes withought saying that I would like my own DB for each bespoke site as there will be specific requirements etc.

    What would you suggest be the best method of populating each site being? I have thought about replication between the existing and new - cherry picking specific data. Or maybe an API/web service which i could use to connect to the old architect directly. MY question is, what about connection times / speed / bandwidth?

    I will be creating all bespoke websites in c# .net and will be implementing them in the MVC architect. These website will be on a standalone server, away from the curreny system. Just to make it clear, these new web applicaions will not be replacing the current system/s, they will be an add-ons using the data already present.

    Hope this all makes sense and that i have explained this well enough.

    Any advice / help would be great.

    Thanks in advance,

    A

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm changing the title of this to "... architecture" - I thought it was a job ad before I read the content. Lemme know if you want it setting back.
    Last edited by pootle flump; 07-27-09 at 05:52.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    6
    no no thats fine. Thanks very much.

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Are your sites Remote or on the Local Lan Bob - I'm afraid it's you who have to tell US what "connection times / speed / bandwidth?" you have available, then we can recommend Suitable Topologys.

    We can discuss how to shunt your data about to where you want, the front end to the data is largely irrelevant here.

    Do your existing SQL Servers already use Replication?

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Jul 2009
    Posts
    6
    Hi,

    Thanks very much for taking your time to query this.

    The Server that host the current data is a remove server. I have been informed that we could request a direct connection between servers, but they will try and push for us to use a web service / api to gather the data I need.

    Once i have the data, i would ideally like to produce my own database (which will be on a local LAN) and manipulate / call that as much as i feel fit.

    There is no current form of replication. I get the feeling they want to keep the data at in their domain (which i can understand) as they will think "once they have what they need - they will no longer need us". Thats not the case....I only want part of the data they have. There is still a need for them.

    The speed / bandwidth im not 100% sure on as I wont have access to network admins till later this month. This is why i would tend to opt for keeping the data on my LAN....but is my thinking right?

    Cheers,

    A

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    MMmmm - So your getting Data from another company over the Internet ? or via a secure WAN ?

    Will the source data be subject to edits or is it a sequential stream of data that does not change ?

    Politics is also largely irrelevant here
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Jul 2009
    Posts
    6
    Im not fully sure what is currently available, but yeah, thats the idea they have.

    They have a large company providing an online system which it to be kept. They want a "side project" to "feed" off this database. I will assume they will want it to be over the internet but i will be pushing for a VPN.

    The source data will be subject to change, how much of the data i want to cheery pick out of their system (which could change) i dont know. There will be very little changing from the small project to the larger system. It is to display, workout and then submit certain business data back into the loop.

    This is why i was thinking about replication - to keep the data consistent.

    I will draw a diagram an post it up. Sorry for not being able to explain this clearly.

    Thanks very much for your help.

    A

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    No Problem Bob - Glad to help.
    I'm off home now so will take another look tommorrow unless the Guru's sort you first.

    My understanding is

    a) Source Data Changes
    b) Will likely connect over open internet

    mmmmmm
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  9. #9
    Join Date
    Jul 2009
    Posts
    6
    Reet-o

    I have attached an image file of how i think the layout is going to be. A remote server with a customer facing and admin facing websites, ran by a central server. Its the data on this server i want.

    The data will be stored there and updated there. My question is, is it worth me trying to create my own little system (local) and update it via replication (from the remote to the local) or is it going to be much better for the app to make an API call for data everytime it wants something from the remote DB? Now i know it depends on connection speeds and connection methods, but i guess thats where the politics comes in and we will have to meet in the middle.

    I would prefer to cherry pick the data that i want from the remote server, replicate / copy it on my local server (in my own database schema - different to the remote) and add any unique data to it. Thus leaving full control to unique data to me while still accessing the remote data.

    Would this be too traffic heavy? Updating the local database every hour or so....or even every 15 mins? I dont want the data to cut them out, i just dont want my web app to grind to a halt when 5000 users are on it and it struggles to make n number of API calls. Likewise with the replication, i dont want the system to slow down due to data transfer.

    Im not sure which route is best to go down. I know its all a little vague and once we know the specific I can pick a best route.....but i guess i am trying to figure out what would be best for us and then make changes to that plan if specific criteria changes (eg. no VPN etc)

    Once again, thanks very much for your time.

    Kindest regards,

    A
    Attached Thumbnails Attached Thumbnails setup.jpg  

  10. #10
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    This is a tricky one Bob & I'm olny commenting after getting a brief glimpse of your challenge.

    I can see know why you were focussing on GUI Front Ends & Politics.

    This is an Overall System Architecture Design challenge, complicated by the fact that your current system is Hosted by an external Company.

    I could easily say setup Merge Replication and start hosting your own website, but at the end of the day the Host DBA's have the final say in what you can & cannot do with THEIR Servers, you may own the Data but they own the Server.

    Forcing them down the route of setting up replication (even if they are willing) will inevitably cost a fair bit of someones money, time and ongoing maintanence.

    Now it goes withought saying that I would like my own DB for each bespoke site as there will be specific requirements etc.
    What you would like & what is allowed/most efficient/most cost effective may not be the same in this instance.

    To Mirror Data (and possibly applications) at two seperate locations you need a basic understanding of Distributed Computing Architecture. The implementation of which is both costly and full of potential Failure points.

    Technically many people on here could setup your SQL replication Publisher/Subscriber/Distributor in less than a day, but that's the easy bit.

    If you don't own the Source SQL Server or they are not 100% enthusiastic about implementing replication for you then this is where 95% of your problems will come from.

    In any Design I would always recommend KEEP IT SIMPLE, as per my footer.

    With 5000 Concurent Users you may have reached a point where you have outgrown the Host.
    OR
    You may have to modify your Design so that it can Run on the Hosts Servers & restrict your Code/Development to Pre-Agreed Formal Release Procedures to the Host.
    OR
    Depending on the (external to your control) Internet Connection Speed & Downtime effect on your Business a WEB API may indeed be your Best Solution.

    Hosting companies are usually protective of keeping all their systems within SLA's and they know to embark on new Topologys can be quite disruptive.

    They are your friends because you give them money.

    I would suggest that you Clarify what you are ALLOWED to do, then come back and ask about Best Practices once you have firm agreements.

    Good Luck Bob

    Sorry I could'nt help more
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  11. #11
    Join Date
    Jul 2009
    Posts
    6
    GWilly,

    thanks very much for your take on this.

    I fully agree that i am going to find out what i am allowed to do and what they are willing to accept. I know that my company is "THE" largest client that they have, and i have been assured that they will "play ball"....thus starts the politics :-D

    in an ideal world - if they say "sure - we will do what you want"...which options would you tend to go for? I am tempted for duplicating specific data of theirs into mine and updating that every 15mins etc. My second option would be to use an api to call set stored procs and retrieve such data. My last option was would be to connect to their data directly. I would like to stay away from this one as i would like to have a copy of the data on my own server.

    hhhuumm....i dunno. i fully agree with the KISS. for now....i might suggest a very small project to test out the API / webservice and get that of the ground. This will allow us to analyse timing and bandwidth etc for larger projects.

    Once again, thank you very much for your time.

    A

  12. #12
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Your Welcome

    Given these two following points carefull consideration

    a) Forcing them down the route of setting up replication (even if they are willing) will inevitably cost......

    b) if they say "sure - we will do what you want".......

    I would opt for Merge Replication with a well designed (include lookup tables) Publication ;-)

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •