Sunday, 21 June 2009

How to write your own capacity management tool

Nothing is better that a free tool, except maybe one you've made yourself.
So here is one I made to collect all the drive space and free space on them into a SQL database.

PART ONE
First of all we need to collect the data from each PC, how you could use a manual list of all the servers but when I was writing this list I was feeling lazy to I used the NET VIEW command to create the list for me, then using WMIC commands to query all the PC for space, just take the code below and save it to a bat or cmd file and you can create the report just by double clicking.

for /f "delims=\ " %%i in ('net view ^| findstr "\\"') do @echo %%i >> servers.txt
del c:\reports\SRVSPACE.CSV
FOR /F %%A IN (servers.txt) DO (
WMIC /Node:%%A LogicalDisk Where DriveType="3" Get DeviceID,FileSystem,FreeSpace,Size /Format:csv | MORE /E +2 >> c:\reports\SRVSPACE.CSV
)

del servers.txt


you could collect more details or change what is collected this was just an example, remember that anything that you can collect in CSV format is easy to import into SQL where reporting services can provide charts and reports that can be scheduled to be sent to your inbox if you so wish.

PART TWO
in the first part we showed the example of creating the csv file that we are importing into SQL so now we need a database and a table to store it.

The following script creates the database the table and the SQL job that will run the import, by creating more table and columns you can add more reports and create a powerful tool to monitor your network not just your hard drives.

--Create DATABASE Capacity_DB
USE MASTER
CREATE DATABASE Capacity_DB
go

--create table Capacity
USE Capacity_DB
CREATE TABLE Capacity

--create columns
(Node VARCHAR(40),
Drive VARCHAR(40),
Format VARCHAR(40),
Freespace VARCHAR(40),
TotalSpace VARCHAR(40),
Collection_Date VARCHAR(40))

GO

use Capacity_DB
go
--create procedure that will be late used by SQL job
CREATE PROCEDURE SP_CapacityImport
AS
-- create temp table
CREATE TABLE #cmimport
(Node VARCHAR(40),
Drive VARCHAR(40),
Format VARCHAR(40),
Freespace VARCHAR(40),
TotalSpace VARCHAR(40))

--import from CSV file
BULK
INSERT #cmimport
FROM 'c:\reports\SRVSPACE.CSV'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

--copy data into capacity table
INSERT INTO Capacity (Node, Drive, Format,Freespace, TotalSpace)
SELECT * FROM #cmimport

--clean up
IF OBJECT_ID('tempdb..#cmimport', 'U') IS NOT NULL
DROP TABLE #cmimport

--update missing dates on new imports
update Capacity
set Collection_Date = (current_timestamp)
where Collection_Date is null
GO

-- Create SQL job to trigger procedure
USE [msdb]
GO

/****** Object: Job [capacity_import] ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 06/21/2009 17:20:29 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND

category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'capacity_import',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [step1] Script Date: 06/21/2009 17:20:29 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'use capacity_db
go
exec sp_CapacityImport
go',
@database_name=N'Capacity_DB',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090621,
@active_end_date=99991231,
@active_start_time=160000,
@active_end_time=235959,
@schedule_uid=N'8aa58ff4-a990-4cb0-8e8c-1780a4b258b7'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


Now you have the database and the table plus the import job set to run once a day, if you place the script from part one on a schedule to run once a day as well then you'll have a working import.

The final part is to create a report from this data, you can ether create a job that will create a summery version and email it too you or create a report using SQL reporting services.

If you aren't already experienced with SSRS (SQL Server Reporting Service) then
Steve Joubert's posting should help you out.

Exactly the same can be use to MySQL or Oracle personally I would recommend using MySQL for this as gives you the greatest number of supported platforms however this example I was using Microsoft SQL 2008




Reblog this post [with Zemanta]

Thursday, 11 June 2009

Site A to Site B tunnel

Keeping Site to Site traffic simple has never been simple and keep it secure and at the same time reducing the packets flowing over it is not easy ether.

So what types of traffic will be going from site A to B?
Active Directory traffic and Replication
WINS/DNS
DFS
Microsoft SQL
PPTP

To keep your traffic as simple as you can I would always recommend using a proxy at each end of the site to site VPN however for some traffic like SQL Replication might not be such a good idea because of the delay it can add, but still I would try to resolve the issue with the proxy then work around it.

Now to give you an idea why I would do this have a look at how many open ports you have with Active Directory

RPC endpoint mapper 135/tcp, 135/udp
Network basic input/output system (NetBIOS) name service 137/tcp, 137/udp
NetBIOS datagram service 138/udp
NetBIOS session service 139/tcp
RPC dynamic assignment 1024-65535/tcp
Server message block (SMB) over IP (Microsoft-DS) 445/tcp, 445/udp
Lightweight Directory Access Protocol (LDAP)389/tcp
LDAP ping 389/udp
LDAP over SSL 636/tcp
Global catalog LDAP 3268/tcp
Global catalog LDAP over SSL 3269/tcp
Kerberos 88/tcp, 88/udp
Domain Name Service (DNS) 53/tcp1, 53/udp
Windows Internet Naming Service (WINS) resolution (if required) 1512/tcp, 1512/udp
WINS replication (if required) 42/tcp, 42/udp

As you can imagine this is much harder to trouble shoot and track of then PPTP on tcp 1723
This is the reason why I would suggest setting up proxy at each end of the VPN. That's not to say you can't open up the ports but to keep it secure you'll need to know the source and destination of all packets, and this can be something of an over head on your configuration.

SQL server uses 1433 and 1434 however this can change depending on settings of the server but for the most part is quite easy.

So lets be begin.
First of all we should have a VPN between the sites the one I like best is a VPN Tunnel as this allows you not only to have the VPN but setup the interfaces with all the ACL rules you want.

I'll use a quite well known example I think, from Richard Deal's Complete Cisco VPN Configuration Guide, I found it a nice bit of night time reading.

RouterA Configuration:
RTRA(config)# crypto isakmp policy 10
RTRA(config-isakmp)# encryption aes 128
RTRA(config-isakmp)# hash sha
RTRA(config-isakmp)# authentication pre-share
RTRA(config-isakmp)# group 2
RTRA(config-isakmp)# exit
RTRA(config)# crypto isakmp key cisco123 address 193.1.1.1 255.255.255.255 no-xauth
RTRA(config)# crypto ipsec transform-set RTRtran esp-aes esp-sha-hmac
RTRA(cfg-crypto-trans)# exit
RTRA(config)# crypto ipsec profile VTI
RTRA(ipsec-profile)# set transform-set RTRtran
RTRA(ipsec-profile)# exit
RTRA(config)# interface tunnel 0
RTRA(config-if)# ip address 192.168.3.1 255.255.255.0
RTRA(config-if)# tunnel source 192.1.1.1
RTRA(config-if)# tunnel destination 193.1.1.1
RTRA(config-if)# tunnel mode ipsec ipv4
RTRA(config-if)# tunnel protection ipsec VTI
RTRA(config)# interface Ethernet0/0
RTRA(config-if)# ip address 192.1.1.1 255.255.255.0
RTRA(config-if)# exit
RTRA(config)# interface Ethernet 1/0
RTRA(config-if)# ip address 192.168.1.1 255.255.255.0
RTRA(config-if)# exit
RTRA(config)# ip route 192.168.2.0 255.255.255.0 tunnel0


RouterB Configuration:
RTRB(config)# crypto isakmp policy 10
RTRB(config-isakmp)# encryption aes 128
RTRB(config-isakmp)# hash sha
RTRB(config-isakmp)# authentication pre-share
RTRB(config-isakmp)# group 2
RTRB(config-isakmp)# exit
RTRB(config)# crypto isakmp key cisco123 address 192.1.1.1 255.255.255.255 no-xauth
RTRB(config)# crypto ipsec transform-set RTRtran esp-aes esp-sha-hmac
RTRB(cfg-crypto-trans)# exit
RTRB(config)# crypto ipsec profile VTI
RTRB(ipsec-profile)# set transform-set RTRtran
RTRB(ipsec-profile)# exit
RTRB(config)# interface tunnel 0
RTRB(config-if)# ip address 192.168.3.2 255.255.255.0
RTRB(config-if)# tunnel source 193.1.1.1
RTRB(config-if)# tunnel destination 192.1.1.1
RTRB(config-if)# tunnel mode ipsec ipv4
RTRB(config-if)# tunnel protection ipsec VTI
RTRB(config)# interface Ethernet0/0
RTRB(config-if)# ip address 193.1.1.1 255.255.255.0
RTRB(config-if)# exit
RTRB(config)# interface Ethernet 1/0
RTRB(config-if)# ip address 192.168.2.1 255.255.255.0
RTRB(config-if)# exit
RTRB(config)# ip route 192.168.1.0 255.255.255.0 tunnel0


So once you have your tunnel up and running we can setup the access lists on the tunnel interfaces remember that you must have permitted GRE protocol on the WAN interfaces for this to work.

In this next example we are using a PPTP connection on both of the Active Directory controllers so that only PPTP traffic is needed to flow over the tunnel, the domain controllers are address on the 3rd IP at each site x.x.x.3

access-list 108 permit tcp host 192.168.1.3 host 192.168.2.3 eq 1723

This can also be used by file server with DFS if remote access and routing is setup on both to use PPTP between them or via the proxy.
DFS by default uses a number of ports that I would not recommend opening for security reasons in the same way Active Directory does.

In this final part I've allowed SQL to travel without the PPTP connection between the SQL servers at each site on IP 50 of the range x.x.x.50

access-list 108 permit tcp host 192.168.1.50 host 192.168.2.50 eq 1433
access-list 108 permit tcp host 192.168.1.50 host 192.168.2.50 eq 1434


Now its important to note that if you are using this in a fail over your going to need to allow all clients to connect to SQL and if its no part of the PPTP then you'll have to set the ACL with a larger allowance for sources.

access-list 108 permit tcp 192.168.1.0 0.0.0.255 host 192.168.2.50 eq 1433
access-list 108 permit tcp 192.168.1.0 0.0.0.255 host 192.168.2.50 eq 1434


Another note to this if your going to send the SQL traffic only in the tunnel without PPTP because of the extra delay in response times, secure it by using certificate authority and force encryption on the Server protocols to make it more secure, however this will mean you'll need to permit tcp 445 for the SQL as well.

now your rule are created you can simply apply them to the tunnel interface

interface tunnel 0
access-list 108 out


You should now be done and secure.

Best practice is also to have access-list on the LAN interface to reduce the traffic on the router but this you will need to know more about you network to setup.

Reblog this post [with Zemanta]

Sunday, 7 June 2009

SQL undeletable jobs

It came to my attention a few weeks ago while where implementing an enterprise automation that jobs create from T-SQL script related to maintenance plans sometimes couldn't be deleted, this also applies to some rare times when maintenance plan deleted but the job didn't.

The result is a job that can't be deleted because its is linked to an entry in MSDB where they are still held.

When the maintenance plan had ether been deleted or was not viewable as it had been created by T-SQL, sadly T-SQL doesn't create the XML file needed for the maintenance plan to be seen from SQL management studio.

As a result you can not delete the job without first deleting the links to it in the MSDB these can be found in the following three tables.

sysmaintplan_subplans, sysmaintplan_plans, sysmaintplan_log

these three tables have to be cleaned up before you can delete the job as it is listed in one or more of them, luck for us there is a common id column call PLAN_ID

So first we need to find the plan_id of our job, if you've been using descriptions on your maintenance job this will be easy if not then you might want to open them up and add descriptions as other wise you'll have a lot of plans and no way to identify them.

Querying the three tables will let us see how many it exist in, this takes but a few seconds.

use msdb
select * from sysmaintplan_subplans

select * from sysmaintplan_plans

select * from sysmaintplan_log


with the output we where able to identify the plan_id as it was the only one without a description, and from there could delete the plan_id from the tables like so.


DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID='9AA4A25B-D4B3-466D-8C9E-20A00722F2CA'

DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = '9AA4A25B-D4B3-466D-8C9E-20A00722F2CA'

DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = '9AA4A25B-D4B3-466D-8C9E-20A00722F2CA'


after this was done we where then able to delete the job as there was no relation to it in the MSDB any longer.

Wednesday, 3 June 2009

Router performance

Router performance can be affected by a number of things as there are several different aspects involved.

Resource issues, such as the performance of the CPU and RAM
Router IOS configuration changes
Bandwidth management: Quality of Service (QoS)
Layer 1 network issues: Bad circuits or cables
Errors and failure of the router hardware


Bandwidth management: Quality of Service (QoS)

To resolve performance issues or improve performance, you may need to implement some form of bandwidth and/or traffic management. This is commonly called (QoS) Quality of Service but there are many different types of QoS, and picking the right one for you depends on what your doing but one thing is for sure you should try to reduce traffic to only permitted types, as you don't want high utilization of interfaces.

One quick way to see what the utilization is on your LAN or WAN circuit is to use the show interfaces command and look for the TX/RX Load as well as the five-minute input/output rate. Here are some examples of the show interfaces output that I am referring to:

reliability 255/255, txload 1/255, rxload 1/255

5 minute input rate 0 bits/sec, 0 packets/sec

5 minute output rate 0 bits/sec, 0 packets/sec

I have personally used these to determine what is maxing out a router’s circuit and to see in what direction that traffic is flowing, but if you want to monitor for longer times and get detailed source and destination I would suggest you use IP Accounting
there some tools such as Cisco IP Accounting Fetcher and Net-Sense that will create reports from the information collected by IP accounting

Layer 1 network issues: Bad circuits or cables

Many times, the reason that users are complaining about performance is that there is a Layer 1 (Physical) network issue. For example, there is an issue with an Ethernet LAN cable or a T1 WAN connection. Errors that cause slow performance are especially common with WAN connections that span long distances.

The best way to check to see if your LAN or WAN connections are causing the slow performance you can use "show interfaces summary" command to see if you have dropped packets or errors

Errors and failure of the router hardware

While the show interfaces command might find issues with your connections, those errors could also be caused by your router hardware. For example, you could have a bad HWIC T1 card that is causing slow performance and causing errors to increment in the "show interfaces" output.

If this is a WAN circuit, many times, your carrier will assist you in testing and troubleshooting that circuit.

personally I'm a fan of AdventNet ManageEngine OpUtils this has been a tool I've liked for sometime and works well for having a single interface to many devices as it likely you won't just have cisco hardware.