AlwaysOn Part 8 – Methods to add Database (SCOM)

SQL Server 2012/2014 AlwaysOn Availability Groups:


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:

AlwaysOn Availability Groups for SCOM Databases

 

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

About Gilles Monville

Gilles Monville is a passionate of IT for many years; he works for over 10 years on Microsoft and VMware environments. In addition to Virtualization and Cloud computing, he loves automation (Orchestrator, PowerShell, Cloud deployment …)

2 comments

  1. Hi, I’m having trouble. When I specify the AG Listener in the “Servername & Instance” during the OpsMger DB setup wizard of SCOM it doesn’t allow me to proceed and throws this error: “The installed version of SQL Server could not be verified or is not supported. Verify that the computer and the installed version of SQL Server meet the minimum requirements for installation, and that the firewall settings are correct. See the Supported Configurations document for further information.” HOWEVER, I’m running Sql Server 2014 Enterprise. As a test I verified that I could log on to the SQL Mgmt Studio as the AG Listener DNS name. If I specify the actual / real server name it allows me to proceed. Any ideas? If I cannot use the AG Listener name here what steps would I have to do on the SQL server or connection? (am not a SQL DB person). Thanks. Darren

  2. Hi, you should set Database Size to 1024MB and try again.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

x

Check Also

SQL AlwaysOn FCI in Azure IaaS Cloud with StarWind Virtual SAN Solution

1 – Introduction This article explains how to deploy an SQL AlwaysOn FCI in Azure ...

AlwaysOn Part 7 – AAG with dedicated Replication Network

SQL Server 2012/2014 AlwaysOn Availability Groups: Article Summary Part 1 – AlwaysOn Introduction Part 2 ...

AlwaysOn Availability Groups Creation

SQL Server 2012/2014 AlwaysOn Availability Groups: Article Summary Part 1 – AlwaysOn Introduction Part 2 ...