SQL Server 2019 Administration Inside Out

SQL Server 2019 Administration Inside Out Paperback – release date: 2020 by Randolph West, Melody Zacharias, William Assaf, Sven Aelterman

Conquer SQL Server 2019 administration–from the inside out

Dive into SQL Server 2019 administration–and really put your SQL Server DBA expertise to work. This supremely organized reference packs hundreds of time-saving solutions, tips, and workarounds–all you need to plan, implement, manage, and secure SQL Server 2019 in any production environment: on-premises, cloud, or hybrid. SQL Server experts offer a complete tour of DBA capabilities available in SQL Server 2019 Database Engine, SQL Server Data Tools, SQL Server Management Studio, and via PowerShell. Discover how experts tackle today’s essential tasks–and challenge yourself to new levels of mastery.

Install, customize, and use SQL Server 2019’s key administration and development tools
Manage memory, storage, clustering, virtualization, and other components
Architect and implement database infrastructure, including IaaS, Azure SQL, and hybrid cloud configurations
Provision SQL Server and Azure SQL databases
Secure SQL Server via encryption, row-level security, and data masking
Safeguard Azure SQL databases using platform threat protection, firewalling, and auditing
Establish SQL Server IaaS network security groups and user-defined routes
Administer SQL Server user security and permissions
Efficiently design tables using keys, data types, columns, partitioning, and views
Utilize BLOBs and external, temporal, and memory-optimized tables
Master powerful optimization techniques involving concurrency, indexing, parallelism, and execution plans
Plan, deploy, and perform disaster recovery in traditional, cloud, and hybrid environments

For Experienced SQL Server Administrators and Other Database Professionals

26th March 201926th March 2019

Adatbázis méret csökkentése – Shrink a Database

Adatbázis fájlok méretét ritkán csökkentjük, hiszen többnyire úgy is megtelik új adatokkal , indexekkel. Érdemes csökkenteni a méretet például ha nagyon sok adatot kipucoltunk az adatbázisból és csak nagyon sokára fog újra felhízni..

-- lényeg, hogy kis adagokban csökkentse a db-t mert soká tarthat és beterhel, valamint trunzactional  db is nő!!

DECLARE @DataFileName VARCHAR(255); 

DECLARE @starttime datetime
DECLARE @endtime datetime
SET @starttime =getdate()
SET @DataFileName = (SELECT name FROM sysfiles WHERE groupid = 1);
DECLARE @TargetSize INT; 

SET @starttime =getdate()
SET @TargetSize = ROUND(8 * (SELECT size FROM sysfiles WHERE groupid = 1) / 1024, 0) - 500;   -- Select current size and substract 500 MB
SELECT @DataFileName as datafiename,@TargetSize as targetsize;
EXEC ('DBCC SHRINKFILE (' + @DataFileName + ', ' + @TargetSize + ')');
SET @endtime =getdate()
SELECT DATEDIFF(millisecond,@starttime,@endtime) AS elapsed_ms;

-- lényeg, hogy kis adagokban csökkentse a db-t mert soká tarthat és beterhel, valamint trunzactional db is nő!!

DECLARE @DataFileName VARCHAR(255);

DECLARE @starttime datetime

DECLARE @endtime datetime

SET @starttime =getdate()

SET @DataFileName = (SELECT name FROM sysfiles WHERE groupid = 1);

DECLARE @TargetSize INT;

SET @starttime =getdate()

SET @TargetSize = ROUND(8 * (SELECT size FROM sysfiles WHERE groupid = 1) / 1024, 0) - 500; -- Select current size and substract 500 MB

SELECT @DataFileName as datafiename,@TargetSize as targetsize;

EXEC ('DBCC SHRINKFILE (' + @DataFileName + ', ' + @TargetSize + ')');

SET @endtime =getdate()

SELECT DATEDIFF(millisecond,@starttime,@endtime) AS elapsed_ms;

Az adatábzis méretet mindig óvatosan csökkentsük, lehet hogy 500 MB üres terület kivétele pillanatok alatt , pár másodperc alatt megtörténik, majd máskor az adatok áthelyezése miatt percekig fut akárcsak 1 MB átszervezése.

Javaslat: kisebb MB-ok csökkentésével kezdjük, majd növeljük a shrink méretet. Amikor elkezd nőni a végrehajtási idő , akkor csökkentsük a shrink méretet.

10th January 201916th January 2019

Adatbázis audit

DevOps környezetben vagy éles környezetben érdemes auditot beállítani. Saját érdekünkben érdemes tudni, hogy ki nyúlt bele és mit csinált!

CREATE SERVER AUDIT [Audit-20190110]
TO FILE
( FILEPATH = N'H:\Audit\'
,MAXSIZE = 100 MB
,MAX_FILES = 500
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 3000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit-20190110] WITH (STATE = ON)
GO

USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190110]
FOR SERVER AUDIT [Audit-20190110]
-- ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_MIRRORING_LOGIN_GROUP),
-- ADD (LOGOUT_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (SERVER_OPERATION_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON)
GO

--Create the database Audit spec
CREATE DATABASE AUDIT SPECIFICATION [dbaudit]
FOR SERVER AUDIT [Audit-20190110]
ADD ( AUDIT_CHANGE_GROUP )
,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)
,ADD ( BACKUP_RESTORE_GROUP )
,ADD ( DATABASE_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP )
,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )
,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )
,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
,ADD ( DBCC_GROUP )
,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )
,ADD ( SCHEMA_OBJECT_CHANGE_GROUP )
,ADD ( SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP )
,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
,ADD ( SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP )
-- 2012-newer allowed events
,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )
   ,ADD ( USER_DEFINED_AUDIT_GROUP )
   --,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )

WITH ( STATE = ON);

GO

SELECT event_time
,action_id
,session_server_principal_name AS UserName
,server_instance_name
,database_name
,schema_name
,object_name
,statement
FROM sys.fn_get_audit_file('H:\Audit*.sqlaudit', DEFAULT, DEFAULT)
-- WHERE action_id IN ( 'SL', 'IN', 'DR', 'LGIF' , '%AU%' )

CREATE SERVER AUDIT [Audit-20190110]

TO FILE

( FILEPATH = N'H:\Audit\'

,MAXSIZE = 100 MB

,MAX_FILES = 500

,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 3000

,ON_FAILURE = CONTINUE

)

ALTER SERVER AUDIT [Audit-20190110] WITH (STATE = ON)

USE [master]

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190110]

FOR SERVER AUDIT [Audit-20190110]

-- ADD (DATABASE_OBJECT_ACCESS_GROUP),

ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),

ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),

ADD (BACKUP_RESTORE_GROUP),

ADD (AUDIT_CHANGE_GROUP),

ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),

ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),

ADD (FAILED_LOGIN_GROUP),

ADD (DATABASE_MIRRORING_LOGIN_GROUP),

-- ADD (LOGOUT_GROUP),

ADD (DATABASE_CHANGE_GROUP),

ADD (DATABASE_OBJECT_CHANGE_GROUP),

ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_CHANGE_GROUP),

ADD (SERVER_OBJECT_CHANGE_GROUP),

ADD (SERVER_OPERATION_GROUP),

ADD (LOGIN_CHANGE_PASSWORD_GROUP),

ADD (SERVER_STATE_CHANGE_GROUP),

ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),

ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),

ADD (USER_CHANGE_PASSWORD_GROUP)

WITH (STATE = ON)

--Create the database Audit spec

CREATE DATABASE AUDIT SPECIFICATION [dbaudit]

FOR SERVER AUDIT [Audit-20190110]

ADD ( AUDIT_CHANGE_GROUP )

,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)

,ADD ( BACKUP_RESTORE_GROUP )

,ADD ( DATABASE_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP )

,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )

,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )

,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )

,ADD ( DBCC_GROUP )

,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )

,ADD ( SCHEMA_OBJECT_CHANGE_GROUP )

,ADD ( SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP )

,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )

,ADD ( SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP )

-- 2012-newer allowed events

,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )

,ADD ( USER_DEFINED_AUDIT_GROUP )

--,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )

WITH ( STATE = ON);

SELECT event_time

,action_id

,session_server_principal_name AS UserName

,server_instance_name

,database_name

,schema_name

,object_name

,statement

FROM sys.fn_get_audit_file('H:\Audit*.sqlaudit', DEFAULT, DEFAULT)

-- WHERE action_id IN ( 'SL', 'IN', 'DR', 'LGIF' , '%AU%' )

14th November 2018

Nem használt oszlopok – Empty columns

Default Null értékű oszlopok , ahol minden Null értékű:

    -- Returns a list of all columns in current database
    -- where the column's value is null for all records.
    
	declare @tempTable TABLE
    (
        TableSchema nvarchar(256),
        TableName nvarchar(256),
        ColumnName sysname,
        NotNullCnt bigint
    );
     
    declare @sql nvarchar(4000);
    declare @tableSchema nvarchar(256);
    declare @tableName nvarchar(256);
    declare @columnName sysname;
    declare @cnt bigint;
     
    declare columnCursor cursor FOR
        SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
        WHERE 
		-- TABLE_NAME ='tablename' and
		IS_NULLABLE = 'YES';
     
    open columnCursor;
     
    fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;
     
    while @@FETCH_STATUS = 0
    begin
        -- use dynamic sql to get count of records where column is not null
        SET @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
            '] where [' + @columnName + '] is not null';
        print @sql; --uncomment for debugging
        exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;
     
        INSERT INTO @tempTable SELECT @tableSchema, @tableName, @columnName, @cnt;
     
        fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;
    end
     
    close columnCursor;
    deallocate columnCursor;
     
    SELECT * FROM @tempTable WHERE NotNullCnt = 0;

-- Returns a list of all columns in current database

-- where the column's value is null for all records.

declare @tempTable TABLE

(

TableSchema nvarchar(256),

TableName nvarchar(256),

ColumnName sysname,

NotNullCnt bigint

);

declare @sql nvarchar(4000);

declare @tableSchema nvarchar(256);

declare @tableName nvarchar(256);

declare @columnName sysname;

declare @cnt bigint;

declare columnCursor cursor FOR

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

WHERE

-- TABLE_NAME ='tablename' and

IS_NULLABLE = 'YES';

open columnCursor;

fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;

while @@FETCH_STATUS = 0

begin

-- use dynamic sql to get count of records where column is not null

SET @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +

'] where [' + @columnName + '] is not null';

print @sql; --uncomment for debugging

exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

INSERT INTO @tempTable SELECT @tableSchema, @tableName, @columnName, @cnt;

fetch next FROM columnCursor INTO @tableSchema, @tableName, @columnName;

end

close columnCursor;

deallocate columnCursor;

SELECT * FROM @tempTable WHERE NotNullCnt = 0;

10th November 201810th November 2018

Table size

Tábla méretek , használt tartalommal:

-- table sizes , tábla méretek egy adatbázisban
SELECT 
    @@SERVERNAME  AS servername,
    DB_NAME ( )  AS dbname,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
	,format(getdate(), 'yyyy-MM-dd') as Datum
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB desc , UsedSpaceKB desc, TableName desc

-- table sizes , tábla méretek egy adatbázisban

SELECT

@@SERVERNAME AS servername,

DB_NAME ( ) AS dbname,

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

,format(getdate(), 'yyyy-MM-dd') as Datum

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

TotalSpaceMB desc , UsedSpaceKB desc, TableName desc

25th September 201825th September 2018

Nyomtatott informatikai könyvek

Microsoft könyveknél baromi drága a szállítási költség Európába, hiába a “nagy” mcp-s kedvezmény. Több könyvnél már lehet számolgatni. Ms press könyveket Amazonon célszerű megrendelni , érdemes összehasonlítani az amazon.com, amazon.de és amazon.co.uk árait, majd hozzákalkulálni a fontosságtól függően a szállítás időtartamát.
Pro SQL Server on Linux
Apress kiadó: gyakori az ebook akció, drágák a nyomtatottak, de.. évente egyszer a Black Friday keretében napokon keresztül óriási akcióval ,12 eurós áron beszerezhető minden egyes könyv és mindezt ingyenes szállítás mellett. Ez a legszuperebb ajánlat, az ember már-már hitetlenkedik, hogy megéri e a kiadónak! Ott a trükk, hogy a célkontinensen, esetünkben Európában nyomtatják ki a könyvet és úgy postázzák! 🙂
Packtpub, Packt Publishing kiadó könyvei elsősorban az ebook eladásra koncentrálnak, gyakori az 10 euró körüli ebook “akció”, könyvek viszont drágák. Ennél a kiadónál viszont érdemes cupon kódokat keresni. Múlt héten rendeltem könyveket és 35%-os kedvezményes kódot találtam, ami az ingyenes szállítás mellet már nagyon jó! Működő kód (írd egybe,azaz kötőjeleket szedd ki): AI-NOW-PRINT-35 . SQL-es és Azuros könyvekre is adta a kedvezményt.

13th September 201813th September 2018

Virtuális szerverek ip címei

Gyakori eset, hogy a laptop hurcolásával más és más ip címet kapnak a virtuális szervereink. Host gépen az alábbi powershell paranccsal tudjuk kiíratni a guest gépek ip címeit:

 Get-VM -ComputerName "mycomputername" | Get-VMNetworkAdapter

1	Get-VM -ComputerName "mycomputername" \| Get-VMNetworkAdapter

10th September 201810th September 2018

SQL Server 2018? SQL Server 2019!

A következő SQL server verzió mégsem 2018 lesz ( az igényeknek megfelelően egyre gyakrabban adjuk ki az új verziókat ígéret), hanem SQL Server 2019 néven fog megjelenni. (hasonlóan Windwos Server 2019, Office 2019).

25th July 20182nd August 2018

HEAP table is fragmented

Általában nem ajánlott HEAP tábla létrehozása (Table without Clustered Index) , egy esetben jöhet jól, ha nagyon gyors adatbetöltés szükséges.
A Heap tábla azonban töredezik, ennek oka, hogy minden Insert a tábla végére kerül és a Deleted sorok sem kerülnek betöltésre.
Amíg a hagyományos indexeket egy index reorganizációval vagy újraépítéssel (rebuild) karbantarthajtuk és gyorssá tehetjük addig a Heap táblán nincs ilyen beépített karbantartás .
Teendő?

Continue reading “HEAP table is fragmented”

6th July 2018

Két lekérdezés eredményének összehasonlítása

Két lekérdezés különbsége:

select * from (select * from query1) as query1
except
select * from (select * from query2) as query2

select * from (select * from query1) as query1

except

select * from (select * from query2) as query2

Szükség esetén az order by elhagyandó a lekérdezésekben.