locked
SQL migration question RRS feed

  • Question

  • Hi

    we are planning to change the reporting architecture. Currently we use IBM and Oracle databases and have some third party reporting tools.

    We want to migrate onto SQL Server 2008 R2 and use SSIS, SSAS, SSRS along with the data warehousing.

    We will have 20TB of data going forward and we are looking towards a SAN solution for the storage.

    What do I need to look out for in planning this? How should I spec the environment/servers/storage?

    Should I go the virtual or physical road?

    Thanks in advance

    Wednesday, August 24, 2011 3:59 PM

Answers

  • That is a huge question, I don't think there is one answer.  And requirements are not clear.  Given that you have 20TB data, the system will be intensive enough that I think Physical road would be best prefered.

    Additional considerations you should take into account are possibly for all SQL Server resrouces must be located on seperate physical servers and not shared services.  SAN techology must be able to hand large amount of work from OLTP and OLAP systems. 

    When designing the disk mode for SQL Server, general best practices are to make sure data, log and tempdb are on seprerate drives.  In addition they are all 64-kb off-site aligned (default by Windows 2008+) with 64KB allocation block size.

    For SSAS the recommendation seem to be 32KB you can read more about that on SQLCAT site to get better understanding.

    SQL Server mode in your case should be enterprise and 64-bit.  Enterprise will allow you to have more than 2-node clustering if that is an option you enterptain.  It will also allow you access to some of the more important functionality that might be of use to you.  Such as encryption, compression, auditing, etc.

    If you go enterprise, you can also look at deploying SSRS as scale-out deployment.

    SSIS are used for package management and monitoring not the actual execution, their for that doesn't require a heavy system; but if you want to storage the packages outside of the SQL Server you can store it on file system also and still manage them using SSIS.

    Memory and Disk will most likely will be the biggest issues, with CPU trailing close beind.  However it is hard to make that as strong statement without any kind of additional data...

    Anyhow thats my two cents, take it as that, with grain of salt.  Food for thought...

    Cheers!


    Mohit K. Gupta --- MCTS, MCITP, MCC2011 http://sqllearnings.blogspot.com/
    • Marked as answer by Peja Tao Friday, August 26, 2011 2:52 AM
    Thursday, August 25, 2011 4:10 AM

All replies

  • Hi

    we are planning to change the reporting architecture.  Currently we use IBM and Oracle databases and have some third party reporting tools.

    We want to migrate onto SQL Server 2008 R2 and use SSIS, SSAS, SSRS along with the data warehousing.

    We will have 20TB of data going forward and we are looking towards a SAN solution for the storage. 

    What do I need to look out for in planning this?  How should I spec the environment?

    Should I go the virtual or physical road?

    Thanks in advance

    • Moved by Peja Tao Friday, August 26, 2011 2:44 AM (From:Database Design)
    • Merged by Peja Tao Friday, August 26, 2011 2:51 AM
    Wednesday, August 24, 2011 2:54 PM
  • As your question appears to be about migration to SQL Server and not about database design, I suggest you post your question in the forum dedicated to your topic, here:

    http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration/threads

    I believe you'll get the best suggestions there.

    Wednesday, August 24, 2011 3:51 PM
  • That is a huge question, I don't think there is one answer.  And requirements are not clear.  Given that you have 20TB data, the system will be intensive enough that I think Physical road would be best prefered.

    Additional considerations you should take into account are possibly for all SQL Server resrouces must be located on seperate physical servers and not shared services.  SAN techology must be able to hand large amount of work from OLTP and OLAP systems. 

    When designing the disk mode for SQL Server, general best practices are to make sure data, log and tempdb are on seprerate drives.  In addition they are all 64-kb off-site aligned (default by Windows 2008+) with 64KB allocation block size.

    For SSAS the recommendation seem to be 32KB you can read more about that on SQLCAT site to get better understanding.

    SQL Server mode in your case should be enterprise and 64-bit.  Enterprise will allow you to have more than 2-node clustering if that is an option you enterptain.  It will also allow you access to some of the more important functionality that might be of use to you.  Such as encryption, compression, auditing, etc.

    If you go enterprise, you can also look at deploying SSRS as scale-out deployment.

    SSIS are used for package management and monitoring not the actual execution, their for that doesn't require a heavy system; but if you want to storage the packages outside of the SQL Server you can store it on file system also and still manage them using SSIS.

    Memory and Disk will most likely will be the biggest issues, with CPU trailing close beind.  However it is hard to make that as strong statement without any kind of additional data...

    Anyhow thats my two cents, take it as that, with grain of salt.  Food for thought...

    Cheers!


    Mohit K. Gupta --- MCTS, MCITP, MCC2011 http://sqllearnings.blogspot.com/
    • Marked as answer by Peja Tao Friday, August 26, 2011 2:52 AM
    Thursday, August 25, 2011 4:10 AM
  • Many thanks for your response Mohit!

    That has helped me greatly and I appreciate you answering!

    Thursday, August 25, 2011 2:25 PM