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
|
|
|
|
|
|