Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

SQL Server 2008 features - Relational & Data Warehouse Scalability list (new) - SQL Server Knowledge Sharing Network (@sqlmaster)

SQL Server 2008 features - Relational & Data Warehouse Scalability list (new)

Scalability is an important aspect in every application, a common achievement within OLTP environment and still applicable to OLAP (Anlaysis Services) environment. In any if the application or a cube or mining model may be unavailable for querying because of a hardware or software failure, also it could be one of the reasons such as unavailable because it needs to be processed.

In this case running multiple instances of SQL Server can be managed easily in terms of performance & scalability, this doesn't applicable on an easy terms for Analysis Services applications as it may cause performance issues. It may be general recommendation that hiking the processor, memory, and disk resources on the server, but more you need to concentrate on scaling the instances of SQL Server and Analysis Services across multiple computers. To achieve this say if the relational database resides along with an Analysis Services database that is suffering hard performance issues, then you can plan move these databases to a separate computer. As a baseline you need to consider the network speed and bandwidth that exist between the Analysis Services database and its underlying databases. Many times the usual suspect of network problems are missed due to the reason of slow or congested, moving the underlying databases to a separate computer will cause affect processing performance.

When you talk about Scalability then Clustering comes into mind as it can be used to scale out the applications, but if query performance is poor but you cannot increase the processor and memory resources on the local server, consider deploying an Analysis Services project onto two or more production servers. In this case you may need additional component such as Network Load Balancing application to make these instances into that cluster.

A simple list of configuring the scalability environment is goes by:

  • Ensure to keep similar level of service pack/hot fixes on all the SQL Servers, Reporting Services & Analysis Services instances if the databases (relational or Analysis Services) are shared  across the servers.
  • In  case of Reporting Services server ensure to test and apply the update on all the associated reporting servers that serve the same scalable shared database or databases.
  • Microsoft recommendation on Scalability side of hardware that no limit on the number of instances, but having a baseline on shared database configuration to 8 servers per database.
  • In the case of Reporting Services database and volumes, make sure to keep mounting a set of reporting volumes onto your reporting servers, use the same drive letter on every server to facilitate managing the volume across the different servers. 
  • In terms of Collation & Sort order ensure to keep all of the instances to use same sort order.
  • Ensure to maintain same set of hardware components such CPU, Disks and even memory settings in similar on all of the instances. In general it is better to go, otherwise if any issue arises it will be hard to identify on which instance it has gone wrong.
  • If the relational or reporting database is spread across the shared servers then ensure to test and deploy synchronized update than a rolling update of scalable shared database.

So when talking about new product features you would be looking at scalability & performance features that can be achieved, similar to this SQL Server 2008 has such advantages. Refer to this  New-Data-Warehouse_Scalability_Features within SQL Server 2008 that introduces the new performance and manageability features for data warehousing across all these components. All these features contribute to improved scalability


Published Thursday, June 12, 2008 4:04 AM by ssqa.net


No Comments