locked
Sql Server 2000 Performance RRS feed

  • Question

  • I am about to Redesign a banking system

    In our company we discussing an upgrading of our banking system from stand alone branch system to Central Bank system and from our experience on the old system we noticed a  degraded in the performance so we are very cautious about moving to centralize system.

     

    Each distributed database contains some files which grow very large eg file with record of 250 char would grow to 5000000 record in three years if I multiply this with number of branches which would be accumulated it will come to 200,000,000 record .

    So do you think it is better to keep the tables separately or combine them in one table.

    Best Regards

    Abdulhakim

    hakkim_m_rahumi@hotmail.com

    • Moved by Tom PhillipsEditor Tuesday, April 27, 2010 8:10 PM Database Design Question (From:SQL Server Database Engine)
    Tuesday, April 27, 2010 8:07 PM

Answers

  • Hi AbdulHakim,

    You can configure the above based on many parameters and criteria (Data Storage Disks, Table Structure design, database size, archive old data in separate database, DB backup plan,...).

    I advice you upgrading to SQL Server 2005 since You can combine the data in one table in a centralize database and apply Table Partitioning which means that you can horizontally partition the data in your table , thus deciding in which filegroup each rows must be placed. However each filegroup can be stored on different physical disks for I/O performance.

    This allows you to operate on a partition even with performance critical operation, such as re-indexing, without affecting the others. In addition, during restore, as soon a partition is available; all the data in that partition are available for querying, even if the restore is not yet fully completed.

    Check this link for more details

    http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx

    For sql Server 2000,  you can use partitioned views.

    Regards,

    Tarek Ghazali

    SQL Server MVP 

     

     

    Tuesday, April 27, 2010 9:55 PM
  • I agree with Tarek Ghazali, It can be achieved in many ways...like (Data Storage Disks, Table Structure design, database size, archive old data in separate database, DB backup plan,...). as said above.

    Adding to Tarek Ghazali, why not upgrade to SQL Server 2008...you have many more options present in SQL Server 2008 new version.

    1. http://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx

    2. http://www.microsoft.com/sqlserver/2008/en/us/overview.aspx

    Thanks,

    Sandeep

    Wednesday, April 28, 2010 12:16 PM

All replies

  • Hello.

    Since you are using SQL Serve 2000 i would suggest you have a look at partitioned views from which you could logicaly build a "centralized system"

     

    Tuesday, April 27, 2010 9:43 PM
  • for large tables it is better to use distributed partitioned views (sql server 2000 supports it)

    and distibute data over several sql servers and use views to collect them

    it is a common system called federated database system

    http://msdn.microsoft.com/en-us/library/aa933141%28SQL.80%29.aspx


    http://www.sdtslmn.com
    Tuesday, April 27, 2010 9:45 PM
  • Hi AbdulHakim,

    You can configure the above based on many parameters and criteria (Data Storage Disks, Table Structure design, database size, archive old data in separate database, DB backup plan,...).

    I advice you upgrading to SQL Server 2005 since You can combine the data in one table in a centralize database and apply Table Partitioning which means that you can horizontally partition the data in your table , thus deciding in which filegroup each rows must be placed. However each filegroup can be stored on different physical disks for I/O performance.

    This allows you to operate on a partition even with performance critical operation, such as re-indexing, without affecting the others. In addition, during restore, as soon a partition is available; all the data in that partition are available for querying, even if the restore is not yet fully completed.

    Check this link for more details

    http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx

    For sql Server 2000,  you can use partitioned views.

    Regards,

    Tarek Ghazali

    SQL Server MVP 

     

     

    Tuesday, April 27, 2010 9:55 PM
  • I agree with Tarek Ghazali, It can be achieved in many ways...like (Data Storage Disks, Table Structure design, database size, archive old data in separate database, DB backup plan,...). as said above.

    Adding to Tarek Ghazali, why not upgrade to SQL Server 2008...you have many more options present in SQL Server 2008 new version.

    1. http://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx

    2. http://www.microsoft.com/sqlserver/2008/en/us/overview.aspx

    Thanks,

    Sandeep

    Wednesday, April 28, 2010 12:16 PM
  • Hi all

    Thanks for answer and respond.

    I need upgrade commercial application stand alone branch system  using microsoft old technology  client/server with ms visual studio,3-Tier model,com+,ms sql server 2000,ad,msmq,windows 2000 advanced server  .

    client interface using main exe project and ActiveX projects and com+.

    with microsoft visual studio 2005(c#2005),microsoft sql server 2005

    moving to centralize system .

    what client interface using ?

    Best Regards

    Abdulhakim

    hakkim_m_rahumi@hotmail.com

    Wednesday, April 28, 2010 9:30 PM