In log shipping omgevingen bestaat regelmatig de wens om een (service-)account
uitsluitend leesrechten te geven op de secondary database, terwijl dit account
geen toegang mag hebben tot de primary database.
Typische use-cases zijn rapportages, exports of monitoring die bewust buiten
de productieomgeving worden gehouden.

SQL Server Log Shipping leent zich hier goed voor, omdat een log shipping secondary
geen automatische failover kent. Een eventuele promotie van de secondary
naar primary gebeurt altijd handmatig en bewust, wat deze inrichting
goed beheersbaar maakt.

Uitgangspunten

• Log Shipping is een Disaster Recovery mechanisme, geen High Availability oplossing
• De secondary database staat in STANDBY / READ ONLY modus
• Er vindt geen automatische rolwissel plaats tussen primary en secondary
• Deze inrichting is bedoeld voor leesgeoriënteerde, niet-kritische workloads

Technische achtergrond

Bij SQL Server Log Shipping gelden de volgende technische eigenschappen:

• Database-objecten en database-rechten worden doorgezet via transaction log restores
• Logins zijn server-level objecten en worden niet meegenomen in log shipping
• Toegang wordt bepaald door:
  • de status van de login (enabled / disabled)
  • de aanwezigheid van een database user

Dit onderscheid maakt het mogelijk om toegang functioneel te beperken tot uitsluitend
de secondary database.

De inrichting

Stap 1 – Maak een Active Directory service account aan

Gebruik één vast AD-account dat bedoeld is voor rapportage- of leesdoeleinden.

Stap 2 – Maak de login aan op beide SQL Servers

Maak dezelfde login aan op:

• De primary SQL Server instance
• De secondary SQL Server instance

Omdat het om een AD-account gaat, is de SID op beide servers gelijk.

Stap 3 – Maak een database user aan in de primary database

• Koppel de database user aan de login
• Geef uitsluitend db_datareader rechten

Deze database-rechten worden via log shipping automatisch doorgezet naar
de secondary database.

Stap 4 – Disable de login op de primary SQL Server

Door de login te disablen op de primary SQL Server:

• kan het account niet verbinden met de primary instance
• blijven de database-rechten wel bestaan

Stap 5 – Laat de login enabled op de secondary SQL Server

Op de secondary SQL Server blijft de login enabled, waardoor:

• verbinding met de secondary database mogelijk is
• alleen leesacties kunnen worden uitgevoerd

Resultaat

• Het account heeft leesrechten op de secondary database
• Het account kan niet verbinden met de primary database
• Rapportage- en leesprocessen zijn gescheiden van productie
• De inrichting blijft overzichtelijk en beheersbaar

Beheer en aandachtspunten

Wanneer een log shipping secondary handmatig wordt gepromoveerd tot primary
(bijvoorbeeld in een DR-situatie), is het belangrijk om:

• de status van de login opnieuw te beoordelen
• expliciet te bepalen of het account toegang mag behouden

Omdat dit een bewuste beheeractie is, kan dit eenvoudig worden opgenomen
in bestaande DR-procedures.

Conclusie

Binnen een log shipping omgeving is het technisch en operationeel goed
verdedigbaar om een account uitsluitend toegang te geven tot de secondary database
door het combineren van database-rechten met een gecontroleerde login-status.

Deze oplossing past bij de aard van log shipping als DR- en reporting-mechanisme
en biedt een praktische en veilige scheiding tussen productie en leesworkloads,
zonder de complexiteit van Always On Availability Groups.

Microsoft heeft twee belangrijke kwetsbaarheden in de beveiliging binnen Microsoft SQL Server gevonden en deze twee issues met een security update gedicht. Het gaat onder andere om een security update binnen de SQL Server Agent, waarbij met behulp van SQL Injectie een aanvaller sysadmin-rechten op de database server zou kunnen verkrijgen.

Er is bovendien met deze security update een lek binnen linked servers gedicht. SQLTeam.NL adviseert uit performance overwegingen nooit linked servers te gebruiken en data op andere manieren binnen data platformen te verplaatsen; bijvoorbeeld door gebruik van SSIS packages te maken.

De kwetsbaarheden zijn in deze link (in het Engels) beschreven. Het artikel bevat ook de links naar alle downloads voor elke versie van SQL Server. Ik adviseer deze update zo spoedig mogelijk in acceptatie te testen en vervolgens in productie te nemen.

Jarenlang zijn multi-dimensionale kubussen de standaard geweest. Uw organisatie is er nu klaar voor om tabular models in te zetten. Daarvoor moet de analysis server worden omgezet van een multidimensional instance naar een tabular instance. Hoe werkt dat?

Microsoft beschrijft in deze link dat het omzetten van de DeploymentMode property in het bestand msmdsrv.ini niet wordt ondersteund. In principe zal Microsoft SQL Server Analysis Service opnieuw op de server moeten worden geïnstalleerd, nadat de multidimensional instance is verwijderd, maar…

Het omzetten van de DeploymentMode property in het bestand msmdsrv.ini werkt wél. Voor een test- of acceptatieomgeving of om tabular models tijdelijk te testen is het omzetten van deze eigenschap dus een prima oplossing die veel tijd scheelt. Hoe werkt het precies?

• Ga met verkenner op de analysis server naar de folder C:\Program Files\Microsoft SQL Server\[uw versie]\OLAP\Config
• Maak een kopie van het bestand msmdsrv.ini naar bijvoorbeeld de temp-folder
• Open het bestand in de temp-folder
• Zoek in het bestand naar DeploymentMode
• Wijzig de waarde naar behoefte: 0=Multidimensional en 2=Tabular
• Sla het bestand op
• Maak een backup van het originele bestand in de SSAS folder
• Kopieer vervolgens het aangepaste bestand uit de temp-folder naar de SSAS-folder toe
• Herstart de analysis server

In de msdb database worden veel processen van microsoft vastgelegd, onder andere systeembrede stored procedures en ook logging van de SQL agent en database mail. Ongemerkt groeit deze database heel snel heel hard en zit deze database in no-time op enkele tientallen gigabyte grootte. Bij goed SQL server beheer hoort dat niet. Die groei moet in de perken worden gehouden.

Waardoor groeit msdb
De groei van de msdb database kent vele oorzaken. De oorzaken die de meeste groei veroorzaken zijn:

• De historie van SQL agent jobs
• De logs van database mail
• De logs van backups
• De logs van logshipping procedures

Wat kunnen we eraan doen?
Wanneer u nog geen onderhoud op de msdb database heeft gepleegd, is het handig om eerst de volgende punten vast te stellen:

• Hoe groot is de msdb database nu?
• Hoeveel van die ruimte is er al vrij?
• Hoe groot is msdb database op de storage?
• Wat zijn mijn grootste tabellen in de database?

Het vast stellen van de bovenstaande punten kan het makkelijkst via SQL Server Management Studio. Door met de rechtermuis klik op de msdb database te klikken, kan er voor Reports worden gekozen. De relevante rapporten zijn Disk Usage en Disk Usage by Table. Dit geeft een goed beeld van de database. Wanneer de database groter dan 5GB is, wijst dat al op excessieve historische logging. Per tabel kan vervolgens worden gekeken hoeveel records er in de tabel zitten en hoeveel ruimte die tabel in neemt. Met onderstaande query kan bijvoorbeeld worden gekeken hoeveel stappen er per SQL agent job zijn gedraaid in het verleden.

SELECT
	sj.name			AS [SQL Server Agent job],
	count(sjh.step_name)	AS [Aantal stappen in de historie]
FROM dbo.sysjobhistory sjh WITH (NOLOCK)
JOIN dbo.sysjobs sj ON sjh.job_id = sj.job_id
GROUP BY sj.name
ORDER BY sj.name;
[/code>]

<strong>Opschonen historische SQL agent jobs</strong>
Voor het opschonen van historische SQL agent jobs wordt de stored procedure <em>sp_purge_jobhistory</em> gebruikt. Deze stored procedure kan de hele historie van één job opruimen of de historie van alle jobs tot en met een bepaalde datum; of een combinatie hiervan. Globaal kan daar onderstaande query voor worden gebruikt
[code language="sql"]
DECLARE @Peildatum datetime = (SELECT DATEADD(MONTH,-1,GETDATE()))
EXECUTE [msdb].[dbo].[sp_purge_jobhistory]
--	@job_name = 'LSRestore_CS-DT1712014_HiX62_Support',
	@oldest_date = @Peildatum

Opschonen database mail
Voor het opschonen van database mail worden de stored procedures sysmail_delete_log_sp en sysmail_delete_mailitems_sp gebruikt. Voorbeeld query:

DECLARE @Peildatum datetime = (SELECT DATEADD(MONTH,-1,GETDATE()))
EXECUTE sysmail_delete_log_sp @logged_before = @Peildatum
EXECUTE sysmail_delete_mailitems_sp @sent_before = @Peildatum

Opruimen van de logshipping historie
Voor het opschonen van historische backup data wordt de stored procedure sp_delete_backuphistory gebruikt. Voorbeeld query:

DECLARE @Peildatum datetime = (SELECT DATEADD(MONTH,-1,GETDATE()))
EXECUTE sp_delete_backuphistory @oldest_date = @Peildatum

Opruimen van de logshipping historie
Voor het opschonen van historische logshipping data wordt de stored procedure sp_cleanup_log_shipping_history gebruikt.
LET OP: deze stored procedure draait in de master database! Voorbeeld query:

DECLARE @database nvarchar(100)
DECLARE @agent_id nvarchar(100)
DECLARE @agent_type int
DECLARE @NumberOfRecords int

DECLARE agent_cursor CURSOR FOR
	SELECT DISTINCT
		database_name,
		agent_id,
		agent_type,
		COUNT(*) AS NumberOfRecords
	FROM msdb.dbo.log_shipping_monitor_history_detail
	GROUP BY database_name,agent_id,agent_type
--	ORDER BY database_name, agent_id, agent_type

OPEN agent_cursor
FETCH NEXT FROM agent_cursor
INTO @database, @agent_id, @agent_type, @NumberOfRecords

IF @@FETCH_STATUS <> 0
PRINT 'No records'

WHILE @@FETCH_STATUS = 0
BEGIN
execute master.dbo.sp_cleanup_log_shipping_history 
	@agent_id = @agent_id,
	@agent_type = @agent_type;
	FETCH NEXT FROM agent_cursor
	INTO @database, @agent_id, @agent_type, @NumberOfRecords
END

CLOSE agent_cursor
DEALLOCATE agent_cursor

En dan…? De msdb database is nog steeds niet kleiner geworden (op de storage)
Dat klopt! Verwijderen van data uit tabellen zorgt er alleen maar voor dat er ruimte binnen de database vrijkomt, die dan eerst weer hergebruikt wordt. Met de functie SHRINKFILE kan de database ook op de storage kleiner worden gemaakt. De meningen zijn erg verdeeld of het gebruik van de functie SHRINKFILE wél of niet verstandig is. Deze discussie moet u binnen uw eigen organisatie voeren. Ik leg u in deze bijdrage alleen uit, hoe de functie wordt gebruikt. De query’s:

-- We gaan naar de msdb database
USE msdb;
GO

-- We verkleinen eerst de data file
DBCC SHRINKFILE (N'MSDBData' , 500);
GO

-- We verkleinen dan de log file
DBCC SHRINKFILE (N'MSDBLog' , 200);
GO

-- Daarna is het verstandig de indexen opnieuw op te bouwen
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)";
GO

Het opnieuw indexeren van de tabellen is een belangrijk onderdeel van de procedure om ervoor te zorgen dat eventuele fragmentatie van de database tabellen wordt tegen gegaan.

Nu is de msdb database weer behapbaar. Om dit zo te houden, is mijn advies om alle opruimquery’s in de SQL agent op te nemen en de opruimacties maandelijks uit te voeren. Dat zou uw msdb database in topconditie moeten houden.

Het volgende scenario doet zich op de SQL Server database server voor waar de logship database op staat:

• De LSAlert job geeft aan dat de logship database niet is bijgewerkt en ‘out-of-sync’ is
• De LSRestore job geeft een groen vinkje om aan te geven dat die job goed heeft gelopen
• De logship database is bijgewerkt
• Recente data uit de brondatabase is ook in de logship database zichtbaar en beschikbaar

Maar … de logship database staat op een SQL Server versie die lager is dan de brondatabase. Bijvoorbeeld de logship database draait op SQL Server versie 2016 en de brondatabase draait (al) op SQL Server versie 2022.

Dit kan tot problemen leiden, omdat de database structuur van systeem databases (zoals bijvoorbeeld msdb) waar SQL Server logging naar toe schrijft een andere structuur hebben (bijvoorbeeld nieuwe velden in systeemtabellen of velden die naar andere systeemtabellen zijn verplaatst). Het zou dan kunnen dat de LSRestore job dan gegevens probeert weg te schrijven die in de nieuwere versie van SQL Server niet meer van toepassing zijn of zijn verplaatst.

Valt dit dan nergens op? Tot nu toe heeft SQLTeam een foutmelding in de schijnbaar goed afgeronde LSRestore job gevonden. Namelijk de foutmelding:

*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data) ***
*** Error: Object must implement IConvertible.(mscorlib) ***

Het niet kunnen wegschrijven van log historie en foutmeldingen uit bovenstaande melding. Triggert de LSAlert job die van deze logging gebruik maakt om te kijken of een logship database is bijgewerkt. Hoewel de melding uit de LSAlert job dus inderdaad onterecht is, is dat in deze situatie goed te verklaren. Bovenstaande foutmelding doet zich voor wanneer de logship database op een lagere versie draait dan de brondatabase.

Onderstaande foutmelding doet zich voor wanneer de logship database op een hogere versie van SQL Server staat dan de brondatabase.

2024-07-23 03:25:00.87 *** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2024-07-23 03:25:00.89 *** Error: Alter failed for Database '[Naam van de logship database]'. (Microsoft.SqlServer.Smo) ***
2024-07-23 03:25:00.89 *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2024-07-23 03:25:00.89 *** Error: Database '[Naam van de logship database]' cannot be opened. It is in the middle of a restore.(.Net SqlClient Data Provider) ***
2024-07-23 03:25:00.92 *** Error: Could not apply log backup file '[Naam en locatie van de transactielog.trn]' to secondary database '[Naam van de logship database]'.(Microsoft.SqlServer.Management.LogShipping) ***
2024-07-23 03:25:00.92 *** Error: This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Advies
Zorg ervoor dat de SQL versies van de brondatabase (primary) en de logship databases van die brondatabase(s) (secondary of secondaries) altijd gelijk aan elkaar is om dit soort situaties te voorkomen.

Enige tijd geleden was ik bezig met het deployen van kubussolutions naar een Microsoft SQL Server Analysis Server. Bij het deployen, verscheen de volgende melding An error has occurred, please contact your administrator \nInternal error: An unexpected error occurred (file ‘pcserialize.cpp’, line 1535, function ‘ASDatabase::Serialize’)

“An unexpected error”; typisch Microsoft waar je dus niets mee kunt. Na lang onderzoek bleek de oorzaak eigenlijk heel simpel: in het security tabblad van de server properties waren geen administrators opgenomen; zelfs geen BUILTIN\Administrators. Blijkbaar is het een goed idee om in dat tabblad in ieder geval één account te vermelden. Zo dus…

Na het toevoegen van een administrator in dit tabblad, rolden de kubussolutions wél goed uit.

Regelmatig komen we databases tegen die op de laatste versie van Microsoft SQL Server draaien en toch niet van de laatste technieken en inzichten gebruik kunnen maken. Met de verhuizing van een SQL Server database naar een nieuwe machine met de laatste SQL Server versie is het nog niet klaar! Dat geldt ook wanneer de SQL Server wordt bijgewerkt met de laatste versie van SQL Server. De betrokken database(s) moet(en) nog worden bijgewerkt!

Microsoft die zorgt ervoor dat een nieuwere versie van SQL Server niet direct impact heeft op de databases die op de SQL Server draaien. Elke database heeft op database niveau in het tabblad ‘Options’ de instelling ‘Compatibility Level’. Deze instelling zorgt ervoor dat de database zich gedraagt zoals die op de ‘oude’ versie van SQL Server is aangemaakt. Dit voorkomt dat de database opeens onder andere performance issues geeft of query plannen maakt, waar de database helemaal niet geschikt voor is.

Wanneer wordt de instelling “Compatibility Level” dan gewijzigd?
Na uitgebreide tests! De best-practice is om een kopie van de database op een testmachine te plaatsen en eerst daar deze instelling naar de hoogste versie te brengen om vervolgens alle relevante query’s en andere processen op de database te testen. Vervolgens kunnen op deze database dan de nodige wijzigingen worden gemaakt (en opnieuw worden getest) om de wijzigingen vervolgens op een geschikt moment in productie te brengen en dan pas in productie de instelling ‘Compatibility Level’ te wijzigen.

Moet de instelling worden gewijzigd?
Nee, het moet niet, voor zolang Microsoft de huidige compatibility level ondersteunt. Of het verstandig is om het niet te doen, is een tweede. Ik kan mij voorstellen dat ook uw organisatie met de laatste inzichten en technieken wilt werken. Dan is het echt noodzakelijk om de tests uit te voeren en uiteindelijk de productie-database naar het juiste niveau te brengen.