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.

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.








Labels: ,

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.
 

Labels:
Subscribe to: Posts (Atom)