SSMS z SQL 2014 - odłączanie bazy w trybie offline

Jeżeli używając SSMS 2014 (a przynajmniej w wersji 12.0.4100.1 instalowanej razem z SQL Server 2014) wykonamy następującą sekwencję czynności:
1. Przełączenie bazy w tryb OFFLINE

2. Odłączenie bazy

To bazy tej nie będzie można ponownie podłączyć. Podczas wskazywania pliku pojawi się błąd:

CREATE FILE encoutered operating system error 5 (Access is denied.) while attempting to open or create the physical file.... Microsoft SQL Server, Error: 5123.

Rozwiązaniem problemu jest uruchomienie Management Studio w trybie administratora.

Pliku bazy odłączonej w SSMS 2014 nie można też otworzyć w nowszych wersjach Management Studio uruchamianych w trybie normalnego użytkownika.
Problem nie istnieje w innych wersjach SSMS (testowane w 2008, 2012 i 2016).
Problem nie istnieje także jeśli odłączana baza nie jest w trybie offline.

Max Server Memory - ustawiona na zbyt małą wartość

Ustawienie w SQL Server opcji "MAX Server Memory" na zbyt małą wartość spowoduje, że SQL Server się nie uruchomi!
W logu Windows zobaczymy wpisy:

Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.  

Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 33086, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Naprawa: Uruchomić SQL Server w minimalnej konfiguracji i przy pomocy narzędzia OSQL ustawić prawidłową wartość MAX Server Memory 1. W oknie poleceń Windows wykonać:

 sqlservr -f -m -S InstanceName  

2. W drugim oknie poleceń wykonać:

 osql -S InstanceName -E -Q "exec sp_configure 'max server memory (MB)',2048 reconfigure with override"  

Uwaga! Do wykonania zmiany należy użyć narzędzia OSQL, a nie SQLCMD! Użycie SQLCMD zakończy się błędem:

 Error: 701, Severity: 17, State: 130. There is insufficient system memory in resource pool 'internal' to run this query.  

TRUSTWORTHY - Niebezpieczna opcja bazy danych

Co może się stać, jeśli baza danych ma ustawioną opcję Trustworthy na ON?
Jeżeli do takiej bazy danych utworzonej przez użytkownika o prawach sysadmina dodamy użytkowników z uprawnieniami db_owner, to mogą oni przejąć kontrolę nad SQL Server.
Jeśli dodatkowo SQL Server działa na użytkowniku o prawach administratora Windows, to mogą także przejąć serwer Windows.

Baza systemowa msdb ma z definicji ustawione Trustworthy na ON :-)

Dokładny opis
http://blogs.technet.com/b/pfe-polska/archive/2012/10/15/czy-trustworthy-warto-zaufa-bezpiecze-stwo-w-sql-server.aspx

Łączenie się do instancji z podaniem numeru portu

Jeżeli chcemy połączyć się do instancji SQL Server działającej na konkretnym porcie ( i znamy ten port), to możemy użyć go w czasie nawiązywania połączenia, podając go po nazwie instancji (oddzielamy przecinkiem).

Sprawdzenie portu, na którym działa usługa

Tematy powiązane:
1. SQL Service Broker
2. Konfiguracja portów SQL Server

Wykorzystanie tabel In-Memory OLTP jako zmiennych tabelarycznych

In-Memory OLTP wprowadzono w SQL Server 2014 Enterprise. W tej wersji miało wiele ograniczeń, ale można tą technologię wykorzystać jako zamiennik zmiennych tabelarycznych i tabel tymczasowych (np. tabele przejściowe w czasie procesów ETL).

Sposób implementacji
1. Utworzenie typu tabelarycznego o strukturze takiej, jak nasza tabela tymczasowa
2. Zadeklarowanie zmiennej jako jako zmiennej utworzonego typu.

 USE HKDB;  
 CREATE TYPE SalesOrderDetailType_inmem  
 AS TABLE  
 (  
 OrderQty smallint NOT NULL,  
 ProductID int NOT NULL,  
 SpecialOfferID int NOT NULL,  
 LocalID int NOT NULL,  
 INDEX IX_ProductID NONCLUSTERED HASH (ProductID) WITH (BUCKET_COUNT = 131072),  
 INDEX IX_SpecialOfferID NONCLUSTERED (SpecialOfferID)  
 )  
 WITH (MEMORY_OPTIMIZED = ON );  
 GO  
 DECLARE @SalesDetail SalesOrderDetailType_inmem;  
 GO  

Wady rozwiązania

• Wymagana jest edycja Enterprise i wersja co najmniej 2014

Zalety:

• zmniejszony udział tempdb - każda baza ma swoją pulę pamięci, nie korzysta z zasobów tempdb
• zadeklarowana zmienna ma indeks!
• możliwy brak utrwalenia danych na dysku, co powoduje ekstremalnie szybkie przetwarzanie danych (wszystko w RAM)
• zoptymalizowane zapisy do logów transakcyjnych
• i wszystkie inne zalety technologii In-Memory OLTP

Przykład zaczerpnięty z książki "SQL Server Internals: In-Memory OLTP, Inside the SQL Server 2014 Hekaton Engine" Kalen Delaney

DAC - Dedicated Administrator Connection

DAC - Dedicated Administrator Connection
Funkcjonalność SQL Server od wersji 2005. Umożliwia nawiązanie sesji awaryjnej z serwerem przez użytkownika z uprawnieniami sysadmin w czasie, gdy serwer nie odpowiada na zwykłe próby nawiązania sesji. Połączenie to jest możliwe dzięki temu, że SQL Server ma specjalnego schedulera dedykowanego tylko do tego połączenia.

 select   
 scheduler_id,   
 cpu_id,   
 status,   
 is_online,   
 is_idle,current_tasks_count,   
 current_workers_count   
 from sys.dm_os_schedulers  

Polecenia wykonywane w czasie sesji DAC mogą wykorzystywać tylko jeden wątek (nie będzie równoległości)

Jak użyć
Z połączenia możemy skorzystać nawiązując tylko jedną sesję. Możemy to zrobić
1. Przez Management Studio - wybierając z menu polecenie File \ New \ Database Engine Query

 

i dopisując w ekranie uwierzytelniania opcję ADMIN: przed nazwą instancji

2. Przez SQLCMD - dodając opcję -A do składni.
Składnia polecenia: SQLCMD -S [SQL Server Name] -U [User Name] -P [Password] -A

Problemy z podłączeniem
1. Nie może być wiele połączeń DAC w tym samym czasie. Jeśli będziemy chcieli nawiązać drugie połączenie, dostaniemy komunikat błędu:

2. Nie możemy połączyć się podczas uruchamiania SSMS - dozwolone są tylko pojedyncze sesje DAC

3. Możemy się połączyć jedynie przy użyciu połączenia RDP - nie można połączyć się zdalnie.

Wykorzystanie połączeń zdalnych wymaga zmiany konfiguracji SQL Servera.

Sprawdzenie bieżących sesji DAC
Korzystając z sys.endpoints oraz sys.dm_exec_endpoints możemy ustalić, czy i skąd nawiązane jest połączenie DAC:

 select   
 dmes.session_id,   
 dmes.login_name,   
 dmes.login_time,   
 e.name as Endpoint_Name,   
 dmes.host_name  
 from sys.dm_exec_sessions dmes  
 join sys.endpoints e  
 on dmes.endpoint_id = e.endpoint_id  

Włączenie możliwości połączeń zdalnych przy użyciu DAC

Sprawdzenie bieżących ustawień:

 Use [master]  
 GO  
 sp_configure 'remote admin connections'  
 GO  

Ustawienie:

 Use [master]  
 GO  
 sp_configure 'remote admin connections', 1  
 GO  
 RECONFIGURE with OVERRIDE  
 GO  

Można to też wykonać przy użyciu interfejsu graficznego w Management Studio:

Na poziomie instancji wyświetlamy "Facets", zmieniamy aspekt na "Surface Area Configuration" i ustawiamy opcję "RemoteDACEnabled" na "true".

Dodatkowo - konieczne jest odblokowanie portu w firewall'u (domyślnie to port 1434)

Dodatkowe informacje
1. Info o schedulerach https://msdn.microsoft.com/en-us/library/ms177526.aspx

Buffer Pool

Buffer Pool
SQL Server zapisuje dane w pliku na tzw. stronach danych (8KB) (data page), pogrupowanych w Extent (po 8 stron, czyli 64 KB). Gdy informacje zapisane w pliku są potrzebne, cała strona danych je zawierająca jest odczytywana i zapisywana w buforach pamięci operacyjnej, które łacznie składają się na Buffer Pool. Pojedynczy bufor także ma wielkość 8KB.

Strona odczytana z dysku to strona "czysta" (clean page). Wszystkie kolejne żądania danych z tej strony danych są obsługiwane przez bezpośredni odczyt z pamięci operacyjnej, z pominięciem kosztownych odczytań z dysku. Gdy strona danych zostanie zmodyfikowana (Insert/update/delete), staje się stroną "brudną" (dirty page). Procesem odczyty czystych stron dancych lub stron indeksu z dysku i zapisaniem do pliku stron czystych zarządza specjalny proces "Buffer Manager"

Jak obejrzeć
Od wersji 2005 SQL Server możemy łatwo odczytać zawartość buforów danych - każda strona w Buffer Pool ma Buffer Descriptor. Widok DMV sys.dm_os_buffer_descriptors zwraca informacje o zawartości bufora, wraz z informacją o tym, z jakiej bazy danych, pliku i strony w pliku te dane pochodzą.

 SELECT * FROM sys.dm_os_buffer_descriptors;  
 GO  

Widok zwraca zarówno informacje o stronach pochodzących z baz systemowych, jak i baz użytkownika. Dzięki informacjom o "pochodzeniu" strony danych wewnętrzny proces checkpoint wie, które gdzie zapisać "brudne" strony w pliku. Informacja  w kolumnie "free_space_in_bytes" informuje, jak dużo miejsca jest "zmarnowane".

Możemy oczywiście pogrupować te wartości

 SELECT (CASE  
       WHEN ( [database_id] = 32767 ) THEN 'Resource Database'  
       ELSE Db_name (database_id)  
      END ) AS 'Database Name',  
     Sum(CASE  
        WHEN ( [is_modified] = 1 ) THEN 0  
        ELSE 1  
       END) AS 'Clean Page Count',  
           Sum(CASE  
        WHEN ( [is_modified] = 1 ) THEN 1  
        ELSE 0  
       END) AS 'Dirty Page Count'  
 FROM  sys.dm_os_buffer_descriptors  
 GROUP BY database_id  
 ORDER BY DB_NAME(database_id);  

Można śledzić wielkość Buffer Pool poprzez PerfMon - liczniki "Buffer Pool"

Czyszczenie 
Polecenie DBCC DROPCLEANBUFFERS powoduje opróżnienie Buffer Pool z czystych stron. Kolejne żądanie danych z tych stron spowoduje ponowne ich odczytanie z dysku i ponowne umieszczenie w Buffer Pool.

Wielkość 
Wielkość Buffer Pool może być kontrolowana pośrednio poprzez ustawienie Max Server Memory. Na wyliczenie wielkości oprócz dostępnej pamięci składa się jednak więcej składników, np. ilość wątków.

Dodatkowe informacje
1. https://technet.microsoft.com/en-us/library/aa337525(v=sql.105).aspx
2. http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-whats-in-the-buffer-pool/