Add new comment

Import rapide des données entre 2 serveurs SQL

L'import des données est habituel en décisionnel. La méthode ordinaire ETL est exporter les donnes source en fichier CSV puis le transmettre sur le serveur cible (stockage partagé) et enfin importer ce fichier en bloc (bulk insert ou bcp).

Voici une autre méthode "pure SQL" qui vous permet réduire le temps en 2 environ. Les gars SSIS peuvent essayer le reproduire ;)

Environnement

Le serveur SRV1 gère la base de données DW dont une vue "dbo.v_agregat" est la source des données. Cette vue agrège légèrement une table de faits (pas si grand pour ce test, 90M lignes environ * 45 colonnes soit 5 Go environ sera suffisant) en jointure de plusieurs (30 environ) tables des dimensions.

Le serveur SRV2 gère la base de données DM dont une table "dbo.agregat" est la cible pour les données sources.

Supposons que la table cible a la même structure (colonnes et types) que la vue source. Ou en envers, c'est la vue source doit être adaptée aux structure de la table cible.

Préparations

Sur le SRV2 on crée le serveur lié qui point la base de données source DW sur SRV1.

EXEC master.dbo.sp_addlinkedserver @server = 'SRV1_DW', @srvproduct='', @provider='SQLNCLI', @datasrc='SRV1', @catalog='dw'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='SRV1_DW',@useself='False',@locallogin=NULL,@rmtuser=N'dwclient',@rmtpassword='Pa$$w0rd'

Il peut être nécessaire d'ajouter aussi certaines options par la procédure "sp_serveroption".

EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SRV1_DW', @optname=N'remote proc transaction promotion', @optvalue=N'true'

Notez que l'utilisateur "dwclient" doit être créé préalablement sur SRV1 et avoir les droits correspondants pour lire les données de la table source. Par exemple :

USE DW
GO
CREATE LOGIN dwclient WITH PASSWORD=N'Pa$$w0rd', DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
ALTER LOGIN dwclient ENABLE;
GO
CREATE USER dwclient FOR LOGIN dwclient WITH DEFAULT_SCHEMA=dbo;
GO
EXEC sp_addrolemember 'db_datareader', 'dwclient'

Importer les données

Depuis la version 2008 SQL Server propose une méthode d'insertion des données avec le minimum de journalisation (logging). Cela veut dire, votre INSERT-SELECT produit quasiment la même journalisation que INSERT BULK. Par contre, il y a des contraintes :

  • la table cible doit être vide
  • la table cible doit être le segment de mémoire (heap). Cela veut dire, la table ne doit avoir l'index cluster
  • l'option TABLOCK doit être appliquée pour la table cible
  • le mode de récupération de la base de données doit être "simple" ou "bulk-logged"

En plus, si la table n'est pas vide il est toujours possible insérer des lignes avec minimum de journalisation. Dans ce cas les contraintes supplémentaires et contradictoires un peu sont :

  • activer le flag de traçage 610 au début d'insertion
  • la table cible doit avoir l'index cluster
  • les données insérées doivent être ordonnées selon cette index cluster

Alors, notre code TSQL pour importer les données dans la table vide peut être comme celui-ci :

-- supprimer tous les index
DROP INDEX ...
-- désactiver temporairement les contraintes
ALTER TABLE dbo.agregat NOCHECK CONSTRAINT ALL;
 
INSERT INTO dbo.agregat WITH (TABLOCK)
(
  -- liste des colonnes cibles
)
SELECT
  -- liste des colonnes sources
FROM SRV1_DW.DW.dbo.v_agregat;
-- recréer les index
-- activer les contraintes sans vérification

Si la table n'est pas vide ou vous ne voulez pas supprimer et reconstruire l'index cluster, rajoutez l'ordonnance correspondant au index cluster et changer légèrement le scénario :

-- supprimer tous les index non-cluster
-- désactiver temporairement les contraintes
DBCC TRACEON(610);
INSERT INTO dbo.agregat WITH (TABLOCK)
(
  -- liste des colonnes cibles
)
SELECT
  -- liste des colonnes sources
FROM SRV1_DW.DW.dbo.v_agregat
ORDER BY
  -- liste des colonnes d'index cluster
DBCC TRACEOFF(610);
-- activer les contraintes sans vérification
-- recréer les index non-cluster

Voilà. Selon les tests, le temps d'import peut être amélioré jusqu'au 100% par rapport du schéma "classique" ETL : export (bcp) - transfert des fichiers - import (bulk). Les raisons principales sont :

  • l'insertion commence de que les premières lignes arrives de source
  • il n'y a pas des écritures et lectures des fichiers supplémentaires

En plus, il n'y a qu'un seul étape au lieu de 3 ce que simplifie l'administration.

Si vous êtes condamnez à mort utiliser SSIS vous pouvez cependant "envelopper" cette technique dans un package et voir votre patron content en étant dans l'ignorance.