|
|
|
|
|
|
|
|
|
|
|
|
|
|
13.08.2010
IV. Things an IT specialist should know about MSSQL Server
21. configurarea instanţei MSSQL - memorie, priority boost, etc;
22. PowerShell & MSSQL;
23. utilizarea scheduled task-urilor (alternativă la SQL Server Agent);
24. Java & MSSQL;
Configurarea instanţei MSSQL - memorie, priority boost, etc.
Înainte de a începe efectiv configurarea unei instanțe MSSQL, trebuie să cunoaștem ce opțiuni pot fi configurate și care sunt setările curente. Acest lucru se poate realiza prin intermediul sys.configurations.
Schimbarea setărilor curente se poate realiza prin intermediul procedurii stocate sp_configure.
Prin intermediul sp_configure specificăm noile setări, aplicarea lor efectivă se va face numai după apelarea RECONFIGURE (în unele cazuri urmată și de o repornire a instanței MSSQL).
În cazul în care se dorește listarea inclusiv a opțiunilor avansate, trebuie să schimbăm valoarea implicită a opțiunii show advanced option.
Următoarele apeluri ale procedurii stocate sp_configure vor returna inclusiv setările opțiunilor avansate.
Setările implicite au fost stabilite pe baza celor mai frecvente implementări MSSQL. Este recomandat a interveni asupra acestor setări numai după ce se efectuează o analiză a respectivei instanțe MSSQL.
De când lucrez cu MSSQL, am intervenit doar asupra următoarelor opțiuni:
priority boost – această opțiune presupune schimbarea priorității alocate procesului instanței MSSQL.
Este important a schimba setarea implicită a priority boost în cazul în care pe același server sunt găzduite și alte aplicații (ex: aplicații web, antivirus, etc.). Accesul la baza de date este esențial, iar în cazul în care procesul instanței MSSQL concurează cu alte procese, este foarte probabil ca timpii de răspuns ai SGBD-ului să fie din ce în ce mai mari. Cei care au administrat servere SharePoint (toate rolurile instalate pe o singură mașină) cu siguranță s-au lovit de mesajul de eroare „Unable to connect to database. Check database connection information and make sure the database server is running.” – deși instanța MSSQL nu prezenta probleme. Acestă situație se datorează faptului că, implicit, atât procesele w3wp.exe cât și sqlservr.exe au același nivel de prioritate (Normal).
În cazul priority boost, după configurare este necesar a reporni instanța MSSQL.
Din acest moment, procesul sqlservr.exe (asociat instanței MSSQL) va avea prioritatea High (prioritate crescută comparativ cu alte procese), deci, timpi de răspuns cu mult îmbunătățiți.
min server memory & max server memory – recomand intervenția asupra acestor opțiuni indiferent de configurație. MSSQL începe să aloce memorie pe măsură ce crește gradul de utilizare al SGBD-ului, iar această memorie nu este prea curând returnată sistemului de operare. Un alt motiv pentru care trebuie intervenit asupra acestor opțiuni este acela de a optimiza accesul la resurse, în special atunci când pe același server sunt găzduite și alte aplicații, ori mai multe instanțe MSSQL. Setările implicite permit utilizarea până la max. 2147483647 megabytes – deci, în cam toate cazurile MSSQL este liber să folosească în întregime resursele sistemului.
Microsoft oferă câteva sfaturi referitoare la modul de calcul și identificare a necesarului de memorie ce trebuie alocat unei instanțe MSSQL. În practică eu încerc să identific maximul utilizării resurselor prin intermediul Performance Logs and Alerts, iar pe baza logurilor extrase (pe parcursul unei zile de producție) stabilesc necesarul de memorie.
De reținut faptul că dacă sunt specificate valorile de minim / maxim, instanța MSSQL se va încadra în specificațiile date doar dacă nivelul de utilizare al SGBD-ului va cere acest lucru și doar dacă punctele de minim / maxim sunt atinse, altfel, instanța va folosi doar minimul necesar de resurse.
PowerShell & MSSQL
Managementul unei instanțe MSSQL poate fi realizat și prin intermediul PowerShell. Cred că va fi necesar a scrie un post dedicat pentru a putea acoperi acest subiect, dar în cadrul acestui articol am să fac o scurtă introducere.
Snap-in-urile pentru PowerShell sunt instalate și înregistrate odată cu instalarea SQL Server Management Studio.
Unde sunt găzduite snap-in-urile?
Cum încărcăm aceste snap-in-uri în PowerShell?
sqlps este utilitarul care pornește PowerShell cu snap-in-urile gata încărcate. Utilitarul poate fi apelat din consolă, sau încărcat direct din directorul unde a fost instalat SQL Server Management Studio (conform instalărilor prezentate în acest articol - C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe).
Cele mai frecvente comenzi:
-
Set-Location (cd);
-
Get-ChildItem (dir);
-
Get-Item;
-
Get-Location (pwd);
-
Get-Member;
Exemplu de conectare la instanța implicită.
Exemplu de conectare la instanța SQLEXPRESS.
Listarea tabelelor bazei de date COMANDA.
Reindexarea indecșilor tabelei Agent.
Cum de am știut de metoda RebuildIndexes? Prin intermediul Get-Member am listat membrii obiectului $Agent_table. Dacă se doresc detalii suplimentare, se poate accesa documentația MSDN.
Generarea codului pe baza căruia tabela LinieComanda poate fi recreată.
Apelarea comenzilor T-SQL se va realiza prin intermediul Invoke-Sqlcmd.
Am să închei această scurtă introducere legată de PowerShell & MSSQL prin a menționa existența SQLPSX (poate fi descărcat de pe codeplex.com). Consider SQLPSX ca fiind necesar în cazul în care managementul instanțelor MSSQL se realizează prin intermediul PowerShell.
Într-un viitor articol PowerShell & MSSQL am să discut detaliat și despre SQLPSX.
Utilizarea scheduled task-urilor (alternativă la SQL Server Agent)
În prima parte a Things an IT specialist should know about MSSQL, am afirmat că serviciul windows SQL Server Agent este instalat odată cu instanța MSSQL (principalul său scop fiind acela de a executa diverse activităţi de mentenanţă), dar în cazul edițiilor EXPRESS, din cauza limitărilor impuse, acest serviciu nu poate fi pornit.
Alternativa la SQL Server Agent ar fi utilizarea scheduled task-urilor.
Ex: realizarea unui scheduled task care să efectueze (în fiecare duminică, ora 10:30 PM) o copie de siguranță în întregime (eng. full backup) a bazei de date COMANDA.
Tot ce trebuie să facem este să programăm execuția scriptului ThingsAnITSpecShouldKnowTSQL54.sql. Recomand a salva output-ul execuțiilor într-un fișier .txt. Deorece parametrul -o al SQLCMD rescrie fișierul de output la fiecare apelare, am să apelez utilitarul SQLCMD dintr-un fișier .bat.
Pentru a nu salva în clar userul și parola de conectare la MSSQL, mă voi folosi de Windows authentication mode.
Java & MSSQL
Cei care fac dezvoltare cu Java & MSSQL au nevoie de Microsoft SQL Server JDBC Driver 3.0 (mai multe detalii despre driver puteţi găsi pe blogul echipei JDBC).
Am să fac o scurtă prezentare pentru dezvoltatorii Java care nu au mai lucrat cu MSSQL (scopul fiind acela de a oferi detalii privind configurarea mediului de lucru).
1. Se descarcă şi dezarhivează Microsoft SQL Server JDBC Driver 3.0.
2. Folosind NetBeans se creează un proiect de tip Java Application.
3. În directorul proiectului JMSSQLApp01 se copiază sqljdbc4.jar şi sqljdbc_auth.dll.
Diferenţa dintre sqljdbc.jar şi sqljdbc4.jar
În cazul în care autentificarea la instanţa MSSQL este de tip Windows authentication mode, sqljdbc_auth.dll este necesar a fi prezent în locaţia de unde are loc apelarea .jar-ului (eng. working directory).
4. Se încarcă JDBC-ul.
JMSSQLApp01
Atenţie!!! Implicit NetBeans working directory este directorul proiectului JMSSQLApp01 (director în care se află sqljdbc_auth.dll).
În cazul în care se va distribui JMSSQLApp01, directorul dist\ este cel care conţine .jar-ul precum şi pachetele adiţionale. La final copiem sqljdbc_auth.dll în directorul unde a avut loc împachetarea (dist\) – pentru a fi siguri că distribuim JMSSQLApp01 împreună cu fişierele ce compun JDBC-ul.
PHP & MSSQL
În cazul PHP & MSSQL va fi nevoie a descărca şi instala Microsoft Drivers for PHP for SQL Server (mai multe detalii despre driver puteţi găsi pe blogul echipei de dezvoltare).
Ca şi în cazul Java & MSSQL am să ofer detalii privind configurarea mediului de lucru (IIS 7.0 & PHP).
1. Se descarcă şi dezarhivează Microsoft Drivers for PHP for SQL Server.
2. În cazul de faţă, mediul de test găzduieşte IIS 7.0 & PHP 5.3.3 (non thread safe version, compiled with Visual C++ 9.0), aşadar în directorul C:\php_5.3.3\ext au fost copiate fişierele php_sqlsrv_53_nts_vc9.dll & php_pdo_sqlsrv_53_nts_vc9.dll.
3. Prin intermediul php.ini configurăm mediul PHP să încarce cele două extensii.
01.08.2010
III. Things an IT specialist should know about MSSQL Server
Listarea pricipalilor parametri ai instanței MSSQL
Listarea principalilor parametri ai unei instanțe MSSQL poate fi efectuată prin intermediul sys.servers, sp_server_info, sys.configurations.
Pe baza valorilor acestor parametri putem să înțelegem configurația instanței MSSQL.
Prin intermediul sp_databases și sys.databases putem vizualiza bazele de date găzduite de o instanță MSSQL. sp_databases și sys.databases sunt esențiale unui IT specialist atunci când managementul unei instanțe MSSQL se face în lipsa SQL Server Management Studio.
Listarea fișierelor bazelor de date
În prima parte a acestui articol am menționat că o baza de date este compusă din mai multe fișiere. Prin intermediul sys.master_files și sys.database_files sunt returnate serii de date referitoare la fișierele bazelor de date.
În cazul în care managementul unei instanțe MSSQL se realizează în linie de comandă, sys.sp_tables și sys.tables sunt folosite pentru obținerea de date referitoare la tabelele unei baze de date.
sys.sp_columns și sys.columns sunt folosite în cazul în care se dorește listarea coloanelor ce compun un anume tabel a bazei de date.
Ce sunt indecșii?
Listarea indecșilor se poate realiza prin intermediul sys.indexes.
Listarea declanșatorilor (eng. triggers)
Ce sunt triggeri (declanșatorii)?
Listarea declanșatorilor precum și alte date referitoare lor pot fi obținute prin intermediul sys.triggers, sys.trigger_events, sys.sql_modules.
Listarea procedurilor stocate
Ce sunt procedurile stocate?
sys.sql_modules, sys.objects
Ce sunt UDF-urile?
sys.sql_modules, sys.objects
Ștergerea bazelor de date
DROP DATABASE
Pentru a putea efectua ștergerea unei baze de date, trebuiesc închise toate conexiunile deschise cu respectiva bază de date.
În producție vă puteți folosi de ThingsAnITSpecShouldKnowTSQL32.sql (atribuiți variabilei @DatabaseToBeDeleted denumirea bazei de date ce se dorește a fi ștearsă).
Ataşarea / deconectarea unei baze de date
sp_detach_db
Sunt situații când se dorește deconectarea unei baze de date de la o instanță MSSQL (spre deosebire de operațiunea de ștergere, fișierele bazei de date vor rămâne stocate pe disc). Pentru a putea efectua deconectarea unei baze de date trebuiesc închise toate conexiunile cu respectiva bază de date.
În producție vă puteți folosi de ThingsAnITSpecShouldKnowTSQL33.sql (atribuiți variabilei @DatabaseToBeDetached denumirea bazei de date ce se dorește a fi deconectată de la instanța MSSQL).
CREATE DATABASE
Verificarea integrităţii unei baze de date
Sunt rare cazurile în care apar probleme legate de integritatea bazelor de date MSSQL. Microsoft recomandă ca în astfel de cazuri să se efectueze o restaurare a bazei date. DBCC CHECKDB este recomandat a fi folosit ca ultimă soluție în cazul în care se dorește repararea bazei de date.
Simpla apelare a lui DBCC CHECKDB presupune doar o verificare a integrității bazei de date (informația relevantă poate fi găsită pe ultima linie din raportul generat). În cazul în care se dorește repararea bazei de date, acest lucru poate fi făcut prin apelarea DBCC CHECKDB cu una din opțiunile REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD (în prealabil baza de date trebuie trecută în modul single-user).
Reindexarea și defragmentarea indecșilor
Indecșii sunt obiecte ale bazelor de date ale căror scop este acela de a facilita accesul rapid la date. Fragmentarea indecșilor este întâlnită atunci când sunt aduse modificări repetate asupra datelor. În cazul în care un index este fragmentat, timpii de interogare și consumul de resurse încep să crească. Tocmai de aceea, se recomandă ca la intervale regulate de timp (în funcție de modul în care o bază de date este exploatată) să se efectueze reindexarea sau defragmentarea a indecșilor.
În cazul reindexării, indexul va fi recreat. Acest lucru va determina (doar pe parcursul operațiunii de reindexare) ca interogările să nu se poată folosi de indexul respectiv (limitare ce poate fi evitată în cazul edițiilor Developer și Enterprise).
În cazul defragmentării, reorganizarea indexului are loc fără ca aceasta să fie recreat, deci interogările (efectuate pe parcursul operațiunii de defragmentare) vor fi foarte puțin afectate. Spre deosebire de reindexare, procesul de defragmentare este mai lent.
Vizualizarea fragmentării indecșilor se poate realiza prin intermediul următoarei fraze SELECT-SQL
ALTER INDEX REBUILD
Reindexarea unui index.
ALTER INDEX REORGANIZE
Defragmentarea unui index.
Digg and favorite queries
ThingsAnITSpecShouldKnowTSQL39.sql
Realizarea copiilor de siguranță, precum și restaurarea bazelor de date sunt operațiuni obligatorii atunci când vorbim de SGBD-uri (eng. DBMS). În prezența SQL Server Management Studio, aceste operațiuni se pot realiza foarte ușor. Totuși, sunt situații când acest lucru se poate realiza doar în linie de comandă.
Un exemplu ar fi automatizarea procesului de backup / restore în cazul unei ediții EXPRESS. Așa cum am menționat și în prima parte a Things an IT specialist should know about MSSQL, serviciul windows SQL Server Agent este instalat odată cu instanța MSSQL (principalul său scop fiind acela de a executa diverse activităţi de mentenanţă), dar în cazul edițiilor EXPRESS, din cauza limitărilor impuse, acest serviciu nu poate fi pornit.
Alternativa la SQL Server Agent ar fi utilizarea scheduled task-urilor, dar presupune cunoașterea comenzilor T-SQL.
Realizarea copiilor de siguranță, sau restaurarea bazelor de date presupune implicit existența unei strategii backup & restore. Nu îmi propun a discuta acest subiect în acest post, dar am să menționez câteva aspecte importante ce trebuiesc luate în calcul atunci când o astfel de strategie este creată:
-
Identificați bazele de date pentru care trebuiesc realizate copii de siguranță.
-
Stabiliți frecvența cu care copiile de siguranță trebuie realizate. De obicei răspunsul îl obținem pe baza întrebărilor „Cât ești dispus să pierzi? Esti dispus să pierzi datele bazei de date de acum X minute? Dar de acum X ore / zile?”.
Datele unei baze de date sunt rezultatul unor intrări: ex: operatorul unei aplicații de contabilitate, un proces automatizat de monitorizare și control a producției, etc. Aceste intrări în baza de date au un cost (un echivalent în bani). În cazul pierderilor de date din baza de date, aceste costuri pot fi calculate: costurile salariale ale contabilului / interval de timp, costurile suferite cu reverificarea producției / interval de timp, eventual chiar și pierderile suferite din cauza opririi procesului de producție pe perioada verificărilor, etc.
-
Stabiliți intervalul de timp maximum admis în care bazele de date trebuiesc restaurate.
-
Stabiliți locația unde copiile de siguranță vor fi păstrate. „Copiile de siguranță vor fi salvate pe discurile locale, discuri externe, în rețea? Doriți a efectua copii ale copiilor de siguranță?”
-
Stabiliți o metodă de restricționare a accesului la copiile de siguranță.
-
Stabiliți intervalul de timp pentru care copiile de siguranță trebuiesc păstrate / arhivate.
-
Stabiliți modul de realizare a proceselor de backup / restore. Procesele vor fi automatizate?
-
Stabiliți o modalitate de verificare a proceselor de backup / restore.
-
La perioade de timp regulate, reverificați procedurile de backup & restore. În mediul de test testați corectitudinea lor.
-
etc.
Tipuri de backup MSSQL:
-
full backup: se realizează o copie în întregime a bazei de date. Toate celelalte tipuri de backup se bazează pe existența unei astfel de copii de siguranță. Acest tip de backup se realizează cu baza de date activă (fiind permis accesul și eventualele modificări) și poate fi efectuat indiferent de tipul de model de restaurare (eng: database recovery model: SIMPLE, BULK_LOGGED, FULL).
-
differential backup: realizează o copie de siguranță doar a modificărilor apărute de la ultimul full backup. Acest tip de backup se realizează online și poate fi efectuat indiferent de tipul de model de restaurare (SIMPLE, BULK_LOGGED, FULL).
-
transaction log backup: efectuează o copie de siguranță de la ultimul full backup, differential backup sau transaction log backup. Operația de backup se realizează online, nu este atât de consumatoare de resurse precum copia în întregime a unei baze de date, și se poate aplica doar asupra bazelor de date ale căror model de restaurare este BULK_LOGGED sau FULL.
BACKUP DATABASE
Exemplu full backup.
BACKUP DATABASE
Exemplu differential backup.
BACKUP DATABASE
Exemplu transaction log backup.
RESTORE LABELONLY
RESTORE HEADERONLY
RESTORE FILELISTONLY
RESTORE DATABASE
Exemplu restaurare bază date pe baza copiei de siguranță full.
RESTORE DATABASE
Exemplu restaurare bază date pe baza copiilor de siguranță full și differential.
RESTORE DATABASE
Exemplu restaurare bază date pe baza copiilor de siguranță full, differential și transaction log.
Să presupunem că baza de date Comanda este configurată în modul de restaurare FULL, iar realizarea copiilor de siguranță se realizează conform secveței:
|
Zi |
Ora |
Tip backup |
|
Luni |
05:00 AM |
Differential backup |
|
11:00 AM |
Transaction log backup |
|
04:30 PM |
Transaction log backup |
|
10:30 PM |
Transaction log backup |
|
Marți |
05:00 AM |
Differential backup |
|
11:00 AM |
Transaction log backup |
|
04:30 PM |
Transaction log backup |
|
10:30 PM |
Transaction log backup |
|
Miercuri |
05:00 AM |
Differential backup |
|
11:00 AM |
Transaction log backup |
|
04:30 PM |
Transaction log backup |
|
10:30 PM |
Transaction log backup |
|
Joi |
05:00 AM |
Differential backup |
|
11:00 AM |
Transaction log backup |
|
04:30 PM |
Transaction log backup |
|
10:30 PM |
Transaction log backup |
|
Vineri |
05:00 AM |
Differential backup |
|
11:00 AM |
Transaction log backup |
|
04:30 PM |
Transaction log backup |
|
10:30 PM |
Transaction log backup |
|
Sâmbătă |
05:00 AM |
Differential backup |
|
Duminică |
05:00 AM |
Differential backup |
|
10:30 PM |
Full backup |
Se dorește restaurarea bazei de date în starea de Joi ora 04:30 PM. În acest caz, secvența de restaurare va fi:
- restore pe baza backup-ului de Duminică ora 10:30 PM (full);
- restore pe baza backup-ului de Joi ora 05:00 AM (differential);
- restore pe baza backup-ului de Joi ora 11:00 AM (transaction log);
- restore pe baza backup-ului de Joi ora 04:30 PM (transaction log);
Se dorește restaurarea bazei de date în starea de Sâmbătă ora 05:00 AM. Secvența de restaurare va fi:
- restore pe baza backup-ului de Duminică ora 10:30 PM (full);
- restore pe baza backup-ului de Sâmbătă ora 05:00 AM (differential);
COPY_ONLY
Opțiunea COPY_ONLY are rolul de a nu afecta secvența copiilor de siguranță. Spre exemplu, copiile de siguranță differential și transaction log depind de copia de siguranță full.
ThingsAnITSpecShouldKnowTSQL49.sql
Pe baza exemplului anterior menționat, să presupunem că Joi ora 01:00 PM este necesar a realiza un full backup asupra bazei de date Comanda (pentru a avea o copie în mediul de test).
În cazul în care Joi ora 01:00 PM am efectua o copie full a bazei de date fără a specifica opțiunea COPY_ONLY, toate celelalte copii de siguranță (Joi ora 04:30 PM Transaction log backup, Joi ora 10:30 PM Transaction log backup, Vineri ora 05:00 AM Differential backup, etc.) vor depinde de full backup-ul efectuat Joi ora 01:00 PM!!! Deci ștergerea copiei de siguranță efectuată Joi ora 01:00 PM ar compromite toate celelalte copii de siguranță (differential și transaction log).
În schimb, dacă Joi ora 01:00 PM am efectua o copie full a bazei de date specificând opțiunea COPY_ONLY, toate celelalte copii de siguranță vor depinde de full backup-ul efectuat Duminică ora 10:30 PM.
Ca și măsură de siguranță, recomand folosirea opțiunii COPY_ONLY, atunci când:
-
nu dețineți prea multe informații referitoare la bazele de date ale unei instanțe MSSQL;
-
-
doriți a avea o copie a bazei de date pe o altă mașină, dar nu doriți afectarea secvențelor copiilor de siguranță;
-
etc.
STRIPPING BAKCUPS
Presupune împarțirea fișierului de backup în mai multe fișiere (scrise în paralel) și oferă un plus de performanță în ceea ce privește copiile de siguranță efectuate asupra bazelor de date de mari dimensiuni. Atât backup-ul cât și restore-ul trebuie făcut menționând toate fișierele ce compun copia de siguranță (se acceptă împărțirea până la maximum 64 de fișiere).
ThingsAnITSpecShouldKnowTSQL50.sql
|
|
|
|
|
|
|
|
 |
|
|
|
|