Discussion :

[mformiga]

Bonjour,


Je suis a la recherche d'une solution pour l'un de mes clients:

Actuellement, il a un serveur SQL de prod pour son ERP.
En parallèle, il effectue de nombreuses requêtes sous Excel ( tableau de bord) et power Bi qui alourdissent son ERP.

Je voulais savoir s'il était possible d'avoir une copie de la base en lecture seule en temps réel afin de transférer ces requêtes et séparer le traitement de l'erp des requêtes SQL

Je me suis penché sur le cluster Always On ACTIVE / ACTIVE mais j'ai l'impression que cela ne réponds pas a ma problématique.
Cette fonctionnalité serait plutôt pour que chaque serveur héberge une instance différente.

J'ai besoin d'expert ^^


Merci


Merci

[SQLpro]

AlwaysOn répond parfaitement à cette attente en permettant d'avoir des nœuds passif en lecture. C'est généralement ce que l'on fait lorsque l'on a une importante quantité de requêtes en lecture pour du reporting ou pour alimenter une BI...

Mais cela nécessite :
1) la version Enterprise (la version standard ne permet pas d'avoir des réplicas lisible)
2) le paiement de la software assurance
3) le paiement des licences SQL Server sur le nœud lisible (qui ne doit pas forcément avoir les mêmes caractéristiques physique en RAM, disque et nombre de cœurs...)

Compte tenu du coût, il faut être certain que cette solution soit la seule adéquate...

Votre problème semble venir du fait de la surcharge de l'instance actuelle par rapport aux requêtes ordinaires de l'ERP... La question est donc de savoir s'il ne serait pas possible de tout faire sur l'instance actuelle avant de décider d'une telle solution...

Quelques questions pour vous guider :
0) quelle est la version / édition de SQL Server ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT @@VERSION;

1) quel est le volume global des données des bases ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT CAST(SUM(size) / 131072 AS DECIMAL(16, 3)) AS SIZE_MB FROM sys.master_files WHERE type <> 1;

2) quel est le nombre moyen / maximal de connexion ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(*) FROM sys.dm_exec_connections;

3) l'instance est-elle installée sur une machine virtuelle ou physique ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC xp_readerrorlog 0, 1, N'System Manufacturer:';

4) quel est la quantité de RAM disponible sur la machine et le réglage pour l'instance ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT CAST((1.0 * total_physical_memory_kb / POWER(1024, 2)) AS DECIMAL(16,3)) AS PC_RAM,
       CAST((1.0 * committed_target_kb / POWER(1024, 2)) AS DECIMAL(16,3)) SQL_RAM 
FROM   sys.dm_os_sys_memory
       CROSS JOIN sys.dm_os_sys_info;

5) votre instance a t-elle assez de mémoire ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT system_low_memory_signal_state, system_memory_state_desc FROM sys.dm_os_sys_memory;

6) vos bases sont-elle en verrouillage pessimiste ou optimiste ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT name, database_id, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases WHERE database_id > 4;

Pour information SQL Server peut servir simultanément 32000 connexions...

A +

[mformiga]

Merci pour ce retour très explicite.

Pour la version entreprise, j'étais au courant: cela ne posera pas de soucis.
Par contre, il faudra ue je me renseigne pour les CAL sur le nouveau serveur


Voici les réponses a toutes les requêtes

0-Microsoft SQL Server 2022 (RTM-GDR) (KB5040936) - 16.0.1121.4 (X64)   Jul  2 2024 00:22:34   Copyright (C) 2022 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) 
1-102.000 MB
2-509
3-System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
4-PC_RAM	SQL_RAM
95.999	58.594
5-system_low_memory_signal_state
0
system_memory_state_desc
Available physical memory is high
6-name	database_id	snapshot_isolation_state_desc	is_read_committed_snapshot_on
ERPDIVALTO	5	OFF	0
NED_TEST_219B	6	OFF	0
ERP219C	7	ON	0
ERP219B	10	ON	0

[SQLpro]

Vous avez peu de connexion (500) et un volume de base très petit... moins de 1 Go.

Vous avez 2 bases qui permettent des accès en mode de verrouillage optimiste (ERP219C et ERP219B), ce serait bien d'autoriser les autres à faire de même.

Plutôt que d'utiliser AlwaysOn pour rajouter un nœud en lecture, ce qui va couter cher en licences (un peu plus de 30 000 €...) il vaudrait mieux :
1) autoriser les autres bases à faire du verrouillage optimiste
2) agrandir la base de données tempdb pour accueillir les versions de lignes inhérents à ce mode de verrouillage
3) transformer vos requêtes de reporting et BI en procédures stockées en commençant par la commande

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

4) au besoin, utiliser le gouverneur de ressources pour définir un compte de connexion SQL avec une limitation des ressources (20 % de CPU, 25 % de RAM, 30 % de disque) et passer par ce compte pour les report et la BI

A +

[Michel.Priori]

1) quel est le volume global des données des bases ?
SELECT CAST(SUM(size) / 131072 AS DECIMAL(16, 3)) AS SIZE_MB FROM sys.master_files WHERE type <> 1;

1-102.000 MB

Je pense qu'il y a une erreur d'unité.
La colonne SIZE de la vue sys.master_files est exprimée en nombre de pages de 8Ko.
Il faut 128 pages pour occuper 1Mo ; et 131072 pages pour 1 Go

Selon les valeurs fournies vous avez 4 bases de données utilisateur qui occupent globalement 102 Go.

Même avec cette rectification, une occupation de 58 Go de Ram me parrait "large" ;
A mon humble avis, les fluctuations de compteur "Page Life Expectancy" doit évoluer dans des valeurs hautes.

[Michel.Priori]

Re,

Les indications de Frédéric sont et restent à tester.

Autre idée : tester si un "instantané de base de donnée" ne peux pas répondre à vos besoins de reporting XL.
https://learn.microsoft.com/fr-fr/sq...l-server-ver16

Je sais très bien que les utilisateurs sont rétifs à devoir utiliser des données non à jour.
Cependant j'ai déjà assisté à des conversations de sourds avec des personnes "métier" qui avait 2 versions des données du même tableau.
Ne pas oublier d'ajouter la date de collecte et de fraicheur des donnée

[SQLpro]

Non, ma formule est bonne.....

En Ko (page de 8 K) je multiplie par 8

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT 8 * CAST(SUM(size) AS DECIMAL(16, 3)) AS SIZE_KB FROM sys.master_files WHERE type <> 1;

En Mo je divise par 1024 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT 8 * CAST(SUM(size) AS DECIMAL(16, 3)) / 1024.0  AS SIZE_MB FROM sys.master_files WHERE type <> 1;

En Go je divise par 1024² :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT 8 * CAST(SUM(size) AS DECIMAL(16, 3)) / POWER(1024.0, 2)  AS SIZE_GB FROM sys.master_files WHERE type <> 1;

Pour simplifier 8 * PIOWER(1024.0, 2) = 131072

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT CAST(SUM(size) AS DECIMAL(16, 3)) / 131072.0  AS SIZE_GB FROM sys.master_files WHERE type <> 1;

Bon, faut réviser tes maths !!!!

A +

[Michel.Priori]

Bon, faut réviser tes maths !!!!

Non Frédéric, c'est avec des remarques de ce genre que tu te déprécie et que tu fais fuir des internautes

********************************

Mais puisque que tu me lances, je relève le défi : faisons donc un peu de math.

En Ko (page de 8 K) je multiplie par 8

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT 8 * CAST(SUM(size) AS DECIMAL(16, 3)) AS SIZE_KB FROM sys.master_files WHERE type <> 1;

Si l'unité de [size] est 8Ko et que tu la multiplie par 8, ton unité devient 64 Ko, je n'en vois pas quel est l'intérêt (perso j'aurais plutôt fait une division) mais continuons

En Mo je divise par 1024 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT 8 * CAST(SUM(size) AS DECIMAL(16, 3)) / 1024.0  AS SIZE_MB FROM sys.master_files WHERE type <> 1;

En Go je divise par 1024² :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT 8 * CAST(SUM(size) AS DECIMAL(16, 3)) / POWER(1024.0, 2)  AS SIZE_GB FROM sys.master_files WHERE type <> 1;

Je valide qu'en informatique 1Mo = 1024 Ko (et non pas 1000) et que 1Go= 1024*1024 Ko = 1 048 576 Ko

Mais l'unité de base choisie reste 64 Ko et non pas 1 Ko
Voyons où ça nous mène :

Pour simplifier 8 * PIOWER(1024.0, 2) = 131072

Ben non, erreur de calcul manifeste !
Si 1024*1024 = 1 048 576
Alors 8 fois plus ne peux pas faire 131 072 !

Par contre, sur mon PC, ma calculatrice m'indique que 128*1024 = 131 072
Il faut bien admettre qu'à ce niveau là, tomber sur la même valeur, c'est de la chance, non ?
Continuons :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT CAST(SUM(size) AS DECIMAL(16, 3)) / 131072.0  AS SIZE_GB FROM sys.master_files WHERE type <> 1;

A bien voilà nous y sommes, le résultat est bien exprimé en GB (Giga Byte pour les british et Giga octet pour les frenchi ; notons que Gb= Giga Bit soit 8 fois moins)
A une lettre près c'est la même requête que la première fois

1) quel est le volume global des données des bases ?
SELECT CAST(SUM(size) / 131072 AS DECIMAL(16, 3)) AS SIZE_MB FROM sys.master_files WHERE type <> 1;

Du coup, merci, t'as refait ma soirée.

[SQLpro]

Ouf tu as raison..... Je fatigue (mais je plaisante aussi !)

DONC :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT CAST(SUM(size) AS DECIMAL(16, 3)) / 128.0  AS SIZE_MB FROM sys.master_files WHERE type <> 1;
 
SELECT CAST(SUM(size) AS DECIMAL(16, 3)) / (128 * 1024.0)  AS SIZE_GB FROM sys.master_files WHERE type <> 1;
-- ou
SELECT CAST(SUM(size) AS DECIMAL(16, 3)) / 131072.0  AS SIZE_GB FROM sys.master_files WHERE type <> 1;

[KyoshiroKensei]

Bonjour

Et pourquoi pas une solution plus légère avec un second serveur sql standard hébergeant une réplication transactionnelle du serveur de prod?

[Michel.Priori]

Compliqué de faire "plus léger" qu'avec 1 seule instance et une seule base.
Les 'bons réglages' sont à lire dans la réponse de Frédéric.

[KyoshiroKensei]

Je pensais "Plus léger" en terme de cout.

[SQLpro]

Bonjour

Et pourquoi pas une solution plus légère avec un second serveur sql standard hébergeant une réplication transactionnelle du serveur de prod?

La réplication transactionnelle n'est pas destiné à faire de la haute disponibilité... C'est lourd et complexe.... Cela concerne en général quelques tables. Répliquer en mode transactionnel toutes les tables de votre base nécecessite une puissance de calcul importante donc un serveur costaud et couteux... Plus les licences des deux serveurs... Alors que AlwaysOn ne nécessite que de licencier le serveur actif. pas le passif !

A +

[KyoshiroKensei]

La réplication transactionnelle n'est pas destiné à faire de la haute disponibilité... C'est lourd et complexe.... Cela concerne en général quelques tables. Répliquer en mode transactionnel toutes les tables de votre base nécecessite une puissance de calcul importante donc un serveur costaud et couteux... Plus les licences des deux serveurs... Alors que AlwaysOn ne nécessite que de licencier le serveur actif. pas le passif !

A +

Merci pour l'explication.