SharePoint, Office 365, Azure, and Front end JS geek. – Chicago IL
SharePoint, Office 365, Azure, and Front end JS geek. – Chicago IL

Two Ways to Check SQL Aliases for SharePoint

Today I was working on a server and wanted to confirm the SQL alias settings were correct.   There are two ways to do this and I wanted to compare both techniques.

 

1 – CLICONFG  (Bad Way)

The old stand-by.   Trusty EXE file names that just work.   Win + R + CLICONFG.EXE is a favorite for keyboard shortcut key gurus. 

image

 

To see 32-bit settings run “%SystemDrive%\Windows\sysWOW64\cliconfg.exe”   Yes, not as easy to type.  

But wait a minute.  Both windows look exactly the same!   Whaaaaat??    That’s no good.  In a world of 32-bit only this worked fine, but things change and our tools should too.

 

image

 

2 – SSMS (Good Way)

I install SQL Server Management Studio (SSMS) on all of my SharePoint web front ends (WFE) for support and troubleshooting.  It’s a great way to confirm the WFE can “see” SQL and connect successfully.   If your organization has a separate DBA team which manages SQL, this is even more helpful because you can look at the databases and run queries even without RDP access to the SQL machines.  Checking storage used, last backup time, and permissions are all helpful read-only tasks we can safely do with SSMS.

With SSMS installed, you get a Start Menu icon for “SQL Server Configuration Manager.”  Launch that and you’ll be able to view aliases for both 32-bit and 64-bit drivers.  You can also run the MSC directly from PowerShell, the command line, or Start > Run.

SQL 2008 R2 SQL 2012
SQLServerManager10.msc SQLServerManager11.msc

Both are in one place!  Clearly labeled and easy to read!   Now that’s much better in a modern 32/64 world.

 

image
image
image

So there you have it.  Two different ways to look at your SQL alias settings. 

As much as I used CLICONFG.EXE over the years, today I use the GUI because it’s a helpful reminder to look at both 32 and 64-bit settings all at once.   Consistency = fewer issues = happy admins. 

Smile
  • I just wonder on why anyone needs to see this aliases… I just never realized this even existed.

  • Hi Bruno,

    There are several benefits. I do it for flexibility. For disaster recovery, data center moves, and SQL upgrades we need to tell SharePoint about a new SQL backend server. It’s much easier to install SharePoint with a single alias (i.e. “SPSQL”) and then update that 1 alias per machine (as opposed to updating dozens of configuration points inside of SharePoint).

    Eric VanRoy wrote a great explanation at http://www.skylinetechnologies.com/Blog/Lists/Posts/Post.aspx?ID=55

  • Arsenio Lynn

    For problems restoration, information middle goes, and SQL improvements we need to tell SharePoint about a new SQL after sales hosting server.

  • Dave Wolf

    Recently I’ve encountered a couple of ‘spontaneous’ SQL alias corruptions. Different servers. Fix was to delete the existing (32- and 64-bit aliases) and recreate them. No need to uninstall, then reinstall SSMS.

  • Jeff974

    Hello, I have some issues with sql Alias could you help please ?
    We moved from a monoserver SPSQL to a dedicated SQL, we create an alias with the client.exe 32 and 64 with those settings :
    -With server names
    -Dynamic, tcp enabled.

    The result is :
    -The web applications are working well
    -Creating new services applications or delete existing one is impossible, it says that SQL Timeout or cannot be reach.

    So then I tried setting this :
    -Setting FQDN of each server
    -Setting the port to 1433
    => The alias wasnt working, it was pointing to the old Database server (the one on the same SharePoint Farm).

    Thx by advance

  • Jeff974

    Hello, I have some issues with sql Alias could you help please ?

    We moved from a monoserver SPSQL to a dedicated SQL, so now we have 2 servers (sp + sql).

    We created an alias with the C:windowssyswow64cliconfg.exe 32 and 64 with those settings :
    -With server names, dynamic port
    -Dynamic, tcp not enabled (in the general tab of the tool)

    1/ The result is :
    -The web applications are working well
    -Creating new services applications or delete existing one is impossible, it says that SQL Timeout or cannot be reach.

    2/ So then I tried these settings :
    -Setting FQDN of each server instead of server name
    -Setting the port to 1433

    -Enable TCP

    => The alias wasnt working, it was pointing to the old Database server (the one on the same SharePoint Farm).

    Thx by advance

    MSDN FORUM LINK : https://social.msdn.microsoft.com/Forums/office/en-US/eefeee95-a72f-4f5d-b211-aaede171d7c5/changing-sql-database-cannot-createdelete-service-applications-search-managed-metadata?forum=sharepointadminprevious

  • Johannes Prinz

    Still looking for a powershell way to test the configuration but here is one to set it.
    https://github.com/johannesprinz/Powershell/blob/master/Modules/WinDev/Functions/Set-SQLAlias.ps1
    Note this is the first cut, may expand it to take in server name if required later.

© Copyright 2016
@ SPJeff

Return to Top ▲Return to Top ▲