SQL Server Linuxon

Juhúú, megjelent az SQL Server Linuxon. Install segédlet itt: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup

Mint látszik jelenleg 3 platformon érhető el:

  • Red Hat Enterprise Linux
  • Ubuntu
  • Docker Engine

Én Ubuntura raktam fel teszt jelleggel. Ebben a postban néhány fontos konfigurációs  beállítást mutatok be, hogy az SQL Server ne nyűg, hanem használható társ legyen.

Telepítás után rendelkezésre áll az sqlcmd konzol. Gyorsan felejtsük el, keressünk valami használható toolt helyette. Ilyen pl.: a Heidi SQL. Én windows alól, távolról kapcsolódtam az Ubuntura telepített szerverhez SSMS-el. Szerintem ez a legjobb tool a kezeléséhez.

Csatlakozzunk az SQL szerverhez, példa itt: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-connect-and-query-sqlcmd

 

Most állítsuk be, hogy ne zabálja ki alólunk a memóriát a szerver:

use master
go

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
-- Min memory
EXEC sys.sp_configure N'min server memory (MB)', N'512'
GO
-- Max memory
EXEC sys.sp_configure N'max server memory (MB)', N'1024'
GO
-- Optimize memory for ad hoc queries
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

--Nézzük meg, hogy sikerült e:
select
name,
minimum,
value_in_use
from sys.configurations
where name like '%server memory%'
or name like '%optimize for ad hoc workloads%'
go

 

02_memory_config

 

Állítsuk be a modell adatbázist SIMPLE recovery-re. Ez azért fontos, mert az újonnan létrehozott adatbázisok ennek az adatbázisnak a mintájára fognak létrejönni. Aki nem tudja, hogy ez mire való, annak érdemes átállítani. A lényege, hogy az adatbázisok nem fogják felzabálni a diszket.

 

-- Deault recovery modell
ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT
GO

 

Ha esetleg valami baj van a szerverrel, azt az error logban lehet megnézni, nekem default ide települt: var/opt/mssql/log

 

Ami engem érdekelt telepítés után, az az OP rendszer és az SQL Server kapcsolata. Itt olyan jellegű SQL utasításokra gondolok, amik Windows-on pl.: a registryben matatnak vagy a fájlokhoz köthetőek valahogy. Voltak, amik nem ment ment, szóval majd doksikat kell néznem 🙂 De ezt leszámítva eddig minden más rendben volt.

 

 

Verziófrissítés előtt – “Deprecated Database Engine Features”

Hogy a verziófrissítés minél kevesebb izgalmat tartson, ajánlott az alábbi lekérdezést futtatni:

SELECT * 
FROM sys.[dm_os_performance_counters] 
WHERE [object_name] = 'SQLServer:Deprecated Features' 
    AND [cntr_value] > 0

Az eredményt pedig megvizsgálni minimum az alábbi oldalon, a megfelelő verziót kiválasztva.

A nested tárolt eljárás és a @@trancount. Ismerjük? Használjuk?

Amikor több tárolt eljárást kell egymásba ágyazni, amiket egyébként külön-külön is hívhat az alkalmazás, akkor szoktam elővenni a @@trancount-ot.
A nézőpontom az,  hogy 1 begin transaction legyen, és ne legyenek egymásba ágyazott tranzakciók. Az egymásba ágyazással az a baj, hogy ha egy közbenső tranzakció rollback-elve van, akkor az egész tranzakciós lánc rollback-elődik, viszont a kód fut tovább és a rollback utáni utasítások végre fognak hajtódni! Az ezután kiadott commit vagy rollback pedig már hibára fog futni.
Ezeket elkerülendően használnom a @@trancount változót arra, hogy biztosan csak 1 tranzakció nyiljon és csak 1 tranzakció záró utasítás fusson le.

 

Demozzunk egyet.
Készítünk egy t1 táblát, amibe 2 db SP ír. Első (usp_nested1) beszúr egy sort, a második (usp_nested2) frissíti ezt a sort. Bizonyos esetben az első sp tudja hívni a másodikat is, itt lesz az egymásba ágyazás.

 

Hozzuk létre a táblát.

 

USE [tempdb]
GO


IF OBJECT_ID('dbo.t1') IS NOT NULL
    DROP TABLE dbo.t1
GO

IF OBJECT_ID('dbo.usp_nested1') IS NOT NULL
DROP PROCEDURE dbo.usp_nested1
GO

IF OBJECT_ID('dbo.usp_nested2') IS NOT NULL
    DROP PROCEDURE dbo.usp_nested2
GO

CREATE TABLE dbo.t1 (
    id INT NOT NULL PRIMARY KEY,
    amount INT NOT NULL,
    created_at DATETIME NOT NULL
)
GO

Készítsük el a rosszul megírt tárolt eljárást:

CREATE PROCEDURE dbo.usp_nested1 
	@id INT,
	@amount INT,
	@created_at DATETIME,
	@additional_amount INT = NULL
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @trancount INT = @@TRANCOUNT
	DECLARE @result INT = 0


	BEGIN TRAN

	INSERT INTO [dbo].[t1] ([id], [amount], [created_at])
		VALUES (@id, @amount, @created_at);

	IF @additional_amount IS NOT NULL
	BEGIN
		EXEC dbo.usp_nested2 
			@id = @id, 
			@additional_amount = @additional_amount,
			@result = @result OUTPUT
	END

	-- Ez végre fog hajtódni
	INSERT INTO [dbo].[t1]
		    ( [id], [amount], [created_at] )
	VALUES  ( 0, 0, '2010-01-01')

	SELECT 
            @@TRANCOUNT AS trancount, 
            @@NESTLEVEL AS nestedlevel, 
            OBJECT_NAME(@@PROCID, DB_ID()) AS spname


	IF @result = 0
		COMMIT
	ELSE 
		ROLLBACK
END
GO


CREATE PROCEDURE dbo.usp_nested2 
	@id INT,
	@additional_amount INT,
	@result INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @trancount INT = @@TRANCOUNT
	DECLARE @amount INT = 0

		BEGIN TRAN

		UPDATE [dbo].[t1]
		SET @amount = [amount] += @additional_amount
		WHERE [id] = @id

		SELECT 
                    @@TRANCOUNT AS trancount, 
                    @@NESTLEVEL AS nestedlevel, 
                    OBJECT_NAME(@@PROCID, DB_ID()) AS spname

		IF @amount > 10
		BEGIN
			SET @result = 1
        END

		IF @result = 0
			COMMIT
		ELSE 
			ROLLBACK

END
GO

Az usp_nested2 tárolt eljárás ROLLBACK-elni fog, ha adott sorban az amount értéke 10 főlé menne.  (Itt új sor lesz, így ha a @amount + additional_amount > 10 akkor rollback). Futtassuk.

EXEC dbo.[usp_nested1] 
    @id = 11,
    @amount = 10, 
    @created_at = '2010-01-01',
    @additional_amount = 1

Az eredmény egy nagy error:
01_error

És egy olyan sor, amit nem akartunk beszúrni:
02_inserted_row

Egészítsük ki a tárolt eljárást @@trancount változóval és tegyük bele a logikát, hogy csak 1 tranzakció nyitó és záró utasítás legyen.

ALTER PROCEDURE dbo.usp_nested1 
	@id INT,
	@amount INT,
	@created_at DATETIME,
	@additional_amount INT = null
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @trancount INT = @@TRANCOUNT
	DECLARE @result INT = 0

	IF @trancount = 0 
		BEGIN TRAN

		INSERT INTO [dbo].[t1] ([id], [amount], [created_at])
			VALUES (@id, @amount, @created_at);

		IF @additional_amount IS NOT NULL
		BEGIN
			EXEC dbo.usp_nested2 
				@id = @id, 
				@additional_amount = @additional_amount,
				@result = @result OUTPUT
		END

		SELECT 
                   @@TRANCOUNT AS trancount, 
                   @@NESTLEVEL AS nestedlevel, 
                   OBJECT_NAME(@@PROCID, DB_ID()) AS spname

	IF @trancount = 0 AND @@TRANCOUNT > 0
		IF @result = 0
			COMMIT
		ELSE 
			ROLLBACK
END
GO


ALTER PROCEDURE dbo.usp_nested2 
	@id INT,
	@additional_amount INT,
	@result INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @trancount INT = @@TRANCOUNT
	DECLARE @amount INT = 0

	IF @trancount = 0
		BEGIN TRAN

		UPDATE [dbo].[t1]
		SET @amount = [amount] += @additional_amount
		WHERE [id] = @id

		IF @amount > 10
			SET @result = 1


		-- Ez NEM fog végre hajtódni
		INSERT INTO [dbo].[t1]
				( [id], [amount], [created_at] )
		VALUES  ( -1, -1, '2010-01-01')

		SELECT 
                    @@TRANCOUNT AS trancount, 
                    @@NESTLEVEL AS nestedlevel, 
                    OBJECT_NAME(@@PROCID, DB_ID()) AS spname

	IF @trancount = 0 AND @@TRANCOUNT > 0
		IF @result = 0
			COMMIT
		ELSE
			ROLLBACK
END
GO

Ha ismét futtatjuk a tárolt eljárást és a tábla lekérdezést, akkor már nem kapunk se hibát, se “véletlen beszúrt” sort.

A tárolt eljárásokban nem a tökéletes kód volt a cél, hanem a @@trancount használatának bemutatása az egymásba ágyazott tárolt eljárásokban.
Megoldás lehet még a try-catch blokk használata, de én nem vagyok híve, hogy hibát generáljunk ott, ahol erre nincs szükség.