87. Spotkanie PLSSUG – dodatek o FILESTREAM

7 maja 2015 roku na 87. spotkaniu warszawskiej grupy PLSSUG prowadziłem sesję nt. FILESTREAM w SQL Server. Zostały dwa pytania, na które chciałbym uzupełnić odpowiedź.

1. Czy można utworzyć grupę plików zawierającą FILESTREAM na zasobie sieciowym?

W pierwszej chwili odpowiedziałem, że nie – z tego co wiem, nie można założyć grupy FILESTREAM na zasobie sieciowym – musi być bezpośrednio na dysku. Swoją wiedzę opierałem na dokumencie Paula Randalla nt. FILESTREAM oraz jego wpisie na blogu, który ponownie mówi o tym, że reguły tworzenia grup plików pod FILESTREAM maja takie same zasady, jak tworzenie plików baz danych i logów. Przed sesją nie sprawdziłem tego dokładnie, ale kiedy na sesji padło pytanie przypomniałem sobie, że informacje od Paula Randalla pochodzą z 2008 i 2010 roku, a SQL Server 2012 wprowadził możliwość tworzenia plików na zasobach sieciowych. Ale o tym, że jest adnotacja „Filestream is currently not supported on an SMB file share.” już nie pamiętałem – stąd moje zawahanie i stwierdzenie, że się nie da, ale jeszcze sprawdzę.

Dokumentacja mówi, że nie założymy grupy FILESTREAM na zasobie, ale zróbmy test. Jako nazwa zasobu sieciowego posłuży dostęp z maszyny wirtualnej opartej o Virtualbox do maszyny hostującej.

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'PLSSUG_DEMO1')
DROP DATABASE [PLSSUG_DEMO1]
GO

CREATE DATABASE [PLSSUG_DEMO1]
ON PRIMARY (
	NAME		= N'PLSSUG_DEMO1',
	FILENAME	= N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PLSSUG_DEMO1.mdf',
	SIZE		= 102400KB,
	FILEGROWTH	= 51200KB,
	MAXSIZE		= UNLIMITED
),
FILEGROUP [FS_DOCS] CONTAINS FILESTREAM DEFAULT (
	NAME		= N'FS_DOCS1',
	FILENAME	= N'\\VBOXSVR\vm_share\Filestream\FS_DOCS1',
	MAXSIZE		= UNLIMITED
)
LOG ON (
	NAME		= N'PLSSUG_DEMO1_log',
	FILENAME	= N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PLSSUG_DEMO1_log.ldf',
	SIZE		= 51200KB,
	FILEGROWTH	= 20480KB
)
GO
/*
Pojawia się komunikat:
Msg 5135, Level 16, State 2, Line 8
The path '\\VBOXSVR\vm_share\Filestream\FS_DOCS1' cannot be used for FILESTREAM files. For information about supported paths, see SQL Server Books Online.
Msg 1802, Level 16, State 2, Line 8
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
*/

Czyli faktycznie nie można. A jeśli zmapujemy zasób na dysk?

net use Z: \\VBOXSVR\vm_share

A potem powtórzymy test?

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'PLSSUG_DEMO1')
DROP DATABASE [PLSSUG_DEMO1]
GO

CREATE DATABASE [PLSSUG_DEMO1]
ON PRIMARY (
	NAME		= N'PLSSUG_DEMO1',
	FILENAME	= N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PLSSUG_DEMO1.mdf',
	SIZE		= 102400KB,
	FILEGROWTH	= 51200KB,
	MAXSIZE		= UNLIMITED
),
FILEGROUP [FS_DOCS] CONTAINS FILESTREAM DEFAULT (
	NAME		= N'FS_DOCS1',
	FILENAME	= N'Z:\Filestream\FS_DOCS1',
	MAXSIZE		= UNLIMITED
)
LOG ON (
	NAME		= N'PLSSUG_DEMO1_log',
	FILENAME	= N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PLSSUG_DEMO1_log.ldf',
	SIZE		= 51200KB,
	FILEGROWTH	= 20480KB
)
GO
/*
Pojawia się komunikat:
Msg 5135, Level 16, State 2, Line 8
The path 'Z:\Filestream\FS_DOCS1' cannot be used for FILESTREAM files. For information about supported paths, see SQL Server Books Online.
Msg 1802, Level 16, State 2, Line 8
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
*/

To jeszcze jeden test. SQL Server umożliwiał zakładanie plików na zasobach sieciowych już dla wersji 2005, ale pod warunkiem użycia Trace Flag 1807. Niestety, nawet jeżeli przed poleceniami użyjemy DBCC TRACEON(1807, 1) wynik pozostaje bez zmian – w obu powyższych przypadkach.

Czyli jednak nie daje się przechytrzyć i dla wersji SQL Server 2012 i 2014 – nie pozwala utworzyć grupy FILESTREAM na zasobie sieciowym.

2. Czy kompresja backupu wpływa na rozmiar backupu logu transakcyjnego?

Tego zupełnie nie sprawdzałem, więc przeprowadziłem test. Wyniki w tabeli poniżej. Zapytania do backupów z/bez kompresji i do uzyskania informacji o backupach:

-- dla kompresji usuń komentarze
BACKUP DATABASE [PLSSUG_DEMO2]
	TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\PLSSUG_DEMO2.bak'
	WITH FORMAT, INIT--, COMPRESSION
GO

BACKUP LOG [PLSSUG_DEMO2]
	TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\PLSSUG_DEMO2.trn'
	WITH FORMAT, INIT--, COMPRESSION
GO

SELECT  
	[Database name]		= b.database_name,
	[File name]			= REVERSE(SUBSTRING(REVERSE(bmf.physical_device_name), 0, CHARINDEX('\', REVERSE(bmf.physical_device_name)))),
	[Backup (MB)]		= CAST(b.backup_size / 1024 / 1024 AS NUMERIC(10,2)),
	[Compressed (MB)]	= CAST(b.compressed_backup_size / 1024 / 1024 AS NUMERIC(10,2))
FROM msdb.dbo.backupmediafamily bmf
	
	INNER JOIN msdb.dbo.backupset b
		ON b.media_set_id = bmf.media_set_id

ORDER BY  
   b.database_name, 
   b.backup_start_date

Utworzyłem od początku bazę i zaimportowałem 14 plików mp3 z pierwszej płyty ścieżki dźwiękowej Darksiders II. Następnie wywołałem (bez kompresji) backup bazy, a potem logu (wiersze 1 i 2). Powtórzyłem potem operację z włączoną kompresją (wiersze 3 i 4). Potem ponownie utworzyłem bazę, zrobiłem jej backup z kompresją (wiersz 5), zaimportowałem pierwszą płytę ścieżki dźwiękowej i zrobiłem backup logu z kompresją (wiersz 6).

# Database name File name Backup (MB) Compressed (MB)
1 PLSSUG_DEMO2 PLSSUG_DEMO2.bak 59.08 59.08
2 PLSSUG_DEMO2 PLSSUG_DEMO2.trn 1.08 1.08
3 PLSSUG_DEMO2 PLSSUG_DEMO2.bak 59.08 54.40
4 PLSSUG_DEMO2 PLSSUG_DEMO2.trn 1.20 0.04
5 PLSSUG_DEMO2 PLSSUG_DEMO2.bak 5.08 0.40
6 PLSSUG_DEMO2 PLSSUG_DEMO2.trn 56.20 54.03
7 PLSSUG_DEMO2 PLSSUG_DEMO2.trn 53.14 51.73
8 PLSSUG_DEMO2 PLSSUG_DEMO2.bak 110.08 106.09
9 PLSSUG_DEMO2 PLSSUG_DEMO2.trn 1.08 0.01

Widać, że kompresja samego logu dała zysk około 2MB. Dorzuciłem 13 utworów z drugiej płyty i zrobiłem kolejny backup logu (wiersz 7) – dane zostały podobnie skompresowane. Na koniec zrobiłem backup całości, z włączoną kompresją (wiersze 8 – 9).

Dla porównania spakowałem same pliki mp3 za pomocą 7zip z kompresją Ultra. Cała spakowana ścieżka dźwiękowa zajmuje 104MB, przy kompresji płyt oddzielnie: pierwsza zajmuje 53.4MB, druga 51.1MB. Dla porównania rozmiary plików bez kompresji: ~105MB obie płyty razem (54.1MB pierwsza, 51.7MB druga).

Wniosek: włączenie kompresji wpływa na rozmiar logu, a także na rozmiar backupu bazy. A w badanym przypadku – wydajność kompresji dla pojedynczych płyt jest porównywalna z kompresją 7zip i jest mniejsza dla całej ścieżki dźwiękowej.

Reklamy

Jedna uwaga do wpisu “87. Spotkanie PLSSUG – dodatek o FILESTREAM

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s