Monday, September 28, 2015

Intermittent Network Connectivity Failures in Windows 2012 on VMWare with VMXNET3 Driver

Over the past several weeks we’ve had issues with some servers sporadically dropping off the network and quickly regaining connection. Network connectivity was checked and tests were ran but nothing was found. One commonality among the servers is that each is running in VMWare and use the vmxnet3 driver as the main NIC driver. After reading a couple of articles about potential issues surrounding this driver we opened up perfmon to check for discarded packets and found that it was discarding them at an alarming rate.

 

After reading the following article:

Large packet loss at the guest OS level on the VMXNET3 vNIC in ESXi

we made the prescribed changes and haven’t experienced issues since.

Friday, September 25, 2015

Visual Studio Solution is Missing or Can’t Add Another Project to Solution

If you’ve ever opened up a Visual Studio Project and were left wondering where the solution file is, specifically because you need to add another project to that solution, the answer lies in the IDE options.

By navigating to Tools – Options –Projects and Solutions – General, you’ll find the “Always show solution” check box. After selecting that, the top level solution will appear in the Solution Explorer and you’ll be able to add another project to it.

Thursday, September 24, 2015

Re-add Grouping to Tablix After Deleting it in SSRS

If you’ve ever accidentally deleted the default row grouping on a tablix in SSRS, you have two options to get it back.

  1. Recreate the tablix. (Which isn’t fun if you’ve have several columns or lots of formatting)
  2. Recreate the grouping that’s done by default during the wizard. (You used the wizard, didn’t you?)

Rather than starting from scratch with a new tablix, I going to show how to get the default grouping back up and running.

The steps are as follows:

  1. Right click on the detail line that needs the grouping re-added.
  2. Select “Add Group … Adjacent Above…”
  3. Then select the first column in the detail line as the “Group By”
  4. After the grouping is created, cut and paste each of the columns to the newly create line above it. This will keep the formatting and formulas intact. After all the columns have been moved, you can delete the old detail line.

Thursday, September 3, 2015

Sending Availability Group replication traffic through a dedicated network.

We recently set up a SQL 2012 HADR solution that took advantage of Availability Groups (AG’s). In this instance, we utilized a multi-subnet cluster to allow for the primary replica to live in our primary data center, while having the secondary replica in another data center.

Note: In this setup, only asynchronous replicas are allowed.

On each of the servers in the cluster, we have 3 network connections.

Public: All user traffic will flow through this connection

Private: For internal cluster communication

Replication: All AG traffic will be routed through this

After those dedicated connections are setup, database mirroring endpoints are used to receive connections other instances in the AG. You can read more about database endpoints here. Usually, when the endpoints are created in an AG, the following script is executed without specifying a specific IP to use.

--Replica1
CREATE ENDPOINT Hadr_Endpoint
AS TCP(LISTENER_PORT = 5022)
FOR DATA_MIRRORING(ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

--Replica2
CREATE ENDPOINT Hadr_Endpoint
AS TCP(LISTENER_PORT = 5022)
FOR DATA_MIRRORING(ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

Without specifying an IP that the endpoint is listening on, the listener will accept a connection on any valid IP. But, if we want to make sure our AG and user traffic are segregated, we must specify the IP of the Replication network connection. So the script would look like so:

--Replica1
CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.10.89)) --<-- Your Replication IP here
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

--Replica2
CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.20.89)) --<-- Your Replication IP here
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

You can reference the BOL article for creating an endpoint here

NOTE: Equally important when using the CREATE AVAILABILITY GROUP statement is to specify the replication IP in the ENDPOINT_URL section. By default, when scripting the AG, the FQDN will show in that section.

CREATE AVAILABILITY GROUP MyAG   
FOR       DATABASE MyDB1, MyDB2   
REPLICA ON      
'COMPUTER01' WITH         
(        ENDPOINT_URL = 'TCP://10.10.10.89:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL          ),      
'COMPUTER02' WITH         
(        ENDPOINT_URL = 'TCP://10.10.20.89:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL          );

You can read up on the CREATE AVAILABILITY GROUP statement here.

After this done, and you’ve completed the rest of the AG setup, replication traffic will be routed through your Replication network. A quick and easy test of this is to open up Windows Task Manager and watch the traffic on the three different Ethernet connections.



Note the adapter name for each connection.


To test this open up an SSMS connection to the AG listener and send a large amount of DML statements to a database within the AG. If you setup the endpoint with the default LISTENER_IP = ALL, you’ll most likely see a high amount of send and receive traffic on Public interface. But, if you’ve setup the endpoint to listen on the Replication IP, you’ll see a large amount of receive traffic on the Public interface (DML) and a high amount of send traffic on the Replication interface (sending the changes to the secondary replica).