SQL SERVER – None of the IP addresses configured for the availability group listener can be hosted by the server Either configure a public cluster network. Error: 19456

Requirement:

Add a node to availability alwayson group. It’s an multi-subnet and the node is a DR node on a different subnet.

It is a three node cluster two in same data center (US East) and third node is in DR (US Central).

Node A – 172.28.76.12

Node B – 172.28.76.13

Node 3 – 172.29.77.14

Error Message Received while adding the Node 3 in Replica Set.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

None of the IP addresses configured for the availability group listener can be hosted by the server . 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 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)

Solution:

Once looked at the IP addresses under the network name in the “Failover Cluster Manager” and found that there were only one IP range is added one in 172.28.XX.XX Whereas the replica that we were attempting to add was in 172.29.XX.XX range. now added an IP address (172.29.77.45) in the DR subnet as a dependency of the network name in the configured Listener “AG-Listner”. After adding the IP address in the cluster configuration we again attempted to join the replica and issue fixed node is added successfully.

 

Create a AWS Lambda Function to Stop and Start RDS Instance

I am getting request from friends to stop the Relational Database Service (RDS) instance after peak workload times. I am going to use AWS Lambda functions to stop the RDS instance. You can use the same steps to stop other supported types of instances, like MySQL, PostgreSQL, SQL Server or MariaDB.

Below mention steps to stop the RDS instance using Lambda functions.

Step 1: Login in the AWS console.

Step 1.1: Create an IAM role to enable access to start and stop an RDS instance and to enable CloudWatch logs.

Login into the IAM console and click on Policy and Create Policy.

Step 1.2: Once Create Policy editor open than select JSON and replace with the below JSON in the editor

{
    "Version": "2012-10-17",
    "Statement": [
        {           
            "Effect": "Allow",
            "Action": [
                "rds:DescribeDBInstances",
                "rds:StopDBInstance",
                "rds:StartDBInstance"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogGroup",
                "logs:CreateLogStream",
                "logs:PutLogEvents"
            ],
            "Resource": "*"
        }
    ]
}

Step 1.3: Give a policy name: start_stop_RDS_policy, along with a description and click on “Create Policy”

Step 1.4: Now in the IAM console click on Roles and Create Roles

Step 1.5: Choose AWS Services, Lambda as your service and Click on Next Permission.

Step 1.6: In the Search bar search for “start_stop_RDS_policy” and the policy you created earlier should appear as below.

Step 1.7: Provide a role name, start-stop-rds-lambda, and click Create Role.

Step 2: Create a Lambda function.

Step 2.1: To create a lambda function open the Lambda Service console and Click on Create Functions than Select Author from Scratch.

Provide the Basic Information:

Function Name – Stop_RDS_Inst

Runtime – Python 2.7

Under permission click on “Choose and create an existing Role” and Select “Existing Role” and choose the role created “start-stop-rds-lambda”.

Step 2.3: Click on Create Function

Step 2.6: Lambda function will be created as shown below.

Now make a note of ‘ARN‘ which is available at the top right corner which is used to grant permissions for the Lambda function to access Lambda API “GetFunctionConfiguration” and access to the environment variables.

Step 2.4: Go to the rule “start_stop_RDS_policy” and click on Add inline policy.

Step 2.5: Go to the JSON tab and enter as mention below then change the Resource to the Lambda ARN refer the step no 2.6 top right “ARN” value Example: “us-east-1:632886776519:function:RDSInstanceStop”.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "lambda:GetFunctionConfiguration",
            "Resource": "arn:aws:lambda:::function:RDSInstanceStop"
        }
    ]
}

Step 2.6: Give the name to the policy rds_stop_policy and click on Create Policy

Step 3: In the Python function, update the Code

import sys
import botocore
import boto3
from botocore.exceptions import ClientError


def lambda_handler(event, context):

    rds = boto3.client('rds')
    lambdaFunc = boto3.client('lambda')

    print 'Trying to get Environment variable'
    try:
        funcResponse = lambdaFunc.get_function_configuration(
            FunctionName='RDSInstanceStop'
        )

        DBinstance = funcResponse['Environment']['Variables']['DBInstanceName']

        print 'Stoping RDS service for DBInstance : ' + DBinstance

    except ClientError as e:
        print(e)    

    try:
        response = rds.stop_db_instance(
            DBInstanceIdentifier=DBinstance
        )

        print 'Success :: ' 
        return response
    except ClientError as e:
        print(e)    
    return
    {
        'message' : "Script execution completed. See Cloudwatch logs for complete output"
    }

Step 3.1: Now configure the environmental variable as mention below:

Key – DBInstanceName -> Never change the key value

Value – testdbind01 -> Give your RDS instance Name

Step 3.2: Now test the Lambda function created. On the top right corner next to Action tab select the Test and Configure the Test events

Step 3.3: Select Create New Test Event and select the Hello World event template.

Step 3.4: When you click on the execution should succeed.

Step 4: Configure the Schedule to monitor and stop the instance automatically based on lean time.

Step 4.1: Go to cloud watch and click on rules than click on Create Rules.

Step 4.2: In Event Source, select the Schedule and then select the cron expression. Now go to Targets, select the Lambda functions and In functions select the stop-rds-function and click on Configure Details

Now your instance will automatically shutdown based on your configured scheduled. It is not recommended to keep the instance down it will impact the instance maintenance task which will be done in the backed by AWS.

Note: Before implementation please check your environment and apply the above steps accordingly.

Amazon Web Services – Overview of AWS

Introduction about AWS

Amazon Web Services (AWS) began in 2006 offering IT infrastructure services to businesses in the form of web services. Currently it is commonly known as cloud computing. With the cloud services, Business Entities no longer need to plan for and procure servers and other IT infrastructure in advance, whereas they can instantly spin up as many servers in minutes and deliver results faster. AWS provides a highly reliable, scalable, low-cost infrastructure platform in the cloud the across world. One of the key benefits of cloud computing is the opportunity to replace up-front capital infrastructure expenses with low variable costs.

What Is Cloud Computing

Cloud computing is the on-demand delivery of compute power, database storage, applications, and other IT resources through a cloud services platform connected via the Internet with pay-as-you-go pricing model. With cloud computing, you don’t need to make large upfront investments in hardware and spend a lot of time on the heavy lifting of managing that hardware. Instead, you can provision accoridng to your requirement and start operating your IT
department. You can access as many resources as you need, almost instantly, and only pay for what you use.Cloud computing provides a simple way to access Services like (Servers, Storage, Databases and other application services) over the Internet. A cloud services platform such as (Azure, Google Cloud, Amazon Web Services many more) owns and maintains the network-connected hardware required for the application services, while you provision and use what you need via a web application.

Few of the Advantages of Cloud Computing

1. Easily deploy your application in multiple regions around the world with just a few clicks. This means you can        provide lower latency and a better experience for your customers at minimal cost.
2. Focus on projects that differentiate your business, not the infrastructure. Cloud computing lets you focus on your own customers, rather than on the heavy lifting of racking, stacking, and powering servers.
3. Eliminate guessing on your infrastructure capacity needs. When you make a capacity decision prior to deploying an application, you often end up either sitting on expensive idle resources or dealing with limited capacity. With cloud computing, these problems go away. You can access as much or as little capacity as you need, and scale up and down as required with only a few minutes’ notice.
4. In cloud computing, new IT resources are only a click away, which means that you reduce the time to make those resources available to your developers from weeks to just minutes. This results in a dramatic increase in agility for the organization, since the cost and time it takes to experiment and develop is significantly lower.

Types of Cloud Computing

Cloud computing provides developers and IT departments with the ability to focus on their job. Businees has to spend less time as compare to on-primises task like procurement, maintenance, and
capacity planning. As cloud computing has grown, there are several different models and deployment strategies have introduced to meet specific needs of different users. Lets understand the differences between (Infrastructure as a Service, Platform as a Service, and Software as a Service). This will help you to decide what set of services is right for your needs.

Cloud Computing Models

1. Infrastructure as a Service (Iaas)
2. Platform as a Service (PaaS)
3. Software as a Service (SaaS)

Infrastructure as a Service (IaaS)

Infrastructure as a Service (IaaS) contains the basic building blocks for cloud IT and typically
provide access to networking features, computers (virtual or on dedicated hardware), and data
storage space. IaaS provides you with the highest level of flexibility and management control
over your IT resources and is most similar to existing IT resources that many IT departments
and developers are familiar with today.

Platform as a Service (PaaS)

Platform as a Service (PaaS) removes the need for your organization to manage the underlying
infrastructure (usually hardware and operating systems) and allows you to focus on the
deployment and management of your applications. This helps you be more efficient as you
don’t need to worry about resource procurement, capacity planning, software maintenance,
patching, or any of the other undifferentiated heavy lifting involved in running your
application.

Software as a Service (SaaS)

Software as a Service (SaaS) provides you with a completed product that is run and managed
by the service provider. In most cases, people referring to Software as a Service are referring
to end-user applications. With a SaaS offering you do not have to think about how the service
is maintained or how the underlying infrastructure is managed; you only need to think about
how you will use that particular piece of software. A common example of a SaaS application is
web-based email which you can use to send and receive email without having to manage
feature additions to the email product or maintain the servers and operating systems that the
email program is running on.

[Warning] IP address ‘X.X.X.X’ could not be resolved: Name or service not known

In one of  RDS instance getting  warning messages in the error log file.

“[Warning] IP address ‘X.X.X.X’ could not be resolved: Name or service not known”

I have checked and DNS lookup is working fine.

In RDS hope you all aware about the concept of Security Groups where we white list the IP address so that they can connect the RDS instance.

Now coming back to problem statement. When ever you trying to make a connection with instance, MySQL has to do a reverse lookup on IP address connecting to it.

For Example: ‘deepakdba@172.13.124.12’ is the user name which is created to make DB connection. Now when you initiate the request to make the DB connection than it will do the reverse lookup to check whether the IP is from the correct domain or not.

To resolve this warning you have two options.

  1. Create a rule in the security group to whitelist the (0.0.0.0/00) range by doing this you are exposing the database instance to the internet user. I will not recommend it.
  2. You can switch this off with putting “Skip_name_resolve” parameter in your my.cnf file.

 

Hope this will help you.

 

Microsoft SQL Server 2016 Cluster installation

Step by Step installation of SQL Server 2016 cluster. SQL Server 2016 Cluster is similar to SQL Server 2008R2 but in SQL Server 2016 you can configure the TEMPDB during installation.

 

Please download the PDF version. SQL Server 2016 Cluster.

 

Enjoy the SQL cluster Installation in case any issue you can reach me on deepakbhardwaj21@hotmail.com

Important Points before backing up the SQL Server Databases

Important Points before backing up the SQL Server Databases.

Backing up SQL Server databases is one of the important job to protect the data.

Consider the below points while planning and backing up the databases.

1. One of the important step is the Backup plan. Most of the DBA only consider the user database backup during backup plan but DBA has to consider the other things as well like (Jobs, Analysis Services, Integration Services, Reporting Services, Users, System databases and other SQL server properties which is configured for an instances in Environment.

2. Once the Backup Plan Designed you should include the physical backup storage location protection policy, reason is that storage has all the backup of databases. In case if someone got the data than it would be very dangerous to your organisation. He can restore the data from backup file and can access the confidential information which you never want to share with any one. Ideally there are third party tools available in the market which allow backup encryption.

3. Always create one physical backup file for a database. By doing this we can maintain the consistency of backups in case you are maintaining one single backup file and overwrite it every time and due to any reason corruption occur at DB level than it will impact the entire backup and you will not be able to restore the entire data from the corrupted backup file so always create one single backup file while doing database backup.

4. Always store database backup to another location other than SQL Data and Log file location. In case any of (MDF and LDF) file drive corrupted than you will be in a state to restore it from the last available backup.

5. Never grant rights to multiple users for creating backups but you can grant permission to monitor the backup status.

6. Always monitor the backup status, size and time. in case backup fail than trigger a notification so that Database team aware about the backup failure status. By doing this you can do the capacity planning.

7. Always take the system database backup as and when any changes happen at instance level or database level.

8. With backup strategy you must plan the Restore test as well to check the backup consistency.

9. Maintain the backup retention policy as well according to organisation retention policy.

Windows server 2012 failover cluster Error:There are currently no logon servers available to service the logon request

Getting below Error while building windows 2012 failover cluster.

………………………………………………………………………………………………………………………………………………………..

Checking for account information for the computer object in the ‘UserAccountControl’ flag for CN=SQLDBCLU,CN=Computers,DC=abc,DC=co,DC=in.
Set password on SQLDBCLU. There are currently no logon servers available to service the logon request.
Unable to successfully cleanup.
An error occurred while creating the cluster and the nodes will be cleaned up. Please wait…
An error occurred while creating the cluster and the nodes will be cleaned up. Please wait…
There was an error cleaning up the cluster nodes. Use Clear-ClusterNode to manually clean up the nodes.
An error occurred while creating the cluster.
An error occurred creating cluster ‘SQLDBCLU’.

There are currently no logon servers available to service the logon request

To troubleshoot cluster creation problems, run the Validate a Configuration wizard on the servers you want to cluster.

…………………………………………………………………………………………………………………………………………………………

Solution:

When i run the validation wizard no error reported but when start the cluster configuration wizard and after passing the required details, cluster failed to configure.

Than i involved firewall team to check which port is getting blocked at firewall level during configuration.

Now i got the solution the problem is due to port block “646” at firewall level.  I asked team to open the “tcp/udp” port 464. After firewall team confirmation, again run the firewall configuration wizard and got the successful message.

Role of port 464

 

TCP and UDP 464 Replication, User and Computer Authentication, Trusts Kerberos change/set password

InnoDB: WARNING: over 67 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks.

Today one of my friend reported error message in one of his MySQL 5.6.35 Community Version.

InnoDB: WARNING: over 67 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks.

InnoDB: Your buffer pool size is 127 MB. Maybe you should make the buffer pool bigger?

InnoDB: Starting the InnoDB Monitor to print diagnostics, including lock heap and hash index sizes.

Solution:

This warning message mostly occur when the assigned “innodb-buffer-pool” size exceed the limit. Ideally you have to assign at least 70 to 80 percent of system memory to buffer pool. Also check the transactions wether it is a Sequential or Random transaction.

 

To resolve the issue you need to increase the value of “innodb-buffer-pool-size” in the “my.cnf” file and restart the mysql service.

 

Monitor the MySQL performance for a time and check the log file wether the error is coming or not.

 

 

[Warning] IP address ‘10.10.10.10’ could not be resolved: Name or service not known

Getting Warning Messages in MYSQL.log file.

[Warning] IP address ‘XX.XX.XX.XXcould not be resolved: Name or service not known.

Now question is why this warning message captured in MYSQL log file.

First we have to check the IP address or domain is internal or external. If the Users are not authorised users than we have to check the firewall port (3306) wether it is blocked or not for outside network and when you create a MySQL user  “ABC@abc.com” or “ABC@XX.XX.XX.XX” than MySQL has to do a reverse lookup on every IP address.

To fix this warning we need to add the parameter skip-host-cache and skip-name-resolve in my.cnf file.

 

Once added in “My.cnf” file than restart the MySQL service to apply the same.

 

Hope this resolve your concerns.

SQL Server Agent service failed to come Online on the Active Node in a 2 Node Fail-over cluster.

SQL Server Agent service failed to come Online on the Active Node in a 2 Node Fail-over cluster.

Error Messages in the Event-logs.
[sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 435)

[sqagtres] StartResourceService: Failed to start SQLSERVERAGENT service. CurrentState: 1

[sqagtres] OnlineThread: Error 435 bringing resource online.

To understand the problem, we have to check the AgentError log file (SQLAGENT.OUT) but what i see that there is no file in the Log Folder.

This means that file is not getting generated so what next let start the service from comand prompt to ge some more information and to find any permission issues.

Refer the below output when service started from command prompt.

Microsoft (R) SQLServerAgent 10.50.4000.0
Copyright (C) Microsoft Corporation.
StartServiceCtrlDispatcher failed (error 6)

Error 6 menas “The handle is invalid”

Now to understand and find the problemtic area let we run the procmon (Process Monitor is a free tool from Windows Sysinternals, part of the Microsoft TechNet website. The tool monitors and displays in real-time all file system activity). Use the command prompt with Administrator rights and run the procmon. You will get something like as give below which seems that there is no permission issues.

“04:14:05.2397190 PM”,”SQLAGENT.EXE”,”12245″,”RegQueryValue”,”HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.50.MSSQLSERVER\SQLServerAgent\ErrorLogFile”,”SUCCESS”,”Type: REG_SZ, Length: 84, Data: G:\MSSQL\Log\”

Now to cross verify we have logged in another node and check the SQLAGENT.OUT file registry entry which is different from failing node.

In Working Node the value is I:\MSSQL\Log\SQLAGENT.OUT
In failing Node the value is I:\MSSQL\Log\

Now added the file name SQLAGENT.OUT in the registery.

“I:\MSSQL\Log\SQLAGENT.OUT”

Now the SQL agent service is started without any issue.

WP2Social Auto Publish Powered By : XYZScripts.com