MSSQL Migration OnPrem nach Azure SQL per Replikation

Überblick

Wie sehr oft an dieser Stelle, berichten wir hier über unsere Erfahrungen aus Kundenprojekten mit Microsoft Produkten. In diesem Fall von der Online-Migration einer OnPrem SQL Server Datenbank nach Azure SQL mittels Replikation.

Die Frage: Ist das eine gute Idee?

Die Antwort: It depends!

In einem Kundenprojekt unseres Geschäftsführers ist alles bilderbuchmäßig verlaufen:
Wizard für die Replikation starten
Parametrisierung der verschiedenen Wizard-Seiten
Synchronisation des initialen Snapshots und der während dieser Synchronisation aufgelaufenen Transaktionen abwarten
Applikation auf die neue Ziel-DB umschalten
fertig

In einem eigenen Setup gab es hier mehrere Hürden, die zu meistern waren und die möglicherweise irgendwo in den Tiefen der Microsoft Dokumentation beschrieben sind, aber niemals dort gefunden werden, wo man sie vermutet.

Umgebung

Für den hier dargestellten Fall wird als Quelle die Microsoft Testdatenbank AdventureWorks2022 verwendet. Diese kann unter AdventureWorks2022 Download als DB-Sicherungsdatei (.bak) heruntergeladen werden. Nach dem Restore wurde das Full Recovery Modell aktiviert. Dies ist jedoch nicht Voraussetzung für die Replikation, wie auf Welches Wiederherstellungsmodell ist für eine replizierte Datenbank erforderlich? nachzulesen ist.

Die Quellinstanz “MADAFA_DEV” (SQL Server 2022 Developer Edition 2022 (RTM-CU14) (KB5038325) - 16.0.4135.4) befindet sich auf einem Windows 11 Laptop. Als Betriebssystem-Benutzer wird ein Entra ID (früher Azure AD) Account, nennen wir ihn hier “AzureAD\ReplUser”, verwendet. Dieser Azure AD Account hat volle Administrator-Berechtigung auf dem Laptop.

Als Zielumgebung wurde eine Azure SQL Datenbank mit dem Namen AzReplicationDst in einer Azure SQL Server Instanz mit dem Namen azrepldstserver.database.windows.net eingerichtet.

Prüfung vor der Migration, Freigabe für Azure SQL

Azure SQL ist in seiner Funktionalität nicht identisch mit Ihrer OnPrem SQL Server Datenbank. In der Vergleichstabelle findet man “Eine von SQL Server ausgehende Migration kann schwierig sein. Einige SQL Server-Features stehen nicht zur Verfügung.“

In unserem Beispiel verwendet die Quelldatenbank die Volltextsuche mit dem Katalog AW2016FullTextCatalog, der mit dem folgenden Statement in der Quell-Datenbank angelegt und im Vorfeld der Migration mit dem gleichen Statement in die Azure SQL Datenbank übernommen wurde:

CREATE FULLTEXT CATALOG [AW2016FullTextCatalog] WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
GO

Im Vorfeld einer geplanten Migration, nicht nur nach Azure SQL, sollte auf jeden Fall eine Probemigration mit anschließendem Test der Applikation durchgeführt werden. Insbesondere sollte der Applikationshersteller die Freigabe für die Zielumgebung erteilt haben.

Push-Replikation

Azure SQL bietet keine Replikationsfeatures an, so dass die Replikation nur per Push vom Quellserver aus eingerichtet werden kann.

Die Replikations-Wizards

Distributions-Wizard, Konfiguration der Push-Replikation

Im ersten Schritt wird die Distribution konfiguriert. Der Wizard hierzu wird über das Kontextmenü “Replication” -> “Configure Distribution…” gestartet.

Dieser Wizard kann unter Verwendung aller Default-Einstellungen bedient werden (Ausnahme evtl. Schritt 5), d.h.

Distributor ist die aktuelle Instanz
Das vorgegebene Snapshot-Verzeichnis (C:\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\ReplData)kann unverändert übernommen werden (auf ausreichend Platz achten!)
Der Name der neu anzulegenden Systemdatenbank und die Pfade für die Daten- und die TLog-Datei können unverändert übernommen werden. Auch hier muss natürlich ausreichend Platz vorhanden sein. Der Default-Name der Datenbank ist distribution. Wir verwenden hier zur Verdeutlichung den Namen distribution_db.
Auf der vorletzten Seite kann ausgewählt werden, ob man die Distribution konfigurieren und/oder sich die entsprechenden Skripte generieren lassen möchte. Wir verwenden hier beide Optionen um das Skript im nachfolgenden Codeblock wiederzugeben.
Das Skript wird unter dem Namen “ConfigureDistribution.sql” in einem vorgegebenen, aber natürlich auch änderbaren Verzeichnis abgelegt. Hier ist i.d.R. die Option “Overwrite the existing file” der “Append the new script to the existing file” vorzuziehen.

Das generierte Skript ist im folgenden Codeblock - unter Verwendung von umgebungsspezifischen Platzhaltern in <>-Klammern - wiedergegeben:

/****** Scripting replication configuration. Script Date: 20.09.2024 09:21:31 ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 20.09.2024 09:21:31 ******/
use master
exec sp_adddistributor @distributor = N'<laptop-name>\<quellinstanz-name>', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution_db', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1
GO

use [distribution_db] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
	create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties' 
else 
	EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = N'<laptop-name>\<quellinstanz-name>', @distribution_db = N'distribution_db', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

/****** Scripting replication configuration. Script Date: 20.09.2024 09:21:31 ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 20.09.2024 09:21:31 ******/
use master
exec sp_adddistributor @distributor = N'<laptop-name>\<quellinstanz-name>', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution_db', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1
GO

use [distribution_db] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
	create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties' 
else 
	EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = N'<laptop-name>\<quellinstanz-name>', @distribution_db = N'distribution_db', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

Publikations-Wizard

Theoretisch lässt sich die Publikation sehr einfach mit dem Replikations-Wizard einrichten. In der hier dargestellten Umgebung muss jedoch, wie wir sehen werden, ein wenig nachjustiert werden:

Alles beginnt mit dem Kontextmenü “New Publication …“ im Unterknoten “Local Publications” des Knotens “Replication” im SQL Server Management Studio der Quellinstanz.

Auf der ersten Seite des Wizard wird die Quell-DB AdventureWorks2022 ausgewählt.
Auf der zweiten Seite wird von den vier angebotenen Verfahren die transaktionelle Replikation selektiert.
Da die Quelldatenbank im Rahmen der Migration vollständig repliziert werden muss, werden auf Seite drei alle fünf Knoten
- Tabellen
- Stored Procedures
- Views
- Indexed Views
- Benutzerdefinierte Funktionen ausgewählt
Auf der folgenden Seite werden drei Hinweisgruppen zu der Replikation angeboten
- Hinweise zu Tabellen die von Views referenziert werden
- Hinweis, dass Indexed Views nur von der Enterprise Edition unterstützt werden
- Hinweis, dass Objekte, die von Stored Procedures referenziert werden, Bestandteil der Replikation sein müssen

Mainzer Datenfabrik - MSSQL Migration OnPrem nach Azure SQL per Replikation

Die auf der nächsten Wizard-Seite angebotene Möglichkeit der Filterung von Datensätzen wird im Zusammenhang mit der Replikation der gesamten Datenbank nicht benötigt.
Auf Seite sechs wird das Häkchen zum Anlegen des sofortigen Snapshot für die Initialisierung von Subscriptions gesetzt.
Auf Seite sieben wird die Anmeldeinformation für den Snapshot-Agent und die Verbindung zum Publisher eingerichtet. Hier werden der oben im Kapitel Umgebung erwähnte Replikations-User verwendet und der impersonifizierte Prozess Account verwendet.

Auf Seite acht kann die Replikation wahlweise angelegt werden und/oder eine Skript-Datei mit den entsprechenden Befehlen angelegt werden.

Auf der abschließenden Übersicht über die Publikationsparametrisierung verlangt der Wizard die Vergabe eines Namens für die Publikation. Hier verwenden wir AW2022Publication, wobei AW2022 für die Datenbank AdventureWorks2022 steht.

Nach dem Betätigen des Finish-Buttons wird’s unangenehm:

Auf der Fortschrittseite der Konfiguration die in der folgenden Abbildung dargestellt ist, scheitert der zweite der fünf Schritte mit der in der übernächsten Abbildung dargestellten Fehlermeldung. Ergänzung zu der folgenden Abbildung: Wird der Distributor nicht, wie im vorhergehenden Unterkapitel beschrieben, zuvor angelegt, so erfolgt dies implizit über den Replikations-Wizard. Dies resultiert in der ersten Zeile der folgenden Abbildung “Configuring the Distributor”. Wir haben beide Wege, mit und ohne vorherige Distributor-Konfiguration ausprobiert, in beiden Fällen kam es zu der u.a. Fehlermeldung.

Eine Suche nach diesem Fehler auf der Microsoft-Seite führte - erwartungsgemäß - zu keiner Lösung. Es wurde auf der siebenten Wizard-Seite (s.o.) die nicht von Microsoft empfohlene Anmeldungsvariante “Run under the SQL Server Agent service account This is not a recommended security best practice gewählt, das sollte für die einmalige Verwendung der Replikation für die DB-Migration in Ordnung sein. Damit konnte dann der Wizard erfolgreich beendet werden. Auszüge aus dem mitgenerierten Skript (Doubletten für gleichartige Objekttypen wurden weggelassen:

/****** Scripting replication configuration. Script Date: 19.09.2024 14:06:31 ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 19.09.2024 14:06:31 ******/

/* BEGINN: Dieser Codeblock nur, falls man die Distributor-DB nicht bereits zuvor angelegt hat */
use master
exec sp_adddistributor @distributor = N'<laptop-name>\<quellinstanz-name>', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution_db', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1
GO

use [distribution_db] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
	create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties' 
else 
	EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = N'<laptop-name>\<quellinstanz-name>', @distribution_db = N'distribution_db', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
/* ENDE: Dieser Codeblock nur, falls man die Distributor-DB nicht bereits zuvor angelegt hat */

use [AdventureWorks2022]
exec sp_replicationdboption @dbname = N'AdventureWorks2022', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [AdventureWorks2022]
exec sp_addpublication @publication = N'AW2022Publication', @description = N'Transactional publication of database ''AdventureWorks2022'' from Publisher ''<laptop-name>\<quellinstanz-name>''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'AW2022Publication', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

-- Es folgen Aufrufe von sp_addarticle für jedes der zu replizierenden Objekte
-- Beispiel hier für die Tabelle Person.Address (@type = N'logbased')
use [AdventureWorks2022]
exec sp_addarticle @publication = N'AW2022Publication', @article = N'Address', @source_owner = N'Person', @source_object = N'Address', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Address', @destination_owner = N'Person', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_PersonAddress', @del_cmd = N'CALL sp_MSdel_PersonAddress', @upd_cmd = N'SCALL sp_MSupd_PersonAddress'
GO
...
-- Beispiel hier für die Stored Procedure dbo.uspGetBillOfMaterials (@type = N'proc schema only')
use [AdventureWorks2022]
exec sp_addarticle @publication = N'AW2022Publication', @article = N'uspGetBillOfMaterials', @source_owner = N'dbo', @source_object = N'uspGetBillOfMaterials', @type = N'proc schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'uspGetBillOfMaterials', @destination_owner = N'dbo'
GO
...
-- Beispiel hier für die View Person.vAdditionalContactInfo (@type = N'view schema only')
use [AdventureWorks2022]
exec sp_addarticle @publication = N'AW2022Publication', @article = N'vAdditionalContactInfo', @source_owner = N'Person', @source_object = N'vAdditionalContactInfo', @type = N'view schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'vAdditionalContactInfo', @destination_owner = N'Person'
GO
...
-- Beispiel hier für die Indexed View Production.vProductAndDescription (@type = N'indexed view schema only')
use [AdventureWorks2022]
exec sp_addarticle @publication = N'AW2022Publication', @article = N'vProductAndDescription', @source_owner = N'Production', @source_object = N'vProductAndDescription', @type = N'indexed view schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'vProductAndDescription', @destination_owner = N'Production'
GO
...
-- Beispiel hier für die View dbo.ufnGetAccountingEndDate (@type = N'func schema only')
use [AdventureWorks2022]
exec sp_addarticle @publication = N'AW2022Publication', @article = N'ufnGetAccountingEndDate', @source_owner = N'dbo', @source_object = N'ufnGetAccountingEndDate', @type = N'func schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'ufnGetAccountingEndDate', @destination_owner = N'dbo'
GO
...

/****** Scripting replication configuration. Script Date: 19.09.2024 14:06:31 ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 19.09.2024 14:06:31 ******/

/* BEGINN: Dieser Codeblock nur, falls man die Distributor-DB nicht bereits zuvor angelegt hat */
use master
exec sp_adddistributor @distributor = N'<laptop-name>\<quellinstanz-name>', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution_db', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1
GO

use [distribution_db] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
	create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties' 
else 
	EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = N'<laptop-name>\<quellinstanz-name>', @distribution_db = N'distribution_db', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL<quellinstanz-versionsnr.>.<quellinstanz-name>\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
/* ENDE: Dieser Codeblock nur, falls man die Distributor-DB nicht bereits zuvor angelegt hat */

use [AdventureWorks2022]
exec sp_replicationdboption @dbname = N'AdventureWorks2022', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [AdventureWorks2022]
exec sp_addpublication @publication = N'AW2022Publication', @description = N'Transactional publication of database ''AdventureWorks2022'' from Publisher ''<laptop-name>\<quellinstanz-name>''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @publication = N'AW2022Publication', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

-- Es folgen Aufrufe von sp_addarticle für jedes der zu replizierenden Objekte
-- Beispiel hier für die Tabelle Person.Address (@type = N'logbased')
use [AdventureWorks2022]
exec sp_addarticle @publication = N'AW2022Publication', @article = N'Address', @source_owner = N'Person', @source_object = N'Address', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Address', @destination_owner = N'Person', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_PersonAddress', @del_cmd = N'CALL sp_MSdel_PersonAddress', @upd_cmd = N'SCALL sp_MSupd_PersonAddress'
GO
...
-- Beispiel hier für die Stored Procedure dbo.uspGetBillOfMaterials (@type = N'proc schema only')
use [AdventureWorks2022]
exec sp_addarticle @publication = N'AW2022Publication', @article = N'uspGetBillOfMaterials', @source_owner = N'dbo', @source_object = N'uspGetBillOfMaterials', @type = N'proc schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'uspGetBillOfMaterials', @destination_owner = N'dbo'
GO
...
-- Beispiel hier für die View Person.vAdditionalContactInfo (@type = N'view schema only')
use [AdventureWorks2022]
exec sp_addarticle @publication = N'AW2022Publication', @article = N'vAdditionalContactInfo', @source_owner = N'Person', @source_object = N'vAdditionalContactInfo', @type = N'view schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'vAdditionalContactInfo', @destination_owner = N'Person'
GO
...
-- Beispiel hier für die Indexed View Production.vProductAndDescription (@type = N'indexed view schema only')
use [AdventureWorks2022]
exec sp_addarticle @publication = N'AW2022Publication', @article = N'vProductAndDescription', @source_owner = N'Production', @source_object = N'vProductAndDescription', @type = N'indexed view schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'vProductAndDescription', @destination_owner = N'Production'
GO
...
-- Beispiel hier für die View dbo.ufnGetAccountingEndDate (@type = N'func schema only')
use [AdventureWorks2022]
exec sp_addarticle @publication = N'AW2022Publication', @article = N'ufnGetAccountingEndDate', @source_owner = N'dbo', @source_object = N'ufnGetAccountingEndDate', @type = N'func schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'ufnGetAccountingEndDate', @destination_owner = N'dbo'
GO
...

Subscription-Wizard

Abschließend wird die Azure SQL Datenbank als Empfänger der Replikationsinformationen eingerichtet. Wie bereits erwähnt, bietet Azure SQL keine replikationsspezifischen Möglichkeiten, so dass hier eine Push-Replikation von der Quellinstanz aus verwendet werden muss.

Der Subscription-Wizard beginnt mit einer Auswahl der Publikation, in diesem Fall AW2022Publication
Es wird die Default-Einstellung zur Push-Replikation verwendet
Über den Add-Button am untern rechten Rand der Subscribers-Seite müssen die Verbindungsdaten zum Zielserver angegeben werden und hier kommt die nächste Hürde: Wie “normale” SQL Server Instanzen, verwendet Azure SQL auch per Default nur Windows bzw. Entra Authentication, die Subscription benötigt aber SQL Server Authentication. In der Azure SQL Instanz existiert ein generischer SA-User, hier CloudSAb9d8a003, dessen Passwort ist aber nicht bekannt und kann zunächst einmal nicht geändert werden. Hier muss einerseits die ausschließliche EntraID über den Haken (vgl. folgende Abbildung) ausgeschaltet werden und anschließend das Passwort des generischen sa-Users gesetzt werden, so dass man sich hierüber per SQL Authentication anmelden kann (Anmeldung mit dem Management-Studio testen!).

Zum Ändern des Administrations-Passworts navigiert man im Azure Portal zu der jeweiligen Azure SQL Instanz und wählt dort in der oberen Zeile Reset Password.

Anschließend kann dann die Subscriber-Datenbank angegeben werden. Die Instanz heißt hier azrepldstserver.database.windows.net, die Datenbank AzReplicationDst.

Auf der folgenden Seite wurde die Auswahl des Distribution Agenten von Windows bzw. Entra auf den SQL Server Agent Service Account geändert

Die nächste Seite wird mit der Default-Einstellung “Run continuously” unter “Agent Schedule” weitergeschaltet.
Auch die Seite zur Initialisierung der Subscription wird mit den Default-Werten weitergeschaltet.
Zum Abschluss kann, wie bei den anderen beiden Wizards, entschieden werden, ob man die Subscription anlegen und/oder das zugehörige Skript generieren lassen möchte
Wählt man die Skript-Option, so wird nach dem Verzeichnis- und dem Dateinamen gefragt und man kann angeben, ob man an eine evtl. bereits bestehende Datei anfügen oder diese überschreiben möchte.

Auch hier das resultierende Skript:

-----------------BEGIN: Script to be run at Publisher '<laptop-name>\<quellinstanz-name>'-----------------
use [AdventureWorks2022]
exec sp_addsubscription @publication = N'AW2022Publication', @subscriber = N'azrepldstserver.database.windows.net,1433', @destination_db = N'AzReplicationDst', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'AW2022Publication', @subscriber = N'azrepldstserver.database.windows.net,1433', @subscriber_db = N'AzReplicationDst', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20240920, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher '<laptop-name>\<quellinstanz-name>'-----------------

-----------------BEGIN: Script to be run at Publisher '<laptop-name>\<quellinstanz-name>'-----------------
use [AdventureWorks2022]
exec sp_addsubscription @publication = N'AW2022Publication', @subscriber = N'azrepldstserver.database.windows.net,1433', @destination_db = N'AzReplicationDst', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'AW2022Publication', @subscriber = N'azrepldstserver.database.windows.net,1433', @subscriber_db = N'AzReplicationDst', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20240920, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher '<laptop-name>\<quellinstanz-name>'-----------------

Die Replikation

Startet man den Replikationsmonitor über Replication -> Launch Replication Monitor, und wählt auf der linken Seite den Knoten <laptop_name>\<quellinstanz-name>, so zeigt sich folgendes Bild:

Problem sind hier offensichtlich die Jobs, über die die Replikation gesteuert wird. Sieht man sich deren Eigenschaft genauer an, so erkennt man, dass ihr Eigentümer der verwendete Windows Login ist. Best Practice für die Verwendung von Datenbank-Jobs ist, dass man diese dem (gesperrten!) sa User zuordnet.

Mit dem folgenden Skript sehen Sie sich die aktuelle Situation an:

-- Best Practice, auch für die Replikations-Jobs: owner = sa
SELECT s.name AS JobName, l.name AS JobOwner
FROM msdb..sysjobs s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid
WHERE l.name IS NOT NULL
ORDER by l.name

Dies wird durch das folgende Skript geändert, in dem alle der für die Replikation verwendeten Jobs enthalten sein müssen:

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Agent history clean up: distribution_db', @owner_login_name=N'sa'
EXEC msdb.dbo.sp_update_job @job_name=N'Distribution clean up: distribution_db', @owner_login_name=N'sa'
...
EXEC msdb.dbo.sp_update_job @job_name=N'Replication monitoring refresher for distribution_db.', @owner_login_name=N'sa'
		
GO

Anschließend können die benötigten Agenten über die rechte Maustaste im Replikationsmonitor gestartet werden:

Snapshot Agent
Log Reader Agent
Distributor Agent

Nächstes Problem

Der Snapshot und der Log Reader Agent laufen, aber der Distributor Agent beendet seine Arbeit nach kurzer Zeit mit einem Error-Status im Replikationsmonitor.

Das Fenster zu den Details zu diesem Fehler ist in der folgenden Abbildung dargestellt:

Glauben Sie an dieser Stelle nicht, Sie würden über die beiden “Get help” Links zu den Fehlernummern (20084 bzw. 40607) brauchbare oder überhaupt Fehlerinformationen erhalten.

Sieht man sich die Job-Historie des Jobs mit dem Namen <laptop_name>\<instanzname>-AdventureWorks2022-AW2022Publication-AZREPLDSTSERVER.DATAB-3 an, so erfährt man hier u.a. die folgenden Details:

2024-09-20 10:17:41.030 Wird mit Abonnent "azrepldstserver.database.windows.net,1433" verbunden
2024-09-20 10:17:41.217 Agent-Meldungscode: 20084. Der Prozess konnte keine Verbindung mit Subscriber "azrepldstserver.database.windows.net,1433" herstellen.
2024-09-20 10:17:41.227 Category:NULL
Source:  Microsoft OLE DB Driver for SQL Server
Number:  40607
Message: Windows logins are not supported in this version of SQL Server.

Hier geht es also offensichtlich darum, dass sich der Job nicht mit der Azure SQL Datenbank verbinden kann.

Auf der Microsoft Seite zur Parametrisierung des Distribution Agenten erfährt man, dass es neben den Parametern die der vom Wizard generierte Job verwendet, viele weitere Werte gibt. Fügen wir diesem Job die beiden Parameter

-SubscriberLogin
-SubscriberPassword

mit den entsprechenden Werten des generischen Azure SA-Users CloudSAb9d8a003 hinzu, funktioniert auch dieser Job.

Dieser Job durchläuft trotz der o.a. Fehlersituation eine Schleife, so dass er zunächst gestoppt werden muss, bevor er mit der Parametererweiterung neu gestartet wird.

Nach dieser Erweiterung der Parametrisierung läuft der Job und man sieht im Replikationsmonitor jeweils nach einem Refresh den Fortschritt:

Weiteres mögliches Problem

Für den Snapshot den die Replikation initial erstellt, wird ein Verzeichnis benötigt, in dem Skripte abgelegt werden. Der Standard-Pfad zu diesem Verzeichnis ist:

C:\Program Files\Microsoft SQL Server\MSSQL<Versionsnr.>.<Instanzname>\MSSQL\repldata

Dieses Verzeichnis benötigt die Berechtigung für den Dienstuser des Agents und diese musste bei unserem ersten Versuch explizit gesetzt werden. Bei weiteren Versuchen musste hier natürlich nichts mehr geändert werden.

Jetzt noch die Volltextsuche

Nachdem die Replikation jetzt fehlerfrei zu laufen schien und die ersten Tabellen in der Zieltabelle mit Daten gefüllt wurden, trat ein weiteres Problem auf. Diesmal war die Volltextsuche betroffen. Diese hatten wir ja zu Beginn unserer Aktivitäten bereits aktiviert, aber es trat trotzdem die in der folgenden Abbildung dargestellte Fehlermeldung auf, die sich auf die Tabelle [HumanResources].[JobCandidate] bezog.

Die Replikation drehte unermüdlich ihre Schleifen, wies darauf hin, dass einige Replikationsschritte nicht mehr erforderlich seien, weil sie schon abgeschlossen seien und versuchte immer wieder die benötigten Zeilen in die Tabelle JobCandidate einzufügen. Verglich man die Details der Tabelle in Quelle und Ziel miteinander, so fiel tatsächlich auf, dass die Spalte “Resume” im Gegensatz zu der Quellumgebung in der Zielumgebung tatsächlich keinen Volltext besaß. Eine Konfiguration über die Eigenschaften des Volltext-Katalogs im SQL Server Management Studio war nicht möglich, weil SSMS den “Properties”-Knoten für Azure SQL Datenbanken nicht anbietet. Alternativ wurde deshalb der Volltext-Index mit den folgenden Statements eingerichtet:

CREATE FULLTEXT INDEX ON [HumanResources].[JobCandidate] KEY INDEX [PK_JobCandidate_JobCandidateID] 
ON ([AW2016FullTextCatalog]) WITH (CHANGE_TRACKING AUTO)
ALTER FULLTEXT INDEX ON [HumanResources].[JobCandidate] ADD ([Resume] LANGUAGE [English])
ALTER FULLTEXT INDEX ON [HumanResources].[JobCandidate] ENABLE

Nach dieser Änderung konnte die Replikation die Datenübertragung erfolgreich abschließen. Anzumerken ist noch, dass die beiden Tabellen [Production].[Document] und [Production].[ProductReview] ebenfalls Volltext-Spalten besaßen, von diesem Verhalten aber nicht betroffen waren.

Letzte Hinweise

Nachdem die Replikation alle Daten in die Zielumgebung übertragen hat und bevor Sie die Applikation auf die Zieldatenbank umschalten und diese für die Produktion freigeben, vergleichen Sie Anzahl und Typ aller Objekte in der Quell- und Zieldatenbank. Da fehlt doch noch was:

Generieren Sie ein Skript zum Erstellen aller Constraints aller Typen in der Quelldatenbank
Generieren Sie ein Skript zum Erstellen aller Trigger in der Quelldatenbank
Führen Sie die beiden Skripte in der Zieldatenbank aus

Fazit

Grundsätzlich ist Online Migration einer SQL Server Datenbank nach Azure SQL per Replikation über die drei Wizards sehr einfach. Problematisch wird es durch die von den Wizards nicht berücksichtigten Details und möglichen Bugs, wo dann eine Suche nach Ursachen und möglichen Workarounds sehr zeitaufwändig sein kann.

Wenn Sie mehr über dieses Thema erfahren möchten, stehen Ihnen unsere Expert:innen gerne zur Verfügung. Vereinbaren Sie gerne ein unverbindliches Beratungsgespräch über unser Kontaktformular.

Wir helfen Ihnen gerne weiter!