Bandwidth limiting for Comcast EPL (ethernet private line) using a juniper EX2200

So we got ourselves a nifty 50Mb point to point comcast EPL for backup/DR. Specs on a EPL

http://business.comcast.com/enterprise/services/data/ethernet-private-line/EPL_Tech_Specs

So one of the interesting things about it is that it will just discard packets if you go over the 50Mbit/sec limit

So, I need some bandwidth shaping to make this work. I tried all sorts of things. At first if you just plug two computers on both ends I was getting really strange results. I think for a 50mbit connection they prob configure it to be 100Mbit speed and then limit from there. If you plug in gigabit on both sides you get really strange behavior, like 200KB/sec transfer rates.

So on the juniper I want to limit it to a 100Mb connection. To accomplish that log in, type CLI

 configure (set the ports to port-mode access otherwise your switch is a brick)

edit interfaces

edit ge-0/0/0

edit ether-options

set no-auto-negotiation

set link-mode full-duplex

set speed 100m

Ok, so that gets the speed set right, but now we need to shape the traffic.

so get back to the root level

edit class-of-service

edit interfaces

edit ge-0/0/0

set shaping-rate 50m

commit

 

 

Using a WAN emulator inside vmware ESXi

So I needed to stress test our WMS system, apparently there’s a hard to replicate bug when the network goes screwy so I wanted to see if I could cause it. At first I ran across dummycloud… but didn’t get very far with it. Next, I thought I would try wanem since I had used it before on a physical box. So I downloaded the wanem virtual appliance http://wanem.sourceforge.net/ and then used vmware converter to bring it into vcenter (since it was throwing a A SparseVer2BackingInfo disk is found error)

Converter sort of got it working, but they way they packaged the vm is basically a simple vm that boots off an .iso so I set it to boot off the .iso.

So I went with the one nic configuration. I set it to 10.55.55.56, subnet 255.255.0.0 and gateway 10.55.55.56 (looks like you must set a gateway!,even if bogus)

Then I did my route adds (completely wrong over and over again) on PC1 you want to use a route add to set the route how to get to PC2 and vice versa i.e.

PC1 – route add PC2addy mask 255.255.255.255 10.55.55.56 (your wanem addy)

PC2 – route add PC1addy mask 255.255.255.255 10.55.55.56 (your wanem addy)

Then your tracert will actually work!

Another lame problem, the resolution of my VM was too small so the settings bar wouldn’t show i.e. Basic mode etc… when I upped the resolution, problem fixed!

A good post on this is here http://vninja.net/network/using-the-wanem-wan-emulator-virtual-appliance/

Establishing secure connection RDP takes for ever

This was a new one, we put in some sonicwalls and so by default no one would have access to the internetz until they authenticated. The unintended consequence is a slow RDP connection because it can’t verify the certs

Solution is http://codermike.blogspot.com/2011/05/delayed-rdp-connections-in-windows-2008.html

i.e.

 

You can also disable NLA or CredSSP in the 6.1.x client by creating a .rdp file and adding the following property:
enablecredsspsupport:i:0
Setting the following group policy fixed the issue:

Computer Configuration
Policies
  Admin Templates
   System
    Internet Communication Management
     Internet Communication Settings

Set the following setting to Enabled: Turn off Automatic Root Certificates Update 

sp_repldone/sp_replcounters solution!

Here is the text

From http://mattslocumsql.blogspot.com/2012/04/replication-troubleshooting-how-to-deal.html

Replication Troubleshooting – How to deal with out of sync publications

Transactional Replication and nasty errors that cause out of sync publications.

The other day we had an issue on our distributor that caused deadlocks on the Distribution database.  Several of the Log Reader Agents suffered fatal errors due to being chosen as the deadlock victim.  This caused the following error to occur:

  • The process could not execute ‘sp_repldone/sp_replcounters’ on ‘MyPublisherServer’

When I drilled in to view the detail, I found this error:

  • The specified LSN (%value) for repldone log scan occurs before the current start of replication in the log (%newervalue)

After much searching on the error, I came across several forum posts that indicated I was pretty well up a creek.  I then found this post on SQLServerCentral.  Hilary Cotter’s response was the most beneficial for devising a recovery plan and Stephen Cassady’s response helped me refine that plan.

Hilary Cotter (Blog) is an expert when it comes to SQL replication.  He certainly knows his stuff!

The Recovery Plan
Recovering from this issue involves several steps.

For small databases or publications where the snapshot to reinitialize the publication will be small and push quickly, it’s simplest and best to just reinitialize the entire publication and generate/push a new snapshot.

For larger publications (my publication contained almost 1,000 tables) and situations where pushing the snapshot will take an inordinate amount of time (24+ hours in my case) the following process can be used to skip the missing transactions and identify the tables that are now out of sync:

  • Recover the Log Reader Agent by telling it to skip the missing transactions
  • Recover the Distribution Agent by configuring it to ignore data consistency issues
  • Validate the publication to determine which tables are out of sync
  • Drop and republish out of sync tables


Log Reader Agent Recovery
The simplest way to recover the Log Reader Agent is to run the following command against the published database:

  • sp_replrestart
This effectively tells SQL to restart replication NOW, thus ignoring all transactions that have occurred between the time of the failure and the time you run the command.  The longer you wait to run this command, the more activity in the database that gets ignored, which likely results in more tables that fall out of sync.
Distribution Agent Recovery

Now that the Log Reader Agent is capturing transactions for replication, the Distribution Agent will likely get upset because there are transactions missing.  I specifically received the following error:

  • The row was not found at the Subscriber when applying the replicated command

This error causes the Distribution Agent to fail, but there is a system profile for the Distribution Agent that you can select to bypass the data consistency errors.

  • Launch Replication Monitor
  • In the left-hand column
    • Expand the DB server that contains the published database
    • Select the Publication
  • In the right-hand pane
    • Double-click the Subscription
  • In the Subscription window
    • Go to the Action menu and select Agent Profile
    • Select the profile: Continue on data consistency errors. and click OK
      • Be sure to note which profile was selected before changing it so that you can select the appropriate option once recovery is complete
  • If the Distribution Agent is currently running (it’s likely in a fail/retry loop), you’ll need to:
    • Go to the Action menu and select Stop Distribution Agent
    • Go to the Action menu and select Start Distribution Agent
  • If there is more than one subscription, repeat these steps for any additional subscriptions


Subscription Validation
Validating the Subscription(s) is a fairly straightforward task.
  • Launch Replication Monitor
  • In the left-hand column of Replication Monitor
    • Expand the DB server that contains the published database
    • Right-click the Publication and select Validate Subscriptions…
    • Verify Validate all SQL Server Subscriptions is selected
    • Click the Validation Options… button and verify the validation options – I recommend selecting the following options:
      • Compute a fast row count: if differences are found, compute an actual row count
      • Compare checksums to verify row data (this process can take a long time)
    • Once you are satisfied with the validation options, click OK and then click OK to actually queue up the validation process
      • Please note: for large databases, this process may take a while (and the Validate Subscriptions window may appear asNot Responding)
For my publications (~1,000 tables and DB was ~100GB) the validation process took about 20 minutes, but individual results will vary.
If you wish to monitor the validation progress
  • In the right-hand pane of Replication Monitor
    • Double-click the Subscription
  • In the Subscription window:
    • Go to the Action menu and select Auto Refresh


Identify out of sync tables
I created the following script that will return the tables that failed validation:

— This script will return out of sync tables after a Subscription validation has been performed
— Set the isolation level to prevent any blocking/locking
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
mda.publication [PublicationName],
mdh.start_time [SessionStartTime],
mdh.comments [Comments]

FROM distribution.dbo.MSdistribution_agents mda
JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id

— Update Publication name as appropriate
WHERE mda.publication = ‘My Publication’
AND mdh.comments LIKE ‘%might be out of%’
— This next line restricts results to the past 24 hours.
AND mdh.start_time > (GETDATE() – 1)
— Alternatively, you could specify a specific date/time: AND mdh.start_time > ‘2012-04-25 10:30’
— View most recent results first
ORDER BY mdh.start_time DESC

The Comments column will contain the following message if a table is out of sync:
  • Table ‘MyTable’ might be out of synchronization.  Rowcounts (actual: %value, expected: %value).  Checksum values  (actual: -%value, expected: -%value).
Make a list of all tables that are returned by the aforementioned script.
Now the determination needs to be made as to the level of impact.
  • The Reinitialize All Subscriptions option should be used if the following is true:
    • Large number of tables affected (majority of published tables)
    • Unaffected tables are small in size (if the snapshot for the unaffected tables is going to be very small, it’s much easier to just reinitialize everything)
  • Dropping and re-adding individual tables should be used if the following is true:
    • The number of tables affected is far less than the total number of tables
    • The tables that are unaffected are very large in size and will cause significant latency when pushing the snapshot
The latter was the case in my scenario (about 100 out of 1,000 tables were out of sync, and the ~900 tables that were in sync included some very large tables).
Reinitialize All Subscriptions
Follow this process if the determination has been made to use the Reinitialize All Subscriptionsoption:

  • In the left-hand column of Replication Monitor
    • Expand the DB server that contains the published database
    • Right-click the Publication and select Reinitialize All Subscriptions…
    • Verify Use a new snapshot is selected
    • Verify Generate the new snapshot now is NOT selected
    • Click the Mark For Reinitialization button
      • Please note: for large databases, this process may take a while (and the Replication Monitor window may appear as Not Responding)
  • In the right-hand pane of Replication Monitor
    • Select the Agents tab (in SQL 2005 select the Warnings and Agents tab)
    • Right click the Snapshot Agent and select Start Agent
      • The reason for performing this manually is that sometimes when you select the Generate the new snapshot now option, it kicks off the Snapshot Agent before the reinitialization is complete which causes blocking, deadlocks and major performance issues.

Recover out of sync tables
If the determination has been made to recover the individual tables, use the list of tables generated from the validation process and follow this process:

  • In the left-hand column of Replication Monitor
    • Expand the DB server that contains the published database
    • Right-click the Publication and select Properties
    • Select the Articles page in the left-hand column
    • Once the center page has populated, expand each table published to determine if the table is filtered (i.e. not all columns in the table are published).
      • If tables are filtered, make a note of the columns that are not pushed for each table
    • Once review of the tables is complete, click Cancel
      • If you click OK after expanding tables, it will invalidate the entire snapshot and you will end up reinitializing all articles in the publication
    • Right-click the Publication and select Properties
    • Select the Articles page in the left-hand column
    • Clear the check boxes for all out of sync tables and click OK
    • Right-click the Publication and select Properties
    • Select the Articles page in the left-hand column
    • Select the affected tables in the center pane
      • If any tables were not completely replicated, be sure to reference your notes regarding which columns are replicated
    • Click OK when table selection is complete
      • Note: If you receive an error that the entire snapshot will be invalidated, close the Publication Properties window and try adding in a few tables at a time until all tables are selected.
    • In the right-hand pane of Replication Monitor
      • Select the Agents tab (in SQL 2005 select the Warnings and Agents tab)
      • Right click the Snapshot Agent and select Start Agent
    • Double-click the Subscription
    • Go to the Action menu and select Auto Refresh
 
Final cleanup
Once the snapshot has been delivered and replication has caught up on all queued transactions, perform the following to return replication to a normally running state.
    • In the left-hand column of Replication Monitor
      • Expand the DB server that contains the published database
      • Select the Publication
    • In the right-hand pane of Replication Monitor
      • Double-click the Subscription
    • In the Subscription window
      • Go to the Action menu and select Agent Profile
      • Select the profile that was configured before you changed it (if unsure, the Default agent profile is typically the default) and click OK
    • If there is more than one subscription, repeat these steps for any additional subscriptions

I hope this helps if you run into the same situation.  I would like to especially thank Hilary Cotter forsharing his knowledge with the community as his forum and blog posts really helped me resolve the issue.

our #Qlikview 11 upgrade

So I had the fun task of upgrading our server from 11 to 11.2. Usually this is pretty easy next, next, done. (Qlikview has great compatibility across version, they are top notch) this time was a little different because I wanted to upgrade and change over to a new box. (An IBM 3690 X5 196 GB of RAM!) so I messed around a lot and finally found out how to transfer the tasks. This was my step by step. 

Pre-steps. Make screen shots of all the config on the old box, copy over private and public folders to new qlikview server (I should have used robocopy to preserve the file permissions! I did not and of course that means until a refresh some of qvw’s had incorrect permissions (because qlikview will just publish anything in the public folder!). Make a copy of the QVPR folder located @  C:\ProgramData\QlikTech\ManagementService\QVPR (this is the magic, this folder has all your tasks)

Unplug the old qlikview box from the network (can plug it back in if things go south)

Change IP and rename new box to the old box’s name. (reboot, of course)

Install Qlikview, turn on the management service so that the QVPR folder will be created and then overwrite it with my QVPR from the old server. Reboot

Go to each screen and put in my settings. Tada

scratch my head why admins can’t get to admin console, google it, remember I need to add the to the local Qlikview admin group.