Everything on Always on Availability Groups series

Step By Step configuring Windows 2012 R2 fail over cluster How to setup windows cluster for Always on Availability Groups

AlwaysOn Availability Groups: Step by Step Setup Step by Step procedure to setup Always on Availability Groups

Configure Read only routing for an always on availability group

Always on troubleshooting: 

http://mssqllover.blogspot.in/2017/04/the-connection-to-primary-replica-is.html

Step By Step configring Windows 2012 R2 fail over cluster

One of my client requirement is to install Always on availability groups for SQL databases. You don't require shared storage to setup Always on availability. it can be configured in local disks.
AlwaysOn Availability Groups rely on the Windows Server Failover Cluster for failure detection and management of the Availability Group replicas.

Prerequisites required to install Windows 2012 R2 failover cluster feature.

• Get the operating system installed, patched and configured on all participating nodes
• See either Windows Update or an internal Windows Server Update Services (WSUS) server to get all of the required Windows Updates downloaded and installed
• All the participating servers should be added to active directory
• Domain user account must have administrator rights on the computers that are becoming part of the new cluster and the Create Computer Objects permission on the container where computer accounts are created in the domain.
• Ensure the password not temporary and there is complex password
• Both OS Editions, Versions should be at same level on all participating nodes
• You need to install 3.5.1 or greater on all participating nodes
• Make sure you have a separate NIC's for public and private communication
• Make sure you have free IP for windows cluster

How to configure Windows 2012 R2 Failover cluster

Step 1:

Failover Clustering feature has to be installed onto each cluster node
Open server manager and select Add Roles and Features. Next, Add Roles and Features Wizard and Select the Failover Clustering feature. click Next and install.

Step 2:

Validate your hardware configuration to make sure that it meets the requirements for building a failover cluster. Launch failover cluster manager console and click on the Validate Configuration option, found in the Actions pane. This will cause Windows to launch the Validate a Configuration Wizard.





Step 3:

Testing Options dialog box, make sure that the option Run only tests I select. Click Next.

Step 4:

Exclude add eligible storage cluster check box and click Next.




In the Confirmation dialog box, click Next.

click Next. This will run all the necessary validation tests.

In the Summary dialog box, click Finish to create the Windows Failover Cluster.

Step 5:

The length of time that the tests take to complete varies depending on the number of servers in your cluster and on your hardware's performance.

the test results may result few warnings. However, if you see any Error messages, you need to fix those first prior to creating the Windows Server Failover Cluster.




Step 6:

Launch the Create Cluster Wizard. Click Next and In the Select Servers dialog box, enter the host names of the nodes that you want to add as members of your cluster and click Next.

Step 7:

In the Access Point for Administering the Cluster dialog box, enter the virtual hostname and IP address that you will use to administer the cluster. Click Next




Step 8:

In the Confirmation dialog box, Exclude add eligible storage cluster check box and click Next. This will configure Failover Clustering on both nodes of the cluster.

There is no need to use shared storage to create the Windows Server Failover Cluster that we will use for our Availability Group.

Step 9:

In the Summary dialog box, verify that all the report returns successful.

In below screenshot, AONTESTWFCS01 cluster is successfully setup on SQL1 and SQL2 nodes.

Webinar- SQL Server 2016 - Always Encrypted / Security

Join Webinar on SQL Server 2016 - Always Encrypted / Security. Thu 2/23/2017 from 12 PM to 1 PM

SQL Server has had ways to encrypt data in the past - for example, Transparent Data Encryption (TDE). This technology protects the data at rest and when the database files or backups are compromised. However it is accessible to the database itself and to anyone who can own it and obtain the key/certificate/password (system administrators, hackers, what have you). Always Encrypted is a new feature in SQL Server 2016, which encrypts the data both at rest *and* in motion (and keeps it encrypted in memory). Unlike TDE, as well, Always Encrypted allows you to encrypt only certain columns, rather than the entire database.

Click [HERE] to enter lobby.

AlwaysOn Availability Groups: Step by Step Setup

An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to eight sets of corresponding secondary databases. Secondary databases are not backups. Continue to back up your databases and their transaction logs on a regular basis.

To read overview on always on availability groups click [here]

Prerequisites required to enable SQL Server 2012 AlwaysOn Availability Groups Feature.

• Get the operating system installed, patched and configured on all participating nodes
• See either Windows Update or an internal Windows Server Update Services (WSUS) server to get all of the required Windows Updates downloaded and installed 
• Dedicated domain user account be created for use by the SQL Server service. This should just be a regular or domain account 
• Having separate accounts for SQL Agent service, SSRS, SSIS & SSRS. Having separate account is more secure and resilient, since a problem with one account won’t affect all of the SQL Server Services 
• Ensure the password not temporary and there is complex password
• Both SQL & OS Editions, Versions should be at same level on all participating nodes
• All replicas in your AlwaysOn group must be in the same windows domain. They must be able to communicate with each other.
• Always on availability groups is only supported in Enterprise edition starting from SQL server 2012 ( except SQL 2016 it supports basic availability group in standard edition)
• Recommend to have same collation on all replicas
• SQL Server port must be opened at firewall level for communication between replicas
• Create shared network share on all participating nodes
• You need to install 3.5.1 or greater on all participating nodes
• Make sure your databases are in Full Recovery Mode, not Simple or Bulk Logged
• Databases included in your AlwaysOn group must be user databases. System databases cannot participate in AlwaysOn Availability Groups.
• Read-only databases cannot belong to an AlwaysOn group
• Databases may only belong to one availability group at a time
• Make sure full backups of each of your databases are made prior to installing AlwaysOn
• No cluster shared volume is required for Always on, it can be configured in local disks
• Make sure you have a seperate NIC's for public and private communication
• Additional NIC is required if you want to isolate always on replication traffic to dedicated NIC
• Make sure you have two free IP's each for windows cluster IP and Always on listener IP

AlwaysOn Availability Groups require a Windows Server Failover Cluster, we first need to add the Windows Failover Cluster Feature to all the nodes running the SQL Server instances that we will configure as replicas

To know how to install failover cluster click [here]

We have two node windows failover cluster SQL1 & SQL2 already setup as shown in below screenshot.

Once you have installed failover cluster we can now proceed with enabling the AlwaysOn Availability Groups feature in SQL Server 2012. This needs to be done on all of the SQL Server instances that you will configure as replicas in your Availability Group.

How to Enable SQL Server 2012 AlwaysOn Availability Groups Feature

Default standalone SQL instances installed on nodes SQL1 & SQL2

Step 1:

Open SQL Server Configuration Manager. Double-click the SQLServer (MSSQLSERVER) service to open the Properties dialog box.

In the Properties dialog box, select the AlwaysOn High Availability tab. Check the Enable AlwaysOn Availability Groups check box. This will prompt you to restart the SQL Server service. Click OK

In below screenshot, AONTESTWFCS01 is the windows cluster name. SQL1 and SQL2 are nodes.

Step 2:

Restart the SQL Server service

Create and Configure SQL Server 2012 AlwaysOn Availability Groups

Step 3:

To create and configure a SQL Server 2012 AlwaysOn Availability Group,

Open SQL Server Management Studio. Connect to the SQL Server instance

In Object Exporer, expand the AlwaysOn High Availability folder. Right-click on the Availability Groups folder and select the New Availability Group Wizard… option. This will launch the New Availability Group Wizard.
Step 4:

In the Introduction page, click Next.

In the Specify Availability Group Name page, enter the name of the Availability Group in the Availability group name: field. Click Next.

Step 5:

In the Select Databases page, select the checkbox beside the database that you want to include in your Availability Group. The databases have to be in Full recovery model prior to joining them in the Availability group. Click Next.

In below screenshot, we have selected AGTEST database to Always on availability.

Step 6:

In the Specify Replicas page, under the Replicas tab, click the Add Replicas button and connect to the other SQL Server instances that you joined as nodes in your Windows Server Failover Cluster.

Configure the following options:

• Automatic Failover
• Synchornous commit
• Readable secondary 

In below screenshot, specified replicas SQL1 & SQL2.

 
Step 7:

In the Endpoints tab, verify that the port number value is 5022 and endpoint name Hadr_endpoint. 

Step 8:

In below screenshot, Selected backup preferences as shown in below screenshot.

Step 9:

In the Listener tab, select the Create an availability group listener option. Enter the following details.

Listener DNS name: AGLIS01
Port: 16333

Listener IP Address: 192.168.35.18

Click the Add… button to provide an IP address. In the Add IP Address dialog box, enter your preferred virtual IP address in the IPv4 Address field. Click OK. Click Next.

Step 10:

In the Select Initial Data Synchronization page, select the Full option. Provide a shared folder that is accessible the replicas and that the SQL Server service account used by both replicas has Write permissions to. This is just a temporary file share to store the database backups that will be used to initialize the databases in an Availability group. If you are dealing with large databases, it is recommended that you manually initialize the databases prior to configuring them as your network bandwidth may not be able to accommodate the size of the database backups. Click Next.

Step 11:

In the Validation page, verify that all validation checks return successful results. Click Next.

Step 12:

In the Summary page, verify all configuration settings and click Finish. This will create and configure the AlwaysOn Availability Group and join the databases.

vCenter Server fails to start, Purge and Shrink Vcenter SQL Database

One of my client vCenter Server fails to start as below shown error in event logs.

Could not allocate space for object 'dbo.VPX_EVENT'.'VPXI_EVENT_USERNAME' in database 'VCDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup

VirtualCenter Server runs with a Microsoft SQL Server database in our SQL Server Express instance. VMware VirtualCenter Server service may start and stop immediately.

Cause

The Event Viewer log shows that Vcenter database size reached maximum size i.e. 10 GB. vSphere 5.1 vCenter using Microsoft SQL Server 2008 R2 Express, and the VIM_VCDB database has hit the 10GB limit causing the 'VMware VirtualCenter Server' service to terminate unexpectedly with error Event ID 7031.  

In below screenshot, Database size reaches 10GB.

In below Screenshot, Log file for database is full.

Resolution: How to purge old data in vcenter database in SQL Server.

To purge the data in the VPX_EVENT table:

Step 1:

Connect to Servername\SQL Database and log in with the appropriate credentials.

Read My Complete Article "Here" 

Ganapathi varma Chekuri

Email: gana20m@gmail.com

Linkedin

Upcoming list of webinars

SQL folks, upcoming list of webinars from SQL PASS. 

Webinar title / Registration link / more info	Speaker	Date / Time
"Why Your Data Type Choices Matter" https://attendee.gotowebinar.com/register/4226815545680860675	Andy Yun	2/18/2017 9:00PM~10:30PM EST
"Reports on the Go: SSRS 2016 Mobile Reporting" https://attendee.gotowebinar.com/register/2771711963895711489	Steve Wake	3/18/2017 9:00PM~10:30PM EST
"Initial impressions of SQL Server v.Next Public Preview" https://attendee.gotowebinar.com/register/9173218192702009346	Haidong Ji	4/8/2017 9:00PM~10:30PM EST
"Power BI: Zero to Dashboard in an Hour" https://attendee.gotowebinar.com/register/5055477877562979844	Barbara Raney	5/6/2017 9:00PM~10:30PM EDT

Ganapathi varma Chekuri

Linkedin

Fix SQL Server Commvault Backup Failures After an Upgrade

One of my friend sent an email that backups of a SQL Server 2016 instance are failing suddenly when the backups run through the Commvault backup tool. The error is:

Error Code: [30:325]
Description: Error encountered during backup. Error: [System.IO.FileNotFoundException:Could not load file or assembly 'Microsoft.SqlServer.BatchParserClient, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. Could not load file or assembly 'Microsoft.SqlServer.BatchParserClient, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.]

In below screenshot, backups status fails with error code [30:325] in Commvault console.

Cause

When logged into SQL Server machine and investigated the logs to identify if any recent changes on SQL instance. In C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\summary.txt shows that SQL Server 2008 R2 was upgraded the previous night to SQL Server 2016. Then the backup starts failing. You can also find installed date in add/remove program features in control panel.

Resolution

Read My Complete Article "Here"