AlwaysOn Part 8 – Methods to add Database (SCOM)

SQL Server 2012/2014 AlwaysOn Availability Groups:

• Article Summary
• Part 1 – AlwaysOn Introduction
• Part 2 – AlwaysOn Design
• Part 3 – Install and Configure Windows Server 2012 R2 in Core mode
• Part 4 – WSFC Cluster Creation
• Part 5 – Install SQL Core on Windows Core Server
• Part 6 – AlwaysOn Availability Groups Creation
• Part 7 – AlwaysOn Availability Groups Creation (Advanced, with dedicated Replication Network)
• Part 8 – Methods to add Database on Availability Groups (SCOM Example)
• Part 9 – AlwaysOn Availability Groups – PowerShell Monitoring
• ANNEX (Part 6/7) – Manage SQL Endpoint

---

I will not cover the entire SCOM installation, but just the part of Databases configuration.

Note that the Availability Groups (one or two, depending of your architecture) must be created before the SCOM installation. Indeed, during the installation we need to specify the AAG Listener DNS name in place of the classic instance Name.

During the installation, the database is created on the active instance (which hosts the Availability Group Listener) but it’s not added to the AlwaysOn Availability Group, this action must be done at the end of installation. This article covers the methods to add Database to an AAG through SQL Management Studio, T-SQL and PowerShell.

In the previous part (7 – AAG Advanced Configuration), I have created two dedicated AAG for SCOM:

 

Install SCOM

Note: Before installation the AAGs are dispatched (nominal mode):

• *  AAG-3SCOM (with the listener: AAG-3L) is hosted by the AOI2 Instance
  
• *  AAG-4SCOM (with the listener: AAG-4L) is hosted by the AOI4 Instance
  

Start the SCOM installation (full procedure will be written in a dedicated article):

Select components that you want:

First Management server:

And now the part that interest us.

For the “Server name and instance name” we just have to specify the AG Listener DNS Name and the port (check the paths):

Same for the data warehouse database:

Finalize the installation:

Now from SQL, on the AOI2 Instance, we can see the OperationsManager Database (not added to the AG):

And on the AOI4 Instance, we can see the OperationsManagerDW Database:

 

Add SCOM Databases to the Availability Groups

Prepare Databases

Change recovery model to Full and make a Full Backup

With Transact-SQL

-- Set Recovery Model to FULL
USE master ;
ALTER DATABASE OperationsManager SET RECOVERY FULL ;

-- Check Recovery Model
SELECT name, recovery_model_desc
FROM sys.databases WHERE name = 'OperationsManager' ;
GO

-- Make a Full Backup
USE master
GO
BACKUP DATABASE OperationsManager TO DISK = 'G:\MSSQL\MSSQL11.AOI2\MSSQL\Backup\OperationsManager.bak'
GO

Repeat the same operation for the OperationsManagerDW database.

 

Add SCOM Databases to Availability Groups

There are 3 methods to configure a Database in an Availability Group:

• –  From SQL Management Studio
• –  From Transact-SQL
• –  From PowerShell

For demonstration, I will add to the AAGs:

• –  the first SCOM Database (OperationsManager) with Management Studio
• –  the second SCOM Database (OperationsManagerDW) with T-SQL
• –  a test database with PowerShell

 

Add a Database to an Availability Groups through Management Studio

Target: Database “OperationsManager” to the Availability Group “AAG-3SCOM”

Right-click on the Availability Group and select “Add Database”:

Select the SCOM Database:

On the “Select Initial Data Synchronization” enter a shared network location:

There is an issue with Management Studio. For security I have set a static port on all instances (not the default 1433) and I disabled SQL Browser Service. To add a database to an AAG you need to connect all Replicas, but I cannot specify the port and so without the Browser service enabled I cannot connect the secondary instance… (This issue is only present with the use of SQL Management Studio)

So start the SQL Browser Service temporarily (and open the firewall port if needed) and connect to the instance:

Review checks:

Start the operation:

Now first SCOM Database is configured for High Availability on the Availability Group AAG-3COM.

Check the status on the Primary Instance (synchronized under Databases):

On the secondary instance, wait until the status is “Restoring”:

Status OK on the secondary replica:

 

Add a Database to an Availability Groups through T-SQL

Target: Database “OperationsManagerDW” to the Availability Group “AAG-4SCOM”

Prepare the T-SQL script:

Script: AAG-4SCOM-Add-DB-OperationsManagerDW.sql

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect M-SQLA4\AOI4,1764
USE [master]
GO

ALTER AVAILABILITY GROUP [AAG-4SCOM]
ADD DATABASE [OperationsManagerDW];
GO

:Connect M-SQLA4\AOI4,1764
BACKUP DATABASE [OperationsManagerDW] TO DISK = N'\\10.0.1.21\Share\OperationsManagerDW.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO

:Connect M-SQLA2\AOI2,1764
RESTORE DATABASE [OperationsManagerDW] FROM DISK = N'\\10.0.1.21\Share\OperationsManagerDW.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO

:Connect M-SQLA4\AOI4,1764
BACKUP LOG [OperationsManagerDW] TO DISK = N'\\10.0.1.21\Share\OperationsManagerDW_20140505174600.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO

:Connect M-SQLA2\AOI2,1764
RESTORE LOG [OperationsManagerDW] FROM DISK = N'\\10.0.1.21\Share\OperationsManagerDW_20140505174600.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5
GO

:Connect M-SQLA2\AOI2,1764
-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes
if (serverproperty('IsHadrEnabled') = 1)
and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'AAG-4SCOM'
select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
while @conn <> 1 and @count > 0
begin
set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
if @conn = 1
begin
-- exit loop when the replica is connected, or if the query cannot find the replica status
break
end
waitfor delay '00:00:10'
set @count = @count - 1
end
end
end try
begin catch
  -- If the wait loop fails, do not stop execution of the alter database statement
end catch

ALTER DATABASE [OperationsManagerDW] SET HADR AVAILABILITY GROUP = [AAG-4SCOM];
GO

 

From the M-SQLA4 server, start a CMD and execute:

sqlcmd -S M-SQLA4\AOI4,1764 -i c:\tools\AAG-4SCOM-Add-DB-OperationsManagerDW.sql

Check the status of Database, now the both SCOM Databases are OK:

(Note) You can remove all backups in the share folder:

Another prerequisite for a failover of a database on another instance (replica) is that the logins of the application must be configured (with the same permissions: Server Roles/User Mapping) on all instances involved in the Availability group.

To do a failover, the application (here SCOM) logins must be configured on all replicas.

Check SCOM SQL logins on all Instances of the Availability Group. First Replica “M-SQLA2\AOI2”:

• * svc-scomaa
  
• * svc-scomdas
  
• * svc-scomdww
  

Second replica “M-SQLA4\AOI4”:

 

Add a Database to an Availability Groups through PowerShell

Target: Database “AdvWorks” to the Availability Group “AAG-1”

Requirement:

• Check if Database backup mode is set to Full
  

Start PowerShell (elevated privileges):
SCRIPT: SQLAO_Add-database-to-AAG.ps1

1 – Backup Database
Note: If you launch the command remotely, the SQL Browser Service must be started on the target Instance.

Import-Module SQLPS # Backup Database Backup-SqlDatabase -Database “AdvWorks1” -BackupFile “\\10.0.1.21\share\AdvWorks1.bak” -ServerInstance “M-SQLA1\AOI1” Backup-SqlDatabase -Database “AdvWorks1” -BackupFile “\\10.0.1.21\share\AdvWorks1.trn” -ServerInstance “M-SQLA1\AOI1” -BackupAction “Log”

 

The Full DB and log backups are created :

 

2 – Restore Database on the other instance

Note: To execute this command on a remote computer, the SQL Browser service must be activated.

# Restore databases and logs Restore-SqlDatabase -Database “AdvWorks1” -BackupFile “\\10.0.1.21\share\AdvWorks1.bak” -ServerInstance “M-SQLA1\AOI1” -NoRecovery Restore-SqlDatabase -Database “AdvWorks1” -BackupFile “\\10.0.1.21\share\AdvWorks1.trn” -ServerInstance “M-SQLA1\AOI1” ” -RestoreAction “Log” -NoRecovery

Now the Database is in “Restoring” state on the secondary Instance:

 

3 – Join the Database to the AAG on the primary instance

# Join databases to Primary Add-SqlAvailabilityDatabase -Path “SQLSERVER:\SQL\M-SQLA1\AOI1\AvailabilityGroups\AAG-1\” -Database “AdvWorks1”

The database is added on the AAG-1, check the status (must be Synchronized)

 

3 – Join the Database to the AAG on the secondary instance

# Join databases to Secondary Add-SqlAvailabilityDatabase -Path “SQLSERVER:\SQL\M-SQLA3\AOI3\AvailabilityGroups\AAG-1\” -Database “AdvWorks1”

Check status on the secondary node:

Note: You can check the copy Status from PowerShell

# Browse the Active instance (*): # (*) To view information about all of the availability replicas in an availability group, use the server instance that hosts the primary replica. cd SQLSERVER:\SQL\M-SQLA1\AOI1\AvailabilityGroups\AAG-1\DatabaseReplicaStates dir