MSSQL – IT блог

Повреден лог в SQL Server Express

В случай, че база не може да се закачи към SQL Server с подобна грешка:
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

				1

						The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

и стандартната команда с ATTACH_REBUILD_LOG не работи, може да се използва ATTACH_FORCE_REBUILD_LOG, която ще създаде лог-а при всички ситуации.
Transact-SQL
CREATE DATABASE SQLAuthority ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority_original.mdf')
FOR ATTACH_FORCE_REBUILD_LOG

				1
2

						CREATE DATABASE SQLAuthority ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority_original.mdf')
FOR ATTACH_FORCE_REBUILD_LOG

Взето от тук:
https://blog.sqlauthority.com/2016/11/04/sql-server-unable-attach-database-file-activation-failure-log-cannot-rebuilt/

Parameter sniffing или защо понякога заявките в SSMS са бързи, а в приложението не

SQL Server има оптимизация, която се нарича parameter sniffing, която запазва и кешира query планове при извикване и компилиране на stored procedure за конкретни параметри.

По подразбиране .NET приложенията използват малко по-различни параметри отколкото SSMS например:

За приложения:

set arithabort off

1	set arithabort off

за SSMS:

set arithabort on

1	set arithabort on

Съответно в някои случаи заявките се оптимизират грешно за .NET приложения (с arithabort off), но в SSMS работят наред, тъй като няма натрупана статистика, за да се оптимизират.

Бързо решение на проблема (временно!) е да се изпълни

DBCC FREEPROCCACHE

1	DBCC FREEPROCCACHE

Това изчиства кеша на сторнатите процедури и започва наново да се съхраняват. Тези статистики се пазят в паметта, поради което рестарт на сървъра също върши работа.

Има и други опции, като да се добавят различни опции към заявката като use recompile, option recompile, optimize for unknown и т.н.

По-глобално решение би било използването на Trace flag 4136 или

<span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">DATABASE</span> SCOPED CONFIGURATION
{
    PARAMETER_SNIFFING <strong>=</strong> { <strong>ON</strong> | OFF | PRIMARY}
}

ALTER DATABASE SCOPED CONFIGURATION

{

PARAMETER_SNIFFING = { ON | OFF | PRIMARY}

}

По-долу има подробна информация за този (и други случаи) и различни решения на проблема:

http://www.sommarskog.se/query-plan-mysteries.html

Сериозен недостатък при .NET приложения и EF е генерирането на dynamic SQL, който използва sp_executesql и лесно може да възникне такъв проблем.

Конфигуриране на TDE за SQL Server

SQL скрипт за създаване на TDE (Transparent Data Encryption) и осигуряване на “Encrypt the data at rest”.

Информацията в базата данни е криптирана и ако някой намери backup на базата няма да може да я използва.

Друга положителна страна е, че приложението,което достъпва данните няма нужда се променя и продължава да работи както и досега.

Криптирането на базата е онлайн, прозрачно и може да се проследи до къде е стигнало чрез изпълнение на query към dmv таблица.

Важно е да се запазят ключовете и сертификата, за да може базата данни да се прочете, ако се премести на друг сървър!

-- Създаване на master key със силна парола
USE master;
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'strong_password@!123';
GO

-- Резервно копие
USE master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'strong_password@!123';
BACKUP MASTER KEY TO FILE = 'd:\DB\service_master.key'
    ENCRYPTION BY PASSWORD = 'strong_password@!123';
GO

-- Създаване на сертификат
USE master;
CREATE CERTIFICATE TDEEInvoiceCert 
WITH SUBJECT = 'TDE eInvoice Certificate',
EXPIRY_DATE='2030-12-31'
GO

-- Резервно копие на сертификата
BACKUP CERTIFICATE TDEEInvoiceCert TO FILE = 'd:\db\eInvoice.cer'  
    WITH PRIVATE KEY ( FILE = 'd:\db\eInvoice.key' ,   
    ENCRYPTION BY PASSWORD = 'strong_password@!123' );  
GO  

-- Използване на сертификата за криптиране на базата
USE EINVOICE;

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDEEInvoiceCert
GO


ALTER DATABASE EINVOICE  
SET ENCRYPTION ON;

-- Статус на криптирането
select percent_complete, * from sys.dm_database_encryption_keys

-- Създаване на master key със силна парола

USE master;

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'strong_password@!123';

-- Резервно копие

USE master;

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'strong_password@!123';

BACKUP MASTER KEY TO FILE = 'd:\DB\service_master.key'

ENCRYPTION BY PASSWORD = 'strong_password@!123';

-- Създаване на сертификат

USE master;

CREATE CERTIFICATE TDEEInvoiceCert

WITH SUBJECT = 'TDE eInvoice Certificate',

EXPIRY_DATE='2030-12-31'

-- Резервно копие на сертификата

BACKUP CERTIFICATE TDEEInvoiceCert TO FILE = 'd:\db\eInvoice.cer'

WITH PRIVATE KEY ( FILE = 'd:\db\eInvoice.key' ,

ENCRYPTION BY PASSWORD = 'strong_password@!123' );

-- Използване на сертификата за криптиране на базата

USE EINVOICE;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDEEInvoiceCert

ALTER DATABASE EINVOICE

SET ENCRYPTION ON;

-- Статус на криптирането

select percent_complete, * from sys.dm_database_encryption_keys

-- Инсталиране на сертификата на друг сървър

-- На другия сървър също трябва да се създаде master key
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'strong_password@!123';
GO

-- Импортиране на сертификата от файловете: http://sqlity.net/en/2384/restore-certificate/
CREATE CERTIFICATE TDEGdinCert FROM FILE ='D:\eInvoice.cer'
WITH PRIVATE KEY ( FILE='D:\eInvoice.key',
DECRYPTION BY PASSWORD='strong_password@!123' );
GO

-- Инсталиране на сертификата на друг сървър

-- На другия сървър също трябва да се създаде master key

USE master;

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'strong_password@!123';

-- Импортиране на сертификата от файловете: http://sqlity.net/en/2384/restore-certificate/

CREATE CERTIFICATE TDEGdinCert FROM FILE ='D:\eInvoice.cer'

WITH PRIVATE KEY ( FILE='D:\eInvoice.key',

DECRYPTION BY PASSWORD='strong_password@!123' );