T-SQL Commands to set cross subscription Azure SQL Server DB replication

 Setting replication between Azure SQL databases are very important as it helps in recovering your application during any disaster. Same subscription replication is straight forward as it can be done from Azure Portal itself on the database Replica page. 

In case of you would need to replicate your database to a SQL Server in a different subscription then the best way to do is the T-SQL commands . Below is the step by step to create cross subscription SQL replication.

GEO Replication Configuration 

Follow the below steps to configure GEO replication (make sure the public access is enabled while executing the below steps) 

1) Create a privileged login/user on both primary and secondary to be used for this setup: 

a. Connect to your primary Azure SQL Server and create a login and a user on your master database using the below script: 

--Primary Master Database 

create login GeoReplicationMigUser with password = '<password>' 

 

create user GeoReplicationMigUser for login GeoReplicationMigUser

alter role dbmanager add member GeoReplicationMigUser

Get the created user SID and save it:  

  

select sid from sys.sql_logins where name = 'GeoReplicationMigUser'   

  

 b. On the primary database create the required user as below:   

  

-- primary user database 

create user GeoReplicationMigUser for login GeoReplicationMigUser

alter role db_owner add member GeoReplicationMigUser

 

  

 c. Connect to your secondary server and create the same login and user while using the   same SID you got from point A:   

  

--Secondary Master Database 

create login GeoReplicationMigUser with password = <password>, sid=0x010600000000006400000000000000001C95H41B95D9C78CFRA8578FACE37E5A 

create user GeoReplicationMigUser for login GeoReplicationMigUser; 

alter role dbmanager add member GeoReplicationMigUser

    

  

2) Make sure that both primary and secondary Azure SQL servers firewall rules are configured to allow the connection (such as the IP address of the host running SQL Server Management Studio). 

  

3) Log in with the created user to your primary Azure SQL server to add the secondary server and configure GEO replication, by running the below script on the primary master database: 

  

    

-- Primary Master database 

 

alter database DBName add secondary on server[secondarydb.database.windows.net]

 

4) To verify the setup, access your Azure portal, go to your primary Azure SQL database, and access Replicas blade as below: 

    

  

You will notice that the secondary database has been added and configured. 

  

Note: before moving to the next steps make sure your replica has completed the seeding and is marked as "readable" under replica status (as highlighted below):   

  

 

·       Remove Replication

 

ALTER DATABASE DBName              REMOVE SECONDARY ON server [secondarydb]