SQL Server Performance

Best Practices for Reporting Purpose Databases

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by sqlexpert, Sep 21, 2007.

  1. sqlexpert New Member

    Hi ,
    can any body pls confirm what are the best practices for databases used only for reporting purposes ,our requirement is users should also be able to create views on this database and should be able to pull the reports by individual or by web tool.
    Thanks..
    sqlexpert, Sep 21, 2007
    #2

  2. ndinakar Member

    Deny access to create any objects like tables. Make sure all the tables are properly indexed.
    ndinakar, Sep 24, 2007
    #3

  3. satya Moderator

    True, having better indexes is good for optimum performance.
    Also ensure the users requires, at a minimum CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created. For this required further reference on updated BOOKS ONLINE 2005 is recommended.
    satya, Sep 25, 2007
    #4

  4. IDTX2 New Member

    Is the database on it's own server or in it's own instance or is it sharing resources with your production OLTP database?
    IDTX2, Oct 3, 2007
    #5

  5. sqlexpert New Member

    It will be on different server and will take data from our production OLTP server through replication.Thanks
    sqlexpert, Oct 5, 2007
    #6

  6. satya Moderator

    satya, Oct 7, 2007
    #7

  7. sqlexpert New Member

    Thanks it looks link is removed.
    sqlexpert, Oct 9, 2007
    #8

  8. satya Moderator

    satya, Oct 15, 2007
    #9

  9. Raulie New Member

    To let users freely query the database and create views as desired is a WORST practice in my opinion. If this is a small database with tables that contain a few hundred rows this is probabaly no biggy, but for large environments this could be over kill. If you must allow users such access, just follow some of the best practices for performance tunning on the articles and tips section, creating good indexes, monitoring server resources etc..
    Raulie, Oct 13, 2007
    #10

Share This Page