Inovace SQL Server 6.5




Ano, já vím. Existence běžící kopie `SQL Server 6.5`v roce 2008, je absurdní.


Že je stanoveno, co je nejlepší způsob, jak přenést z `6.5`k `2005`? Je nějaká přímá cesta? Většina dokumentace jsem našel se zabývá modernizací `6.5`na `7`.


Měl bych zapomenout nativní `SQL Server`aktualizační utility, scénář se všemi objekty a data, a pokusit se znovu od nuly?


Chtěl jsem, aby se pokusili upgrade tento víkend, ale problémy serveru ji odsunul až příště. Takže nějaké nápady by uvítali v průběhu týdne.


*Aktualizace. To je, jak jsem skončil dělat to:*


* Zálohovat databázi v otázce a Master v `6.5`.
* Spustit `SQL Server 2000`'s `instcat.sql`proti `6.5`' s Master. To umožňuje `SQL Server 2000`‚s OLEDB připojit k `6.5`.
* Použít `SQL Server 2000`je samostatně `Import and Export Data`vytvořit balíček DTS, pomocí `OLEDB`připojení k 6,5. To úspěšně zkopírovány všechny `6.5`‚s tabulky do nové `2005`databáze (také s použitím `OLEDB`).
* Použití `6.5`‚s Enterprise Manager skript si všechny indexy v databázi a spustí do .sql souboru.
* Spustit tento soubor .sql proti novému kopii databáze v Management Studio 2005 je.
* Použít 6.5 je Enterprise Manager skript z všech uložených procedur.
* Spustit tento `.sql`soubor proti `2005`databázi. Několik desítek sprocs měl problémy což je neslučitelné s `2005`. Hlavně `non-ANSI joins`a `quoted identifier issues`.
* Opravil všechny ty záležitosti a re-vykonán na `.sql`soubor.
* Znovu se `6.5`s přihlášení do `2005`a dal jim příslušná oprávnění.


Tam byl trochu máchání / opakování při opravách uložené procedury (tam byly stovky z nich k nápravě), ale upgrade šel skvěle jinak.


Budou moci používat Management Studio místo `Query Analyzer`a `Enterprise Manager 6.5`je tak úžasný rozdíl. Několik zpráva dotazy, které trvalo 20-30 sekund na základě `6.5 database`současné době probíhá během 1-2 sekund, a to bez jakýchkoliv úprav, nové indexy, nebo tak něco. Nečekal jsem, že tento druh okamžité zlepšení.
Hele, já jsem ještě uvízl v tomto táboře taky. Aplikace třetích stran musíme podporovat je konečně chystá 2K5, takže jsme skoro ze dřeva. Ale mám pocit, vaše bolest 8 ^ D


To znamená, že ze všeho, co jsem slyšel od našeho DBA, klíčové je, aby převést databázi do formátu 8.0 první, a pak jít do roku 2005. Věřím, že používá vestavěný migrace / upgrade nástroje pro tento. Existuje několik velkých kroků mezi 6,5 a 8,0, které jsou lépe zde vyřešeny než jít od 6,5 do roku 2005 přímo.


Váš největší bolest, pokud jste nevěděli, už je, že DTS je pryč ve prospěch SSIS. Tam je typ shell modul, který bude provozovat stávající DTS balíčky, ale budete chtít ručně znovu je všechny SSIS. Snadné to bude záviset na složitosti samotných balíčků, ale udělal jsem pár v práci tak daleko a že jsem byl docela hladce.
<p>musíte použít tuto kombinaci kláves:</p><blockquote> <p><kbd>ctrl</kbd> + <kbd>shift</kbd> + <kbd>r</kbd></p> </blockquote><p>případně použijte položku nabídky:</p><blockquote> <p>úpravy&gt; intellisense&gt; aktualizovat místní mezipaměť</p> </blockquote><p>tento článek může být také užitečný (pro budoucí čtenáře, kteří mají komplikovanější problémy s intellisense):</p><ul> <li> <a href="http://www.mssqltips.com/sqlservertip/2591/troubleshooting-intellisense-in-sql-server-management-studio-2012/">odstraňování problémů s intellisense v sql server management studio</a> </li> </ul> <p>před pár minutami jsem to kontroloval: <a href="http://blog.cherouvim.com/a-table-that-should-exist-in-all-projects-with-a-database/">tabulka, která by měla existovat ve všech projektech s databází</a> , se zdá být dost jednoduchá na to, aby se dala do praxe, podívejte se na to:</p><blockquote> <p>říká se tomu schema\_version (nebo migrace nebo cokoli vám vyhovuje) a jeho účelem je sledovat strukturální nebo datové změny v databázi. možná struktura (příklad v mysql) je:</p> <pre><code>create table schema\_version ( `when` timestamp not null default current\_timestamp, `key` varchar(256) not null, `extra` varchar(256), primary key (`key`) ) engine=innodb; </code></pre> <p>vložte do hodnot schema\_version (<code>key</code>, <code>extra</code>) ('001', 'verze schématu');</p> <p>ať už přidáte tuto tabulku od začátku projektu nebo ihned po nasazení první verze na pracovní nebo produkční server, je jen na vás.</p> <p>kdykoli potřebujete provést skript sql, abyste změnili strukturu databáze nebo provedli migraci dat, měli byste do této tabulky přidat také řádek. a udělejte to prostřednictvím příkazu insert na začátku nebo na konci tohoto skriptu (který je zavázán do úložiště kódu projektu).</p> <p>…</p> </blockquote> <p>i když plně souhlasím s tím, že ovládání zdroje je tím správným způsobem, chápu také, že ne všechna prostředí jsou dostatečně disciplinovaná, aby se na to mohla spoléhat sama (pokud vůbec), a že někdy je třeba provést změny přímo, aby aplikace zůstala běh, uložte klienta, co máte.</p><p>spouštěč ddl můžete použít k uchování všech revizí v tabulce v samostatné databázi (a samozřejmě tuto databázi samozřejmě často zálohovat). předpokládejme, že máte databázi nástrojů:</p><pre><code>use utility; go create table dbo.procedurechanges ( eventdate datetime not null default current\_timestamp, eventtype nvarchar(100), eventddl nvarchar(max), databasename nvarchar(255), schemaname nvarchar(255), objectname nvarchar(255), hostname nvarchar(255), ipaddress varchar(32), programname nvarchar(255), loginname nvarchar(255) ); </code></pre><p>nyní ve vaší databázi nejprve pojďme chytit to, čemu říkáme „počáteční kontrola“ - aktuální verze uložených procedur:</p><pre><code>use yourdb; go insert utility.dbo.procedurechanges ( eventtype, eventddl, databasename, schemaname, objectname ) select n'initial control', object\_definition([object\_id]), db\_name(), object\_schema\_name([object\_id]), object\_name([object\_id]) from sys.procedures; </code></pre><p>nyní, abyste zachytili následné změny, přidejte do databáze spouštěč ddl:</p><pre><code>use yourdb; go create trigger capturestoredprocedurechanges on database for create\_procedure, alter\_procedure, drop\_procedure as begin set nocount on; declare @eventdata xml = eventdata(), @ip varchar(32); select @ip = client\_net\_address from sys.dm\_exec\_connections where session\_id = @@spid; insert utility.dbo.procedurechanges ( eventtype, eventddl, schemaname, objectname, databasename, hostname, ipaddress, programname, loginname ) select @eventdata.value('(/event\_instance/eventtype)[1]', 'nvarchar(100)'), @eventdata.value('(/event\_instance/tsqlcommand)[1]', 'nvarchar(max)'), @eventdata.value('(/event\_instance/schemaname)[1]', 'nvarchar(255)'), @eventdata.value('(/event\_instance/objectname)[1]', 'nvarchar(255)'), db\_name(), host\_name(), @ip, program\_name(), suser\_sname(); end go </code></pre><p>postupem času bude snadné vidět a porovnat změny v postupech, sledovat, jak se do systému přidávají nové postupy, vidět, jak vypadají procedury, a mít dobrou představu o tom, s kým o jakékoli z těchto událostí hovořit.</p><p>více informací zde:</p><p> <a href="http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/">http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/</a> </p> <p>na základě marianova návrhu jsem našel <a href="https://dba.stackexchange.com/questions/688/replicating-a-remote-mysql-database-to-ms-sql-server-2008/689#689">tato odpověď</a> o replikaci v opačném směru nastavením mysql serveru jako propojeného serveru v ms sql serveru. když je mysql nastaven jako propojený server, mohu současně spouštět dotazy sql v obou databázích, což poskytuje přesně funkčnost, kterou jsem potřeboval k vyřešení tohoto problému.</p> <p><strong> aktualizace leden 2017 - sql server 2016+/azure sql database </strong></p><p>sql server 2016 a aktuální verze azure sql database má nyní následující syntaxi pro funkce, procedury, tabulky, databáze atd. ( <a href="https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/"><code>drop if exists</code></a> ):</p><pre><code>drop function if exists dbo.fn\_myfunc; </code></pre><p>a sql server 2016 service pack 1 přidává ještě lepší funkčnost pro moduly (funkce, procedury, spouštěče, zobrazení), což znamená, že nedochází ke ztrátě oprávnění nebo závislostí ( <a href="https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/"><code>create or alter</code></a> ):</p><pre><code>create or alter function dbo.fn\_myfunc ... </code></pre><p>obě tato vylepšení syntaxe mohou vést k mnohem jednodušším skriptům používaným pro řízení zdroje, nasazení atd.</p><p>ale pokud používáte ...</p><hr><p><strong> starší verze </strong></p><p>při skriptu z management studio je třeba udělat to, co sql server dělá:</p><pre><code>if not exists (select 1 from sys.objects where type = 'fn' and name = 'fn\_myfunc') begin declare @sql nvarchar(max); set @sql = n'create function ...'; exec sp\_executesql @sql; end </code></pre><p>nebo můžete říct:</p><pre><code>begin try drop function dbo.fn\_myfunc; end try begin catch print 'function did not exist.'; end catch go create function... </code></pre><p>nebo můžete jen říct:</p><pre><code>drop function dbo.fn\_myfunc; go create function... </code></pre><p>(zde se zobrazí chybová zpráva, pokud funkce již neexistuje, ale skript bude pokračovat od příštího go, takže ať už kapka pracovala nebo ne, bude funkce stále vytvořena (znovu).)</p><p>pokud funkci zrušíte a znovu ji vytvoříte, ztratíte také oprávnění a informace o závislostech.</p> <h2>souhrn</h2><p>nechte propojený server dělat co nejvíce.<br> je <em> nemožné </em> pro sql server optimalizovat dotaz na propojeném serveru, dokonce i na jiném serveru sql</p><h2>dlouho</h2><p>klíčovým faktorem je <em> kde </em> je dotaz spuštěn.</p><p>v tomto případě se jedná o triviální výběr, takže všechny řádky z tabulky budou odeslány po drátu. nezáleží na tom.</p><p>když přidáte připojení a kde, pak na tom může záležet. chcete, aby sql server umožnil propojenému serveru provést co největší filtrování, aby se zmenšila velikost dat přicházejících po síti.</p><p>například druhý případ by zde měl být efektivnější.</p><pre><code>select * from openquery(&lt;linked server&gt;, 'select &lt;column list&gt; from mytable') t1 join somelocaltable t2 on ... where t1.foo = 'bar' select * from openquery(&lt;linked server&gt;, 'select &lt;column list&gt; from mytable where foo = ''bar''') join somelocaltable t2 on ... </code></pre><p>omezení openquery je v tom, že nemůžete parametrizovat: proto potřebujete dynamický sql, abyste mohli přidat klauzule where atd.</p><p>výkon propojených serverů může být ovlivněn <a href="http://msdn.microsoft.com/en-us/library/ms178532.aspx"><code>sp\_serveroption</code></a> . nastavení <code>collation compatible</code> říká vše</p><blockquote> <p>pokud je tato možnost nastavena na true, sql server předpokládá, že všechny znaky na propojeném serveru jsou kompatibilní s místním serverem, pokud jde o znakovou sadu a pořadí řazení (nebo pořadí řazení). to umožňuje serveru sql server odesílat porovnání sloupců znaků zprostředkovateli. pokud tato možnost není nastavena, sql server vždy vyhodnocuje porovnání na sloupcích znaků místně.</p> </blockquote><p>to znamená, zkuste nenechat sql server zpracovat data místně.</p><p>poznámka: na mé <code>foo = 'bar'</code> druhý příklad výše je filtr odeslán na propojený server, protože je to pouze řetězcová konstanta na sql server. skutečná klauzule where v prvním příkladu může nebo nemusí být zaslána vzdáleně.</p><p>nakonec jsem také zjistil, že rozložení dat do dočasné tabulky a jejich připojení k místním tabulkám je často lepší než připojení přímo k openquery.</p> <p>nedávno jsem měl něco podobného s tabulkou 3,5 milionu řádků. moje aktualizace by nikdy nedokončila. po mnoha experimentech a frustrace jsem konečně našel viníka. ukázalo se, že se jedná o indexy v aktualizované tabulce.</p><p>řešením bylo zrušit všechny indexy v aktualizované tabulce před spuštěním příkazu aktualizace. jakmile jsem to udělal, aktualizace skončila za pár minut. po dokončení aktualizace jsem znovu vytvořil indexy a vrátil se zpět do práce. pravděpodobně vám to v tuto chvíli nepomůže, ale možná někdo, kdo hledá odpovědi.</p><p>nechal bych indexy na stole, ze kterého stahujete data. ten nebude muset nadále aktualizovat žádné indexy a měl by pomoci s hledáním dat, která chcete aktualizovat. na pomalém notebooku to fungovalo dobře.</p> <p>příkaz <code>merge</code> má složitou syntaxi a ještě složitější implementaci, ale v zásadě jde o spojení dvou tabulek, filtrování dolů na řádky, které je třeba změnit (vložit, aktualizovat nebo smazat), a poté provést požadované změny. vzhledem k těmto vzorovým údajům:</p><pre><code>declare @categoryitem as table ( categoryid integer not null, itemid integer not null, primary key (categoryid, itemid), unique (itemid, categoryid) ); declare @datasource as table ( categoryid integer not null, itemid integer not null primary key (categoryid, itemid) ); insert @categoryitem (categoryid, itemid) values (1, 1), (1, 2), (1, 3), (2, 1), (2, 3), (3, 5), (3, 6), (4, 5); insert @datasource (categoryid, itemid) values (2, 2); </code></pre><p><strong> cíl </strong></p><pre><code>╔════════════╦════════╗ ║ categoryid ║ itemid ║ ╠════════════╬════════╣ ║ 1 ║ 1 ║ ║ 2 ║ 1 ║ ║ 1 ║ 2 ║ ║ 1 ║ 3 ║ ║ 2 ║ 3 ║ ║ 3 ║ 5 ║ ║ 4 ║ 5 ║ ║ 3 ║ 6 ║ ╚════════════╩════════╝ </code></pre><p><strong> zdroj </strong></p><pre><code>╔════════════╦════════╗ ║ categoryid ║ itemid ║ ╠════════════╬════════╣ ║ 2 ║ 2 ║ ╚════════════╩════════╝ </code></pre><p>požadovaným výsledkem je nahrazení dat v cíli daty ze zdroje, ale pouze pro <code>categoryid = 2</code>. po výše uvedeném popisu <code>merge</code> bychom měli napsat dotaz, který spojuje zdroj a cíl pouze na klíče, a filtrovat řádky pouze v klauzulích <code>when</code>:</p><pre><code>merge into @categoryitem as target using @datasource as source on source.itemid = target.itemid and source.categoryid = target.categoryid when not matched by source and target.categoryid = 2 then delete when not matched by target and source.categoryid = 2 then insert (categoryid, itemid) values (categoryid, itemid) output $action, isnull(inserted.categoryid, deleted.categoryid) as categoryid, isnull(inserted.itemid, deleted.itemid) as itemid ; </code></pre><p>to dává následující výsledky:</p><pre><code>╔═════════╦════════════╦════════╗ ║ $action ║ categoryid ║ itemid ║ ╠═════════╬════════════╬════════╣ ║ delete ║ 2 ║ 1 ║ ║ insert ║ 2 ║ 2 ║ ║ delete ║ 2 ║ 3 ║ ╚═════════╩════════════╩════════╝ ╔════════════╦════════╗ ║ categoryid ║ itemid ║ ╠════════════╬════════╣ ║ 1 ║ 1 ║ ║ 1 ║ 2 ║ ║ 1 ║ 3 ║ ║ 2 ║ 2 ║ ║ 3 ║ 5 ║ ║ 3 ║ 6 ║ ║ 4 ║ 5 ║ ╚════════════╩════════╝ </code></pre><p>realizační plán je: <img src="https://i.stack.imgur.com/fovhu.jpg" alt="merge plan"></p><p>všimněte si, že jsou obě tabulky plně naskenovány. můžeme to považovat za neefektivní, protože pouze řádky, kde <code>categoryid = 2</code> bude ovlivněno v cílové tabulce. zde přicházejí varování v books online. jeden chybný pokus o optimalizaci, aby se dotkl pouze nezbytných řádků v cíli, je:</p><pre><code>merge into @categoryitem as target using ( select categoryid, itemid from @datasource as ds where categoryid = 2 ) as source on source.itemid = target.itemid and target.categoryid = 2 when not matched by target then insert (categoryid, itemid) values (categoryid, itemid) when not matched by source then delete output $action, isnull(inserted.categoryid, deleted.categoryid) as categoryid, isnull(inserted.itemid, deleted.itemid) as itemid ; </code></pre><p>logika v klauzuli <code>on</code> je použita jako součást spojení. v tomto případě je spojení úplným vnějším spojením (viz <a href="http://technet.microsoft.com/en-us/library/bb522522%28v=sql.105%29.aspx">tento záznam books online</a> proč). použití kontroly kategorie 2 na cílových řádcích jako součásti vnějšího spojení nakonec povede k odstranění řádků s jinou hodnotou (protože neodpovídají zdroji):</p><pre><code>╔═════════╦════════════╦════════╗ ║ $action ║ categoryid ║ itemid ║ ╠═════════╬════════════╬════════╣ ║ delete ║ 1 ║ 1 ║ ║ delete ║ 1 ║ 2 ║ ║ delete ║ 1 ║ 3 ║ ║ delete ║ 2 ║ 1 ║ ║ insert ║ 2 ║ 2 ║ ║ delete ║ 2 ║ 3 ║ ║ delete ║ 3 ║ 5 ║ ║ delete ║ 3 ║ 6 ║ ║ delete ║ 4 ║ 5 ║ ╚═════════╩════════════╩════════╝ ╔════════════╦════════╗ ║ categoryid ║ itemid ║ ╠════════════╬════════╣ ║ 2 ║ 2 ║ ╚════════════╩════════╝ </code></pre><p>kořenová příčina je ze stejného důvodu, proč se predikáty chovají v klauzuli vnější spojení <code>on</code> odlišně, než když jsou specifikovány v klauzuli <code>where</code>. syntaxe <code>merge</code> (a implementace spojení v závislosti na zadaných klauzulích) jen ztěžují vidět, že tomu tak je.</p><p> <a href="http://technet.microsoft.com/en-us/library/bb510625%28v=sql.105%29.aspx">navádění v books online</a> (rozšířeno v položce <a href="http://technet.microsoft.com/en-us/library/cc879317%28v=sql.105%29.aspx">optimizing performance</a> ) nabízí navádění, které zajistí, že správná sémantika je vyjádřena pomocí syntaxe <code>merge</code>, aniž by uživatel nutně musel muset pochopit všechny podrobnosti implementace nebo zohlednit způsoby, kterými by optimalizátor mohl legitimně přeskupit věci z důvodů efektivnosti provádění.</p><p>dokumentace nabízí tři možné způsoby implementace časného filtrování:</p><p><strong> zadání podmínky filtrování v klauzuli <code>when</code> </strong> zaručuje správné výsledky, ale může znamenat, že více řádků je čteno a zpracováno ze zdroje a cíle tabulky, než je nezbytně nutné (viz první příklad).</p><p><strong> aktualizace prostřednictvím zobrazení </strong>, která obsahuje podmínku filtrování, také zaručuje správné výsledky (protože změněné řádky musí být přístupné pro aktualizaci prostřednictvím zobrazení), ale vyžaduje to vyhrazené zobrazení, které splňuje liché podmínky pro aktualizaci zobrazení.</p><p><strong> použití společného výrazu tabulky </strong> přináší podobné riziko při přidávání predikátů k klauzuli <code>on</code>, ale z poněkud odlišných důvodů. v mnoha případech to bude bezpečné, ale vyžaduje to odbornou analýzu prováděcího plánu, aby se to potvrdilo (a rozsáhlé praktické testování). například:</p><pre><code>with target as ( select * from @categoryitem where categoryid = 2 ) merge into target using ( select categoryid, itemid from @datasource where categoryid = 2 ) as source on source.itemid = target.itemid and source.categoryid = target.categoryid when not matched by target then insert (categoryid, itemid) values (categoryid, itemid) when not matched by source then delete output $action, isnull(inserted.categoryid, deleted.categoryid) as categoryid, isnull(inserted.itemid, deleted.itemid) as itemid ; </code></pre><p>výsledkem jsou správné výsledky (neopakované) s optimálnějším plánem:</p><p><img src="https://i.stack.imgur.com/tazmk.jpg" alt="merge plan 2"></p><p>plán čte pouze řádky pro kategorii 2 z cílové tabulky. to může být důležitým hlediskem výkonu, pokud je cílová tabulka velká, ale je příliš snadné tuto chybu získat pomocí syntaxe <code>merge</code>.</p><p>někdy je snazší zapsat <code>merge</code> jako samostatné operace dml. tento přístup může dokonce <strong> vést lépe </strong> než jediný <code>merge</code>, což je skutečnost, která lidi často překvapuje.</p><pre><code>delete ci from @categoryitem as ci where ci.categoryid = 2 and not exists ( select 1 from @datasource as ds where ds.itemid = ci.itemid and ds.categoryid = ci.categoryid ); insert @categoryitem select ds.categoryid, ds.itemid from @datasource as ds where ds.categoryid = 2; </code></pre> <p>od <a href="http://forums.microsoft.com/technet/showpost.aspx?postid=3825120&amp;siteid=17">podpora společnosti microsoft</a> přes fóra společnosti microsoft:</p><blockquote> <p>další šetření zjistí, že se jedná o očekávané chování aplikace visio 2007. pokud aplikace visio otevře připojení pomocí ovladače serveru visio sql server zkontroluje verzi serveru a protože sql server 2008 dodáno po aplikaci visio 2007 nerozpozná sql server 2008 jako podporovanou verzi a zavře spojení. počkejte na budoucí verzi aplikace visio na loď, která rozpoznává sql server 2008 nebo použijte ovladač visio generic odbc, který může úspěšně otevřít připojení k serveru sql server 2008. třetí možností je použít kopii serveru sql server 2005 pro počáteční reverzní inženýrství. tým visio si je vědom tohoto problému.</p> </blockquote> <p>v ssms musíte změnit některé možnosti:</p><p>nástroje - možnosti - výsledky dotazu - sql server - výsledky do mřížky (nebo textu) -&gt; <strong>při kopírování nebo ukládání výsledků uveďte záhlaví sloupců</strong>.</p><p>změněná nastavení se použijí na nová, ale ne existující okna dotazů.</p>