wiki

Инструменты пользователя

Инструменты сайта

Вы находитесь здесь: start » how-to » MSSQL
Вы посетили: • kubectl-image • MSSQL
how-to:mssql

Содержание

MSSQL

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15 - Настройка учетных записей службы

Подключиться в режиме чтения: 

applicationIntent=Readonly

Firewall

Powershell: 

New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow

MSSQL instance MSSQL14.MSSQLSERVER: 

New-NetFirewallRule -DisplayName "SQLServer Program" -Direction Inbound -Program "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -Action Allow

https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver15

Always On

Пользовать от кого запущен MS SQL Server должен иметь права: 

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account OR user>];

https://docs.microsoft.com/ru-ru/sql/database-engine/availability-groups/windows/configure-read-scale-availability-groups?view=sql-server-ver15

Состояние репликации БД

Состояние репликации БД 

SELECT 
    ar.replica_server_name, 
    adc.database_name, 
    ag.name AS ag_name, 
    dhdrs.synchronization_state_desc, 
    dhdrs.is_commit_participant, 
    dhdrs.last_sent_lsn, 
    dhdrs.last_sent_time, 
    dhdrs.last_received_lsn, 
    dhdrs.last_hardened_lsn, 
    dhdrs.last_redone_time
FROM sys.dm_hadr_database_replica_states AS dhdrs
INNER JOIN sys.availability_databases_cluster AS adc 
    ON dhdrs.group_id = adc.group_id AND 
    dhdrs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = dhdrs.group_id
INNER JOIN sys.availability_replicas AS ar 
    ON dhdrs.group_id = ar.group_id AND 
    dhdrs.replica_id = ar.replica_id
WHERE database_name='DATABASE'
is_commit_participant 0 = фиксация транзакций не синхронизируется с этой базой данных.
1 = фиксация транзакции синхронизирована по отношению к этой базе данных.
Для баз данных в реплике доступности асинхронной фиксации это значение всегда равно 0.
Для синхронной фиксации данное значение является точным только в базе данных-источнике.

https://learn.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-database-replica-states-azure-sql-database?view=azuresqldb-current


ODBC

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server - Install the Microsoft ODBC driver for SQL Server (Linux)
https://learn.microsoft.com/eN-us/sql/connect/odbc/dsn-connection-string-attribute - DSN and Connection String Keywords and Attributes
https://xakinfo.ru/os/monitor-mssql-zabbix/
Zabbix ODBC

CLR

Показать конфигурацию CLR: 

EXEC sp_configure 'clr enabled';

Включить CLR: 

EXEC sp_configure 'clr enabled' , '1';

sys.assemblies

Показать загруженные assembly_files (clr) для текущей базы: 

SELECT 
 af.name,
 af.content 
FROM sys.assemblies a
INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id 
--WHERE a.name = 'VsOnline.SQLCLR'

https://www.mssqltips.com/sqlservertip/3126/exporting-clr-assemblies-from-sql-server-back-to-dll-files/
https://docs.microsoft.com/ru-ru/sql/t-sql/statements/create-assembly-transact-sql?view=sql-server-ver15

sys.trusted_assemblies

Показать доверенные сборки: 

SELECT * FROM sys.trusted_assemblies

Добавить/удалить в доверенные (пример для 'VsOnline.SQLCLR'):

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

-- Run on every node --
USE master;
GO
 
-- Enable clr
EXEC sp_configure 'clr enabled' , '1';  
RECONFIGURE;
 
-- Add VsOnline.SQLCLR in trusted_assembly --
 
DECLARE @clrName nvarchar(4000) = 'VsOnline.SQLCLR'
DECLARE @asmBin varbinary(MAX) = 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300AF83515C0000000000000000E00022200B013000001C00000006000000000000C23A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000703A00004F00000000400000A803000000000000000000000000000000000000006000000C000000383900001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C81A000000200000001C000000020000000000000000000000000000200000602E72737263000000A80300000040000000040000001E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000002200000000000000000000000000004000004200000000000000000000000000000000A43A00000000000048000000020005004C240000EC140000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200360000000100001103281100000A256F1200000A0A026F1300000A0B027E1400000A281500000A2D0F0F00042D03062B0107281600000A2A7E1400000A2A0000133003002A0000000100001103281100000A256F1200000A0A0F0006281700000A6F1300000A0B0F00042D03062B0107281700000A2A56020F01281800000A0F02281900000A28010000062A56020F01281800000A0F02281900000A28020000062A1E02281A00000A2A367E0200000402036F1B00000A2A367E0200000402036F1C00000A2A367E0100000402036F1D00000A2A367E0100000402036F1E00000A2A5673120000068001000004731100000680020000042A220203281F00000A2A26020304282000000A2A001330020024000000020000110203282100000A0A066F2200000A2D02142A066F2300000A046F2400000A6F2500000A2A220F01282600000A2A3202282700000A282800000A2AC202732900000A2520000100006F2A00000A2520000100006F2B00000A251B6F2C00000A25176F2D00000A282E00000A2A001B3004004000000003000011041E5B732F00000A0A160B2B1603075A0C0608283000000A161A6F3100000A0717580B07041E5B1A5B32E2066F3200000A0DDE0A062C06066F3300000ADC092A01100000020009002B34000A000000003A02281A00000A02037D3400000A2A001B3005008B00000004000011042D0B7201000070733500000A7A027B3400000A027B3400000A6F3600000A283700000A0203027B3400000A6F3800000A6F3900000A6F3A00000A0A733B00000A0B070617733C00000A0C08733D00000A0D09046F3E00000ADE0A092C06096F3300000ADC076F3200000A283F00000A1304DE14082C06086F3300000ADC072C06076F3300000ADC11042A000128000002005200095B000A0000000002004B002974000A00000000020042003C7E000A000000001B3006008300000005000011042D0B7213000070733500000A7A04284000000A027B3400000A027B3400000A6F3600000A283700000A0203027B3400000A6F3800000A6F3900000A6F4100000A0A734200000A0B070616733C00000A0C08734300000A0D096F4400000A1304DE1E092C06096F3300000ADC082C06086F3300000ADC072C06076F3300000ADC11042A0001280000020058000A62000A00000000020051001B6C000A00000000020048002E76000A00000000DA042D0B7213000070733500000A7A052D0B7237000070733500000A7A046F4500000A2D02162A020304284600000A051A284700000A2A001B3005003900000006000011021E5B732F00000A0A284800000A72590000706F4900000A0B060716021E5B6F3100000A066F3200000A0CDE0A062C06066F3300000ADC082A0000000110000002000900242D000A0000000042534A4201000100000000000C00000076342E302E33303331390000000005006C000000B4070000237E000020080000FC07000023537472696E6773000000001C1000009C00000023555300B8100000100000002347554944000000C81000002404000023426C6F620000000000000002000001571F02080904000000FA013300160000010000003300000009000000040000001C000000320000000100000049000000010000001B00000006000000040000000100000003000000020000000000720401000000000006009C024E0606001D034E0606008A034E0606002902180613006E06000006005102E80406000003E8040600CC02E80406007103E80406003D03E80406005603E80406006802E80406003D022F0606001B022F060600AF02E80406008302160406002707A9040600DF01A9040600DF04A9040A00EB02A5050A0050047D060A00D4047D060A00DF00A5050600D000A9040E006104B5060600930440000600B004B9070600C304B90706008604B90706008005400006005A05400006003905A9040E008C07B5060E004E05B5060E00FA04B5060E00FE01B50606002B06B9070600C000B90706008001B90706009D01B90706009805A9040600990440000600A801A90406000A05A90406008C01B90706008D05400006007207A90406006705400006005304A90406002005A90406000D048007000000003700000000000100010081011000A2064A0045000100010001001000E6064A0045000100030001001000D4064A0045000100060001001000F8064A00450003000C0001001000CD05E20706000300100001001000E305E2070A0003001200A10000001900E207000003001400810010000100E2074500030017003100E3058B013100CD058F0104007205CD005180400793015020000000009600C001960101009420000000009600750096010400CA20000000009600C0019F010700E02000000000960075009F010A00F620000000008618C00501000D00FE200000000096002401AA010D000C210000000096004C01AA010F001A210000000096003701B101110028210000000096001101B1011300F620000000008618C005010015003621000000009118C605B70115004C210000000096005A046A00150055210000000096006A01700017006021000000009600460570001A00F620000000008618C00501001D00902100000000C4005A00BB011D009921000000008618C00501001F00A621000000008618C00501001F00D82100000000C4005A00C3011F00000000000000C6052E0763002100000000000000C605360763002300000000000000C605B207CA0125003422000000008418C0059B002800442200000000E6012E0763002900042300000000E601360763002B00BC2300000000E601B207CA012D00F423000000009100AB07B8003000000000000000C4055A00E100310000000100B401000002008F0000000300E80100000100DA01000002008F0000000300E80100000100B401000002008F0000000300E80100000100DA01000002008F0000000300E801000001009E00000002006101000001009E0000000200FF00000001009E0000000200FF00000001009E00000002006101000001007A07000002003105000001007A07000002003105000003006607000001007A07000002003105000003005405000001009E0000000200D703000001009E0000000200D703000001009E00000002006101000001009E0000000200FF00000001009E0000000200FF0000000300EE00000001007905000001009E00000002006101000001009E0000000200FF00000001009E0000000200FF0000000300EE0000000100F903000001009E0000000200D70309000E000900C00501001100C00505001900C00501002100C0050A003100C00510003900C00510004100C00510004900C00510005100C00510005900C00510006100C00510006900C00515007100C00510007900C00510008100C0051000A100C00501000101A5003500010147073C000101590741009100B20348009100D6074C009100BC00540091001E075400A900A8035B00B100A8035F008900C00501000C002E0763000C003607630014003607630014002E07630009015F046A0009016F017000090161047C00110112075F00C900070783001901A00489002101A8035B00C1009207900029010E0295000C00C0059B003101C0050100D900ED030500D900C9030500D9000104A100D9007701A8001400C0059B00D100C005050049019206B80051011502BE00D1009E07900059010602010024007205CD006101C0051000D900E103DD002400AB07B800D900BB03DD0024005A00E100D9000806E900D100C0050100E900C005F200F100C005FE00710115021000790141040501790130041701D900F805E900D100C0051D01F900C005FE008101D5005B0089016704DD0024003607630089019B06230199012E003501990192063B010E0010004A0120000B00D2012E000B00D2012E001300D7012E001B00E0012E002300FF012E002B0008022E0033001D022E003B001D022E0043001D022E004B0008022E00530023022E005B001D022E0063001D022E006B003B022E00730065022E007B00720240000B00D20143000B00D20160008300D20180008300D201C0008300C002E0008300C00200018300C00220018300C002800183007103A00183007103C001830071032E007700AF00D1000B012C011A0021002700C6000480000001000000000000000000000000004A00000004000000000000000000000041016C000000000004000000000000000000000041016000000000000400000000000000000000004101A90400000000000002001000A607000000001200A60700000050617373776F726450726F746563746F72426173656031004950617373776F726450726F746563746F726031006765745F55544638003C4D6F64756C653E0053797374656D2E494F0056734F6E6C696E652E53514C434C520047657449560053797374656D2E44617461006D73636F726C696200436F6E766572744C6F63616C4461746554696D65546F5574630074696D655A6F6E65496E666F4964007573657249640046696E6453797374656D54696D655A6F6E6542794964004164640052696A6E6461656C4D616E6167656400477569640052656164546F456E6400446174614163636573734B696E640070726F766964656450617373776F72640070726F74656374656450617373776F7264004F6C6450726F7465637450617373776F7264004E657750726F7465637450617373776F7264004F6C64556E70726F7465637450617373776F7264004E6577556E70726F7465637450617373776F72640070617373776F72640052656745785265706C616365007365745F4D6F64650050616464696E674D6F64650043727970746F53747265616D4D6F6465004369706865724D6F64650049446973706F7361626C65007574634461746554696D6500436F6E76657274557463546F4C6F63616C4461746554696D65006C6F63616C4461746554696D65007573654461796C69676874536176696E6754696D65004361707475726500446973706F73650043726561746500577269746500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500457874656E73696F6E41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565004D696E56616C7565006765745F426C6F636B53697A65007365745F426C6F636B53697A6500626C6F636B53697A65006765745F4B657953697A65007365745F4B657953697A65006B657953697A65007365745F50616464696E6700456E636F64696E670053797374656D2E52756E74696D652E56657273696F6E696E670046726F6D426173653634537472696E6700546F426173653634537472696E670053716C537472696E6700526567457849734D61746368006765745F4C656E6774680056734F6E6C696E652E53514C434C522E646C6C0043727970746F53747265616D004D656D6F727953747265616D006765745F4974656D0053797374656D0053796D6D6574726963416C676F726974686D004943727970746F5472616E73666F726D0053716C426F6F6C65616E0054696D655370616E0053797374656D2E5265666C656374696F6E0047726F7570436F6C6C656374696F6E00417267756D656E744E756C6C457863657074696F6E00537472696E67436F6D70617269736F6E007061747465726E0054696D655A6F6E65496E666F00526567457847657447726F75700067726F75700053747265616D526561646572005465787452656164657200436970686572006369706865720053747265616D577269746572005465787457726974657200426974436F6E766572746572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F72004775696450617373776F726450726F746563746F7200496E7450617373776F726450726F746563746F7200437265617465446563727970746F7200437265617465456E63727970746F720053797374656D2E446961676E6F7374696373004165730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C547970657300476574427974657300457175616C73004461746554696D65457874656E73696F6E730053797374656D2E546578742E526567756C617245787072657373696F6E730050617373776F726446756E6374696F6E73004461746554696D6546756E6374696F6E7300526567657846756E6374696F6E73006765745F47726F757073006765745F53756363657373005375627472616374004F626A6563740050726F7465637400556E70726F7465637400536563726574006765745F426173655574634F6666736574004765745574634F6666736574007265706C6163656D656E7400436F6E7665727400696E7075740053797374656D2E5465787400526567657800546F42797465417272617900546F417272617900544B6579004765744B6579005665726966790053797374656D2E53656375726974792E43727970746F677261706879006F705F457175616C6974790056734F6E6C696E652E53514C434C522E536563757269747900000011700061007300730077006F00720064000023700072006F00740065006300740065006400500061007300730077006F00720064000021700072006F0076006900640065006400500061007300730077006F00720064000041380033003900430030004400390035003900440034004600340037003900430038003100450045003000420034003900350043004300330042003900360039000000BF15293F4D049E41B18E74541BBC05C100032000010420010108052001011115042001010E04200101020615122401116105151224010806151220011300060702114D114D0600011280810E042000114D062001114D11490306114907000202114911490620011149114D0320000E032000020620020E13000E050002020E0E0600030E0E0E0E040701126506000212650E0E05200012808D0620011280890E0420001D0505000012809505200101126D0620010111809D062001011180A1080704126908081D050500011D0508072003011D050808061512240113000306126D0B070512711269127512790E032000080720021D0513000808200212711D051D050B2003011280A912711180B5062001011280A90500010E1D050B0705127112691275127D0E0500011D050E052001011D05080003020E0E1180C908070312691D051D050500001280CD0520011D050E08B77A5C561934E08940380033003900430030004400390035003900440034004600340037003900430038003100450045003000420034003900350043004300330042003900360039000306121C0306121802060E080003114911490E020A000311491149115511590600020E11610E0500020E080E030000010720021D051161080620021D0508080720030213000E0E04010000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000001401000F56734F6E6C696E652E53514C434C52000005010000000017010012436F7079726967687420C2A920203230313800002901002433383634663238332D643462632D346466352D393538302D66626231333465306666653700000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E362E310100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E362E3180AF0100030054020F497344657465726D696E697374696301540209497350726563697365005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000080AF0100030054020F497344657465726D696E697374696301540209497350726563697365015455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737300000000000000000000AF83515C00000000020000001C01000054390000541B0000525344536415DFC3CCEEAA4B94D748057140D42301000000443A5C4465765C56534F6E6C696E65576562536974654769745C56734F6E6C696E655C56734F6E6C696E652E53514C434C525C6F626A5C52656C656173655C56734F6E6C696E652E53514C434C522E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000983A00000000000000000000B23A0000002000000000000000000000000000000000000000000000A43A0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C03000000000000000000004C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC020000010053007400720069006E006700460069006C00650049006E0066006F0000008802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D0065000000000000000000480010000100460069006C0065004400650073006300720069007000740069006F006E0000000000560073004F006E006C0069006E0065002E00530051004C0043004C0052000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000048001400010049006E007400650072006E0061006C004E0061006D0065000000560073004F006E006C0069006E0065002E00530051004C0043004C0052002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100380000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000560073004F006E006C0069006E0065002E00530051004C0043004C0052002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D00650000000000560073004F006E006C0069006E0065002E00530051004C0043004C0052000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000C43A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;
DECLARE @hash varbinary(64);
 
SELECT @hash = HASHBYTES('SHA2_512', @asmBin);
 
-- ADD (uncomment):
--/*
EXEC sys.sp_add_trusted_assembly @hash = @hash,
                                 @description = @clrName;
--*/
 
-- DROP (uncomment):
/*
EXEC sys.sp_drop_trusted_assembly @hash = @hash;
*/
-- DROP by description
/*
DECLARE @hash varbinary(64);
SELECT @hash = (SELECT hash FROM sys.trusted_assemblies WHERE description='VsOnline.SQLCLR')
EXEC sys.sp_drop_trusted_assembly @hash = @hash
*/


https://nielsberglund.com/2017/07/23/sql-server-2017-sqlclr---whitelisting-assemblies/
http://ianpicknell.blogspot.com/2009/12/adding-strong-name-to-third-party.html

TRUSTWORTHY

Показать значения для баз: 

SELECT name,is_trustworthy_on FROM sys.databases;

Изменить значение для базы (пример для базы VSOnlineTest): 

ALTER DATABASE VSOnlineTest SET TRUSTWORTHY ON;

https://docs.microsoft.com/ru-ru/sql/relational-databases/security/trustworthy-database-property?view=sql-server-ver15

Optimize for ad hoc workloads

Показать текущее значение: 

USE master
SELECT * FROM sys.configurations WHERE NAME = 'optimize for ad hoc workloads'

Включить: 

USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE WITH OVERRIDE
GO

https://docs.microsoft.com/ru-ru/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option
https://wiki.it-kb.ru/1c/setting-up-microsoft-sql-server-for-1c-enterprise-8-3/optimize-for-ad-hoc-workloads

Безопасность

Показать SID пользователя: 

SELECT SUSER_SID('login');

sid user db | sid login server

sid user db | sid login server 

USE DB;
SELECT name, sid FROM sys.sysusers WHERE name = 'login'
GO
USE master;
SELECT name, sid FROM sys.sql_logins WHERE name = 'login'
GO


Поменять пользователю SID: 

DROP LOGIN [USER];
CREATE LOGIN [USER] WITH PASSWORD = 'password', SID = 0x2542795EDA11A2468F5D9CCDEA47C665;

https://docs.microsoft.com/ru-ru/sql/t-sql/functions/suser-sid-transact-sql

Сформировать SQL запрос для переноса учётных записей:

create login

create login 

SELECT 'create login [' + sp.name + '] ' + CASE
        WHEN sp.type IN (
                'U'
                ,'G'
                )
            THEN 'from windows '
        ELSE ''
        END + 'with ' + CASE
        WHEN sp.type = 'S'
            THEN 'password = ' + master.sys.fn_varbintohexstr(sl.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(sl.sid) + ', check_expiration = ' + CASE
                    WHEN sl.is_expiration_checked > 0
                        THEN 'ON, '
                    ELSE 'OFF, '
                    END + 'check_policy = ' + CASE
                    WHEN sl.is_policy_checked > 0
                        THEN 'ON, '
                    ELSE 'OFF, '
                    END + CASE
                    WHEN sl.credential_id > 0
                        THEN 'credential = ' + c.name + ', '
                    ELSE ''
                    END
        ELSE ''
        END + 'default_database = ' + sp.default_database_name + CASE
        WHEN len(sp.default_language_name) > 0
            THEN ', default_language = ' + sp.default_language_name
        ELSE ''
        END
FROM sys.server_principals sp
LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
LEFT JOIN sys.credentials c ON sl.credential_id = c.credential_id
WHERE sp.type IN (
        'S'
        ,'U'
        ,'G'
        )
    AND sp.name <> 'sa'
    AND sp.name NOT LIKE 'NT Authority%'
    AND sp.name NOT LIKE 'NT Service%'

https://blog.sqlauthority.com/2017/11/30/sql-server-alwayson-availability-groups-script-sync-logins-replicas/

Owner БД

show owner

show owner 

USE [master]
GO
SELECT db.name AS [DATABASE],sp.name [Owner] FROM sys.databases db LEFT JOIN sys.server_principals sp ON db.owner_sid=sp.sid
WHERE database_id>5


Сформировать T-SQL скрипт для изменения owner:

generate script owner

generate script owner 

SELECT 'USE [' + db.name+']; EXEC sp_changedbowner [sa];' FROM 
sys.databases db LEFT JOIN sys.server_principals sp
ON db.owner_sid=sp.sid
WHERE sp.name <>'sa'


https://www.sqlshack.com/different-ways-to-change-database-owners-in-sql-server/

Сменить пароль

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

ALTER LOGIN userlogin WITH
     PASSWORD = 'newPassword'
     OLD_PASSWORD = 'oldPassword';
GO


Роли

Показать разрешения роли текущей базы данных: 

EXEC sp_helprotect @username = '${ROLENAME}'

Показать роли пользователя текущей базы данных: 

EXEC sp_helpuser '${LOGIN}'

Роль для SYSTEM

Добавить роль dbcreator: 

EXEC master..sp_a
    @loginame = N'NT AUTHORITY\SYSTEM', @roleddsrvrolemembername = N'dbcreator'

Предоставить разрешения на сервер (ALTER ANY CONNECTION): 

GRANT ALTER ANY CONNECTION TO [NT AUTHORITY\SYSTEM]

Роль для резервного копирования

Для каждой базы: 

USE DBforBACKUP;
GO
CREATE LOGIN [<HOSTNAMEorDOMAIN>\sqlBackup] FROM WINDOWS;
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [<HOSTNAMEorDOMAIN>\sqlBackup];

Для DBCC SHRINKFILE или DBCC SHRINKDATABASE требуется членство в фиксированной роли сервера sysadmin или фиксированной роли базы данных db_owner.

Роль выполнения процедур

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE DBforEXECUTE
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
EXEC sp_addrolemember 'db_executor', 'USERforEXECUTE'

https://stackoverflow.com/questions/2298247/built-in-database-role-in-sql-server-2005-to-permit-execution-of-stored-procedur


Привилегии / Permission

Показать выданные привилегии текущей базы

Показать выданные привилегии текущей базы 

SELECT  pri.name AS username
  ,pri.type_desc AS [USER TYPE]
  ,permit.permission_name AS [Permission]
  ,permit.state_desc AS [Permission State]
  ,permit.class_desc Class
  ,object_name(permit.major_id) AS [Object Name]
FROM sys.database_principals pri
LEFT JOIN
  sys.database_permissions permit
ON permit.grantee_principal_id = pri.principal_id


https://databasefaqs.com/sql-server-user-permissions/

Шифрование (TDE)

Создать мастер ключ и сертификат:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'superStrogPasswrodMK';
GO
CREATE CERTIFICATE TDE_Cert_26
WITH 
SUBJECT='Database_Encryption',
EXPIRY_DATE = '20260201';
GO


Для кластера или восстановления на другом сервере, сделать резервное копирование сертификата и ключа к нему:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

BACKUP CERTIFICATE TDE_Cert_26 TO FILE ='C:\TDE_Cert_26.crt'  
 WITH PRIVATE KEY   
 (   
  FILE ='E:\TDE_Cert_26.pvk',
  ENCRYPTION BY PASSWORD = 'superStrogPasswrodPVK'
 )
GO

Для Azure SQL в бинарный ASN формат: 

SELECT CERTENCODED(CERT_ID('TDE_Cert_26'));
SELECT CERTPRIVATEKEY(CERT_ID('TDE_Cert_26'), 'superStrogPasswrodPVK');


Создать на другом сервере мастер ключ и сертификат из бэкапа:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'superStrogPasswrodMK';
GO
CREATE CERTIFICATE TDE_Cert_26   
    FROM FILE = 'E:\TDE_Cert_26.crt'   
    WITH PRIVATE KEY (FILE = 'C:\TDE_Cert_26.pvk',   
    DECRYPTION BY PASSWORD = 'superStrogPasswrodPVK');
GO

Для Azure SQL из бинарного ASN формата: 

CREATE CERTIFICATE TDE_Cert_26
    FROM BINARY = 0x3...D
    WITH PRIVATE KEY (
       BINARY = 0x1...3 
       , DECRYPTION BY PASSWORD = 'superStrogPasswrodPVK'
	);


Зашифровать базу:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE dbname;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE dbname
SET ENCRYPTION ON;
GO

https://docs.microsoft.com/ru-ru/sql/relational-databases/security/encryption/transparent-data-encryption

Показать, зашифрована ли база:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

DECLARE @db sysname = 'mydb';
IF EXISTS (
    SELECT *
    FROM sys.dm_database_encryption_keys
    WHERE database_id = db_id(@db)
       AND encryption_state = 3 /* encrypted */
)
BEGIN
   print concat(quotename(@db), ' is encrypted.')
END
ELSE
BEGIN
   print concat(quotename(@db), ' is not encrypted.')
END

https://stackoverflow.com/questions/64379237/sql-server-tde-use-t-sql-to-show-if-there-is-a-database-key

Показать все зашифрованные базы и их состояние:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

SELECT DB_NAME(database_id) AS name, * FROM sys.dm_database_encryption_keys
ORDER BY  name

encryption_state:
0 - Database Encryption Key (DEK) не создан.
1 - Database Encryption Key (DEK) создан, но база данных не зашифрована.
2 - Выполняется первоначальное шифрование.
3 - База данных зашифрована.
4 - Идет смена ключа.
5 - Идет расшифровка.

https://www.interface.ru/home.asp?artId=29621

Удалить шифрование и ключ шифрования базы данных:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

ALTER DATABASE AdventureWorks2012  
SET ENCRYPTION OFF;  
GO  
USE AdventureWorks2012;  
GO  
DROP DATABASE ENCRYPTION KEY;  
GO

https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-database-encryption-key-transact-sql

Показать все сертификаты на сервере:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE master;
GO
SELECT
     *,
     CERTENCODED(C.certificate_id)
FROM sys.certificates C;


Показать каким сертифкатом зашифрована база:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE [master]
GO
SELECT
DB_NAME(db.database_id) DbName, db.encryption_state
, encryptor_type, cer.name, cer.expiry_date, cer.subject
FROM sys.dm_database_encryption_keys db
JOIN sys.certificates cer 
ON db.encryptor_thumbprint = cer.thumbprint
GO

https://www.mssqltips.com/sqlservertip/5009/updating-an-expired-sql-server-tde-certificate/
https://dba.stackexchange.com/questions/92080/query-to-list-encryption-certificate-for-databases

Добавить базу в группу Always On:

1. Always On группа должна быть настроена
2. Сертификат шифрования с ключём должен быть создан на вторичном сервере
3. Отключить резервное копирование БД, включая логи транзакций
4. Сделать резервную копию БД (Full и Transaction Log)

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

BACKUP DATABASE [TDE_Test] 
TO  DISK = N'E:\Backup\TDE_Test.bak' WITH NOFORMAT, NOINIT,  
NAME = N'TDE_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP LOG [TDE_Test] TO  DISK = N'E:\Backup\Backup\TDE_Test.trn' WITH NOFORMAT, NOINIT,  
NAME = N'TDE_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
ALTER AVAILABILITY GROUP "AG01" ADD DATABASE [TDE_Test]

5. Восстановить БД на вторичном сервере (Full и Transaction Log):

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE [master]
RESTORE DATABASE [TDE_Test] FROM  DISK = N'E:\Backup\TDE_Test.bak' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
GO
RESTORE LOG [TDE_Test] FROM  DISK = N'E:\Backup\TDE_Test.trn' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10
GO
ALTER DATABASE [TDE_Test] SET HADR AVAILABILITY GROUP = "AG01";

https://www.sqlshack.com/how-to-add-a-tde-encrypted-user-database-to-an-always-on-availability-group/

Обновить сертифкат:
https://www.mssqltips.com/sqlservertip/5009/updating-an-expired-sql-server-tde-certificate/

Docker

https://github.com/mrlioncub/mssql-tools

docker run

docker run 

docker run -p 1433:1433 \
 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<supper-password>' -e MSSQL_MEMORY_LIMIT_MB="2048" -e 'MSSQL_PID=Express' \
 -e MSSQL_COLLATION=Cyrillic_General_CI_AS -e TZ=Asia/Yekaterinburg \
 -v /data/docker/mssql/mssql:/var/opt/mssql/ \
 -v /data/docker/mssql/backups:/var/opt/mssql/backups \
 --restart on-failure:3 \
 -h mssql.domain.com -d --name mssql mcr.microsoft.com/mssql/server:2019-latest

docker-compose

docker-compose

docker-compose.yaml: 

version: '3.4'
volumes:
  mssql:
    driver_opts:
      type: none
      device: ${VOLPATH-/data/docker/mssql}/mssql
      o: bind
  backups:
    driver_opts:
      type: none
      device: ${VOLPATH-/data/docker/mssql}/backups
      o: bind
 
services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2019-latest
    hostname: mssql.domain.com
    environment:
    - ACCEPT_EULA=Y
    - SA_PASSWORD=<super-password>
#    - MSSQL_PID=Express
    - MSSQL_MEMORY_LIMIT_MB=10240
#    - MSSQL_COLLATION=Cyrillic_General_CI_AS
#    - TZ=Asia/Yekaterinburg
    volumes:
    - mssql:/var/opt/mssql:rw
    - backups:/var/opt/mssql/backups:rw
    ports:
      - 1433:1433
    restart: on-failure:3
    deploy:
      replicas: 1
#      resources:
#        limits:
#          memory: 2g
      restart_policy:
        condition: any
    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$${SA_PASSWORD}" -Q "SELECT 1" -b > /dev/null
      interval: 10s
      timeout: 3s
      retries: 5
      start_period: 60s

Запуск:

  • docker-compose: 
    docker-compose up -d
  • swarm: 
    docker stack deploy --compose-file docker-compose.yaml mssql


Изменить директорию для резервных копий по умолчанию: 

docker exec --user root mssql /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/backups

https://docs.microsoft.com/ru-ru/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-ver15#backupdir

Аутентификация AD (keytab)

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-containers-ad-auth-adutil-tutorial?view=sql-server-ver15
https://github.com/vicrem/mssql

Always On

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть

Запустить контейнеры с параметрами: 

    hostname: node[1-2]
    environment:
    - ACCEPT_EULA=Y
    - SA_PASSWORD=<SA-Pa$$w0rd>
    - MSSQL_PID=Developer
    - MSSQL_ENABLE_HADR=1
    ports:
      - 1433:1433
      - 5022:5022

На первой ноде создать пользователя и сертификат к нему, сделать резервную копию сертификата и ключа: 

CREATE LOGIN dbm_login WITH PASSWORD = 'super-Pa$$w0rd';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'super-Pa$$w0rd';
GO
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm',
  EXPIRY_DATE = '20320201';
GO
BACKUP CERTIFICATE dbm_certificate
TO FILE = '/var/opt/mssql/backups/dbm_certificate.cer'
WITH PRIVATE KEY (
  FILE = '/var/opt/mssql/backups/dbm_certificate.pvk',
  ENCRYPTION BY PASSWORD = 'super-Pa$$w0rd'
);

На второй ноде создать пользователя и импортировать сертификат созданный на первой ноде: 

CREATE LOGIN dbm_login WITH PASSWORD = 'super-Pa$$w0rd';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'super-Pa$$w0rd';
GO
CREATE CERTIFICATE dbm_certificate   
  AUTHORIZATION dbm_user
  FROM FILE = '/var/opt/mssql/backups/dbm_certificate.cer'
  WITH PRIVATE KEY (
  FILE = '/var/opt/mssql/backups/dbm_certificate.pvk',
  DECRYPTION BY PASSWORD = 'super-Pa$$w0rd'
)

На двух нодах создать endpoint: 

CREATE ENDPOINT [Hadr_endpoint]
  AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = 5022)
  FOR DATA_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = CERTIFICATE dbm_certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES
    );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login]

Создать группу доступности на первой ноде: 

CREATE AVAILABILITY GROUP [ag]
  WITH ( CLUSTER_TYPE = NONE, 
    AUTOMATED_BACKUP_PREFERENCE = NONE )
  FOR REPLICA ON
  N'node1'
  WITH (
    ENDPOINT_URL = N'tcp://node1:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL),
  ),
  N'node2'
  WITH (
    ENDPOINT_URL = N'tcp://node2:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
  )

Добавить второй кластер в группу доступности: 

ALTER AVAILABILITY GROUP [ag] JOIN WITH (CLUSTER_TYPE = NONE)
ALTER AVAILABILITY GROUP [ag] GRANT CREATE ANY DATABASE

На первой ноде добавить базу данных в группу доступности: 

ALTER AVAILABILITY GROUP [ag] ADD DATABASE [DatabaseName]

https://dbtut.com/index.php/2020/06/09/sql-server-2019-alwayson-availability-group-on-docker-containers/
https://github.com/enriquecatala/sqlserver-docker-alwayson


tmpfs

Dockerfile

Dockerfile 

FROM ubuntu:18.04 as build
 
ADD so/nodirect_open.c /
RUN apt update && apt install -y gcc && \
    gcc -shared -fpic -o /nodirect_open.so nodirect_open.c -ldl
 
FROM mcr.microsoft.com/mssql/server:2019-CU9-ubuntu-18.04
USER root
COPY --from=build /nodirect_open.so /so/nodirect_open.so
RUN echo "/so/nodirect_open.so" >> /etc/ld.so.preload
ENV LD_PRELOAD=/so/nodirect_open.so

https://github.com/microsoft/mssql-docker/issues/110

Примеры

https://github.com/Tavalik/SQL_TScripts

Активные соединения

Количество соединений к каждой базе

Количество соединений к каждой базе 

SELECT
    DB_NAME(dbid) AS DBName,
    COUNT(dbid) AS NumberOfConnections,
    loginame AS LoginName
FROM
    sys.sysprocesses
WHERE
    dbid > 0
GROUP BY
    dbid, loginame


Список медленных запросов

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC


Активный сервер

Пример для Failover серверов.

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть

Показать возможность обновления текущей базы данных: 

IF (DATABASEPROPERTYEX(DB_NAME(), 'Updateability') = 'READ_WRITE')
  BEGIN
    PRINT 'PRIMARY'
  END
ELSE
  BEGIN
    PRINT 'SECONDARY'
  END

или: 

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

Показать текущее имя сервера: 

SELECT @@SERVERNAME


https://learn.microsoft.com/ru-ru/sql/t-sql/functions/databasepropertyex-transact-sql

Backup & Restore

Резервное копирование и восстановление для mssql в контейнере на удалённом хосте по ssh:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

#!/bin/bash
 
DATE=$(date +%F)
 
DB="dbname"
DBUSER="sa"
DBPASS="password"
REMOTEHOST="docker4"
PATHB="/data/1c"
 
FILENAME=$DB\_$DATE
if ! [ -d $PATHB ]; then mkdir -p $PATHB; fi
 
#Backup sql
/opt/mssql-tools/bin/sqlcmd -S c1 -U $DBUSER -P $DBPASS -Q "BACKUP DATABASE $DB to DISK='/var/opt/mssql/backups/$FILENAME.bak'"
 
#Move & compress backup
CONTAINER=$(ssh $REMOTEHOST "docker ps -q -f name=mssql")
ssh $REMOTEHOST "docker exec $CONTAINER cat /var/opt/mssql/backups/$FILENAME.bak" | gzip > $PATHB/$FILENAME.bak.tgz
ssh $REMOTEHOST "docker exec $CONTAINER rm /var/opt/mssql/backups/$FILENAME.bak"
 
#Clean backup
while [ `ls $PATHB/$DB\_* | wc -l` -gt 7 ] ; do
    rm `ls $PATHB/$DB\_* | head -1`
done
 
 
#Restore
#zcat $PATHB/$FILENAME.bak.tgz | ssh $REMOTEHOST docker exec -i $CONTAINER dd status=none of=/var/opt/mssql/backups/$FILENAME.bak
#/opt/mssql-tools/bin/sqlcmd -S c1 -U $DBUSER -P $DBPASS -Q "RESTORE DATABASE $DB to DISK='/var/opt/mssql/backups/$FILENAME.bak'"
#ssh $REMOTEHOST "docker exec $CONTAINER rm /var/opt/mssql/backups/$FILENAME.bak"


Резервное копирование на локальной системе:

backupsql.bat

backupsql.bat 

@echo off
setlocal EnableDelayedExpansion
set LOG=D:\service\backup_sql.log
echo %TIME% Start... > %LOG%
 
:: Сколько копий храним
set count=7
:: Куда бэкапим
set dst=D:\backup
 
:: Устанавливаем текущую дату
for /f "tokens=1" %%n in ('date /t') do set thedate=%%n
set dd=%thedate:~0,2%
set mm=%thedate:~3,2%
set yyyy=%thedate:~6,4%
set TODAY=%yyyy%%mm%%dd%
 
call :BACKUP
call :COMPRESS
call :CLEARARHIVE
goto END
 
:END
echo %TIME% :END >> %LOG%
goto :eof
 
:BACKUP
echo %TIME% :BACKUP >> %LOG%
sqlcmd -U user -P pass -i D:\service\backup.sql >> %LOG%
goto :eof
 
:COMPRESS
echo %TIME% :COMPRESS >> %LOG%
"C:\Program Files\7-Zip\7z.exe" a %dst%\%TODAY%_nvp_book.7z %dst%\%TODAY%_nvp_book.bak
del /Q %dst%\%TODAY%_nvp_book.bak
goto :eof
 
:CLEARARHIVE
echo %TIME% :CLEARARHIVE >> %LOG%
for /F "delims=" %%i in ('dir %dst%\*.7z /B /O:-D') do (
set /A count=!count! - 1
if !count! LSS 0 del /F /Q "%dst%\%%i" >> %LOG% 2>&1
)
:eof

backup.sql

backup.sql 

DECLARE @pathName NVARCHAR(512) 
SET @pathName = 'D:\Backup\' + Convert(varchar(8), GETDATE(), 112) + '_dbname.bak' 
BACKUP DATABASE [dbname] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N'dbname', SKIP, NOREWIND, NOUNLOAD,  STATS = 10


Дифференцированный бэкап определённых БД:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

DECLARE @name NVARCHAR(256) -- database name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
 
-- specify database backup directory
SET @path = 'Z:\diff\'
-- specify filename format
SELECT  @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('DB1','DB2','DB3')
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @fileName = @path + @name + '_' + @fileDate + '.bak'
   BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION, STATS = 10, DIFFERENTIAL
 
   FETCH NEXT FROM db_cursor INTO @name
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

Показать информацию о запросе:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

SELECT
    r.[command]
    ,db_name = db_name(r.database_id)
    ,r.[start_time]
    ,eta_completion_time = DATEADD(ms, r.[estimated_completion_time], GETDATE())
    ,r.[percent_complete]
    ,r.[estimated_completion_time] / 60000. AS [estimated_completion_time_min]
    ,r.session_id
    ,s.original_login_name
    ,t.text
FROM master.sys.dm_exec_requests r
JOIN master.sys.dm_exec_sessions s ON r.session_id = s.session_id
JOIN master.sys.dm_exec_connections h ON r.session_id = h.session_id
CROSS APPLY master.sys.dm_exec_sql_text(h.most_recent_sql_handle) t
WHERE r.[command] = 'BACKUP DATABASE'
    OR r.[command] = 'RESTORE DATABASE'
    OR r.[command] = 'BACKUP LOG'
    OR r.[command] = 'RESTORE LOG'
    OR r.[command] LIKE '%DBCC%'


Показать подробную информацию о запросе:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE master
GO 
SELECT 
    req.session_id, 
    database_name = db_name(req.database_id),
    req.status,
    req.blocking_session_id, 
    req.command,
    [sql_text] = SUBSTRING(txt.TEXT, (req.statement_start_offset / 2) + 1, (
                (
                    CASE req.statement_end_offset
                        WHEN - 1 THEN Datalength(txt.TEXT)
                        ELSE req.statement_end_offset
                    END - req.statement_start_offset
                    ) / 2
                ) + 1),
    req.percent_complete,
    req.start_time,
    cpu_time_sec = req.cpu_time / 1000,
    granted_query_memory_mb = CONVERT(NUMERIC(8, 2), req.granted_query_memory / 128.),
    req.reads,
    req.logical_reads,
    req.writes,
    eta_completion_time = DATEADD(ms, req.[estimated_completion_time], GETDATE()),
    elapsed_min = CONVERT(NUMERIC(6, 2), req.[total_elapsed_time] / 1000.0 / 60.0),
    remaning_eta_min = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0),
    eta_hours = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0/ 60.0),
    wait_type,
    wait_time_sec = wait_time/1000, 
    wait_resource
FROM sys.dm_exec_requests AS req WITH(NOLOCK)
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt 
WHERE req.session_id>50
    AND command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')

https://alibaba-cloud.medium.com/sql-server-best-practices-monitoring-backup-and-restore-progress-2ff10a7a43f8

Резервное копирование определённых баз c проверкой на состояние и на исключение одновременного повтороного резервирования (при запуске из другого процесса):

full

full 

-- Backup Full
DECLARE @name NVARCHAR(256) -- database name
DECLARE @namelog NVARCHAR(256) -- log trnsaction name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
DECLARE @sqluse NVARCHAR(256) 
 
-- specify database backup directory
SET @path = 'Z:\full\'
-- specify filename format
SELECT  @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('db1','db2','db3','db4','db5')
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @fileName = @path + @name + '_' + @fileDate + '.bak'
   BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION
   FETCH NEXT FROM db_cursor INTO @name
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

diff

diff 

-- Backup Diff
DECLARE @name NVARCHAR(256) -- database name
DECLARE @namelog NVARCHAR(256) -- log trnsaction name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
DECLARE @sqluse NVARCHAR(256) 
 
-- specify database backup directory
SET @path = 'Z:\diff\'
-- specify filename format
SELECT  @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('db1','db2','db3','db4','db5')
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
  IF NOT EXISTS (SELECT db_name(r.database_id)
    FROM master.sys.dm_exec_requests r
    WHERE (r.[command] = 'BACKUP DATABASE'
      OR r.[command] = 'BACKUP LOG'
      OR r.[command] LIKE '%DBCC%')
      AND db_name(r.database_id) LIKE @name)
  BEGIN
    SET @fileName = @path + @name + '_' + @fileDate + '.bak'
    BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION, DIFFERENTIAL
    FETCH NEXT FROM db_cursor INTO @name
  END
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

transaction log

transaction log 

-- Backup TransactionLog
DECLARE @name NVARCHAR(256) -- database name
DECLARE @namelog NVARCHAR(256) -- log trnsaction name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
DECLARE @sqluse NVARCHAR(256) 
 
-- specify database backup directory
SET @path = 'Z:\tl\'
-- specify filename format
SELECT  @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('db1','db2','db3','db4','db5')
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
  IF NOT EXISTS (SELECT db_name(r.database_id)
    FROM master.sys.dm_exec_requests r
    WHERE (r.[command] = 'BACKUP DATABASE'
      OR r.[command] = 'BACKUP LOG'
      OR r.[command] LIKE '%DBCC%')
      AND db_name(r.database_id) LIKE @name)
  BEGIN
    SET @fileName = @path + @name + '_' + @fileDate + '.trn'
    BACKUP LOG @name TO DISK = @fileName WITH COMPRESSION
    SET @namelog = @name + '_log'
    SET @sqluse = 'USE ' + @name + '; DBCC SHRINKFILE (' + @namelog + ' , 1);'
    EXEC sp_sqlexec @sqluse
    FETCH NEXT FROM db_cursor INTO @name
  END
END
 
CLOSE db_cursor
DEALLOCATE db_cursor


Размер таблиц

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

SELECT
t.Name AS TableName,
s.Name AS SchemaName,
p.Rows AS RowCounts,
SUM(a.total_pages) * 8 / 1048576.0 AS TotalSpaceGB,
SUM(a.used_pages) * 8 / 1048576 AS UsedSpaceGB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
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
t.Name;


Rebuild

Показать состояние индексов на текущей базе:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

SELECT dbschemas.[name] AS 'Schema',
  dbtables.[name] AS 'Table',
  dbindexes.[name] AS 'Index',
  indexstats.avg_fragmentation_in_percent,
  indexstats.avg_page_space_used_in_percent,
  indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
  AND indexstats.index_id = dbindexes.index_id
ORDER BY dbtables.[name]

или 

SELECT s.name schema, t.name TABLE, i.name INDEX, d.avg_fragmentation_in_percent fragmentation
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) d
INNER JOIN sys.tables  t ON d.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON d.object_id = i.object_id AND d.index_id = i.index_id
WHERE  d.index_id > 0 AND d.page_count > 8
ORDER BY fragmentation DESC


Перестроить индексы (@fillfactor - коэффициент заполнения в индексе):

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

DECLARE @TableName VARCHAR(255)
DECLARE @SQL NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80 
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@SQL)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

https://stackoverflow.com/questions/32505775/rebuild-all-indexes-in-a-database

Список индексов базы с запросом в поле для фрагментации:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent,
'ALTER INDEX ' + QUOTENAME(ind.name)  + ' ON ' +QUOTENAME(object_name(ind.object_id)) + 
CASE    WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD ' 
        WHEN indexstats.avg_fragmentation_in_percent>=5 THEN 'REORGANIZE'
        ELSE NULL END AS [SQLQuery]  -- if <5 not required, so no query needed
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id 
    AND ind.index_id = indexstats.index_id 
WHERE 
--indexstats.avg_fragmentation_in_percent , e.g. >10, you can specify any number in percent 
ind.Name IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent DESC

https://stackoverflow.com/questions/1086812/script-for-rebuilding-and-reindexing-the-fragmented-index

Attach / Detach

Отсоединение:

Отсоединение: 

EXEC sp_detach_db 'TestDB', 'true';

Присоединение:

Присоединение: 

CREATE DATABASE [TestDB] ON 
  (FILENAME = N'D:\Data\TestDB.mdf'),
  (FILENAME = N'D:\Data\TestDB.ldf')
FOR ATTACH
--или
EXEC sp_attach_db @dbname = 'TestDB',
  @filename1 = N'D:\TestDB.mdf', 
  @filename2 = N'D:\TestDB_log.ldf'

если присоединилась в режиме READ_ONLY: 

ALTER DATABASE [TestDB] SET READ_WRITE


xp_cmdshell

Пример монтирования сетевого диска:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

--Get value advanced options
EXEC sp_configure 'show advanced options';
--To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', '1'
GO
RECONFIGURE
GO
--Get value xp_cmdshell
EXEC sp_configure 'xp_cmdshell';
--To allow xp_cmdshell
EXEC sp_configure 'xp_cmdshell','1'
GO
RECONFIGURE
GO
-- Mount
EXEC xp_cmdshell 'net use Z: "\\fs.domain.com\backup" /Y /persistent:yes'
--Revert
 
EXEC sp_configure 'xp_cmdshell','0'
GO
EXEC sp_configure 'xp_cmdshell'
EXEC sp_configure 'show advanced options', '0'
GO
EXEC sp_configure 'show advanced options'


после необходимо вернуть значения 'xp_cmdshell' и 'show advanced options'

Удалить файл из файловой группы

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

DBCC SHRINKFILE (LogicalFileName, emptyfile)
GO
ALTER DATABASE vsonline REMOVE FILE LogicalFileName


файл не должен быть primary

Проверить является ли файл primary: 

SELECT FILEPROPERTY('LogicalFileName', 'IsPrimaryFile')

Перенос базы tempdb

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = temp2, FILENAME = 'T:\Data\tempdb_mssql_2.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp3, FILENAME = 'T:\Data\tempdb_mssql_3.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp4, FILENAME = 'T:\Data\tempdb_mssql_4.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp5, FILENAME = 'T:\Data\tempdb_mssql_5.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp6, FILENAME = 'T:\Data\tempdb_mssql_6.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp7, FILENAME = 'T:\Data\tempdb_mssql_7.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp8, FILENAME = 'T:\Data\tempdb_mssql_8.ndf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'T:\Data\templog.ldf');
GO


Database Mail

Экспорт настроек Database Mail
Пароли не экспортируются
После переноса настроек необходимо вернуть «Configuration option» в исходное значение

Reverse_Engineer_database_Mail_Settings.sql

Reverse_Engineer_database_Mail_Settings.sql 

USE msdb
GO
DECLARE @TheResults VARCHAR(MAX),
 @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
 BEGIN
 --CREATE Profile [' + p.name + ']
 EXECUTE msdb.dbo.sysmail_add_profile_sp
 @profile_name = ''' + p.name + ''',
 @description = ''' + ISNULL(p.description,'') + ''';
 END --IF EXISTS profile
 '
 +
 '
 IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
 BEGIN
 --CREATE Account [' + a.name + ']
 EXECUTE msdb.dbo.sysmail_add_account_sp
 @account_name = ' + CASE WHEN a.name IS NULL THEN ' NULL ' ELSE + '''' + a.name + '''' END + ',
 @email_address = ' + CASE WHEN a.email_address IS NULL THEN ' NULL ' ELSE + '''' + a.email_address + '''' END + ',
 @display_name = ' + CASE WHEN a.display_name IS NULL THEN ' NULL ' ELSE + '''' + a.display_name + '''' END + ',
 @replyto_address = ' + CASE WHEN a.replyto_address IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address + '''' END + ',
 @description = ' + CASE WHEN a.description IS NULL THEN ' NULL ' ELSE + '''' + a.description + '''' END + ',
 @mailserver_name = ' + CASE WHEN s.servername IS NULL THEN ' NULL ' ELSE + '''' + s.servername + '''' END + ',
 @mailserver_type = ' + CASE WHEN s.servertype IS NULL THEN ' NULL ' ELSE + '''' + s.servertype + '''' END + ',
 @port = ' + CASE WHEN s.port IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
 @username = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity + '''' END + ',
 @password = ''NotTheRealPassword'',
 @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
 @enable_ssl = ' + CASE WHEN s.enable_ssl = 1 THEN ' 1 ' ELSE ' 0 ' END + ';
 END --IF EXISTS account
 '
 + '
IF NOT EXISTS(SELECT *
 FROM msdb.dbo.sysmail_profileaccount pa
 INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
 INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
 WHERE p.name = ''' + p.name + '''
 AND a.name = ''' + a.name + ''')
 BEGIN
 -- Associate Account [' + a.name + '] to Profile [' + p.name + ']
 EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = ''' + p.name + ''',
 @account_name = ''' + a.name + ''',
 @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
 END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
 FROM msdb.dbo.sysmail_profileaccount pa
 INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
 INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
 WHERE p.name = ''' + p.name + '''
 AND a.name = ''' + a.name + ''')
 BEGIN
 EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
 END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
 BEGIN
 EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
 END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
 BEGIN
 EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
 END
*/
 '
FROM sysmail_profile p
INNER JOIN sysmail_profileaccount pa ON p.profile_id = pa.profile_id
INNER JOIN sysmail_account a ON pa.account_id = a.account_id
LEFT OUTER JOIN sysmail_server s ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c ON s.credential_id = c.credential_id
 ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
 E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
 E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
 E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
 --E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,
 Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),
 ItemSplit(
 ItemOrder,
 Item
 ) AS (
 SELECT N,
 SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))
 FROM Tally
 WHERE N < DATALENGTH(@vbCrLf + @TheResults)
 --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf
 AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter
 )
 SELECT
 ROW_NUMBER() OVER (ORDER BY ItemOrder) AS ItemID,
 Item
 FROM ItemSplit


https://www.sqlservercentral.com/forums/topic/database-mail-export

SQL Agent

Экспорт настроек SQL Agent Operators

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

SELECT * 
FROM [msdb].[dbo].[sysoperators]
 
SELECT
 'EXECUTE msdb.dbo.sp_update_operator
 @name = N'''+NAME+''',
 @email_address = N'''+email_address+''';'
FROM msdb.dbo.sysoperators


https://learn.microsoft.com/ru-ru/sql/relational-databases/system-stored-procedures/sp-add-operator-transact-sql

Database Mail

Для доступа к отправке почты, добавить права:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE msdb;
CREATE USER username FOR LOGIN username;
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = 'username';
GO


https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-common-errors

Проверить отправку почты (профиль должен быть настроен):

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

EXECUTE AS USER  = 'app_vsomigration';
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'profile@domain.com',
  @recipients = 'toemail@domain.com',
  @body = 'BODY',
  @subject = 'TEST'
REVERT;


Auto Growth

Показать текущие настройки Growth всех баз кроме системных

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

SELECT
    DB_NAME(mf.database_id) AS [DatabaseName],
    mf.name AS [LogicalName],
    mf.type_desc AS [FileType],
    mf.physical_name AS [FilePath],
    CONVERT(DECIMAL(18,2), mf.size / 128.0) AS [CurrentSizeMB],
    CASE 
        WHEN mf.is_percent_growth = 1 THEN 
            CAST(mf.growth AS VARCHAR(20)) + ' %'
        ELSE 
            RTRIM(
                CASE 
                    WHEN mf.growth * 8.0 / 1024 = FLOOR(mf.growth * 8.0 / 1024)
                        THEN CAST(CAST(mf.growth * 8.0 / 1024 AS INT) AS VARCHAR(20))
                    ELSE 
                        CAST(ROUND(mf.growth * 8.0 / 1024, 2) AS VARCHAR(20))
                END
            ) + ' MB'
    END AS [AutoGrowth],
    CASE 
        WHEN mf.max_size = -1 THEN 'Unlimited'
        ELSE 
            RTRIM(
                CASE 
                    WHEN mf.max_size * 8.0 / 1024 = FLOOR(mf.max_size * 8.0 / 1024)
                        THEN CAST(CAST(mf.max_size * 8.0 / 1024 AS INT) AS VARCHAR(20))
                    ELSE 
                        CAST(ROUND(mf.max_size * 8.0 / 1024, 2) AS VARCHAR(20))
                END
            ) + ' MB'
    END AS [MaxSize]
FROM sys.master_files AS mf
ORDER BY DB_NAME(mf.database_id), mf.type_desc;


Изменить Growth для всех баз кроме системных (для данных 1024MB, лога 512MB):

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

DECLARE @DataGrowthMB INT = 1024;
DECLARE @LogGrowthMB  INT = 512;
 
DECLARE @name NVARCHAR(256)
DECLARE @SQL NVARCHAR(MAX) = N'';
 
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
  AND state = 0 -- database is online
  AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY name DESC
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
  WITH Files AS (
      SELECT
          d.name AS db_name,
          mf.name AS file_name,
          mf.type_desc
      FROM sys.databases d
      JOIN sys.master_files mf ON d.database_id = mf.database_id
      WHERE d.name = @name
  )
  SELECT 
      @SQL = STRING_AGG(cmd, CHAR(10) + CHAR(10))
  FROM (
      SELECT 
          N'USE [' + db_name + N'];' + CHAR(10) +
          N'ALTER DATABASE [' + db_name + N'] MODIFY FILE (NAME = N''' + file_name + N''', FILEGROWTH = ' +
          CASE WHEN type_desc = 'LOG' THEN CAST(@LogGrowthMB AS NVARCHAR(MAX))
               ELSE CAST(@DataGrowthMB AS NVARCHAR(MAX))
          END + N'MB);'
          AS cmd
      FROM Files
  ) AS t;
 
  PRINT @SQL;
  EXEC sp_executesql @SQL;
 
  FETCH NEXT FROM db_cursor INTO @name
END
 
CLOSE db_cursor
DEALLOCATE db_cursor


Alter databases

Включить на всех базах, кроме системных мониторинг производительности с использованием хранилища запросов (https://learn.microsoft.com/ru-ru/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store):

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

DECLARE @name sysname;
DECLARE @SQL nvarchar(MAX);
DECLARE db_cursor CURSOR READ_ONLY FOR
 
SELECT name
FROM master.sys.databases
WHERE database_id > 4
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY name DESC
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = N'ALTER DATABASE [' + @name + N'] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);';
   PRINT @SQL;
   EXEC(@SQL);
   FETCH NEXT FROM db_cursor INTO @name
END
 
CLOSE db_cursor
DEALLOCATE db_cursor


Проблемы

База в состоянии Restoring

Выполнить: 

RESTORE DATABASE dbname WITH RECOVERY

База в состоянии In Recovery

Проверить процент выполнения:

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

DECLARE @DBName VARCHAR(64) = 'databasename'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [Text] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog
 
SELECT TOP 5
	[LogDate]
	,SUBSTRING([Text], CHARINDEX(') is ', [Text]) + 4,CHARINDEX(' complete (', [Text]) - CHARINDEX(') is ', [Text]) - 4) AS PercentComplete
	,CAST(SUBSTRING([Text], CHARINDEX('approximately', [Text]) + 13,CHARINDEX(' seconds remain', [Text]) - CHARINDEX('approximately', [Text]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
	,CAST(SUBSTRING([Text], CHARINDEX('approximately', [Text]) + 13,CHARINDEX(' seconds remain', [Text]) - CHARINDEX('approximately', [Text]) - 13) AS FLOAT)/3600.0 AS HoursRemaining
	,[Text]
 
FROM @ErrorLog 
WHERE CHARINDEX(@DBName, [Text]) > 0 AND CHARINDEX('approximately', [Text]) > 0
ORDER BY [LogDate] DESC


Удалить базу:

  1. Остановить сервер
  2. Удалить ldf и mdf файлы базы
  3. Запустить сервер
  4. Удалить базу

База в состоянии Recovery Pending

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть 

USE master;
GO
ALTER DATABASE DB1 SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE DB1 SET ONLINE WITH ROLLBACK IMMEDIATE;


Переименование базы Msg 5030

Решение:

  1. Установить базу в режим SINGLE_USER
  2. Переименовать базу
  3. Вернуть режим базы MULTI_USER 
ALTER DATABASE OLD_DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE OLD_DBName MODIFY NAME = NEW_DBNAME;
ALTER DATABASE NEW_DBNAME SET MULTI_USER;

https://sqlconjuror.com/sql-server-resolve-error-5030-to-rename-database/
https://docs.microsoft.com/ru-ru/sql/relational-databases/databases/set-a-database-to-single-user-mode

Удаление LogFile Msg 5009

Файл физически удалён, но логически отображается.

Нажмите, чтобы отобразить

Нажмите, чтобы скрыть

Ошибка: Drop failed for LogFile $NAME. One or more files listed in the statement could not be found or could not be initialized. (Microsoft SQL Server, Error: 5009)

Решение: Создать и удалить ещё один файл 

USE DBName;
GO
ALTER DATABASE [DBName] ADD FILE ( NAME = N'DBName_log2', FILENAME = N'DISK:\Data\Test_log2.ldf', SIZE = 1MB, MAXSIZE = 1MB );
GO
BACKUP LOG Test TO DISK = N'DISK:\Backup\DBName.tl' WITH COMPRESSION;
GO
DBCC SHRINKFILE (N'DBName_log2' , EMPTYFILE);
GO
ALTER DATABASE [DBName]  REMOVE FILE [DBName_log2];

https://learn.microsoft.com/ru-ru/sql/relational-databases/errors-events/mssqlserver-5009-database-engine-error

Установка на контроллер домена 0x851A001A

Решение:
Во время установки назначить пользователя для запуска:

  • NT AUTHORITY\NETWORK SERVICE
  • NT AUTHORITY\LOCAL SERVICE

https://blog.sqlauthority.com/2017/01/27/sql-server-sql-installation-fails-error-code-0x851a001a-wait-database-engine-recovery-handle-failed/

Удаление пользователя

Ошибка: 

 Error 15138, Level 16, State 1, Line 1
    The database principal owns a fulltext catalog in the database, and cannot be dropped.

Решение: Изменить хозяина sys.fulltext_catalogs для этой базы
Посмотреть в базе: 

SELECT * FROM sys.fulltext_catalogs

Поменять: 

ALTER AUTHORIZATION ON Fulltext Catalog::[CatalogName] TO [dbo];

Изменение owner БД

Ошибка: 

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.

Решение: 

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [username]

или 

USE [dbname]; EXEC sp_dropuser [username];
USE [dbname]; EXEC sp_changedbowner [username];

https://vmblog.ru/izmenit-vladelca-mssql-server/
https://subhrosaha.wordpress.com/2012/04/26/sql-server-error-the-proposed-new-database-owner-is-already-a-user-or-aliased-in-the-database/

Отправка почты

Ошибка: 

Msg 229, Level 14, State 5, Procedure msdb.dbo.sp_send_dbmail, Line 1 [Batch Start Line 0]
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

Решение: Database Mail

Ссылки

https://learn.microsoft.com/RU-RU/sql/linux/sql-server-linux-configure-environment-variables - Настройка параметров SQL Server с помощью переменных среды в Linux
https://www.microsoft.com/en-us/download/details.aspx?id=39046 - Microsoft Kerberos Configuration Manager for SQL Server (Checking SPN)
https://its.1c.ru/db/metod8dev/content/5861/hdoc@2239bf5c - Проблемы и решения / Высокая загрузка CPU на сервере СУБД MS SQL Server

how-to/mssql.txt · Последнее изменение: — lioncub