HAVIT Knowledge Base

Na MSSQL serveru se občas stává, že backupování přestane truncatovat transaction log a ten narůstá a narůstá.

Z nějakého důvodu je potřeba udělat sekvenci shrink-backup-shrink, aby bylo dosaženo požadovaného účinku:

1) Run this code:

2) Run this code if you want to truncate the transaction log and not keep a backup of the transaction log. Truncate_only invalidates your transaction log backup sequence. Take a full backup of your database after you perform backup log with truncate_only:

-or-

Run this code if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact. See SQL Server Books Online topic "BACKUP" for more information:

3) Run this code:

Aktualizace pro SQL 2008

V SQL Serveru 2008 již není TRUNCATE_ONLY přepínač použitelný, místo toho je potřeba přepnout DB do Simple recovery modelu a udělat shrink logu. Pak je možné přepnout zpět na Full.

Občas se Vám může hodit možnost vyskriptovat kompletní databázové schéma do SQL skriptu. Spuštěním takového skriptu pak můžete úplné DB schema zpětně reakonstruovat. Pokud jste si (ne)oblíbili schopnosti databázové edice Visual Studia stejně jako já, pak Vás potěším, že to jde i mnohem jednodušeji pomocí tzv. SQL Publishing Wizzardu přímo ze sady standardních management  nástrojů SQL Serveru 2005:

Vyskriptování schématu DB z GUI

1. Spusťte si SQL Server 2005 Management Studio
2. V Object Exploreru pravým tlačítkem na dotčenou databázi a zvolte Tasks ~ Generate Scripts...
3. Dole zaškrtněte "Script all objects in the selected database" (v horní části okna máte předvolenu dotčenou DB), pokračujte Next,
4. Options - Podle své potřeby můžete upravit nastavení generátoru a ovlivnit podobu výsledného skriptu, např. zvolit jinou cílovou verzi SQL Serveru, pokračujte Next,
5. Script Mode - zvolte, kam chcete skript vygenerovat - obvykle do souboru, pokračujte Next,
6. Review nastavení + Finish

...a je hotovo.

Vyskriptování schématu DB z příkazové řádky

...není nic jednoduššího:

"C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2\sqlpubwiz" script -d DbName -S ServerName -U UserName -P Password TargetScriptFile.sql -schemaonly -f

U nás tímto způsobem průběžně ukládáme ke každé solution aktuální schéma DB, tak, aby bylo v Subversion (source-control) vždy uložena příslušná verze schématu DB, s kterou aplikace pracuje.

Pro úplnost dodávám, že pro synchronizaci databázových schémat a generování rozdílových skriptů používám RedGate SQL Compare.

1. COALESCE() je z ANSI/ISO standardu SQL32, kdežto ISNULL() je jen T-SQL rozšíření (Microsoft SQL Serveru).
2. ISNULL() má vlastní interpretaci, kdežto COALESCE() je pouze zkrácený zápis pro CASE strukturu a i se tak provádí:
   CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END
3. Typ výsledku ISNULL() je vždy dle prvního parametru, typ výsledku COALESCE() odpovídá CASE struktuře, tj. pokouší se o maximální záběr ze všech parametrů (SQL Server Books Online: "Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.").
   DECLARE @Test char(2)
SET @Test = NULL
SELECT ISNULL(@Test, 'abcde'), COALESCE(@Test, 'abcde')Např. výše uvedený test vrací 'ab', 'abcde'.
4. Vzhledem k převodu COALESCE() na CASE oproti vlastnímu provádění je ISNULL() mnohdy rychlejší.
5. ISNULL() pochopitelně bere pouze dva parametry, kdežto COALESCE() víc.
   
   
   

SQL Server 2005 přichází s novou funkčností tzv. Service Brokera - komplexní message-based komunikační platformy, s frontami a dalšími prvky potřebnými pro stavbu robustních SOA aplikací (Service Oriented Architecture).

Podrobněji o Service Brokeru viz SQL Books Online, pro nás je pro tuto chvíli podstatné, že prostřednictvím Service Brokera lze implementovat rozumnou podobu notifikací z SQL serveru do .NET aplikací, kdy jsou .NET aplikace SQL serverem informovány o určitých událostech (např. různých CREATE, DROP, ALTER DDL příkazech), o změnách dat (tzv. query notifications) či jakýchkoliv jiných zprávách, které si sami iniciujeme (např. z trigerů, atp.). Jak dále uvidíme, dotnetovské SqlCacheDependency či SqlDependency nejsou nic jiného, než konkrétním využitím Service Brokera.

Základní principy

Podívejme se nejprve na základní principy práce Service Brokera:

1. Komunikace prostřednictvím Service Brokera je založena na zprávách (MESSAGE). Každá jednotlivá zpráva představuje příslušnou událost, příkaz, notifikaci, prostě atomickou komunikační jednotku.
2. Zprávy jsou uchovávány ve frontách (QUEUE). Fronta je určitým bufferem mezi odesílatelem a příjemcem zprávy.
3. Formální požadavky, které musí zpráva splňovat (např. XML schema), určuje typ zprávy (MESSAGE TYPE).
4. Zprávy se posílají v konverzaci (DIALOG CONVERSATION) mezi dvěma endpointy, které představují služby (SERVICE). Cesty Mezi různými instancemi SQL serveru lze zprávy předávat pomocí routů (ROUTE).
5. Formální požadavky na konverzaci/dialog určuje CONTRACT, který mj. určuje typ zpráv vyměňovaných mezi službami a směr, kterým se posílají.
6. Zpráva může mimo explicitní konverzace vzniknout např. i událostí (EVENT) na straně SQL Serveru (např. DDL události CREATE, ALTER, DROP, nebo trace události, jak je známe z Profileru), nebo prostřednictvím sledování aktualizací dat (Query Notification), kdy SQL Server sleduje výsledky určitého SQL dotazu a oznámí jejich změnu (klasicky využíváno pro expiraci datové cache ASP.NET).
7. Podstatné je, že Service Broker sám neinicializuje komunikaci mimo SQL Server, do .NET aplikace, naopak .NET aplikace si musí zprávu sama vyzvednout z fronty (RECEIVE), resp. může využít konstrukce WAITFOR, čímž v případě prázdné fronty pasivně vyčkává, než se zpráva objeví.

Výše uvedené není ani zdaleka vyčerpávajícím popisem fungování Service Brokeru, pro naše účely však postačuje jako úvod do problematiky.

Pro dále popisovanou funkčnost je potřeba mít Service Brokera na databázi zapnutého:

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

Query Notifications - SqlNotificationRequest, SqlDependency, SqlCacheDependency

Nejběžnějším způsobem využití Service Brokera v .NET aplikaci jsou Query Notification Services - novinka SQL Serveru 2005 spočívají v možnosti vyžádat (subscribe) sledování výsledku určitého SQL dotazu (query). V případě změny pak dojde k vytvoření příslušné zprávy (message), jejímu uložení do fronty (queue), odkud si ji z naší .NET aplikace vyzvedneme a o aktualizaci na straně SQL Serveru se tak dozvíme (a může tak například dojít k vyřazení určitých dat z cache atp.).

Základní principy Query Notifications

1. Query Notifications nelze aktivovat z T-SQL, ani CLR kódu hostovaného v SQL Serveru. Query Notifications lze aktivovat pouze prostřednictvím klientské aplikace, v případě .NET Frameworku prostřednictvím třídy SqlNotificationRequest (vlastnost SqlCommand.Notification).
2. SqlNotificationRequest je svázán s příkazem (SqlCommand), kterému je nastaven a teprve vykonáním tohoto příkazu se provede příslušná subscription QN na straně SQL serveru.
3. SQL Server sleduje pouze první změnu výsledku dotazu, tím QN končí a případný další notification request je potřeba iniciovat novým vykonáním příkazu.
4. V žádném případě se nejedná o komunikaci iniciovanou SQL Serverem, nýbrž si musíme z příslušné fronty sami zprávu o aktualizaci vyzvednout, resp. si obsah fronty průběžně hlídat.
5. V případě SqlDependency a SqlCacheDependency za nás vyzvednutí zprávy z příslušné fronty Service Brokera zajišťuje samotný .NET Framework prostřednictvím opakovaného volání příkazu WAITFOR (RECEIVE ...) TIMEOUT v kontinuálně otevřené samostatné SqlConnection ze samostatného threadu naší aplikace. Přesvědčit se o tom můžete v SQL Profileru.

Ilustrační příklad SqlDependency:

    class Program
    {
        static void Main(string[] args)
        {
            const string connString = "Server=localhost;Database=AdventureWorks;Integrated Security=true;";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("SELECT Bonus FROM Sales.SalesPerson");
                cmd.Connection = conn;

                SqlDependency dependency = new SqlDependency(cmd);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                SqlDependency.Start(connString);

                cmd.ExecuteNonQuery();

                Console.ReadLine();
            }

        }

        static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            Console.WriteLine("Bonus changed...");
        }
    }

SqlNotificationRequest

Pokud nám nestačí vyšší programátoská abstrakce SqlDependency či SqlCacheDependency a potřebujeme řídit Query Notifications podrobněji, můžeme využít třídu SqlNotificationRequest, resp. instanční vlastnost SqlCommand.Notification, kam instanci třídy SqlNotificationRequest přiřadíme a zajistíme tak subscribování daného příkazu do Query Notifications Service. Na rozdíl od SqlDependency pak ale musíme sami zajistit vyzvedávání zpráv z příslušné fronty a jejich zpracování (viz níže).

Požadavky na sledovaný SQL dotaz

Dotaz, ke kterému chceme QueryNotification aktivovat, musí splňovat určité nemalé restrikce:

• jména tabulek musí být uváděna včetně schématu, tedy Sales.SalesPerson, dbo.MojeTabulka, atp.
• nelze použít SELECT *, vždy musíme udělat výčet sloupců
• nelze použít agregační funkce
• nelze používat subqueries, outer-joins, self-joins

Podrobný výčet omezení viz např. http://msdn2.microsoft.com/en-US/library/ms181122.aspx.

Event Notifications

Dalším typem zpráv, které můžeme od SQL Serveru prostřednictvím Service Brokera odebírat, jsou tzv. Event Notifications. V zásadě se jedná o DDL a trace události na straně serveru, ať už na úrovni databáze nebo serveru jako celku. Např. CREATE_DATABASE, ALTER_PROCEDURE, DROP_ASSEMBLY, Audit_Login, SP_Recompile, atp. atp. Události jsou hiearchicky uspořádány a lze se přihlásit i k odběru celé skupiny najednou.

Na rozdíl od Query Notifications, kdy se nám příslušné prvky Service Brokera vytvářely převážně automaticky, zde již musíme provést základní inicializaci ručně. Na rozdíl od Query Notifications se Event Notifications celé řídí prostřednictvím T-SQL příkazů. Jednoduchý příklad by mohl vypadat nějak takto:

CREATE QUEUE MyNotifyQueue;

CREATE SERVICE MyNotifyService
    ON QUEUE MyNotifyQueue
    (
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
    )


CREATE ROUTE MyNotifyRoute WITH SERVICE_NAME = 'MyNotifyService', ADDRESS = 'LOCAL';

CREATE EVENT NOTIFICATION Notify_AlterProcedure
    ON DATABASE
    FOR ALTER_PROCEDURE
    TO SERVICE 'MyNotifyService', 'current database'MESSAGE TYPE pro události je již v systému pod názvem PostEventNotification připraven, vytvoříme tedy jen frontu (CREATE QUEUE), službu (CREATE SERVICE), routu (CREATE ROUTE) a pak už samotnou event-notifikace (CREATE EVENT NOTIFICATION). Tím je na straně SQL Serveru vše připraveno a nyní už můžeme jen vyzvedávat zprávy na straně .NET aplikace:

    class Program
    {
        static bool done = false;

        static void Main(string[] args)
        {
            Thread t = new Thread(ReceiveEvent);
            t.Start();
            
            while (!done)
            {
                Thread.Sleep(1000);
                Console.Write(".");
            }

            Console.ReadLine();
        }

        static void ReceiveEvent()
        {
            const string connString = "Server=localhost;Database=AdventureWorks;Integrated Security=true;";

            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "WAITFOR (RECEIVE TOP(1) * FROM MyNotifyQueue), TIMEOUT @Timeout";
                cmd.Parameters.AddWithValue("@Timeout", 60000); // 60s

                do
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        OnEvent();
                    }
                    done = true;
                }
                while (!done);
            }
        }

        static void OnEvent()
        {
            Console.WriteLine("Event received...");
        }
    }Obdobně jako interní implementace SqlDependency zde v samostatném threadu provádíme průběžný polling WAITFOR(RECEIVE ...) s příslušným timeoutem, zatímco v hlavním vlákně běží aplikace dál (obsluha UI, atp.). Receiving-vláknu bychom také měli nastavit vlastnost IsBackground na true, aby při skončení hlavního vlákna došlo k ukončení tohoto pollingu.

Vlastní zprávy odesíláné z SQL Serveru (např. z triggerů)

Dalším zdrojem zpráv předávaných prostřednictvím Service Brokera mohou být i zprávy, které sami vytvoříme, jejichž odeslání sami inicializujeme - např. z různých triggerů (DML i DDL), nebo uložených procedur.

Pro tuto chvíli přesahuje podrobný popis zamýšlený rozsah tohoto článku, nicméně jak je už z výše uvedeného zřejmé, nejde opět o nic jiného než o inicializaci jednotlivých prvků Service Brokera (vše T-SQL) a dále posílání zpráv prostřednictvím T-SQL příkazů BEGIN DIALOG a SEND. Samotná .NET aplikace by pak byla stejný princip, jak u Event Notifikacions, jen formát zpráv se bude lišit podle toho, jak si ho zadefinujeme (zprávy mohou být i prázdné, nebo plain-text).

Mnohé příklady odesílání zpráv přes Service Brokera viz SQL Books Online. Možná tento článek rozšířím někdy později.

Velmi často se setkávám se zaměňováním "funkcí" @@IDENTITY a SCOPE_IDENTITY(). Obě dvě dvě představují hodnotu identity-sloupce posledního INSERTu, nicméně každý trochu jinak definovanou a v určitých případech se mohou jejich výsledky lišit.

Definice

@@IDENTITY představuje poslední vloženou identity hodnotu v kontextu celé session, veškeré její aktivity.

SCOPE_IDENTITY() představuje poslední vloženou identity hodnotu v kontextu aktuálního scope, v nejužším slova smyslu.

Příklad

Mějme tabulku Table1 s identity-column a k ní insert trigger, který vkládá hodnotu do druhé tabulky Table2 opět s identity-column. A tady je rozdíl, pokud zavoláme dávku

INSERT INTO Table1 DEFAULT VALUES

SELECT @@IDENTITY         -- vrátí identity hodnotu Table2 z triggeru
SELECT SCOPE_IDENTITY()   -- vrátí identity hodnotu Table1

...s použitím @@IDENTITY si můžeme pěkně naběhnout.

IDENT_CURRENT('table')

Funkce IDENT_CURRENT('tablename') vrací poslední hodnotu identity-column tabulky, nezávisle na session či scope.

Při přesunech databází mezi servery, obnovování ze záloh a podobných úkonech se nám může stát, že se ztratí propojení mezi databázovým uživatelem (User) a jeho loginem (SQL Server login). Pomocí běžných management-nástrojů pak nelze toto propojení obnovit.

Propojení obnovíme pomocí stored procedury sp_change_users_login:

...tuto metodu nelze použít pro Windows-loginy, pouze pro SQL Server loginy.

Pro SQL2000 lze použít GUI utilitu Db Maint Sync SQL Logins.

Na SQL serveru do verze 2000 mě štvalo, že nemá žádnou rozumnou práci s poli. Jakmile tedy vyšel SQL2005 s možností použití .NET Frameworku (CLR), napsal jsem si hned vlastní typ IntArray. Nyní, po půl roce jeho používání, mohu říct, že se výborně osvědčil.

Typ IntArray samozřejmě není primárně určen pro použití v tabulkách, kde by sloužil jako typ sloupce, tím bychom degradovali relační schéma. Co nám tedy takové pole prvků typu int usnadní?

• můžeme snadno psát parametrické dotazy s podmínkou WHERE CiselnikID IN [1, 2, 3, ...], typické pro vyhledávání - máme-li například tabulku osob, číselník jejich pracovních pozic a chceme-li vypsat několik pozic najednou - například všechny účetní, personalisty a uklízečky k tomu,
• můžeme snadno "jedním vrzem" (do jedné řádky) načítat objekty i s položkami - pokud používáme v Business Layer klasický přístup s ghost objekty inicializovanými svým ID a s lazyloadem dalších hodnot, pak můžeme krásně načítat nadřazený objekt i s jeho členskými kolekcemi. Například v objednavka.Load() načteme data objednávky a zároveň pole IDček všech řádek objednávky, kterým hned vytvoříme ghost objekty,
• stejně jako načítat, můžeme snadno ukládat vybrané kolekce objektu, například můžeme snadno uložit jedním vrzem uživatele i se seznamem jeho rolí.

SqlInt32Array

První, co tedy potřebujeme, je samotný CLR user-defined type:

Jak jste si jistě všimli, mimo typu IntArray zavádíme hned i UDF funkci IntArrayToTable, kterou bude v T-SQL pole převádět na tabulku.

Náš UDT můžeme do SQL serveru zavést buď přímo z Visual Studia pomocí Deploy, pokud máme jako typ projektu SQL Server Project, nebo ručně pomocí T-SQL:

Příklad vyhledávání:

Příklad využití pole IDček pro ukládání:

Použití UDT z aplikace pak vypadá nějak takto:

SqlInt32ArrayAggregate

Na to, abychom z tabulky se sloupcem int hodnot dostali pole, potřebujeme agregát. Tedy obdobu SUM, AVG, MAX, MIN, prostě něco, co udělá ze všech hodnot jednu.

A příklad použití takového agregátu:

... a jeho využití v aplikaci:

 ...určitě se toho dá mnohé vylepšovat, uvítám samozřejmě jakékoliv komentáře.