SharePoint and SQL Aliases
Hey there, yes I’m still alive… it’s been a while since my last blog post.
But I want to write about something which I find very interesting.
Using SQL aliases with SharePoint is (at least from my perspective) a best practice when setting up SharePoint environments. It gives you a lot of flexibility and is not that much effort to do. (You can even script it out in PowerShell, or use it from the AutoSPInstaller project on codeplex – special Thanks is going to Todd Klindt for highlighting this to me).
One of my clients has decided to use SQL aliases in a slightly different way, they don’t create an alias for each Database server but they create mostly an dedicated SQL alias for each database they have ( for different service applications, one for all the content databases etc.) this increases the flexibility how databases can be handled but it comes along with some caveats. For example it is recommended to modify your hosts.txt and point the SQL aliases to the proper server IP (otherwise you will face problems for example with SPDiag, also creating some service applications can get a little bit tricky).
Now my client has a lot of SQL aliases and a lot of servers. All the aliases have been created via Powershell but as things are in life an alias gets added another one gets renamed etc. and sometimes manual adjustments are done for several reasons (troubleshooting, to annoy other people etc.)
For that we came up with the idea to create a little script which gets us an small handy report of all the servers and the configured SQL aliases which I’m currently working on (besides my Rockstar career)
Let me describe the thoughts I made before starting implementing the script. I want the script to perform the following actions:
- Execute from one server and gather the information of all servers in the farm
- When getting the servers in the farm ignore the SQL aliases which are mentioned as database servers in the “servers in farm” section of central administration
- Get the x86 and x64 SQL alias configuration and compare them for every server
- compare the SQL alias configuration of all servers with the server you run the script on
- Generate a .txt file for the reference configuration and a .txt file for the collected information of all servers
- Check the hosts file for each server in the farm
- Check if every SQL alias is represented in the hosts file
So now the scope is clear (at least to me) I will start implementing this. I’m not sure if I will be able to publish the script to my blog but if you guys are interested in checking on how certain things have been handled I’m more than happy to start a little discussion with you, just leave a comment or hit me up on twitter @spsoldier