ptg 2244 CHAPTER 54 Managing Linked and Remote Servers partitioned views that can pull data together from multiple servers into a single view. For end-user queries that use these views, it appears as if the data is coming from a single local table. For more information on distributed partitioned views, see Chapter 27, “Creating and Managing Views.” This chapter provides an overview of linked servers in SQL Server 2008 along with a brief discussion of remote servers. As mentioned earlier, remote servers and RPCs are legacy features that are still supported for backward compatibility. In other words, you can still set up and use remote servers and RPCs, but linked servers are much easier to set up and provide greater functionality. Keep in mind that Microsoft is also telling folks to convert any remote server implementations they currently have to linked server implementations. Deprecating remote servers was also announced in SQL Server 2005; however, due to the outcry from many customers, it was not dropped in SQL Server 2008. You can likely count on it not surviving another major release, though. What’s New in Managing Linked and Remote Servers With SQL Server 2008, there are no significant changes to the linked servers’ capabilities, and there are virtually no changes in the remote servers area. Of the few changes for linked servers, the following are the most significant: . Remote servers are being deprecated, and this means system stored procedures such as sp_addserver, sp_remoteoption, sp_helpremotelogin, and sp_addremotelogin will go away. . System variable @@REMSERVER and server-wide settings such as SET REMOTE_PROC_TRANSACTIONS will also go away. . The system stored procedure was introduced, such as sp_setnetname to associate a remote or linked server name with the network name of the server that is remote (not local). . More DATA providers are now certified with Microsoft. This includes flat files, DB2, Informix, and Oracle providers used with linked servers. . A bit more tightening has been done on the login/account delegation model that linked servers utilize. . For remote servers, RPCs are disabled, by default, with SQL Server 2005 and SQL Server 2008. This greatly enhances the default security of your SQL Server out of the box. Managing Remote Servers Remember that remote servers are supported in SQL Server 2008 for backward compatibil- ity only. (Remote servers are being deprecated!) By definition, a remote server is a server you access as part of a client process without opening a separate, distinct, and additional direct client connection. SQL Server can manage the communication between servers using Remote Procedure Calls (RPCs). Essentially, the “local” SQL Server to which the ptg 2245 Managing Remote Servers 54 Remote Server [SQL08DE02] SQL Server 2008 Local Server [SQL08DE01] Client SQL Server 2008 Results Connection Results RPCs FIGURE 54.1 The remote server is accessed through the local server. The client has to main- tain only a single connection to the local server. client is connected opens another connection to the “remote” server and submits a remote stored procedure request to the remote server. Execution results are passed back to the local server, and they are then passed back to the originating client application (see Figure 54.1). You call a remote procedure the same way you call a local procedure; the only difference is that you need to fully qualify the name of the procedure with the name of the server. This fully qualified procedure name includes the server name node, database/catalog name node, database owner node, and remote stored procedure name itself. The local SQL Server recognizes this as a remote procedure, establishes the connection to the remote server, and executes the stored procedure (remotely). Following is the syntax for a fully qualified remote procedure call execution: EXECUTE remote_server_name.db_name.owner_name.procedure_name Here’s an example: EXECUTE [DBARCH-LT2\SQL08DE02].[UnleashedRemoteDB].[dbo].[Top10Customers] Remote servers are more limited in functionality and a bit more time-consuming to set up than linked servers. The following are the basic steps involved in setting up remote servers: 1. Define the local and remote servers on both servers. 2. Configure each server for remote access. 3. Create logins to be used with the remote servers. 4. Create the login mappings from the local server to the remote server. 5. Grant execute permissions for the remote login. If you are connecting between multiple SQL Server 2008, SQL Server 2005, SQL Server 2000, or SQL Server 7.0 servers, it is best to set them up as linked servers and not just ptg 2246 CHAPTER 54 Managing Linked and Remote Servers remote servers. However, if you need to execute Remote Procedure Calls only or are on a pre-SQL Server 7.0 server, you need to set up remote servers as described here. Before we look at an example of a local server connecting to a remote server, let’s first set up a database, create a sample table, and create a stored procedure to execute on the remote server. You can grab the CustomersPlusSQLTable.sql SQL script for this purpose from the CD for this book. The CustomersPlusSQLTable.sql script contains a create database statement that creates a database named UnleashedRemoteDB, creates a table named CustomersPlus in this database, and populates the table with about 89 rows of test data. You should go ahead and grab the script now and execute it on the target remote server ( SQL08DE02 in this example). You need to edit the create database statement ( FILENAME parameter) for your own environment. While you are executing this script, go ahead and grab the three other scripts you will also need to complete this remote server section: LocalServerSQL.sql, RemoteServerSQL.sql, and RPCexecution.sql. Remote Server Setup You can assume that the local server is called SQL08DE01 and the remote server is called SQL08DE02 (as shown in Figure 54.1). First, you need to use sp_addserver to add the remote server name to the system table in the master database if it’s not defined already. But first, let’s see what servers are already defined at the local server. To do so, you run sp_helpserver from the local server (SQL08DE01). This provides you with the complete list of local and remote servers known to this server: EXECUTE sp_helpserver go name network_name status id collation_name connect_timeout query_timeout DBARCH-LT2\SQL08DE01 DBARCH-LT2\SQL08DE01 rpc,rpc out,use remote collation 0 NULL 0 0 You can also see the same information by doing a simple SELECT against the sys.servers system view: SELECT * FROM sys.servers Generally, you don’t need to execute sp_addserver for the local server. This is usually taken care of during SQL Server installation. The local server has an ID of 0. If you need to add the entry for the local server, you can specify the local flag as the second argument: exec sp_addserver [DBARCH-LT2\SQL08DE01], local You need to execute sp_addserver for each of the remote servers you will access from the local server. The SQL script LocalServerSQL.sql that you just got from the CD contains ptg 2247 Managing Remote Servers 54 these commands. For example, on the local server (SQL08DE01), you execute the following command to add SQL08DE02: EXECUTE sp_addserver [DBARCH-LT2\SQL08DE02] Then, to see this new entry, you again execute sp_helpserver, as follows: EXECUTE sp_helpserver go name network_name status id collation_name connect_timeout query_timeout DBARCH-LT2\SQL08DE01 DBARCH-LT2\SQL08DE01 rpc,rpc out,use remote collation 0 NULL 0 0 DBARCH-LT2\SQL08DE02 DBARCH-LT2\SQL08DE02 rpc,rpc out,use remote collation 1 NULL 0 0 You now see the newly added remote server entry (with an ID of 1 in this example). To drop a remote server entry, you need to execute sp_dropserver. For example, on the local server ( SQL08DE01), you execute the following command to drop the SQL08DE02 remote server entry: EXECUTE sp_dropserver [DBARCH-LT2\SQL08DE02] If the local server is a 7.0 or later version of SQL Server, you can add the remote servers by using sp_addlinkedserver: EXECUTE sp_addlinkedserver [DBARCH-LT2\SQL08DE02] This command sets up the server for remote stored procedure execution and for direct data access. (You learn more on this later in this chapter, in the section “Linked Servers.”) Now, on the remote server ( [DBARCH-LT2\SQL08DE02]), you need to define the local server ( [DBARCH-LT2\SQL08DE01]) that will be connecting to it. The SQL script RemoteServerSQL.sql that you just got from the CD contains these commands: EXECUTE sp_addserver [DBARCH-LT2\SQL08DE01] You also need to verify that each server (both the local and remote servers) allows remote connections. This is the SQL Server 2008 default but can easily be verified by looking at the properties for each server from SQL Server Management Studio. By default, remote access is automatically enabled during setup to support replication. Figure 54.2 shows the current configured values of the remote server connection entry, indicating that remote connections to this server are allowed (checked) for the DBARCH-LT2\SQL08DE02 remote SQL Server. You need to double-check both servers (local and remote). ptg 2248 CHAPTER 54 Managing Linked and Remote Servers FIGURE 54.2 Allowing SQL Server remote connections (this check box has been circled). You can also configure this remote connection access by using sp_configure. The proper syntax is as follows: EXECUTE sp_configure ‘remote access’, 1 reconfigure After enabling remote access, you need to shut down and restart each server. Now you need to follow the basic rule of allowing only a named SQL login the capability to execute a remote stored procedure. In this way, you can tightly control the permissions and execution of that stored procedure from wherever the remote execution request origi- nates from. You start by creating the logins you want to use to connect to the local SQL Server ( SQL08DE01 in this example) and the same login name on the remote SQL Server ( SQL08DE02 in this example). You can do this by using Microsoft SQL Server Management Studio (new login) or by using a CREATE LOGIN SQL command, as follows (on the local server, which is SQL08DE01 in this example): FROM THE LOCAL SERVER ONLY CREATE LOGIN ForRPC WITH PASSWORD = ‘password’, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_DATABASE = master go This SQL Server login will connect from the client application. Note that your environ- ment might want to enforce various password policies, conventions, and expiration dates. Next, you create the corresponding SQL login on the remote server (and make that login a user in the database where the remote procedure is located). Again, this can be done by ptg 2249 Managing Remote Servers 54 using Microsoft SQL Server Management Studio (new login) on the remote server ( SQL08DE02 in this example) or by using the CREATE LOGIN and CREATE USER SQL commands as follows: FROM THE REMOTE SERVER ONLY CREATE LOGIN ForRPC WITH PASSWORD = ‘password’, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_DATABASE = UnleashedRemoteDB go USE [UnleashedRemoteDB] GO CREATE USER [ForRPC] FOR LOGIN [ForRPC] GO Notice that you also identify the default database for this remote login to be the database created earlier in this example ( UnleashedRemoteDB test database). You also make the login a user within that database. Now you must set up login mappings on the remote server and possibly on the local server. Basically, remote server login mappings must be set up on the remote server to map the incoming login for an RPC connection from a specified server to a local login (on the remote server). In other words, you need to define how to map the logins from the server making the remote procedure request ( SQL08DE01) to the environment on the server receiving the request ( SQL08DE02). Also, the trusted option of sp_remoteoption is not supported in SQL Server 2005 or SQL Server 2008. This change was made to close a huge security hole in prior SQL Server versions. Although you are technically setting things up on the remote server ( SQL08DE02), when you are doing things on the remote server, it is typically referred to as the local server, and the local server ( SQL08DE01) is treated as the remote server. It’s easiest to think about this situation from the point of view of where you are standing (at the local server versus the remote server). Then it will make a lot more sense. Following is the syntax for the sp_addremotelogin command: EXECUTE sp_addremotelogin remote_server_name [, local_login_name [, remote_login_name]] For example, on the remote server (SQL08DE02), you execute the following command to map the newly created login on SQL08DE01 to the same login on SQL08DE02: EXECUTE sp_addremotelogin [DBARCH-LT2\SQL08DE01], ForRPC, ForRPC This is the simplest mapping method. It presumes that the logins are the same on both servers, and it maps login to login. To see the complete list of resulting mappings, you simply execute sp_helpremotelogin: EXECUTE sp_helpremotelogin go ptg 2250 CHAPTER 54 Managing Linked and Remote Servers server local_user_name remote_user_name options DBARCH-LT2\SQL08DE01 ForRPC ForRPC TIP If users from the remote server need access on your server, don’t forget to add them with the CREATE LOGIN statement first. The login to which you map the remote logins determines the permissions the remote users will have on the local server. If you want to restrict the procedures that the remote users can execute, you need to be sure to set the appropriate permissions on the procedure for the login to which they are mapping. To set execute permissions for the RPC named Top10Customers to the SQL login of ForRPC, you use the following: GRANT EXECUTE ON [UnleashedRemoteDB].[dbo].[Top10Customers] TO ForRPC go That’s it! You are now ready to execute an RPC via the local server as soon as you connect to the local server with the just-created ForRPC SQL login credentials. The SQL script name RPCexecution.sql contains the remote stored procedure execution statement. You simply log in to the local server ( SQL08DE01 in this example) using the ForRPC SQL login and execute the fully qualified remote stored procedure as follows: FROM LOCAL SERVER – SQL08DE01 in our example use [master] go EXECUTE [DBARCH-LT2\SQL08DE02].[UnleashedRemoteDB].[dbo].[Top10Customers] go CustomerID CompanyName City Country YTDBusiness BERTU Bertucci Villa Milano Italy 200039.80 QUICK QUICK-Stop Cunewalde Germany 117483.39 SAVEA Save-a-lot Markets Boise USA 115673.39 ERNSH Ernst Handel Graz Austria 113236.68 HUNGO Hungry Owl All-Night Cork Ireland 57317.39 RATTC Rattlesnake Canyon Albuquerque USA 52245.90 HANAR Yves Moison Paris France 34101.15 FOLKO Folk och fä HB Bräcke Sweden 32555.55 MEREP Thierry Gerardin Vannes France 32203.90 KOENE Königlich Essen Brandenburg Germany 31745.75 (10 row(s) affected) ptg 2251 Linked Servers 54 As you can see, setting up remote servers can be a bit confusing and a tedious process. You have to perform setup tasks on both the local and remote servers. In addition, the mapping of logins severely limits what types of servers can be accessed. Login mappings are performed at the remote server instead of the local server, which works fine if the remote server is a SQL Server machine, but how do you perform this task in another data- base environment that doesn’t have user mappings? How do you tell an Oracle database to which Oracle user to map a SQL Server user? When you understand how linked servers are set up and what expanded capabilities they provide, you won’t want to use remote servers unless you absolutely have to, which should be only when you need to execute RPCs on pre-7.0 SQL Servers. Linked Servers Linked servers enable SQL Server–based applications to include most any other type of data source to be part of a SQL statement execution, including being able to directly refer- ence remote SQL servers. They also make it possible to issue distributed queries, updates, deletes, inserts, commands, and full transactions on heterogeneous data sources across your entire company (network). SQL Server essentially acts as the master query manager. Then, via OLE DB providers and OLE DB data sources, any compliant data source is easily referenced from any valid SQL statement or command. For each data source, either they are directly referenced, or SQL Server creates provider-specific subqueries issued to a specialized provider. This is very close to being a federated data management capability across most heterogeneous data sources. Unlike remote servers, linked servers have two simple setup steps: 1. Define the remote server on the local server. 2. Define the method for mapping remote logins on the local server. All linked server configurations are performed on the local server. The mapping for the local user to the remote user is stored in the local SQL Server database. In fact, you don’t need to configure anything in the remote database. Using linked servers also allows SQL Server to use OLE DB to link to many data sources other than just SQL Server. OLE DB is an API that allows COM/.NET applications to work with databases as well as other data sources, such as text files and spreadsheets. This capability lets SQL Server have access to a vast amount of different types of data as if these other data sources were local SQL Server tables or views. This is extremely powerful. Unlike Remote Procedure Calls (and remote servers only), linked servers also allow distrib- uted queries and transactions. ptg 2252 CHAPTER 54 Managing Linked and Remote Servers TRULY A LINKED SERVER Keep in mind that when you define linked servers, SQL Server really keeps these data resources linked in many ways. Most importantly, it keeps the schema definitions linked. In other words, if the schema of a remote table on a linked server changes, any server that has links to it also knows the change (that is, gets the change). Even when the linked server’s schema comes from something such as Excel, if you change the Excel spreadsheet in any way, that change is automatically reflected back at the local SQL Server that has defined that Excel spreadsheet. This is extremely significant from a metadata and schema integrity point of view. This is what is meant by “com- pletely linked.” Distributed Queries Distributed queries access data stored in OLE DB data sources. SQL Server treats these data sources as if they contained SQL Server tables. Basically, via a provider such as OLE DB, the data source is put in terms of recordsets. Recordsets are the way SQL Server needs to see any data. The Microsoft SQL Native Client OLE DB provider (with PROGID SQLNCLI) is the official OLE DB provider for SQL Server 2008. You can view or manipulate data through this provider by using the same basic Data Manipulation Language (DML) syntax as for T-SQL for SQL Server ( SELECT, INSERT, UPDATE, or DELETE statements). The main difference is the table-naming convention. Distributed queries use a four-part table name syntax for each data source as follows: linked_server_name.catalog.schema.object_name The following distributed query accesses data from a sales table in an Oracle database, a region table in a Microsoft Access database, and a customer table in a SQL Server data- base—all with a single SQL statement: SELECT s.sales_amount FROM access_server region AS r, oracle_server sales_owner.sale AS s, sql_server.customer_db.dbo.customer AS c where r.region_id=s.region_id and s.customer_id=c.customer_id and r.region_name=’Southwest’ and c.customer_name=’ABC Steel’ All these data sources are on completely different physical machines. But with linked servers and distributed queries, you might not ever realize this. Distributed Transactions With SQL Server distributed transactions, it is now possible to manipulate data from several different data sources in a single transaction. Distributed transactions are supported if the OLE DB provider has built in the XA transactional functionality. For example, suppose two banks decide to merge. The first bank (let’s call it OraBank) stores ptg 2253 Adding, Dropping, and Configuring Linked Servers 54 all checking and savings accounts in an Oracle database. The second bank (let’s call it SqlBank) stores all checking and savings accounts in a SQL Server 2008 database. A customer has a checking account with OraBank and a savings account with SqlBank. What would happen if the customer wanted to transfer $100 from the checking account to the savings account? You can handle this task by using the following code while main- taining transactional consistency: BEGIN DISTRIBUTED TRANSACTION One hundred dollars is subtracted from the savings account. UPDATE oracle_server savings_owner.savings_table SET account_balance = account_balance - 100 WHERE account_number = 12345 One hundred dollars is added to the checking account. UPDATE sql_server.checking_db.dbo.checking_table SET account_balance = account_balance + 100 WHERE account_number = 98765 COMMIT TRANSACTION; The transaction is either committed or rolled back on both databases. Adding, Dropping, and Configuring Linked Servers The next few sections show how to add, drop, and configure linked servers through system stored procedures. All these configuration options can also be done very easily with SQL Server Management Studio. The following sections occasionally describe that capability but focus on the SQL commands method because you will usually use this method in real-life production systems. sp_addlinkedserver Before you can access an external data source through SQL Server, it must be registered inside the database as a linked server. Essentially, you must capture (register) the connec- tion information and specific data source information within SQL Server. After it is regis- tered, the data source can simply be referenced within the SQL statement by a single logical name. You use the sp_addlinkedserver stored procedure for this registering purpose. Only users with the sysadmin or setupadmin fixed server roles can run this procedure. SQL Server 2008 ships with a number of OLE DB providers, including providers for Oracle, DB2, Informix, Access, and other SQL Server 6.5/7.0/2000/2005 databases, as well as databases that can be reached through ODBC and JDBC. SQL Server also comes with OLE DB providers for Microsoft Excel spreadsheets and Indexing Service and a whole bunch more. Microsoft puts each of these providers through extensive testing to certify them. We have found that sometimes a provider isn’t available on the 64-bit version of SQL Server 2008 yet. So, as a precaution, check the list of 64-bit providers before you upgrade your SQL Server to the 2008 64-bit version. If you are not using a Microsoft-certi- fied provider, however, you might still be able to use that provider if it is compliant with the OLE DB provider specifications. . remote server section: LocalServerSQL .sql, RemoteServerSQL .sql, and RPCexecution .sql. Remote Server Setup You can assume that the local server is called SQL0 8DE01 and the remote server is called SQL0 8DE02. local server to the remote server. 5. Grant execute permissions for the remote login. If you are connecting between multiple SQL Server 2008, SQL Server 2005, SQL Server 2000, or SQL Server 7.0 servers,. Servers 54 Remote Server [SQL0 8DE02] SQL Server 2008 Local Server [SQL0 8DE01] Client SQL Server 2008 Results Connection Results RPCs FIGURE 54.1 The remote server is accessed through the local server. The