Wednesday 30 December 2020

Powershell - Set Azure SQL Firewall for all Azure SQL Service Tag IP in a Region

 With one of my client we had to create a external data source between 2 Azure DB's but we were not happy to set Allow Azure services option to enable connectivity for 2 Azure DB's and open up Firewall to DB from all Azure resources so we went through the option of setting Allow Azure Services Off and whitelisting SQL ServiceTag IP's for Azure region where our DB's were hosted, Below is the script i created to accomplish the task and WestEurope is the region I used...


Copyright © 2020 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Monday 15 June 2020

Restore Azure SQL Database to different subscription without bacpac

Have you ever faced a scenario doing bacpac for a large database in order to move to another subscription, and waiting endlessly for this process to complete and thought why are't we able to just restore AzSQL Database. Well actually we can and it is well documented in Microsoft too under (Copy a transactionally consistent copy of a database in Azure SQL Database ) the problem is it is not under restore section so on basic google search it becomes bit tricky as the default bacpac option is the one you are going to see.

Summarising the process in the link, you can do this only using SSMS and not through Portal,You have to use a login that has the same name and password as the database owner of the source database on the source server. The login on the target server must also be a member of the dbmanager role, or be the server administrator login. Then execyte the below TSQL(Server name will be just the AZSQL Server name without database.windows.net


-- Execute on the master database of the target server (server2) to start copying from Server1 to Server2
CREATE DATABASE Database2 AS COPY OF server1.Database1;

Hope the article helps!