MSSQL Server. Example of using linked server

Today I decided to share the article as once the bundled server came to my rescue when working with MSSQL. First I will describe the situation in which I had to meet him.

I worked as a web programmer in the information center of one of the ministries with about a hundred subordinate institutions. In each subordinate institution on the server was installed a desktop program written in delphi, to which data was entered daily. Once a quarter, each such institution had to upload the dbf file, come to our center, according to this upload, receive reports and hand them over to the ministry. So it was still in the dos program, and then this algorithm just without changing anything, was transferred to delphi. Uploading was done using Transact-SQL, and the logic in it was not simple.

In parallel with this, the bus operated offline, which accumulated data from all institutions on a single central server. The bus had a buggy: it created duplicates on the primary key and not all of the data reached. There was no specific algorithm for correcting these errors; different employees were involved in this at different periods of time, without informing each other. The tire developer quit. After three years of such work, the data on the central server was significantly different from the data on the servers of the institutions, but all officially adhered to the version that there were no problems with the bus.

At one point, the scheme with unloading the file was considered obsolete, and money was allocated for revision. It was decided to submit reports on the site in your account by pressing a button. Now the employees of the institutions should not have traveled to deliver the report, first to us and then to the ministry, all communication should be carried out through the site. Data had to be taken from the central server. The head of the department, despite the fact that he knew about the problems with the bus, ordered to take the procedure that worked on the servers of the institutions, in the sample add condition taking into account the segment of institutions, and implement uploading reports on the site from the central server. After this was done, he appointed me in charge of this process, and the management officially announced that we would hand over the reports to the ministry in a new way.
')
Everything collapsed. Due to data discrepancies between the servers, the reports were with incorrect numbers. It also lay down in terms of performance, we were not ready for such a load. I needed a quick solution to the problem. The option to simply write the settings for connecting to their database on my website for each institution and didn’t fit the procedure on their server (using programming language), since, in addition to receiving data, it was necessary to start processing each time to convert this data into reports. The processing procedure has already been implemented and debugged in mssql on a central server, and transferring it to a programming language would take a lot of resources and time. It was necessary to handle the means of the database.

Googling I found the information that there are linked servers in MSSQL. Using them for my server, I could set up communication with any remote server that is on the same network as mine and from which I have authorization data. After setup, I could write a request on my server, indicate on which linked server it should be executed, and the request was executed on a remote server, using its databases and its resources.

To create a linked server, run the script:

EXEC master.dbo.sp_addlinkedserver @server = N'MY_SERV_1', @datasrc=N'192.168.1.1' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MY_SERV_1',@useself=N'False',@locallogin='sa',@rmtuser='sa',@rmtpassword='1111111' 

Server options

server is the name of the server by which we will access it.
@datasrc - ip address of the remote server

Server Authorization Settings

@rmtsrvname is the name we assigned to the server
@locallogin - account name
@rmtpassword - account password
@rmtuser - database user

When creating a linked server, some of the parameters for data access are set to 'false' (you can see the list of parameters here ). If you need to set some parameters to 'true', for example, 'rpc' and 'rpc out', then the following commands should be added to the creation script:

 EXEC master.dbo.sp_serveroption @server=N'MY_SERV_1', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server= N'MY_SERV_1', @optname=N'rpc out', @optvalue=N'true' GO 

Please note that in the server parameter we specified the name we gave to the linked server.

As a result, the entire server creation script would look like this.

 EXEC master.dbo.sp_addlinkedserver @server = N'MY_SERV_1', @datasrc=N'192.168.1.1' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MY_SERV_1',@useself=N'False',@locallogin='sa',@rmtuser='sa',@rmtpassword='1111111' GO EXEC master.dbo.sp_serveroption @server=N'MY_SERV_1', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server= N'MY_SERV_1', @optname=N'rpc out', @optvalue=N'true' GO 

The request to the created server is executed, as well as to its own, but at the beginning a prefix is ​​specified with the name of the associated server. Also, when accessing, you need to specify the name of the scheme (in the example below, the scheme is called 'DBO'):

 SELECT * FROM [MY_SERV_1'].MY_BASE.DBO.MY_TABLE 

In general, technical support for a couple of days for all institutions prescribed the associated server. I added the code to get the data from the servers of the institutions. The numbers in the reports became true again and the issue of performance was resolved. That's how I quickly and easily got out of a difficult situation.

Of course, initially, when developing a system, it is not necessary to lay on the associated servers to implement the described functionality. It is better initially to competently approach the design of the system, for example, by making it on the web, where there will be one central server and so that everything does not slow down to keep in the staff of experts knowledgeable in database optimization. This example is for cases when the system is already designed, and it is unlikely to be redone. A linked server will also be useful when reconciling reports, if data is only available on a productive server, and the stored procedure can be changed only on the development server.