locked
Using SQL server (not express) for reporting needs. RRS feed

  • Question

  • We have several types of databases in our network.  People have used Microsoft Access on their PC's and the run times have gotten very long.  Here is an example of a query and the tables it uses.

    BILL_CURRENT (imported, current billing details, about 3500 rows)
    SHIPMENTS (imported, shipping details from our shipping system, about 25,000 rows)
    CUSTOMER (ODBC link to DB2 table on AS400, about 100,000 rows)
    RESIDENTIALS (imported table, about 1000 records)

    We have a few Windows 2003 & 2008 servers with SQL licenses.  Should we consider exporting data into a SQL table and run our reports on the server.  The Access databases are stored on shared drives on Windows servers and run on the PC (Access is loaded on the PC not the server). 

    Is there a better approach to bringing the data all in one place to meet our reporting needs?  Should we consider a data warehouse approach to help solve our performance issues?

    Thanks
    Thursday, March 11, 2010 5:30 PM

Answers

  • I would recommend you to use MS SQL server for reporting needs. So to import data from all other databases, please use SSIS (SQL Server Integration Service), moreover this is a FREE tool that come as part of SQL Serve License. I have experienced 892 million records with SQL Server 2005 without any performance issues. Data ware housing tools are very expensive and the volume of record greater that 2 billion really suits for it.
     
    I have attached link below,  How to do with SSIS ?
     
     
    Please feel free to contact incase you are not clear with my above statements. Mail me if you need any help too.
     
    - Ananth Ramasamy Meenachi www.msarm.com
     
    "SteveGemini" wrote in message news:19a1669a-b039-48a2-a4c4-e363ca817623...
    We have several types of databases in our network.  People have used Microsoft Access on their PC's and the run times have gotten very long.  Here is an example of a query and the tables it uses.

    BILL_CURRENT (imported, current billing details, about 3500 rows)
    SHIPMENTS (imported, shipping details from our shipping system, about 25,000 rows)
    CUSTOMER (ODBC link to DB2 table on AS400, about 100,000 rows)
    RESIDENTIALS (imported table, about 1000 records)

    We have a few Windows 2003 & 2008 servers with SQL licenses.  Should we consider exporting data into a SQL table and run our reports on the server.  The Access databases are stored on shared drives on Windows servers and run on the PC (Access is loaded on the PC not the server). 

    Is there a better approach to bringing the data all in one place to meet our reporting needs?  Should we consider a data warehouse approach to help solve our performance issues?

    Thanks

    --- Hope this helps. Ananth Ramasamy Meenachi http://www.msarm.com
    Thursday, March 11, 2010 10:07 PM
  • if you use SQL Server you can get solve all your problems in one platform

    you can replace Access with SQL Server by using Integration Service

    After installing IIS you can reach your reports wherever you want by using Reporting Service

    and finally you could use analysis service to analyze data and make predictions
    Friday, March 12, 2010 11:12 AM
  • First off, why not express? It is free and the rowcounts you give aren't too large (unless the size of the rows are large). Obviously if you have SQL Licenses, that is much better. 

    You came to a SQL Server forum, so I will guarantee you that 99% of us go "blech" to Access and will tell you to "upgrade" to SQL Server.  I have heard good things about Access, but if you know what you are doing and have tuned Access to the limitations, that it is very likely that putting your databases on SQL Server will improve performance (the ODBC link to DB2 seems like a very likely bottleneck). 

    But, it is all dependent on what you are doing, and whether you can bring all of the data to the SQL Server, and how well you can tune the ODBC connection, either using a linked server on SQL Server, or SSIS to import your data into the server.
    Louis

    Saturday, March 13, 2010 10:25 AM

All replies

  • I would recommend you to use MS SQL server for reporting needs. So to import data from all other databases, please use SSIS (SQL Server Integration Service), moreover this is a FREE tool that come as part of SQL Serve License. I have experienced 892 million records with SQL Server 2005 without any performance issues. Data ware housing tools are very expensive and the volume of record greater that 2 billion really suits for it.
     
    I have attached link below,  How to do with SSIS ?
     
     
    Please feel free to contact incase you are not clear with my above statements. Mail me if you need any help too.
     
    - Ananth Ramasamy Meenachi www.msarm.com
     
    "SteveGemini" wrote in message news:19a1669a-b039-48a2-a4c4-e363ca817623...
    We have several types of databases in our network.  People have used Microsoft Access on their PC's and the run times have gotten very long.  Here is an example of a query and the tables it uses.

    BILL_CURRENT (imported, current billing details, about 3500 rows)
    SHIPMENTS (imported, shipping details from our shipping system, about 25,000 rows)
    CUSTOMER (ODBC link to DB2 table on AS400, about 100,000 rows)
    RESIDENTIALS (imported table, about 1000 records)

    We have a few Windows 2003 & 2008 servers with SQL licenses.  Should we consider exporting data into a SQL table and run our reports on the server.  The Access databases are stored on shared drives on Windows servers and run on the PC (Access is loaded on the PC not the server). 

    Is there a better approach to bringing the data all in one place to meet our reporting needs?  Should we consider a data warehouse approach to help solve our performance issues?

    Thanks

    --- Hope this helps. Ananth Ramasamy Meenachi http://www.msarm.com
    Thursday, March 11, 2010 10:07 PM
  • if you use SQL Server you can get solve all your problems in one platform

    you can replace Access with SQL Server by using Integration Service

    After installing IIS you can reach your reports wherever you want by using Reporting Service

    and finally you could use analysis service to analyze data and make predictions
    Friday, March 12, 2010 11:12 AM
  • First off, why not express? It is free and the rowcounts you give aren't too large (unless the size of the rows are large). Obviously if you have SQL Licenses, that is much better. 

    You came to a SQL Server forum, so I will guarantee you that 99% of us go "blech" to Access and will tell you to "upgrade" to SQL Server.  I have heard good things about Access, but if you know what you are doing and have tuned Access to the limitations, that it is very likely that putting your databases on SQL Server will improve performance (the ODBC link to DB2 seems like a very likely bottleneck). 

    But, it is all dependent on what you are doing, and whether you can bring all of the data to the SQL Server, and how well you can tune the ODBC connection, either using a linked server on SQL Server, or SSIS to import your data into the server.
    Louis

    Saturday, March 13, 2010 10:25 AM