Migrace databáze na hosting SQL Serveru

Nejlepší způsob, jak přesunout databázi SQL Serveru na nový hosting, je nativní záloha a obnova: BACKUP DATABASE ... WITH COPY_ONLY, CHECKSUM na starém serveru, zkopírovat .bak a na novém spustit RESTORE DATABASE ... WITH MOVE. Je to přesné, rychlé a všechno zůstává uvnitř databáze - schéma, data, uživatelé, oprávnění, statistiky. Funguje to jen směrem nahoru, ze stejné nebo starší verze SQL Serveru, a jen když se databáze vejde do cílové edice. Když to nejde - přesun na starší verzi, příchod z Azure SQL Database nebo z MySQL či PostgreSQL - zbývá BACPAC, generované skripty nebo konverze schématu s načtením dat. Tento návod popisuje, jak vybrat metodu, jaké kontroly udělat předem, každou metodu postupně a úklid, který potřebuje každá migrace.

Výběr metody#

Rozhodují hlavně dvě otázky. Je zdroj SQL Server stejné nebo starší verze než cíl? Pak záloha a obnova. Je novější, nebo je to Azure SQL Database, která soubor .bak vůbec vytvořit neumí? Pak BACPAC. Generované skripty jsou pro malé databáze, kde chceš číst nebo upravovat, co se přesouvá, a konverzní nástroje pro odchod z jiného enginu.

Kontroly před jakýmkoli přesunem#

Pět minut dotazů na zdroji ušetří neúspěšnou obnovu o půlnoci.

Verze. Na zdroji spusť SELECT @@VERSION;. Záloha se obnoví jen na stejnou nebo novější hlavní verzi: 2022 je verze 16, 2019 je 15, 2017 je 14, 2016 je 13. SQL Server 2022 obnoví zálohy od SQL Serveru 2008 výše; databáze, která je pořád na 2005 nebo starší, potřebuje mezizastávku přes verzi, která ji přijme.

Velikost. Pokud je cíl Express, každá databáze je omezená na 10 GB datových souborů (log se nepočítá). Zkontroluj, kolik skutečně využíváš, ne alokovanou velikost souboru:

SELECT name, type_desc,       size / 128.0                              AS allocated_mb,       FILEPROPERTY(name, 'SpaceUsed') / 128.0   AS used_mbFROM sys.database_files;

Databáze s 14 GB alokovanými a 6 GB využitými se na Express obnoví jen tehdy, když se datové soubory nejdřív zmenší pod 10 GB, protože obnova vytváří soubory v původní velikosti. Datový soubor zmenši na zdroji (nebo na obnovené kopii) před finální zálohou, poté znovu sestav indexy a počítej s tím, že shrink indexy silně fragmentuje. Pokud opravdu využíváš víc než 10 GB, nejdřív archivuj stará data nebo zvol edici bez limitu - kde ta hranice leží, popisuje hosting SQL Server Express.

Funkce závislé na edici. Některé funkce svazují databázi s edicí. Tento pohled vypíše všechny používané:

SELECT feature_name FROM sys.dm_db_persisted_sku_features;

Od SQL Serveru 2016 Service Pack 1 je většina programovacích funkcí - dělení na oddíly, columnstore, komprese dat, in-memory OLTP - dostupná ve všech edicích, takže u moderního zdroje to obvykle nevrátí nic. Pokud vypíše Transparent Data Encryption, taková databáze se na Express neobnoví; nejdřív ji u zdroje dešifruj.

Závislosti mimo databázi. Sepiš, co žije na starém serveru, a ne v databázi: úlohy SQL Server Agent, linked servery, loginy, profily Database Mail, serverové triggery a každý kód, který odkazuje na jiné databáze třídílným názvem (otherdb.dbo.Table). Nic z toho se zálohou nepřenese. Úlohy Agenta si zaslouží zvláštní pozornost, když je cíl Express, který Agenta nemá - každá úloha se stane naplánovaným voláním sqlcmd někde jinde, jak ukazuje sqlcmd a bcp.

Metoda 1: záloha a obnova#

Na zdroji vytvoř zálohu typu copy-only, abys nenarušil jeho stávající řetězec záloh:

BACKUP DATABASE [shop]TO DISK = N'D:\Backups\shop-migrate.bak'WITH COPY_ONLY, CHECKSUM, INIT, STATS = 10;RESTORE VERIFYONLY FROM DISK = N'D:\Backups\shop-migrate.bak' WITH CHECKSUM;

Zkopíruj soubor na nový server, do adresáře, který proces SQL Serveru přečte. RESTORE vyhodnocuje cestu na serveru, ne na tvé pracovní stanici, takže nahrání souboru je samostatný krok: SFTP, správce souborů hostingu nebo cokoli, co hosting nabízí. Na RE:NODE má každý server SFTP a správce souborů; pokud nevíš, kterou složku engine přečte, zeptej se podpory dřív, než nahraješ velký soubor.

Potom si přečti logické názvy souborů a obnov s každým souborem přesunutým do datového adresáře cíle. Záloha z Windows má v sobě zapsané cesty z Windows a na cíli s Linuxem se musí přesunout všechny:

RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/data/shop-migrate.bak';RESTORE DATABASE [shop]FROM DISK = N'/var/opt/mssql/data/shop-migrate.bak'WITH MOVE N'shop'     TO N'/var/opt/mssql/data/shop.mdf',     MOVE N'shop_log' TO N'/var/opt/mssql/data/shop_log.ldf',     RECOVERY, CHECKSUM, STATS = 10;

SELECT SERVERPROPERTY('InstanceDefaultDataPath'); vrátí správný adresář, pokud ho neznáš. Po obnově můžeš soubor zálohy ze serveru smazat - v tarifu s omezeným diskem je 9GB .bak vedle 9GB databáze nejrychlejší cesta, jak ho zaplnit. Možnosti a chyby obnovy podrobněji popisuje záloha a obnova SQL Serveru.

Hostovaný server může mít databázi už vytvořenou za tebe. Můžeš ji přepsat pomocí REPLACE, nebo obnovit pod původním názvem a nasměrovat věci tam. Pokud je pro tvůj login sa výchozí databází ta předvytvořená a ty obnovuješ pod jiným názvem, změň výchozí databázi, aby nástroje otevíraly tu správnou:

ALTER LOGIN [sa] WITH DEFAULT_DATABASE = [shop];

Metoda 2: BACPAC pomocí SqlPackage#

BACPAC je soubor zip, který obsahuje schéma databáze jako model plus data každé tabulky ve formátu hromadného kopírování. Vzniká tak, že se jako klient připojíš ke zdroji, takže nepotřebuje přístup k souborům ani na jednom serveru, funguje z Azure SQL Database a umí přesunout databázi i na starší verzi, pokud nepoužívá funkce, které starší verze nemá.

$ sqlpackage /Action:Export \    /SourceConnectionString:"Server=old.example.net,1433;Database=shop;User Id=sa;Password=...;TrustServerCertificate=True" \    /TargetFile:shop.bacpac$ sqlpackage /Action:Import \    /SourceFile:shop.bacpac \    /TargetConnectionString:"Server=db.example.net,14330;Database=shop;User Id=sa;Password=...;TrustServerCertificate=True"

Import databázi vytvoří, nebo naplní existující, která je úplně prázdná. V SSMS jsou stejné operace v nabídce Tasks u databáze jako Export Data-tier Application a u uzlu Databases jako Import Data-tier Application.

Kompromisy jsou skutečné:

• Konzistence. Export čte tabulky jednu po druhé bez jediného snímku. Pokud se během exportu zapisuje, BACPAC může obsahovat objednávku bez jejích řádků. Zastav aplikaci, nebo exportuj z obnovené kopie zálohy.
• Rychlost. Je několikrát pomalejší než nativní záloha a obnova a import znovu sestavuje všechny indexy. Pro pár gigabajtů v pohodě, pro desítky zdlouhavé.
• Validace. Export nejdřív validuje schéma a odmítne databáze, které nedokáže vyjádřit - nejčastěji kvůli odkazům na jiné databáze nebo objektům, které se už nekompilují. Chyba objekty jmenuje; oprav je nebo je zahoď a spusť znovu.
• Co se nepřenáší. Statistiky, data Query Store, transakční log a historie záloh se nepřenesou. Loginy také ne, ale contained uživatelé a databázoví uživatelé ano.

SqlPackage se připojuje se stejnými výchozími hodnotami šifrování jako ostatní moderní ovladače Microsoftu, takže server se self-signed certifikátem potřebuje v connection stringu TrustServerCertificate=True, jako výše. Klíčová slova pro šifrování popisuje connection stringy SQL Serveru.

Metoda 3: generované skripty a bcp#

U malé databáze umí SSMS vypsat celé věci jako T-SQL: klikni pravým tlačítkem na databázi, Tasks, Generate Scripts. Vyber objekty, pak v Advanced nastav „Types of data to script“ na „Schema and data“ a „Script for Server Version“ na verzi cíle. Výsledkem je jediný soubor .sql s příkazy CREATE a za nimi INSERTy.

Je čitelný, upravitelný a nezávislý na verzi, takže je to správný nástroj pro databázi o několika stovkách megabajtů, kterou chceš cestou uklidit. Pro cokoli většího je to špatný nástroj: skript s miliony jednořádkových příkazů INSERT běží pomalu a SSMS má potíže ho vůbec otevřít. Velké skripty spouštěj přes sqlcmd -i, ne v okně dotazu.

Pro větší databáze obě metody zkombinuj: vyskriptuj jen schéma, spusť ho na cíli a data přesouvej tabulku po tabulce pomocí bcp v nativním formátu, což je rychlé a přesné. Nejdřív načti nadřazené tabulky, pak podřízené, nebo během načítání vypni cizí klíče a poté je znovu zapni s WITH CHECK, aby byly zase důvěryhodné.

Příchod z MySQL nebo PostgreSQL#

Přesun z jiného enginu je konverze, ne kopie. Pro MySQL, Oracle, Access, Db2 a SAP ASE zkonvertuje schéma bezplatný SQL Server Migration Assistant (SSMA) od Microsoftu, upozorní na to, co zkonvertovat neumí, a zkopíruje data. Pro PostgreSQL SSMA neexistuje; tam schéma převedeš ručně nebo migracemi svého frameworku a data načteš přes CSV a bcp, nebo malým skriptem, který čte z jednoho připojení a hromadně kopíruje do druhého.

Mapování typů je většinou mechanické:

Textové typy zaslouží nejvíc přemýšlení, protože MySQL a PostgreSQL jsou všude v UTF-8, kdežto varchar v SQL Serveru ne, pokud sloupec nepoužívá kolaci UTF-8. Volbu vysvětluje kolace a Unicode v SQL Serveru. Práci vyžaduje i samotné SQL: LIMIT se změní na TOP nebo OFFSET ... FETCH, identifikátory v zpětných apostrofech a dvojitých uvozovkách na hranaté závorky, RETURNING na OUTPUT a upserty na MERGE nebo update-potom-insert. Rozdíly, které bolí, shrnuje základy T-SQL pro vývojáře aplikací. Pokud aplikace používá ORM, bývá rychlejší přepnout jeho provider a schéma vygenerovat z migrací než převádět DDL ručně.

Přepnutí s malým výpadkem#

U malé databáze je nejjednodušší přepnutí to poctivé: dej aplikaci do režimu údržby, vytvoř finální zálohu, obnov, změň connection string a aplikaci vrať zpět. U databáze o 5 GB na slušných linkách je to okno v řádu minut.

Když je to příliš dlouho, použij plnou zálohu plus diferenciální. Plnou zálohu obnov předem bez recovery, aby čekala na další:

-- Days before: restore the full backup, leave it waitingRESTORE DATABASE [shop] FROM DISK = N'/var/opt/mssql/data/shop-full.bak'WITH MOVE N'shop' TO N'/var/opt/mssql/data/shop.mdf',     MOVE N'shop_log' TO N'/var/opt/mssql/data/shop_log.ldf',     NORECOVERY;-- At cutover: stop writes, take a differential on the source, thenRESTORE DATABASE [shop] FROM DISK = N'/var/opt/mssql/data/shop-diff.bak'WITH RECOVERY;

Diferenciální záloha obsahuje jen to, co se změnilo od plné zálohy, takže výpadek je čas na vytvoření, zkopírování a aplikaci této zálohy. Pozor, plná záloha na zdroji pro to nesmí být COPY_ONLY, protože diferenciální záloha vždy vychází z poslední běžné plné zálohy. Pokud je zdroj v modelu obnovy full, mohou mezeru ještě zmenšit zálohy logu. V obou případech si celou sekvenci jednou nacvič na zkušební databázi, než přijde ta pravá noc. Stranu aplikace - změny schématu, se kterými se obě verze kódu srovnají - popisuje migrace bez výpadku.

Po obnově: seznam úklidu#

Každá přesunutá databáze potřebuje před nasměrováním aplikace pár stejných věcí:

1. Oprav osiřelé uživatele. Loginy SQL na novém serveru mají nová SID, takže obnovení uživatelé jim neodpovídají. Vytvoř každý login a pak ALTER USER [app] WITH LOGIN = [app];. Pokud chceš loginy přenést i s hesly a SID, skript sp_help_revlogin od Microsoftu vygeneruje na zdroji příkazy CREATE LOGIN. Model vysvětluje loginy, uživatelé a role v SQL Serveru.
2. Zkontroluj úroveň kompatibility. Obnovená databáze si ponechá úroveň, kterou měla, což může být na serveru 2022 110 nebo 130. Nejdřív na této úrovni otestuj aplikaci a pak ji zvyš: ALTER DATABASE [shop] SET COMPATIBILITY_LEVEL = 160;. S Query Store můžeš porovnat plány před a po a vynutit starý plán u každého dotazu, který se zhorší; jak, ukazuje Query Store v SQL Serveru a pomalé dotazy.
3. Aktualizuj statistiky. EXEC sp_updatestats; v obnovené databázi, aby optimalizátor nepracoval s čísly nasbíranými na jiném hardwaru a s odhadovačem jiné verze.
4. Zkontroluj integritu. Jednou DBCC CHECKDB WITH NO_INFOMSGS;, abys začal se známou dobrou databází.
5. Nastav ověřování stránek. Databáze, které vznikly na velmi starých verzích, mohou pořád používat TORN_PAGE_DETECTION. Moderní nastavení je ALTER DATABASE [shop] SET PAGE_VERIFY CHECKSUM;.
6. Zkontroluj model obnovy a velikost logu, který mohl ze zdroje dorazit nafouknutý.
7. Aktualizuj connection stringy: nový host, port za čárkou, ověřování SQL a nastavení šifrování.

FAQ#

Mohu obnovit zálohu z SQL Serveru 2022 na SQL Serveru 2019?

Ne. Zálohy se obnovují jen na stejnou nebo novější verzi a žádná volba ani trace flag to nezmění. Místo toho exportuj BACPAC, nebo vyskriptuj schéma a data přesuň pomocí bcp, až ověříš, že databáze nepoužívá nic, co 2019 nemá.

Jak migrovat z Azure SQL Database na hostovaný SQL Server?

Exportuj BACPAC z Azure SQL Database, přes portál nebo SqlPackage, a naimportuj ho na cíli. Azure SQL Database neumí vytvořit nativní soubor .bak, takže záloha a obnova z ní není k dispozici.

Moje databáze má 12 GB. Poběží na SQL Server Express?

Tak, jak je, ne. Express omezuje data v každé databázi na 10 GB. Zjisti, kolik je skutečně využito, ne alokováno, archivuj nebo smaž stará data a před zálohou zmenši datový soubor pod limit. Pokud data skutečně přesahují 10 GB, potřebuješ placenou edici.

Musím přesouvat soubor transakčního logu?

Přesouváš ho přes WITH MOVE jako datový soubor, ale jeho obsah moc nezáleží: obnova databázi zotaví a log začne od nuly. Pokud byl log na zdroji obrovský, po obnově ho jednou zmenši a nastav rozumnou velikost, ať nepřenášíš staré nafouknutí.

Přenesou se moje uložené procedury a pohledy?

Ano, při záloze a obnově, BACPAC i generovaných skriptech - jsou součástí databáze. Nepřenese se vše, co je uložené na úrovni serveru: loginy, úlohy Agenta, linked servery a serverové triggery. Sepiš si je předem.