Connection Alias

SQL Server : Configuration du client sur toutes machines

Christian Robert [Skweeky] — Mon, 08 Jan 2007 17:47:52 GMT

Pour faire à l'article de Fabrice , je viens de trouver (enfin) l'outil de configuration réseau pour

re: Connection Alias

Robert te Kaat — Thu, 01 Feb 2007 16:38:16 GMT

I'm having issues using System.Transactions together with aliases. If I use an alias, the TransactionScope always promoted the transaction to a distributed transaction (DTC), but when using the actual servername (default instance), it correctly uses local transactions. Can you explain the reason for that?

(I use SQL 2005)

re: Connection Alias

Xinwei Hong — Thu, 15 Feb 2007 21:38:25 GMT

I'm not familar with DTC.

Check this webpage. http://www.vbdotnetheaven.com/UploadFile/mosessaur/TransactionScope12072006232808PM/TransactionScope.aspx

It says:

"If the first connection that you open in a transaction scope is to anything other than a SQL Server 2005 database, the transaction scope promotes the local transaction to a distributed transaction

immediately. This immediate promotion occurs because the resource managers for these other databases do not support automatic promotion of local transactions to distributed transactions."

It seems somehow it does consider a connection to an alias as to a SQL Server 2005 database. That's my guess though.

re: Connection Alias

Simon Millar — Thu, 01 Mar 2007 05:47:11 GMT

We are attempting to use a Content Switch VIP (Virtual IP) for ODBC DSN's pointing to Named SQL Instances in a Cluster. This is so we can fail over to a Cluster located in a Disaster Recovery facility without amending DSN's (or Alias config) on several App Servers.

It actually works fine, as long as we specify the Ports to connect to. It seems the VIP's are incompatible with SQL Browser listening on UDP 1434. Just wondering if anyone else has had same experience - maybe we're missing something?

Scripting and default protocol

YokiXml — Wed, 23 May 2007 16:58:24 GMT

I am deploying a Visual Basic 6.0 application who need a DSN. I will like make the DSN programatically using Windows Scripting Host. The problem tha i find, is tha by scripting i can not select the protocoll, who i wish use. Always use the default protocoll, it is Name Pipes. I will like establish TCP/IP how default protocoll programatically. Could you help me how i can stablish them, without use cliconf.exe.

Thanks for you explanation about the SQL Server use protocoll, in the conection time.

re: Connection Alias

Matt Neerincx (MSFT) — Wed, 23 May 2007 20:32:54 GMT

You can force the protocol by using one of the protocol prefixes in front of the server name.

For for example if the server is machine1, you can say:

Server=tcp:machine1

There are other prefixes, np for named pipes and lpc for local shared memory protocol.

You can also specify the port like so:

Server=tcp:machine1,5555

Connection Alias

Nancy — Fri, 15 Jun 2007 22:10:21 GMT

I´m migrating SQL Server 2000 to SQL Server 2005 and I want to use an alias because there is an application in Visual Basic 6.0 that uses the SQL 2000 database, connected by an ODBC. I want to define an alias in SQL 2005 with the name of the instance in SQL 2000. I have done some test, and I found that is necessary to define the alias in the client too, instaling the SQL Server Native Connectivity. Is it true? is necessary to define the alias in the client?

What I have in mind is to define the alias in the server, and every computer using the VB application could connect to SQL 2005 without making any change in the computer.

Thank you for your help.

Regards,

Nancy

re: Connection Alias

Xinwei Hong — Tue, 19 Jun 2007 00:56:08 GMT

Alias is used by client stack, so you have to add the alias on every client machine. The server does not (or does not need to) know the alias.

I love blog comments - SharePoint and SQL failovers.

SharePoint Hosting and Development — Sun, 29 Jul 2007 21:33:22 GMT

I love blog comments. They keep you honest and earnest. In response to my blog post Database Mirroring

re: Connection Alias

SQLGURU — Wed, 09 Jul 2008 15:37:07 GMT

Hi,

Where the information you enter in client network utility is stored? In the registry? Can I change the server name for an alias programmatically? We have sharepoint 2007 using sql aliases, and the sql backend databases are mirrored. Since, sharepoint is not mirror aware we have a requirement to change the server name to new principle server (in case of failover) and this needs to be done automatically by a service running on sharepoint side when it detects there is a failover on sql mirror? Can somebody answer please..

re: Connection Alias

SQL Protocols — Wed, 16 Jul 2008 22:08:33 GMT

Yes. You can configure alias programmatically. Please see

http://blogs.msdn.com/sql_protocols/archive/2006/04/08/572057.aspx

The alias entry is stored in registry as I mentioned in the post.

Thanks.

Xinwei

re: Connection Alias

Simon — Mon, 15 Dec 2008 14:20:14 GMT

Can you explain a bit more about your statement.

"SQLClient does not read information from registry and thus won't use alias"

What do you mean by SQLClient I assume you are not referring to SNAC.

re: Connection Alias

SQL Protocols — Mon, 15 Dec 2008 20:42:44 GMT

Yes, I meant SQLClient provider, not SNAC. But, my statement was wrong. SQLClient also reads and uses Alias info. I have updated the text. Thanks.

re: Connection Alias

Debashish Shome (DB) — Sat, 26 Sep 2009 08:02:14 GMT

This posting and the thread is excellent. After spending hours and trying different combination suggested here, I was able to get the SQL Enterprise Manager to connect to the SQL server successfully.

Environment that I had:

Windows 2003 Standard Server R2 32 Bit English

Firewall was On in the server

MS SQL 2005 32 Bit

I was using Windows XP client.

The solution:

Finally, the reason it worked is when I added sqlbrowser.exe and sqlservr.exe in the exception list in the Windows Firewall.

I hope this helps. ... DB

Please feel free to contact if you require assistance.