Méthode graphique SQL Server pour exploiter la base de données sur plusieurs serveurs (LinkedServer)

Cet article vous apporte des connaissances pertinentes sur SQL, qui présente principalement la méthode graphique de fonctionnement de la base de données inter-serveurs SQL Server. SQL Server Management Studio (SSMS) est un environnement intégré pour gérer l'infrastructure SQL Server, jetons un coup d'œil. ça, j'espère que cela sera utile à tout le monde.

Apprentissage recommandé : "Tutoriel SQL"

Introduction aux connaissances de base

Utilisez l'outil de gestion de base de données SSMS (SQL Server Management Studio) de SQL Server comme plate-forme de fonctionnement.

SQL Server Management Studio (SSMS) est un environnement intégré pour gérer l'infrastructure SQL Server. À l'aide de SSMS, vous pouvez accéder, configurer, gérer et développer tous les composants de SQL Server, Azure SQL Database et SQL Data Warehouse. SSMS rassemble un certain nombre d'outils graphiques et un éditeur de script riche dans un seul utilitaire complet pour fournir aux développeurs et aux administrateurs de bases de données de tous niveaux un accès à SQL Server.

Qu'est-ce que le fonctionnement inter-serveurs ?

Le fonctionnement entre serveurs signifie que vous pouvez vous connecter localement à la base de données sur le serveur distant et effectuer des opérations de base de données associées sur la base de données de l'autre partie, telles que des ajouts, des suppressions, des modifications et des recherches.

Pourquoi les opérations inter-serveurs sont nécessaires

Avec l'augmentation du volume de données et l'expansion du volume d'affaires, différentes bases de données doivent être installées sur différents serveurs. Parfois, en raison des besoins de l'entreprise, les données de différents serveurs sont actuellement intégrées. -des opérations de serveur sont requises.

Quels sont les outils pour les opérations cross-serveur ?

DBLINK (lien de base de données), comme son nom l'indique, est un lien vers la base de données, tout comme une ligne téléphonique, c'est un canal. Lorsque nous voulons accéder aux données d'une autre table de base de données via une base de données locale, une base de données distante doit être utilisée. être créé dans la base de données locale dblink, via dblink, la base de données locale peut accéder aux données des tables de base de données distantes, tout comme accéder à la base de données locale.

Méthode 1 : Utilisez SSMS pour créer un serveur lié à distance SQL Server (LinkedServer) -- lien simple vers le SqlServer distant

1 Ouvrez SSMS --> Connectez-vous à la base de données locale --> > Serveur lié (clic droit) --> Créez un nouveau serveur lié, comme indiqué ci-dessous :

2 Saisissez les informations pertinentes dans la boîte de dialogue contextuelle

Quantity Saisissez l'adresse IP de l'autre serveur dans. [Link Server] ;

weight Dans [Server] Type], sélectionnez [SQL Server]

3. Cliquez sur [Sécurité] à gauche, la page suivante apparaîtra, entrez le compte et le mot de passe de l'autre partie. base de données à l’étape 3.

Après avoir cliqué sur le bouton OK, le LinkedServer est créé avec succès. À ce moment, vous pouvez voir le serveur lié créé :

Afficher le code du serveur lié : Faites un clic droit sur le serveur lié créé et écrivez le script du serveur lié sous la forme --> Modification de la requête La fenêtre du serveur ouvrira le script du serveur lié que vous venez de créer.

--Une fois le LinkedServer créé, le code correspondant sera automatiquement généré - Lien vers la base de données SQLServer distante :

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='########';

Remarque : Il y a un inconvénient ici, c'est-à-dire que toutes les bases de données de l'ensemble du SqlServer distant sont lié (généralement, seule une base de données spécifique est requise), et le nom du serveur lié est une IP et ne peut pas être personnalisé ! Par conséquent, le meilleur moyen est de créer directement la base de données de liens via le code (voir « 3. Explication détaillée du code »).

Une fois le LinkedServer créé avec succès, nous pouvons utiliser le DBLINK créé pour nous connecter au serveur Linked distant. Vérifions-le en interrogeant la table sur le serveur de l'autre partie en utilisant celle créée.

--Méthode pour interroger les données dans LinkedServer : [Nom DBLINK].[Nom de la base de données partenaire].[Nom du schéma de la base de données du modèle].[Nom de la table de la base de données partenaire]

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

Le champ FROM ci-dessus est suivi de [Nom DBLINK].[Autre nom de la base de données].[Nom du modèle sous l'autre base de données].[Autre nom de la table de la base de données], table Aucun contenu devant le nom peut manquer.

Les résultats de la requête sont les suivants :

Méthode 2 : SSMS crée un serveur lié à SQLServer (LinkedServer) - personnalisez d'autres bases de données liées à SqlServer

1.

 

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教程》

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!