Setting Up a SQL Server Alias with SharePoint 2010 and SharePoint 2013


Using a SQL Server alias, you’ll be able to change the database server in your SharePoint farm.  In addition, setting an alias should really be a best practice for most new farm builds.  This technique is useful when moving or virtualizing your database server, setting up a test environment, or especially when migrating from SQL Server 2008 / 2008 R2 to SQL Server 2012.

Repeat the following steps for all your SharePoint Frontend and Application Servers. Important: Only do this on the SharePoint Servers. Don’t do this on the SQL Server!

The steps we’re going to run include the following:

1. Setup the alias using 32-bit config tool.

2. Setup the (same) alias using 64-bit config tool.

Let’s begin…

1. Run the 32-bit SQL tool.  Run the following application, “C:\windows\system32\cliconfg.exe”

Select the tab “Alias” and click the button “Add”.

image thumb Setting Up a SQL Server Alias with SharePoint Server 2010

Select “TCP/IP” and define the name of your original server (as the alias). Next, fill in the new server name of your database server and select the “Dynamically determine port”-checkbox. After that click “OK”.

Note 1: If you know the port, you will actually want to uncheck the “Dynamically determine port”, and set the fixed value.

Note 2: If your new (or old) server is using an instance, be sure to include that, for example, something like NewServerName\INSTANCENAME

image thumb1 Setting Up a SQL Server Alias with SharePoint Server 2010

Now you have defined your SQL alias name. Click “OK”.

image thumb2 Setting Up a SQL Server Alias with SharePoint Server 2010

Note: It’s recommended that you should setup an alias for the server name itself, as well as it’s fully qualified name – for example, setup an alias for “OriginalServerName”, as well as “OriginalServerName.domain.com”.

2. Run the 64-bit SQL tool. Run the following application, “C:\windows\syswow64\cliconfg.exe”

Using the 64bit tool, repeat all the same steps you did above.

After you’ve completed steps (1) and (2) on each SharePoint Server, you can either create your new farm or startup your existing farm, and it should be working again with your new alias.

Original Source: http://splog.cairo.ag/2011/02/22/using-sql-server-alias-names-with-sharepoint-server-2010/ – Many thanks!

POWERSHELL BABY…

So Wes Kroesbergen reached out and gave me a Powershell script to knock this out for all you powershell loversSmile  "* Keep in mind that it should be run from an elevated PS window in order to be able to write to the registry."

function CreateSQLAlias
{
    Param([Parameter(Position=0,Mandatory=$true)][string] $aliasName,
    [Parameter(Position=1,Mandatory=$true)][string] $serverName)

    # Declare paths for both x86 and x64 environments
    $x86 = "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo"
    $x64 = "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"

    # Prep registry for aliases
    if ((Test-Path -path $x86) -ne $True)
    {
        New-Item $x86
    }
    if ((Test-Path -path $x64) -ne $True)
    {
        New-Item $x64
    }

    $TCPconn = "DBMSSOCN," + $serverName

    # Create the alias for both x86 and x64
    New-ItemProperty -Path $x86 -Name $aliasName -PropertyType String -Value $TCPconn
    New-ItemProperty -Path $x64 -Name $aliasName -PropertyType String -Value $TCPconn
}

Enjoy….Thanks Wes.

 

Incoming search terms:
  • sql alias sharepoint
  • move sp2010 database to SQL 2012
  • edit sql server sharepoint
  • find sql server from alias name
  • sharepoint 2010 sql alias best practice
  • sharepoint alias sql
  • sql alias cliconfg sharepoint instance
  • unable to login after configuring alias name for sharepoint
Advertisements

12 thoughts on “Setting Up a SQL Server Alias with SharePoint 2010 and SharePoint 2013

  1. hi!,I really like your writing so so much! share we be in contact
    more about your article on AOL? I need an expert on this area
    to solve my problem. May be that’s you! Looking
    forward to peer you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s