Most SQL Server applications access objects in the same database, but once in a while you need to step outside the database to another database on the same server or on another SQL Server instance. In either scenario, you can utilize aliased names of database servers and objects in other databases, instead of using the actual names. This article will demonstrate different options for using aliases in SQL Server. I'll show how aliases can be beneficial to reduce the number of changes you need to make in your code or configuration files when the physical location of your distributed objects needs to be changed.
First, let's look at the server-level alias. A SQL Server alias is a mechanism that allows you to create an aliased name for a SQL Server instance. You can think of server aliasing as a mapping between the aliased name and the actual instance name or the IP address of the SQL Server instance.
In addition to the names, you can also define the port number and the network protocol. Database connections that use the aliased name will be connected to the mapped server instance using the network protocol specified in the alias definition. Figure 1 shows you a sample alias – I am aliasing the IP address of a SQL Server instance to the name Inventory, plus specifying the port number 6379.
Figure 1: Sample alias of the IP address of a SQL Server instance. (Click on image for enlarged view.)
Server aliases can be very beneficial in many different scenarios:
- If you have a distributed environment with multiple servers that reference each other using Linked Servers, you can define your linked servers using aliases rather than the actual names. If, in the future, you need to move databases from one server to another, you will not have to modify linked server names in your T-SQL code, just modify where the alias is pointing to.
- You can alias an IP address to a friendly name.
- You can enforce the Named Pipes connection protocol instead of the default TCP/IP. This is useful if you want to connect using Windows Authentication to a SQL Server in another domain or over the Internet using VPN. If the TCP/IP connection gives you the "Cannot generate SSPI context" error, you might be successful by creating an alias and specifying the Named Pipes protocol.
- Quickly redirect application connections from the primary to the standby SQL Server in your high-availability scenarios. If your primary server is down, you will not have to modify all connection strings in all applications. Instead, all you have to do is alias the name of the primary server to point to the secondary server. If you need to go back in the other direction, you can delete the alias or point the server to itself.
Creating a server alias depends on what client tools are installed on the computer. If you have SQL Server 2000 client tools, use SQL Client Network Utility and select the Alias tab. If you have SQL Server 2005 tools, open SQL Server Configuration Manager and expand the SQL Native Client Configuration node to find the node for Aliases. Most application servers don't have SQL Server tools installed, but luckily the client network utility is also included in the MDAC pack install. You can run it from DOS by executing "cliconfg" from the command prompt.
Synonyms in SQL Server 2005
Now let's discuss aliasing of database objects using the new feature in SQL Server 2005 called Synonyms. A synonym is a database level object that allows
you to define an alternate name for another database object. The aliased object can reside in the same database, in another database on the same server, or even on another server. You can create synonyms for these database objects: tables, views, stored procedures and user-defined functions.
T-SQL has two commands for working with synonyms – CREATE SYNONYN and DROP SYNONYM. For whatever reason, there is no ALTER equivalent so you have to drop and recreate the synonym if you need to make a change. Figure 2 shows how to create a synonym in the AdventureWorks database for a table in the AdventureWorksDW database:
Figure 2: Creating a synonym in AdventureWorks. (Click on image for enlarged view.)
The following T-SQL creates the same synonym:
CREATE SYNONYM [dbo].[DimProduct]
FOR
[AdventureWorksDW].[dbo].[DimProduct]
If you now execute "SELECT * FROM DimProduct" in the AdventureWorks database, SQL Server returns data from the AdventureWorksDW.dboDimProduct table.
Creating database aliases provides the following advantages:
- You don't have to use three-part names when referencing objects in another database on the same server or four-part names for objects on another server. Instead, you reference them as if they resided in the same database.
- If the location of the object changes, you can just create a new alias or modify the existing one instead of changing the code in your application. However, keep in mind that when you move a table to another server, creating a synonym will not help you avoid dealing with the idiosyncrasies and restrictions of using linked servers. For example, you will have to make sure that DTC is correctly configured and running. While you may not need to modify your object names, you still should thoroughly test your distributed application and make sure that cross-server data retrievals and updates are still working.
- You can create synonyms in the same database to provide backward compatibility for older applications when you drop or rename objects.
SQL Server synonyms are loosely bound to the referenced objects. This means you can delete aliases without getting any notification that other database objects are referencing it. Also, keep in mind that synonym chaining is not allowed. If MyTable2 is a synonym for MyTable, you cannot create MyTable3 as a synonym for MyTable2.
You have different options for referencing database servers and database objects by aliased names. When the physical structure of your distributed environment needs to be changed, use aliasing to minimize changes in your code and application configurations.
| ABOUT THE AUTHOR: |
|
|
Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He regularly contributes SQL Server articles to Visual Studio Magazine, SQL Server Magazine and other technical publications and presents at user groups and conferences in U.S. and Canada. He is an active member and volunteer for Professional Association for SQL Server. Roman also serves as Tech Chair for the SQL Server track at the annual DevTeach conferences in Canada and is president of the Vermont SQL Server User Group.
|
|
