Azure/ Azure Kubernetes Cluster/ MS SQL Server / Azure /Azure DevOps and Terraform

The articles in the blog deals with implementing/Administration/Troubleshooting of SQL Server, Azure,GCP and Terraform I rarely write for a place to store my own experiences for future search but can hopefully help others along the way

About Me

My photo
Rakesh Kumar
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!
View my complete profile

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.








Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Cannot connect to WMI provider, Invalid class [0x80041010]

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.

 




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.
 

Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Mirroring from SQl server 2008 to SQl server 2014 with minimum downtime

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.. 
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Culture=neutral, msddsp, PublicKeyToken=b03f5f7f11d50a3a', Version=9.0.0.0

Distribution Database Growth is huge in Merge Replication

Distribution Database Growth is huge in Merge Replication


Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest

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

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

Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest

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.

Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: 21197, Publisher failed to allocate a new set of identity ranges for the subscription, Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers

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.


Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: 5057, API, code, control, error, Level 16, Line 3, Msg 41009, resource, returned, state 7, WSFC

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

Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: 0x8009030c, An OS call failed: (8009030c), Database mirroring connection error 2 'DNS lookup failed with error: '11004, Mirroring disconnected with error No 10054 and 11004

How do i change,Update SQL Server Collation for SQL Server failover cluster Instance.

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


Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: How to i 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

Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Failed to open file C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\devenv.exe.config.tmp Error Code: 1603, Service pack installation failed.

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


Issue:-

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

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



Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Failed to join local availability replica to availability group, Level 16, Line 2, Msg 41158, State 3

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

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

Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest

Availability replica is in disconnected state.

Availability replica is in disconnected state

Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Availability replica is in disconnected state and AlwaysOn secondaries are in disconnected state.

How to create SQL logins in AlwaysOn Environment

       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 
go
-- 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 
go
DROP LOGIN [TestDBA]
GO

-- 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 :) 




Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest

SQL Server setup fails on SQLBrowserConfigAction_install_ConfigNonRC_CPU32

Issue:- SQL Server setup Installation  gets fail with below error highlighted in details.txt file.
Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: A;CI;KR;;;[SQLServer2005SQLBrowserUser$XXXXDB1, SQLBrowserConfigAction_install_ConfigNonRC_CPU32

How to detect Head Blocker


How to detect  head Blocker
Read more »
No comments:
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: detect, Head Blocker, In SQL Server

OnlineThread: Error 435 bringing resource online and error 2310 while failover of database server.

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.


Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Operating system error = 2310, ResUtilsStartResourceService failed (status 435)

View Node Weight Configure and Adjust Cluster Quorum Node Weight Settings.


Configure Cluster Quorum NodeWeight Settings


Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: View Node Weight Configure and Adjust Cluster Quorum Node Weight Settings.

How to add Database in AlwaysOn Availability Group - Using Script


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


Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Add Database in AlwaysOn Availability Group, Alter availability group, Alter database

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

Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Commit limit. Page file, Windows successfully diagnosed a low virtual memory condition.Commit Charge, Windows-Resource-Exhaustion-Detector_2004

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

Read more »
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: fn_backup_db_config, Server Managed Backup aka Smart Backup and Restore from Azure storage, smart_admin.sp_backup_on_demand, smart_backup_files, SQL Server Managed Backup to Microsoft Azure

There is already a master key in the database. Please drop it before performing this statement.Drop Master key plus Understanding encryption hierarchy..


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/
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Level 16, Msg 15578, There is already a master key in the database.

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.

or
sp_changedbowner 'Contoso\sqlservice'


Thanks for Reading...

Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Labels: Error number: 15517, MSSQL_REPL20011
Newer Posts Older Posts Home
Subscribe to: Posts (Atom)

Followers

Blog Archive

  • ►  2025 (34)
    • ►  April (5)
    • ►  March (1)
    • ►  February (27)
    • ►  January (1)
  • ►  2024 (68)
    • ►  November (2)
    • ►  October (13)
    • ►  September (40)
    • ►  August (10)
    • ►  May (2)
    • ►  January (1)
  • ►  2023 (37)
    • ►  August (1)
    • ►  July (1)
    • ►  April (3)
    • ►  March (15)
    • ►  February (6)
    • ►  January (11)
  • ►  2022 (9)
    • ►  December (2)
    • ►  October (6)
    • ►  February (1)
  • ►  2021 (4)
    • ►  January (4)
  • ►  2020 (8)
    • ►  December (1)
    • ►  August (3)
    • ►  July (1)
    • ►  May (1)
    • ►  April (2)
  • ►  2019 (13)
    • ►  October (1)
    • ►  September (3)
    • ►  August (1)
    • ►  April (3)
    • ►  March (2)
    • ►  February (1)
    • ►  January (2)
  • ►  2018 (74)
    • ►  November (1)
    • ►  September (3)
    • ►  August (5)
    • ►  July (13)
    • ►  June (5)
    • ►  May (2)
    • ►  April (17)
    • ►  March (14)
    • ►  February (8)
    • ►  January (6)
  • ►  2017 (14)
    • ►  September (1)
    • ►  July (2)
    • ►  June (1)
    • ►  April (3)
    • ►  March (2)
    • ►  February (5)
  • ►  2016 (2)
    • ►  August (1)
    • ►  April (1)
  • ▼  2015 (32)
    • ▼  December (2)
      • Cannot connect to WMI provider. You do not have pe...
      • Minimize Downtime for Mirrored Databases When Upgr...
    • ►  November (3)
      • Could not load file or assembly 'msddsp, Version=9...
      • Distribution Database Growth is huge in Merge Repl...
      • How to add Table to Existing Transactional -Repli...
    • ►  October (8)
      • Troubleshooting The Publisher failed to allocate a...
      • The Windows Server Failover Clustering (WSFC) reso...
      • Database mirroring connection error 2 'DNS lookup ...
      • How do i change,Update SQL Server Collation for SQ...
      • Failed to open file C:\Program Files (x86)\Microso...
      • Adding Replica of Multisubnet gives error operatio...
      • Manual Failover of Availability Group to Disaster ...
      • Availability replica is in disconnected state.
    • ►  September (11)
      • How to create SQL logins in AlwaysOn Environment
      • SQL Server setup fails on SQLBrowserConfigAction_i...
      • How to detect Head Blocker
      • OnlineThread: Error 435 bringing resource online a...
      • View Node Weight Configure and Adjust Cluster Quo...
      • How to add Database in AlwaysOn Availability Group...
      • How to troubleshoot Error No#2004- Windows success...
      • Introducing SQL Server Managed Backup aka Smart Ba...
      • There is already a master key in the database. Ple...
      • The process could not execute 'sp_replcmds' on 'SQ...
    • ►  August (5)
    • ►  July (3)
  • ►  2014 (1)
    • ►  August (1)
  • ►  2013 (11)
    • ►  August (4)
    • ►  March (7)
  • ►  2012 (6)
    • ►  April (1)
    • ►  March (5)
  • ►  2011 (1)
    • ►  October (1)
Simple theme. Powered by Blogger.