Best Practice Ezine #19 – SQL

Best Practice Ezine.  Computer Performance. Advertise

Guy’s Best Practice & Litmus Tests Ezine #19 – SQL

If you remember, my ezine started out by asking simple questions that you could try on your server. The answers are designed to separate the sheep from the goats, the amateurs from professionals. I call these simple questions Guy’s Litmus Tests.

Contents for Ezine #19

SQL Litmus Tests

When I first see a customer’s server it is hard to know how to pitch the advice, that’s why I develop Guy’s Litmus tests. I was looking at an SQL server recently; my expertise is managing the server rather than designing the databases. Here are some of the questions I asked:

1) Service Account

Which account is used to start the MSSQLServer service?

Amateurs use the Administrator

Professionals create a special account.

Well the site that I was visiting had configured the Administrator account to start the SQL service. I dropped into conversation, ‘Do you get any problems with the SQL service when you change the administrator’s password?’ ‘Nah’ he said ‘We never change the administrator’s password’. O.K. I thought, keep it simple here, this must be a low security company. When I checked the SQL Agent, I just knew that it would not be started. Well, the Agent could not start because no account had been assigned for logon.

2) SQL Agent

How is the SQL Agent configured?

Amateurs – not working

Professional’s assigned a special logon account that never expires and never has to change its password.

I love SQL Agent.  I particularly enjoy configuring Alerts and Jobs to automate backup and other routine tasks.  SQL Agent falls into the category of task where investing half an hour up front that pays back handsomely when you are under stress, or want do something more interesting than manual backups.

 See more about SQL Here

Guy Recommends: The Free IP Address Tracker (IPAT) IP Tracker

Calculating IP Address ranges is a black art, which many network managers solve by creating custom Excel spreadsheets.  IPAT cracks this problem of allocating IP addresses in networks in two ways:

For Mr Organized there is a nifty subnet calculator, you enter the network address and the subnet mask, then IPAT works out the usable addresses and their ranges. 

For Mr Lazy IPAT discovers and then displays the IP addresses of existing computers. Download the Free IP Address Tracker

DHCP (Dynamic Host Control Protocol)

Your first step with DHCP in Windows 2000 and Server 2003, is to Authorize it; to let Active Directory know of DHCP’s existence.  Microsoft say this is stop rogue administrators setting up too many DHCP servers.  Anyway, make sure you Authorize DHCP before you deploy clients like XP.

As ever, a single point of failure is your enemy, so have two DHCP servers for each subnet.  Each server offers the client an IP addresses, from its DHCP scopes.  My tip is to split these scopes 80 / 20 rather than 50:50.   Use 80% of the range for the ‘local’ DHCP server and 20 for the ‘distant’ or spare DHCP server.

DHCP Litmus Tests

1) Scope Options

Professionals say ‘We use 3 or 4 of the scope options, for example, Type 003 – Router, Type 006 – DNS, Type 015 – Domain name’.

Amateurs say ‘What is a scope option?’

2) Audit Logging.

Amateurs say ‘We don’t like DHCP because you cannot tell which user has which IP address.

Professionals say:  We implement Logging, so we have records of which IP address was assigned to which IP address.  So if the boss asks who had on April 2nd we can tell him’   To keep records, all you need to do is right-click the server, properties and then check the ‘Enable DHCP Audit Logging’ box.

See more interesting articles and tips

E 201 AutoHotKey  •Network Experts Config Generator Free Download  • Ezines

  • E 144 Training Advice  •E 117 DSTE 150 Dragon Den  • E 134 DevCon  • E 80 Fun

E 64 Horseless Carriage  •E 19 SQL  •E 13 Urban Myths  • Solarwinds Orion NPM Review