none
Server upgrade and consolidation best practices RRS feed

  • General discussion

  • Good morning,

    I hope this is the appropriate forum for this discussion.  My apologies, in advance, if it is not.

    I am the SQL DBA for an organization running multiple SQL Servers (SQL2k and SQL 2k5).  Most of these database servers support third party applications such as Microsoft Office SharePoint Server (MOSS), other Microsoft Products and numerous other vendor's products.  In addition there are several development efforts which utilize various SQL server services as well as the database engine itself.  My boss is pressuring for some consolidation/upgrade of this architecture and I totally agree.  The issue is one of the senior managers is proposing a single clustered instance of SQL server to support all these scenarios.  Is this a reasonable alternative?  My initial reaction is that this is NOT a good idea.  The current configuration supports over 300 databases and over 5000 users both internal and external (through a DMZ) to our network.  

    This particular manager is an extremely knowledgable network and system engineer but does not know much about SQL Server.  However he is adamant about this proposed achitecture.  Windows clustering was choosen as a high availability option without investigating the other possibilities available to SQL Server.  While my organization does not have unlimited funding, hardware purchases and hardware lifecycle maintenance are fairly robust and usually get approved, so this is not an insurmountable obstacle.

    I realize each organization is unique but would like the group's feedback on some general architecture and configuration best practices.  If this were a proposal in your company what recommendations and arguments would you present?  I am already working on several different proposals for my boss and would like to reduce these to "executive/managment speak" as opposed to my more technical explanations.

    Thanks in advance for your advice. 

    Friday, March 13, 2009 1:21 PM

All replies

  • Security would be the primary concern.  Single Clustered Instance -> Can all these Application/products  have Single Sysadmin. Very first reason for multiple instance
     
    Since cost is not an issue if you have proper hardware 300 DBs are not an issue for SQL Server.

    Do you have both OLTP and OLAP applications in this env? in that case performance may be an issue like run away queries in OLAP APP may slowdown your day to day busy OLTP application.


    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Friday, March 13, 2009 4:51 PM
    Moderator
  • I agree that failover clustering is a great HA alternative if you are trying to consolidate 300 databases.  However, a single failover configuration may not be able to support all of your workloads.  When consolidating, I typically implement multiple instance failover configurations on 2 nodes up to 16 nodes when running Windows Server 2008 and SQL Server 2008.  16 nodes may not be necessary, however, you most likely require more than one instance on 2 to 3 nodes.  For example, you can implement 2 instances on 3 nodes.  Instance 1 would be active on Node 1, Instance 2 would be active on Node 2 and Node 3 would be passive for both Instance 1 and Instance 2. 

    When consolidating, it is a best practice to start off with a single instance and add more instances if you require:

    - Security and Autonomy
    - Have different Server Collation Settings
    - Have different Authentication Requirements
    - Have different workloads - OLTP, OLAP
    - Clustering different SQL Components - ie isolate Database Engine and Analysis Services
    - Single Instance can not support the I/O requirements of all the database, therefore, workload should be spread across instances and physical nodes.

    Here is a great whitepaper from MS on consolidation:

    Server Consolidation with SQL Server 2008

    http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx

    Thanks

    Ross Mistry

    ---------------------

    Ross Mistry, MVP
    Author: SQL Server 2008 Management and Administration & Windows Server 2008 Unleashed
    Blog Site: http://www.networkworld.com/community/mistry




    ------ Ross Mistry, MVP Author: SQL Server 2008 Management and Administration and Windows Server 2008 Unleashed.
    Monday, March 16, 2009 2:55 AM
  • Could I suggest that the most convincing argument in "exec/management speak" is a series of diagrams showing the databases, users, and uses that your future system should be able to manage. The visual logic makes real what they are making decisions on. These diagrams then form the centre of your presentation when you pose real life scenarios (like failure of a piece of hardware or corruption of system files.) If you can find a way to ennumerate something of the time saving/efficiency of at least three arrangements that explore Sql servers capabilities even better. Remember that you will  be presenting a technical argument in contrast to your managers technical argument and the decision makers will be grateful if you address their priorities first, and any conflict between your suggestions and his last (if at all). Put your whole heart into proving his ideas, rather than disproving them. (You may find more benefits in his proposal than you thought).

     If it were up to me I would take take this advice from Ross Mistry
     "When consolidating, it is a best practice to start off with a single instance and add more instances if you require:
    - Security and Autonomy
    - Have different Server Collation Settings
    - Have different Authentication Requirements
    - Have different workloads - OLTP, OLAP
    - Clustering different SQL Components - ie isolate Database Engine and Analysis Services
    - Single Instance can not support the I/O requirements of all the database, therefore, workload should be spread across instances and physical nodes."

    I would show this advice to the manager who prefers a single instance along with any other best practice quotes I could find.
    Friday, April 17, 2009 11:35 AM
  • There will be another whitepaper on consolidation with SQL Server 2008 which is more technical and focused on other aspects (i.e. planning and doing the actual consolidation) coming from Microsoft along with some job aids in the next few months. The one Ross links is a good resource for higher ups.


    Allan Hirt Blog: http://www.sqlha.com/blog Author: Pro SQL Server 2008 Failover Clustering (Apress - due out June, 2009)
    Friday, April 17, 2009 1:00 PM
  • Hi Allan,

    Do you have a publishing date on the new white paper?  If so please provide the link and date!!!

    Thanks Much!!!

    Ross
    ------ Ross Mistry, MVP Author: SQL Server 2008 Management and Administration and Windows Server 2008 Unleashed.
    Friday, April 24, 2009 5:00 AM