Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,644,772 hits
  • Select GETDATE()

    July 2019
    M T W T F S S
    « Apr    
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  
  • Advertisements

Archive for the ‘AlwaysOn’ Category

Solution : The connection to the primary replica is not active. The command cannot be processed.

Posted by blakhani on July 1, 2014


It has been close to a year since I published my first book (SQL Server 2012 AlwaysOnPaperback, Kindle) and since then I have been contacted by many DBA to troubleshoot various issue related to AlwaysOn Availability Groups. One of the most common error which I have seen is as below.

Msg 35250, Level 16, State 7, Line 1
The connection to the primary replica is not active. The command cannot be processed.

This error mostly appears when we try to join the database to availability group. by UI, T-SQL or PowerShell.

SSMS UI:

While trying to create new Availability Group, we might received below and “join” step would fail.

image

Here is the message in text format.

TITLE: Microsoft SQL Server Management Studio
——————————
Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)
——————————
ADDITIONAL INFORMATION:
Failed to join the database ‘Production’ to the availability group ‘ProductionAG’ on the availability replica ‘SRV2’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error: 35250)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=35250&LinkId=20476
——————————
BUTTONS:
OK
——————————

T-SQL:

image

Msg 35250, Level 16, State 7, Line 1
The connection to the primary replica is not active. The command cannot be processed.

PowerShell:

Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\SRV2\DEFAULT\AvailabilityGroups\ProductionAG" -Database "Production"

**********************************
Add-SqlAvailabilityDatabase : The connection to the primary replica is not active.  The command cannot be processed.
At line:1 char:28
+ Add-SqlAvailabilityDatabase <<<<  -Path "SQLSERVER:\SQL\SRV2\DEFAULT\AvailabilityGroups\ProductionAG" -Database "Production"
     + CategoryInfo          : InvalidOperation: (:) [Add-SqlAvailabilityDatabase], SqlException
     + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDatabaseCommand
**********************************

Solution

I have always suggested them to start looking at errorlog and check what is the error which most of the DBA have reported.

2014-06-30 17:29:33.500 Logon        Database Mirroring login attempt by user ‘HADOMAIN\SRV1$.’ failed with error: ‘Connection handshake failed. The login ‘HADOMAIN\SRV1$’ does not have CONNECT permission on the endpoint. State 84.’.  [CLIENT: 192.168.1.11]

In above message, HADOMAIN is my domain name and SRV1 is the host name of SQL Server hosting primary replica.

Here is what have solved the issue for them.

  • Change SQL Server service account to a domain account and grant connect permission to the instances. If we are using different domain accounts on each replica then we need to add service accounts of all secondary replicas to primary replica logins.
  • If we are using non domain account (like LocalSystem or NT Service\MSSQLServer account) as service account and we can’t change it to domain account then we need to create machine accounts as login and grant connect permission. In our case machine name is SRV1 so machine account is HADOMAIN\SRV1$ (notice that $ at the end is a computer account)

     

    create login [HADOMAIN\SRV1$] from windows;
    go
    grant connect on endpoint::Mirroring to [HADOMAIN\SRV1$];
    go

Note: Endpoint Name might be different. We need to pick as per below image: If you have configured via UI earlier, it should be Hadr_endpoint

image

If you are running firewall, please make sure that port used by availability group is not blocked. We can easily find port using below command:

SELECT
te.port AS [ListenerPort],
te.is_dynamic_port AS [IsDynamicPort],
ISNULL(te.ip_address,'''') AS [ListenerIPAddress],
CAST(case when te.endpoint_id < 65536 then 1 else 0 end AS bit) AS [IsSystemObject]
FROM
sys.endpoints AS e
INNER JOIN sys.tcp_endpoints AS te ON te.endpoint_id=e.endpoint_id
image

Make sure that you have added exception for the port in firewall.

This is already documented in books online

{

If any server instances that are hosting the availability replicas for an availability group run as different accounts, the login each account must be created in master on the other server instance. Then, that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance.

}

Hope this would help you.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in AlwaysOn, Troubleshooting | Tagged: , , | Leave a Comment »

    Now Available @ Amazon : My Book on SQL Server 2012 AlwaysOn

    Posted by blakhani on August 5, 2013


    It might come as surprise to many of you. Finally, book authored by me is available for public on Amazon. (paperback and kindle). Those who follow Vinod’s (b|t) blog have seen this announcement already.

    It has been close to an year of writing and multiple reviews, my book on “SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” is finally available on Amazon.

    Reason of this blog is to answer two common questions which would be asked by my friends – “How?” and “Why?”

    “How” –

    A little spark was ignited when me, Vinod (b|t). and Pinal (b|t) were sitting together after a User Group meeting and thinking – what can be done to share the knowledge. Pinal and Vinod were already a well-known blogger and author. I said in feeble voice – I may have some additional knowledge on AlwaysOn, how about a book on this topic? … all of us agreed and journey started.
    Thanks to Vinod and Pinal for the spark of inspiration to set me off into looking things from different perspective. I wouldn’t have thought to become an “author” without your support. 

    After take off of the project, table of content of 5 chapters were prepared and we have taken task to write everything about the feature. I got my home PC upgraded to better RAM so that I can use my home PC for Hyper-V images which were needed for the book. We targeted 100 page and during weekly checkpoint meeting we always thought “something is missing” … after reaching 500+ pages, we felt that we have covered almost everything about the feature which can bring a starter to intermediate level.  Decision was taken to increase the page size and trim down the number of pages without compromising quantity and quality of knowledge. Lots of hours spent on emails, phone calls, chat, internet research, books-online reading, blogs reading to make fine prints. Finally, after an year of hard efforts, book was ready to be published. In paperback as well as kindle version.

    “Why” –

    It feels good when someone meets and says – “You blog helped me in fixing a problem”. Being a part of SQL Server technical support team, I felt that there is not much “organized” content on AlwaysOn feature. I spoke to many folks and sensed a “fear” of this new feature. There are various blogs on the same topic and books online also has lots of content. By writing this book and sharing my knowledge , I feel that I am doing my part for the community which is driven by passionate people and MVPs.  I love technology, SQL Server and its features. There were many questions asked and being of a very tidy disposition, I liked to have them answered. That’s why we added a new chapter in the book for FAQs along and there were around 70+ questions.

    What we have in the book?

    Here is the crisp summary of the book.

    clip_image001

    18 Chapters at a Glance

    Chapter 1. High Availability and Disaster Recovery Concepts
    Chapter 2. Existing High Availability and Disaster Recovery Options
    Chapter 3. What is AlwaysOn?
    Chapter 4. Understanding Quorum Models
    Chapter 5. AlwaysOn Availability Groups
    Chapter 6. Availability Group (AG) Actions on the Secondary
    Chapter 7. Deploying AlwaysOn Availability Groups
    Chapter 8. Features of AlwaysOn Availability Groups
    Chapter 9. AlwaysOn Monitoring and Troubleshooting
    Chapter 10. AlwaysOn Diagnostics
    Chapter 11. AlwaysOn Advanced Monitoring
    Chapter 12. Deployment Variations of AlwaysOn
    Chapter 13. AlwaysOn Common Issues
    Chapter 14. Availability Group Failover
    Chapter 15. Migrating from Previous High Availability Scenarios
    Chapter 16. AlwaysOn Availability Group Maintenance Activities
    Chapter 17. AlwaysOn – FAQs
    Chapter 18. SQL Server 2014 – AlwaysOn Enhancements Teaser

     

    How can you get it?

    At this moment we don’t have any other channel to publish this book in India. Our publisher is in constant pursuit to look for alternatives, till then Amazon will the only channel.
    Print Book: Amazon
    e-Book: Kindle

    You can read first two chapters for free on amazon via LOOK INSIDE feature on kindle edition.

    image

     

    I truly hope that it would help many DBAs in the world to get started with AlwaysOn Availability Group feature and deploy as high availability + disaster recovery in the environment.

    Before I conclude this blog post, I want to specifically call out name of few people from my management team who have backed me in this adventure of learning and sharing via this book – Sri Krishna Jagannath (Team Manager – SQL Support), Rahul Jacob (Team Manager – SQL Support), Xavier S Raj (Escalation Manager – SQL Support), Ranjan Bhattacharjee (Group Manager – Developer Support). It would have been impossible to deliver this book without their unconditional support.

    Last, but not the least, Special Thanks to my wife and daughter for being supportive through out this journey!

     

    Cheers,
    Balmukund Lakhani
    Twitter @blakhani

    Posted in AlwaysOn, Denali, Introduction, SQL Server, SQL Server 2012, Step by Step, TechEd | 11 Comments »

    Step by Step – Configuring SQL Server 2012 AlwaysOn in Windows 7 Virtual Environment (Part 1)

    Posted by blakhani on May 3, 2012


    I have been demonstrating AlwaysOn feature to various audiences on various forums (TechEd, SQL Saturday, Virtual TechDays etc.). I have been using Windows Server 2008 R2 with Hyper-V to create multiple machine and demonstrate the feature. Someone asked me if he can do the same thing using his Windows 7 laptop? I tried looking at virtualization techniques available on Windows 7 and I found that it should be possible. I have tried to make it as descriptive as possible with step by step screenshots to configure whole setup.This topic would be a multipart series.

    1. Configure Windows 7 to use Virtualization and Configure three machines (One domain controller, two member servers). Also configure networking between them.
    2. Create Domain and configure the machines to make sure they are able to talk to each other.
    3. Install SQL Server 2012 on the servers.
    4. Configure AlwaysOn.

    First download Windows Virtual PC on Windows 7 machine. Go to http://www.microsoft.com/windows/virtual-pc/download.aspx and choose below option. I am not going to Install Windows XP mode.

    01_VPC

    One clicked, choose your platform and language and click on download button.

    02_VPC

    This would install Windows Update to enable Virtual PC

    03_Windows Installer

     

    Restart the machine and you should be able to see below under "Start” > “Programs” > “Windows Virtual PC”

    04_Windows Virual PC Shortcut

    Next step would be to download the Virtual Hard Disk (a.k.a. VHD) file which I can use to configure my machine. Note that You need to use 32 bit images to make this work. Here is the link to download Windows Server 2008 images http://www.microsoft.com/en-us/download/details.aspx?id=14527 (Windows Server 2008 Enterprise Edition x86 (Full Install) VHD)

    There are three parts which you need to download and then extract to user by Virtual PC. It would look like below.

    05_Downloaded VHD

    Double Click on the File and Extract the data into “C:\VHDs\DC” folder as shown below:

    06_Extract

    Once extraction is complete, make three copies of the vhd (Windows2008Fullx86Ent) file and keep them in separate folders as shown below. [I am showing only two, same exists for Srv2 also]

    07_Copies

    Lets add the machine to Virtual PC. Open the UI from "Start” > “Programs” > “Windows Virtual PC” > “Windows Virtual PC” and choose “Create virtual machine”

    08_Create Machine 

    Follow the wizard and create DC

    09_Create Machine1 

    10_Create Machine_Mem

    11_Create Machine_Disk

    One done, lets go to network setting and choose “Internal Network” This is because I don’t want to create domain controller in my office domain network.

    Right Click on DC and choose “Settings”

    12_Setting

    Go to Networking option and choose “Internal Network”

    13_Setting_Network

    Now start the first machine and make sure we are able to log in.

    14_DC_Startup

    You need to follow few steps which are pretty self-explanatory. It would also as you to set machine name (I have given name as DC) new password for Administrator. Once you are done with that, this is what you should see 🙂

    15_DC_LoggedIn

    As homework, before moving to second part, you need to do the same thing with two more machines and here is what you should see in Windows Virtual PC.

    16_Three Machines

    See you at next part of Series!

    Hope this would help someone in the world!

    Cheers,
    Balmukund Lakhani
    Twitter @blakhani

    Posted in AlwaysOn, Denali, Images, Installation, Installation Guide, Introduction, screen shot, Screenshot, SQL Server 2012, Step by Step | 4 Comments »