SQL Server graphic method for operating database across servers (LinkedServer)

This article brings you relevant knowledge about SQL, which mainly introduces the graphic method of SQL Server cross-server database operation. SQL Server Management Studio (SSMS) is used to manage SQL Let’s take a look at the integrated environment of Server infrastructure. I hope it will be helpful to everyone.

Recommended study: "SQL Tutorial"

Introduction to basic knowledge

SQL Server database management tools SSMS (SQL Server Management Studio) operates for the platform.

SQL Server Management Studio (SSMS) is an integrated environment for managing SQL Server infrastructure. Using SSMS, you can access, configure, manage, and develop all components of SQL Server, Azure SQL Database, and SQL Data Warehouse. SSMS brings together a number of graphical tools and a rich script editor in one comprehensive utility to provide developers and database administrators of all skill levels with access to SQL Server.

What is a cross-server operation?

Cross-server operation means that you can connect locally to the database on the remote server and perform related database operations on the other party's database, such as additions, deletions, modifications, and searches.

Why cross-server operations are necessary

With the increase in data volume and expansion of business volume, different databases need to be installed on different servers. Sometimes due to business needs, different servers To integrate the data in the server, cross-server operations are required.

What are the tools for cross-server operations?

DBLINK (database link), as the name suggests, is a link to the database. Just like a telephone line, it is a channel. When we want to access data in another database table across a local database, the local database must To create a dblink of a remote database, the local database can access the data in the remote database table just like the local database through dblink.

Method 1: Use SSMS to create a SQL Server remote linked server (LinkedServer)--simple link to the remote SqlServer

1. Open SSMS --> Log in to the local database --> Server Object --> Linked Server (right-click) --> Create a new linked server, as shown below:

2. Enter relevant information in the pop-up dialog box

● Enter the IP address of the other server in [Linked Server];

● Select [SQL Server] in [Server Type];

3. Click [Security] on the left, and the following page will appear. In step 3, enter the account and password of the other party's database.

After clicking the OK button, the linked server (LinkedServer) is created successfully. At this time, you can see the created linked server:

View the code of the linked server: Right-click on the created linked server and write the linked server script as --> ; Create to --> New Query Editor window to open the script for the linked server you just created.

--After the linked server (LinkedServer) is created, the relevant code will be automatically generated - link to the remote SQLServer database:

EXEC master.dbo.sp_addlinkedserver @server = N'192.168.110.189,1433',@srvproduct=N'SQL Server';
-- @rmtsrvname
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'192.168.110.189',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########';

Note: There is a The disadvantage is that the link is to all databases in the entire remote SqlServer (generally only one specific database is required), and the name of the linked server is an IP and cannot be customized! Therefore, the best way is to directly create the link database through code (see "3. Code Detailed Explanation").

After the LinkedServer is successfully created, we can use the created DBLINK to connect to the remote Linked server. Let's verify it by querying the table on the other party's server using the created one.

--Method for querying data in LinkedServer: [DBLINK name].[Other database name].[Mode name of the other database].[Other database table name]

SELECT * FROM [192.168.110.189].[erp25new].[dbo].[fee_data]

The above FROM field is followed by [DBLINK name].[Partner database name]. [Schema name in the other party's database].[Table name in the other party's database]. None of the contents in front of the table name should be missing.

The query results are as follows:

Method 2: SSMS creates a SQLServer linked server (LinkedServer)--customize other databases linked to SqlServer

1. [General] selection page:

 

2.【安全性】选择页：

 

自定义链接数据库到SQLServer【新建链接服务器】对话框中需输入的相关信息说明：

1.【常规】页

● 在【链接服务器】中，输入 自定义的链接服务器别名，如：DBLINK_TO_TESTDB

● 在【服务器类型】中选择【其他数据源】；

▶[提供程序]中选择 第一个Microsoft OLE DB Provider for SQL Server

▶[产品名称]中，可以空白不填，也可以填写SQL Server { 注意提供程序是OLE DB Provider for SQL Server时产品名称这里必须为空白！}

▶[数据源]中 远程数据库的地址,端口\实例名 ，如 10.10.0.73,1433\MSSQLSERVER

▶[访问接口字符串]中，可以空着不填； 也可以填下方的：(注意######是密码,请换成自己的密码)

Provider=sqloledb;Data Source=10.10.0.73,1433\MSSQLSERVER;Initial Catalog=TESTDB;User Id=apps;Password=#####;

▶[目录]就是数据库名称，这里填上我们需要远程连上的数据库 TESTDB (可以换成自己实际的)。

2.【安全性】页

● 选择【使用此安全上下文建立连接(M)】

▶[远程登录]： 远程数据库的连接账号

▶[使用密码]： 远程数据库连接账号的密码

--链接服务器(LinkedServer)创建完成后会自动生成相关代码 —— 链接到远程的SQLServer数据库(自定义)：

EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_TO_TESTDB',@srvproduct=N'',@provider=N'SQLNCLI', @datasrc=N'10.10.0.73';EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname=N'DBLINK_TO_TESTDB',@useself=N'False',@locallogin=NULL,@rmtuser=N'apps',@rmtpassword='########';

/****** 实际例子 系统生成的Object: LinkedServer [DBLINK_TO_TESTDB] ******/

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_TO_TESTDB', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.10.0.73,1433\MSSQLSERVER', @catalog=N'TESTDB'

/*For security reasons the linked server remote logins password is changed with ########*/

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST',@useself=N'False',@locallogin=NULL,@rmtuser=N'apps',@rmtpassword='########'

其他方式： 提供程序换成其它的， 如本机SQL Server Native Client 11.0 (SQL Server Native Client 11.0 不支持连接到SQL Server 2000或更早的版本) 等

 

方法三：用SSMS创建SQLServer链接服务器(LinkedServer)--链接到非SqlServer的其它数据库

 

四、代码详解：方法一和方法二是通过SSMS直接操作的,下方直接使用sql脚本来创建链接服务器(LinkedServer)

A. SSMS链接到远程SQLServer数据库

(本地SQLServer数据库链接服务器(LinkedServer)到远程SQLServer数据库。)

--LinkedServer链接到远程SQLServer数据库：

--1. 声明将要链接的‘链接名称(自定义)’,远程数据库产品名(或别名),(提供商,数据库服务器地址及实例名)

EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_TO_TESTDB',@srvproduct=N'SQL Server';

--2. 声明‘链接名称(自定义)’,@useself=N'False',@locallogin=NULL,将要链接的数据库服务器的账号和密码

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'192.168.110.189',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########';

B. SSMS链接到远程非SQLServer数据库

(本地SQLServer数据库链接服务器(LinkedServer)到远程非SQLServer的数据库。如远程的MySQL、Oracle等数据库。)

--链接到远程的非SQLServerd数据库(如链接到远程MySQL、Oracle等数据库)：

--1. 声明‘自定义的链接名称’,远程数据库产品名(或别名),提供商,数据库服务器地址及实例名

EXEC master.dbo.sp_addlinkedserver @server = N'TEST_SQL_SERVER',@srvproduct=N'TEST',@provider=N'SQLNCLI11', @datasrc=N'192.168.110.189';-

-2. 声明登录信息 ‘自定义的链接名称’,@useself=N'False',@locallogin=NULL,远程数据库的账号和密码

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST_SQL_SERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########';

实际例子-SQL Server通过Linkserver连接MySql

--通过SSMS链接到远程MySql数据库(SQL Server连接MySql)--使用的访问接口为：MySql Provider for OLE DB--

EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_TO_MysqlTESTDB', @srvproduct = N'MySql', @provider = N'MSDASQL', @provstr = N'Driver={MySQL ODBC 5.1 Driver};Server=10.167.69.6,3306/sytv;Database=TESTDB;User=root;Password=root;Option=3';--

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'DBLINK_TO_MysqlTESTDB', @useself = N'False', @locallogin = N'10.167.69.6,3306/sytv', @rmtuser = N'root', @rmtpassword = N'root';

实际例子-SQL Server通过Linkserver连接Oracle

--通过SSMS链接到远程Oracle数据库(SQL Server连接Oracle)

--使用的访问接口为：Oracle Provider for OLE DB

USE [master]

GO

--Declare Oracle OLEDB 'OraOLEDB.Oracle':

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1;--Create the Linked Server to the ECT database in Oracle:

EXEC sp_addlinkedserver 'DBLINK_TO_OraTESTDB', 'Oracle', 'OraOLEDB.Oracle', '10.167.69.6/prt';--EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_TO_OraTESTDB', @srvproduct=N'oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'10.167.69.6/orcl'

--Create the Remote Login for the Oracle Linked Server:

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'DBLINK_TO_OraTESTDB',@useself=N'False',@locallogin=N'apps',@rmtuser=N'SYSTEM',@rmtpassword='######'; 

--最后可以测试一下是否连接成功 --select * from openquery(DBLINK_TO_OraTESTDB,'select * from SYSTEM.HELP');

推荐学习：《SQL教程》

The above is the detailed content of SQL Server graphic method for operating database across servers (LinkedServer). For more information, please follow other related articles on the PHP Chinese website!