locked
Doubts on SQL Server based Application Architecture RRS feed

  • Question

  •  

    Hi All,

     

    My Application:

    I am currently working on a SQL Server based application. Therein we receive some business data from both internal legacy applications and external vendors as well. For this we use BizTalk which receives XML from various sources and pushes data into my application which then gets processed herein. This is a huge application which contains more tthan 250 tables with around 110 views and around 325 + SPs and functions. Also the application supports its web interface and SSRS based reporting. The processing is wired around scheduled jobs and SSRS subscriptions

     

    The data from the external sources may not be upto the mark hence we use a complex model of some source tables where BizTalk dumps the data which is validated and then valid records are moved to another model of similar tables structures and then processed.

     

    I am aware that we unfortunately are under-utilizing BizTalk to only capture the XML data that comes accross and use it to dump into the source model.There are instances that the data received is not properly referenced to its parent nodes / tables and still makes its way into the respective source model tables.

     

    My DOUBTS:

    The doubt is should I continue the way itself or should I move the unreferenced data to some other table.We are currently able to identify the mapped records only hence do not use the XML Mapper. Is there a way can BizTalk identify the unreferenced nodes using the Mapper?

     

    Also Architecture-wise which is better? The current one or the one where we push the unreferenced data to some other table. REMEMBER that we would still have to undertake data validations. Consider the following areas for choosing the better architecture of the two:

    1. The complexity of logic 
    2. Ease of maintenance
    3. Code Readability
    4. Reporting
    5. Support perspective
    6. MIS Reporting
    7. Error Reporting
    8. Performance of queries and Joins
    9. Index Rebuilding
    10. Database growth
    11. Archival Strategies
    12. Scalability
    13. ... and anything else which you feel I might have missed.

    I need to understand the pros and cons of all these areas before taking my next steps around enhancing the same.

     

    Any help in this regards would be greately welcome. PLS if you are already working as an Architect my expectations from you are much higher in terms of your opinion.

     

    Thanks in anticipation.

     

    - Shreesh Trivedi

    Friday, July 4, 2008 12:03 PM

Answers

  • This is not the correct forum I will move you to architecture but you could do all of the above without Biztalk if all your customers use Excel tell them to save the Excel as XML and collect it in one folder. Then create an automated SSIS package that checks the folder to move the files to your database.  And everything about the database is normal size, I don't think it will affect your application performance if all your objects are modeled.  One more thing get to know the SQL Server Agent most of the tasks you listed can be automated.

    Friday, July 4, 2008 1:21 PM
  • Hello Shreesh,

    I think your doubts are justified. Having a fresh start I would probably opt for a two stage ETL solution. Importing and checking data in one step, importing valid data into the working/live database. This is a one way solution btw, if it has to be bidirectional I'd choose a different strategy. Since this is <only> ETL BizTalk would not be needed, as you suggested.

    succes!

    Remco van Toor, Software Architect


    Tuesday, July 8, 2008 10:13 PM

All replies

  • This is not the correct forum I will move you to architecture but you could do all of the above without Biztalk if all your customers use Excel tell them to save the Excel as XML and collect it in one folder. Then create an automated SSIS package that checks the folder to move the files to your database.  And everything about the database is normal size, I don't think it will affect your application performance if all your objects are modeled.  One more thing get to know the SQL Server Agent most of the tasks you listed can be automated.

    Friday, July 4, 2008 1:21 PM
  • Hello Shreesh,

    I think your doubts are justified. Having a fresh start I would probably opt for a two stage ETL solution. Importing and checking data in one step, importing valid data into the working/live database. This is a one way solution btw, if it has to be bidirectional I'd choose a different strategy. Since this is <only> ETL BizTalk would not be needed, as you suggested.

    succes!

    Remco van Toor, Software Architect


    Tuesday, July 8, 2008 10:13 PM