Consolidating SQL Server Databases

Editor's Note: The following MVP Monday post is by French SQL Server MVP David Barbarin and is available in both English and French.

Why shall I consolidate?

Consolidation, who doesn’t know this term? The number of IT projects about consolidation has increased these last years. The databases are also concerned. Then can we ask if we are in a fashion wave? The response is no, because consolidation provides a lot of advantages. TCO reduction is certainly the best argument for the financial managers. The aim is to reduce the infrastructure cost while optimizing the use of hardware resources. Administration also becomes simpler for IT staff. The sprawl of SQL Server instances implies the increase of complexity to administrate and the increase of the total cost of licenses. Furthermore, the advent of virtualization appeared as a consolidation accelerator. Indeed, today the provisioning of the servers in production environment and others becomes easier regarding a configuration standard while sharing the resources of a physical computer between virtual hosts. Finally, reducing the number of physical servers also opens the way towards the storage convergence. It allows considering more robust solutions about fault tolerance, performance and scalability.

Consolidate with one or many instances?

When we talk about consolidation with SQL Server, we have quickly to ask if we need to implement a standalone or multi instances. Keep in mind that a consolidated server hosts a lot of databases and applications. Several factors are involved in the definition of the target architecture like security, server collation, resources control or difference of versions. The step is very important and should not be overlooked.

Each application has its own set of prerequisites for security.  Some of them require server level permissions or maybe administrator privileges for the SQL Server instance. It will be possible to reduce this set of privileges but only in certain cases, which can compromise the security of the others databases. The choice to setup a dedicated instance can be a good option. The main goal is to give an application the control of the SQL Server instance and to isolate in the same time the security of the other databases.

Furthermore, the server collation determines the collation which the system databases use like model or tempdb. It will ensure the fact that all the databases of the consolidated instance that will use tempdb have a compatible collation in order to avoid conflicts during join operation for example.

We can add that an assessment of resources consumed by an application on the database server must be estimated before the deployment on production environment. Indeed, each application consumes a certain quantity of resources like memory, processors, network or disks. The purpose of this assessment is to verify that each database or each set of databases will not use resources of others databases. In certain contexts, SLA can exist and require a minimum threshold of resources to ensure an adequate level of service for an application. SQL Server 2008 provides an interesting feature that could meet to the requirements: the resource governor that allows to control and to limit the application resources consummation by using resources pools and workload groups. Unfortunately this feature is available only with Enterprise Edition and in this case the implementation of a dedicated instance is the only alternative left.

Finally, we will often face to an environment with many different versions of SQL Server. In this context, we have two options : update the application to the referenced version for consolidation or add an instance with the adequate version. Many instances with different versions will exist on the same consolidated server. Each application will be spread to the correct instance depending on its version. However before to install a SQL Server with a specified version, the application manufacturers should be consulted to verify if the migration to the correct version is available. In most cases, we can use the compatibility level of databases to migrate slowly without the management of an additional instance and its resources.

Consolidation and High-availability

High- availability is a concept that we always have to keep in mind with consolidation.  Reducing the
TCO on the entire SQL Server infrastructure could be considered as an investment in high-availability infrastructure. RPO (Recovery Point Objective) and RTO (Recovery Time Objective) are generally the safety keys that will determine the target architecture. Of course, we will have to take into account the budget aspect. It is the same thing with storage. The sprawl of standalone SQL Server instances on the dedicated physical servers often involves the use of local storage or JBODS (Just Bund of Disks). Consolidating SQL Server instances will often converge in the same time into the local storage to a solution that will be more centralized, efficient and scalable. Generally, companies will opt for a SAN (Storage Area Network) that provides more advantages than local storage or DAS (Direct Attached Storage) although the performance of SAN versus DAS is not so obvious. For example, back up local disks by backup agents over the network is much more complex when it comes to DAS. Moreover, during a hardware failure of a server, we can easily move the storage to a backup server with SAN. 

Why shall we integrate a high- availability solution to consolidation? The implementation of a consolidated infrastructure centralizes the hosting of all the databases of the information system, making it much more critical. Indeed, stopping a consolidated instance can have much more impact on the business because all the databases and applications become unavailable. Maintenance operations such as update security or services packs become more complex or can degrade the business during a long time (depending on the maintenance operation) if no failover solution is implemented.  Therefore, it is important to secure this environment and ensure a certain level of service.

Consolidation plan

What are the elements to consider for a successful consolidation project of SQL Server instances? Once the identification of SQL Server instances to consolidate is completed, we have to begin an assessment of the server resources needed to determine the target server. Then, we must also identify individually the consumed resources by each database or by each set of databases of an application to ensure that they have a similar level of service than the old environment or even increase it. SQL Server 2008 R2 provides a good feature for that: UCP (Unit Control Point) that allows getting information about resources CPU and space used on disks in centralized manner. Unfortunately, UCP is limited to manage SQL Server instances with a version equal to 10.0.4 and more (SQL Server 2008 SP2 and more). 32 bits architectures are deprecated today and we have to focus on 64 bits architecture for consolidating environments. This type of architecture removes the limitation of 4GB of memory and increases the working set of SQL Server. 

In addition, we have also to ensure a correct sizing of the database tempdb. Indeed, keep in mind that there is only one tempdb database on a SQL Server instance. In a consolidated instance, this aspect is very important because tempdb can be used heavily depending on the number of the databases and their workload. A good practice is to place tempdb on a dedicated performance.  Determining the number of files to create is more complicated. We can calculate this number based on the rule of one file per processor or one file per two processors depending on the version of SQL Server. We can also estimate the number of file based on the workload burden on the server by monitoring.

Finally, we must also consider the overlapping of the login accounts and the security model. Indeed, it may happen that several applications use the same logins with a different password. Similarly, the security model used by a login account for an application can be not compatible on the target architecture. If nothing can be changed, we will probably have to consider an additional instance to host the concerned applications.  

Conclusion

In a perfect world the consolidation of SQL Server instances could be a simple task but in reality we have to consider a lot of things like the understanding of the current architecture, the applications used by companies or the security model. Planning is an important phase of the consolidation project because it ensures that the target architecture will meet the requirements of performance and the availability required by the business.

Author’s Bio

David BARBARIN is currently a database consultant for a Microsoft GOLD PARTNER company in switzerland and participates in the development of add-valued offers for data management around Microsoft SQL Server. David was a speaker on several sessions with GUSS and SQLPass in switzerland since 2010 and wrotes several articles and blog posts about SQL Server. Furthermore, he is actively involved in SQL Server community such Developpez.com, Technet SQL Server and Beyond Relational. David brings his experience to various clients for whom he could work.

MVP Mondays

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager for Dynamics, Excel, Office 365, Platforms and SharePoint in the United States. She has been working with MVPs since her early days as Microsoft Exchange Support Engineer when MVPs would answer all the questions in the old newsgroups before she could get to them

French

Pourquoi consolider ?

Consolider est un terme qu’il est impossible de ne plus entendre de nos jours dans nos entreprises. Le nombre de projets informatique autour de la consolidation n’a pas cessé de croître ces dernières années. Les bases de données ne dérogent pas à la règle.  On peut alors se poser la question suivante : sommes-nous dans un phénomène de mode ? La réponse est bien entendu non. La consolidation amène son lot d’avantages. La réduction du TCO est sans aucun doute l’argument le plus vendeur auprès de la direction financière parce qu’il s’agit ici de réduire les coûts d’infrastructure tout en optimisant l’utilisation des ressources matérielles. De plus le travail des équipes IT devient également beaucoup plus simple car la multiplication des instances SQL Server sur le réseau impliquaient une augmentation des serveurs à administrer d’une part et une augmentation du coût global des licences d’autre part. L’avènement de la virtualisation a été également un accélérateur de consolidation. En effet, aujourd’hui il devient relativement simple et rapide de provisionner des serveurs dans un contexte de production ou autre en fonction de standard de configuration par exemple tout en partageant les ressources d’une même machine physique entre les hôtes virtuels.   Enfin, la  réduction du nombre de serveurs physiques ouvre la voie vers la convergence du stockage, ce qui permet d’envisager des solutions beaucoup plus robustes en termes de tolérance de pannes, de gestion et d’évolutivité.

Consolider sur une ou plusieurs instances ?

Lorsqu’il est question de consolidation avec SQL Server, le choix d’une installation standalone ou multi instances se pose rapidement. Il faut garder à l’esprit  qu’une instance consolidée héberge un certain nombre de bases de données et donc un certain nombre d’applications. Plusieurs facteurs entrent en jeu dans la définition d’une architecture cible comme la sécurité, la collation de niveau serveur, le contrôle des ressources ou la différence de versions qui existent sur l’environnement existant. Cette phase  est donc très importante et ne doit pas être négligée.

Chaque application possède son lot de prérequis en terme de sécurité. Certaines d’entre elles nécessiteront des permissions de niveau serveur voire même des privilèges administrateur sur l’instance SQL. On pourra tenter de réduire ce niveau de privilège, mais seulement dans certains cas, ce qui peut donc compromettre la sécurité des autres bases de données. Le choix d’une instance dédiée peut s’avérer alors une bonne alternative. Il s’agit ici de laisser la possibilité à une application de gérer l’instance SQL Server qui l’héberge et d’isoler la sécurité des autres applications.

D’autre part, la collation de niveau serveur détermine celle qui sera utilisée par les bases de données systèmes tels que model ou tempdb. Il faudra veiller à ce que l’ensemble des bases de données de l’instance SQL consolidée qui utiliseront tempdb possèderont une collation compatible afin d’éviter les conflits lors des opérations de jointure par exemple.

En outre, une estimation des ressources consommées  (processeurs, disques, réseau, mémoire) par une  application sur le serveur de bases de données doit être effectuée avant toute mise en production. Le but est de vérifier qu’une base de données ou un ensemble de bases de données ne viendra pas vampiriser l’ensemble des  ressources allouées à une instance, au détriment des autres. Dans certains contextes d’entreprise, des SLA peuvent exister et déterminent des seuils de ressources minimum à respecter pour garantir un niveau de service suffisant pour une application. SQL Server propose depuis sa version 2008, une fonctionnalité intéressante qui peut répondre à cette problématique : le gouverneur de ressources qui permet de contrôler et de limiter les ressources consommées  pour une application à travers des pools de ressources et des groupes de charge de travail sur une instance SQL Server. Ce dernier  n’est malheureusement disponible qu’à partir de l’édition Entreprise et la mise en place d’une instance dédiée devient alors la seule alternative.

Enfin, il arrive souvent que l’environnement SQL Server à consolider possède des niveaux de versions diverses et variés. Dans ce cas, deux options s’offrent à nous : mettre à niveau l’application vers une version de SQL Server référencée comme version de consolidation ou ajouter une instance  supplémentaire mais avec un niveau de version différent. Ainsi, plusieurs instances de versions différentes cohabiteront sur un même serveur de consolidation, et les applications pourront être réparties sur chaque instance en fonction de la version de SQL Server qu’elles utilisent. Cependant avant de créer une instance SQL Server avec un niveau de version spécifique, on pourra consulter l’éditeur sur la possibilité de migrer son application vers le niveau de version adéquate. Dans la plupart des cas, il est possible de contourner le problème de version en utilisant les niveaux de compatibilité de bases de données pour effectuer une migration en douceur et faire l’économie  d’une  gestion supplémentaire d’instance et des ressources monopolisées par celle-ci.  

Consolidation et haute disponibilité

L’aspect haute-disponibilité est omniprésent dans les projets de consolidations d’instances SQL Server.  La réduction du TCO permet d’envisager un investissement sur une infrastructure de haute disponibilité. Le RPO (Recovery Point Objective) et le RTO (Recovey Time Objective) sont en général les facteurs de sécurité qui détermineront la solution adéquate. Il faudra bien entendu prendre en compte une composante budget dans le choix de la solution. Il en va de même avec le stockage. La multiplication des instances SQL Server standalone sur des serveurs physiques  la plupart du temps dédiés impliquent généralement l’utilisation d’un stockage local ou de JBODs (Just Bund Of Disks). La consolidation des instances SQL Server permet souvent de faire converger l’ensemble de ce  type de stockage vers une solution beaucoup plus centralisée, performante et évolutive. Le plus souvent les entreprises opteront pour un SAN (storage Area Network) qui offrent plus d’avantage que les stockages locaux ou DAS (Direct Attached Storage) bien que les performances des SAN face aux DAS ne soient pas si évidentes. Par exemple sauvegarder des disques locaux à travers le réseau par le biais d’agents de sauvegarde est beaucoup plus mois simple lorsqu’il s’agit de DAS. Par ailleurs, lors d’une panne hardware d’un serveur, on pourra déplacer beaucoup facilement le stockage associé vers un serveur de secours avec un SAN.

Pourquoi intégrer une solution de haute disponibilité  à la consolidation ? L’implémentation d’une infrastructure consolidée centralise l’hébergement  des bases de données du système d’information, ce qui en fait un élément beaucoup plus critique. En effet, l’arrêt d’une instance consolidée peut avoir un impact beaucoup plus important sur le business car c’est tout un ensemble de bases de données et d’applications qui deviennent indisponibles. Les opérations de maintenance telles que la mise à jour de sécurité et les services packs deviennent plus compliqués et peuvent dégrader le fonctionnement du business (en fonction de l’opération de maintenance) lorsqu’aucune solution de failover n’existe.  Il est donc primordial de sécuriser cet environnement et de garantir une certaine continuité de service. 

Planifier la consolidation

Quels sont les éléments à prendre en compte pour réussir un projet de consolidation d’instances SQL Server ? Un fois l’identification des instances SQL Server à consolider terminée,  il faudra commencer par évaluer les ressources serveurs nécessaires pour le serveur cible, puis identifier  individuellement les ressources consommées par chaque base de données ou chaque ensemble de bases de données d’une application.  Il s’agit ici de garantir un niveau de service similaire à l’ancien environnement voire l’augmenter.  SQL Server 2008 R2 propose une  fonctionnalité intéressante pour pouvoir répondre à ces deux questions : UCP (Unit Control Point) qui permet de visualiser d’une manière centralisée les ressources CPU et disques réellement utilisées par différentes instances SQL Server. Malheureusement, UCP se limite à la gestion des instances d’une version supérieure ou égale à 10.0.4 (SQL Server 2008 SP2 et plus). Si d’autres versions de SQL Server sont à consolider, UCP ne pourra pas être utilisé. Les architectures 32 bits étaient obsolètes, il faudra privilégier les architectures 64 bits pour des environnements consolidés afin de supprimer la limitation d’utilisation de la mémoire à 4Go d’une part et augmenter l’espace de travail utilisable par SQL Server d’autre part.

De plus, il faudra également veiller à dimensionner correctement  la base de données temporaire tempdb. Il faut garder à l’esprit qu’il n’existe qu’une seule base de données tempdb pour une instance SQL Server. Dans un environnement consolidé, cela a d’autant plus d’importance car son utilisation est susceptible d’augmenter en fonction du nombre de bases de données et de leur charge de travail. Une bonne pratique est de placer cette base de données temporaire sur un stockage dédié avec de bonnes  performances. Le nombre de fichiers de données à créer est quant à lui plus compliqué à estimer. On peut se baser sur la règle d’un fichier par processeur ou d’un fichier pour 2 processeurs en fonction de la version de SQL Server mais ceci n’est pas forcément juste et dépendra surtout principalement de la charge globale que devra
supporter l’instance SQL Server consolidée. Le monitoring sera donc de mise !!

Enfin, il faudra également considérer les problèmes de chevauchement des comptes de connexion et de la sécurité. En effet, il peut arriver que plusieurs applications utilisent les mêmes comptes de connexion mais avec un mot de passe différent ou que modèle de sécurité utilisé par un compte de connexion pour une application ne soit pas compatible sur l’architecture cible. Si rien ne peut être changé il faudra sans doute prévoir l’installation d’une instance supplémentaire pour pouvoir héberger ces applications.

Conclusion

Dans le meilleur des mondes la consolidation des instances SQL Server devrait être une tâche simple à réaliser. Cependant comme nous avons pu le voir, consolider nécessite de prendre en compte un ensemble de considérations importantes comme la compréhension de l’architecture physique existante, les applications utilisées ou encore le modèle de sécurité. La planification est une phase importante dans un projet de consolidation car elle permet garantir que l’architecture cible répondra aux exigences de performance et disponibilité imposées par le business.

Author’s Bio in French:

David BARBARIN est actuellement consultant en bases de données pour une entreprise Suisse GOLD Partner Microsoft et participe au développement d’offres à valeur ajoutée de data management autour de la technologie Microsoft SQL Server. Intervenant en tant que speaker à plusieurs reprises lors des sessions GUSS et SQLPass Suisse depuis 2010, David a également écrit de nombreux articles et billets de blog. De plus, il participe activement aux communautés SQL Server sur Developpez.com, Technet SQL Server et Beyond Relational.  David met son expérience au service des différents clients pour lesquels il a pu travailler.