SQL Server Product Life Cycle

Part of managing the SQL Server environment , is maintaining the latest Service Packs remain within the Product Support  data range.

The Microsoft web site has a search feature to return the latest dates  about: General availability, maintstream support and Service Pack Support End Dates

http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=sql+server+2005&Filter=FilterNO

Nic Teaming

Network card teaming or NIC teaming are networking terms describing methods of combining multiple parallel network connections . The main purposes are 1) increasing throughput beyond a single connection and 2)  redundancy.

 The three main teaming configurations

 Network Fault Tolerance (NFT)

1)  Two – Eight Physical NICS are teamed together into one virtual network adapter.

2)  Only one port (Primary) is used for transmit and receive. The remainder adapters are deemed secondary Ports. They are used in the event of a Primary Port failure.

3) Heartbeats can be received and transmitted by all ports

Transmit Load Balancing with Fault Tolerance

1) All NFT features plus Transmit Load Balancing  (TLB)

2) Two – Eight Physical NICS are teamed together into one virtual network adapter

3) TLB allows   allows load balancing on IP traffic tramsmitted from server

4) Incoming traffic is not load balanced i.e Primary Adapter  manages all incoming traffic

Switch Assisted Load Balancing (SLB) with Fault Tolerance

1)     All NFT and  TLB features plus load balancing incoming traffic

2)     Load balancing incoming and transmit traffic

3)     SLB load balances all traffic regardless of protocol

Related Posts

Speed and Duplex settings

Author: Jack Vamvas (http://www.sqlserver-dba.com)

CPU Count

I document the CPU Count for SQL Server inventory management.

Using a powershell script , iterate through all the SQL Servers and capture the CPU count with the following t-sql.

----Returns the logical cpu count on server 
SELECT cpu_count FROM  sys.dm_os_sys_info

----Ratio of the number of logical or physical cores that are exposed by one physical processor package

SELECT  hyperthread_ratio FROM  sys.dm_os_sys_info

----Physical CPU count 
SELECT cpu_count/hyperthread_ratio as physical_count  FROM sys.dm_os_sys_info

 Related Posts

Affinity Mask on SQL Server 2008 R2

SQL Server – SOS_SCHEDULER_YIELD and how to reduce it

SQL Sp_send_dbmail and bcc

To send BCC and Copy in SQL Sp_send_dbmail,  use this way to send email from a configured Database Mail server.

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'JackVamvas@sqlserver-dba.com',
@copy_recipients ='JackVamvas@sqlserver-dba.com’
@blind_copy_recipients ='tuning@sqlserver-dba.com’
@body= 'Some text for body', 
@subject = 'Some text for subject',
@profile_name = 'MyProfile1';

The email addresses in @recipients go on the To:  line.

To  use   COPY and BCC on sp_send_dbmail , the parameters are :

@copy_recipients ='JackVamvas@sqlserver-dba.com’
@blind_copy_recipients = 'tuning@sqlserver-dba.com'

Related Posts

Set up Database Mail on SQL Server 2005  

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Speed and Duplex settings

Incorrect Speed and Duplex settings can hurt performance.

I've set up a maintenance policy  to maintain the appropriate speeds on the Network Adapters. I've only found this problem on non-fibre networks.
There are a few machines network adapter settings with an "Auto" setting. The machines I've noticed are the 100Mb speed.
By changing the Speed & Duplex setting to 100 Mb full - the problem is solved.

Solution

 

1)  There is the immediate issue - which I solved by manually resetting.The danger of manually resetting , is that I might miss a machine , which is part of the chain.

 

2)   Plan to script script out some way of comparing the network adapter speed with the switch ports speeds. This becomes more complex when you realise that developing a catch-all for every type of network adapter is not straightforward.There are utilities available for Intech based adapters , but if you are using other manufactures network adapters - then some work needs to be done .

I've noticed this is happening because the standard install is from an unprocessed install image. Therefore in a narrow implementation window it is easy to overlook , as it's a configuration setting once the install has occured

The impact of not having the right speed is : 1 day test is taking about 3 days.
I've implemented a few tests across Desktops and Servers - switching back and forth from the different settings. With standardised scripts acceptance test takes about 30 minutes , but setting the inappropriate setting will take about 3 hrs.

The problem is that the network card is auto-sensing incorrectly, therefore a less than optimum speed , which is hurting the network performance . It is not obvious in every application but for very network intense applications .

Be aware - and check speed and duplex settings

Author: Jack Vamvas (http://www.sqlserver-dba.com)