About Me

My photo
I am MCSE in Data Management and Analytics with specialization in MS SQL Server and MCP in Azure. I have over 13+ years of experience in IT industry with expertise in data management, Azure Cloud, Data-Canter Migration, Infrastructure Architecture planning and Virtualization and automation. Contact me if you are looking for any sort of guidance in getting your Infrastructure provisioning automated through Terraform. I sometime write for a place to store my own experiences for future search and read by own blog but can hopefully help others along the way. Thanks.

Step by step how to refresh Databases In AlwaysOn if TDE is also enabled





Assume customer comes and say they want to refresh database companytest database from companyprod database.

Prerequsite:-

An existing AlwaysOn Availability Group with at least one Primary[Win2k12-2] and one Secondary replica [Win2k12-3] instance.
A TDE encrypted database on the same instance as the primary replica, online and accessible.
A Database Master Key on all replica servers hosting the availability group (the primary will already have one since it has a TDE encrypted database).

Here:-
Win2k12-2 = Primary replica
Win2k12-3 = Secondary replica
MyTestAg = Availability group Name
\\PDOMAINDC\AlwaysOnBackup = Backup shared folder..



and Task here is to refresh CompanyTest database from CompanyProd Both Databases have TDE already Enabled.


The broad steps which we need to carry out to meet our requirement is as:-
    • Remove a Secondary Database from Availability Group
    • Remove the specified primary database and the corresponding secondary databases from the availability group.
    • Drop database from a secondary replica

    • Take full backup of companyprod from primary replica and restore into compantest database on primary replica with norecovery
    • Take Transaction log backup of companyprod from primary replica and restore into compantest database on primary replica with recovery.
    • On primary replica we specify user database ‘compnaytest’ as we want to add to the availability group.
    • Again, take Fullbackup of companytest from primary replica with copy_only option.
    • Restore companytest to secondary replica in norecovery mode
    • Take T-log backup of companytest from primary replica
    • Restore T-log backup of companytest to secondary replica in norecovery mode
    • Join the secondary database ‘companytest’ to the local secondary replica.

  • Remove a Secondary Database from Availability Group

: connect WIN2k12-3


Use master
Go
ALTER DATABASE [companytest] SET HADR OFF;
GO


  • Remove the specified primary database and the corresponding secondary databases from the availability group.

: connect Win2k12-2


USE [master]
GO
ALTER AVAILABILITY GROUP [MyTestAg] REMOVE DATABASE [companytest];
GO



: connect WIN2k12-3



drop database companytest
GO



: Connect WIN2K12-2



BACKUP DATABASE [companyprod] TO  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companyprod_full.bak' WITH  COPY_ONLY, FORMAT, INIT, COMPRESSION,  STATS = 1
GO



RESTORE DATABASE [companytest] FROM  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companyprod_full.bak' WITH  RECOVERY,replace
, NOUNLOAD,  STATS = 5,
move N'AdventureWorks2014_Data' to N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\companytest.mdf',
move N'AdventureWorks2014_Log' to N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\companytest_log.ldf'
GO




: Connect WIN2K12-2

USE [master]
GO
ALTER AVAILABILITY GROUP [MyTestAg] ADD DATABASE [companytest];
GO



: Connect WIN2K12-2
BACKUP DATABASE [companytest] TO  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companytest_full_Again.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO



: Connect WIN2K12-3
RESTORE DATABASE [companytest] FROM  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companytest_full_Again.bak' WITH  NORECOVERY, NOUNLOAD,  STATS = 5
GO



: Connect WIN2K12-2
BACKUP LOG [companytest] TO  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companytest_Again_1.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO



: Connect WIN2K12-3
RESTORE LOG [companytest] FROM  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companytest_Again_1.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO


-- Wait for the replica to start communicating

: Connect WIN2K12-3

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'MyTestAg'
    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 the execution of the alter database statement
end catch

Join the secondary database ‘companytest’ to the local secondary replica.

ALTER DATABASE [companytest] SET HADR AVAILABILITY GROUP = [MyTestAg];
GO


Thanks for reading..


and
 How to add a TDE encrypted database to an Availability Group

 refer this hyper link..



The computer object associated with the cluster network name resource 'AG-DIGA_AG-DIGAL' could not be updated in domain 'adven.com' during the Resource post on-line operation.

Problem Statement:-

When Creating a New Resource or Role in Windows Server 2012 R2 Failover Cluster, the Network Name Fails to Come Online or Failed to Create Associated Computer Object in Domain

The computer object associated with the cluster network name resource 'AG-AlwaysOnA_AG-AlwaysOnAL' could not be updated in domain 'adven.com'
 during the 
Resource post on-line operation.
The text for the associated error code is: There is no such object on the server.

The cluster identity 'HomeCluster$' may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain.

Product:Windows Operating System
ID:1207
Source:Microsoft-Windows-FailoverClustering
Version:6.0
Symbolic Name:RES_NETNAME_UPDATE_COMPUTER_ACCOUNT_FAILED
Message:Cluster network name resource '%1' cannot be brought online. The computer object associated with the resource could not be updated in domain '%2' for the following reason:
%3.

The text for the associated error code is: %4

The cluster identity '%5' may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain.

So what changed? 

In Windows Server 2012 R2, Microsoft added  a new feature that was high in demand. We now have the ability to specify an OU during Create Cluster Wizard. That way the Cluster Name Object (CNO) can be created in some OU other than the default Computers container.  Additionally, if you move the computer objects for the servers to a different OU, when you create the Cluster, even if you do not specify an OU during creation, the CNO will be created in the same OU where the server computer objects reside instead of the default Computers container. 
what is CNO:-
What is the Cluster Name Object (CNO)? When you create a failover cluster by using the Create Cluster Wizard, you must specify a name for the cluster. If you have sufficient permissions when you create the cluster, the cluster creation process automatically creates a computer object in AD that matches the cluster name. This object is called the cluster name object or CNO. It is also what we use to administer the Cluster as a single unit. These can also be pre-staged

Before you begin, make sure that you know the following:
  • the name that you want to assign the cluster
  • the name of the user account or group to which you want to grant rights to create the cluster
As a best practice, we recommend that you create an OU for the cluster objects. If an OU already exists that you want to use, membership in the Account Operators group is the minimum required to complete this step. If you need to create an OU for the cluster objects, membership in the Domain Admins group, or equivalent, is the minimum required to complete this step.

Note:- If you create the CNO in the default Computers container instead of an OU, you do not have to complete Step 3 of this topic. In this scenario, a cluster administrator can create up to 10 VCOs without any additional configuration.

To prestage the CNO in AD DS

  1. On a computer that has the AD DS Tools installed from the Remote Server Administration Tools, or on a domain controller, open Active Directory Users and Computers. To do this on a server, start Server Manager, and then on the Tools menu, click Active Directory Users and Computers.
  2. To create an OU for the cluster computer objects, right-click the domain name or an existing OU, point to New, and then click Organizational Unit. In the Name box, enter the name of the OU, and then click OK.
  3. In the console tree, right-click the OU where you want to create the CNO, point to New, and then click Computer.
  4. In the Computer name box, enter the name that will be used for the failover cluster, and then click OK.
    System_CAPS_ICON_note.jpg Note
    This is the cluster name that the user who creates the cluster will specify on the Access Point for Administering the Clusterpage in the Create Cluster wizard or as the value of the –Name parameter for the New-Cluster Windows PowerShell cmdlet.
  5. As a best practice, right-click the computer account that you just created, click Properties, and then click the Object tab. On the Object tab, select the Protect object from accidental deletion check box, and then click OK.
  6. Right-click the computer account that you just created, and then click Disable Account. Click Yes to confirm, and then click OK.
Note:- You must disable the account so that during cluster creation, the cluster creation process can confirm that the account is not currently in use by an existing computer or cluster in the domain.

                                           Disabled CNO in the example Clusters OU

You must configure permissions so that the user account that will be used to create the failover cluster has Full Control permissions to the CNO.
Membership in the Account Operators group is the minimum required to complete this step.

To grant the user permissions to create the cluster

  1. In Active Directory Users and Computers, on the View menu, make sure that Advanced Features is selected.
  2. Locate and then right-click the CNO, and then click Properties.
  3. On the Security tab, click Add.
  4. In the Select Users, Computers, or Groups dialog box, specify the user account or group that you want to grant permissions to, and then click OK.
  5. Select the user account or group that you just added, and then next to Full control, select the Allow check box.
    Granting user Full Control permissions to CNO
    Figure 2. Granting Full Control to the user or group that will create the cluster
  6. Click OK.
After you complete this step, the user who you granted permissions to can create the failover cluster. However, if the CNO is located in an OU, the user cannot create clustered roles that require a client access point until you complete Step 3.
System_CAPS_ICON_note.jpg Note
If the CNO is in the default Computers container, a cluster administrator can create up to 10 VCOs without any additional configuration. To add more than 10 VCOs, you must explicitly grant the Create Computer objects permission to the CNO for the Computers container.
Granting Full Control to the user or group that will create the cluster

What is VCO:-
o what’s the Virtual Computer Object (VCO)? The VCO is also a computer object that is automatically created when you configure any Cluster roles that have an associated client access point. A client access point is nothing more than an IP address and network name. Again, the network name is associated with a computer object in AD. For example, if you setup a file server and call the role FileServer1, you will have a computer object in AD directory called FileServer1. The CNO is responsible for creating these. When the CNO is automatically created, we assign it the appropriate permissions in order to be able to do so. If we’re prestaging or manually creating these, then we rely on the AD admin to follow the TechNet documentation and assign the appropriate permissions.

So what’s the problem?

By default whichever OU you move the CNO to does not have the appropriate permissions for the CNO to create the VCO and you’ll see the errors described at the beginning of this blog or you’ll simply fail to see the resource come online. On closer investigation, you’ll see the network name failed as shown below:
If you go look in Active Directory, you’ll notice that the computer object or VCO was never created for my MSDTC resource pictured above. There should be a VCO by the name of SQL1MSDTC, but no such object exists in AD. It was never created because the CNO did not have the appropriate permissions to create it.
But wait, I thought you said the CNO was automatically granted the permissions to create the VCOs?
Correct, that is the case. On the CNO itself, we give it the right permissions automatically. However, we change nothing at the OU level. In order for the CNO to create the VCOs in a custom OU, we either need to prestage the VCOs (see: http://technet.microsoft.com/en-us/library/dn466519.aspx for instructions on how to do so) or we need to grant the CNO permissions to Create Computer objects at the OU level.
To do this, we do the following (these are straight from the TechNet document linked above):
  1. In Active Directory Users and Computers, on the View menu, make sure that Advanced Features is selected.
  2. Right-click the OU where you created the CNO in Step 1: Prestage the CNO in AD DS, and then click Properties.
  3. On the Security tab, click Advanced.
  4. In the Advanced Security Settings dialog box, click Add.
  5. Next to Principal, click Select a principal.
  6. In the Select User, Computer, Service Account, or Groups dialog box, click Object Types, select the Computers check box, and then click OK.
  7. Under Enter the object names to select, enter the name of the CNO, click Check Names, and then click OK. In response to the warning message that says that you are about to add a disabled object, click OK.
  8. In the Permission Entry dialog box, make sure that the Type list is set to Allow, and the Applies to list is set to This object and all descendant objects.
  9. Under Permissions, select the Create Computer objects check box.
After I do this, the next time I start my AG-DIGA role, everything comes online as expected.
If we peak at AD again, you can see the AG-DIGA VCO computer object is now created:
Even after doing this prestage the setup or failover of role gives error 1207. kindly refer 

Active Directory Permissions for Cluster Accounts



Referernce :-
https://blogs.technet.microsoft.com/askpfeplat/2014/11/17/when-creating-a-new-resource-or-role-in-windows-server-2012-r2-failover-cluster-the-network-name-fails-to-come-online-or-failed-to-create-associated-computer-object-in-domain/
https://technet.microsoft.com/en-us/library/cc773451(v=ws.10).aspx

http://www.tech-coffee.net/alwayson-availability-group-wsfc-cluster-creation/

https://technet.microsoft.com/en-us/library/dn466519(v=ws.11).aspx -- step -3