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

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

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.

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

Configure Cluster Quorum NodeWeight Settings

How to add Database in AlwaysOn Availability Group - Using Script

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

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/

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

Autoamtic Failover of AlwaysOn and Error No:1135 in System log and NetftRemoteUnreachable event found in Cluster log

Issue: - Automatic failover of AlwaysOn  Availability group from one node to another has been notified in IaaS Environments.


Its resolution is in these 2 links

Link:-

 http://blogs.msdn.com/b/clustering/archive/2012/11/21/10370765.aspx

 http://blogs.msdn.com/b/alwaysonpro/archive/2014/06/02/iass-with-sql-alwayson-tuning-failover-cluster-network-thresholds.aspx