Alias of a SQL Server
-
Was hoping to get some help on setting up our SQL Server with an alias. Currently, we run reports off it and then output the files on a folder structure on the server. For instance, we run SQL Job "Create Employee Retention Report", and it exports a file to "D:\Reports\Employees\EmployeeRetentionReport.txt", which is a drive on the server.
However, when users need to access these reports, they have to connect via the UNC path: "\\ServerName\Employees\EmployeeRetentionReport.txt". Also, when we connect to SSMS, it is "ServerName, 1433". However, we have a new company policy that is going into effect, that all servers have to be rebuilt each year, which also entails a new server name. So users would have to remap all their paths to "\\ServerName_NEW\Employees\....".
Also, we have tons of SSIS packages that reference the server, so we don't want to have to update these each year either.
So we want to create an Alias, so that we do not have to update paths each year for all of these files and connections. However, as we work in a large company, I am unsure how to go about doing this. I know there is something within SQL Configuration that you can set an Alias, but I am not sure how this will propagate throughout a company. I feel like it is something that needs done on a domain controller or something, not on the SQL Server itself, but I must admit (obviously) networking and this type of administration of SQL Server is beyond my skill level and it seems when I search for Alias on the web, all I can find is the SQL Server Configuration method.
So hoping someone can help out. Maybe it isn't a technique using Alias, but something else I can research or perhaps I just have a fundamental lack of understanding on how Alias works.
-
Aliases can get you pretty far once you set up a little automation.
Aliases are stored in the Windows Registry (at least on Windows machines). Once you've created an Alias, you can search the Registry for it and export the entry to a .reg file. That file can be used to import the Alias elsewhere by importing the Registry entry. For your example above, I created an Alias called ServerName that points to 'NewServerName' port 1433. Then I used RegEdit to export the alias to a .reg file, and opened it in Notepad:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo]
"ServerName"="DBMSSOCN,NewServerName,1433"It's just a text file with a ".reg" extension.
You can deploy this alias with a GPO so it downloads and installs automatically at login.
When servers change, update the .reg file and the GPO.
Being just a text file with a .reg extension, it's also easy to write a little PowerShell to read a spreadsheet, CSV, SQL table, etc. and write the .reg file with all the Aliases you need.
Eddie Wuerch
MCM: SQL -
Keep in mind you can use DNS-aliasses instead of (client side) sql server aliasses !
If you rely on kerberos, you will have to register SPN(s) using the FQN of the DNS-alias.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Can you build up a new server ('name_new) and then when ready to migrate, rename the old server to 'name_old', then rename the new server to the existing server name? That's how I do it and nothing needs changed on the Client, and you have the old one around for a bit in case you missed something...an easy rollback plan too.
You'd have to use sp_dropserver/sp_addserver (and restart sql) to change the internal name.
-
One risk with SQL Aliases is if you need to change the alias for any reason. SQL upgrades like SQL 2017 to 2022 using a migration upgrade approach means you get a new instance ID OR migrating your SQL stuff from server A to server B. You have to make sure that after the change you update ALL machines that are using SQL Aliases.
Mind you, you have the same problem with any other method - DNS means you have to update DNS and HOPE the client machines grab the fresh DNS entry not the cached one. Hard-coding the connection string means you have lots of things to update if something goes wrong. Also, if I am not mistaken, DNS aliases will only get you to the machine - if the machine contains multiple instances, the DNS alias doesn't help as much. SQL Aliases can point to the specific instance and port so it's a bit nicer.
You can use GPO's to handle SQL Aliases (as they are just registry entries), but then when you do changes you have to force-push them out to users which can be a pain the butt if the user(s) turn their machines off at the end of the day.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply