Share via

BizTalk and SQL Server Alias

  • Article

I recently came across a case in which my customer wanted to move his custom application database to a new database server but did not want to change the name of his database server name. This was because he had a lot of applications deployed in BizTalk which make use of these custom databases and he did not want to change the BizTalk application which pointed to these custom databases.

He wanted to implement SQL server Alias and wanted to check what the supported and possible ways to do it are.

Overall there are 3 methods of making an application connect to a different SQL Server without making the changes to the application itself

1. Client side SQL
Aliases: These aliases reside on the client machine and make sure that any application hosted on that machine is references the <OldServerName>, the connection string is automatically changed to reference the <NewServerName>. This is done at the OS level.

PRO: Easy to implement.

CON: Needs to implemented on each client machine, Will need to make changes to SPN for Kerberos to function.

Below are few links which would help you achieve this.
https://blogs.technet.com/b/meamcs/archive/2013/01/22/creating-a-sql-server-alias-using-the-sql-server-client-network-utility.aspx
https://msdn.microsoft.com/en-us/library/ms190445.aspx

2. DNS Alias: These aliases are made on the DNS server and any time over the entire network, if you are referencing the <OldServerName>, this name will resolve to NewIPAddress instead of OldIPAddress.

PRO: Need to make only one change for the entire network

CON: Might have unexpected effects on other applications, Will need to make changes to SPN for Kerberos to function.

You can check out the below link which talks about all the steps required to create a DNS Alias.

https://blogs.msdn.com/b/dbrowne/archive/2012/05/21/how-to-add-a-hostname-alias-for-a-sql-server-instance.aspx

3. Moving the SQL Server to a new machine and renaming the machine as OldServerName: You can migrate the databases to a new server and then you can remove the <OldServerName> machine from the domain and rename the new server as <OldServerName>

PRO: No need of SPN changes for Kerberos, absolutely no changes apart from this need to be made.

CON: Complicated and multi-tiered steps to implement.

Once you rename the new server as the old server, perform the below steps to change the SQL server name in the system metadata.
https://msdn.microsoft.com/en-us/library/ms143799.aspx

Apart from this, SQL server alias can also come in handy in the below scenario:

Create SQL Alias to communicate with remote SQL Server using Non-Standard Port

When we want to install BizTalk Server with a remote SQL Server using non default port we cannot use “servername\instance, port number” for BizTalk installation, it will give you the following error:

Error Configuration Framework]Feature: [Group] Failed to configure with error message [Exception of type 'System.EnterpriseServices.TransactionProxyException' was thrown.]

Instead you should create a SQL Server Alias on the client machine to connect to SQL Server machine.

An alias can be created on the client machine by running the SQL Server Configuration Manager tool.

  • Under the SQL Server Native Client folder, right click on the “Aliases” item and select the “New Aliases” item.
  • When doing this the “Alias – New” dialog box will be displayed. In the new alias dialog box, you can create an alias where you can specify the instance name and the port number it is using.
    • In the “Alias Name “set the name of alias: “BizTalkSQL”
    • In “Port No” specify the port where Database Engine running is listening: 1433
    • In “Protocol” leave the default value “TCP/IP”
    • In “Server” specify “servername\instance”

 

Refer for details on How to Install BizTalk Server 2010 in a Basic Multi-Computer Environment :
https://social.technet.microsoft.com/wiki/contents/articles/6845.how-to-install-biztalk-server-2010-in-a-basic-multi-computer-environment.aspx

Hope this information helps.

Written by: 
Yakshit Gohel

Reviewed by:
Jainath Ramanathan

Microsoft GTSC India

Comments

  • Anonymous
    August 27, 2013
    So we have a problem, when you use a SQL alias for the Biztalk, the SQL Agent Jobs on the DB server reference the Alias.  We've created the alias on the DB server but the SQL Agent jobs only work when the "Owner" of the job is SA vs. the account that had SA privs when Biztalk was installed and created the original jobs.Any thoughts?