Student te TCT pranuar ne MIT

Per te vleresuar nje qender trajnimesh si TCT, ku objektivi kryesor eshte te merren njohuri profesionale ne cdo fushe te teknologjise, duhen konsideruar dy komponente: puna madhore qe behet nga instruktoret per te gjithe studentet, dhe padyshim vete studentet. Jane ata, qe me deshiren e mire per te marre njohuri te reja dhe per te ecur me te njejtin hap me teknologjine e diteve te sotme, na  ndihmojne me vazhdueshmerine e punes sone te perditshme.
Dhe ja ku permendim nje nga studentet tona aktuale ne kursin “Java Programmer” Fjona Parllaku. Maturante e shkolles “Turgut Ozal Tirane”, studente e Universitetit Epoka, sapo eshte pranuar me burse ne Universitetin nr. 1 ne bote, MIT (Massachusetts Institute of Technology).
Per me teper, mund te lexoni ne linkun e meposhtem:

Trajnim: Investigim problemesh databaze – programim

Kur ndodh nje problem IT duhet te jete i qete dhe i perqendruar ne problemin e ndodhur.

ANALIZA – Hapat qe ndiqen:

  1. Koha e problemit?
  2. Ambjenti i problemit?
  3. Nr i perdoruesve qe i ka ndodh ky problem?
  4. Kur ishte hera fundit qe ka funksionuar ne menyre te sakte?
  5. Cfare ka ndryshuar ne sistem para se te ndodhte ky problem?
  6. Si lidhet ky problem me sherbime te tjera periferale?

ZGJIDHJA (me disa hapa)

TESTIMI (me disa raste)

DOKUMENTIMI

Trajnim databaze – troubleshooting

Problemet e lidhura me: 1) Service, 2) Network, 3) Login

*Kontrollohet:

  1. A eshte SQL Service i ndezur si sherbim
    1. Master DB Corrupted?
    2. Model DB Corrupted?
    3. TempDB path not found?
  2. nslookup (name resolution) te SQL Server instance
  3. Ping Server IP
  4. A logohesh ne windows server ku eshte instalu SQL Server per te eleminuar Network-un. (Ketu logohesh me Shared Memory Protocol)
  5. A eshte TCP/IP (ose Named Pipes) Enabled ne SQL Server
  6. A duhet qe ky login te futet ne SQL Server?
    1. A eshte bere locked out?
    2. A ka permissions to connect?
  7. Nese autentikimi behet me Domain Controller a mund te lidhet SQL Server me Domain Controller?
  8. PER CDO GJE KONSULTOHU ME SQL SERVER LOG DHE WINDOWS SYSTEM LOG.

Database backups

–full backup (overwrite backupset, pa formatuar media set, me emer)

BACKUP DATABASE [testxml]
TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\testxml.bak’
WITH NOFORMAT, INIT, NAME = N’testxml-Full Database Backup’
GO

–differential backup (me append, me emer, dhe pa formatuar media set)

BACKUP DATABASE [testxml]
TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\testxml.bak’
WITH differential, NOFORMAT, NOINIT, NAME = N’testxml-Diff Database Backup’
GO

–Log backup (me append, me emer, dhe pa formatuar media set)

BACKUP LOG [testxml]
TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\testxml.bak’
WITH NOFORMAT, NOINIT, NAME = N’testxml-log Database Backup 2′
GO

**Vetem T-Log backup ben shkurtimin e LOG File, asnje backup tjeter.

**DBCC Shrinkfile rekomandohet te kryhet vetem mbas nje full backup.

databaze media set informacion

Per te pare informacionin ne nje mediaset ( bak file) e cila mund te kete nje ose me shume backup sets (backups) perdorim queries te meposhtem.

per media set-in

restore labelonly from disk=’C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\testxml.bak’

per backup sets

restore headeronly from disk=’C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\testxml.bak’

per files qe jane bere backup nga ky media set

restore filelistonly from disk=’C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\testxml.bak’

Encrypt Database – create certificate

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘test’;

go

CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My test DEK Certificate’;

go

USE testxml;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO

ALTER DATABASE testxml

SET ENCRYPTION ON;

GO

backup certificate MyServerCert to file=’C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_cert’;

Go

ALTER DATABASE testxml

SET ENCRYPTION OFF;

GO

SQL Login mismatch – not logohet mbasi ke bere nje backup -restore

use your_db

go

Per te kontrolluar users jetim (orphan)

exec sp_change_users_login ‘Report’

Per te lidhur nje user me nje login ekzistues mbasi nje backup/restore

exec sp_change_users_login ‘Update_One’, ‘myUser’,’mylogin’

Per te lidhur nje user me nje login nqs ekziston edhe nqs nuk ekziston ( e krijon on the fly)

exec sp_change_users_login ‘Auto_Fix’, ‘myUser’,’mylogin’,’Pa$$word’

Pasword e vendos vetem nese doni qe login te krijohet.

NQS deshironi te krijoni LOGIN nga e para dhe t’a lidhni vete atehere:

create login MyUserLogin with password =’Pa$$w0rd’, SID=’0x12345678987655444′

SID duhet t’a merrni nga SID i user-it nga komanda

exec sp_change_users_login ‘Report’