Month: April 2017

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’