Nel precedente articolo I Server Collegati: da Management Studio abbiamo visto come gestire i server collegati da Management Studio; vi sono però circostanze in cui torna comodo gestirli da codice (ad esempio, in uno script di importazione dati). Ed è proprio quello che vedremo un questo articolo.
Cominciamo col dire che i dati relativi ai linked server sono reperibili nella vista di master.sys.servers
. Per verificare se un linked server esiste, possiamo interrogare la vista sulla base del name
oppure del datasource
.
SELECT * FROM sys.servers WHERE [name] = 'SERVERPRODUZIONE'; SELECT * FROM sys.servers WHERE [data_source] = 'TURRINI\SQLEXPRESS';
La gestione dei linked server avviene tramite una serie di stored procedure che creano il linked server, gli abbinano le impostazioni di login, impostano una serie di parametri ed infine rimuovono il linked server. In realtà, a seconda del tipo di linked server, alcune di queste stored procedure non sono usate.
sp_addlinkedserver |
Crea il linked server, consentendo così di accedere ai suoi dati |
sp_addlinkedsrvlogin |
Associa le informazioni di login al linked server |
sp_serveroption |
Permette di impostare alcuni parametri del collegamento |
sp_dropserver |
Rimuove il linked server |
Server collegati ad altra istanza Sql Server
Creazione
Come logico aspettarsi, la prima istruzione da richiamare è la sp_addlinkedserver
:
EXEC master.dbo.sp_addlinkedserver @server = N'SERVERPRODUZIONE', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'SRVPROD';
Parametri
@server |
è il nome, arbitrario, che attribuiamo al linked server. ovviamente non deve andare in conflitto con uno già esistente |
@srvproduct |
è il nome del prodotto. In teoria, potrebbe essere lasciato a NULL nel caso di collegamento ad un’atra istanza di Sql Server; in realtà deve essere impostato alla stringa vuota altrimenti genera un errore |
@provider |
è l’identificativo del provider OleDb. In teoria, se omesso, viene usato SQLNCLI che comporta l’uso della versione più recente del provider Sql Native Client; in realtà, se lasciato a NULL viene generato un errore, quindi è meglio specificare SQLNCLI . |
@datasrc |
è il nome dell’istanza Sql Server a cui collegarsi |
Parametri di login
Fatto ciò, dobbiamo specificare i parametri per il login (non sto nemmeno a dire che la password è inventata)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVERPRODUZIONE' , @useself=N'False' , @locallogin=NULL , @rmtuser=N'sa' , @rmtpassword='Password123'
Parametri
@rmtsrvname |
è il nome attribuito sopra al linked server |
@useself |
indica se la connessione deve essere effettuata usando l’account di accesso locale (True ) oppure remoto (False ). |
@locallogin |
se impostato a NULL (che è il valore di default), indica che la voce (il mapping) che verrà creato sarà attivo per tutti gli utenti locali; se invece viene valorizzato, può essere un account di accesso di Sql Server oppure di Windows, purchè abbia accesso a Sql Server |
@rmtuser |
è l’account di accesso remoto, usato solo se il parametro @useself è stato impostato a False . |
@rmtpassword |
la password associata all’utente remoto @rmtuser ; come @rmtuser , questo parametro viene usato solo se @useself è stato impostato a False . |
Nell’esempio riportato sopra, si accede all’istanza Sql tramite un account definito in quella stessa istanza Sql Server. Volendo usare un account di Windows (se l’istanza lo consente, ovviamente), basta cambiare un paio di parametri:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVERPRODUZIONE' , @useself=N'False' , @locallogin='DomainName\UserName' , @rmtuser=N'userName' , @rmtpassword='123password';
Opzioni aggiuntive
Infine dobbiamo specificare alcuni parametri del server collegato; per farlo useremo la stored procedure master.dbo.sp_serveroption
:
EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'collation compatible' , @optvalue=N'false'
Parametri
@server |
è il nome attribuito sopra al linked server |
@optname |
è il nome del parametro da impostare |
@optvalue |
è il valore del parametro |
I parametri impostabili sono diversi e vi rimando alla documentazione su MSDN per una descrizione più accurata.
EXEC master.dbo.sp_addlinkedserver @server = N'SERVERPRODUZIONE' , @srvproduct=N'' , @provider=N'SQLNCLI' , @datasrc=N'BASE10'; EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVERPRODUZIONE' , @useself=N'False' , @locallogin=NULL , @rmtuser=N'sa' , @rmtpassword='sqlserver2012'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'collation compatible' , @optvalue=N'false'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'data access' , @optvalue=N'true'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'dist' , @optvalue=N'false'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'pub' , @optvalue=N'false'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'rpc' , @optvalue=N'false'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'rpc out' , @optvalue=N'false'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'sub' , @optvalue=N'false'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'connect timeout' , @optvalue=N'0'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'collation name' , @optvalue=null; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'lazy schema validation' , @optvalue=N'false'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'query timeout' , @optvalue=N'0'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'use remote collation' , @optvalue=N'true'; EXEC master.dbo.sp_serveroption @server=N'SERVERPRODUZIONE' , @optname=N'remote proc transaction promotion ' , @optvalue=N'true'; EXEC master.dbo.sp_dropserver @server=N'SERVERPRODUZIONE' , @droplogins='droplogins';
Rimozione
Infine, sfrutteremo la stored procedure master.dbo.sp_dropserver
per rimuovere il server collegato (una volta che avremo finito di usarlo, ovviamente!)
EXEC master.dbo.sp_dropserver @server=N'SERVERPRODUZIONE', @droplogins='droplogins'
Parametri
@server |
è il nome attribuito precedentemente al server collegato (linked server) |
@droplogins |
se impostato a ‘droplogins’ allora rimuove anche i mapping tra utenti locali e remoti; in teoria, se lasciato a NULL dovrebbe mantenere queste associazioni |
Tutto qui? Tutto qui.