SQL Server w środowisku domenowym

Dobór edycji, konto usługi gMSA, połączenia z instancjami nazwanymi (SQL Browser, SPN, Kerberos, certyfikat), loginy i role, obcinanie logu transakcji oraz kopie zapasowe bez SQL Agenta w domenie Active Directory.

Bazy danychBezpieczeństwo

SQL Server w domenie Active Directory zależy od kilku elementów, które muszą być zgodne: edycji i jej limitów, konta usługi, nazwy, portu i SPN instancji, loginów powiązanych z grupami katalogu oraz modelu odzyskiwania i kopii zapasowych. Błąd w jednym z nich daje objawy w innym miejscu: ciche przejście na NTLM, bazę bez loginu po odtworzeniu, log zapełniający dysk. Dokument opisuje te elementy na wspólnym przykładzie: domena corp.example.com, serwery srv-sql-01 i srv-sql-02, instancje CRM, ERP i HR, konta gMSA CORP\svc-sql$ i CORP\svc-sqlbackup$ oraz grupy GG-SQL-Hosts i GG-SQL-Admins.

Wybór edycji: limity SQL Server Express

Liczby dotyczą SQL Server 2025 (17.x), chyba że zaznaczono inaczej. Express ma trzy limity skali: moc obliczeniową ograniczoną do mniejszej z wartości 1 gniazdo lub 4 rdzenie, pulę bufora do 1410 MB na instancję oraz maksymalny rozmiar bazy 50 GB (10 GB w wersjach 2019 i 2022). Limity procesora i pamięci dotyczą instancji, a limit rozmiaru każdej bazy osobno; liczba baz w instancji nie zależy od edycji.

Do limitu rozmiaru liczy się łączny przydzielony rozmiar plików danych bazy, razem z wolnym miejscem wewnątrz plików; plik logu się nie wlicza, a drugi plik danych nic nie daje. Gdy plik ma urosnąć ponad limit, SQL Server odmawia: ALTER DATABASE kończy się błędem 1827, a zapytanie potrzebujące nowego miejsca dostaje błąd 1105. Odczyty działają dalej, ale zapisy wymagające nowych stron zatrzymują się. Przed zmianą edycji istnieją inne drogi. Kompresja danych (ROW i PAGE), partycjonowanie i columnstore są dostępne także w Express, ale zmiana schematu bazy dostawcy wymaga jego zgody. Miejsce zwolnione wewnątrz pliku jest od razu dostępne dla nowych danych, ale przydzielony rozmiar się nie zmienia, więc udział w limicie spada dopiero po DBCC SHRINKFILE, który fragmentuje indeksy i jest operacją jednorazową. Dla bazy, która w wersji 2019 lub 2022 zbliża się do 10 GB, najtańszą odpowiedzią bywa aktualizacja do Express 2025, o ile dostawca aplikacji wspiera tę wersję; limity procesora i pamięci się nie zmieniają.

Limit 1410 MB dotyczy puli bufora, czyli pamięci podręcznej stron danych i indeksów, a nie całego procesu: sqlservr.exe zajmuje więcej, bo potrzebuje pamięci na stosy wątków, połączenia i biblioteki, więc zużycie powyżej 1,4 GB w Menedżerze zadań nie oznacza błędu konfiguracji. W drugą stronę limit działa bezwzględnie: max server memory powyżej tej wartości niczego nie zmienia, a dołożenie pamięci serwerowi nie pomaga. Znaczenie limitu zależy od tego, jaka część bazy jest czytana na co dzień: baza 20 GB, w której praca dotyczy głównie danych z ostatnich miesięcy, może działać poprawnie w 1,4 GB. Sygnałem problemu jest pula bufora stale wypełniona do limitu, niski licznik Page life expectancy w godzinach pracy (np. kilkadziesiąt sekund) i duża liczba odczytów z dysku. Limity procesora i pamięci dotyczą pojedynczej instancji, więc kilka instancji na serwerze pozwala wykorzystać moc, której jedna instancja nie użyje; nie pomaga to na limit rozmiaru bazy.

Brak funkcji bywa ważniejszy niż limity skali. Express nie ma SQL Server Agenta, planów konserwacji, wbudowanego log shippingu, kompresji i szyfrowania kopii, TDE, FCI, grup dostępności (jedyną drogą odtworzenia jest wtedy przywrócenie kopii), Database Mail (alerty muszą pochodzić z systemu monitorowania) ani Resource Governora. Bez Agenta kopie zapasowe, utrzymanie indeksów i DBCC CHECKDB uruchamia Harmonogram zadań z sqlcmd.

EdycjaLicencjaProcesor na instancjęPula buforaRozmiar bazy
Expressbezpłatnamniejsza z wartości: 1 gniazdo, 4 rdzenie1410 MB50 GB
Standardpłatna, produkcjamniejsza z wartości: 4 gniazda, 32 rdzenie256 GB524 PB
Enterprisepłatna, produkcjamaksimum systemu operacyjnegomaksimum systemu operacyjnego524 PB

Edycje Developer i Evaluation mają funkcje edycji płatnych, ale licencję wyłącznie na tworzenie, testy i ocenę, więc nie nadają się na serwery produkcyjne.

Przejście na Standard uzasadnia co najmniej jeden z sygnałów: pliki danych przekraczają około 80% limitu, dojdą do niego w ciągu kilku miesięcy i archiwizacja nie wchodzi w grę; pula bufora stoi stale na 1410 MB, Page life expectancy w godzinach pracy jest niska, a dysk obsługuje głównie odczyty z bazy; dostawca, docelowy czas i punkt odtworzenia (RTO, RPO), polityka bezpieczeństwa lub audyt wymagają funkcji, której Express nie ma. Wynik poniżej 70% limitu rozmiaru nie wymaga działań, powyżej 80% jest czasem na decyzję: archiwizację, aktualizację wersji albo zmianę edycji. Zmiana Express na Standard w tej samej wersji odbywa się w miejscu, bez przenoszenia baz: SQL Server Installation Center, Maintenance, Edition Upgrade, z kluczem produktu Standard (w trybie cichym setup.exe /q /ACTION=EditionUpgrade /INSTANCENAME=CRM z parametrami /PID i /IACCEPTSQLSERVERLICENSETERMS). Instancja restartuje usługi, więc wymaga krótkiej przerwy, a usługę Agenta (po instalacji Express wyłączoną) trzeba ustawić na start automatyczny i skonfigurować jej konto. Enterprise jest uzasadniony, gdy instancja potrzebuje więcej niż 32 rdzeni lub 256 GB puli bufora, grup dostępności z kilkoma bazami i więcej niż dwiema replikami albo operacji online, takich jak przebudowa indeksów bez blokowania tabeli.

Konto usługi: gMSA

Hasło konta usługi ustawionego z opcją „Hasło nigdy nie wygasa” zwykle zostaje bez zmian latami, a zna je kilka osób, także takie, które już nie pracują w organizacji. Zmiana wymaga nowego hasła w Active Directory, ręcznego wpisania go w każdej usłudze SQL na każdym serwerze i restartu każdej instancji; usługa pominięta przy zmianie nie wystartuje po najbliższym restarcie serwera i zgłosi błąd 1069 („The service did not start due to a logon failure”). Grupowe zarządzane konto usługi (gMSA, group Managed Service Account) usuwa ten problem u źródła.

Mechanizm

Konto gMSA to obiekt Active Directory zbliżony budową do konta komputera. Hasło oblicza kontroler domeny na podstawie klucza głównego usługi KDS (Key Distribution Service) i atrybutów konta, a domyślnie zmienia je co 30 dni. Interwał ustawia się wyłącznie przy tworzeniu konta (-ManagedPasswordIntervalInDays); inny interwał wymaga nowego konta. Hasło mogą pobrać tylko podmioty wpisane w atrybucie msDS-GroupMSAMembership (parametr -PrincipalsAllowedToRetrieveManagedPassword), najprościej grupa komputerów, tu GG-SQL-Hosts z serwerami srv-sql-01 i srv-sql-02. Serwer z tej grupy pobiera bieżące i poprzednie hasło, dlatego zmiana hasła nie wymaga restartu usług. SQL Server obsługuje gMSA jako konto usługi od wersji 2014.

CechaKonto wirtualneZwykłe konto domenowegMSA
Kto zna hasłonikt, konto nie ma hasłaadministrator, który je ustawił, i każdy, komu je przekazanonikt, hasło oblicza kontroler domeny
Zmiana hasłanie dotyczyręcznie w AD i w każdej usłudze, z restartemautomatycznie, domyślnie co 30 dni, bez restartu
Dostęp do zasobów sieciowychjako konto komputera, np. CORP\srv-sql-01$jako to kontojako CORP\svc-sql$
Praca na kilku serwerachnietaktak, na serwerach z grupy GG-SQL-Hosts

Instalator domyślnie uruchamia silnik na koncie wirtualnym, na przykład NT SERVICE\MSSQL$CRM. To poprawne ustawienie dla pojedynczego serwera bez zasobów sieciowych. W sieci konto wirtualne występuje jako konto komputera, więc uprawnienie do udziału z kopiami dostaje cały serwer ze wszystkimi usługami, a SPN dla własnych nazw instancji trzeba rejestrować na koncie komputera i przenosić razem z instancją.

Tworzenie konta

Kontrolery domeny potrzebują klucza głównego KDS, zanim zaczną obliczać hasła gMSA. Klucz tworzy się raz na las, na kontrolerze domeny, z uprawnieniami Domain Admins lub Enterprise Admins, po sprawdzeniu Get-KdsRootKey, czy już nie istnieje. Mimo nazwy przełącznika -EffectiveImmediately kontrolery czekają do 10 godzin od utworzenia klucza, aby replikacja zdążyła go rozesłać, zanim pozwolą utworzyć konto; datę wejścia w życie pokazuje pole EffectiveTime. W laboratorium z jednym kontrolerem dokumentacja dopuszcza datę 10 godzin wstecz (-EffectiveTime), w produkcji nie. Add-KdsRootKey wywołane bez parametrów ustawia datę wejścia w życie na 10 dni od chwili utworzenia, a nie na teraz. Klucza nie usuwa się i nie tworzy ponownie na próbę: kontrolery buforują stary klucz, a po ponownym utworzeniu trzeba zrestartować usługę KDC na każdym z nich.

Konto tworzy New-ADServiceAccount, domyślnie w kontenerze Managed Service Accounts. Parametr -DNSHostName jest wymagany dla konta grupowego, a -KerberosEncryptionType ogranicza szyfrowanie biletów do wskazanych typów, tu AES128 i AES256. Kolejny serwer SQL dodaje się do grupy GG-SQL-Hosts, a nie do samego konta: Set-ADServiceAccount z parametrem -PrincipalsAllowedToRetrieveManagedPassword ustawia listę uprawnionych od nowa i łatwo nim odebrać dostęp działającym serwerom.

# Tylko gdy Get-KdsRootKey nic nie zwraca: klucz główny KDS, raz na las
Add-KdsRootKey -EffectiveImmediately

# Grupa komputerów, które mogą pobrać hasło konta
New-ADGroup -Name GG-SQL-Hosts -GroupScope Global -GroupCategory Security
Add-ADGroupMember -Identity GG-SQL-Hosts -Members 'srv-sql-01$', 'srv-sql-02$'

# Konto gMSA; interwał zmiany hasła można ustawić tylko teraz
New-ADServiceAccount -Name svc-sql -DNSHostName svc-sql.corp.example.com `
    -PrincipalsAllowedToRetrieveManagedPassword GG-SQL-Hosts `
    -ManagedPasswordIntervalInDays 30 `
    -KerberosEncryptionType AES128, AES256

Na każdym serwerze SQL potrzebny jest moduł ActiveDirectory dla PowerShella (Install-WindowsFeature RSAT-AD-PowerShell). Install-ADServiceAccount -Identity svc-sql instaluje konto na serwerze i buforuje je lokalnie, a Test-ADServiceAccount -Identity svc-sql sprawdza, czy serwer może pobrać hasło; oba wymagają sesji administratora. Wynik False ma zwykle jedną z trzech przyczyn: serwer nie należy do grupy GG-SQL-Hosts albo jeszcze o tym nie wie, klucz KDS nie dotarł do kontrolera domeny, z którym rozmawia serwer, albo serwer nie obsługuje typów szyfrowania wymaganych przez konto. Lista grup trafia do biletu Kerberos konta komputera w chwili jego wydania, więc serwer dodany do grupy po starcie ma jeszcze bilet ze starą listą i do jego odnowienia, zwykle do 10 godzin, nie pobierze hasła. Pomaga restart serwera albo klist purge -li 0x3e7 w wierszu poleceń administratora, który usuwa bilety sesji SYSTEM.

Zmiana konta usługi

Konto instancji zmienia się w SQL Server Configuration Manager: SQL Server Services, właściwości usługi SQL Server (CRM), zakładka Log On, opcja This account. Wpisuje się konto ze znakiem dolara na końcu (CORP\svc-sql$), a pola hasła zostają puste. Nowe konto działa po restarcie usługi i na ten czas bazy instancji są niedostępne; jest to jedyny przestój we wdrożeniu, więc zmianę planuje się w oknie serwisowym, a kolejne zmiany hasła odbywają się bez restartów. Przystawka Usługi (services.msc) nie jest właściwym narzędziem: Microsoft zaleca zmieniać konto usług SQL Servera wyłącznie jego narzędziami, a Configuration Manager aktualizuje też lokalny magazyn zabezpieczeń chroniący klucz główny usługi. Uprawnienia do plików i rejestru instancji oraz prawo Log on as a service ma według dokumentacji identyfikator usługi NT SERVICE\MSSQL$CRM, więc w większości składników zmiana konta nie wymaga ponownego nadawania uprawnień.

Przed przełączeniem na nowe konto przenosi się wszystko, co było nadane staremu: uprawnienia do udziałów sieciowych (na przykład z kopiami), loginy starego konta na innych instancjach i ustawienia delegacji. SPN usuwa się ze starego konta i rejestruje na CORP\svc-sql$.

Jedno konto dla wszystkich instancji czy kilka

Wspólne konto svc-sql dla instancji CRM, ERP i HR na obu serwerach jest najprostsze: SPN wszystkich instancji leżą na jednym koncie, delegację konfiguruje się raz, a przeniesienie instancji ze srv-sql-01 na srv-sql-02 sprowadza się do zmiany rekordu DNS. Ceną jest zasięg skutków przejęcia. Każdy proces działający jako SYSTEM na dowolnym serwerze z grupy GG-SQL-Hosts może pobrać hasło konta, bo tak pobiera je sam system. Atakujący, który przejmie jedną instancję, na przykład przez xp_cmdshell, działa w sieci jako CORP\svc-sql$, czyli z dostępem do udziałów wszystkich instancji, z ich delegacją i z loginami nadanymi temu kontu na innych serwerach.

Zasada praktyczna to jedno konto na grupę instancji o podobnej wrażliwości danych i tych samych administratorach; instancja z danymi kadrowymi (HR) dostaje osobne konto gMSA. Osobne konto tylko częściowo ogranicza skutki przejęcia innej instancji na tym samym serwerze, bo konta usług SQL Servera mają uprawnienie SeImpersonatePrivilege, które często pozwala podnieść uprawnienia do poziomu systemu; pełną izolację daje dopiero osobny serwer.

Połączenie z instancją nazwaną

Połączenie z instancją nazwaną zależy od elementów, które klient ustala sam: portu, usługi SQL Browser, nazwy w DNS, SPN na koncie usługi i certyfikatu serwera. Każdy z nich zawodzi osobno: brak Browsera daje błąd 26, brak SPN po cichu przełącza uwierzytelnianie na NTLM, a niezaufany certyfikat przerywa połączenie po aktualizacji sterownika. Wspólne rozwiązanie to stały port, osobna nazwa DNS dla instancji, ręcznie zarejestrowany SPN i certyfikat z wewnętrznego urzędu certyfikacji.

Instancje i SQL Browser

Na jednym serwerze może działać jedna instancja domyślna i wiele nazwanych. Każda to osobna usługa Windows z własnym procesem sqlservr.exe, konfiguracją sieci i portem. Instancja domyślna (MSSQLSERVER) nie ma nazwy w adresie i nasłuchuje na TCP 1433. Instancja nazwana (usługa MSSQL$CRM, adres srv-sql-01\CRM) dostaje domyślnie port dynamiczny wybierany przy starcie usługi, bo dwie instancje nie mogą używać tego samego portu. Express instaluje się jako instancja nazwana SQLEXPRESS i podlega tym samym zasadom.

Usługa SQL Server Browser przy starcie zajmuje port UDP 1434 i zapamiętuje porty TCP oraz nazwy potoków wszystkich instancji na serwerze. Klient z adresem srv-sql-01\CRM wysyła na UDP 1434 zapytanie z nazwą instancji (format opisuje MC-SQLR), a dopiero po odpowiedzi otwiera połączenie TCP z właściwym portem. Wynikają z tego zasady:

  • bez działającego Browsera połączenie w postaci serwer\INSTANCJA bez portu kończy się błędem 26 (Error Locating Server/Instance Specified), a połączenia z podanym portem działają dalej;
  • instancja domyślna nie potrzebuje Browsera, dopóki nasłuchuje na porcie 1433; przy innym porcie klient musi go podać;
  • instalator ustawia automatyczny start Browsera przy instalacji instancji nazwanej, także Express; zmiana na start ręczny albo wyłączony daje objaw dopiero przy najbliższym restarcie serwera, więc przyczyna bywa odległa w czasie od skutku;
  • Browser przyjmuje zapytania bez uwierzytelnienia, więc reguła zapory dla UDP 1434 dopuszcza tylko podsieci aplikacji, tutaj 10.10.30.0/24;
  • na serwerze z kilkoma adresami IP odpowiedź Browsera może wyjść z innego adresu niż ten, na który klient wysłał zapytanie; zapora Windows po stronie klienta odrzuca taki datagram i połączenie przez serwer\INSTANCJA kończy się przekroczeniem czasu, mimo że Browser działa, a rozwiązaniem jest port w ciągu połączenia.

Stały port zamiast dynamicznego

Port dynamiczny może się zmienić po restarcie instancji, jeśli zajmie go inny proces. Klienci odnajdują go przez Browsera, więc połączenie nadal działa, ale przestają pasować reguły zapory na konkretny port, ręcznie zarejestrowane SPN i ciągi połączenia z portem. Stały port ustawia się w SQL Server Configuration Manager: Protocols for CRM, TCP/IP, zakładka IP Addresses, sekcja IPAll. Pole TCP Dynamic Ports musi być puste, a TCP Port zawiera numer portu; zmiana działa po restarcie usługi. Wartość 0 w polu TCP Dynamic Ports nie wyłącza portu dynamicznego, tylko go włącza. Numer portu wybiera się spoza zakresu 49152–65535, którego Windows używa dla portów efemerycznych. Klienci łączący się przez nazwa,port nie potrzebują wtedy Browsera, który zostaje wygodą dla administratorów wpisujących w SSMS srv-sql-01\CRM.

Własna nazwa DNS dla każdej instancji

Osobna nazwa DNS dla każdej instancji jest wygodniejsza niż udostępnianie jej pod nazwą serwera. Aplikacja nie zna wtedy nazwy serwera, a przeniesienie instancji sprowadza się do zmiany rekordu DNS; przy tym samym koncie gMSA SPN zostaje bez zmian.

InstancjaNazwa DNS (rekord A)Port TCPSPN
CRMsql-crm.corp.example.com14331MSSQLSvc/sql-crm.corp.example.com:14331
ERPsql-erp.corp.example.com14332MSSQLSvc/sql-erp.corp.example.com:14332
HRsql-hr.corp.example.com14333MSSQLSvc/sql-hr.corp.example.com:14333

Alias w postaci rekordu CNAME wygląda na równoważny, ale sterownik klienta zwykle buduje SPN z nazwy kanonicznej, czyli z nazwy docelowej rekordu. Klient łączący się z aliasem prosi o bilet dla MSSQLSvc/srv-sql-01.corp.example.com:14331, a SPN zarejestrowany dla aliasu nie zostanie użyty. Nazwa instancji musi być rekordem A.

Adres w ciągu połączenia

Adres serwera ma postać [protokół:]serwer[\instancja][,port], a prefiks wymusza protokół:

AdresZnaczenie
srv-sql-01\CRMport od SQL Browsera (UDP 1434)
tcp:sql-crm.corp.example.com,14331tylko TCP, port podany wprost
np:\\srv-sql-01\pipe\MSSQL$CRM\sql\querypotok nazwany przez SMB (TCP 445)
lpc:srv-sql-01\CRMpamięć współdzielona, tylko lokalnie

Gdy podano port, Browser nie jest pytany. W aplikacjach stosuje się pełną nazwę DNS zamiast adresu IP: przy adresie IP sterownik próbuje rozwiązać go do pełnej nazwy i z niej buduje SPN, a NTLM pojawia się, gdy rozwiązanie się nie uda albo SPN dla tej nazwy nie istnieje. Sterownik zwykle nie dopasuje też adresu IP do nazwy w certyfikacie, chyba że znajduje się w rozszerzeniu SAN. Bez prefiksu sterownik próbuje kolejnych włączonych protokołów, więc po awarii TCP komunikat może dotyczyć ostatniej próby (np. Named Pipes Provider, error: 40); prefiks tcp: usuwa tę niejednoznaczność. Potoki nazwane są po instalacji wyłączone dla połączeń sieciowych we wszystkich edycjach.

W nowej instalacji edycji Express i Developer protokół TCP/IP jest wyłączony, w odróżnieniu od Enterprise i Standard: połączenia lokalne przechodzą, a z innych komputerów nie łączy się nikt. TCP/IP włącza się w SQL Server Configuration Manager (SQL Server Network Configuration, Protocols for HR, TCP/IP, Enable) i restartuje usługę instancji.

Szyfrowanie domyślnie wymagane i certyfikat

Nowe wersje sterowników i narzędzi Microsoftu domyślnie wymagają szyfrowania i sprawdzają certyfikat serwera (stan na październik 2026):

Sterownik lub narzędzieOd wersjiUstawienie domyślne
Microsoft.Data.SqlClient4.0Encrypt=True, od 5.0 Mandatory
ODBC Driver for SQL Server18Encrypt=yes (Mandatory)
OLE DB Driver for SQL Server (MSOLEDBSQL19)19Encrypt=Mandatory
SQL Server Management Studio20Encryption: Mandatory

Instancja bez skonfigurowanego certyfikatu generuje przy starcie certyfikat samopodpisany, któremu klienci domyślnie nie ufają, więc po aktualizacji sterownika aplikacja, która działała latami, zgłasza błąd The certificate chain was issued by an authority that is not trusted. Najszybsze obejście to TrustServerCertificate=True: połączenie pozostaje szyfrowane, ale klient nie sprawdza tożsamości serwera, więc ktoś, kto przejmie ruch, może podstawić własny serwer. Przy Encrypt=Strict (TDS 8.0, od SQL Server 2022) to ustawienie nie działa. Encrypt=False jest gorszym wyjściem: gdy serwer nie wymusza szyfrowania, szyfrowane jest tylko logowanie, a dane płyną otwartym tekstem.

Właściwe rozwiązanie to certyfikat z wewnętrznego urzędu certyfikacji (AD CS), któremu ufają komputery w domenie. Wystawia się go z rozszerzonym użyciem klucza Server Authentication i wszystkimi nazwami używanymi przez klientów w rozszerzeniu SAN (sql-crm.corp.example.com, sql-erp.corp.example.com, sql-hr.corp.example.com oraz srv-sql-01.corp.example.com). Klucz prywatny musi być przechowywany przez starszego dostawcę CSP (KeySpec równy AT_KEYEXCHANGE), a nie przez KSP, a konto usługi CORP\svc-sql$ potrzebuje prawa odczytu tego klucza w LocalMachine\My. Certyfikat wskazuje się dla każdej instancji osobno w Configuration Manager (Properties, zakładka Certificate), a zmiana działa po restarcie usługi. Datę ważności obejmuje monitorowanie certyfikatów, bo część klientów odrzuca wygasły certyfikat przy każdym połączeniu. Gdy aplikacja musi łączyć się nazwą, której nie ma w certyfikacie, Microsoft.Data.SqlClient od wersji 5.0 przyjmuje parametr HostNameInCertificate: zmienia on tylko porównanie nazwy i nie wyłącza kontroli łańcucha ani ważności, więc jest bezpieczniejszy niż TrustServerCertificate.

Uwierzytelnianie Kerberos i delegacja

Przy uwierzytelnianiu Windows sterownik klienta prosi kontroler domeny o bilet dla usługi o określonej nazwie, czyli SPN (Service Principal Name). Kontroler szuka konta z tym SPN w atrybucie servicePrincipalName i szyfruje bilet kluczem tego konta, a SQL Server odszyfrowuje go kluczem konta, na którym działa usługa. Dla SQL Servera SPN ma jedną z trzech postaci: MSSQLSvc/sql-crm.corp.example.com:14331 dla połączeń TCP (nazwa i port), MSSQLSvc/srv-sql-01.corp.example.com:CRM dla instancji nazwanej łączącej się przez potoki nazwane albo MSSQLSvc/srv-sql-01.corp.example.com dla instancji domyślnej przy protokole innym niż TCP.

Rys. 1. Klient zwykle buduje SPN z nazwy kanonicznej z DNS i portu; gdy kontroler domeny nie znajdzie go na koncie usługi, połączenie po cichu przechodzi na NTLM.
Opis tekstowy diagramu

Klient łączy się z ciągiem Server=sql-crm,14331. W kroku 1 rozwiązuje nazwę w DNS: rekord A wskazuje adres 10.10.20.11 i ustala nazwę kanoniczną.

W kroku 2 z nazwy kanonicznej i portu buduje nazwę SPN w postaci MSSQLSvc/sql-crm…:14331 i prosi kontroler domeny (KDC) o bilet. Kontroler domeny musi znaleźć ten SPN na koncie usługi svc-sql$.

W kroku 3 SQL Server odszyfrowuje bilet i uwierzytelnia klienta protokołem Kerberos. Gdy SPN nie istnieje na koncie usługi, połączenie po cichu przechodzi na NTLM, bez możliwości delegacji.

Sterownik zwykle rozwiązuje nazwę z ciągu połączenia w DNS i używa nazwy kanonicznej, a port bierze z ciągu połączenia albo od SQL Browsera; szczegóły zależą od stosu klienta. Jeśli kontroler domeny nie znajdzie SPN o tak zbudowanej nazwie, mechanizm Negotiate wybiera NTLM bez żadnego komunikatu. Połączenie działa, ale bez wzajemnego uwierzytelnienia serwera i bez delegacji.

ObjawTypowa przyczyna
auth_scheme równe NTLM przy połączeniu z innej maszynybrak SPN dla nazwy i portu, których używa klient
Cannot generate SSPI contextSPN zarejestrowany na innym koncie niż konto usługi albo ten sam SPN na dwóch kontach
NTLM tylko przy połączeniu przez aliasalias jest rekordem CNAME albo nie ma SPN dla nazwy aliasu
NTLM przy połączeniu po adresie IPadres nie został rozwiązany do pełnej nazwy albo SPN dla tej nazwy nie istnieje
NTLM po restarcie instancjiport dynamiczny inny niż port w zarejestrowanym SPN
ANONYMOUS LOGON na serwerze połączonympierwsze połączenie przez NTLM albo brak skonfigurowanej delegacji

Rejestracja SPN

SPN musi być zarejestrowany na koncie, na którym działa usługa; najlepiej sprawdza się gMSA, bo hasło zmienia kontroler domeny, a jedno konto obsługuje instancje na kilku serwerach. Przy starcie usługa SQL Server próbuje zarejestrować swoje SPN na koncie usługi, a przy zatrzymaniu je usunąć. Udaje się to tylko wtedy, gdy konto ma prawo odczytu i zapisu atrybutu servicePrincipalName na własnym obiekcie. Według dokumentacji SPN mogą zarejestrować konta wbudowane (np. NETWORK SERVICE), konta wirtualne, zarządzane konta usług (MSA) i konta z uprawnieniem do rejestracji SPN; przy pozostałych kontach rejestrację wykonuje ręcznie administrator domeny. Dla MSA zastrzeżono, że rejestracja wymaga nadanych praw odczytu i zapisu servicePrincipalName. Dokumentacja nie rozstrzyga osobno przypadku gMSA, więc przed poleganiem na rejestracji automatycznej sprawdza się dziennik błędów po starcie instancji; brak uprawnień objawia się komunikatem could not register the Service Principal Name z kodem 0x2098.

Rejestracja automatyczna obejmuje tylko nazwę serwera, a pełne prawo zapisu pozwala kontu dopisać sobie dowolny SPN, także należący do innej usługi. Przy instancjach nazwanych z własnymi nazwami DNS lepiej sprawdza się rejestracja ręczna, przy której wpis z kodem 0x2098 w dzienniku przy każdym starcie jest oczekiwany; ryzykiem jest pominięty albo nieaktualny SPN po zmianie portu, nazwy lub konta usługi. setspn wymaga uprawnień administratora domeny albo delegowanego prawa zapisu servicePrincipalName. Przełącznik -S odmawia rejestracji, gdy taki SPN istnieje już na innym koncie, a setspn -X wyszukuje duplikaty w domenie (poprawny wynik to found 0 group of duplicate SPNs). Przy przenoszeniu instancji na serwer z innym kontem usługi SPN najpierw usuwa się ze starego konta (setspn -D), a dopiero potem dodaje do nowego.

Delegacja dla serwerów połączonych

Kerberos jest warunkiem koniecznym, ale nie wystarczającym. Gdy instancja CRM wykonuje w imieniu użytkownika zapytanie przez serwer połączony do instancji ERP, konto usługi CRM musi mieć prawo przekazania jego tożsamości; przy NTLM kończy się to błędem Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON', bo NTLM nie pozwala przekazać tożsamości dalej. Serwery połączone obsługują delegację nieograniczoną, a od SQL Server 2017 CU17 także ograniczoną; delegacja ograniczona oparta na zasobach nie jest dla nich obsługiwana. Bezpieczny wariant to delegacja ograniczona tylko z protokołem Kerberos: w msDS-AllowedToDelegateTo konta usługi wpisuje się wyłącznie SPN instancji ERP, bez flagi TrustedToAuthForDelegation. Delegacji nieograniczonej (Trust this user for delegation to any service) nie włącza się: konto usługi mogłoby występować w imieniu każdego użytkownika wobec dowolnej usługi w domenie. Login serwera połączonego używa bieżącego kontekstu zabezpieczeń użytkownika (@useself = 'TRUE' w sp_addlinkedsrvlogin), a użytkownik ma dostęp także na instancji ERP.

setspn -S MSSQLSvc/sql-crm.corp.example.com:14331 'CORP\svc-sql$'
# Tylko gdy klienci łączą się przez potoki nazwane
setspn -S MSSQLSvc/srv-sql-01.corp.example.com:CRM 'CORP\svc-sql$'

# Konto usługi może przekazać tożsamość użytkownika tylko do usługi o tym SPN
Set-ADServiceAccount -Identity svc-sql -Add @{'msDS-AllowedToDelegateTo' = 'MSSQLSvc/sql-erp.corp.example.com:14332'}

Schemat uwierzytelnienia istniejących połączeń pokazuje kolumna auth_scheme w sys.dm_exec_connections (złączona z sys.dm_exec_sessions po session_id, z filtrem net_transport = 'TCP' i auth_scheme <> 'SQL'). Ocenia się połączenia z innych maszyn, bo połączenie z samego serwera pokazuje NTLM także przy poprawnym SPN. Każdy wiersz z NTLM to klient łączący się nazwą lub portem bez SPN, przez alias CNAME albo po adresie IP.

Loginy, użytkownicy i role

Login jest obiektem instancji. Przechowuje go baza master, widać go w sys.server_principals i decyduje on o tym, czy SQL Server przyjmie połączenie. Login Windows odpowiada kontu albo grupie z katalogu i nie ma hasła w SQL Serverze, a login SQL ma nazwę i skrót hasła zapisany w master. Użytkownik jest obiektem bazy (sys.database_principals); to jemu nadaje się uprawnienia i role. Jest zapisany w samej bazie, więc trafia do kopii zapasowej i wraca przy odtworzeniu. Z loginem łączy go SID zapisany w obu miejscach, a nie nazwa. Login Windows ma SID konta z Active Directory, taki sam na każdym serwerze w domenie, a login SQL dostaje SID przy CREATE LOGIN, więc ten sam login założony na dwóch serwerach ma dwa różne identyfikatory.

Grupy katalogu zamiast kont osobowych

Loginy dla pojedynczych osób tworzą listę, której nikt nie potrafi zweryfikować. Lepszy układ to loginy wyłącznie dla grup, na przykład GG-SQL-CRM-Read dla osób czytających dane CRM i GG-SQL-CRM-Write dla tych, które je zmieniają. Dla każdej grupy powstaje login na poziomie instancji (CREATE LOGIN [CORP\GG-SQL-CRM-Read] FROM WINDOWS) i użytkownik w bazie powiązany z nim przez SID (CREATE USER … FOR LOGIN …). Nadanie dostępu to dodanie osoby do grupy w Active Directory, a wyłączenie konta zamyka też dostęp do SQL Servera.

Loginu grupy nie da się wyłączyć poleceniem ALTER LOGIN … DISABLE, które kończy się błędem Msg 15151. Grupę blokuje się uprawnieniem DENY CONNECT SQL TO [CORP\GG-SQL-CRM-Read]. Odmowa ma pierwszeństwo przed nadaniami uzyskanymi inną drogą, więc obejmuje także członków grupy, którzy mają dostęp przez inną grupę albo własny login; nie dotyczy członków roli sysadmin. SQL Server odczytuje członkostwo w grupach z tokenu Windows w chwili logowania i trzyma je przez cały czas trwania połączenia: osoba dodana do grupy dostaje dostęp dopiero po ponownym zalogowaniu do Windows i nowym połączeniu, a usunięcie z grupy nie przerywa otwartych sesji, do czego służy KILL.

Role serwera i bazy

Rola sysadmin pozwala na wszystko, a odmowy (DENY) jej członków nie dotyczą. Instalator dodaje do niej konto sa, identyfikatory usług instancji (NT SERVICE\MSSQL$CRM, NT SERVICE\SQLAgent$CRM), NT SERVICE\SQLWriter i NT SERVICE\Winmgmt oraz konta wskazane podczas instalacji. Poza wpisami usług w roli powinna być jedna grupa: GG-SQL-Admins. Rola securityadmin zarządza loginami i nadaje uprawnienia, ale dokumentacja każe traktować ją jak równoważną sysadmin, bo możliwość nadawania uprawnień pozwala przypisać większość uprawnień serwera; tak samo traktuje się CONTROL SERVER. Od SQL Server 2022 (16.x) istnieją stałe role o wąskim zakresie: ##MS_LoginManager## tworzy i zmienia loginy, ale nie nadaje uprawnień, a ##MS_ServerStateReader## daje kontu monitoringu odczyt widoków DMV.

Stałe role bazy działają na całą bazę: db_datareader i db_datawriter obejmują wszystkie tabele, ale nie pozwalają wykonywać procedur, a członkowie db_ddladmin mogą według dokumentacji podnieść sobie uprawnienia. Dokładniejszy model to własne role z uprawnieniami na schemat. Obiekty aplikacji leżą w schemacie app, a rola dostaje prawo do całego schematu (GRANT SELECT ON SCHEMA::app TO crm_read; GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::app TO crm_write), więc nowe tabele i procedury są objęte uprawnieniami bez dodatkowych nadań. Dzięki łańcuchowi własności członek roli z samym EXECUTE uruchamia procedurę zmieniającą tabele, do których nie ma bezpośrednich praw. Członkami ról własnych są użytkownicy grup katalogu (ALTER ROLE crm_read ADD MEMBER …). Własnych ról nie dodaje się jako członków ról stałych, bo dokumentacja wskazuje to jako drogę do niezamierzonego podniesienia uprawnień.

RolaKomu nadawać
sysadmin (serwer)grupa GG-SQL-Admins i konta usług dodane przez instalator
securityadmin (serwer)nikomu spoza sysadmin; od SQL Server 2022 zamiast niej ##MS_LoginManager##
##MS_ServerStateReader## (serwer, od 2022)konto monitoringu
db_owner, db_ddladmin (baza)administratorzy bazy i konto wdrażające zmiany schematu, nie konto aplikacji
db_datareader, db_datawriter (baza)raporty czytające całą bazę; zapis zwykle lepiej oprzeć na roli własnej
własna rola z GRANT … ON SCHEMAaplikacja i grupy użytkowników
db_backupoperator (baza)konto zadań kopii, bez prawa odczytu tabel

Konto aplikacji, tryb mieszany i hasła loginów SQL

Aplikacja z loginem SQL ma hasło w pliku konfiguracyjnym, często jawnym tekstem, a jego zmiana wymaga jednoczesnej zmiany na wszystkich serwerach aplikacji. Lepszy układ to aplikacja na koncie Windows: pula aplikacji IIS albo usługa Windows na koncie gMSA, na przykład CORP\svc-crm-app$. Konto dostaje login i użytkownika jak grupy i członkostwo w roli crm_write, a nie w db_owner. Gdy wszyscy logują się kontami Windows, tryb mieszany (przyjmujący loginy SQL) nie jest potrzebny. Konto sa należy do roli sysadmin i pozostaje wyłączone (ALTER LOGIN sa DISABLE), także po przełączeniu na tryb mieszany.

Gdy login SQL jest nieunikniony, o haśle decydują dwie opcje. CHECK_POLICY (domyślnie ON) stosuje politykę haseł Windows: długość, złożoność i blokadę po nieudanych próbach. CHECK_EXPIRATION (domyślnie OFF) dodaje wygasanie hasła; kombinacja CHECK_POLICY = OFF z CHECK_EXPIRATION = ON nie jest obsługiwana. Dla loginu aplikacji rozsądny zestaw to CHECK_POLICY = ON i CHECK_EXPIRATION = OFF z planową zmianą hasła, bo wygasanie oznacza awarię w dniu utraty ważności. Przy CHECK_POLICY = ON login podlega blokadzie po przekroczeniu progu nieudanych logowań, więc jeden serwer ze starym hasłem po zmianie szybko blokuje login wszystkim pozostałym. Stan pokazuje LOGINPROPERTY(name, 'IsLocked'), a odblokowanie następuje przez ALTER LOGIN crm_app WITH PASSWORD = '…' UNLOCK albo przez wyłączenie i ponowne włączenie CHECK_POLICY.

Użytkownik bez loginu po odtworzeniu bazy

Baza CrmDB odtworzona z kopii na srv-sql-02 zawiera użytkownika crm_app z SID-em loginu z srv-sql-01, a login założony na srv-sql-02 ma nowy SID. To użytkownik osierocony (orphaned user): aplikacja się nie łączy, a próba przypisania loginu do bazy w SSMS kończy się błędem 15023. ALTER USER crm_app WITH LOGIN = crm_app przepisuje w bazie SID użytkownika na SID wskazanego loginu z tej instancji, a role i uprawnienia zostają bez zmian. Gdy kopia trafia na drugi serwer regularnie, login SQL zakłada się tam z tym samym SID-em: odczytuje się go z sys.sql_logins i podaje w CREATE LOGIN … WITH SID = ….

Login grupy Domain Admins ma rolę sysadmin, ale administrator zalogowany na serwerze SQL otwiera SSMS i dostaje błąd logowania (18456, zwykle stan 11) albo nie widzi baz. Przyczyną jest kontrola konta użytkownika (UAC): proces bez podniesienia uprawnień dostaje token filtrowany, w którym grupy uprzywilejowane służą wyłącznie do odmowy. Lepsza od uruchamiania SSMS jako administrator jest dedykowana grupa GG-SQL-Admins, a konta z Domain Admins nie logują się na serwery członkowskie.

Log transakcji i model odzyskiwania

Plik logu CrmDB_log.ldf o rozmiarze 80 GB przy pliku danych 5 GB, brak miejsca na dysku i błąd 9002 (The transaction log for database 'CrmDB' is full due to 'LOG_BACKUP'.), po którym aplikacja przestaje zapisywać zmiany, to zwykle nie skutek awarii, tylko modelu FULL bez kopii logu: SQL Server przechowuje każdą zmianę, dopóki nie trafi ona do kopii logu.

Obcinanie logu

Każda zmiana w bazie trafia najpierw do logu transakcji, a dopiero później do pliku danych. Log to zwykle jeden plik .ldf, dzielony wewnętrznie na wirtualne pliki logu (VLF), które powstają przy tworzeniu pliku i przy każdym jego przyroście. Nowe rekordy są dopisywane na końcu logu. Fragment od najstarszego rekordu, który jest jeszcze potrzebny do odtworzenia bazy po awarii (MinLSN), do ostatniego zapisanego rekordu to aktywna część logu; jej początek wyznacza między innymi najstarsza otwarta transakcja. VLF leżące w całości przed MinLSN są nieaktywne, ale do ponownego zapisu nadają się dopiero po obcięciu logu (log truncation). Gdy zapis dojdzie do końca pliku, wraca na jego początek, do zwolnionych VLF. Jeśli żaden VLF nie jest wolny, SQL Server powiększa plik o wartość FILEGROWTH, a gdy nie może tego zrobić, zgłasza błąd 9002. Obcięcie logu nie zmniejsza pliku: to operacja logiczna, która oznacza nieaktywne VLF jako gotowe do ponownego zapisu. Plik na dysku zmniejsza wyłącznie DBCC SHRINKFILE, a zmniejszenie pliku nie rozwiąże problemu, dopóki coś blokuje obcinanie.

W modelu SIMPLE log jest obcinany automatycznie po punkcie kontrolnym (checkpoint), a kopii logu nie da się wykonać, więc przy kopii pełnej wykonywanej co noc utrata plików bazy o 16:00 oznacza utratę zmian z całego dnia. W modelu FULL log jest obcinany dopiero po kopii logu (BACKUP LOG), o ile od poprzedniej kopii wystąpił punkt kontrolny; kopia pełna bazy nie obcina logu. W zamian ciąg kopii logu pozwala odtworzyć bazę do wybranej chwili, na przykład do minuty przed omyłkowym DELETE bez klauzuli WHERE.

CechaSIMPLEFULL
Możliwa utrata danychzmiany od ostatniej kopii pełnej lub różnicowejzmiany od ostatniej kopii logu
Odtworzenie do punktu w czasienietak, RESTORE LOG … WITH STOPAT
Log shipping, grupy dostępnościnietak
Domyślny w edycjiExpressStandard i Enterprise

W edycjach Standard i Enterprise baza systemowa model ma model FULL i każda nowa baza dziedziczy go stamtąd. W Express model ma domyślnie SIMPLE, więc FULL ustawia się ręcznie. Do pierwszej kopii pełnej baza w FULL zachowuje się jak w SIMPLE. Potem pojawia się nocna kopia pełna, kopii logu nikt nie planuje i żaden VLF nie jest już zwalniany: plik rośnie o każdy dzień pracy, każdą przebudowę indeksów i każdy import, kopie pełne się udają, a dysk zapełnia się w ciągu kilku miesięcy. Rozwiązania są dwa: zaplanować kopie logu albo świadomie zmienić model na SIMPLE.

log_reuse_wait_desc: na co czeka log

Kolumna log_reuse_wait_desc w sys.databases pokazuje, co blokowało ponowne użycie logu w chwili ostatniego punktu kontrolnego; ta sama wartość jest w komunikacie błędu 9002 po słowach full due to.

WartośćPrzyczynaCo zrobić
NOTHINGw logu są wolne VLFnic; zbyt duży plik można jednorazowo zmniejszyć
LOG_BACKUPbaza w FULL lub BULK_LOGGED czeka na kopię logukopia logu i stały harmonogram albo świadome przejście na SIMPLE
ACTIVE_BACKUP_OR_RESTOREtrwa kopia danych albo odtwarzaniepoczekać na koniec operacji
ACTIVE_TRANSACTIONdługa lub porzucona transakcja, w każdym modeluznaleźć ją przez DBCC OPENTRAN, zatwierdzić albo wycofać
REPLICATION, AVAILABILITY_REPLICAreplikacja, CDC albo replika grupy dostępności nie odebrały jeszcze logusprawdzić agenta Log Reader albo kolejki na replikach

W bazie z regularnymi kopiami logu wartość LOG_BACKUP jest normalna; problemem jest brak kopii albo odstępy dłuższe niż w planie. Przejście na SIMPLE pomaga tylko przy LOG_BACKUP: przy ACTIVE_TRANSACTION niczego nie zwolni, a jedynie przerwie łańcuch kopii logu.

Naprawa i rozmiar pliku

Jeśli baza ma zostać w FULL, pierwszym krokiem jest kopia logu (BACKUP LOG [CrmDB] TO DISK = … WITH CHECKSUM; plik .trn staje się częścią łańcucha). Zajmuje ona tyle, ile zajęta część logu, więc dysk docelowy musi pomieścić nawet kilkadziesiąt gigabajtów. Gdy log nigdy nie był kopiowany, potrzebne są dwie kopie logu, aby silnik bazy mógł obciąć log do punktu ostatniej kopii. Jeśli akceptowana jest utrata zmian od ostatniej kopii pełnej, można przejść na SIMPLE (ALTER DATABASE [CrmDB] SET RECOVERY SIMPLE, potem CHECKPOINT). Zmiana przerywa łańcuch kopii logu: odtworzenie do punktu w czasie przestaje być możliwe, a zaplanowane kopie logu tej bazy trzeba wyłączyć.

Gdy log_reuse_wait_desc pokazuje NOTHING, plik można zmniejszyć. Docelowy rozmiar to nie zero, tylko tyle, ile log potrzebuje w najbardziej obciążonym okresie między kopiami logu; dla bazy z przykładu rozsądne jest 4 GB. Plik zmniejsza się do 1024 MB (DBCC SHRINKFILE (N'CrmDB_log', 1024), tylko do granicy VLF), a potem powiększa do docelowego rozmiaru jednym krokiem (ALTER DATABASE [CrmDB] MODIFY FILE (NAME = N'CrmDB_log', SIZE = 4096MB)), co daje rozsądną liczbę VLF. Jeśli DBCC SHRINKFILE nie zejdzie do zadanego rozmiaru, aktywna część logu leży na końcu pliku i po kolejnej kopii logu polecenie trzeba powtórzyć. Zmniejszanie ma być jednorazowe: zadanie nocne z DBCC SHRINKFILE albo AUTO_SHRINK to błędne koło, bo plik i tak urośnie przy następnym obciążeniu, a Microsoft odradza traktowanie zmniejszania plików jako rutynowej konserwacji.

Przyrost procentowy rośnie razem z plikiem: przy 10% plik 80 GB powiększa się jednorazowo o 8 GB. Od SQL Server 2022 z natychmiastowej inicjalizacji plików korzystają tylko przyrosty logu do 64 MB, więc przyrost 8 GB nadal jest zerowany, a zapisy czekają na koniec tej operacji; w starszych wersjach zerowany jest każdy przyrost logu. Rozsądny jest stały przyrost w megabajtach (FILEGROWTH): 64 MB dla małych logów, 256–512 MB dla kilkugigabajtowych i nie więcej niż 1024 MB.

Kopie zapasowe bez SQL Agenta

Brak Agenta nie oznacza braku kopii: BACKUP i RESTORE działają w Express jak w wyższych edycjach, z wyjątkiem kompresji i szyfrowania kopii, a brakujący mechanizm uruchamiania o czasie zastępuje Harmonogram zadań, skrypt PowerShell z sqlcmd i konto gMSA. Plik .bak z Express można odtworzyć na dowolnej edycji tej samej lub nowszej wersji. Kopie logu wykonuje się dla baz w modelach FULL i BULK_LOGGED. Kopie logu odtwarzane po kolei na drugim serwerze zastępują log shipping, którego Express nie ma.

Konto zadań i uprawnienia

Zadania kopii działają na osobnym koncie gMSA CORP\svc-sqlbackup$, a nie na koncie usługi CORP\svc-sql$, więc przejęcie zadania nie daje tożsamości silnika bazy. Konto tworzy się jak konto usługi, z grupą GG-SQL-Hosts jako listą serwerów uprawnionych do pobrania hasła. W SQL Serverze dostaje login (CREATE LOGIN [CORP\svc-sqlbackup$] FROM WINDOWS) i rolę db_backupoperator w każdej bazie, czyli BACKUP DATABASE, BACKUP LOG i CHECKPOINT bez prawa odczytu tabel. Użytkownika w bazach tworzy się jednorazowo pętlą po sys.databases (bez tempdb, tylko bazy ONLINE); ponowne uruchomienie zgłasza błąd 15023 dla baz, w których użytkownik już istnieje, a bazy odtworzone z innego serwera trzeba uzupełnić ręcznie, inaczej pierwsza kopia kończy się błędem uprawnień. Plik kopii zapisuje proces SQL Servera, a nie konto zadania, więc prawo zapisu w katalogu kopii dostaje identyfikator usługi NT SERVICE\MSSQL$CRM, obejmujący tylko tę instancję. Konto zadań dostaje prawo modyfikacji katalogu i zmianę na udziale SQLBackup na serwerze docelowym.

Skrypt kopii

-Type Full wykonuje pełną kopię wszystkich baz poza tempdb, -Type Log obejmuje bazy w FULL i BULK_LOGGED z rozpoczętym łańcuchem kopii (last_log_backup_lsn w sys.database_recovery_status nie jest NULL), bo BACKUP LOG bez pełnej kopii kończy się błędem 4214. Nowa baza dostaje więc pierwszą kopię logu po najbliższej kopii pełnej.

#Requires -Version 5.1
param(
    [Parameter(Mandatory)][ValidateSet('Full', 'Log')][string]$Type,
    [string]$Instance = "$env:COMPUTERNAME,14331",
    [string]$Root = 'D:\SQLBackup\CRM',
    [string]$Share = '\\srv-sql-02\SQLBackup\CRM',
    [int]$RetentionDays = 14
)
$stamp  = Get-Date -Format 'yyyyMMdd_HHmmss'
$log    = Join-Path $Root ('backup_{0}_{1:yyyyMM}.log' -f $Type, (Get-Date))
$failed = 0

# Kopia logu: bazy w FULL i BULK_LOGGED z rozpoczętym łańcuchem; pełna kopia: wszystkie poza tempdb
$filter = "d.recovery_model_desc IN ('FULL', 'BULK_LOGGED') AND r.last_log_backup_lsn IS NOT NULL"
if ($Type -eq 'Full') { $filter = "d.name <> 'tempdb'" }
$query = "SET NOCOUNT ON; SELECT d.name FROM sys.databases AS d " +
         "JOIN sys.database_recovery_status AS r ON r.database_id = d.database_id " +
         "WHERE d.state_desc = 'ONLINE' AND $filter;"
$databases = sqlcmd -S $Instance -E -C -b -h -1 -W -w 1024 -Q $query | Where-Object { $_ }
if ($LASTEXITCODE -ne 0) { exit 1 }

foreach ($db in $databases) {
    $dir = Join-Path $Root $db
    New-Item -ItemType Directory -Path $dir -Force | Out-Null
    if ($Type -eq 'Full') {
        $sql = "BACKUP DATABASE [$db] TO DISK = N'$dir\${db}_${stamp}_FULL.bak' WITH CHECKSUM, INIT;"
    } else {
        $sql = "BACKUP LOG [$db] TO DISK = N'$dir\${db}_${stamp}_LOG.trn' WITH CHECKSUM, INIT;"
    }
    sqlcmd -S $Instance -E -C -b -Q $sql | Out-File -FilePath $log -Append -Encoding utf8
    if ($LASTEXITCODE -ne 0) { $failed++ }
}

# Kopia na udział: przy kopii logu tylko pliki .trn
$mask = if ($Type -eq 'Full') { '*.bak', '*.trn' } else { '*.trn' }
robocopy $Root $Share $mask /S /R:3 /W:10 /NP /NJH /NJS "/LOG+:$log" | Out-Null
if ($LASTEXITCODE -ge 8) { $failed++ }

# Retencja po udanej pełnej kopii: pliki starsze niż N dni i starsze od ostatniej pełnej kopii
if ($Type -eq 'Full' -and $failed -eq 0) {
    $limit = (Get-Date).AddDays(-$RetentionDays)
    foreach ($folder in Get-ChildItem -Path $Root -Directory) {
        $files = Get-ChildItem -Path $folder.FullName -File | Where-Object Extension -in '.bak', '.trn' | Sort-Object Name
        $lastFull = $files | Where-Object Name -like '*_FULL.bak' | Select-Object -Last 1
        if (-not $lastFull) { continue }
        $files | Where-Object { $_.Name -lt $lastFull.Name -and $_.LastWriteTime -lt $limit } | Remove-Item
    }
}
if ($failed -gt 0) { exit 1 }
exit 0

Skrypt łączy się z lokalną instancją przez port, a nie przez nazwę sql-crm, żeby po przełączeniu rekordu A zadanie na starym serwerze nie zaczęło kopiować nowego serwera na własny dysk. Nazwa pliku (CrmDB_20261018_101500_LOG.trn) sprawia, że kolejność alfabetyczna jest kolejnością wykonania, z czego korzystają retencja i odtwarzanie. WITH CHECKSUM zapisuje sumę całej kopii, którą później weryfikuje RESTORE; strona z błędną sumą przerywa kopię. Przełącznik -b daje kod wyjścia 1 przy błędzie o ważności powyżej 10, a -C ufa certyfikatowi serwera bez weryfikacji i zostaje tylko do czasu wdrożenia certyfikatu z wewnętrznego urzędu; potem się go usuwa. Kod 1 skryptu trafia do Harmonogramu zadań i przy braku Database Mail jest głównym sygnałem dla monitoringu.

Kopia lokalna skopiowana zaraz potem daje szybkie odtworzenie z dysku i kopię, która przetrwa awarię serwera; kopia wprost na udział uzależniałaby zapis od jego dostępności. Retencja usuwa pliki starsze niż 14 dni, ale wyłącznie sprzed ostatniej pełnej kopii, więc ta kopia i kopie logu po niej zostają zawsze. Skrypt przycina wyłącznie kopię lokalną w katalogu $Root; kopia na udziale wymaga osobnej retencji po stronie drugiego serwera. Udział na srv-sql-02 i oba serwery są w domenie, więc konto z uprawnieniami Domain Admins dosięga wszystkich kopii naraz. Trzecia kopia plików .bak trafia do repozytorium poza domeną albo do magazynu obiektowego z blokadą obiektów, z osobnymi poświadczeniami.

Harmonogram zadań na koncie gMSA

Konto gMSA nie ma hasła do wpisania w kreatorze, więc zadanie rejestruje się w PowerShellu: New-ScheduledTaskPrincipal -UserId 'CORP\svc-sqlbackup$' -LogonType Password, bez podawania hasła, a następnie Register-ScheduledTask z akcją powershell.exe -NoProfile -NonInteractive -File C:\Scripts\Backup-SqlInstance.ps1 -Type Full. Zadanie CRM - FULL startuje codziennie o 01:00, a zadanie CRM - LOG (-Type Log) o 00:05 z powtarzaniem co 15 minut przez 10 lat (-RepetitionInterval, -RepetitionDuration). Start zadania logu o 00:05 omija 01:00, gdy startuje pełna kopia, ale kopie logu mogą nakładać się na trwającą pełną kopię, bo dokumentacja dopuszcza kopię logu w dowolnym momencie po pierwszej pełnej kopii. Harmonogram zadań nadaje kontu prawo Log on as a batch job automatycznie, chyba że to prawo ustawia GPO domeny; wtedy konto gMSA trzeba dopisać w tym GPO (User Rights Assignment). Bez tego prawa zadanie się rejestruje, ale nie startuje.

Drugi serwer: odtwarzanie kopii logu i przełączenie

Na srv-sql-02 działa instancja CRM w tej samej wersji, na porcie 14331 i na koncie CORP\svc-sql$, więc po przełączeniu nie zmienia się ani ciąg połączenia, ani SPN. Kopii z nowszej wersji nie odtworzy starsza, dlatego obie instancje aktualizuje się razem. Konto zadań dostaje tu ten sam blok uprawnień co na srv-sql-01 oraz rolę dbcreator (RESTORE istniejącej bazy mogą wykonać tylko członkowie sysadmin i dbcreator oraz właściciel bazy); ponieważ dbcreator pozwala nadpisać dowolną bazę, odtwarzanie lepiej uruchamiać na osobnym koncie. Bazę inicjuje się raz pełną kopią przez RESTORE DATABASE … WITH NORECOVERY (z MOVE, gdy ścieżki plików się różnią); zostaje w stanie RESTORING i przyjmuje kolejne kopie logu.

Zadanie odtwarzania działa co 15 minut, pięć minut po kopii na srv-sql-01. Skrypt Restore-SqlLogs.ps1 odczytuje z historii odtworzeń w msdb (restorehistory, backupset, backupmediafamily) nazwę ostatnio odtworzonego pliku i odtwarza nowsze pliki *_LOG.trn w kolejności nazw, każdy z NORECOVERY; bez historii zaczyna od najstarszego pliku. Ciągłość LSN sprawdza sam SQL Server: brakujący plik zatrzymuje odtwarzanie z błędem 4305, a błąd 4326 oznacza kopię, której zmiany baza już zawiera i którą skrypt pomija.

Przy planowanym przełączeniu ostatnią kopię logu na srv-sql-01 wykonuje się z BACKUP LOG … WITH NORECOVERY, CHECKSUM, więc stara baza przechodzi w stan RESTORING i nie przyjmie już zmian. Po odtworzeniu tego pliku na srv-sql-02 (przy awarii od razu na plikach, które dotarły na udział) wykonuje się RESTORE DATABASE [CrmDB] WITH RECOVERY. Następnie rekord A sql-crm dostaje adres srv-sql-02, a jego TTL obniża się z wyprzedzeniem (w przykładzie do 5 minut). Loginy Windows zakłada się na srv-sql-02 wcześniej, a loginy SQL z tym samym SID co na srv-sql-01. Po przełączeniu wyłącza się zadanie odtwarzania, włącza zadania CRM - FULL i CRM - LOG (z -Share wskazującym udział na srv-sql-01) i od razu uruchamia pierwszą pełną kopię na nowym serwerze.

WariantMaksymalna utrata danychCzas przywrócenia
Tylko pełna kopia nocnazmiany z całego dnia, do 24 godzinodtworzenie jednego pliku .bak
Pełna kopia nocna i kopia logu co 15 minutdo 15 minut i czas skopiowania pliku na udziałkopia pełna i wszystkie kopie logu po niej, przy awarii o 16:00 około 60 plików
To samo i odtwarzanie na srv-sql-02jak wyżejkilkanaście minut: ostatnie kopie logu, RECOVERY, zmiana rekordu A

Test odtworzenia

RESTORE VERIFYONLY sprawdza kompletność kopii i sumy kontrolne, ale nie strukturę danych, a odtwarzanie na srv-sql-02 potwierdza tylko ciągłość łańcucha. Pełny test to odtworzenie pod inną nazwą (RESTORE DATABASE [CrmDB_Test] … WITH MOVE …, CHECKSUM, RECOVERY, nazwy logiczne plików z RESTORE FILELISTONLY) i kontrola DBCC CHECKDB bez komunikatów, a potem usunięcie bazy testowej.

Decyzje i alternatywy

PodejścieKiedy ma sensOgraniczenie
Konto wirtualne (NT SERVICE\MSSQL$CRM)pojedynczy serwer bez zasobów sieciowychw sieci występuje jako konto komputera; SPN trzeba rejestrować na koncie komputera
Zwykłe konto domenowebrak gMSA, np. aplikacja niewspierająca kont zarządzanychhasło znane ludziom, ręczna zmiana w każdej usłudze z restartem
gMSA, jedno konto dla instancji o podobnej wrażliwościprosty SPN, jedna delegacja, przenoszenie instancji zmianą rekordu Aprzejęcie jednej instancji daje tożsamość na wszystkich; pełną izolację daje dopiero osobny serwer
gMSA osobne dla instancji z wrażliwymi danymi (HR)dane kadrowe, inni administratorzywięcej kont i SPN do utrzymania
TrustServerCertificate=Truedoraźnie, do wdrożenia certyfikatubrak sprawdzenia tożsamości serwera; nie działa przy Encrypt=Strict
Certyfikat z wewnętrznego urzędu certyfikacji, SAN dla wszystkich nazwdocelowo, każda instancja osobnoklucz w starszym CSP, prawo odczytu dla konta usługi, kontrola ważności
Delegacja ograniczona tylko Kerberosserwery połączone CRM do ERPserwery połączone nie obsługują delegacji opartej na zasobach
Delegacja nieograniczonanigdykonto usługi występuje w imieniu każdego użytkownika wobec dowolnej usługi
Login SQL aplikacji z CHECK_POLICY = ONaplikacja bez wsparcia uwierzytelniania Windowshasło w pliku konfiguracyjnym, blokada przy starym haśle na jednym serwerze
Model SIMPLEzaakceptowana utrata zmian od ostatniej kopii pełnejbrak odtwarzania do punktu w czasie, log shippingu i grup dostępności
Model FULL z kopią logu co 15 minututrata do 15 minut, odtwarzanie do punktu w czasiewymaga harmonogramu kopii logu, miejsca na kopie i monitorowania łańcucha
Express z kopiami z Harmonogramu zadań i odtwarzaniem na drugim serwerzebaza w limitach Express, akceptowalne przełączenie ręcznebrak Agenta, Database Mail, kompresji kopii i automatycznego failoveru
Standardlimit rozmiaru, pula bufora albo wymagane funkcjelicencja; zmiana w miejscu z restartem usług
Enterprisewięcej niż 32 rdzenie lub 256 GB puli bufora, grupy dostępności z kilkoma bazami, operacje onlinenajwyższy koszt licencji

Kolejność prac

  1. Pomiar limitów Express: przydzielony rozmiar plików danych każdej bazy (z sys.master_files, type = 0, bez tempdb) na tle limitu 50 GB lub 10 GB zależnie od wersji, pula bufora i Page life expectancy w godzinach pracy; topologia maszyny wirtualnej to jedno gniazdo z czterema rdzeniami. Decyzja o archiwizacji, aktualizacji wersji lub zmianie edycji zapada przy 80% limitu.
  2. Konta usług: na jakich kontach działają silnik bazy i Agent (nazwa kończąca się znakiem $ oznacza konto zarządzane, a konto domenowe z ręcznym hasłem jest kandydatem do migracji), klucz KDS (Get-KdsRootKey), grupa GG-SQL-Hosts, konto svc-sql i Test-ADServiceAccount na każdym serwerze. Kontrola: wynik True na wszystkich serwerach.
  3. Zmiana konta usługi w Configuration Manager w oknie serwisowym, po przeniesieniu uprawnień do udziałów, loginów i delegacji ze starego konta. Kontrola: usługa startuje, a SPN są zarejestrowane już na nowym koncie.
  4. Sieć instancji: stały port dla każdej instancji, rekord A na instancję, reguła zapory dla UDP 1434 tylko z podsieci aplikacji, typ uruchomienia SQL Browser Automatic (Get-Service SQLBrowser), włączony TCP/IP na instancjach Express i Developer.
  5. Certyfikat z wewnętrznego urzędu certyfikacji dla każdej instancji, z SAN dla wszystkich nazw; kontrola: klienci nowych sterowników łączą się bez TrustServerCertificate.
  6. SPN rejestrowane ręcznie (setspn -S) i delegacja ograniczona dla serwerów połączonych; kontrola: setspn -X bez duplikatów oraz auth_scheme równe KERBEROS dla połączeń z innych maszyn, a nie NTLM.
  7. Dostęp: loginy tylko dla grup katalogu, własne role bazy z uprawnieniami na schemat, aplikacje na kontach Windows. Kontrola członków ról sysadmin i securityadmin (sys.server_role_members): w sysadmin wyłączone sa, identyfikatory NT SERVICE\… i grupa GG-SQL-Admins, w securityadmin brak wierszy; konto osobowe, Domain Admins albo login aplikacji w którejkolwiek z ról wymaga poprawy.
  8. Modele odzyskiwania: ostatnia kopia pełna i ostatnia kopia logu każdej bazy zestawione z recovery_model_desc i log_reuse_wait_desc (msdb.dbo.backupset). Baza w FULL z datą w kopii pełnej i pustym albo zbyt starym ostatnim logiem jest przyczyną błędu 9002; dla baz w SIMPLE brak kopii logu jest prawidłowy.
  9. Kopie: konto svc-sqlbackup$ z rolą db_backupoperator, skrypt i zadania w Harmonogramie zadań, kopia na udział na drugim serwerze, odtwarzanie logu z NORECOVERY. Kontrola: pierwsza kopia pełna, pierwsze kopie logu i odtworzenie pod inną nazwą z DBCC CHECKDB bez komunikatów.
  10. Zmniejszenie przerośniętego logu (jednorazowo, po kopii logu, przy NOTHING) i stały FILEGROWTH w megabajtach.

Ograniczenia i pułapki

  • Test połączenia z konsoli serwera. SSMS na tym samym komputerze łączy się przez pamięć współdzieloną, więc test przechodzi nawet przy wyłączonym TCP/IP i zatrzymanym Browserze. Dostępność instancji sprawdza się z innego komputera, najlepiej z podsieci aplikacji, i z prefiksem tcp:.
  • Kopia logu poza skryptem. Każda kopia logu wykonana poza skryptem przerywa łańcuch na drugim serwerze, który zgłasza błąd 4305: doraźna kopia z SSMS, inny program kopii albo agent kopii maszyn wirtualnych wykonujący kopie logu SQL Servera. Doraźna kopia pełna łańcucha nie przerywa, a doraźna kopia logu wymaga opcji COPY_ONLY.
  • Usunięcie pliku .ldf. Usługa trzyma plik otwarty, więc da się go usunąć dopiero po zatrzymaniu SQL Servera, a po ponownym starcie baza nie przechodzi w stan ONLINE. Log może zawierać zmiany, których nie ma jeszcze w pliku danych, więc zastąpienie go pustym plikiem grozi niespójną bazą; rozmiar zmniejsza się wyłącznie przez DBCC SHRINKFILE.
  • Gniazda wirtualne w Express. Gdy hiperwizor przedstawia każdy procesor wirtualny jako osobne gniazdo, maszyna z czterema procesorami wirtualnymi wygląda dla SQL Servera jak cztery gniazda po jednym rdzeniu, a Express użyje tylko jednego procesora. Rozwiązaniem jest topologia jednego gniazda z czterema rdzeniami, a nie zmiana edycji.
  • Obejścia certyfikatu i delegacji. TrustServerCertificate=True na stałe wyłącza kontrolę tożsamości serwera, a delegacja nieograniczona pozwala kontu usługi występować w imieniu każdego użytkownika. Docelowo stosuje się certyfikat z wewnętrznego urzędu i delegację ograniczoną do SPN instancji ERP.

Źródła