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.

Kumar, Rakesh > Minimize Downtime for Mirrored Databases When Upgrading Server Instances from SQL server 2008 to SQL server 2012/2014/2016. > MIrror-1.JPG

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...

Switch Mirroring Session to high safety without automatic failover and suspend Mirroring
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)

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

Logic inherited from this link:- https://msdn.microsoft.com/en-US/library/bb677181(v=sql.120).aspx

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..

Distribution Database Growth is huge in Merge Replication

How to add Table to Existing Transactional -Replication when database size is approx 1 TB.

Troubleshooting The Publisher failed to allocate a new set of identity ranges for the subscription error 21197

The process could not read file 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ReplData\unc\WIN2K12-1_ADVENTUREWORKS2014_MERGE_ADVENTUREWORKS2014_MERGE\20151029073334\Name_73.sch' due to OS error 3.

The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057. The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid.

Error:-

While creating an Availability Group Listener I received this error
Msg 41009, Level 16, State 7, Line 1
The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057.
The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. For information about this error code, see "System Error Codes" in the Windows Development documentation.

Database mirroring connection error 2 'DNS lookup failed with error: '11004(The requested name is valid, but no data of the requested type was found.)'.' Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c

Database Mirroring was in Disconnected state and when checked Errorlog file found below entry in Principal and Mirror Server

How I do change/Update SQL Server Collation for SQL Server failover cluster Instance?

Failed to open file C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\devenv.exe.config.tmp

Issue:-

SQL Server 2008 R2 setup failed while update from SP1 to CU5

Adding Replica of Multisubnet gives error operation encountered SQL Server error 19456 and has been rolled back.

Issue:-

Failed to join local availability replica to availability group 'SQL0040DAG01'. The operation encountered SQL Server error 19456 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 19456)

Disconnecting connection from Node3SQL03...

Issue:-

Servers in Multi-subnet clustering with AlwaysOn Configured in Multi-subnet Envirnment
DataCenter-1	IP	DataCenter-2	MgmtIP
Node1SQL01	192.168.1.10	Node3SQL03	192.168.2.12
Node2SQL02	192.168.1.11	Node4SQL04	192.168.2.13

and existing Availability group is -- SQL0040DAG01

The requirement is to add 3rd replica{Node3SQL03} in existing AG which belongs to a different data center and hence in the different network.

I encountered below error, while adding Availability Replica in AG in Multi-subnet environment:

None of the IP addresses configured for the availability group listener can be hosted by the server 'Node3SQL03'. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can be hosted on a public cluster network for this server.

Failed to join local availability replica to availability group 'SQL0040DAG01'. The operation encountered SQL Server error 19456 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 19456)

Failed to join local availability replica to availability group 'SQL0040DAG01'. The operation encountered SQL Server error 19456 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.

Disconnecting connection from Node3SQL03...

And

Connecting to Node3SQL03...

Msg 41158, Level 16, State 3, Line 2

Failed to join local availability replica to availability group 'SQL0040DAG01'. The operation encountered SQL Server error 41106 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.

Disconnecting connection from Node3SQL03...

Reason of this issue:-

For this AG, which is implemented in multi-subnet cluster, existing listener was configured only with one of the subnet IPaddress [192.168.1.*], which belongs to datacenter 1.

This error message popped up while configuring the replicas in another subnet, and the reason for error message was that the listener was configured with only one subnet IP address.

Hence there is a need to add IP address for another subnet in this existing listener.

Resolution:-

So once we will configure listener with IPaddress of second subnet, as shown in the figure -1, we will be able to successfully add replica of the secondary subnet’s [192.168.2.*].

To do this, I clicked on existing listener SQL0040DAG01 –> Property --> and added an IP address of 192.168.2.14(Datacenter2)

Then Click OK.

Now try to add 3rd replica on existing AG, the issue will get resolve.

Hope this will help to someone who has this issue.

Thanks for reading...

Manual Failover of Availability Group to Disaster Recovery site in Multi-Site Cluster

Availability replica is in disconnected state

How to create Login’s in case of AlwaysOn Database Server.

-------------------------------------------------------------------------------------------------------------------------------------------------

The Process is:-

Availability group (SQL0040AAG01) has 4 replica names

1. SQLNode1 -- Primary

2. SQLNode2 -- Secondary

3. SQLNode3 -- -- Secondary

4. SQlNode4 -- -- Secondary

Whereas SQLNode1 is Primary replica and other 3 are secondary replica.

Then, the logic is for other 3 replica login SID should match...

/* On the PRIMARY replica */ [SQLNODE1]

  USE  [master]

  GO

  CREATE LOGIN [TestDBA] WITH PASSWORD=N'Test@123', DEFAULT_DATABASE=[master],
  CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

  GO

  USE [Adven_pub]

  GO

  CREATE USER [TestDBA] FOR LOGIN [TestDBA]

  GO

  USE [Adven_pub]

  GO

  ALTER ROLE [db_datareader] ADD MEMBER [TestDBA]

  GO

  USE [Reservation]

  GO

  CREATE USER [TestDBA] FOR LOGIN [TestDBA]

  GO

In primary replica using this site – > https://support.microsoft.com/en-us/kb/918992

get an script for sp_help_revlogin

Execute this complete stored procedure in master database, from Primary replica

then In primary replica use below command :-

sp_help_revlogin 'TestDBA'

you will get an output like this :-

/* sp_help_revlogin script

** Generated Sep 30 2015 12:47PM on SQLNode1 */

-- Login: TestDBA
CREATE LOGIN [TestDBA] WITH PASSWORD = 0x0200670719A986569F7AC81D280F060E1DF59E55DE89DD83905F9443DD752329EC77AA7038C6D816D46A07C952F9D7F2BF7DEFDFB5744659F566543A790624712C82A457BBA3 HASHED, SID = 0xC6E83AFC0F3A674BB70D0279A60291DB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Connect SQLNode2

copy create login script to secondary replica:-

and execute this in

use master

-- Login: TestDBA
CREATE LOGIN [TestDBA] WITH PASSWORD = 0x0200670719A986569F7AC81D280F060E1DF59E55DE89DD83905F9443DD752329EC77AA7038C6D816D46A07C952F9D7F2BF7DEFDFB5744659F566543A790624712C82A457BBA3 HASHED, SID = 0xC6E83AFC0F3A674BB70D0279A60291DB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Permissions will get replicated automatically to secondary replica using AlwaysOn methodology.

Replicate the same process in another secondary replica for that database... .

If user exists in secondary replica and no one is using this login or password mismatch error comes or orphan login issue comes then

Connect secondary replica SQLNode2

Copy this to secondary replica:-

Use master

DROP LOGIN [TestDBA]

-- Login: TestDBA

CREATE LOGIN [TestDBA] WITH PASSWORD = 0x0200670719A986569F7AC81D280F060E1DF59E55DE89DD83905F9443DD752329EC77AA7038C6D816D46A07C952F9D7F2BF7DEFDFB5744659F566543A790624712C82A457BBA3 HASHED, SID = 0xC6E83AFC0F3A674BB70D0279A60291DB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Thanks for reading and Happy Learning :)

SQL Server setup fails on SQLBrowserConfigAction_install_ConfigNonRC_CPU32

Issue:- SQL Server setup Installation gets fail with below error highlighted in details.txt file.

How to detect Head Blocker

How to detect head Blocker

Issue:-

Could not register Service Control Handler. Operating system error = 2310(This shared resource does not exist.).

n [sqsrvres] StartResourceService: Failed to start MSSQL$HomeSQL01 service. CurrentState: 1

n [sqsrvres] OnlineThread: ResUtilsStartResourceService failed (status 435)

n [sqsrvres] OnlineThread: Error 435 bringing resource online.

n Could not register Service Control Handler. Operating system error = 2310(This shared resource does not exist.).

n [sqsrvres] StartResourceService: Failed to start MSSQL$HomeSQL01 service. CurrentState: 1

n sqsrvres] OnlineThread: ResUtilsStartResourceService failed (status 435)

n [sqsrvres] OnlineThread: Error 435 bringing resource online.

Configure Cluster Quorum NodeWeight Settings

How to Add Database in AlwaysOn Availability Group - using TSQL Script

How to troubleshoot Error No#2004- Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory:

Issue:-

Windows-Resource-Exhaustion-Detector_2004: Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory: sqlservr.exe (1668) consumed 4012085248 bytes, svchost.exe (848) consumed 18599444

Introducing SQL Server Managed Backup aka Smart Backup and Restore from Azure storage SQL 2014

Step by Step configuration for SQL Server Managed Backup aka Smart Backup and Restore from Azure storage

Issue: -There is already a master key in the database. Please drop it before performing this statement.

------------------------------------------------------------------------------------------------------------------------

USE Master ;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Singapore@123';
GO

Issue:-
Msg 15578, Level 16, State 1, Line 1
There is already a master key in the database. Please drop it before performing this statement.

hence Executing this command

let's see what happen

USE Master;
go
drop Master Key
Go

Msg 15580, Level 16, State 1, Line 1
Cannot drop master key because certificate 'TDECert' is encrypted by it.

it looks we have TDECert Certificate which is encrypted by this master key, Analogy is you have kept your certificate in a Wood drawer. your certificate is protected with this wooden drawer, hence you can not drop this master key.

hence the next step would be to drop the certificate How?

USE Master;
go
drop certificate TDECert
go

In my case this statement executed successfully.

hence you can now drop master key.

Use master;
go
drop master key
go

-- This Command will execute successfully.. if previous drop certificate has ----executed successfully then..
it means your certificate is not bound to any database encryption key.

otherwise you will get error..

Msg 3716, Level 16, State 15, Line 1
The certificate ‘TDECert’ cannot be dropped because it is bound to one or more database encryption key.

This message is very clear and it states that the certificate cannot be dropped as its related to the database Encryption key.

then Execute this command
use Master
Go
Drop database Encryption key
Go;
it will give error

or
we will get error:-
Msg 33102, Level 16, State 7, Line 1

Cannot encrypt a system database. Database encryption operations cannot be performed for 'master', 'model', 'tempdb', 'msdb', or 'resource' databases.

this message is very loud & clear that we can not execute this command in system database.

Msg 33105, Level 16, State 1, Line 1
Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.

it looks we have to turn off encryption before dropping database encryption key..

hence search for which database encryption is enabled and for that database you can turn Encryption off.

/*This Script will give you result output for which database TDE is enabled.
---------------------------------------------------------------------------------------------------------
USE master;
GO

SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;

GO
----------------------------------------------------------------------------------------------------------------
Use Master
Go
Alter database TestDatabase Set encryption off
Go
-- Command completed successfully.

Now we can successfully drop encryption key for that database

use TestDatabase
go
drop database encryption key
go

Now you can drop in below order..

1. Encryption key -- DROP Database encryption key -- on user database
2. Certificate -- DROP Certificate TDECert -- on master database
3. master key -- DROP Master Key -- on master database

Use Testdatabase
Go
DROP Database encryption key;
go

use master
go
select * from sys.certificates;
--get certificate which you have to drop

DROP Certificate TDECert;

DROP Master Key;

In this way, we can clear our test/Lab environment.

Thank you very much for reading this and if time allows leave Comment.

Extra Reading :- http://www.sqlservercentral.com/articles/Encryption/108750/

The process could not execute 'sp_replcmds' on 'SQLONE'. Log reader failes with Cannot execute as the database principal becuase the principal "dbo" does not exist

Log Reader Agent do not restart and it gets fail with below error
The process could not execute 'sp_replcmds' on 'SQLONE'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011

----------------------------------------------------

Issue:-

Error messages:

The process could not execute 'sp_replcmds' on 'SQLONE'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)

Get help: http://help/15517

The process could not execute 'sp_replcmds' on 'SQLONE'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

Get help: http://help/MSSQL_REPL22037

The SQL Agent was configured to run using Contoso\sqlservice.

In login properties, this account also has "system administrator" rights.

Executed log reader agent using a command prompt, received similar error..

C:\Program Files\Microsoft SQL Server\110\COM>logread.exe -Publisher [SQLONE] -PublisherDB [AdventureWorks2012] -Distributor [SQLONE] -DistributorSecurityMode 1 -Continuous

I received Similar error as above

Problem: - This issue arises sometimes when database owner remains blank, then this issue comes.

For RCA you can refer this link:-

Resolution:-

This Error usually comes when owner of Published database remains blank..

Hence to resolve this issue..

ALTER Authorization on DATABASE::[<dbname>] to [sa] --Make sure sa is enabled.

sp_changedbowner 'Contoso\sqlservice'

Thanks for Reading...

About Me

Cause

Workaround

How I do change/Update SQL Server Collation for SQL Server failover cluster Instance?

Reason of this issue:-

Resolution:-

Manual Failover of Availability Group to Disaster Recovery site in Multi-Site Cluster

Availability replica is in disconnected state

How to create Login’s in case of AlwaysOn Database Server.

Issue:-

Configure Cluster Quorum NodeWeight Settings

How to Add Database in AlwaysOn Availability Group - using TSQL Script

Issue: -There is already a master key in the database. Please drop it before performing this statement.

Log Reader Agent do not restart and it gets fail with below errorThe process could not execute 'sp_replcmds' on 'SQLONE'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)Get help: http://help/MSSQL_REPL20011

Issue:-

Error messages:

The process could not execute 'sp_replcmds' on 'SQLONE'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)Get help: http://help/MSSQL_REPL20011

Get help: http://help/15517

The process could not execute 'sp_replcmds' on 'SQLONE'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

Get help: http://help/MSSQL_REPL22037

Log Reader Agent do not restart and it gets fail with below error
The process could not execute 'sp_replcmds' on 'SQLONE'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011

The process could not execute 'sp_replcmds' on 'SQLONE'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011