Kto usunął tabele w bazie???

W tej części pokażę jak sprawdzić kto i kiedy usunął nam tabalę z bazy danychy
Na początek utworzymy sobie testową tabelę w naszej bazie

USE HURTOWNIA
GO
CREATE TABLE TestTable (ID INT)
GO
ALTER TABLE TestTable
ADD FirstCol VARCHAR(100)
GO

Commands completed successfully.

Następnie ją usuwamy

DROP TABLE TestTable
GO

Commands completed successfully.

Teraz przyszła pora na śledztwo kto jest winny zniknięcia tabeli w bazie Hurtownia. Uruchamiamy Raport.

Jak widać w raporcie widać kto stworzył tabelę i kto ją usunął 😉

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

SSIS – Loopy czyli pętle SSIS-owe

Poniżej opiszę wykorzystanie komponentu wykonującego proste pętle w naszych paczkach SSIS.
Na początek umieszczamy w naszym nowym projekcie komponent LOOP oraz umieszczamy w nim „Execute SQL Task”. Tworzymy też nowe dwie zmienne potrzebne do działania naszej „pętli”.

Zmienna COUNT to liczba powtórzeń a INC kolejna liczba która będzie stopniowo rosnąć lub maleć osiągając wartość COUNT gdzie nastąpi zatrzymanie pętli.

Klikamy w nasz komponent Loop i go modyfikujemy.

Wpisujemy nasze zmienne tak jak poniżej w odpowiednie okna zakładki FOR LOOP

Teraz klikamy w nasz komponent znajdujący się w środku komponentu LOOP. Będzie on powtarzany kilka razy.

Aby nie zaśmiecać bazy AdventuresWorks2012 tworzymy połączenie do naszej testowej bazy Hurtownia.


Polecenie ma robić prosty insert losowej liczby do tabeli LOSOWA.

Uzupełniamy pozostałe opcję komponentu.

Uruchamiamy naszą paczkę.

Sprawdzamy czy inserty się zapisały w tabeli. Jak widać wykonały się 10 razy więc jest OK

Dla porządku możemy jeszcze dołożyć komponent wykonujący TRUNCATE na naszej tabeli żeby liczba wierszy nam nie rosła z kazdym uruchomieniem naszej paczki.

Zaszufladkowano do kategorii SSIS | Dodaj komentarz

SSIS – Zapytanie, zmienne i MessageBoxy

W tym odcinku pokażę jak wykorzystać zmienne do prostego zapytania na bazie oraz wyświetlenia potem odpowiedniego komunikatu (MessageBox). Tworzymy nowy projekt i umieszczamy w nim dwa komponenty (Execute SQL Task i Script Task) po czym łączymy je ze sobą.

Klikamy prawym klawiszem myszy na polu naszego projektu i otwieramy okno zmiennych (Variables)

W oknie klikając na pierwszą ikonkę tworzymy nową zmienną i nazywamy ją VarLudzie typu Int32.

Teraz klikamy na nasz pierwszy komponent Execute SQL Task

Nim cokolwiek wpiszemy w ustawieniach naszego komponentu wykonujemy na naszej bazie danych proste zapytanie

Jak widać zapytanie zwróciło nam liczbę wierszy 19972. W ustawieniach komponentu wybieramy nasz połączenie do bazy (Connection).

W oknie SQLStatement wpisujemy nasze zapytanie.

W ResultSet zmienamy opcję na SingleRow

Przechodzimy do zakładki Result Set i wybieramy zapomocą przycisku ADD z listy naszą zmienną.

W kolumnie ResultName wpisujemy 0 (zero) jako że wzracana będzie tylko jeden wynik (jako pierwsza kolumna tabeli licząc od zera). Jeśli by było więcej kolumn w wyniku naszego zapytania oznaczylibyśmy je kolejno 0,1,2 itd

Aby sprawdzić co zwraca nasze zapytanie możemy założyć BreakPointa na naszym projekcie który spowoduje zatrzymanie się wykonywania na pierwszym kroku abyśmy mogli prześledzić jakie wartości przyjmują zmienne.
W tym celu klikamy na naszym pierwszym komponencie i wybieramy Edit Breakpoint

Wybieramy z listy drugą opcję „Break when the container receives the OnPostExecute event”

Potwierdzamy przyciskiem OK i uruchamiamy projekt.

Jak widać projekt zatrzymał się na naszym Breakpoint-cie.

Klikająć w ikonkę zmiennych (kostka) otworzy się nam okno ze zmiennymi.

Musimy tylko teraz przeciagnąć naszą zmienną (VarLudzie) do okna WATCH 1.

Jak widać zmienna przyjęła wartość 19972 czyli wszystko jest OK, więc wyłączamy nasz Breakpoint

Teraz zajmiemy się naszym drugim komponentem (Script Task) który będzie miał za zadanie wyświetlenie naszego wyniku za pomocą MessageBox-a. Klikamy w nasz komponent.

W ReadWriteVariables wybiarmy naszą zmienną przekazaną przez wcześniejszy komponent.

następnie klikamy w EDIT SCRIPT. Po otwarciu nowego okna Visual Studio znajdujemy w treści skryptu fragment

i modyfikujemy dodajemy do niego linie

string strMessage = Dts.Variables["User::VarLudzie"].Value.ToString();
MessageBox.Show(strMessage);

Wklejamy tekst tak jak poniżej

Zapisujemy skrypt, zamykamy okno Visual Studio, zapisujemy nasz projekt i uruchamiamy projekt

Powinno pojawić się okienko

Zaszufladkowano do kategorii SSIS | Dodaj komentarz

Liczba wierszy w tabelach bazy danych

Aby sprawdzić ilość wierszy we wszystkich tabelach w danej bazie danych wykonujemy zapytanie:

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name;

SchemaName			TableName			TotalRowCount
------------------------------------------------------------------------------
Person				Address				19614
Person				AddressType			6
dbo				AWBuildVersion			1
Production			BillOfMaterials			2679
...
...
Production			UnitMeasure			38
Purchasing			Vendor				104
Production			WorkOrder			72591
Production			WorkOrderRouting		67131

(71 rows affected)
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Kompresja tabel w SQL Server

Uruchamiamy kompresję na pojedynczej tabeli w naszej bazie.


Wybieramy rodzaj kompresji.

Za pomocą tego przycisku możemy sprawdzić jak mocno skompresuje się nasza tabela.





Jeśli wejdziemy we właściwości tabeli kompresja nie jest włączona.

Aby ja uruchomić na stałe musimy wykonać polecenie:

USE [AdventureWorks2012]
ALTER TABLE [Person].[Person] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)

Sprawdzamy czy kompresja jest włączona

Powyższy przykład pokazuje jak uruchomić kompresję na pojedynczje tabeli. Jeśli chcielibyśmy uruchomić kompresję na całej bazie danych (wszytskich tabelach) należy wykonać:

EXEC sp_MSforeachtable @command1 = 'alter table ? REBUILD WITH (DATA_COMPRESSION = PAGE);'

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

BCP

Po tą tajemniczą nazwą skrywa się narzędzie Bulk Copy Program utility (w skrócie BCP). Służy ono do kopiowania danych pomiędzy instancją bazy danych SQL Server i płaskimi plikami. Programu można używać z poziomu SQL przy pomocy funkcji CMDSHELL lub z poziomu windowsowej linii poleceń CMD.

Więcej o tym narzędziu można znaleźć na stronie Microsoftu
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

Ja dziś pokaże jak przy pomocy tego programu można w prosty sposób wykonać backup pojedynczej tabeli.

Na początek musimy mieć włączoną w naszej instancji funkcję XP_CMDSHELL. Jeśli nie mamy włączonej to wywołanie BCP zwróci błąd:

Msg 15281, Level 16, State 1, Procedure master..xp_cmdshell, Line 1 [Batch Start Line 0]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

Uruchamiamy XP_CMDSHELL

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Jeśli już ją uruchomiliśmy możemy przystąpić do tworzenia kopii naszej tabeli. W naszym przykładzie będzie to tabela PERSON z naszej bazy_testowej

USE [baza_testowa]
GO
SELECT [imie],[nazwisko],[wiek] FROM [dbo].[person]
GO

imie       nazwisko             wiek
---------- -------------------- -----------
Jan        Nowak                65
Julia      Pawlak               14
Adam       Kowalski             51
Katarzyna  Adamska              42
Anna       Duda                 66

(5 rows affected)

Uruchamiamy BCP z query SQL

DECLARE
@table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = '[baza_testowa].[dbo].[person]'
SET @file = 'G:\SQLBackup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '.bcp'
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

gdzie @table to nasza tabela której chcemy zrobić kopię.

output
-----------------------------------------------------------------
NULL
Starting copy...
NULL
5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (5000.00 rows per sec.)
NULL

(7 rows affected)

Na dysku w folderze G:\SQLBACKUP utworzył się plik [baza_testowa].[dbo].[person]_20190220.bcp. Nie powinno się używać dysku C gdyż Windows ma problemy z uprawnieniami do plików na tym dysku (trzeba czasami mieć prawa administratora do ich odczytu)

Możemy to samo polecenie wykonać prościej z linii poleceń.

bcp [baza_testowa].[dbo].[person] OUT G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220.bcp -c -T

W powyższym poleceniu wynik działania jest wyświetlony na ekranie. Można go też uruchomić w trybie cichym gdzie zarówno występujące błędy jak i wynik działania są zapisywane do plików tekstowych Error_out.log i Output_out.log

bcp [baza_testowa].[dbo].[person] OUT G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220_2.bcp -m 1 -n -e G:\SQLBackup\Error_out.log -o G:\SQLBackup\Output_out.log -S -T

Zawartość pliku Output_out.log

Starting copy...

5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 62     Average : (80.65 rows per sec.)

Jeśli chcemy wykorzystać uwierzytelnianie w bazie danych wydajemy polecenie

bcp [baza_testowa].[dbo].[person] out G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220_3.bcp -c -U -S

Pora odzyskać tabelę z naszego backupu. Jako że polecenie BCP spowoduje odczyt danych z pliku i zapis ich do tabeli aby odzyskać tabelę najpierw należy ją wyczyścić z danych

TRUNCATE TABLE [baza_testowa].[dbo].[person_bcp]

Commands completed successfully.

Sprawdzamy zawartość tabeli

SELECT [imie],[nazwisko],[wiek]
FROM [baza_testowa].[dbo].[person]

imie       nazwisko             wiek
---------- -------------------- -----------

(0 rows affected)

Tabela jest wyczyszczona. Następnie wczytujemy dane z pliku (z linii poleceń)

bcp [baza_testowa].[dbo].[person] IN G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220.bcp -c -T

lub z query w Management Studio

DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = '[baza_testowa].[dbo].[person]'
SET @file = 'G:\SQLBackup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '.bcp'
SET @cmd = 'bcp ' + @table + ' in ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

output
-------------------------------------------------------------------
NULL
Starting copy...
NULL
5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (5000.00 rows per sec.)
NULL

(7 rows affected)

Sprawdzamy naszą tabelę

SELECT [imie],[nazwisko],[wiek]
FROM [baza_testowa].[dbo].[person]

imie       nazwisko             wiek
---------- -------------------- -----------
Jan        Nowak                65
Julia      Pawlak               14
Adam       Kowalski             51
Katarzyna  Adamska              42
Anna       Duda                 66

(5 rows affected)

Tabela odzyskana

Za pomocą BCP możemy wykonać backup pojedynczej kolumny

bcp "SELECT nazwisko FROM [baza_testowa].[dbo].[person] WITH (NOLOCK)" queryout g:\BCP\nazwisko_[baza_testowa].[dbo].[person].bcp -c -T

pojedynczych wierszy

bcp "SELECT * from [baza_testowa].[dbo].[person] WHERE imie = 'Adam'" queryout G:\BCP\Adam_[baza_testowa].[dbo].[person].bcp -d WideWorldImporters -c -T

lub zbackupować wynik zapytania

bcp "SELECT imie,nazwisko,wiek FROM [baza_testowa].[dbo].[person] ORDER BY wiek" queryout G:\BCP\People.txt -t, -c -T

Możemy też zdefiniować typ pliku backupu

REM non-XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\BCP\StockItemTransactions_c.fmt -c -T

REM non-XML native format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\BCP\StockItemTransactions_n.fmt -n -T

REM XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\BCP\StockItemTransactions_c.xml -x -c -T

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Sprawdzanie ilości wolnego miejsca na dysku w SQL Server

Poniżej najprostsze zapytanie zwracajace ilość miejsca na dysku na którym znajdują się bazy danych. UWAGA! Zapytanie to nie działa w wersji SQL2008 !

EXEC MASTER..xp_fixeddrives

drive MB free
----- -----------
C     43948
D     16985
E     34184
F     6148
G     1514

(5 rows affected)

Innym zapytaniem możemy sprawdzić miejsce na dyskach powiązanych z naszymi bazami danych. Dyski na których nie ma baz nie będą wyświetlane.

SELECT DISTINCT
SUBSTRING(dovs.logical_volume_name,1,20) AS LogicalName,
SUBSTRING(dovs.volume_mount_point,1,20) AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO

LogicalName          Drive                FreeSpaceInMB
-------------------- -------------------- -------------
SYSTEM               C:\                  43956

(1 row affected)

Po modyfikacji wyświetla ilość wolnego miejsca w powiązaniu z naszymi bazami.

SELECT DISTINCT
SUBSTRING(DB_NAME(dovs.database_id),1,25) DBName,
SUBSTRING(dovs.logical_volume_name,1,20) AS LogicalName,
SUBSTRING(dovs.volume_mount_point,1,20) AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO

DBName                    LogicalName          Drive                FreeSpaceInMB
------------------------- -------------------- -------------------- -------------
baza_testowa              SYSTEM               C:\                  43954
AdventureWorks2012        SYSTEM               C:\                  43954
master                    SYSTEM               C:\                  43954
ReportServerTempDB        SYSTEM               C:\                  43954
msdb                      SYSTEM               C:\                  43954
ReportServer              SYSTEM               C:\                  43954
tempdb                    SYSTEM               C:\                  43954
Northwind                 SYSTEM               C:\                  43954
DataWarehouse             SYSTEM               C:\                  43954
FGRestoreTEST             SYSTEM               c:\                  43954
TriggerDatabase           SYSTEM               C:\                  43954
CDCDatabase               SYSTEM               C:\                  43954
SSISDB                    SYSTEM               C:\                  43954
model                     SYSTEM               C:\                  43954

(14 rows affected)

Mozemy też wyświetlić jeszcze bardziej szczegółowo w rozbiciu na konkretne pliki

SELECT DISTINCT
SUBSTRING(DB_NAME(dovs.database_id),1,25) DBName,
SUBSTRING(mf.physical_name,1,70) as PhysicalFileLocation,
SUBSTRING(dovs.logical_volume_name,1,15) AS LogicalName,
SUBSTRING(dovs.volume_mount_point,1,10) AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO

DBName                    PhysicalFileLocation                                                   LogicalName     Drive      FreeSpaceInMB
------------------------- ---------------------------------------------------------------------- --------------- ---------- -------------
SSISDB                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf                   SYSTEM          C:\        43953
ReportServer              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf             SYSTEM          C:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf                  SYSTEM          C:\        43953
SSISDB                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf                   SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2012.ndf                                                  SYSTEM          c:\        43953
ReportServerTempDB        C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB_log.ldf   SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FGRestoreTEST.mdf                                           SYSTEM          c:\        43953
msdb                      C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf                  SYSTEM          C:\        43953
AdventureWorks2012        C:\SQLData\AdventureWorks2012_log.ldf                                  SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2013.ndf                                                  SYSTEM          c:\        43953
Northwind                 C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\Northwind.ldf                SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FGRestoreTEST_log.ldf                                       SYSTEM          c:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf           SYSTEM          C:\        43953
DataWarehouse             C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\DataWarehouse_log.ldf        SYSTEM          C:\        43953
TriggerDatabase           C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\TriggerDatabase.mdf          SYSTEM          C:\        43953
baza_testowa              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\baza_testowa.mdf             SYSTEM          C:\        43953
ReportServerTempDB        C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf       SYSTEM          C:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf                   SYSTEM          C:\        43953
AdventureWorks2012        C:\SQLData\AdventureWorks2012_Data.mdf                                 SYSTEM          C:\        43953
CDCDatabase               C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\CDCDatabase.mdf              SYSTEM          C:\        43953
model                     C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf                 SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2011.ndf                                                  SYSTEM          c:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf           SYSTEM          C:\        43953
msdb                      C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf                 SYSTEM          C:\        43953
DataWarehouse             C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\DataWarehouse.mdf            SYSTEM          C:\        43953
model                     C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf                    SYSTEM          C:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf           SYSTEM          C:\        43953
Northwind                 C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\Northwind.mdf                SYSTEM          C:\        43953
ReportServer              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServer_log.ldf         SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2010.ndf                                                  SYSTEM          c:\        43953
baza_testowa              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\baza_testowa_log.ldf         SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2014.ndf                                                  SYSTEM          c:\        43953
master                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf                   SYSTEM          C:\        43953
CDCDatabase               C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\CDCDatabase_log.ldf          SYSTEM          C:\        43953
master                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf                  SYSTEM          C:\        43953
TriggerDatabase           C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\TriggerDatabase_log.ldf      SYSTEM          C:\        43953

(36 rows affected)
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

SQLCMD czyli łączymy się do zdalnego serwera SQL

Często podczas tworzenia zapytań musimy skorzystać z danych znajdujących się na jakimś innym serwerze SQL. Z reguły wykonujemy połączenie za pomocą Management Studio wykonujemy zapytanie a następnie wracamy do naszego serwera na którym obecnie pracujemy. Aby ułatwić pracę w Management Studio utworzono funkcję SQLCMD. Najpierw musimy ją uruchomić. Aby to zrobić otwieramy nowe okno Query a następnie z górnego menu Query wybieramy SQLCMD MODE.

Nim uruchomimy ten tryb zapytania korzystające z tej funkcji będą zwracać błędy

Po uruchomieniu zostaną one podświetlone kolorem szarym.

Jak już uruchomiliśmy nasze SQLCMD możemy próbować podłączyć się do serwera.
Polecenie

:connect localhost

spowoduje połączenie się do lokalnego serwera (tak jakbyśmy wykonywali zwykłego selecta bez włączania SQLCMD.

Polecenie

:CONNECT SQLSERVER1\INSTANCE1

Łączy się do serwera SQLSERVER1 do instancji INSTANCE1
Możemy po tym poleceniu wpisać np. jakiegoś selecta który wykona się właśnie na tym serwerze i jego bazach

Jeśli chcemy użyć uwierzytelnienia za pomocą konta i hasła w bazie danych wykonujemy:

:CONNECT SQLSERVER1 -U MyLoginName -P MyPassword

Jeśli chcemy wykonać polecenie w systemie operacyjnym np wyświetlić zawartość folderu wpisujemy

!!DIR
!!:GO

lub

!!%SystemRoot%\system32\cmd.exe /c dir c:\
!!:GO

Wykonanie selecta wygląda podobnie

!!sqlcmd /Q "exit(SELECT COUNT(*) FROM [Northwind].[dbo].[Employees])"

Niedługo podam jeszcze kilka innych przykładów. CDN… 🙂

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Extended Events w SQL Server

Poniżej omówię w skrócie kolejne narzędzie monitorujące zdarzenia w naszej bazie danych jakim jest Extended Events. Uruchamiamy Management Studio i znajdujemy narzędzie w zakładce Management. Rozwijamy je i w środku odnajdujemy gałąź Sessions.

Możemy teraz uruchomić kreatora naszej sesji jak to ładnie nazwali a co będzie naszym procesem monitorującym.

Albo uruchomić prostszą wersję

My uruchomimy wizarda

Wpisujemy nazwę naszej „sesji”.Mozemy zaznaczyć aby uruchamiała się razem z nasza instancją.

Na razie nie posiadamy żadnego szablonu więc wybieramy drugą opcję

Teraz wybieramy jakie zdarzenia w naszej instancji chcemy monitorować. W naszym przykładzie będą to podstawowe operacje na bazie danych (tworzenie, zmiana rozmiaru plików i usunięcie).

Po wybraniu przy użyciu strzałki dodajemy go do prawego panelu.

Następnie wybiramy jakie dane mają być wyświetlone po wykryciu zdarzenia.

Tu na razie nic nie zmieniamy. Możemy tu ustawić opcję filtrowania danych.

Tutaj ustawimy by zarejestrowane dane zostały zapisane do pliku na dysku w celu późniejszej analizy. Określamy oprócz ścieżki do pliku, jego wielkość, ilość plików (po wykorzystaniu maksymalnej liczny zostaną one nadpisane).

Małe podsumowanie

Przy pomocy przycisku SCRIPT możemy utworzyć skrypt do utworzenia naszej sesji


CREATE EVENT SESSION [MySession_1] ON SERVER
ADD EVENT sqlserver.database_created(
ACTION(sqlos.task_time,sqlserver.client_hostname,sqlserver.database_id,
sqlserver.database_name,sqlserver.nt_username,
sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.database_dropped(
ACTION(sqlos.task_time,sqlserver.client_hostname,sqlserver.database_id,
sqlserver.database_name,sqlserver.nt_username,
sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.database_file_size_change(
ACTION(sqlos.task_time,sqlserver.client_hostname,sqlserver.database_id,
sqlserver.database_name,sqlserver.nt_username,
sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.databases_log_file_size_changed(
ACTION(sqlos.task_time,sqlserver.client_hostname,sqlserver.database_id,
sqlserver.database_name,sqlserver.nt_username,
sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file
(SET filename=N'C:\SQLData\MySession_1.xel',max_file_size=(30))
WITH (STARTUP_STATE=OFF)
GO

No i kończymy tworzenie. Na koniec zaznaczamy aby sesja została uruchomiona zaraz po utworzeniu oraz aby zdarzenie zostało wyświetlone jak tylko się wydarzy.

Po zamknięciu kreatora otworzy się okno w którym będą wyświetlane wykryte zdarzenia.

W drzewie Session pojawi się nasz nowy element

Jakby okno ze zdarzeniami nie było otwarte możemy go zawsze otworzyć z menu

Tak więc mamy otwarte okno monitorujące nasze zdarzenia (na razie puste)

Pora na kilka „zdarzeń”. Na początek utworzymy nową bazę.

Zmienimy rozmiar pliku MDF

Zmienimy rozmiar pliku LDF

A teraz zalogujemy się na innego użytkownika i usunięmy naszą testową bazę.

Jak widać wszystko jest rejestrowane (kto usunął, kiedy i jakiego polecenia użył).
Tak jak wcześniej skonfigurowaliśmy wszystkie zdarzenia zostały zapisane do pliku XEL.

Naszą sesję możemy w każdej chwili zatrzymać aby dalej nie monitorowała zdarzeń.

W momencie kiedy otwarte jest okno wyświetlające nasze zdarzenia w górnym menu pojawia się dodatkowa opcja „Extended Events”.

Jest tu kilka opcji do skonfigurowania.

Jeśli naciśniemy poniższy przycisk, możemy wybrać plik XEL do wczytania do okna ze zdarzeniami.

Możemy też wyeksportować nasze ustawienia sesji jako szablon XML do wykorzystania przy tworzeniu nowych sesji.

Przy tworzeniu nowej sesji p oprostu wybieramy w template nasza szablon

Jak już wszystko przetestowaliśmy, zatrzymujemy sesję a następnie możemy ją usunąć poleceniem

DROP EVENT SESSION [MySession_1] ON SERVER

lub z menu

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Mirroring w SQL Server

Nie będę się rozpisywał do czego to służy, bo od tego są książki. Pokażę jak w prosty sposób to skonfigurować.
Po pierwsze na obu serwerach (Primary i Secondary czyli Mirror) serwisy SQL muszą być uruchamiane z kont lokalnych (w naszym przykładzie SQLService). Najlepiej stworzyć specjalne konta dla naszych serwisów.

Pamiętajmy że po zmianie konta z którego będzie uruchamiany serwis należy je zrestartować.

Utworzymy mirror bazy. Logujemy się na oba serwery. Na serwerze PRIMARY uruchamiamy konfigurację Mirroringu.

lub w inny sposób wchodząc we właściwości bazy

i pierwssze problemy 🙂

Zwróciło błąd bo baza jest w trybie Recowery Simple. Musimy zmienić na Recovery Full we właściwościach bazy.

lub poleceniem

USE [master]
GO
ALTER DATABASE [Northwind] SET RECOVERY FULL WITH NO_WAIT
GO

Commands completed successfully.

Teraz trzeba wykonać pełny backup naszej bazy oraz drugi backup logów transakcyjnych (najlepiej do tego samego pliku z funkcję Append). Następnie nalezy przenieść plik backupu na nasz drugi serwer i tam odtworzyć bazę.
UWAGA !!! bazę odtwarzamy z trybie NORECOVERY

Nasze bazy będą po tym wyglądały tak:


Jak widać druga baza jest w trybie RESTORING.

Pora uruchomić mirroring.

Uruchomi się kreator mirroringu


kreator pyta nas czy będziemy wykorzystywali „świadka”. Jest to specjalny serwer który będzie monitorował nasze serwery z mirrorem. Jeśli tylko straci kontakt z serwerem automatycznie przełączy mirror na drugi serwer. Aby połaczyć serwery w mirroringu potrzebna jest wersja Stabdard lub Enterprise. Na świadku musi być zainstalowany SQL ale może to być nawet wersja Express (SQL służy tylko do komunikacji więc nie ma specjalnych wymagań).

Świadek będzie wiec zostawiamy ptaszka 🙂

Tu nic nie zmieniamy (w sumie to tak tylko informacyjnie jest podane).

Tu łączymy się z serwerem SECONDARY

A tu z serwerem „świadka” WITNESS

W tym oknie wpisujemy konto za pomocą którego będziemy autoryzowani na naszych trzech serwerach (Primary,Secondary i Witness). Może to być jedno wspólne konto domenowe lub dla każdego inne konto (te same z którego uruchamialiśmy serwisy SQL).

No i kończymy

Pora uruchomić nasz skonfigurowany Mirror

Potrwa to kilka chwil ale jeśli wszystko jest OK powinno się pojawić takie okno.

Jak można zauważyć mirroring komunikuje się z serwerami za pomocą portów 5022, 5023 i 5024. Taka konfiguracja występuje gdy uruchamiamy ją na jednym serwerze (jak w naszym przykładzie). Nie jest to dobre rozwiązanie bo jak padnie nam serwer znikają obie bazy wraz ze świadkiem. Najlepiej to skonfigurować na trzech różnych serwerach i wtedy mirror będzie się komunikował po porcie 5022.

Nasze bazy powinny zmienić swój status na Principal/Synchronized i Secondary/Synchronized/Restoring.

Jest OK ale dla pewności możemy uruchomić Database Mirroring Monitor.

Proszę się przerazić jak zobaczycie czerwone ikonki alarmu. Po kilku chwilach gdy bazy się zsynchronizują wszystko się ustabilizuje.

Bazy teraz w momencie awarii powinny się przełączyć na drugą. Oczywiście bazy można ręcznie przestawiać. Do tego służy przycisk FAILOVER

Po jego naciśnięciu otrzymamy komunikat że połączenia z bazą PRIMARY zostaną zerwane i czy chcemy tego.

I już mamy bazę na serwer 2 jako PRIMARY

Jesli byśmy wyłączyli serwis SQL na serwerze 1 to baza na serwerze 2 (obecnie PRIMARY) straci połączenie.

UWAGA!!! Trzeba na to szybko reagować, gdyż na bazie PRIMARY będą zbierane logi transakcyjne do czasu usunięcia awarii. Więc jeśli planujemy kilkudniową awarię to lepiej jest usunąć mirroring na tej bazie i po naprawie serwera 1 ponownie ją skonfigurować.

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz