About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!

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 







Cannot connect to WMI provider. You do not have permission or the server is unreachable.


Issue:-
---------------------------
SQL Server Configuration Manager
---------------------------
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]
---------------------------
OK  
---------------------------




Problem :- 
Sometimes SQL Server Configuration Manager does not open and get error specified above..

Cause:-
Cause
This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.
 
Resolution:-

In order to resolve this issue..

Workaround
To work around this problem, open a command prompt, type the following command, and then press ENTER:
mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"
Note For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

The value of number depends on the version of SQL Server:nnn

SQL SERVER 2014 -- 120
SQL SERVER 2012 -- 110
SQL Server 2008 R2 -- 100
SQL Server 2008 -- 100
SQL Server 2005 -- 90

I executed below command in the server 



After you run the Mofcomp tool, 

Restart the WMI service for the changes to take effect. The service name is Windows management Instrumentation.

After successful execution of mofcomp command and restart of WMI service..
you will be able successfully able to connect to SQL Server Configuration Manager.








Minimize Downtime for Mirrored Databases When Upgrading Server Instances from SQL server 2008 to SQL server 2012/2014/2016.

Various ways to Migrate SQL Server from one version to another.

1.  Traditional ways
     1. Take backup from old server and restore into new server – Hardware cost involved.
     2. Detach database from old Server and attach into new server – Long downtime

   2. Rolling upgrade
                                      1.   In-place update of SQL server from SQL server 2008 or 2008 R2 to SQL Server 2012/ 2014 – Risk involved as after PONR, in place upgrade fails   we cannot revert back to previous
                                       2.  Minimize Downtime for Mirrored Databases When Upgrading Server Instances.

Here we will discuss how to Minimize Downtime for Mirrored Databases When Upgrading Server Instances.

                   When upgrading server instances to SQL Server 2014, you can reduce downtime for each mirrored database to only a single manual failover by performing a sequential upgrade, known as a rolling upgrade. A rolling upgrade is a multi-stage process that in its simplest form involves upgrading the server instance that is currently acting as the mirror server in a mirroring session, then manually failing over the mirrored database, upgrading the former principal server, and resuming mirroring. In practice, the exact process will depend on the operating mode and the number and layout of mirroring session running on the server instances that you are upgrading.     

Suppose we have 2 SQL Server Instance

    Principal Server in SQL Server 2008 / SQL server 2008 R2        -- Says Server-A.
    Mirror Server in SQL Server 2008 /SQL Server 2008 R2 -- Says Server-B.

    High Performance/High Safety Mirroring is configured between Server A and Server B.


 

        
          
The End Task is to upgrade Server-A and Server -B from SQL Server 2008 to SQL Server 2014, with Minimum downtime.
How we will achieve this.

As this is a multi-Stage process, hence it all depends on operating mode of mirroring...
  
   If operating mode is High Performance means, Asynchronous mirroring...
          
  1. Switch Mirroring Session to high safety without automatic failover and suspend Mirroring
  2. Now Perform rolling upgrade in each session by:-
-          Upgrade Mirror Server – Server-B. Hence now Mirror Server Server-B will be in SQL server 2014.
-          Now manually failover to upgraded Mirror Server. – Server –B.
-          Now Connect  Frontend Application with Server – B
-          You will observe Mirroring Working perfectly between Server – A (SQL server 2008) to Server –B(SQL Server 2014)
   

  1. Run DBCC CHECKDB on every principal database.                     
  2. Resume Mirroring
  3. Return session to high performance mode.
  4. Upgrade the new mirror Server instance.  (Server –A), you will observe Mirroring working perfectly.
  5. Now manually failover to upgraded Mirror Server- Server-A from Server -B
  6. Change Application connection back to Server-A.

 





The following illustration is a flowchart that shows the basic stages of a rolling upgrade for each operating mode. The corresponding procedures are described after the illustration.
 

Could not load file or assembly 'msddsp, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'

Issue:-
when we double click on the Maintenance plans to modify or Execute, under Management --> Mainteance Plans. We get below error. 

Could not load file or assembly 'msddsp, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified. (Microsoft.DataTransformationServices.Design)





In order to resolve this error...

In your existing envirnment, copy this Folder from Any good Server, the source server from where you are getting this file should have same version of Windows + SQL Server. 

Copy MSDesigners8 folder to
..\Program Files (x86)\Common Files\Microsoft Shared\MSDesigners8
\Resources 
  • msdds.dll 
  • msddsf.dll 
  • msddslm.dll 
  • msddslmp.dll 
  • msddsp.dll 

2. Register following DLLs with RegSvr32
  • msdds.dll 
  • msddsf.dll 
  • msddslm.dll 


3. Import following .NET assemblies 

  • msddslmp.dll 
  • msddsp.dll 
It actually works just dragging it and drop to ..\Windows\Assemblies 

If the above does not work:

Go to the source server to below path:
C:\windows\assembly\GAC_MSIL

Copy folders “msddslmp.dll” and “msddsp.dll” to “C:\windows\assembly\GAC_MSIL” path of target server.

your issue will get resolved. 

Thanks for Reading..