Replicarea de tip "merge" folosind date partitionate - concluzii despre performanta

Un scenariu des intalnit de replicare de baze de date de tip „merge” este acela in care un server central SQL Server publica o baza de date catre un numar mare de abonati mobili (ex: PDA-uri cu SQL Server Mobile).

Pentru a raspunde la anumite constrangeri fizice (lipsa de spatiu de stocare pe dispozitivul mobil, banda de retea redusa la conectarea cu serverul central, etc) dar si la eventualele constrangeri de securitate (daca se vrea de exemplu ca fiecare utilizator mobil sa aiba acces doar la datele de care are nevoie, etc) se pot folosi filtre de replicare.

Filtrele de replicare sunt de doua feluri – statice sau dinamice; cele statice filtreaza datele orizontal sau vertical pentru toti abonatii – nu fac diferentierea intre dispozitivele mobile si practic permit excluderea din baza de date replicata a unor linii sau coloane.

Filtrele dinamice permit in schimb particularizarea setului de date in functie de utilizatorul mobil: exista filtre dinamice parametrizate (care folosesc functiile SUSER_SNAME() sau HOST_NAME() pentru a identifica PDAul sau userul care se conecteaza la server) si filtre dinamice de tip join (care stabilesc ce date si din ce tabele vor ajunge la un anumit PDA).

Spre exemplu, in Tabela1 in care exista o coloana NumePDA se poate adauga un filtru parametrizat de tipul: NumePDA = HOST_NAME() ceea ce permite ca la conectarea unui PDA ce are numele „PDA1” acesta sa primeasca doar randurile din tabela care au valoarea PDA1 in coloana NumePDA.

Apoi, se pot defini filtre de tip join care sa lege aceasta tabela cu altele din baza de date, spre exemplu Tabela1.ClientID = Tabela2.ClientID;

In urma folosirii acestor filtre, atunci cand PDAul cu numele PDA1 se conecteaza la serverul central pentru a se sincroniza el va avea acces doar la randurile din Tabela1 care au NumePDA = PDA1 si la cele din Tabela2 care indeplinesc conditia din filtrul join: Tabela1.ClientID = Tabela2.ClientID

Tabela1:

Tabela2:

Evident, in practica filtrele sunt mult mai complexe, in functie de nevoile de business ale clientilor, ceea ce poate duce la penalizari de performanta la momentul sincronizarii.

Ce se intampla in momentul sincronizarii?

In mod traditional, in SQL Server 2000, la momentul sincronizarii are loc un proces de evaluare a partitiilor care folsoeste functia sp_MSsetupbelongs pentru a determina ce randuri ii corespund fiecarui abonat. Acest lucru presupune parcurgerea tabelelor sistem folosite de replicare si identificarea randurilor care apartin de abonatul care s-a conectat la server.

Incepand cu SQL Server 2005, inca din momentul in care un anumit rand a fost modificat pe serverul central, el se va adauga partitiei corespunzatoare abonatului / abonatilor de care apartine; astfel, se evita calcularea partitiilor si se reduc timpii de sincronizare. Acest sistem (numit partitionare precalculata) este implicit in SQL Server 2005, dar in cazul in care anumite conditii de specificare ale filtrelor nu sunt indeplinite, el nu poate fi folosit si se recurge la metoda de evaluare la momentul sincronizarii.

Articolul https://msdn.microsoft.com/en-us/library/ms151737(SQL.90).aspx explica pe larg in ce conditii nu se pot folosi partitiile precalculate, dar in general acest lucru se intampla cand in filtrul parametrizat nu apar functiile SUSER_SNAME() sau HOST_NAME() explicit, ci functii definite de utilizator (care fac apel la SUSER_SNAME() sau HOST_NAME() ).

In acest caz, la sincronizare se efectueaza procesul de evaluare a partitiilor, iar asta poate dura mult in cazul in care mai multi abonati se conecteaza in acelasi timp, pe serverul central exista foarte multe modificari ale datelor si/sau logica de filtrare este complexa.

Pentru a incerca sa reducem timpul de sincronizare, exista mai multe posibilitati:

1. Reducerea numarului de agenti ce se conecteaza in acelasi timp (daca este posibil);

2. Verificarea fragmentarii pt tabelele sistem folosite in procesul de eveluare a partitiilor -> de obicei este bine sa rulam un script de defragmentare pe toate tabelele sistem folosite de replicare, pentru ca acestea se modifica foarte mult atunci cand se executa operatii pe tabelele utilizator;

3. Modificarea logicii de filtrare (filtrele de tip parametrizat sau cele join) -> acesta este probabil punctul cel mai complicat pentru ca trebuie tinut seama de cerintele de functionare ale aplicatiei. Unele solutii sunt:

- folosirea SUSER_SNAME() si HOST_NAME() doar in filtrele parametrizate, de prefrinta explicit; aceste functii se pot folosi si in filtrele join sau chiar in cele statice, dar acest lucru este nerecomandat;

- indexarea coloanelor ce apar in filtrele join;

- daca una din coloanele ce apar intr-un filtru join au atributul UNIQUE, se seteaza optiunea join unique key pt filtrul respectiv; astfel, se evita cautarea intr-o tabela din momentul in care o valoare corespunzatoare filtrului a fost gasita;

- in cazul (foarte des intalnit) in care in definitia filtrului apar functii nondeterministe (ca de exemplu GETDATE() ) este recomandat sa adaugam o coloana de tip bit in tabela replicata care sa indice daca un rand trebuie sa nu sa fie replicat. Modificarea bitului se poate face printr-un job SQL Server care va parcurge toata tabela si va marca radnurile ce trebuiesc replicate, iar conditia din filtru se va modifica astfel incat sa referntieze aceasta coloana de tip bit; astfel se evita pe de o parte probleme de performanta, dar si o functionare defectuoasa a aplicatiei: daca in filtru avem conditia WHERE data > (GETDATE() – 1) asta presupune ca ne asteptam ca la urmatoarea rulare a agentului de replicare liniile cu data posterioara datei de ieri sa fie prelucrate de agentul de replicare. In realitate, agentul de replicare va prelucra doar randurile care au fost modificate de la ultima sa rulare, deci va ignora randurile care corespund conditiei din filtru, dar nu s-au modificat! In acest caz, nu s-a schimbat nicio valoare in tabela. Solutia este sa folosim o coloana nou de tip bit care sa fie initializata cu 0, iar in momentul in care detectam ca a trecut o zi, sa modificam valoarea bit-ului in 1; noul filtru de replicare va fi bazat pe aceasta coloana noua: WHERE bit = 1. Articolul https://msdn.microsoft.com/en-us/library/ms365153(SQL.90).aspx da mai multe detalii despre aceasta solutie.

Andreea Vasilescu