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
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....
