November 2010 - Posts

Ever since I have been working with Named Sql Instances using static ports, it has been a pain. 

Connection strings need to be recoded to include the port syntax:

  • <ServerName>[\InstanceName],<Port>
  • <IP Address>[\InstanceName],<Port>

This can be a bit of a drag when you have multiple config files on an application server for example. Setting up client side aliases avoids the need to manually recode connection strings. However if you have multiple servers and client machines to manage, this can be a pain, you dont want to manually set up every server on every machine. I was recently searching for a way to transfer these aliases around and was assisted by Joe Stefanelli who informed me the aliases were actually stored in the registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

To transfer these entries to another machine:

Navigate to that location in regedit and use the File -> Export... menu option to export the branch to a .REG file. Then copy that file to the new machine and use File -> Import... to load the keys you just exported into the new machine's registry.

Mark Broadbent (blog | twitter) also gave me a few ideas about how to take this registry file and roll it out via group policy. As Mark informs me these aliases can also be used for DR purposes by repointing the alias at a new server. This is a pretty neat trick I thought

Posted by blakmk

I  was recently invited to a client site where I hadn't been in a couple of months. I could smell the neglect as soon as I examined the server:

  • Error logs ripe with deadlock events
  • Horrific sql in stored procedures
  • Agent jobs failing
  • Database maintenance jobs disabled due to lack of space to perform index rebuilds.
  • The databases had also been shrunk several times leading to excessive fragmentation in the tables indexes.

Contrary to what you thinking, this wasnt a company staffed by morons on a non critical application, it actually had some pretty talented developers (including a database specialist) working and managing a high throughput OLTP system. However, a developer is a developer and the mindset is different to a DBA.

No decent DBA in there right mind would disable Sql Server maintenance jobs and shrink a LIVE database without immediatly rebuilding the indexes. Steps would have been taken to order extra space as it became low. Innefficient stored procedures would have been rejected before they entered the LIVE environment. 

What im trying to say is Dev guys and Ops people have different priorities and while a Dev is happy to churn out as much code as possible an Ops DBA is busy conserving and preserving the revenue that is coming in along with the maximising the investment on server hardware. I also dont think its totally a personality thing, I have quite happily fitted into both roles at different times but rarely within the same company. If you wear a hat, wear it totally....

 

 

 

 

Posted by blakmk | 1 comment(s)
Filed under: ,