Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



Automate Azure SQL Database (As-A-Service) Copy From a Logical Server to another Logical Server
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-10-04









Solution:



Powershell:

#Prepare Automate Login-AzureRmAccount: (Need to execute this only once & then comment out)
#Login-AzureRmAccount
#Select-AzureRmSubscription -SubscriptionName ""
#$path = "C:\Azure\ProfileContext.ctx"
#Save-AzureRmContext -Path $path -Force


#Automate Login-AzureRmAccount:

$path = ‘C:\Azure\ProfileContext.ctx’
Import-AzureRmContext -Path $path


Function BuildSAAS
{

#Section 1: Define Variables
$SourceServer = "az-srv-nyc-01"
$SourceDB = "Sales"
$PartnerServer = "az-srv-bak-01" #This server must be in the same Azure subscription as the source
$PartnerDB = "Sales"

$resource = Get-AzureRMResource | Where-Object Name -eq "az-srv-bak-01/Sales"

If ($resource.name -eq "az-srv-bak-01/Sales")
{
Write-Host ">>>>> Removing SQL Database..."
Remove-AzureRmSqlDatabase -DatabaseName $PartnerDB -ServerName $PartnerServer -ResourceGroupName "RG-BAK-01"
Start-Sleep -s 60 #Ensure Remove Completes
Write-Host ">>>>> Creating SQL Database..."
$DatabaseCopy = New-azurermsqldatabasecopy -ServerName $SourceServer -DatabaseName $SourceDB -CopyServerName $PartnerServer -CopyDatabaseName $PartnerDB -ResourceGroupName "RG-NYC-01" -CopyResourceGroupName "RG-BAK-01"
}

If([string]::IsNullOrEmpty($resource.name))
{
Write-Host ">>>>> Creating SQL Database..."
Start-Sleep -s 1
$DatabaseCopy = New-azurermsqldatabasecopy -ServerName $SourceServer -DatabaseName $SourceDB -CopyServerName $PartnerServer -CopyDatabaseName $PartnerDB -ResourceGroupName "RG-NYC-01" -CopyResourceGroupName "RG-BAK-01"
}
}

BuildSAAS



Call Powershell from a SQL Job:

Powershell.exe -executionpolicy remotesigned -File C:\Azure\Build-SAAS.ps1



Log the Build from a SQL Job:


Create a Permanent Log table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LOG_SAAS_REBUILD](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NULL,
[DatabaseName] [varchar](25) NULL,
[DatabaseState] [varchar](25) NULL,
[ReplicationStateDesc] [varchar](100) NULL,
[PartnerServer] [varchar](100) NULL,
[PartnerDatabase] [varchar](25) NULL,
[PercentComplete] [int] NULL,
[StartDate] [datetime] NULL,
[ModifyDate] [datetime] NULL,
[IsContinuousCopy] [int] NULL,
[InsertedDate] [datetime] NULL,
CONSTRAINT [PK_LOG_BUILD_SAAS] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


SQL Job:

Declare @db varchar(50) = 'Sales'

If Exists(Select 1 from [LinkedServer].Master.sys.dm_database_copies)
Begin
Insert into dbo.LOG_SAAS_REBUILD ([ServerName],[DatabaseName],[DatabaseState],[ReplicationStateDesc],[PartnerServer],[PartnerDatabase],[PercentComplete],[StartDate],[ModifyDate],[IsContinuousCopy],[InsertedDate])
Select
'a1-srv-bak-01' as 'server', t2.[name] as 'database', t2.state_desc as 'database_state', t1.replication_state_desc, t1.partner_server, t1.partner_database, t1.percent_complete,
t1.[start_Date], t1.modify_date, t1.is_continuous_copy, getdate()
from
[LinkedServer].Master.sys.dm_database_copies t1
inner join [LinkedServer].Master.sys.databases t2 on t1.database_id = t2.database_id
where t2.name = @db
End