Bazy Danych

Dziś pokażę jak wykonać przeniesienie danych pomiędzy serwerami z różnymi wersjami SQL. Jeśli chcielibyśmy przenieść ze starszego serwera na nowszy nie powinno być z tym większego problemu. Problemy pojawiają się jeśli chcemy przenieść dane (bazy) z serwera o wyższej wersji do serwera z niższą wersją. Niestety tu napotkamy na problemy (zarówno próbując wykonać Attach plików MDF i LDF z nowszej wersji, jak i próbując odzyskać backup wykonany na wyższej wersji)

Możemy skorzystać z exportu a następnie importu danych znajdujących się w bazie. Ja jednak pokażę jak wygenerować skrypty pozwalające na migrację danych.

Załóżmy że mamy dwa serwery (SQL2019 i SQL2016) i chcemy przenieść całą bazę z wyższej wersji (2019) do wersji starszej (2016).
Łączymy się do serwera źródłowego. Wybieramy bazę którą chcemy przenieść i z menu wybieramy opcję GENERATE SCRIPTS

Mamy do wybory dwie opcje. Pierwsza pozwala na wygenerowanie skryptu dla całej bazy.

Drugi dla pojedynczych obiektów bazy (tabele, widoki lub procedury składowane).

Przy wyborze Single Script File (jeden skrypt) wybieramy Ścieżkę i nazwę skryptu. Możemy również m.in. wybrać od razu wczytanie skryptu do okna SQL Management Studio.

Po naciśnięciu przycisku ADVANCED mamy sporą listę parametrów dotyczących tworzonych skryptów.

Na głównie będą interesowały poniższe czyli wybór czy w skrypcie mają być zawarte dane znajdujące się w tabelach czy tylko skrypty tworzące strukturę czy jedno i drugie:

Wersja docelowej wersji SQL-a:

Edycja docelowej wersji SQL-a:

Możemy również wybrać opcję w której wygenerowane zostaną skrypty dla poszczególnych obiektów (np. tabel). Wskazujemy w takim przypadku tylko folder gdzie mają się znaleźć skrypty a nazwy zostaną wygenerowane automatycznie

Małe podsumowanie i naciśnięcie FINISH spowoduje uruchomienie tworzenia skryptów.

Skrypty utworzone.

Teraz nie pozostaje nic innego jak skopiować je na docelowy serwer i uruchomić.
Powinniśmy zacząć od skryptu tworzącego nasza bazę (w naszym przypadku Northwind.Database.sql). Przy tworzeniu bazy nalezy zwrócić uwagę na ścieżki do plików bazy czy jest wpisana odpowiednia ścieżka.
Następnie wykonujemy skrypty dla poszczególnych obiektów (najpierw tabel, potem widoków, procedur składowanych itd).
Jak widać poniżej skrypt dla tabeli dbo.Categories.Table składa się z sekcji tworzącej tabele (CREATE TABLE) wraz z indeksami jak i jej napełnienie (INSERT dla każdego wiersza osobny)

PROCEDURA

Inną metoda jest zastosowanie procedury za pomocą której wygenerujemy skrypt to wprowadzenia danych do naszej tabeli. Link do wkryptu sp_generate_inserts.sql

Procedurę uruchamiamy poleceniem (przykładowo dla bazy Nortwind i tabeli dbo.Customers

EXEC sp_generate_inserts 'Customers'

Aby ograniczyć wielkość pliku uruchomimy to z parametrem określającym wygenerowanie skryptu dla TOP(5) wierszy tabeli

EXEC sp_generate_inserts 'Customers', @top = 5

--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
 
SET NOCOUNT ON
 
PRINT 'Inserting values into [Customers]'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('ALFKI','Alfreds Futterkiste','Maria Anders','Sales Representative','Obere Str. 57','Berlin',NULL,'12209'
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('ANATR','Ana Trujillo Emparedados y helados','Ana Trujillo','Owner','Avda. de la Constitución 2222','Méxi
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('ANTON','Antonio Moreno Taquería','Antonio Moreno','Owner','Mataderos  2312','México D.F.',NULL,'05023','
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('AROUT','Around the Horn','Thomas Hardy','Sales Representative','120 Hanover Sq.','London',NULL,'WA1 1DP'
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('BERGS','Berglunds snabbköp','Christina Berglund','Order Administrator','Berguvsvägen  8','Luleå',NULL,'S

PRINT 'Done'

SET NOCOUNT OFF

Po zakończeniu prac usuwamy procedure poleceniem
drop proc sp_generate_inserts

Więcej informacji dotyczącej procedury sp_generate_inserts :
https://vyaskn.tripod.com/code.htm#inserts

Załóżmy, że codziennie wieczorem ktoś uruchamia jakieś zapytania do naszej bazy powodując np. zwiększone obciążenie serwera. Aby znaleźć winowajcę możemy wykorzystać narzędzie Extended Events.
Sposób konfiguracji opisałem w oddzielnym artykule (http://micek1968.pl/?p=3632).
Dziś skupimy się na konfiguracji aby wyszukiwało wykonywane „duże” zapytania.

Wchodzimy we właściwości naszej sesji Extended Events.


Przechodzimy do zakładki Event. proponuję w tym miejscu wybrać z biblioteki zdarzeń tylko „SQL_STATEMENT_COMPLETE”. W zupełności to wystarczy do naszych poszukiwań.

Przechodzimy w prawo do konfiguracji zdarzeń, a następnie do zakładki filtrowania.

Interesują nas tylko „duze” zapytania więc wybieramy zapytania które zwracają więcej niż 50 wierszy. Wybieramy z listy rozwijanej „LAST_ROW_COUNT”

a następnie podajemy wartość powyżej której ma wyszukiwać.

Przy ponownym otwarciu ustawień znak „>” zmienia się automatycznie w wartość „greater_than_uint64”

Zatwierdzamy ustawienia i uruchamiamy Sesję Extended Events

Następnie wykonujemy (testowo) kilka zapytań na bazie Northwind. Nie ma tam dużo wierszy ale wszystkie zapytania zwracające więcej niż 50 wierszy zostaną zarejestrowane.

SELECT * FROM [Northwind].[DBO].[ORDER_DETAILS] 
SELECT * FROM [Northwind].[DBO].[ORDERS]
SELECT * FROM [Northwind].[DBO].[PRODUCTS]
SELECT * FROM [Northwind].[DBO].[CUSTOMERS]

Następnie zamykamy sesję

I wyświetlamy zapisane dane

Jak widać nasze zapytania zostały zarejestrowane:

Widać kto to wykonywał, z jakiej komputera, ile wierszy zwróciło, treść zapytania i wiele innych rzeczy.
Wyszukiwanie możemy za pomocą filtra oczywiście ograniczyć do jednej bazy i wielu innych różnych parametrów.

Problemem się staje kiedy to puściliśmy rejestrację zdarzeń do Extended Events np na cały dzień i zarejestrowało nam kilkaset tysiecy zapytań do bazy. Jak znaleźć wśród tego interesujące nas zapytanie.
Możemy się wspomóc odczytem zarejestrowanych danych z plików poprzez zapytanie t-sql.

W naszym przykładzie zdarzenia są zapisane w pięciu plikach, ale może być ich więcej i o większej pojemności.

Najprostszym zapytanie zwracającym zarejestrowane wyniki będzie

select * from sys.fn_xe_file_target_read_file('C:\Temp\MySession_*.xel', null, null, null)

Zwróci to nam wszystkie zarejestrowane zdarzenia. Mozemy ograniczyć się do pojedynczych plików wpisując ich nazwę.

Jeśli np wiemy, że zapytanie dotyczy konkretnej bazy mozemy dodać warunek
WHERE event_data like '%Orders%'
Zapytanie zwróci tyle zdarzenia dotyczące tabeli ORDERS.

Wszelkie dane dotyczące danego zdarzenia znajdują się w kolumnie EVENT_DATA i EVENT_DATA_XML (zapisane w formacie XML).

Możemy bardziej uporządkować wyświetlane dane

SELECT
object_name,
file_name,
file_offset,
event_data,
'CLICK_NEXT_CELL_TO_BROWSE_XML RESULTS!'
AS [CLICK_NEXT_CELL_TO_BROWSE_XML_RESULTS],
CAST(event_data AS XML) AS [event_data_XML]
-- TODO: In ssms.exe results grid, double-click this xml cell!
FROM
sys.fn_xe_file_target_read_file(
--'C:\Temp\MySession_1_0_132878711812050000.xel',
'C:\Temp\MySession_1_0_*.xel',
null, null, null
)
WHERE event_data like '%Orders%'

Poniższe zapytanie zwraca nam zapisane zdarzenia w bardziej czytelnej postaci.

select
n.value('(@name)[1]', 'varchar(50)') as event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name,
n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end
from (select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('C:\Temp\MySession_*.xel', null, null, null)) ed
cross apply ed.event_data.nodes('event') as q(n)
-- WHERE n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') like '%Orders%'

Jeśli nas interesuje tylko tabela ORDERS musimy odznaczyć ostatni wiersz z warunkiem WHERE.

A na na koniec taka mała porada, że jeśli chcielibyśmy zarejestrować tylko zapytania wykonywane w określonym przedziale czasowym (np między 2 i 3 w nocy najprościej powinniśmy utworzyli dwa joby: jeden uruchamiający a drugi zatrzymujący naszą sesję (nie ma możliwości ustawienia tego we właściwościach sesji).
W kroku joba nalezy umieścić polecenie uruchamiające/zatrzymujące naszą sesję

ALTER EVENT SESSION event_session_name ON SERVER STATE = {START | STOP}

Standardowo maile wysyłane w usłudze SQLMail są w formacie tekstowym. Czasami jednak potrzebne jest skorzystanie z formatu HTML, aby np wysłać tabelkę lub po prostu „pokolorować” naszego maila ;). I tym się dziś zajmiemy.
Ogólnie opisując należy w treści maila wysłać kod HTML. Poniższy skrypt właśnie to robi.

Na początek coś prostego. W mailu wyślemy trzy teksty różnego koloru i pisane różną czcionką:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = SQLMail_profile,
@recipients='micek1968@gmail.com',
@subject = 'Kolorowy Mail HTML',
@body = '<font color="green" face="arial"><i> Zielony tekst ARIAL <br /> </i></font>
<hr />
<font color="red" face="times"><i> Czerwony tekst TIMES <br /> </i></font>
<hr />
<font color="blue" face="courier"><i> Niebieski tekst COURIER <br /> </i></font>
',
@body_format = 'HTML' ;

W mailu otrzymamy

Teraz wyślemy coś bardziej skomplikowanego czyli wynik zapytania w formie prostej tabeli:

DECLARE @BodyText NVARCHAR(MAX);

SET @BodyText =
N’Lista pracowników w IT w roku 2021:<BR><BR>’ +
N'<table border=”1″ style=”width:30%;”>’ +
N'<tr>
<th style=”background-color:#FF0000″>Imię</th>
<th style=”background-color:#00FF00″>Nazwisko</th>’ +
CAST ( ( SELECT top(10) FirstName as 'td’,”,LastName as 'td’
from [AdventureWorks2012].[Person].[Person]
FOR XML PATH(’tr’), TYPE) AS NVARCHAR(MAX) ) +
N'</table>’ ;

exec msdb.dbo.sp_send_dbmail
@profile_name = SQLMail_profile
, @recipients = 'micek1968@gmail.com’
, @subject = 'Raport za 2021 rok’
, @Body = @BodyText
, @body_format = 'HTML’;

W wyniku wykonania tego skryptu otrzymamy na maila:

Na zakończenie jeszcze mail w formie HTMLa z załącznikiem graficznym.

create procedure sendMail (@attachement nvarchar(maX))
as
begin

EXEC msdb.dbo.sp_send_dbmail
@profile_name = SQLMail_profile,
@body = @body,
@body_format ='HTML',
@recipients='micek1968@gmail.com',
@subject = 'Mail z załącznikiem' ,
@file_attachments = @attachement
End

Uruchomienie procedury wysyłającej maila z załącznikiem:

exec sendMail 'C:\TEMP\kotek.jpg'

W mailu otrzymamy:

Usunięcie procedury

drop procedure sendMail

Aby wyjaśnić pojęcie Identity czyli autoinkrementacji w tabelach utworzymy tabele z kolumną ID zdefiniowaną jako IDENTITY oraz kolumną Name


CREATE TABLE [TESTOWA].[dbo].[IdentityTable1]
(
id INT IDENTITY,
name VARCHAR(20)
)

Commands completed successfully.

Zapisujemy do niej kilka wierszy nie podając wartości ID

INSERT INTO [TESTOWA].[dbo].[IdentityTable1]
(name) VALUES ('ADAM'),('PIOTR'),('ANDRZEJ'),('KRZYSZTOF'),('JERZY'),('MARIUSZ');
GO

(6 rows affected)

SELECT * FROM [TESTOWA].[dbo].[IdentityTable1]

id          name
----------- --------------------
1           ADAM
2           PIOTR
3           ANDRZEJ
4           KRZYSZTOF
5           JERZY
6           MARIUSZ

(6 rows affected)

Jak widać w kolumnie ID wartości przyjmują kolejne liczby.

Jeśli byśmy chcieli podać liczbę ID w insercie zwróci nam błąd

INSERT INTO [TESTOWA].[dbo].[IdentityTable1]
(id,name) VALUES (1,'ROBERT');
GO

Msg 544, Level 16, State 1, Line 35
Cannot insert explicit value for identity column in table 'IdentityTable1' 
when IDENTITY_INSERT is set to OFF.

Jest możliwość zapisu z wartością ID z użyciem opcji IDENTITY_INSERT ale o tym napiszę później.

Tworzymy drugą tabelę z kolumną ID (identity) podając wartość od jakiej ma zacząć liczyć ID (2) oraz co ile ma się zwiększać wartość ID (5).

CREATE TABLE IdentityTable2
(
id INT IDENTITY (2,5),
name VARCHAR(20)
)

Commands completed successfully.

Przenosimy dane z tabeli 1 do 2. Jako, że kolumna ID na ustawione Identity przenosimy tylko dane z kolumny Name

INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
SELECT name from [TESTOWA].[dbo].[IdentityTable1]
GO

(6 rows affected)

SELECT * FROM [TESTOWA].[dbo].[IdentityTable2]

id          name
----------- --------------------
2           ADAM
7           PIOTR
12          ANDRZEJ
17          KRZYSZTOF
22          JERZY
27          MARIUSZ
 
(6 rows affected)

Jak wyżej próba zapisania wiersza ze zdefiniowaną wartością ID będzie zwracało błąd.

Wartość @@IDENTITY zwraca nam ostatnią wartość Identity jaką użyliśmy w ostatnio używanej tabeli. Obecnie pokazuje nam wartość z tabeli IdentityTable2, ale jeśli wykonamy insert do tabeli IdentityTable1, będzie pokazywało wartość odpowiednią dla tej tabeli (6)

SELECT @@IDENTITY as 'IDENTITY'

IDENTITY
---------------------------------------
27

(1 row affected)

INSERT INTO [TESTOWA].[dbo].[IdentityTable1]
(name) VALUES ('ROBERT');
GO

SELECT @@IDENTITY as 'IDENTITY'

IDENTITY
---------------------------------------
7

(1 row affected)

Możemy też wydać zapytanie:

SELECT
IDENT_CURRENT('IdentityTable1') as CurrentIdentTable1,
IDENT_CURRENT('IdentityTable2') as CurrentIdentTable2

CurrentIdentTable1                      CurrentIdentTable2
--------------------------------------- ---------------------------------------
6                                       32

(1 row affected)

SELECT SCOPE_IDENTITY() as 'IDENTITY';

IDENTITY
---------------------------------------
32
 
(1 row affected)

USE TETSOWA
DBCC CHECKIDENT([IdentityTable2])

Checking identity information: current identity value '32', current column value '32'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Aby zmienić wartość identity na pozycji "startową" 3 wykonujemy polecenie:
DBCC CHECKIDENT([IdentityTable2], RESEED, 3)

Checking identity information: current identity value '32'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Checking identity information: current identity value '3' czyli zgodnie z tym co zdefiniowaliśmy dla tabeli IdentityTable2 następna wartość będzie 8.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
(name) VALUES ('ANNA'),('KATARZYNA'),('ZOFIA'),('MONIKA'),('TERESA'),('KRYSTYNA');

(6 rows affected)

SELECT * FROM [TESTOWA].[dbo].[IdentityTable2]

id          name
----------- --------------------
2           ADAM
7           PIOTR
8           ANNA
12          ANDRZEJ
13          KATARZYNA
17          KRZYSZTOF
18          ZOFIA
22          JERZY
23          MONIKA
27          MARIUSZ
28          TERESA
32          ROBERT
33          KRYSTYNA

(13 rows affected)

Teraz spróbujemy wymusić zapis do tabeli z pominięciem Identity. Wykorzystamy do tego opcję IDENTITY_INSERT

SET IDENTITY_INSERT [TESTOWA].[dbo].[IdentityTable2] ON;
GO
INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
(id,name) VALUES (10,'BORYS');
GO
SET IDENTITY_INSERT [TESTOWA].[dbo].[IdentityTable2] OFF;
GO

Commands completed successfully.
(1 row affected)
Commands completed successfully.

id          name
----------- --------------------
2           ADAM
7           PIOTR
8           ANNA
10          BORYS
12          ANDRZEJ
13          KATARZYNA
17          KRZYSZTOF
18          ZOFIA
22          JERZY
23          MONIKA
27          MARIUSZ
28          TERESA
32          ROBERT
33          KRYSTYNA

(14 rows affected)

Po wyłączeniu (OFF) parametru IDENTITY_INSERT kolejne ID będą przyjmowały wartości zgodnie z definicją Indentity.

INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
(name) VALUES ('TAMARA');

id          name
----------- --------------------
2           ADAM
7           PIOTR
8           ANNA
10          BORYS
12          ANDRZEJ
13          KATARZYNA
17          KRZYSZTOF
18          ZOFIA
22          JERZY
23          MONIKA
27          MARIUSZ
28          TERESA
32          ROBERT
33          KRYSTYNA
38          TAMARA

(15 rows affected)